如何自动化Excel 2013工作表中列标题的重命名使用C＃ [英] How to automate renaming of column headers in Excel 2013 worksheets using C#

问题描述

我正在使用Visual Studio 2012在SSIS包中开发一个脚本任务，它将清除几百个Excel 2013（.xlsx）文件的工作表和列标题名称，最终导入到SQL Server 2012数据库中，每个每个工作簿（Excel文件）的工作表成为唯一的表。每个Excel文件可以包含一个或超过二十个工作表，每个工作表可以包含一个或多个列。好消息是，所有工作表都包含第一行的列标题。动态导入过程处理工作表和列标题的命名数量不一致不是问题，我已经在流控制中开发了下一步。

I am developing a script task in an SSIS package using Visual Studio 2012 that will be cleaning up the worksheet and column header names for several hundred Excel 2013 (.xlsx) files to be eventually imported into a SQL Server 2012 database with each worksheet of each workbook (Excel file) becoming a unique table. Each Excel file can contain one or over twenty worksheets and each worksheet can contain one or multiple columns. The good news is that all of the worksheets contain column headers on the first row. The dynamic importing process handling an inconsistent number of naming of worksheets and column headers is not a problem, I've already developed this next step in the flow control.

无法弄清楚如何重命名列标题（包含空格），使其不包含每个工作表的空格。

What I cannot figure out is how to rename the column headers (containing spaces) to not contain spaces for each worksheet.

使用以下C＃代码（.NET Framework 4） ve已经能够成功地重命名工作表，以使它们不包含空格。 SSIS不能使用包含空格和特殊字符的工作表名称。

Using the following C# code (.NET Framework 4) I've already been able to successfully rename the worksheets so that they do not contain spaces. SSIS does not work with worksheet names containing spaces and special characters.

    public void Main()
    {

        string xlFile = Dts.Variables["User::ExcelFile"].Value.ToString();

        Excel.Application app = new Excel.Application();
        Excel.Workbook excelWorkbook;
        //Excel.Worksheet excelWorksheet;

        try
        {
            excelWorkbook = app.Workbooks.Open(xlFile);

            string tempsheet = " ";
            //int CountWorksheets = excelWorkbook.Sheets.Count;

            // Rename worksheets replace empty space with an underscore needed for an SSIS import
            foreach (Excel.Worksheet sheet in excelWorkbook.Worksheets)
            {
                tempsheet = sheet.Name;
                tempsheet = tempsheet.Replace(" ", "_");
                sheet.Name = tempsheet;
                //MessageBox.Show(Path.GetFileName(xlFile) + "  " + tempsheet + "  " + sheet.UsedRange.Columns.Count);
            }

            excelWorkbook.Save();
            excelWorkbook.Close();

        }
        catch (Exception ex)
        {
            MessageBox.Show("Excel sheet rename failed for file " + xlFile + " based on " + ex.Message);
        }
        finally
        {
            app.Quit();
            app = null;
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }

        Dts.TaskResult = (int)ScriptResults.Success;
    }

上述代码将应用以下命名空间：

The above code is applying the following Namespaces:

    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using System.IO;
    using Excel = Microsoft.Office.Interop.Excel;
    using System.Text;

根据上面的代码，任何人都可以提供一个建议如何重命名列标题在每个工作表的第一行）我已经做了重命名工作表名称？例如，First Name和Last Name的列标题变为First_Name和Last_Name。由于这些最终成为新数据库表中的字段名，我希望避免在单词之间包含空格。我运行多个搜索，但找不到一个很好的解决方案。提前感谢。

Based on the above code, can anybody provide a suggestion as to how to also rename the column headers (on the first row of every worksheet) as I've already done in renaming the worksheet names? For example, a column headers of "First Name" and "Last Name" become "First_Name" and "Last_Name". With these eventually becoming field names in new database tables, I like to avoid including the space between words. I've run multiple searches but could not find a good solution. Thanks in advance.

推荐答案

由于包含空格的字段名称不会阻止导入在SSIS包中工作，因此字段名称从工作表列标题可以在以后在表中创建和填充后更新。这可以使用sp_rename通过TSQL来完成。以下代码为数据库中包含一个或多个空格的每个字段生成sp_rename。

Since field names containing spaces will not prevent the import from working in an SSIS package, the field names originating from the worksheet column headers can be updated later after they're created in the tables and populated. This can be done with TSQL using sp_rename. The following code generates the sp_rename for every field in the database containing one or more spaces. Then all that is required is to copy and past from the Results it generates into the query and run them all.

SELECT 'EXEC sp_rename '''+table_name+'.['+column_name+']'','''+replace(column_name,' ','_')+''',''COLUMN''' 
FROM information_schema.columns
WHERE column_name like '% %'

使用C＃任务脚本收集工作表列标题。

It would still be nice to know how to gather the worksheet column headers using the C# Task Script.

这篇关于如何自动化Excel 2013工作表中列标题的重命名使用C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！