使用OLEDB从Excel导入时数据被截断,有什么替代方案?

本文关键字:什么 方案 Excel OLEDB 导入 数据 使用 | 更新日期: 2023-09-27 18:14:03

我有一个WPF应用程序,我有从Excel读取数据的功能。我使用OLEDB做这件事,它工作得很好,直到我发现列有255个限制,除非数据> 255个字符不存在于前八行中,否则数据将被截断。修复此问题是更新注册表,这意味着更新所有用户的注册表。所以我不想用那种方法。

OLEDB代码:

string strSQL = "SELECT * FROM [Sheet1$]";
 OleDbCommand cmd = new OleDbCommand(strSQL, conn);
 DataSet ds1 = new DataSet();
 OleDbDataAdapter da = new OleDbDataAdapter(cmd);
 da.Fill(ds1);

作为替代方案,我尝试了Interop。Excel。然而,它似乎比OLEDB慢。使用Interop.Excel加载需要2秒的Excel表格需要大约15秒。

System.Data.DataTable tempTable = new System.Data.DataTable();
tempTable.TableName = "ResultData";
Excel.Application app = new Excel.Application();
Excel.Workbook book = null;
Excel.Range range = null;
try
{
 app.Visible = false;
 app.ScreenUpdating = false;
 app.DisplayAlerts = false;
 book = app.Workbooks.Open(inputFilePath, Missing.Value, Missing.Value, Missing.Value
                                              , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                                             , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                                            , Missing.Value, Missing.Value, Missing.Value);
  foreach (Excel.Worksheet sheet in book.Worksheets)
  {
    Logger.LogException("Values for Sheet " + sheet.Index, System.Reflection.MethodBase.GetCurrentMethod().ToString());
    // get a range to work with
    range = sheet.get_Range("A1", Missing.Value);
    // get the end of values to the right (will stop at the first empty cell)
    range = range.get_End(Excel.XlDirection.xlToRight);
    // get the end of values toward the bottom, looking in the last column (will stop at first empty cell)
    range = range.get_End(Excel.XlDirection.xlDown);
     // get the address of the bottom, right cell
     string downAddress = range.get_Address(
     false, false, Excel.XlReferenceStyle.xlA1,
     Type.Missing, Type.Missing);
      // Get the range, then values from a1
      range = sheet.get_Range("A1", downAddress);
                    object[,] values = (object[,])range.Value2;
      //Get the Column Names 
      for (int k = 0; k < values.GetLength(1); )
      {
         tempTable.Columns.Add(Convert.ToString(values[1, ++k]).Trim());
      }
      for (int i = 2; i <= values.GetLength(0); i++)//first row contains the column names, so start from the next row.
      {
      System.Data.DataRow dr = tempTable.NewRow();
         for (int j = 1; j <= values.GetLength(1); j++)//columns
        {
           dr[j - 1] = values[i, j];
         }
                        tempTable.Rows.Add(dr);
                    }
                }

是否有另一种替代方案与OLEDB一样快?

列和行在Excel工作表中不是固定的。

使用OLEDB从Excel导入时数据被截断,有什么替代方案?

如果您正在使用xlsx文件,我建议您切换到Open XML SDK for Office,它比OLEDB或互操作连接方法性能要好得多。

然而,有些人认为SDK很难使用,所以有第三方软件包将SDK包装成一个更友好的界面,但我个人不觉得SDK太难做。

感谢您的回复。这是我最后使用的代码:参考链接:http://www.prowareness.com/blog/reading-data-from-excel-document-using-openxml/

 public static DataSet ExtractExcelSheetValuesToDataTable(string xlsxFilePath, string sheetName)
        {
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(xlsxFilePath, true))
            {
                //Access the main Workbook part, which contains data
                WorkbookPart workbookPart = myWorkbook.WorkbookPart;
                WorksheetPart worksheetPart = null;
                if (!string.IsNullOrEmpty(sheetName))
                {
                    Sheet ss = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).SingleOrDefault<Sheet>();
                    worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);
                }
                else
                {
                    worksheetPart = workbookPart.WorksheetParts.FirstOrDefault();
                }
                SharedStringTablePart stringTablePart = workbookPart.SharedStringTablePart;
                if (worksheetPart != null)
                {
                    Row lastRow = worksheetPart.Worksheet.Descendants<Row>().LastOrDefault();
                    Row firstRow = worksheetPart.Worksheet.Descendants<Row>().FirstOrDefault();
                    if (firstRow != null)
                    {
                        foreach (Cell c in firstRow.ChildElements)
                        {
                            string value = GetValue(c, stringTablePart);
                            dt.Columns.Add(value);
                        }
                    }
                    if (lastRow != null)
                    {
                        for (int i = 2; i <= lastRow.RowIndex; i++)
                        {
                            DataRow dr = dt.NewRow();
                            bool empty = true;
                            Row row = worksheetPart.Worksheet.Descendants<Row>().Where(r => i == r.RowIndex).FirstOrDefault();
                            int j = 0;
                            if (row != null)
                            {
                                foreach (Cell c in row.Descendants<Cell>())
                                {
                                    int? colIndex = GetColumnIndex(((DocumentFormat.OpenXml.Spreadsheet.CellType)(c)).CellReference);
                                    if (colIndex > j)
                                    {
                                        dr[j] = "";
                                        j++;
                                    }
                                    //Get cell value
                                    string value = GetValue(c, stringTablePart);
                                    //if (!string.IsNullOrEmpty(value))
                                    //    empty = false;
                                    dr[j] = value;
                                    j++;
                                    if (j == dt.Columns.Count)
                                        break;
                                }
                                //foreach (Cell c in row.ChildElements)
                                //{
                                //    //Get cell value
                                //    string value = GetValue(c, stringTablePart);
                                //    //if (!string.IsNullOrEmpty(value))
                                //    //    empty = false;
                                //    dr[j] = value;
                                //    j++;
                                //    if (j == dt.Columns.Count)
                                //        break;
                                //}
                                //if (empty)
                                //    break;
                                dt.Rows.Add(dr);
                            }
                        }
                    }
                }
            }
            ds.Tables.Add(dt);
            return ds;
        }
        public static string GetValue(Cell cell, SharedStringTablePart stringTablePart)
        {
            if (cell.ChildElements.Count == 0) return null;
            //get cell value
            string value = cell.ElementAt(0).InnerText;//CellValue.InnerText;
            //Look up real value from shared string table
            if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
                value = stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
            return value;
        }
        private static int? GetColumnIndex(string cellReference)
        {
            if (string.IsNullOrEmpty(cellReference))
            {
                return null;
            }
            //remove digits
            string columnReference = Regex.Replace(cellReference.ToUpper(), @"['d]", string.Empty);
            int columnNumber = -1;
            int mulitplier = 1;
            //working from the end of the letters take the ASCII code less 64 (so A = 1, B =2...etc)
            //then multiply that number by our multiplier (which starts at 1)
            //multiply our multiplier by 26 as there are 26 letters
            foreach (char c in columnReference.ToCharArray().Reverse())
            {
                columnNumber += mulitplier * ((int)c - 64);
                mulitplier = mulitplier * 26;
            }
            //the result is zero based so return columnnumber + 1 for a 1 based answer
            //this will match Excel's COLUMN function
            return columnNumber;
        }