我正在尝试使用Oledb从我的网络上读取导出的文件，但总是在代码 OleDbConnection.open() 上获得“ External table is table is not in the expected format ” . 但是当我尝试将该excel上的值复制到新的excel时，我的代码正在使用新的代码 . 它奇怪吧？我必须将值移动到新的excel中才能读取它
这是我的导出excel的最后一个代码，我已经尝试了另一个代码，比如使用htmlwriter make excel，结果总是相同的 .
[HttpGet]
public ActionResult ExportToExcel()
{ExcelPackage pck = new ExcelPackage();
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Sheet1");
ws.Cells.Style.Font.Size = 11; //Default font size for whole sheet
ws.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet
if (System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.IsRightToLeft) // Right to Left for Arabic lang
{
ExcelWorksheetView wv = ws.View;
wv.ZoomScale = 100;
wv.RightToLeft = true;
ws.PrinterSettings.Orientation = eOrientation.Landscape;
ws.Cells.AutoFitColumns();
}
else
{
ExcelWorksheetView wv = ws.View;
wv.ZoomScale = 100;
wv.RightToLeft = false;
ws.PrinterSettings.Orientation = eOrientation.Landscape;
ws.Cells.AutoFitColumns();
}
ws.Cells.AutoFitColumns();
DataTable dt = new DataTable(); // Read records from database here
var ReturID = (Int64)Session["ReturID"];
var Header = ReturService.GetHeader(ReturID, "");
List<Kino.ViewModel.psa_kino_retur_materialViewModel> test = new List<Kino.ViewModel.psa_kino_retur_materialViewModel>();
test = ReturService.GetTableRetur(ReturID);
var v = test.AsQueryable();
var Data = (from a in v select a).ToList().Select(
p => new
{
p.material_id,
p.material_name,
p.delivery_qty,
p.sales_unit,
p.retur_information
}
); ;
DataColumn[] cols = { new DataColumn("Material", typeof(string)),
new DataColumn("Material Name", typeof(string)), new DataColumn("Quantity", typeof(int)),
new DataColumn("Sales Unit", typeof(string)),new DataColumn("Order Reason", typeof(string)) };
dt.Columns.AddRange(cols);
foreach (var item in Data)
{
DataRow row = dt.NewRow();
row[0] = item.material_id;
row[1] = item.material_name;
row[2] = item.delivery_qty;
row[3] = item.sales_unit;
row[4] = item.retur_information;
dt.Rows.Add(row);
}
ws.Cells[2,1].LoadFromDataTable(dt, true); // Print headers true
ExcelWorksheet ws2 = pck.Workbook.Worksheets.Add("Sheet2");
ExcelWorksheet ws3 = pck.Workbook.Worksheets.Add("Sheet3");
HttpContext.Response.Clear();
HttpContext.Response.AddHeader("", "");
HttpContext.Response.Charset = System.Text.UTF8Encoding.UTF8.WebName;
HttpContext.Response.ContentEncoding = System.Text.UTF8Encoding.UTF8;
HttpContext.Response.AddHeader("content-disposition", "attachment; filename=Report.xls");
HttpContext.Response.ContentType = "application/text";
HttpContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
HttpContext.Response.BinaryWrite(pck.GetAsByteArray());
HttpContext.Response.End();
return View();}
这是我读取excel的代码
if (postedFile != null)
{
string path = Server.MapPath("~/Uploads/");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
filePath = path + Path.GetFileName(postedFile.FileName);
string extension = Path.GetExtension(postedFile.FileName);
postedFile.SaveAs(filePath);
string conString = string.Empty;
switch (extension)
{
case ".xls": //Excel 97-03.
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 and above.
conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
DataTable dt = new DataTable();
conString = string.Format(conString, filePath);
using (OleDbConnection connExcel = new OleDbConnection(conString))
{
using (OleDbCommand cmdExcel = new OleDbCommand())
{
using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
{
cmdExcel.Connection = connExcel;
//Get the name of First Sheet.
try
{
connExcel.Open();
}
catch (Exception)
{
throw;
}
try
{
DataTable Count = new DataTable();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
//Read Data from First Sheet.
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
odaExcel.SelectCommand = cmdExcel;
odaExcel.Fill(Count);
connExcel.Close();
connExcel.Open();
int CheckHeader = 2;
Boolean Stoplooping = false;
foreach (DataRow row in Count.Rows)
{
foreach (var item in row.ItemArray)
{
if (item.ToString()== "Material")
{
Stoplooping = true;
break;
}
}
if (Stoplooping==true)
{
break;
}
CheckHeader++;
}
cmdExcel.CommandText = "SELECT * From [" + sheetName + "A"+CheckHeader+":AA" + (Count.Rows.Count + 1).ToString() + "]";
odaExcel.SelectCommand = cmdExcel;
odaExcel.Fill(dt);
connExcel.Close();
}
catch (Exception)
{
ModelState.AddModelError("", "No Data Found");
}
}
}
}
这是oled db的连接字符串
<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;'" />
<add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=2;'" />