excel数据导入到sqlserver中---------工作笔记

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using JuMi.BLL.Business;

using System.Data;

using System.Data.OleDb;

using System.Windows.Forms;

using JuMi.Model.Business;

using JuMi.BLL.Business;

namespace JuMi.Web.Form.Manager.Business

{

    public partial class WebForm1 : JuMi.Web.UI.BasePage

    {

        protected void Page_Load(object sender, EventArgs e)

        {

        }

        /// <summary>

        /// 导入广告商信息

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        protected void btnAdverInfo_Click(object sender, EventArgs e)

        {

            DataTable dt = ExcelDemo.GetExcelToDatatable("F:\\Demo\\excelDemo.xlsx", "table");

            int count = 0;

            for (int i = 0; i < dt.Rows.Count; i++)

            {

                Model_Advertiser model = new Model_Advertiser();

                model.Adver_CompanyName = dt.Rows[i][0].ToString();//名称

                model.Adver_Distinguish = dt.Rows[i][1].ToString();//税号

                model.Adver_RegisPhone = dt.Rows[i][2].ToString();//电话

                model.Adver_BankNumber = dt.Rows[i][3].ToString();//银行账号

                model.Adver_BankAddress = dt.Rows[i][4].ToString();//开户行地址

                model.Adver_RegisAdress = dt.Rows[i][5].ToString();//注册地址

                model.Adver_State = 0;

                if (AdvertiserBLL.Add(model) > 0)

                {

                    count++;

                }

            }

            if (count >= dt.Rows.Count)

            {

                JscriptMsgSuccess("广告商信息导入成功！", "", "function(){api.reload();}");

            }

            else

            {

                JscriptMsgError("广告商信息导入失败！", "", "function(){api.reload();}");

            }

        }

    }

}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using System.Data.OleDb;

namespace JuMi.BLL.Business

{

    public class ExcelDemo

    {

        /// <summary>

        /// Excel数据导入Datable

        /// </summary>

        /// <param name="fileUrl">文件路径</param>

        /// <param name="table"></param>

        /// <returns></returns>

        public static DataTable GetExcelToDatatable(string fileUrl, string table)

        {

            //支持office2007之前的 .xls

            //const string cmdText = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1';";//链接字符串

            //支持.xls和.xlsx，即包括office2010等版本的   HDR=Yes代表第一行是标题，不是数据；

            const string cmdText = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";

            DataTable dt = null;

            //建立连接

            OleDbConnection conn = new OleDbConnection(string.Format(cmdText, fileUrl));

            try

            {

                //打开链接

                if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)

                {

                    conn.Open();

                }

                DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                //获取excel的第一个sheet名称

                string sheetName1 = schemaTable.Rows[]["TABLE_NAME"].ToString().Trim();

                //查询sheet数据

                string strSql1 = "select * from [" + sheetName1 + "]";

                OleDbDataAdapter da = new OleDbDataAdapter(strSql1, conn);

                DataSet ds = new DataSet(); da.Fill(ds, table);

                dt = ds.Tables["" + table + ""];

                return dt;

            }

            catch (Exception exc)

            {

                throw exc;

            }

            finally

            {

                conn.Close();

                conn.Dispose();

            }

        }

    }

}