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已经完成了.

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