C#操作Excel ExcelHelper类(Excel2007)

http://www.cnblogs.com/fellowcheng/archive/2010/08/21/1805158.html 

 

上面没有读取Excel文件的方法，补充一个：

FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

//1. Reading from a binary Excel file ('97-2003 format; *.xls)
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//...
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();

//5. Data Reader methods
while (excelReader.Read())
{
    //excelReader.GetInt32(0);
}

//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();

 图解：

使用OLEDB可以对excel文件进行读取，我们只要把该excel文件作为数据源即可。

一 在D盘创建excel文件test.xls:

 

二 将工作表Sheet1的内容读取到DataSet

 

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/test.xls;"+
    "Extended Properties='Excel 8.0'";
DataSet ds = new DataSet();
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [Sheet1$]", strConn);
oada.Fill(ds);

 

读取的DataSet为：

 

从图中可以看出excel文件中的第一行变成了DataSet中的列名，这正是系统的默认设置。

三 如果想把第一行也作为数据行，那我们可以给连接字符串添加一个HDR=No属性

如：

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/test.xls;"+
    "Extended Properties='Excel 8.0;HDR=No'";
DataSet ds = new DataSet();
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [Sheet1$]", strConn);
oada.Fill(ds);

结果也许会让你有点想不到:

 

 

 

第一行的第一列和第三列都变成空的了，这是因为系统把第一列识别成了数字，把第三列识别成了日期，而第一行的数据不符合格式的要求，所以就变成空的了。

四 我们还可以把所有列都做为字符串来读取，只要添加属性IMEX=1即可

 

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/test.xls;"+
    "Extended Properties='Excel 8.0;HDR=No;IMEX=1'";
DataSet ds = new DataSet();
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [Sheet1$]", strConn);
oada.Fill(ds);

结果又会如何呢？

 

 

 

 

是不是再次出乎你的意料，第三行的日期怎么变成数字了，其实excel在转换格式的时候就自动把日期变成数字了，那这个数字是怎么来的呢 ？ 如果你把日期改成1900年1月1日，那么你可以看到他的转换结果是1，以此类推，39902是哪一天就明白了吧。

五 也许你并不想读取整个excel的内容

如果只想读取前两列可以用：select * from [Sheet1$A:B]

如果只想读取A1到B2的内容，就用：select * from [Sheet1$A1:B2]

六 如果不知道工作表的名字或名字被人为修改了该怎么办呢？

我们可以通过索引来获取指定工作表的名字，以下方法可以用来获取工作表名称的数组：

ArrayList al = new ArrayList();
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/test.xls;"+
    "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable sheetNames = conn.GetOleDbSchemaTable
    (OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
conn.Close();
foreach (DataRow dr in sheetNames.Rows)
{
    al.Add(dr[2]);
}
return al;

 

 

2010年7月7日 修正：

IMEX=1的时候并不是全都会作为字符串来处理，根据系统的默认设置，通常如果前8行有字符串，则该列会作为字符串来处理，如果全都为数字，则该列为数字列，日期也是一样。

如果你觉得8行不够或者太多了，则只能修改注册表HKEY_LOCAL_MACHINE/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows，如果此值为0，则会根据所有行来判断使用什么类型，通常不建议这麽做，除非你的数据量确实比较少。