FileUpload控件实例应用 Excel数据导入数据库

HTML:

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <asp:Panel ID="TitlePanel" runat="server" CssClass="titlePadding">
        <asp:Label ID="labTitle" runat="server"></asp:Label>
    </asp:Panel>
    <div class="divDetail" style="margin: 10px;">
        <table>
            <tr>
                <td>
                    选择导入文件
                </td>
                <td>
                    <asp:FileUpload ID="FileUpload1" runat="server" />
                </td>
            </tr>
            <tr>
                <td align="center" colspan="2">
                    <asp:Button ID="btn_import" runat="server" Text="导入" OnClick="btn_import_Click" />
                </td>
            </tr>
        </table>
    </div>
    <div id="strinfo" runat="server" class="mbox pbox">
    </div>
    </form>
</body>
</html>

Using

using System.IO;
using System.Data.OleDb;
using System.Collections.Generic;

 

后台代码：导入按钮事件：

protected void btn_import_Click(object sender, EventArgs e)
        {   string ModuleTag = "cars_hr_recruit_pool_import";
            DataTable dt = xsldata(FileUpload1, "人才档案模板", ModuleTag, "Yes");
            SR.Model.hr_recruit_pool model = new SR.Model.hr_recruit_pool();

            if (dt != null)
            {
                  
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        model.Plan_no = dt.Rows[i][0].ToString();
                        model.Plan_name = dt.Rows[i][1].ToString();
                        model.Position = dt.Rows[i][2].ToString();
                        //....
                       //新增数据记录函数
                        bll.AddItem(model);
                    }
  strinfo.InnerHtml = "导入数据库成功";
                }

        }

 

读取Excel文件数据函数：xlsdata
/// <summary>
        /// Excel数据转为datatable
        /// </summary>
        /// <param name="fileupload">上传控件名</param>
        /// <param name="sheet">Excel中sheet名</param>
        /// <param name="ModuleTag">模块名</param>
        /// <param name="HDR">Yes，这代表第一行是标题，不做为数据使用；NO，则表示第一行不是标题，做为数据来使用</param>
        protected DataTable xlsdata(FileUpload fileupload, string sheet, string ModuleTag,string HDR)
        {
            if (fileupload.FileName == "")
            {
                JScript.Alert("请选择文件");
                return null;
            }
            string fileExtenSion;
            fileExtenSion = Path.GetExtension(fileupload.FileName);
            if (fileExtenSion.ToLower() != ".xls" && fileExtenSion.ToLower() != ".xlsx")
            {
                JScript.Alert("上传的文件格式不正确");
                return null;
            }
            try
            {
                string url = AttachFilePath + ModuleTag + "\\";
                string FileName = url + Path.GetFileName(fileupload.FileName);
                DeleteFile(FileName);
                if (!Directory.Exists(url))
                    Directory.CreateDirectory(url);
                fileupload.SaveAs(FileName);
                string connstr2003 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties='Excel 8.0;HDR=" + HDR + ";IMEX=1;'";
                string connstr2007 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + "\"";
                OleDbConnection conn;
                if (fileExtenSion.ToLower() == ".xls")
                {
                    conn = new OleDbConnection(connstr2003);
                }
                else
                {
                    conn = new OleDbConnection(connstr2007);
                }
                conn.Open();
                string sql = "select * from ["+sheet+"$]";
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                DataTable dt = new DataTable();
                OleDbDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
                sdr.Close();
                conn.Close();
                //删除服务器里上传的文件  
                DeleteFile(FileName);
               //循环删除空行
                removeEmpty(dt);
                return dt;
            }
            catch
            {
                return null;
            }
        }
循环删除空行函数
 //循环去除datatable中的空行
        protected int removeEmpty(DataTable dt)
        {
            if (dt == null)
                return 0;
            List<DataRow> removelist = new List<DataRow>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                bool rowdataisnull = true;
                for (int j = 0; j < dt.Columns.Count; j++)
                {

                    if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim()))
                    {
                        rowdataisnull = false;
                    }

                }
                if (rowdataisnull)
                {
                    removelist.Add(dt.Rows[i]);
                }

            }
            for (int i = 0; i < removelist.Count; i++)
            {
                dt.Rows.Remove(removelist[i]);
            }
            return 1;
        }

 

 

 

 

模板贴图：

 

posted @ 2013-03-26 14:23  偶不是大叔  阅读(4310)  评论(2)  编辑  收藏  举报