Excel导入Dataset 然后插入数据库

最新推荐文章于 2024-07-22 21:42:23 发布

guang_ge

最新推荐文章于 2024-07-22 21:42:23 发布

阅读量2.4k

点赞数

分类专栏： C#.NET 文章标签： dataset excel string exception properties object

C#.NET 专栏收录该内容

7 篇文章

订阅专栏

本文介绍了一种从Excel文件中读取数据并导入到数据库的方法。通过使用OLE DB连接和ADO.NET组件，可以有效地将Excel表格数据转换为SQL Server数据库中的记录。文章提供了完整的代码示例，包括如何读取Excel的不同工作表并将它们保存为数据集，然后遍历这些数据集以执行插入操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

//获取Excel中的期初库存信息
            /// <summary>
            /// Function：获取这个Excel中的所有表
            /// Coder：徐臻
            /// Time：20008-12-18
            /// </summary>
            /// <param name="FilePath">文件路径</param>
            /// <returns>返回这个Excel表的数据集</returns>
            public static DataSet GetDataSet(string FilePath)
            {
                string OledbConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + FilePath + "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'");
                OleDbConnection conn = new OleDbConnection(OledbConnectionString);
                ArrayList SheetNameList = new ArrayList();
                try
                {
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    DataTable dtExcelSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                    string SheetName = "";
                    for (int i = 0; i < dtExcelSchema.Rows.Count; i++)
                    {
                        SheetName = dtExcelSchema.Rows[i]["TABLE_NAME"].ToString();
                        SheetNameList.Add(SheetName);
                    }

                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
                DataSet dsExcel = new DataSet();

                try
                {
                    string strSql = "";

                    for (int i = 0; i < SheetNameList.Count; i++)
                    {
                        strSql = "select * from [" + (string)SheetNameList[i] + "]";
                        OleDbDataAdapter oleExcelDataAdapter = new OleDbDataAdapter(strSql, conn);
                        DataTable dtExcel = new DataTable((string)SheetNameList[i]);
                        oleExcelDataAdapter.Fill(dtExcel);
                        dsExcel.Tables.Add(dtExcel);
                    }
                    return dsExcel;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

   protected void ibtnImport_Click(object sender, ImageClickEventArgs e)
    {
        //判断上传文件是否符合要求
        if (System.IO.Path.GetExtension(FileUpload1.FileName) != ".xls")
        {
          ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alter('hao')</script>");
          return;
        }
        else
        {
            string filePath = ExcelLead.ReturnUpperDirectory(AppDomain.CurrentDomain.BaseDirectory) + "//ycqcgl//EquipmentExcelTemp//" + DateTime.Now.ToString("yyyyMMddhhmmss") + "_" + FileUpload1.FileName;
            FileUpload1.SaveAs(filePath);
            string fileName = FileUpload1.FileName;
            int start = fileName.IndexOf('.');
            fileName = fileName.Substring(0, start);
            GetDate(filePath,fileName);
        }
    }

    //获取导入数据
    public void GetDate(string FilePath,string fileName)
    {
        DataSet ds = new DataSet();
        ds = ExcelLead.GetDataSet(FilePath);
        int num = 0;
        SqlConnection conn = new SqlConnection(GlobalThings.ConnectionString);
        try
        {
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                num += ds.Tables[i].Rows.Count;
                for (int j = 0; j < ds.Tables[i].Rows.Count; j++)
                {
                    string str = "insert into Equipment_Temp (名称,型号,数量,单位,厂家,仓库,器材类型) values('" + ds.Tables[i].Rows[j]["名称"].ToString() + "','" + ds.Tables[i].Rows[j]["规格"].ToString() + "','" + ds.Tables[i].Rows[j]["数量"].ToString() + "','" + ds.Tables[i].Rows[j]["单位"].ToString() + "','" + ds.Tables[i].Rows[j]["厂家"].ToString() + "','" + ds.Tables[i].TableName.ToString() + "','" + fileName + "')";
                    SqlCommand comm = new SqlCommand(str, conn);
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    comm.ExecuteNonQuery();
                }
            }
            lblOrder.Text = "共" + num + "条数据库加载成功";
        }
        catch (Exception ep)
        {
            lblOrder.Text = "数据加载失败，引起失败的原因为：" + ep.Message;
        }
        finally
        {
            conn.Close();
        }
    }