摘要:[C#] Excel to DataTable by Microsoft.Office.Interop.Excel
public DataTable getExcel(string strPath, out string strMsg)
{
try
{
//Excel to Array[][]
Excel.Application xlsApp = new Excel.Application();
xlsApp.Visible = true;
Excel.Workbook xlsBook = xlsApp.Workbooks.Open(txtFile.Text);
Excel.Worksheet xlsSheet = xlsBook.ActiveSheet;
Excel.Range xlsRangeFirstCell = xlsSheet.get_Range("A1");
Excel.Range xlsRangeLastCell = xlsSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell);
Excel.Range xlsRange = xlsSheet.get_Range(xlsRangeFirstCell, xlsRangeLastCell);
object[,] objValue = xlsRange.Value2 as object[,];
//Array[][] to DataTable
long lngColumnCount = objValue.GetLongLength(1);
long lngRowCount = objValue.GetLongLength(0);
DataTable dtExcel = new DataTable();
for (int j = 1; j <= lngColumnCount; j++)
{
dtExcel.Columns.Add(objValue[1, j].ToString());
}
for (int i = 1; i <= lngRowCount; i++)
{
DataRow drRow = dtExcel.NewRow();
for (int j = 1; j <= lngColumnCount; j++)
{
drRow[j - 1] = objValue[i, j].ToString();
}
dtExcel.Rows.Add(drRow);
}
xlsBook.Close();
xlsApp.Quit();
strMsg = "";
return dtExcel;
}
catch (Exception ex)
{
strMsg = ex.Message;
return null;
}
}