asp.net 将Excel导入到Sql2005或2000

财务的小姑娘一直嚷嚷着要我给做个从网页上导入到数据库的工具,终于心一软答应她了.

下面叙述一下我asp.net 将Excel导入到Sql2005或2000的思路和步骤：

1、将Excel文件上传到服务器端

这个我不想详细讲了,网上一搜一大把的.

注意：（1在取服务器路径时一定要用this.Page.MapPath(".")而不要用 this.Page.Request.ApplicationPath 或this.Page.Request.Path;因为后两个在调试的时候可能不报错,但在发布时会报错。

（2 站点根目录要给Asp.net用户写的权限。否则文件上传不上去的，报拒绝访问.

2、将服务器端的EXcel数据读入内存

private static DataTable getXslTable(string XlsFileName)//从excel中读取数据,并放到DataTable中
{
DataSet ZDDataSet = new DataSet();
System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.jet.OLEDB.4.0;Data Source=" + XlsFileName + ";Extended Properties=Excel 8.0");//这个更据不同的Excel版本不一样

//XlsFileName 为Excel的路径
objConn.Open();
//DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
// String tableName = schemaTable.Rows[0][2].ToString().Trim();
string SqlStr = "Select * from [CPCMLFXB$]";//CPCMLFXB是要读的Sheet的名字
System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(SqlStr, objConn);
System.Data.OleDb.OleDbDataAdapter DAP = new System.Data.OleDb.OleDbDataAdapter(command);
DAP.Fill(ZDDataSet, "CPCMLFXB");
objConn.Close();
return ZDDataSet.Tables["CPCMLFXB"];
}

这个不也不难是ba

3、将读入内存的数据按一个写好的xml解析并放入DataSet中

为什么要写成XML,好处这个解析程序将可以公用,只要配置不同的Xml就可以了，XML全文如下：

<?xml version="1.0" encoding="utf-8" ?>
<!--用于导入产品处毛利分析表的,导入的表名Table, FieldName数据表的字段,xslFieldName xsl中的字段名 IsEnCode=1表示需要简繁转换否则不需要,EnLarge 放大倍数-->
<ImportSetting>
<Table>SCD_CPCMLFXLR</Table>
<Fields>
<Field FieldName="HTH" xslFieldName="合同號碼" IsEnCode="1" EnLarge="1" ISNULL="1"></Field>
<Field FieldName="GCMC" xslFieldName="工程名稱" IsEnCode="1" EnLarge="1" ISNULL="1"></Field>
<Field FieldName="HTJE" xslFieldName="合同金额" IsEnCode="0" EnLarge="1" ISNULL="0"></Field>
<Field FieldName="HTSL" xslFieldName="稅率" IsEnCode="0" EnLarge="100" ISNULL="1"></Field>
<Field FieldName="ZSBCB" xslFieldName="材料成本-主设备" IsEnCode="0" EnLarge="1" ISNULL="0"></Field>
<Field FieldName="FZCLCB" xslFieldName="材料成本-辅助材料" IsEnCode="0" EnLarge="1" ISNULL="0"></Field>
<Field FieldName="YSFY" xslFieldName="施工费用-运输费用" IsEnCode="0" EnLarge="1" ISNULL="1"></Field>
<Field FieldName="XCFY" xslFieldName="施工费用-现场费用" IsEnCode="0" EnLarge="1" ISNULL="1"></Field>
<Field FieldName="WBFY" xslFieldName="施工费用-外包费用" IsEnCode="0" EnLarge="1" ISNULL="1"></Field>
<Field FieldName="RLFY" xslFieldName="施工费用-人力费用" IsEnCode="0" EnLarge="1" ISNULL="1"></Field>
<Field FieldName="WHFYBL" xslFieldName="維護費計提比例" IsEnCode="0" EnLarge="100" ISNULL="1"></Field>
<Field FieldName="QTCB" xslFieldName="其它" IsEnCode="0" EnLarge="1" ISNULL="1"></Field>
<Field FieldName="XYFY_PXFY" xslFieldName="协议费用-培训费用" IsEnCode="0" EnLarge="1" ISNULL="1"></Field>
<Field FieldName="XYFY_DLFY" xslFieldName="协议费用-代理费用" IsEnCode="0" EnLarge="1" ISNULL="1"></Field>
<Field FieldName="XYFY_CLCBFY" xslFieldName="协议费用-材料设备" IsEnCode="0" EnLarge="1" ISNULL="1"></Field>
<Field FieldName="XYFY_GCAZFY" xslFieldName="协议费用-工程安装" IsEnCode="0" EnLarge="1" ISNULL="1"></Field>
<Field FieldName="CPLX" xslFieldName="產品" IsEnCode="1" EnLarge="1" ISNULL="1"></Field>
</Fields>
</ImportSetting>

其实完全可以将Sheet的名称也放进来,这样sheet的名称也可以配置了.关于XML还可以做更多的选项以适应不同的需求.

4、将DataSet更新到Sql
private static string InsertSql(string XMLFileName, string Connstr, DataTable xlsTable)//将从Excel导出的数据导入到Sql2005 字段对应及目标表由XMLFileName决定Connstr为数据库连接字符串,其实也可以放在XML中，因为我这个在程序中可以获取到所以就传进来了 XMLFileName 为上面的XML的存储路径 DataTable xlsTable 为第二步得到的DataTable {
XmlDocument xmldoc = new XmlDocument();
xmldoc.Load(XMLFileName);
string strTableName = xmldoc.GetElementsByTagName("Table")[0].InnerText;
XmlNodeList fieldNodes = xmldoc.GetElementsByTagName("Field");
if (fieldNodes.Count == 0)
{
return "配置文件可能问题,没有字段！";
}
//至此xml已经准备好了

SqlConnection ZDConnection = new SqlConnection(Connstr);
string strSql = "Select";
for (int i = 0; i < fieldNodes.Count; i++)
{
strSql += " " + fieldNodes[i].Attributes["FieldName"].Value+",";
}
strSql += "BS from " + strTableName+" where BS=-999";//这样使得查出来的记录为空,查询的Sql

DataSet sqlDataSet = new DataSet();
SqlCommand cmdSelect = new SqlCommand(strSql, ZDConnection);
SqlDataAdapter Sda = new SqlDataAdapter(cmdSelect);
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(Sda);
Sda.Fill(sqlDataSet, strTableName);

//数据转存到strTableName
for (int i = 0; i < xlsTable.Rows.Count; i++)
{
DataRow NewRow=sqlDataSet.Tables[strTableName].NewRow();
for (int j = 0; j < fieldNodes.Count; j++)
{

// NewRow["" + fieldNodes[j].Attributes["FieldName"].Value] = ""+xlsTable.Rows[i]["" + fieldNodes[j].Attributes["xslFieldName"].Value];
EncodeRobert edControl = new EncodeRobert();
string strtemp = "";
if ("" + fieldNodes[j].Attributes["IsEnCode"].Value == "1")
{

try
{
strtemp = "" + xlsTable.Rows[i]["" + fieldNodes[j].Attributes["xslFieldName"].Value];
NewRow["" + fieldNodes[j].Attributes["FieldName"].Value] = strtemp;
NewRow["" + fieldNodes[j].Attributes["FieldName"].Value] = edControl.SCTCConvert(ConvertType.Simplified, ConvertType.Traditional, "" + NewRow["" + fieldNodes[j].Attributes["FieldName"].Value]);
}
catch (Exception rowException)
{
return "Excel格式不正確!<br>" + rowException.Message + ":<br>" + rowException.StackTrace;
}
}
else
{
try
{
strtemp = "" + xlsTable.Rows[i]["" + fieldNodes[j].Attributes["xslFieldName"].Value];
NewRow["" + fieldNodes[j].Attributes["FieldName"].Value] = "" + strtemp == "" ? "0" : "" + xlsTable.Rows[i]["" + fieldNodes[j].Attributes["xslFieldName"].Value];
NewRow["" + fieldNodes[j].Attributes["FieldName"].Value] = float.Parse("" + NewRow["" + fieldNodes[j].Attributes["FieldName"].Value]) * float.Parse("" + fieldNodes[j].Attributes["EnLarge"].Value);
if (fieldNodes[j].Attributes["ISNULL"].Value == "0")
{
if (float.Parse("" + NewRow["" + fieldNodes[j].Attributes["FieldName"].Value]) < 0.01)
NewRow["" + fieldNodes[j].Attributes["FieldName"].Value] = 0.01;
}
}
catch (Exception rowException)
{
return "Excel格式不正確!<br>" + rowException.Message + ":<br>" + rowException.StackTrace;
}
}
}
NewRow["BS"] = 1;
sqlDataSet.Tables[strTableName].Rows.Add(NewRow);
}
//更新数据
try
{
int n= Sda.Update(sqlDataSet, strTableName);
return "導入成功,共導入數據"+n+"條.";
}
catch (Exception updateException)
{
return "Excel格式不正確!<br>" + updateException.Message + ":<br>" + updateException.StackTrace;
}
}

恭喜你,至此asp.net 将Excel导入到Sql2005或2000已经完成了.

水平有限,请广大博友指教啊.