论文导读：Excel是目前应用非常广泛的计算、分析信息并管理电子表格的办公软件之一。在管理信息系统的开发中，通常需要在Excel文件和数据库进行数据交换。本文基于.net平台采用C#语言，针对Excel文件与数据库的数据交换存在的问题进行讨论，形成一个比较完善的数据交换方法。
关键词：.net，数据交换，Excel
Excel是目前应用非常广泛的计算、分析信息并管理电子表格的办公软件之一。在管理信息系统的开发中，通常需要在Excel文件和数据库进行数据交换。因此，如何将包含所需数据的Excel文件中的数据导入到管理信息系统的数据库；如何将管理信息系统中存在的数据导出成Excel文件，并控制其格式以满足打印等工作的需要是程序设计必须考虑的问题。
本文基于.net平台采用C#语言，针对Excel文件与数据库的数据交换存在的问题进行讨论，形成一个比较完善的数据交换方法。
1 Excel文件导入DataSet
在.NET中访问读取Excel数据时一般有以下三种方法：（1）采用OleDB读取EXCEL文件；（2）引用的COM组件：Microsoft.Office.Interop.Excel.dll读取EXCEL文件；（3）将EXCEL文件转化成CSV（逗号分隔）的文件，用文件流读取。第二种方法使用COM组件来读取Excel文件数据是直接打开Excel文件，然后逐行读取，运行效率较低，并且在作释放的时候有可能碰到不可预知的错误[1]；而第三种方法需要将Excel文件另存为CSV文件，需要人工干预，不适合自动处理[2]。论文参考网。因此，本文采用第一种方法，即通过OleDB连接，把excel文件作为数据源来读取。
1.1 实现方法
把Excel文件当做一个数据源来进行数据的读取操作，实例如下：
public DataSet ExcelToDS(string Path)
{
string strConn = 'Provider=Microsoft.Jet.OLEDB.4.0;';
strConn += 'DataSource='+ Path + ';';
strConn += 'ExtendedProperties=Excel 8.0;';
OleDbConnection conn = newOleDbConnection(strConn);
conn.Open();
string strExcel = '';
OleDbDataAdapter myCommand =null;
DataSet ds = null;
strExcel='select * from[sheet1$]';
myCommand = new OleDbDataAdapter(strExcel,strConn);
ds = new DataSet();
myCommand.Fill(ds,'table1');
return ds;
}
对于Excel中的表即sheet([sheet1$])如果不是固定的可以使用下面的方法得到
string strConn = 'Provider=Microsoft.Jet.OLEDB.4.0;';
strConn += 'DataSource='+ Path + ';';
strConn += 'ExtendedProperties=Excel 8.0;';
OleDbConnection conn = newOleDbConnection(strConn);
DataTable schemaTable =objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,null);
stringtableName=schemaTable.Rows[0][2].ToString().Trim();
1.2 存在的问题及解决方法
（1）导入数据出现null值的处理方法
Microsoft.Jet.OLEDB.4.0读取数据会出现当某一字段内分别含有文本和数字的混合数据时，某一类型的数据会产生丢失。产生这种问题的根源与Excel ISAM（IndexedSequential Access Method，索引顺序存取方法）驱动程序的限制有关，Excel ISAM 驱动程序通过检查前几行中实际值确定一个 Excel 列的类型，然后选择能够代表其样本中大部分值的数据类型[3]。论文参考网。也即Excel ISAM查找某列前几行（默认情况下是8行），把占多的类型作为其处理类型。例如如果数字占多，那么其它含有字母等文本的数据项就会置空；相反如果文本居多，纯数字的数据项就会被置空。解决这一问题的办法是改造连接字符串，如下所示：
string strConn = 'Provider=Microsoft.Jet.OLEDB.4.0;';
strConn += 'DataSource='+ Path + ';';
strConn += 'ExtendedProperties='Excel 8.0;HDR=Yes;IMEX=1'';
程序代码ExtendedProperties项中的HDR和IMEX所代表的含义。HDR用来设置是否将Excel表中第一行作为字段名，“YES”代表是，“NO”代表不是即也为数据内容；IMEX是用来告诉驱动程序使用Excel文件的模式，其值有0、1、2三种，分别代表导出、导入、混合模式。通过IMEX=1来把混合型作为文本型读取，避免null值。
（2）Excel2007 文件读取方法
Excel 2007文件（.xlsx文件）是一种不带宏的Office Open XML格式文件。当读取.xlsx文件时，需要修改上述连接字符串，修改后的连接字符串如下：
string strConn +='Provider=Microsoft.ACE.OLEDB.12.0;';
strConn += 'DataSource='+ Path + ';';
strConn += 'ExtendedProperties='Excel 12.0 Xml;HDR=Yes;IMEX=1'';
2 DataSet导出 Excel文件
2.1 实现方法
public void DSToExcel(string Path,DataSetoldds)
{
string strConn = 'Provider=Microsoft.Jet.OLEDB.4.0;';
strConn += 'DataSource='+ Path + ';';
strConn += 'ExtendedProperties=Excel 8.0; ';
OleDbConnection myConn = newOleDbConnection(strConn) ;
string strCom='select *from [Sheet1$]';
myConn.Open ( ) ;
OleDbDataAdapter myCommand =new OleDbDataAdapter ( strCom, myConn ) ;
System.Data.OleDb.OleDbCommandBuilderbuilder=new OleDbCommandBuilder(myCommand);
//QuotePrefix和QuoteSuffix主要是对builder生成InsertComment命令时使用。论文参考网。
builder.QuotePrefix='[';
//获取insert语句中保留字符（起始位置）
builder.QuoteSuffix=']';
//获取insert语句中保留字符（结束位置）
DataSet newds=new DataSet();
myCommand.Fill(newds,'Table1') ;
for(inti=0;i<oldds.Tables[0].Rows.Count;i++)
{
DataRow nrow= newds.Tables['Table1'].NewRow();
for(intj=0;j<newds.Tables[0].Columns.Count;j++)
{
nrow[j]=oldds.Tables[0].Rows[i][j];
}
newds.Tables['Table1'].Rows.Add(nrow);
}
myCommand.Update(newds,'Table1');
myConn.Close();
}
在以上程序中必须使用将DataSet中的数据做循环后写入OleDb数据源中，不能使用ImportRow方法将一行导入到news中，因为ImportRow将保留原来DataRow的所有设置(DataRowState状态不变)。在使用ImportRow后，所有导入行的DataRowState!=Added，数据将不能更新到Excel中
3 结语
在应用程序的设计中，需要访问Excel数据的情况非常普遍，本文以在.NET中对访问Excel表格拟采取的方法进行探讨，给出了比较完善的解决方法，对可能出现的问题进行了分析并提出处理方法。对于不是使用.NET开发的情况，本论文的分析和所提供的方法亦可参考。
参考文献：
[1] 刘玉敏等,基于文本格式的Excel数据导入,科学技术与工程[J],2010年1期
[2] 刘柏峰,陈伟,陈晓军,C#中操纵Excel的几种方法,微型电脑应用[J],2006年11期
[3] PBR.Excel使用DAOOpenRecordset NULL作为返回值[EB/OL]. http://support.microsoft.com/kb/194124 , 2004-6-24
|
