如何将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!