Posted on 2008-07-28 19:11
毛必盛 阅读(163)
评论(2) 编辑 收藏 网摘 所属分类:
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();
}