工作表名称未知时如何将excel文件上传到db 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); 
    }

推荐答案

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}

祝你好运！

Good luck!

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