C#读取Excel数据与VB很不一样,VB是直接从Excel单元格中读取,如:Trim(xlworksheet.Cells(2, i).Value),C#是先把所有数据读出成一个表,然后再对该表做相应操作。。。
上周需新写一个程序,之前有类似的VB代码,于是想用C#写,导入Excel竟然成了一个障碍,上网找了找,一时半会还没解决,由于时间比较紧急,先放一边了,周末在家闲的没事,把问题给解决了。。。
C#读取Excel数据与VB很不一样,VB是直接从Excel单元格中读取,如:Trim(xlworksheet.Cells(2, i).Value),C#是先把所有数据读出成一个表,然后再对该表做相应操作。。。
根据Excel文件名(完整路径)读取SheetName:

/**//// <summary>
/// Get SheetName from Excel file and put it into data table
/// </summary>
/// <param name="FileName">Filename with its absolute path</param>
/// <returns>Data table</returns>
public DataTable GetSheetFromExcel(string FileName)


{
Missing Miss = Missing.Value;

DataTable dtSheet = new DataTable();
//Excel.Application xlApp
this.excel = new Excel.ApplicationClass();
//Excel.Workbook xlBook
this.workBook = excel.Workbooks.Open(FileName, Miss, Miss, Miss, Miss, Miss, Miss, Miss, Miss, Miss, Miss, Miss, Miss, Miss, Miss);

dtSheet.Columns.Add("SheetName");

for (int i = 1; i <= workBook.Sheets.Count; i++)

{
dtSheet.Rows.Add(((Excel.Worksheet)workBook.Worksheets[i]).Name.ToString());
}

Quit();
this.workBook = null;
this.excel = null;
return dtSheet;
}

通过Excel文件名和SheetName读取Excel数据,返回一个表:

/**//// <summary>
/// Get data from Excel file and put it into data table by SheetName
/// </summary>
/// <param name="FileName"></param>
/// <param name="SheetName"></param>
/// <returns>DataTable</returns>
public DataTable ExcelData(string FileName, string SheetName)


{
DataSet dsExcel = new DataSet();
DataTable DT = new DataTable();

string strXls = FileName.Substring(FileName.LastIndexOf(".") + 1).ToLower();
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=Excel 8.0";
OleDbConnection oleConn = new OleDbConnection(strConn);
oleConn.Open();
OleDbCommand myOleDbCommand = new OleDbCommand("select * from [" + SheetName + "$]", oleConn);
OleDbDataAdapter dataAda = new OleDbDataAdapter(myOleDbCommand);
oleConn.Close();
dsExcel = new DataSet();
dataAda.Fill(dsExcel, "[" + SheetName + "$]");
DT = dsExcel.Tables[0];

return DT;
}
退出Excel:

/**//// <summary>
/// Quit excel file
/// </summary>
public void Quit()


{
this.excel.Quit();
}