2

I have an SSIS package which runs an SQL script and puts the results into an Excel file; the file at the beginning has just a header row with bolded column names.

What I have found is that, no matter how my data is formatted in my data flow tasks, my numbers end up going into the Excel file as text.

Could somebody please help me with a script task that will update the columns O and P to be displayed correctly as currency, with the £ symbol.

This is how they look after SSIS imports the data:

enter image description here

This is how I require them to look:

enter image description here

Hadi
  • 36,233
  • 13
  • 65
  • 124
Stew
  • 305
  • 2
  • 5
  • 14
  • You might be able to create a hidden row above your header that has the correct format. That might allow it to insert in the correct format. – Khal_Drogo Mar 05 '20 at 16:13

1 Answers1

3

You should use Mcirosot.Office.Interop.Excel.dll within a script task to change the column number format as following:

using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;

string inputFile = @"D:\Test.xlsx";

Excel.Application ExcelApp = new Excel.Application();
Excel.Workbook ExcelWorkbook = ExcelApp.Workbooks.Open(inputFile);
ExcelApp.Visible = false;
//O index is 15
ExcelWorksheet.Columns[15].NumberFormat = "£#,##0.00";
//P index is 16
ExcelWorksheet.Columns[16].NumberFormat = "£#,##0.00";
ExcelWorkbook.Save();

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

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

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

References

Hadi
  • 36,233
  • 13
  • 65
  • 124