Microsoft.Office.Interop.Excel 操作excel到DataTable中

来源：互联网发布：软件著作权申请费用: 编辑：程序博客网时间：2024/05/18 00:34

方法如下：

/// <summary>

/// Microsoft.Office.Interop.Excel 操作excel到Datatable中
/// </summary>
public System.Data.DataTable GetExcelTable(string filename)
{
object missing = System.Reflection.Missing.Value;
Application myExcel = new Application();//lauch excel application
if (myExcel == null)
{
return null;
}
else
{
myExcel.Visible = false; myExcel.UserControl = true;
Workbook myBook = myExcel.Application.Workbooks.Open(filename, missing, true, missing, missing, missing,
missing, missing, missing, true, missing, missing, missing, missing, missing);

if (myBook != null) //打开成功
{
myExcel.Visible = false;

Excel.Worksheet mySheet = (Excel.Worksheet)myBook.Worksheets[1]; //得到工作表
System.Data.DataTable dt = new System.Data.DataTable();

for (int j = 1; j <= mySheet.Cells.CurrentRegion.Columns.Count; j++)
dt.Columns.Add();

for (int i = 1; i <= mySheet.Cells.CurrentRegion.Rows.Count; i++) //把工作表导入DataTable中
{
DataRow myRow = dt.NewRow();

for (int j = 1; j <= mySheet.Cells.CurrentRegion.Columns.Count; j++)
{
Excel.Range temp = (Excel.Range)mySheet.Cells[i, j];
string strValue = temp.Text.ToString();
myRow[j - 1] = strValue;
}
dt.Rows.Add(myRow);
}
myExcel.Quit(); //退出Excel文件
myExcel = null;
Process[] procs = Process.GetProcessesByName("excel");
foreach (Process pro in procs)
{
pro.Kill();//杀掉进程
}
GC.Collect();
return dt;
}
}
//打开不成功
return null;
}