上传Excel，并将Excel内容保存到数据库

 

前台页面

        <div style="text-align: right;">
            序列号信息：
            <asp:FileUpload ID="FileSN" runat="server" />
            <asp:Button ID="btnSN" runat="server" Text="上传" OnClick="btnSN_Click" />

       </div>

 

 

后台代码

添加引用 using System.IO;

    protected void btnSN_Click(object sender, EventArgs e)
    {
        try
        {
            if (!string.IsNullOrEmpty(file.PostedFile.FileName))
            {
                //获取上传文件扩展名称
                string FullName = file.PostedFile.FileName;
                if (!(FullName.IndexOf(".xlsx") > 0 || FullName.IndexOf(".xls") > 0))//判断上传文件是否为Excel文件
                    throw new Exception("上传文件需为EXCEL格式！");
                FileInfo FileInfo = new FileInfo(FullName);
                string FileName = DateTime.Now.ToString("yyyyMMddhhmmss") + FileInfo.Name;//定义文件名
                string pathws = ConfigurationManager.AppSettings["SNFileWs"].ToString();//发布的文件路径
                string path = ConfigurationManager.AppSettings["SNFile"].ToString();//保存在本地的文件路径
                if (!Directory.Exists(path)) Directory.CreateDirectory(path);//如果本地不存在路径Path，则创建路径Path
                file.PostedFile.SaveAs(path + "\\" + FileName);//保存文件内容
                string NewFileName = FileName;
                string FilePath = pathws + "\\" + FileName;
                string UpdateTime = DateTime.Now.ToString();
                int Status = (int)enumDealerFileState.FilePass;
                string UserId = WebUtility.GetCurrentUserId().ToString();
                DataTable dt = ExcelToDataTable(path + "\\" + FileName, "Sheet1", true);//将Excel文件内容转换为DataTable
                string PK_StoreSNFile = StoreSNDAL.InsertStoreSNFile(UserId, FileInfo.Name, NewFileName, DateTime.Now.ToString(), FilePath, Status);
                StringBuilder SqlStr = new StringBuilder();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    SqlStr.Append(InsertStr(PK_StoreSNFile, dt.Rows[i][0].ToString(), dt.Rows[i][1].ToString(), dt.Rows[i][2].ToString()));
                }
                StoreSNDAL.InsertStoreSNFileDataByStr(SqlStr);
                Response.Write("<script>alert('上传成功。')</script>");
            }
            else
            {
                Response.Write("<script>alert('请选择上传文件。')</script>");
            }
        }
        catch (Exception ex)
        {
            CustomValidator1.IsValid = false;
            CustomValidator1.ErrorMessage = ex.Message;
        }
    }

 

    /// <summary>
    /// 将Excel文件的内容保存到DataTable
    /// </summary>
    /// <param name="fileName">文件完整路径</param>
    /// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
    /// <param name="isFirstRowColumn">第一行是否是DataTable的列名：true=是，false=否</param>
    /// <returns>DataTable数据表</returns>
    public static DataTable ExcelToDataTable(string fileName, string sheetName, bool isFirstRowColumn)
    {
        string HDR = string.Empty;//如果第一行是数据而不是标题的话, 应该写: "HDR=No;"
        if (isFirstRowColumn)
        {
            HDR = "YES";//第一行是标题
        }
        else
        {
            HDR = "NO";//第一行是数据
        }
        //源的定义 
        string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "Data Source=" + fileName + ";" + "Extended Properties='Excel 8.0;HDR=" + HDR + ";IMEX=1';";
        //Sql语句
        string strExcel = "select * from [" + sheetName + "$]";
        //定义存放的数据表
        DataSet ds = new DataSet();
        //连接数据源
        using (OleDbConnection conn = new OleDbConnection(strConn))
        {
            try
            {
                conn.Open();
                //适配到数据源
                OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
                adapter.Fill(ds, sheetName);
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
        return ds.Tables[sheetName];
    }

 

 

 

Web.Config

<configuration>

<appSettings>
    <add key="SNFile" value="C:\SNFile"/>
    <add key="SNFileWs" value="http://acc-test-01/SNFile"/>
  </appSettings>

</configuration>