.net excel导入到数据库

最新推荐文章于 2022-08-18 14:31:43 发布

Candy_m

最新推荐文章于 2022-08-18 14:31:43 发布

阅读量2.2k

点赞数

文章标签： excel .net dataset exception asp string

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">
     <table bgcolor="#1d82d0" border="0" cellpadding="0" cellspacing="1" class="Text"
        width="100%">
        <tr bgcolor="#ffffff">
            <td valign="top">
                <table border="0" cellpadding="0" cellspacing="0" class="Text" width="100%">
                    <tr>
                        <td width="15">
                             </td>
                        <td valign="top" width="100%">
                            <table border="0" cellpadding="0" cellspacing="1" class="Text" width="100%">
                                <tr height="30">
                                    <td style="width: 120px" width="120">
                                    </td>
                                    <td align="left" style="width: 350px" width="350">
                                    </td>
                                    <td class="hint">
                                    </td>
                                </tr>
                                <tr height="30">
                                    <td style="width: 120px" width="120">
                                        <font face="宋体">请选择要导入的文件</font></td>
                                    <td align="left" style="width: 350px" width="350">
                                        <input id="FileExcel" runat="server" name="FilePhoto" size="42" style="width: 300px"
                                            type="file" /><font color="red"></font></td>
                                    <td class="hint">
                                        <font face="宋体">
                                            <asp:Button ID="BtnImport" runat="server" CssClass="button" OnClick="BtnImport_Click1"
                                                Text="导入" />
                                            </font></td>
                                </tr>
                            </table>
                        </td>
                    </tr>
                </table>
                <asp:Label ID="LblMessage" runat="server" Font-Bold="True" ForeColor="Red"></asp:Label>
            </td>
        </tr>
    </table>
    当前excel里的全部信息，共<asp:Label ID="lballcount" runat="server"></asp:Label>条信息<br />
    <asp:GridView ID="gvgetall" runat="server" AutoGenerateColumns="False" AllowPaging="True" EmptyDataText="没有数据！" OnPageIndexChanging="gvgetall_PageIndexChanging">
        <Columns>
            <asp:BoundField DataField="email" HeaderText="email" />
            <asp:BoundField DataField="PASS" HeaderText="密码" />
            <asp:BoundField DataField="REAL_NAME" HeaderText=" 姓名" />
            <asp:BoundField DataField="SCHOOL" HeaderText=" 学校" />
            <asp:BoundField DataField="COL_NAME" HeaderText="学院" />
            <asp:BoundField DataField="SUB_NAME" HeaderText="专业" />
            <asp:BoundField DataField="GRADE" HeaderText="年级" />
            <asp:BoundField DataField="CLASS" HeaderText="班级" />
        </Columns>
    </asp:GridView>
    成功导入到数据库共<asp:Label ID="lbimport" runat="server" ></asp:Label>条<asp:Label ID="lbunimport"
        runat="server"></asp:Label><br />
    <asp:GridView ID="gvimport" runat="server" AutoGenerateColumns="False" AllowPaging="True" EmptyDataText="没有数据！" OnPageIndexChanging="gvimport_PageIndexChanging">
       <Columns>
     <asp:BoundField DataField="email" HeaderText="email" />
            <asp:BoundField DataField="PASS" HeaderText="密码" />
            <asp:BoundField DataField="REAL_NAME" HeaderText="姓名" />
            <asp:BoundField DataField="SCHOOL" HeaderText="学校" />
            <asp:BoundField DataField="COL_NAME" HeaderText="学院" />
            <asp:BoundField DataField="SUB_NAME" HeaderText="专业" />
            <asp:BoundField DataField="GRADE" HeaderText="年级" />
            <asp:BoundField DataField="CLASS" HeaderText="班级" />
               </Columns>
    </asp:GridView>
    <br />
</asp:Content>

.cs代码

using System.Collections.Generic;
using ccwu.DAL;
using ccwu.Model;

public partial class ccwu_ecregisters : System.Web.UI.Page
{
    //导入excel2003里面的数据，当数据导入成功后绑定的两个数据源：当前EXCECL里面的，与已经导入到数据库里面的
    public static IList<ccwu.Model.T_RESUME_INFO> list = new List<ccwu.Model.T_RESUME_INFO>();
    public static DataSet dsall = new DataSet();
    public static int count = 0;
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    private void ImportXlsToData(string fileName)
    {
        try
        {
            if (fileName == string.Empty)
            {
                throw new ArgumentNullException("Excel文件上传失败！");
            }

            string oleDBConnString = String.Empty;
            oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0;";
            oleDBConnString += "Data Source=";
            oleDBConnString += fileName;
            oleDBConnString += ";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, 8);
        }
        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("~/Service/GraduateChannel/GraduateApply/ImgUpLoads");
                //新文件名
                modifyfilename = System.Guid.NewGuid().ToString();
                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++)
            {
                ccwu.Model.T_RESUME_INFO model = new ccwu.Model.T_RESUME_INFO();
                string schooolid = pds.Tables[0].Rows[i][3].ToString();

                model.SCHOOL_INFO_ID = schoolid(schooolid);
                model.EMAIL = pds.Tables[0].Rows[i][0].ToString();
                model.pass = pds.Tables[0].Rows[i][1].ToString();
                model.REAL_NAME = pds.Tables[0].Rows[i][2].ToString();
                model.SCHOOL = pds.Tables[0].Rows[i][3].ToString();
                model.COL_NAME = pds.Tables[0].Rows[i][4].ToString();
                model.SUB_NAME = pds.Tables[0].Rows[i][5].ToString();
                model.GRADE = pds.Tables[0].Rows[i][6].ToString();
                model.CLASS = pds.Tables[0].Rows[i][7].ToString();
                //excel的格式：必须是英文列头
                //EMAIL 密码     姓名       学校    学院     专业     年级   班级
                //email PASS REAL_NAME SCHOOL COL_NAME SUB_NAME GRADE CLASS
                // 0   1      2        3       4       5        6      7
                Add(model);
            }
            //全部信息
            this.gvgetall.DataSource = pds;
            this.gvgetall.DataBind();
            dsall = pds;
            this.lballcount.Text = pds.Tables[0].Rows.Count.ToString();
        }
        else
        {
            throw new Exception("导入数据为空！");
        }
        return true;
    }

    /// <summary>
    /// 插入数据到数据库
    /// </summary>
    public void Add(ccwu.Model.T_RESUME_INFO model)
    {
        string sql = "select * from T_STUDENT_INFO where email='" + model.EMAIL.ToString() + "'";//根据一个号去查询
        DataSet ds = ccwu.DBUtility.DbHelperSQL.Query(sql.ToString());
        // int count = 0;
        if (ds.Tables[0].Rows.Count == 0)
        {
            //insert into PersonRecord
            ccwu.DAL.T_STUDENT_INFO dalstudent = new ccwu.DAL.T_STUDENT_INFO();
            ccwu.DAL.T_RESUME_INFO dalresume = new ccwu.DAL.T_RESUME_INFO();
            ccwu.Model.T_STUDENT_INFO m = new ccwu.Model.T_STUDENT_INFO();
            m.EMAIL = model.EMAIL.ToString();
            m.PASS = model.pass.ToString();
            m.SCHOOL_INFO_ID = Convert.ToInt32(model.SCHOOL_INFO_ID.ToString());
            //向学生注册表里
            int lastid = dalstudent.Add(m);
            model.STUDENT_INFO_ID = lastid;
            //向简历表里添加信息
            dalresume.AddForAll(model);
            list.Add(model);
        }
        if (ds.Tables[0].Rows.Count > 0)
        {
            count = count + 1;
        }

    }
    protected void BtnImport_Click1(object sender, 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 = "数据导入成功！";
            this.gvimport.DataSource = list;
            this.gvimport.DataBind();
            this.lbimport.Text = list.Count.ToString();
            int all = Convert.ToInt32(this.lballcount.Text.ToString());
            int import = Convert.ToInt32(this.lbimport.Text.ToString());
            int unimport = all - import;
            if (unimport > count)
            {
                this.lbunimport.Text = "," + unimport.ToString() + "条，由于传输原因未导入到数据库！";
            }
        }
        catch (Exception ex)
        {
            LblMessage.Text = ex.Message;
        }
    }
    public int schoolid(string schoolname)
    {
        ccwu.DAL.T_SCHOOL_INFO dal = new ccwu.DAL.T_SCHOOL_INFO();
        DataSet ds = dal.GetList(" name='" + schoolname + "'");
        return Convert.ToInt32(ds.Tables[0].Rows[0]["ID"].ToString());
    }
    protected void gvgetall_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        this.gvgetall.PageIndex = e.NewPageIndex;
        this.gvgetall.DataSource = dsall;
        this.gvgetall.DataBind();
    }
    protected void gvimport_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        this.gvimport.PageIndex = e.NewPageIndex;
        this.gvimport.DataSource = list;
        this.gvimport.DataBind();
    }
}