[asp.net] 把excel数据导入access数据库表中

前台界面：

<form id="form1" runat="server">
    <div>
    <table class="Text" cellSpacing="1" cellPadding="0" width="100%" bgColor="#1d82d0" border="0">
  <tr bgColor="#ffffff">
    <td vAlign="top">
      <table class="Text" cellSpacing="0" cellPadding="0" width="100%" border="0">
        <tr>
          <td width="15">&nbsp;</td>
          <td vAlign="top" width="100%">
            <table class="Text" cellSpacing="1" cellPadding="0" width="100%" border="0">
              <tr height="30">
                <td style="WIDTH: 120px" width="120"><FONT face="宋体">请选择要导入的文件</FONT></td>
                <td style="WIDTH: 350px" align="left" width="350"><INPUT id="FileExcel" style="WIDTH: 300px" type="file" size="42" name="FilePhoto" runat="server"><FONT color="red"></FONT></td>
                <td class="hint"><FONT face="宋体"><asp:button id="BtnImport" Text="导 入" CssClass="button" Runat="server"></asp:button></FONT></td>
              </tr>
            </table>
          </td>
        </tr>
      </table>
      <asp:label id="LblMessage" runat="server" Font-Bold="True" ForeColor="Red"></asp:label>
    </td>
  </tr>
</table>
    </div>
    </form>

后台代码(aspx.cs)：

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Text;
using System.Configuration;
using DBUtility;
public partial class cncncadmin_test1 : System.Web.UI.Page
{
   //protected System.Web.UI.HtmlControls.HtmlInputFile FileExcel;
   //protected System.Web.UI.WebControls.Button BtnImport;
   //protected System.Web.UI.WebControls.Label LblMessage;
    DBUtility.SqlHelper sql1 = new SqlHelper();
   protected void Page_Load(object sender, EventArgs e)
    {

    }
    #region Web 窗体设计器生成的代码
    override protected void OnInit(EventArgs e)
    {
        //
        // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
        //
        InitializeComponent();
        base.OnInit(e);
    }

    /// <summary>
    /// 设计器支持所需的方法 - 不要使用代码编辑器修改
    /// 此方法的内容。
    /// </summary>
    private void InitializeComponent()
    {
        this.BtnImport.Click += new System.EventHandler(this.BtnImport_Click);
        this.Load += new System.EventHandler(this.Page_Load);

    }
    #endregion


    //// <summary>
    /// 从Excel提取数据--》Dataset
    /// </summary>
    /// <param name="filename">Excel文件路径名</param>
    private void ImportXlsToData(string fileName)
    {
        try
        {
            if (fileName == string.Empty)
            {
                throw new ArgumentNullException("Excel文件上传失败！");
            }
            string oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + fileName + "; Extended Properties='Excel 8.0;'";
            OleDbConnection oleDBConn = null;
            OleDbDataAdapter oleAdMaster = null;
            DataTable m_tableName = new DataTable();
            DataSet ds = new DataSet();
            oleDBConn = new OleDbConnection(oleDBConnString);
            oleDBConn.Open();
            m_tableName = oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (m_tableName != null && m_tableName.Rows.Count > 0)
            {
                m_tableName.TableName = m_tableName.Rows[0]["TABLE_NAME"].ToString();
             }
            string sqlMaster;
            sqlMaster = " SELECT *  FROM [" + m_tableName.TableName + "]";
            oleAdMaster = new OleDbDataAdapter(sqlMaster, oleDBConn);
            oleAdMaster.Fill(ds, "m_tableName");
            oleAdMaster.Dispose();
           // oleDBConn.Close();
            oleDBConn.Dispose();
             AddDatasetToSQL(ds, 5);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    /// <summary>
    /// 上传Excel文件
    /// </summary>
    /// <param name="inputfile">上传的控件名</param>
    /// <returns></returns>
    private string UpLoadXls(System.Web.UI.HtmlControls.HtmlInputFile inputfile)
    {
        string orifilename = string.Empty;
        string uploadfilepath = string.Empty;
        string modifyfilename = string.Empty;
        string fileExtend = "";//文件扩展名
        int fileSize = 0;//文件大小
        try
        {
            if (inputfile.Value != string.Empty)
            {
                //得到文件的大小
                fileSize = inputfile.PostedFile.ContentLength;
                if (fileSize == 0)
                {
                    throw new Exception("导入的Excel文件大小为0，请检查是否正确！");
                }
                //得到扩展名
                fileExtend = inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1);
                if (fileExtend.ToLower() != "xls")
                {
                    throw new Exception("你选择的文件格式不正确，只能导入EXCEL文件！");
                }
                //路径
                uploadfilepath = Server.MapPath("./WHHRREPORT/");
                //新文件名
                modifyfilename = System.Guid.NewGuid().ToString();
                //modifyfilename = inputfile.PostedFile.FileName;
                modifyfilename += "." + inputfile.Value.Substring(inputfile.Value.LastIndexOf(".") + 1);
                //判断是否有该目录
                System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(uploadfilepath);
                if (!dir.Exists)
                {
                    dir.Create();
                }
                orifilename = uploadfilepath + "\\" + modifyfilename;
                //如果存在,删除文件
                if (File.Exists(orifilename))
                {
                    File.Delete(orifilename);
                }
                // 上传文件
                inputfile.PostedFile.SaveAs(orifilename);
            }
            else
            {
                throw new Exception("请选择要导入的Excel文件!");
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return orifilename;
    }

    /// <summary>
    /// 将Dataset的数据导入数据库
    /// </summary>
    /// <param name="pds">数据集</param>
    /// <param name="Cols">数据集列数</param>
    /// <returns></returns>
    private bool AddDatasetToSQL(DataSet pds, int Cols)
    {
        int ic, ir;
        ic = pds.Tables[0].Columns.Count;
        if (pds.Tables[0].Columns.Count < Cols)
        {
            throw new Exception("导入Excel格式错误！Excel只有" + ic.ToString() + "列");
        }
        ir = pds.Tables[0].Rows.Count;
        if (pds != null && pds.Tables[0].Rows.Count > 0)
        {
            for (int i = 0; i < pds.Tables[0].Rows.Count; i++)
            {
                Add(pds.Tables[0].Rows[i][1].ToString(),
                    pds.Tables[0].Rows[i][2].ToString(), pds.Tables[0].Rows[i][3].ToString(),
                    pds.Tables[0].Rows[i][4].ToString(), pds.Tables[0].Rows[i][5].ToString());
            }
        }
        else
        {
            throw new Exception("导入数据为空！");
        }
        return true;
    }


    public void Add(string XM, string SFZH, string LQZY, string YX, string KSH)
    {

        string filename = string.Empty;
        filename = UpLoadXls(FileExcel);//上传XLS文件
       // string oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filename + "; Extended Properties='Excel 8.0;'";
          string oleDBConnString   = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
        string sql = null;
      

       sql = "select * from Student ";
       OleDbConnection conn = new OleDbConnection(oleDBConnString);
       conn.Open();
        DataTable dt = new DataTable();
        DataSet ds = new DataSet();
      System.Data.OleDb.OleDbDataAdapter mycomm = new System.Data.OleDb.OleDbDataAdapter(sql, conn);
      mycomm.Fill(ds,"dt");

        if (dt.Rows.Count == 0)
        {
         
           StringBuilder strSql = new StringBuilder();
     
           BLL.StudentManager bll = new BLL.StudentManager();
           Model.Student model = new Model.Student();
           model.Name = XM;
           model.Scard = SFZH;
           model.Zcard = KSH;
           model.Zresult = LQZY;
           model.Yresult = YX;
           if (bll.Add1(model))
           {
               Response.Write("<script>window.alert('添加成功');</script>");
           }
         
          }
    }
    private void BtnImport_Click(object sender, System.EventArgs e)
    {
        string filename = string.Empty;
        try
        {
            filename = UpLoadXls(FileExcel);//上传XLS文件
            ImportXlsToData(filename);//将XLS文件的数据导入数据库               
            if (filename != string.Empty && System.IO.File.Exists(filename))
            {
                System.IO.File.Delete(filename);//删除上传的XLS文件
            }
            LblMessage.Text = "数据导入成功！";
        }
        catch (Exception ex)
        {
            LblMessage.Text = ex.Message;
        }
    }

}

excel中字段 ：XM、SFZH、KSH、LQZY、YX

数据库表 Student 中字段：S_Name、S_SCard、S_ZResult、S_YResult、S_ZCard

 

 

方法二：

 protected void btnImport_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
        {
            Response.Write("<script>alert('请您选择Excel文件')</script> ");
            return;//当无文件时,返回
        }
        string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
        if (IsXls != ".xls")
        {
            Response.Write("<script>alert('只可以选择Excel文件')</script>");
            return;//当选择的不是Excel文件时,返回
        }
        string filename = FileUpload1.FileName;              //获取Execle文件名  DateTime日期函数
        string savePath = Server.MapPath(("upfiles\\") + filename);//Server.MapPath 获得虚拟服务器相对路径
        FileUpload1.SaveAs(savePath);                        //SaveAs 将上传的文件内容保存在服务器上
        DataSet ds = ExcelSqlConnection(savePath, filename);           //调用自定义方法
        DataRow[] dr = ds.Tables[0].Select();            //定义一个DataRow数组
        int rowsnum = ds.Tables[0].Rows.Count;
        if (rowsnum == 0)
        {
            Response.Write("<script>alert('Excel表为空表,无数据!')</script>");   //当Excel表为空时,对用户进行提示
        }
        else
        {
            for (int i = 0; i < dr.Length; i++)
            {
                //前面除了你需要在建立一个“upfiles”的文件夹外，其他的都不用管了，你只需要通过下面的方式获取Excel的值，然后再将这些值用你的方式去插入到数据库里面
                string title = dr[i]["标题"].ToString();
                string linkurl = dr[i]["链接地址"].ToString();
                string categoryname = dr[i]["分类"].ToString();
                string customername = dr[i]["内容商"].ToString();
                
                //Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>");
            }
            Response.Write("<script>alert('Excle表导入成功!');</script>");
        }

    }

    #region 连接Excel  读取Excel数据   并返回DataSet数据集合
    /// <summary>
    /// 连接Excel  读取Excel数据   并返回DataSet数据集合
    /// </summary>
    /// <param name="filepath">Excel服务器路径</param>
    /// <param name="tableName">Excel表名称</param>
    /// <returns></returns>
    public static System.Data.DataSet ExcelSqlConnection(string filepath, string tableName)
    {
        string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
        OleDbConnection ExcelConn = new OleDbConnection(strCon);
        try
        {
            string strCom = string.Format("SELECT * FROM [Sheet1$]");
            ExcelConn.Open();
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, ExcelConn);
            DataSet ds = new DataSet();
            myCommand.Fill(ds, "[" + tableName + "$]");
            ExcelConn.Close();
            return ds;
        }
        catch
        {
            ExcelConn.Close();
            return null;
        }
    }
    #endregion