SSIS - change excel columns number format from Script Task

- August 15, 2019

Problem:

I am exporting Data from SQL Server, utilizing SSIS Data Flow Task. Here all columns appear as Text despite export formatting. Hence I need to develop a SSIS Script task to do the necessary conversion. I am facing trouble in developing the script.

I have Tried different options available in the internet. but unsuccessfully.

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

namespace ST_de899f405b7b4083b0ad8cba6b3df2e3
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
    public void Main()
    {
        string inputFile = (string)Dts.Variables["Target_FullFilePath"].Value;

        Excel.Application ExcelApp = new Excel.Application();
        Excel.Workbook ExcelWorkbook = ExcelApp.Workbooks.Open(inputFile);
        Excel.Range formatRange;
        ExcelApp.Visible = true;

        foreach (Excel.Worksheet ExcelWorksheet in ExcelWorkbook.Sheets)
        {
            ExcelWorksheet.Select(Type.Missing);

            ExcelWorksheet.Columns[10].NumberFormat = "@";
            formatRange = ExcelWorksheet.Columns[17];
            formatRange.NumberFormat = "####";
            ExcelWorksheet.Columns[17].NumberFormat = "####";
            ExcelWorksheet.Columns[17].NumberFormatString = "0.00";
            ExcelWorksheet.Columns[10].Text = "#";
            ExcelWorksheet.Columns[16].Text = "General";

            ExcelWorksheet.Columns[31].Text = "m/d/yyyy";

        }
        ExcelWorkbook.Save();

        GC.Collect();
        GC.WaitForPendingFinalizers();

        ExcelWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
        Marshal.FinalReleaseComObject(ExcelWorkbook);

        ExcelApp.Quit();
        Marshal.FinalReleaseComObject(ExcelApp);

        Dts.TaskResult = (int)ScriptResults.Success;
    }
    enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };
    }
}

Expected Result: Columns numbered 16, 17, 22 to be converted to "General" and look like decimal numbers. Column 31 to be converted to "General" and look like Date.

I confirm the corresponding columns have relevant values only except column header.

Solution:

Before providing the solution, i have to explain some points about Excel Number Format

What is Number Format property?

Referring to Number format codes documentation:

You can use number formats to change the appearance of numbers, including dates and times, without changing the actual number. The number format does not affect the cell value that Excel uses to perform calculations. The actual value is displayed in the formula bar.

What is General Number format?

Referring to Reset a number to the General format documentation:

The General format is the default number format that Excel applies when you type a number. For the most part, numbers that are formatted with the General format are displayed just the way that you type them.

How Date are stored in Excel?

Referring to How Dates Work in Excel:

The dates in Excel are actually stored as numbers, and then formatted to display the date.

Your excepted result

You mentioned that:

Expected Result: Columns numbered 16, 17, 22 to be converted to "General" and look like decimal numbers. Column 31 to be converted to "General" and look like Date.

Based on what we mentioned you cannot convert column 31 to "General" and make it look like Date.

Solution

You just need to set NumberFormat property to an empty string to set it as "General"

ExcelWorksheet.Columns[16].NumberFormat = "";

Experiments

I Created an Excel file with 4 columns: NumberColumn, DateColumn, DecimalColumn and StringColumn as shown in the image above:

I created a console application with the following code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Text.RegularExpressions;
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            string inputFile = @"D:\Test.xlsx";

            Excel.Application ExcelApp = new Excel.Application();
            Excel.Workbook ExcelWorkbook = ExcelApp.Workbooks.Open(inputFile);
            Excel.Range formatRange;
            ExcelApp.Visible = true;

            foreach (Excel.Worksheet ExcelWorksheet in ExcelWorkbook.Sheets)
            {
                ExcelWorksheet.Select(Type.Missing);

                ExcelWorksheet.Columns[1].NumberFormat = "";
                ExcelWorksheet.Columns[2].NumberFormat = "yyyy-MM-dd"; // convert format to date
                ExcelWorksheet.Columns[2].NumberFormat = "";
                ExcelWorksheet.Columns[3].NumberFormat = "0.00000"; // convert format to decimal with 5 decimal digits
                ExcelWorksheet.Columns[3].NumberFormat = "";
                ExcelWorksheet.Columns[4].NumberFormat = "";


            }
            ExcelWorkbook.Save();

            GC.Collect();
            GC.WaitForPendingFinalizers();

            ExcelWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
            Marshal.FinalReleaseComObject(ExcelWorkbook);

            ExcelApp.Quit();
            Marshal.FinalReleaseComObject(ExcelApp);
        }
    }
}

After executing the application, the Excel looked like the following:

Discussion and Conclusion

From the image above, we can see that all columns are changed to General Number format, but if values are stored as numbers they will be shown as they are stored: Date values are shown as Excel serials (numbers), decimal values are shown with only one decimal digit, even if we changed the format to five digits before resetting the format to General.

In Brief, you cannot handle how the values are shown when the Number Format is "General", if you need to show values as dates you have to set the number format to yyyy-MM-dd or any other date format.

Reference

C# Accessing EXCEL, formatting cell as General

Original Post: https://stackoverflow.com/questions/57460180/c-sharp-script-in-ssis-script-task-to-convert-excel-column-in-text-format-to/57503046#57503046

Search This Blog

Data Developers