读取EXCEL档为DataTable,后作处理导入数据库

最新推荐文章于 2018-06-11 20:44:29 发布

原创最新推荐文章于 2018-06-11 20:44:29 发布 · 884 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excel #exception #dataset #string #null #文档

2008 VC#ASP.NET 专栏收录该内容

86 篇文章

订阅专栏

本博客介绍了一个过程，通过使用C#编程语言，将上传的Excel文档转换为DataSet，并将其数据导入到数据库中。包括文件上传验证、错误处理、数据转换和数据库操作。

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

    protected void btn_ToBom_Click(object sender, EventArgs e)
    {
        Pass = "";
        Eorr = "";
        txt_Pass.Text = "";
        if (FileUpload1.HasFile)
        {   //上传文档到服务器
            if (UpLoadXls(FileUpload1))
            {   //转换数据到Data表
                ImportXlsToData(FileUpload1.FileName);
                if ( Eorr.Trim().Length == 0&&ExcelDataTable != null)
                {

                   //数据处理并导入数据库
                    SetMasterPassDT();
                }
            }
        }
        else
        {
            Eorr += "请选择要导入的EXCEL文档!\r\n";
        }
        //如果存在,删除文件
        if (File.Exists(File_Name))
        {
            File.Delete(File_Name);
        }
        File_Name = "";
        txt_Pass.Text = Eorr+Pass;
    }

/// <summary>
    /// Excel 转换成DataSet
    /// </summary>
    /// <param name="FileName"></param>
    private void ImportXlsToData(string FileName)
    {
        string fileExtend = string.Empty;
        try
        {
            if (FileName == string.Empty)
            {
                throw new ArgumentNullException("请选择导入的Excel文档！\r\n");
            }
            string oleDBConnString = String.Empty;
            oleDBConnString = "Provider=Microsoft.ACE.OLEDB.12.0;";
            oleDBConnString += "Data Source=" + Server.MapPath("../TempFileUpload/" + FileName.Trim());
            oleDBConnString += ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";
            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();
            ds.Tables[0].Columns.Add("ID");
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                DataRow dr = ds.Tables[0].Rows[i];
                if (dr != null)
                {
                    dr["ID"] = i;
                }
            }
            DataColumn[] keys = new DataColumn[1];
            keys[0] = ds.Tables[0].Columns["ID"];
            ds.Tables[0].PrimaryKey= keys;
            ExcelDataTable = ds.Tables[0];
        }
        catch (Exception ex)
        {
            Eorr+= ex.Message;
        }
    }
    ///文件上传
    private bool UpLoadXls(FileUpload FileUploadName)
    {
        string orifilename = string.Empty;
        string uploadfilepath = string.Empty;
        string modifyfilename = string.Empty;
        string fileExtend = "";//文件扩展名
        int fileSize = 0;//文件大小
        try
        {
        if (FileUploadName.HasFile)
        {
            String fileName = FileUploadName.FileName;
            //得到文件的大小
            fileSize = FileUploadName.PostedFile.ContentLength;
            if (fileSize == 0)
            {
              throw new Exception("导入的Excel文件大小为0，请检查是否正确！");
            }
            //得到扩展名

            fileExtend = fileName.Substring(fileName.LastIndexOf(".") + 1);
            if (fileExtend.ToLower() != "xls" && fileExtend.ToLower() != "xlsx")
            {
              throw new Exception("你选择的文件格式不正确，只能导入EXCEL文件！");
            }
            //路径
            uploadfilepath = Server.MapPath("~/TempFileUpload");
            modifyfilename += "." + fileName.Substring(fileName.LastIndexOf(".") + 1);
            //判断是否有该目录
            System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(uploadfilepath);
            if (!dir.Exists)
            {
                dir.Create();
            }
            orifilename = uploadfilepath + "\\" + fileName;
            //如果存在,删除文件
            if (File.Exists(orifilename))
            {
                File.Delete(orifilename);
            }
            // 上传文件
            FileUploadName.PostedFile.SaveAs(orifilename);
            File_Name = orifilename;
            return true;
        }
        else
        {
            throw new Exception("请选择要导入的EXCEL文档!");
        }