c# - 如何仅使用 OLEDB C# SSIS 在 Excel 文件中创建现有 Excel 工作表（表）的新副本

Question

我正在尝试创建一个 Excel 模板的副本，该模板当前已经存在于正在处理的 Excel 文档中，并将数据插入其中。该代码旨在从表格中获取数据，将其插入到 Excel 文件的特定单元格中，这些单元格在其上方命名为突出显示的列，以便对其进行格式化。我正在使用 SSIS 脚本任务来遍历表中的每一行数据。插入数据后，我将尝试为要插入的下一行数据创建模板的副本。

//Foreach record set of data in table
//Insert data into the template into the specific cells
//Create new sheet for next iteration of data to be inserted
//end script

我只能使用 System.Data.OleDb 方法/库，因为 UAT 和 PROD 服务器此时不能接受其他库。所以算法是这样设置的。脚本在 Foreach 循环中，所以...

我希望它能够创建正在向其中插入数据的当前 Excel 工作表的副本。

using (OleDbConnection ExcelConnection = new OleDbConnection(ExcelLocationConnectionString))
{

    ExcelConnection.Open();
    DataTableCollection TablesInFile = ExcelConnection.GetSchema("Tables").DataSet.Tables;

    using (OleDbCommand InsertDataCommand = new OleDbCommand())
    {
        InsertDataCommand.Connection = ExcelConnection;
        DataTable TableToInsertDataInto = TablesInFile[TablesInFile.Count];//Index could be out of range

        if (YestAccountBranch == TodayAccountBranch)           
            AccountBranchChange = "No changes have been made to Account Branch.";
        
        if (YestAccountType == TodayAccountType)           
            AccountTypeChange = "No changes have been made to Account Type.";
        
        if (YestCostCenter == TodayCostCenter)            
            CostCenterChange = "No changes have been made to Cost Center.";
        
        InsertDataSQL = "INSERT INTO [" + TableToInsertDataInto.ToString() + "$B5:F5:I5:L5:P5:T5:Y5:F2] VALUES('" + AccountNumber + "','" +
                    "" + CostCenterChange + "','" + AccountTypeChange + "','" + AccountBranchChange + "','" + TransactionLimit +
                    "','"+ DailyCumulativeLimit +"','" + BLCompanyID + "/" + ClientName + "','" + DateTime.Now.ToShortDateString().ToString() + "')";

        InsertDataCommand.CommandText = InsertDataSQL;
        InsertDataCommand.ExecuteNonQuery();

    }

    //Create table in excel sheet
    using (OleDbCommand CreateTableCommand = new OleDbCommand())
    {
        CreateTableCommand.Connection = ExcelConnection;

        string tableName = "AccountChange" + TablesInFile.Count.ToString() + 1;
        CreateTableCommand.CommandText = $"CREATE TABLE [{tableName}]";

    }
        
}

标签： c#excelssisoledb