使用SSIS在Excel中删除单个工作表

问题描述：

由于一些并发问题，我被迫使用多个工作表创建一个工作簿。在执行结束时，一些工作表将有数据，一些不会。我使用执行SQL任务创建工作表。

Due to some concurrency issues, I have been forced to create a workbook with multiple sheets. At the end of the execution, some sheets will have data and some wont. I create the sheets using an Execute SQL Task.

我试图循环遍历工作簿，并删除不超过一行的工作表。换句话说，如果行数不大于1，请删除工作表。对此问题的任何指针都将不胜感激。如果您需要更多有关我的问题的详细信息，请通知我。谢谢你提前。

I am trying to loop through the workbook and delete the sheets which dont have more than a single row. In other words, delete the sheet if row count is not greater than 1. Any pointers on this question will be appreciated. Please let me know if you need more details on my question. Thank you in advance.

编辑

以下是我的脚本任务从MSDN得到我修改它到一个可以获得excel表并计算行数的点，现在我想做的是当count = 1然后删除工作表。有人可以帮我吗？

Following is the script task that I got from MSDN. I modified it to a point where it can get to the excel sheet and make a count of the rows, now all I want to do is when the count = 1 then delete the sheet. Can someone help me here?

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;

namespace ST_c346c80b4e6747688383c47a9f3e6f78.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion



            public void Main()
        {
            string count = "";
            string fileToTest;
            string tableToTest;
            string connectionString;

            fileToTest = Dts.Variables["ExcelFile"].Value.ToString();
            tableToTest = Dts.Variables["ExcelTable"].Value.ToString();

            Dts.Variables["ExcelTableExists"].Value = false;

                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + fileToTest + ";Extended Properties=Excel 8.0";
                string SQL = "SELECT COUNT (*) FROM [" + tableToTest + "$]";
                using (OleDbConnection conn = new OleDbConnection(connectionString))
                {
                    conn.Open();
                    using (OleDbCommand cmd = new OleDbCommand(SQL, conn))
                    {
                        using (OleDbDataReader reader = cmd.ExecuteReader())
                        {
                            reader.Read();
                            count = reader[0].ToString();
                            //if (count = 1)

                        }
                    }
                    conn.Close();
                }
                //return count;
            }

       }
}

EDIT

在进一步的调查中，我发现我需要添加excel互操作程序集，以使其工作。我没有这个选择，因为这个解决方案将被移植到140台不同的机器。

On further investigation, I found that I would need to add the excel interop assembly for this to work. I dont have that option because this solution will be ported to 140 different machines.

答

遗憾的是，您无法使用OLEDB在Excel中删除工作表，最好的方法是使用DROP清除数据表命令

Sadly you cannot delete a sheet in an Excel using OLEDB, the best you can do is clear it of data using the DROP Table command

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                     "Data Source=" + fileToTest + 
                     ";Mode=ReadWrite;Extended Properties=Excel 8.0";

string SQL = "SELECT COUNT(*) FROM [" + tableToTest + "$]";

using (OleDbConnection conn = new OleDbConnection(connectionString))
{
    conn.Open();
    using (OleDbCommand CountCmd = new OleDbCommand(SQL, conn))
    {
        int RecordCount = (int)CountCmd.ExecuteScalar();

        if (RecordCount == 1)
        {
            SQL = "DROP TABLE [" + tableToTest + "$]";
            using (OleDbCommand DropCmd = new OleDbCommand(SQL, conn))
            {
                DropCmd.ExecuteNonQuery();
            }
        }
    }
    conn.Close();
}

注意：使用 Mode = ReadWrite 。您可以包含/排除 HDR =是/否，但如果您想要阅读/不包含 IMEX = 1 写入访问工作簿

NOTE: The use of Mode=ReadWrite. You may include/exclude the HDR=Yes/No but you must not include IMEX=1 if you want read/write access to the Workbook

BTW：不需要使用OleDbDataReader来读取单个标量结果，而是使用ExecuteScalar（）。

BTW: There's no need to use a OleDbDataReader to read a single scalar result, use ExecuteScalar() instead.

使用OleDB的唯一方法是将要保留的数据复制到新的excel文件中，并替换原来的数据。如果你这样做，你会失去任何公式或格式。

The only way around this using OleDB is to copy the data you want to retain into a new excel file and replace the original one. If you do this however you will lose any formulas or formatting.

使用SSIS在Excel中删除单个工作表

相关推荐