将Excel工作表2003或2007保存在SQL Server数据库上 [英] saving excel sheet 2003 or 2007 on sql server database

问题描述

你好朋友，我想给用户提供以.xls和.xlsx格式上传excel表格的便利.在本地，我的代码对于.xlsx文件工作正常，但为.xls文件提供错误.
但是在服务器上，这两种格式都出错.
这是我的代码

hello friends i want to give user facility of uploading excel sheet in both .xls and .xlsx format .On Local ,My code is working fine for .xlsx file , but giving error for .xls file .
But on server it is giving error for both formats.
This is my code

using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;

 public void ShowStatus()
    {
        DsOrders = ObjStatus.ShowOrderStatus();
        if (DsOrders.Tables.Count > 0)
        {
            if (DsOrders.Tables[0].Rows.Count > 0)
            {
                Grdorders.DataSource = DsOrders;
                Grdorders.DataBind();
            }
            else
            {
                lblstatus.Text = "There is not record ";
            }
        }

    }
    public void saveCustomers(string DirPath, string filename, string FileExtnsn)
    {



        //Declare Variables - Edit these based on your particular situation
        string sSQLTable = "PaymentReport";
        string sExcelFileName = filename;
        string sWorkbook = "[Sheet1$]";
        string extnsn = FileExtnsn;
        string sExcelConnectionString;
        //Create our connection strings


        //Dim sSqlConnectionString As String = Resources.Resource.ConnectionString

        //Series of commands to bulk copy data from the excel file into our SQL table
        if (extnsn == ".xlsx")
        {
            sExcelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DirPath + sExcelFileName +

";Extended Properties=''Excel 12.0 Xml;HDR=YES''";
        }
        else
        {
            sExcelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DirPath + sExcelFileName +
                        ";Extended Properties=''Excel 8.0;HDR=YES;''";
        }
        OleDbConnection OleDbConn = new OleDbConnection(sExcelConnectionString);
        //OleDbCommand OleDbCmd = new OleDbCommand(("Select a,b,c,d,e,f,g,h,pk,j,k,l,m,n,o from " + sWorkbook), OleDbConn);
        OleDbCommand OleDbCmd = new OleDbCommand(("Select [Way Bill No],[Stage Description],[Reasion Description],[Remarks],[Updated Location] from " + sWorkbook), OleDbConn);



        // Dim OleDbCmd As OleDbCommand = New OleDbCommand(("SELECT custTitle, custFName, custMName, custLName FROM " & sWorkbook), OleDbConn)


        //OleDbCmd.CommandTimeout = "600";
        OleDbConn.Open();

        //Try

        //Dim productID As Integer
        bool customerExists = false;
        string notAddedIds = " Following User''s were not added as the User ID''s already exist.<br /> Please try adding these users with different id''s. <br />";

        int rcode = 0;
        string stremp = "";


        OleDbDataReader dr = OleDbCmd.ExecuteReader();

        if (dr.HasRows)
        {

            while (dr.Read())
            {

             //    [Way Bill No],[Stage Description,[Reason Description],[Remarks],[Updated Location]

                ObjStatus.intwaybillno = getVal(Convert.ToInt32(dr["Way Bill No"]));
                ObjStatus.strStageDescription = getVal2((dr["Stage Description"]).ToString());
                ObjStatus.strReasionDescription = getVal3((dr["Reasion Description"]).ToString());
                ObjStatus.strremarks = getVal4((dr["Remarks"]).ToString());
                ObjStatus.strUpdatedLocation = getVal5((dr["Updated Location"]).ToString());
                         rcode = ObjStatus.Insertexceldataintoorderstatus();
                if (rcode == 1)
                {
                    lblmsg.Text = "Status has been added successfully";
                 
                }
                else
                {

                }
            }


            dr.Close();
            OleDbConn.Close();
        }

    }




    public int getVal(int myVal)
    {
        if (myVal == 0)
        {
            return (0);
        }
        else
        {

            return myVal;
        }
    }
    public string getVal2(string myVal)
    {
        if (myVal == System.DBNull.Value.ToString())
        {
            return ("");
        }
        else
        {
            myVal = myVal.Trim();
            return (myVal);
        }
    }


    public string getVal3(string myVal)
    {
        if (myVal == System.DBNull.Value.ToString())
        {
            return ("");
        }
        else
        {
            myVal = myVal.Trim();
            return (myVal);
        }
    }
    public string getVal4(string myVal)
    {

        if (myVal == System.DBNull.Value.ToString())
        {
            return ("");
        }
        else
        {
            myVal = myVal.Trim();
            return (myVal);
        }

    }
    public string getVal5(string myVal)
    {
        if (myVal == System.DBNull.Value.ToString())
        {
            return ("");
        }
        else
        {
            myVal = myVal.Trim();
            return (myVal);
        }
    }

    protected void BtnSbmit_Click(object sender, EventArgs e)
    {

        try
        {
            //string edatetime = getDate(DateTime.Now);
            string DirPath = Server.MapPath("ExcelSheet\\");
            string filepath = null;
            string filename = null;
            string Fextnsn = null;
            if (UploadExcel.PostedFile.ContentLength != 0)
            {
                //First Create Director
                if (!Directory.Exists(DirPath))
                {
                    Directory.CreateDirectory(DirPath);
                }
                filepath = UploadExcel.PostedFile.FileName;
                FileInfo fInfo = new FileInfo(filepath);
                filename = fInfo.Name;
                Fextnsn = Path.GetExtension(UploadExcel.FileName).ToLower();
                //save the posted file

                //Err().Visible = false;
                UploadExcel.PostedFile.SaveAs(DirPath + filename);
                try
                {
                    saveCustomers(DirPath, filename, Fextnsn);
                    lblmsg.Text = "Status has been added successfully ";
           
                }
                catch (Exception ex)
                {
                    //err.Visible = True
                    //err.Text = "Please upload the expected format file"
                    //File.Delete(DirPath & filename)
                }
                if (File.Exists(DirPath + filename))
                {
                    File.Delete(DirPath + filename);

                }
            

            }
            else
            {
                //Err().Visible = true;
                //Err().Text = "Some error occured during the Uploading";
            }
            ShowStatus();
         
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }


    }

它显示错误:-未在.xlsx文件的本地计算机上注册``Microsoft.ACE.OLEDB.12.0''提供程序.

错误
未在本地计算机上使用.xlsx文件注册"Microsoft.Jet.OLEDB.4.0"提供程序.



请帮助我，谢谢提前的朋友.

It is showing error :-The ''Microsoft.ACE.OLEDB.12.0'' provider is not registered on the local machine with .xlsx file.

Error
The ''Microsoft.Jet.OLEDB.4.0'' provider is not registered on the local machine with .xlsx file.



please help me ,thanks in advance friends.

推荐答案

; 字符串extnsn = FileExtnsn; 字符串sExcelConnectionString; //创建我们的连接字符串 //将sSqlConnectionString设置为String = Resources.Resource.ConnectionString //一系列命令，用于将数据从excel文件批量复制到我们的SQL表中 如果(extnsn ==".xlsx") { sExcelConnectionString ="Provider = Microsoft.ACE.OLEDB.12.0; Data Source =" + DirPath + sExcelFileName + ;扩展属性=''Excel 12.0 Xml; HDR = YES''"; } 别的 { sExcelConnectionString ="Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + DirPath + sExcelFileName + ;扩展属性=" Excel 8.0; HDR = YES;"; } OleDbConnection OleDbConn =新的OleDbConnection(sExcelConnectionString); //OleDbCommand OleDbCmd = new OleDbCommand((从"+ sWorkbook"中选择a，b，c，d，e，f，g，h，pk，j，k，l，m，n，o，OleDbConn); OleDbCommand OleDbCmd = new OleDbCommand((从[+ sWorkbook]中选择[方式清单编号]，[阶段说明]，[原因说明]，[备注]，[更新位置]，OleDbConn)； //Dim OleDbCmd As OleDbCommand = New OleDbCommand(("SELECT custTitle，custFName，custMName，custLName FROM"& sWorkbook)，OleDbConn) //OleDbCmd.CommandTimeout ="600"; OleDbConn.Open(); //尝试 //将productID设为整数 bool customerExists = false； string notAddedIds =由于用户ID已经存在，因此未添加关注用户.< br/>请尝试添加具有不同ID的这些用户.< br/>"; int rcode = 0; 字符串stremp ="; OleDbDataReader dr = OleDbCmd.ExecuteReader(); 如果(dr.HasRows) { 而(dr.Read()) { //[Way Bill No]，[阶段说明，[原因说明]，[备注]，[更新位置] ObjStatus.intwaybillno = getVal(Convert.ToInt32(dr ["Way Bill No"]))); ObjStatus.strStageDescription = getVal2((dr ["Stage Description"]).ToString()); ObjStatus.strReasionDescription = getVal3((dr ["Reasion Description"]).ToString()); ObjStatus.strremarks = getVal4((dr ["Remarks"]).ToString()); ObjStatus.strUpdatedLocation = getVal5((dr ["Updated Location"]).ToString()); rcode = ObjStatus.Insertexceldataintoorderstatus(); 如果(rcode == 1) { lblmsg.Text =状态已成功添加"； } 别的 { } } dr.Close(); OleDbConn.Close(); } } public int getVal(int myVal) { 如果(myVal == 0) { 返回(0); } 别的 { 返回myVal; } } 公共字符串getVal2(字符串myVal) { 如果(myVal == System.DBNull.Value.ToString()) { 返回 (""); } 别的 { myVal = myVal.Trim(); 返回(myVal); } } 公共字符串getVal3(字符串myVal) { 如果(myVal == System.DBNull.Value.ToString()) { 返回 (""); } 别的 { myVal = myVal.Trim(); 返回(myVal); } } 公共字符串getVal4(字符串myVal) { 如果(myVal == System.DBNull.Value.ToString()) { 返回 (""); } 别的 { myVal = myVal.Trim(); 返回(myVal); } } 公共字符串getVal5(字符串myVal) { 如果(myVal == System.DBNull.Value.ToString()) { 返回 (""); } 别的 { myVal = myVal.Trim(); 返回(myVal); } } 受保护的void BtnSbmit_Click(对象发送者，EventArgs e) { 尝试 { //string edatetime = getDate(DateTime.Now); 字符串DirPath = Server.MapPath("ExcelSheet \\"); 字符串filepath = null; 字符串文件名= null; 字符串Fextnsn = null; 如果(UploadExcel.PostedFile.ContentLength！= 0) { //首先创建总监 如果(！Directory.Exists(DirPath)) { Directory.CreateDirectory(DirPath); } filepath = UploadExcel.PostedFile.FileName; FileInfo fInfo =新的FileInfo(filepath); 文件名= fInfo.Name; Fextnsn = Path.GetExtension(UploadExcel.FileName).ToLower(); //保存发布的文件 //Err().Visible = false; UploadExcel.PostedFile.SaveAs(DirPath +文件名); 尝试 { saveCustomers(DirPath，文件名，Fextnsn); lblmsg.Text =状态已成功添加"； } 抓住(前例外) { //err.Visible = True //err.Text =请上传所需的格式文件" //File.Delete(DirPath& filename) } 如果(File.Exists(DirPath +文件名)) { File.Delete(DirPath +文件名); } } 别的 { //Err().Visible = true; //Err().Text =上传期间发生一些错误"; } ShowStatus(); } 抓住(前例外) { Response.Write(ex.Message); } }

"; string extnsn = FileExtnsn; string sExcelConnectionString; //Create our connection strings //Dim sSqlConnectionString As String = Resources.Resource.ConnectionString //Series of commands to bulk copy data from the excel file into our SQL table if (extnsn == ".xlsx") { sExcelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DirPath + sExcelFileName + ";Extended Properties=''Excel 12.0 Xml;HDR=YES''"; } else { sExcelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DirPath + sExcelFileName + ";Extended Properties=''Excel 8.0;HDR=YES;''"; } OleDbConnection OleDbConn = new OleDbConnection(sExcelConnectionString); //OleDbCommand OleDbCmd = new OleDbCommand(("Select a,b,c,d,e,f,g,h,pk,j,k,l,m,n,o from " + sWorkbook), OleDbConn); OleDbCommand OleDbCmd = new OleDbCommand(("Select [Way Bill No],[Stage Description],[Reasion Description],[Remarks],[Updated Location] from " + sWorkbook), OleDbConn); // Dim OleDbCmd As OleDbCommand = New OleDbCommand(("SELECT custTitle, custFName, custMName, custLName FROM " & sWorkbook), OleDbConn) //OleDbCmd.CommandTimeout = "600"; OleDbConn.Open(); //Try //Dim productID As Integer bool customerExists = false; string notAddedIds = " Following User''s were not added as the User ID''s already exist.<br /> Please try adding these users with different id''s. <br />"; int rcode = 0; string stremp = ""; OleDbDataReader dr = OleDbCmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { // [Way Bill No],[Stage Description,[Reason Description],[Remarks],[Updated Location] ObjStatus.intwaybillno = getVal(Convert.ToInt32(dr["Way Bill No"])); ObjStatus.strStageDescription = getVal2((dr["Stage Description"]).ToString()); ObjStatus.strReasionDescription = getVal3((dr["Reasion Description"]).ToString()); ObjStatus.strremarks = getVal4((dr["Remarks"]).ToString()); ObjStatus.strUpdatedLocation = getVal5((dr["Updated Location"]).ToString()); rcode = ObjStatus.Insertexceldataintoorderstatus(); if (rcode == 1) { lblmsg.Text = "Status has been added successfully"; } else { } } dr.Close(); OleDbConn.Close(); } } public int getVal(int myVal) { if (myVal == 0) { return (0); } else { return myVal; } } public string getVal2(string myVal) { if (myVal == System.DBNull.Value.ToString()) { return (""); } else { myVal = myVal.Trim(); return (myVal); } } public string getVal3(string myVal) { if (myVal == System.DBNull.Value.ToString()) { return (""); } else { myVal = myVal.Trim(); return (myVal); } } public string getVal4(string myVal) { if (myVal == System.DBNull.Value.ToString()) { return (""); } else { myVal = myVal.Trim(); return (myVal); } } public string getVal5(string myVal) { if (myVal == System.DBNull.Value.ToString()) { return (""); } else { myVal = myVal.Trim(); return (myVal); } } protected void BtnSbmit_Click(object sender, EventArgs e) { try { //string edatetime = getDate(DateTime.Now); string DirPath = Server.MapPath("ExcelSheet\\"); string filepath = null; string filename = null; string Fextnsn = null; if (UploadExcel.PostedFile.ContentLength != 0) { //First Create Director if (!Directory.Exists(DirPath)) { Directory.CreateDirectory(DirPath); } filepath = UploadExcel.PostedFile.FileName; FileInfo fInfo = new FileInfo(filepath); filename = fInfo.Name; Fextnsn = Path.GetExtension(UploadExcel.FileName).ToLower(); //save the posted file //Err().Visible = false; UploadExcel.PostedFile.SaveAs(DirPath + filename); try { saveCustomers(DirPath, filename, Fextnsn); lblmsg.Text = "Status has been added successfully "; } catch (Exception ex) { //err.Visible = True //err.Text = "Please upload the expected format file" //File.Delete(DirPath & filename) } if (File.Exists(DirPath + filename)) { File.Delete(DirPath + filename); } } else { //Err().Visible = true; //Err().Text = "Some error occured during the Uploading"; } ShowStatus(); } catch (Exception ex) { Response.Write(ex.Message); } }

它显示错误:-未在.xlsx文件的本地计算机上注册``Microsoft.ACE.OLEDB.12.0''提供程序.

错误
未在本地计算机上使用.xlsx文件注册"Microsoft.Jet.OLEDB.4.0"提供程序.



请提前帮助我，谢谢朋友.

It is showing error :-The ''Microsoft.ACE.OLEDB.12.0'' provider is not registered on the local machine with .xlsx file.

Error
The ''Microsoft.Jet.OLEDB.4.0'' provider is not registered on the local machine with .xlsx file.



please help me ,thanks in advance friends.

您所依赖的是服务器上安装的Microsoft Office，情况并非如此.

对于这些类型的方案，最好使用非MSOffice Excel组件.

You are relying on Microsoft Office being installed on your server which is not the case.

For these types of scenarios you are better of using non MSOffice Excel components.

这篇关于将Excel工作表2003或2007保存在SQL Server数据库上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！