如何将Excel工作表导出到DataTable

问题描述:

我使用以下代码导出

I used following code to export

public DataTable Import(String path)
        {
            System.Data.DataTable dt = null;

                try
                  {

                      int index = 0;
                      object rowIndex = 2;

                      dt = new System.Data.DataTable();
                      dt.Columns.Add("Client Name");
                      dt.Columns.Add("Course Name");
                      dt.Columns.Add("File Name");
                      dt.Columns.Add("File Type");
                      dt.Columns.Add("Developer");
                      dt.Columns.Add("Status");



                      DataRow row;

                      Microsoft.Office.Interop.Excel.ApplicationClass app = new Microsoft.Office.Interop.Excel.ApplicationClass();
                      Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                      Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;



                      while (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2 != null)
                      {
                          rowIndex = 2 + index;
                          row = dt.NewRow();
                          row[0] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2);
                          row[1] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 2]).Value2);
                          row[2] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 3]).Value2);
                          row[3] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 4]).Value2);
                          row[4] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 5]).Value2);
                          row[5] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 6]).Value2);
                          index++;
                          dt.Rows.Add(row);


                      }
                      app.Workbooks.Close();
                    }
                    catch(Exception ex)
                    {
                        lblError.Text = ex.Message;
                    }
                    return dt;
        }


但它导致以下错误:


But its leading to following error :

Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).<br />


那么如何在不使用Microsoft.Office.Interop.Excel的情况下上传excel?


So how to upload excel without using Microsoft.Office.Interop.Excel ?

public class ExcelToDataTable
    {
        public static DataTable ExtractExcelSheetValuesToDataTable(string xlsxFilePath, string sheetName, out bool blnResult)
        {
            blnResult = false;
            DataTable dt = new DataTable();
            using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(xlsxFilePath, true))
            {
                //Access the main Workbook part, which contains data
                WorkbookPart workbookPart = spreadSheet.WorkbookPart;
                WorksheetPart worksheetPart = null;
                if (!string.IsNullOrEmpty(sheetName))
                {
                    Sheet ss = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).SingleOrDefault<Sheet>();
                    if (ss != null)
                    {
                        worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);
                        if (worksheetPart != null)
                        {
                            blnResult = true;
                        }
                    }
                    else
                    {
                        blnResult = false;
                    }
                }
                else
                {
                    blnResult = false;
                }
                if (!blnResult)
                {
                    return null;
                }
                SharedStringTablePart stringTablePart = workbookPart.SharedStringTablePart;
                if (worksheetPart != null)
                {
                    Row firstRow = worksheetPart.Worksheet.Descendants<Row>().FirstOrDefault();
                    //Get Columns
                    if (firstRow != null)
                    {
                        foreach (Cell c in firstRow.ChildElements)
                        {
                            string value = GetValue(c, stringTablePart);
                            //Add Columns to datatable
                            dt.Columns.Add(value);
                        }
                    }
                    IEnumerable<Row> dataRows = from row in worksheetPart.Worksheet.Descendants<Row>() where row.RowIndex > 1 select row;
                    foreach (Row row in dataRows)
                    {
                        //Create New Row
                        DataRow dr = dt.NewRow();
                        int j = 0;
                        int n;
                        if (row != null)
                        {
                            Boolean blnIsEmpty = true;
                            var catalogueValues =
                            from cell in row.Descendants<Cell>()
                            select new ExcelCol
                              {
                                  cellVal = (cell.CellValue != null ? (Int32.TryParse(cell.CellValue.InnerText, out n) ? (cell.DataType != null && cell.DataType == CellValues.SharedString ? stringTablePart.SharedStringTable.ChildElements[int.Parse(cell.CellValue.InnerText)].InnerText : cell.CellValue.InnerText) : cell.CellValue.Text) : ""),
                                  celRef = GetColumnNumber(Regex.Replace(cell.CellReference.Value, @"\d", ""))
                              };
                            foreach (var item in catalogueValues)
                            {
                                if (!string.IsNullOrEmpty(item.cellVal))
                                {
                                    dr[item.celRef - 1] = item.cellVal;
                                    blnIsEmpty = false;
                                }
                                j++;
                                if (j == dt.Columns.Count)
                                    break;
                            }
                            if (!blnIsEmpty)
                            {
                                dt.Rows.Add(dr);
                            }
                        }
                    }
                }
            }
            return dt;
        }
        private static int GetColumnNumber(string name)
        {
            int number = 0;
            int pow = 1;
            for (int i = name.Length - 1; i >= 0; i--)
            {
                number += (name[i] - 'A' + 1) * pow; pow *= 26;
            }
            return number;
        }
        private 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;
            Int32 val;
            if (Int32.TryParse(value, out val))
            {
                //Look up real value from shared string table
                if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
                {
                    value = stringTablePart.SharedStringTable.ChildElements[val].InnerText;
                }
            }
            else
            {
                value = cell.CellValue.Text;
            }
            return value;
        }
    }


使用
DocumentFormat.OpenXml.Spreadsheet


像这样:
BULK INSERT DataBaseName.dbo.[tableName]
   FROM ''c:\Upload.csv''
   WITH
      (
         FIELDTERMINATOR = '','',
         ROWTERMINATOR = ''\n''             
      )



祝你好运!



Good luck!