C# 快速将 Excel 导入数据表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17577184/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Importing Excel into a DataTable Quickly
提问by user1334858
I am trying to read an Excel file into a list of Data.DataTable, although with my current method it can take a very long time. I essentually go Worksheet by Worksheet, cell by cell, and it tends to take a very long time. Is there a quicker way of doing this? Here is my code:
我正在尝试将 Excel 文件读入 Data.DataTable 列表，尽管使用我当前的方法可能需要很长时间。我基本上一个工作表，一个单元一个单元地去工作表，它往往需要很长时间。有没有更快的方法来做到这一点？这是我的代码：
List<DataTable> List = new List<DataTable>();
// Counting sheets
for (int count = 1; count < WB.Worksheets.Count; ++count)
{
// Create a new DataTable for every Worksheet
DATA.DataTable DT = new DataTable();
WS = (EXCEL.Worksheet)WB.Worksheets.get_Item(count);
textBox1.Text = count.ToString();
// Get range of the worksheet
Range = WS.UsedRange;
// Create new Column in DataTable
for (cCnt = 1; cCnt <= Range.Columns.Count; cCnt++)
{
textBox3.Text = cCnt.ToString();
Column = new DataColumn();
Column.DataType = System.Type.GetType("System.String");
Column.ColumnName = cCnt.ToString();
DT.Columns.Add(Column);
// Create row for Data Table
for (rCnt = 0; rCnt <= Range.Rows.Count; rCnt++)
{
textBox2.Text = rCnt.ToString();
try
{
cellVal = (string)(Range.Cells[rCnt, cCnt] as EXCEL.Range).Value2;
}
catch (Microsoft.CSharp.RuntimeBinder.RuntimeBinderException)
{
ConvertVal = (double)(Range.Cells[rCnt, cCnt] as EXCEL.Range).Value2;
cellVal = ConvertVal.ToString();
}
// Add to the DataTable
if (cCnt == 1)
{
Row = DT.NewRow();
Row[cCnt.ToString()] = cellVal;
DT.Rows.Add(Row);
}
else
{
Row = DT.Rows[rCnt];
Row[cCnt.ToString()] = cellVal;
}
}
}
// Add DT to the list. Then go to the next sheet in the Excel Workbook
List.Add(DT);
}
采纳答案by D Stanley
Caling .Value2is an expensive operation because it's a COM-interop call. I would instead read the entire range into an array and then loop through the array:
校准.Value2是一项昂贵的操作，因为它是一个 COM 互操作调用。我会改为将整个范围读入一个数组，然后遍历该数组：
object[,] data = Range.Value2;
// Create new Column in DataTable
for (int cCnt = 1; cCnt <= Range.Columns.Count; cCnt++)
{
textBox3.Text = cCnt.ToString();
var Column = new DataColumn();
Column.DataType = System.Type.GetType("System.String");
Column.ColumnName = cCnt.ToString();
DT.Columns.Add(Column);
// Create row for Data Table
for (int rCnt = 1; rCnt <= Range.Rows.Count; rCnt++)
{
textBox2.Text = rCnt.ToString();
string CellVal = String.Empty;
try
{
cellVal = (string)(data[rCnt, cCnt]);
}
catch (Microsoft.CSharp.RuntimeBinder.RuntimeBinderException)
{
ConvertVal = (double)(data[rCnt, cCnt]);
cellVal = ConvertVal.ToString();
}
DataRow Row;
// Add to the DataTable
if (cCnt == 1)
{
Row = DT.NewRow();
Row[cCnt.ToString()] = cellVal;
DT.Rows.Add(Row);
}
else
{
Row = DT.Rows[rCnt + 1];
Row[cCnt.ToString()] = cellVal;
}
}
}
回答by Vimal CK
Please check out the below links
请查看以下链接
http://www.codeproject.com/Questions/376355/import-MS-Excel-to-datatable(6 solutions posted)
http://www.codeproject.com/Questions/376355/import-MS-Excel-to-datatable（发布了 6 个解决方案）
回答by alex.pulver
MS Office Interop is slow and even Microsoft does not recommend Interop usage on server side and cannot be use to import large Excel files. For more details see why not to use OLE Automationfrom Microsoft point of view.
MS Office Interop 很慢，甚至 Microsoft 也不建议在服务器端使用 Interop，并且不能用于导入大型 Excel 文件。有关更多详细信息，请参阅从 Microsoft 的角度来看为什么不使用 OLE 自动化。
Instead, you can use any Excel library, like EasyXLSfor example. This is a code sample that shows how to read the Excel file:
相反，您可以使用任何 Excel 库，例如EasyXLS。这是一个代码示例，展示了如何读取 Excel 文件：
ExcelDocument workbook = new ExcelDocument();
DataSet ds = workbook.easy_ReadXLSActiveSheet_AsDataSet("excel.xls");
DataTable dataTable = ds.Tables[0];
If your Excel file has multiple sheets or for importing only ranges of cells (for better performances) take a look to more code samples on how to import Excel to DataTable in C# using EasyXLS.
如果您的 Excel 文件有多个工作表或仅导入单元格范围（以获得更好的性能），请查看更多代码示例，了解如何使用 EasyXLS 在 C# 中将 Excel 导入到 DataTable。
回答by Derpy
In case anyone else is using EPPlus. This implementation is pretty naive, but there are comments that draw attention to such. If you were to layer one more method GetWorkbookAsDataSet()on top it would do what the OP is asking for.
如果其他人正在使用 EPPlus。这种实现非常幼稚，但有一些评论引起了人们的注意。如果您要GetWorkbookAsDataSet()在顶部再添加一种方法，它将满足 OP 的要求。
/// <summary>
/// Assumption: Worksheet is in table format with no weird padding or blank column headers.
///
/// Assertion: Duplicate column names will be aliased by appending a sequence number (eg. Column, Column1, Column2)
/// </summary>
/// <param name="worksheet"></param>
/// <returns></returns>
public static DataTable GetWorksheetAsDataTable(ExcelWorksheet worksheet)
{
var dt = new DataTable(worksheet.Name);
dt.Columns.AddRange(GetDataColumns(worksheet).ToArray());
var headerOffset = 1; //have to skip header row
var width = dt.Columns.Count;
var depth = GetTableDepth(worksheet, headerOffset);
for (var i = 1; i <= depth; i++)
{
var row = dt.NewRow();
for (var j = 1; j <= width; j++)
{
var currentValue = worksheet.Cells[i + headerOffset, j].Value;
//have to decrement b/c excel is 1 based and datatable is 0 based.
row[j - 1] = currentValue == null ? null : currentValue.ToString();
}
dt.Rows.Add(row);
}
return dt;
}
/// <summary>
/// Assumption: There are no null or empty cells in the first column
/// </summary>
/// <param name="worksheet"></param>
/// <returns></returns>
private static int GetTableDepth(ExcelWorksheet worksheet, int headerOffset)
{
var i = 1;
var j = 1;
var cellValue = worksheet.Cells[i + headerOffset, j].Value;
while (cellValue != null)
{
i++;
cellValue = worksheet.Cells[i + headerOffset, j].Value;
}
return i - 1; //subtract one because we're going from rownumber (1 based) to depth (0 based)
}
private static IEnumerable<DataColumn> GetDataColumns(ExcelWorksheet worksheet)
{
return GatherColumnNames(worksheet).Select(x => new DataColumn(x));
}
private static IEnumerable<string> GatherColumnNames(ExcelWorksheet worksheet)
{
var columns = new List<string>();
var i = 1;
var j = 1;
var columnName = worksheet.Cells[i, j].Value;
while (columnName != null)
{
columns.Add(GetUniqueColumnName(columns, columnName.ToString()));
j++;
columnName = worksheet.Cells[i, j].Value;
}
return columns;
}
private static string GetUniqueColumnName(IEnumerable<string> columnNames, string columnName)
{
var colName = columnName;
var i = 1;
while (columnNames.Contains(colName))
{
colName = columnName + i.ToString();
i++;
}
return colName;
}
回答by Adil
Dim sSheetName As String
Dim sConnection As String
Dim dtTablesList As DataTable
Dim oleExcelCommand As OleDbCommand
Dim oleExcelReader As OleDbDataReader
Dim oleExcelConnection As OleDbConnection
sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test.xls;Extended Properties=""Excel 12.0;HDR=No;IMEX=1"""
oleExcelConnection = New OleDbConnection(sConnection)
oleExcelConnection.Open()
dtTablesList = oleExcelConnection.GetSchema("Tables")
If dtTablesList.Rows.Count > 0 Then
sSheetName = dtTablesList.Rows(0)("TABLE_NAME").ToString
End If
dtTablesList.Clear()
dtTablesList.Dispose()
If sSheetName <> "" Then
oleExcelCommand = oleExcelConnection.CreateCommand()
oleExcelCommand.CommandText = "Select * From [" & sSheetName & "]"
oleExcelCommand.CommandType = CommandType.Text
oleExcelReader = oleExcelCommand.ExecuteReader
nOutputRow = 0
While oleExcelReader.Read
End While
oleExcelReader.Close()
End If
oleExcelConnection.Close()
回答by Balaji Dinakaran
class DataReader
{
Excel.Application xlApp;
Excel.Workbook xlBook;
Excel.Range xlRange;
Excel.Worksheet xlSheet;
public DataTable GetSheetDataAsDataTable(String filePath, String sheetName)
{
DataTable dt = new DataTable();
try
{
xlApp = new Excel.Application();
xlBook = xlApp.Workbooks.Open(filePath);
xlSheet = xlBook.Worksheets[sheetName];
xlRange = xlSheet.UsedRange;
DataRow row=null;
for (int i = 1; i <= xlRange.Rows.Count; i++)
{
if (i != 1)
row = dt.NewRow();
for (int j = 1; j <= xlRange.Columns.Count; j++)
{
if (i == 1)
dt.Columns.Add(xlRange.Cells[1, j].value);
else
row[j-1] = xlRange.Cells[i, j].value;
}
if(row !=null)
dt.Rows.Add(row);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
xlBook.Close();
xlApp.Quit();
}
return dt;
}
}
