/// <summary>
/// 根据Excel转换成DataTable
/// </summary>
/// <param name="FileName">文件名称</param>
/// <returns></returns>
private System.Data.DataTable getTableByExecl(string FileName)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook;
Microsoft.Office.Interop.Excel.Worksheet worksheet;
object oMissing = System.Reflection.Missing.Value;//相当null
workbook = excel.Workbooks.Open(FileName, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
worksheet = (Worksheet)workbook.Worksheets[1];
int rowCount = worksheet.UsedRange.Rows.Count;
int colCount = worksheet.UsedRange.Columns.Count;
Microsoft.Office.Interop.Excel.Range range1;
System.Data.DataTable dt = new System.Data.DataTable();
for (int i = 0; i < colCount; i++)
{
range1 = worksheet.get_Range(worksheet.Cells[1, i + 1], worksheet.Cells[1, i + 1]);
if (range1.Value2 == null)
{
dt.Columns.Add("");
continue;
}
dt.Columns.Add(range1.Value2.ToString());
}
for (int j = 0; j < rowCount; j++)
{
DataRow dr = dt.NewRow();
for (int i = 0; i < colCount; i++)
{
range1 = worksheet.get_Range(worksheet.Cells[j + 1, i + 1], worksheet.Cells[j + 1, i + 1]);
dr[i] = range1.Value2 == null ? "" : range1.Value2.ToString();
}
dt.Rows.Add(dr);
}
excel.Quit();
return dt;
}