public static DataSet ReadExcel2003(string excelPath)
{
DataSet ds = new DataSet();
//string workName = "";
Microsoft.Office.Interop.Excel.Application xApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
xApp.Visible = false;
//得到WorkBook对象, 可以用两种方式之一: 下面的是打开已有的文件
Microsoft.Office.Interop.Excel.Workbook xBook = xApp.Workbooks._Open(excelPath,
Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//xBook=xApp.Workbooks.Add(Missing.Value);//新建文件的代码
//指定要操作的Sheet，两种方式：
Microsoft.Office.Interop.Excel.Sheets xSheets = xBook.Sheets;
for (int k = 1; k <= xSheets.Count; k++)
{
Microsoft.Office.Interop.Excel.Worksheet xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xSheets[k];
//查找列数
int rowCount = 0;
for (int i = 1; i < xSheet.Rows.Count; i++)
{
if (((Microsoft.Office.Interop.Excel.Range)xSheet.Cells[i, 1]).Value2 == null)
{
rowCount = i;
break;
}
}
//查找行数
int columnCount = 0;
for (int i = 1; i < xSheet.Columns.Count; i++)
{
if (((Microsoft.Office.Interop.Excel.Range)xSheet.Cells[2, i]).Value2 == null)
{
columnCount = i;
break;
}
}
DataTable dt = new DataTable();
for (int i = 1; i < columnCount; i++)
{
string ss = ((Microsoft.Office.Interop.Excel.Range)xSheet.Cells[1, i]).Value2.ToString();
if (ss != "")
{
dt.Columns.Add(ss);
}
else
{
dt.Columns.Add("Column" + i);
}
}
for (int i = 2; i < rowCount; i++)
{
DataRow dr = dt.NewRow();
for (int j = 1; j < columnCount; j++)
{
dr[j - 1] = ((Microsoft.Office.Interop.Excel.Range)xSheet.Cells[i, j]).Value2 == null ? "" : ((Microsoft.Office.Interop.Excel.Range)xSheet.Cells[i, j]).Value2.ToString();
}
dt.Rows.Add(dr);
}
ds.Tables.Add(dt);
}
try
{
xBook.Save();
//Microsoft.Office.Interop.Excel.Worksheet xSheet2=(Microsoft.Office.Interop.Excel.Worksheet)xApp.ActiveSheet;
xBook.Close(false, false, Missing.Value);
xApp.Quit();
}
catch { }
IntPtr t = new IntPtr(xApp.Hwnd);
int m = 0;
GetWindowThreadProcessId(t, out m);
Process p = Process.GetProcessById(m);
p.Kill();
return ds;
}
这个可以读取多个excel的工作簿返回dataset
