C#使用OleDb读取导出的文件Excel(始终获取异常外部表不是预期的格式)

我正在尝试使用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;'" />

回答(0)