工作表名称未知时如何将excel文件上传到db C＃ [英] How to upload excel file to db when sheet name is unknown C#

问题描述

我必须将一张工作表上传到我的数据库，但工作表名称不同，我看到了一个关于如何在工作表名称未知时上传的示例（< ahref =https：www.codeproject.com =articles = 8096 =c-retrieve-excel-workbook-sheet-names=>）但我不太明白它是如何工作的以及如何将它合并到我的代码中，真的很感激帮助，因为我仍然是初学者（学生）:)



我尝试过：

i have to upload a sheet into my database but the sheet names differ, i have seen an example on how to upload when sheet name is unknown (<ahref="https: www.codeproject.com="" articles="" 8096="" c-retrieve-excel-workbook-sheet-names"="">) but i dont quite understand how it works and how to incorporate it into my code , would really appreciate help as i am still a beginner(student) :)

What I have tried:

public void uploadWBsheet(string excelfile)
    {
        //declare variables - edit these based on your particular situation
        string ssqltable = "[dbo].[UPLOAD_WB]";
        // make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have different
        string myexceldataquery = "Select * FROM [$] ";
        try
        {
            //create our connection strings
            string sexcelconnectionstring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelfile + ";Extended Properties=" + "\"excel 12.0;hdr=yes;\"";

            SqlConnection sqlconn = new SqlConnection(strConnString);

            sqlconn.Open();
    
            //series of commands to bulk copy data from the excel file into our sql table
            OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);
            OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);
            oledbconn.Open();
            OleDbDataReader dr = oledbcmd.ExecuteReader();




            SqlBulkCopy bulkcopy = new SqlBulkCopy(strConnString);
            bulkcopy.DestinationTableName = ssqltable;
            //Mapping Table column    

            bulkcopy.ColumnMappings.Add("BeneficiaryID", "[BeneficiaryID]");
            bulkcopy.ColumnMappings.Add("BeneficiaryName", "[BeneficiaryName]");
            bulkcopy.ColumnMappings.Add("BranchNameID", "[BranchNameID]");
            bulkcopy.ColumnMappings.Add("BranchCode", "[BranchCode]");
            bulkcopy.ColumnMappings.Add("AccountType", "[AccountType]");
            bulkcopy.ColumnMappings.Add("AccountNumber", "[AccountNumber]");
            bulkcopy.ColumnMappings.Add("TotalWages", "[TotalWages]");
            bulkcopy.ColumnMappings.Add("PaymentDate", "[PaymentDate]");


            //sqlcmd.ExecuteNonQuery();
            while (dr.Read())
            {
                bulkcopy.WriteToServer(dr);

            }
            oledbconn.Close();
            sqlconn.Close();
        }
        //this.importtotemp();


        catch (Exception) { }
        ClientScript.RegisterStartupScript(GetType(), "alert", "alert('File Uploaded');", true);


    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        string CurrentFilePath = Path.GetFullPath(fuAttachment.PostedFile.FileName);
        uploadWBsheet(CurrentFilePath); 
    }

推荐答案

;
尝试
{
//创建我们的连接strings
string sexcelconnectionstring = @Provider = Microsoft.ACE.OLEDB.12.0; Data Source =+ excelfile +; Extended Properties =+\excel 12.0; hdr = yes; \;

SqlConnection sqlconn = new SqlConnection（strConnString）;

sqlconn.Open（）;

//批量复制数据的一系列命令excel文件到我们的sql表
OleDbConnection oledbconn = new OleDbConnection（sexcelconnectionstring）;
OleDbCommand oledbcmd = new OleDbCommand（myexceldataquery，oledbconn）;
oledbconn.Open（）;
OleDbDataReader dr = oledbcmd.ExecuteReader（）;




SqlBulkCopy bulkcopy = new SqlBulkCopy（strConnString）;
bulkcopy.DestinationTableName = ssqltable;
//映射表列

bulkcopy.ColumnMappings.Add（BeneficiaryID，[BeneficiaryID]）;
bulkcopy.ColumnMappings.Add（BeneficiaryName，[BeneficiaryName]）;
bulkcopy.ColumnMappings.Add（BranchNameID，[BranchNameID]）;
bulkcopy.ColumnMappings.Add（BranchCode，[BranchCode]）;
bulkcopy.ColumnMappings.Add（AccountType，[AccountType]）;
bulkcopy.ColumnMappings.Add（AccountNumber，[AccountNumber]）;
bulkcopy.ColumnMappings.Add（TotalWages，[TotalWages]）;
bulkcopy.ColumnMappings.Add（PaymentDate，[PaymentDate]）;


//sqlcmd.ExecuteNonQuery（）;
while（dr.Read（））
{
bulkcopy.WriteToServer（dr）;

}
oledbconn.Close（）;
sqlconn.Close（）;
}
//this.importtotemp（）;


catch（例外）{}
ClientScript.RegisterStartupScript（GetType（），alert，alert（'File Uploaded'）;，true）;


}

protected void Button1_Click（object sender，EventArgs e）
{
string CurrentFilePath = Path.GetFullPath（fuAttachment.PostedFile） 。文件名）;
uploadWBsheet（CurrentFilePath）;
}

"; try { //create our connection strings string sexcelconnectionstring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelfile + ";Extended Properties=" + "\"excel 12.0;hdr=yes;\""; SqlConnection sqlconn = new SqlConnection(strConnString); sqlconn.Open(); //series of commands to bulk copy data from the excel file into our sql table OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring); OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn); oledbconn.Open(); OleDbDataReader dr = oledbcmd.ExecuteReader(); SqlBulkCopy bulkcopy = new SqlBulkCopy(strConnString); bulkcopy.DestinationTableName = ssqltable; //Mapping Table column bulkcopy.ColumnMappings.Add("BeneficiaryID", "[BeneficiaryID]"); bulkcopy.ColumnMappings.Add("BeneficiaryName", "[BeneficiaryName]"); bulkcopy.ColumnMappings.Add("BranchNameID", "[BranchNameID]"); bulkcopy.ColumnMappings.Add("BranchCode", "[BranchCode]"); bulkcopy.ColumnMappings.Add("AccountType", "[AccountType]"); bulkcopy.ColumnMappings.Add("AccountNumber", "[AccountNumber]"); bulkcopy.ColumnMappings.Add("TotalWages", "[TotalWages]"); bulkcopy.ColumnMappings.Add("PaymentDate", "[PaymentDate]"); //sqlcmd.ExecuteNonQuery(); while (dr.Read()) { bulkcopy.WriteToServer(dr); } oledbconn.Close(); sqlconn.Close(); } //this.importtotemp(); catch (Exception) { } ClientScript.RegisterStartupScript(GetType(), "alert", "alert('File Uploaded');", true); } protected void Button1_Click(object sender, EventArgs e) { string CurrentFilePath = Path.GetFullPath(fuAttachment.PostedFile.FileName); uploadWBsheet(CurrentFilePath); }

如果您想获得工作表的名称，则必须使用 GetSchema 方法：

If you would like to get the name of sheet, you have to use GetSchema method:

string sFileName = @"filename";
string sConStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES';", sFileName);
DataTable dt = new DataTable();
using (OleDbConnection connection = new OleDbConnection(sConStr))
{
    connection.Open();
    var sheets = connection.GetSchema("TABLES").AsEnumerable()
        .Select(x=>x.Field<string>("TABLE_NAME"))
        .ToList();
    foreach(var sheet in sheets) //loop through the collection of sheets ;)
    {
        //your logic here...
                string myexceldataquery = string.Format("Select * FROM [{0}

;，表格）;
//获取数据
使用（OleDbCommand oledbcmd = new OleDbCommand（myexceldataquery，oledbconn）
{
oledbconn.Open（）;
using（OleDbDataReader dr = oledbcmd。 ExecuteReader（））
{
// sql bulk copy here！
}
}
}
}

; ", sheet); //get data using(OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn) { oledbconn.Open(); using(OleDbDataReader dr = oledbcmd.ExecuteReader()) { //sql bulk copy here! } } } }

祝你好运！

Good luck!

这篇关于工作表名称未知时如何将excel文件上传到db C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！