asp.net Excel导入数据到sql

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.OleDb;

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        DataTable dt = ExcelToDataTable("/ceshi.xls", "Sheet1");
        int mark = 286000;
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            string Area = dt.Rows[i]["区域"].ToString();
            int TypeId = 12;
            string Title = dt.Rows[i]["工程名"].ToString();
            string Model = dt.Rows[i]["型号"].ToString();
            string Norms = dt.Rows[i]["规格"].ToString();
            string Materials = dt.Rows[i]["表面材料"].ToString();
            string Number = dt.Rows[i]["数量"].ToString();
            string Contents = dt.Rows[i]["备注"].ToString();

            int SN = mark + 1000;
            mark = mark + 1000;
            string sql = string.Format("insert into T_GongChengYeJi(Area,TypeId,Title,Model,Norms,Materials,Number,Contents,SN) values('{0}',{1},'{2}','{3}','{4}','{5}','{6}','{7}',{8})"
                , Area, TypeId, Title, Model, Norms, Materials, Number, Contents, SN);

            DBHelper.IDUCommand(sql);
        }
        WebUI.Alert("导入完成！", this.Page);
    }

    public static DataTable ExcelToDataTable(string strExcelFileName, string strSheetName)
    {

        //HDR和IMEX也一定要配合使用,IMEX=1应该是将所有的列全部视为文本,HDR:YES第一行作为列名，NO表示第一行不为列名

        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + urlconvertorlocal(strExcelFileName) + ";" + "Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";

        string strExcel = string.Format("select * from [{0}$]", strSheetName); //这是一种方法

        //string strExcel = "select * from [sheet1$]";

        DataSet ds = new DataSet();

        OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);

        adapter.Fill(ds, strSheetName);

        return ds.Tables[strSheetName];

    }

    /// <summary>
    /// 转换为绝对路径
    /// </summary>
    /// <param name="imagesurl1"></param>
    /// <returns></returns>
    private static string urlconvertorlocal(string imagesurl1)
    {

        string tmpRootDir = System.Web.HttpContext.Current.Server.MapPath(System.Web.HttpContext.Current.Request.ApplicationPath.ToString());//获取程序根目录

        string imagesurl2 = tmpRootDir + imagesurl1.Replace(@"/", @"\"); //转换成绝对路径

        return imagesurl2;

    }
}