如果你新建一个项目的话,首先要添加Microsoft.Office.Core 与Microsoft.Office.Interop.Exce这两个应用,然后就能很方便的操作了,示例代码(只实现了简单的读写):
private Excel._Application excelApp;
private Workbook wbclass;
excelApp = new Excel.Application();
object objOpt = System.Reflection.Missing.Value;
wbclass = (Workbook)excelApp.Workbooks.Open("E:\Book6.xlsx", objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt);
上面声明,引用,并把要操作的 excel 的路径传给他
得到所有的表名:
List<string> list = new List<string>();
Excel.Sheets sheets = wbclass.Worksheets;
string sheetNams = string.Empty;
foreach (Excel.Worksheet sheet in sheets)
{
list.Add(sheet.Name);
}
获取某个表中的数据,这里获取的是sheet 表中的:
public Excel.Worksheet GetWorksheetByName(string name)
{
Excel.Worksheet sheet = null;
Excel.Sheets sheets = wbclass.Worksheets;
foreach (Excel.Worksheet s in sheets)
{
if (s.Name == name)
{
sheet = s;
break;
}
}
return sheet;
}
public System.Data.DataTable GetDateTable(string name)
{
System.Data.DataTable dt = new System.Data.DataTable();
var worksheet = GetWorksheetByName(name); //调用上面的方法,利用表名得到这张表
string cellContent;
int iRowCount = worksheet.UsedRange.Rows.Count;
int iColCount = worksheet.UsedRange.Columns.Count;
Excel.Range range;
for (int iRow = 1; iRow <= iRowCount; iRow++)
{
DataRow dr = dt.NewRow();
for (int iCol = 1; iCol <= iColCount; iCol++)
{
range = (Excel.Range)worksheet.Cells[iRow, iCol];
cellContent = (range.Value2 == null) ? "" : range.Text.ToString();
if (iRow == 1)
{
dt.Columns.Add(cellContent);
}
else
{
dr[iCol - 1] = cellContent;
}
}
if (iRow != 1)
dt.Rows.Add(dr);
}
return dt;
}
上面得到的只是
System.Data.DataTable
如何把数据取出来,请看下面:
var dataTable = GetDateTable("Sheet1"); //调用上面的方法
foreach (DataRow row in dataTable.Rows)
{
string a = (string)row[2];
string b = (string)row[4];
}