asp.net 自编的Excel导入功能小例
最近接的工作需要做Excel文件导入数据库的功能，当时未提供文件格式，于是有了后来那点事。
思想：通过XML文件来保存EXCEL文件的格式、字段，并与数据库中字段对应。这样EXCEL变动时程序中只调整xml文件就可以实现匹配。
整个内容分三部分：1.通过XML文件来保存EXCEL文件的格式、字段，并与数据库中字段对应，
2.读取EXCEL文件名及页标签，
3.执行导入。
XML文件:
<?xml version="1.0" encoding="utf-8" ?>
<ExcelFiles>
<ExcelFile SQLname="数据库中表名" fileName="EXCEL文件名" >
<columns>
<!--<column SQLname="数据库中字段名" fileName="EXCEL中字段名" OracleDbType="Oracle数据库中该字段类型" ></column> -->
<column SQLname="a" fileName="表字段A" OracleDbType="VARCHAR2"></column>
<column SQLname="b" fileName="表字段B " OracleDbType="VARCHAR2"></column>
<!-- 注意这里用到了双结点及结点下含子节点 -->
<columns SQLname="c,d" fileName="表字段C,表字段D " OracleDbType="VARCHAR2">
<column SQLname="e" fileName="表字段E" OracleDbType="VARCHAR2"></column>
<column SQLname="f" fileName="表字段F " OracleDbType="VARCHAR2"></column>
</columns>
<column SQLname="g" fileName="G" OracleDbType="VARCHAR2"></column>
对应EXCEL表格形式：
| A | B | C,D | G | |
| E | F | |||
| 数据 | 数据 | 数据 | 数据 | 数据 |
| 数据 | 数据 | 数据 | 数据 | 数据 |
| 数据 | 数据 | 数据 | 数据 | 数据 |
| 数据 | 数据 | 数据 | 数据 | 数据 |
| 数据 | 数据 | 数据 | 数据 | 数据 |
| 数据 | 数据 | 数据 | 数据 | 数据 |
/// <summary>
/// 查询xml文件找到EXCEL文件的对应项。
/// </summary>
/// <param name="sqlName">对应SQL表名</param>
/// <param name="columnlist">EXCEL文件的列名及对应SQL表字段名</param>
/// <param name="fileName">EXCEL文件的文件名</param>
public void getSQLName(out string sqlName, out XmlNodeList columnList, string fileName)
{
System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
xmlDoc.Load(Server.MapPath("XMLFile.xml"));
XmlNodeList list = xmlDoc.SelectSingleNode("ExcelFiles").ChildNodes;
sqlName = string.Empty;
columnList = null;
foreach (XmlNode node in list)
{
if (fileName.Contains(node.Attributes["fileName"].Value.ToString()) && node.Attributes["fileName"].Value.ToString()!=string.Empty )
{
sqlName = node.Attributes["SQLname"].Value.ToString();
columnList = node.SelectSingleNode("columns").ChildNodes;
//这块代码应该写成一个递归循环查看子节点下是否还有子节点，我这里没改，要用多层循环的自己改下咯。
}
}
if (sqlName == string.Empty)
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "", "alert('没有找到同类型的EXCEL文件模板，请检查上传的文件名是否符号要求！');", true);
return;
}
}
2.
/// <summary>
/// 获得Excel中的第一个下标：sheetname。方法是先返回Excel的架构信息，再从架构中读取下标。
/// </summary>
/// <param name="filepath">服务器中的文件的物理路径</param>
/// <returns></returns>
public void ExcelSheetName( out string sheetname,string filepath )
{
sheetname = string.Empty;
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable sheetNames = conn.GetOleDbSchemaTable
(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
conn.Close();
foreach (DataRow dr in sheetNames.Rows)
{
sheetname = dr[2].ToString();
}
}
3.
/// <summary>
/// 将上传excel写入数据库相应表中
/// </summary>
public void SqlBulkUpload()//问题还未解决：1.绝对物理路径path。
{
try
{
string filename = FileUpload1.FileName;//.PostedFile.FileName.Substring(FileUpload1.PostedFile.FileName.LastIndexOf("\\"));
string sqlName = string.Empty;
XmlNodeList xmlList = null;
//在上传文件前先确定上传文件名称格式是否正确。如果有误，直接退出；如正确则获取对应sql表及字段名。
getSQLName(out sqlName, out xmlList, filename);
//上传文件到服务器指定文件夹。（用重名会覆盖，看后期是否再修改。）
string serverPath = Server.MapPath("uploadFiles\\") + filename;
FileUpload1.PostedFile.SaveAs(serverPath);
//获取EXCEL中第1个页标签。
string sheetname = string.Empty;
ExcelSheetName(out sheetname, serverPath);
//读取服务器中的此文件到datatable中。并在datatable中加入一列，用于生成id。
string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ serverPath
+ ";Extended Properties=Excel 8.0";
OleDbConnection conn = new OleDbConnection(connstr);
string cmdText = "SELECT * FROM [" + sheetname + "]";
OleDbCommand cmd = new OleDbCommand(cmdText, conn);
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
//这个地方来判断表的结构
conn.Close();
// 获取服务器连接数据库
string ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ORACLEConnectionStrings"].ConnectionString;
string ProviderName = System.Configuration.ConfigurationManager.ConnectionStrings["ORACLEConnectionStrings"].ProviderName.ToString();
//针对SQL数据库
if (ProviderName.Contains("SqlClient"))
{
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["id"] = Guid.NewGuid();
}
//以xml中的对应字段为格式模板，拷贝datatable中的数据到数据库。
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ConnectionString))
{
foreach (XmlNode nod in xmlList)
{
bulkCopy.ColumnMappings.Add(nod.Attributes["fileName"].Value.ToString(), nod.Attributes["SQLname"].Value.ToString());
}
bulkCopy.DestinationTableName = sqlName;
bulkCopy.WriteToServer(dt);
}
}
//针对Oracle数据库
else if (ProviderName.Contains("OracleClient"))
{
//这里因为客户端的版本10.2g无法支持OracleBulkCopy方法最终被放弃，必须11g及以上版本才能支持。
//可以用 bulk insert 的方法来处理。原理和.net提供的OracleBulkCopy方法原理相同，但因为是oracle自带函数，不需要版本支持。
//OracleBulkCopy bulkCopy = new OracleBulkCopy(ConnectionString);
//foreach (XmlNode nod in xmlList)
//{
// bulkCopy.ColumnMappings.Add(nod.Attributes["fileName"].Value.ToString(), nod.Attributes["SQLname"].Value.ToString());
//}
//bulkCopy.DestinationTableName = sqlName;
//bulkCopy.WriteToServer(dt);
System.Data.OracleClient.OracleConnection oracleConn = new System.Data.OracleClient.OracleConnection(ConnectionString);
Hashtable htable = new Hashtable();
oracleConn.Open();
string cmdTxt = "insert into " + sqlName + " (";
foreach (XmlNode nod in xmlList)
{
cmdTxt += nod.Attributes["SQLname"].Value.ToString() + ",";
}
cmdTxt = cmdTxt.Remove(cmdTxt.LastIndexOf(",")) + ")";
for (int i = 0; i < dt.Rows.Count; i++)
{
System.Data.OracleClient.OracleCommand oracleCmd = new System.Data.OracleClient.OracleCommand();
oracleCmd.Connection = oracleConn;
oracleCmd.CommandText = cmdTxt;
oracleCmd.CommandText += " values(";
for( int j=0;j<dt.Columns.Count;j++ )
{
if (xmlList[j].Attributes["OracleDbType"].Value == "VARCHAR2" )
{
oracleCmd.CommandText += "'";
oracleCmd.CommandText += dt.Rows[i][j].ToString() + "',";
}
else if (xmlList[j].Attributes["OracleDbType"].Value == "DATE")
{
oracleCmd.CommandText += "to_date('" + dt.Rows[i][j].ToString() + "','yyyy-mm-dd hh24:mi:ss'),";
}
else
{
oracleCmd.CommandText += dt.Rows[i][j].ToString() + ",";
}
}
oracleCmd.CommandText=oracleCmd.CommandText.Substring(0, oracleCmd.CommandText.Length - 1);
oracleCmd.CommandText += ")";
int mark = oracleCmd.ExecuteNonQuery();
oracleCmd.Dispose();
if (mark <= 0)//记录导入Oracle失败的行。
{
htable.Add((i+1).ToString(), dt.Rows[i][0]);
}
}
oracleConn.Close();
if (htable.Count > 0)
{
string errorRow = string.Empty;
foreach ( System.Collections.DictionaryEntry objEntry in htable)
{
errorRow += "第 " + objEntry.Key.ToString() +" 行数据故障，第一列数据为：" + objEntry.Value.ToString()+"；";
}
errorRow += "请检查相应EXCEL文件进行修改。";
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "", "alert('"+errorRow+"');", true);
}
}
else
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "", "alert('选择的数据库不是SQL或ORACLE数据库！~');", true);
return;
}
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "", "alert('文件上传成功！~');", true);
}
catch(Exception ex)
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "", "alert('"+ex.ToString()+"！');", true);//文件上传失败
return;
}
}
