I have a script task that builds an Excel file. The business needs certain columns formatted in certain ways. The code is below, and I am curious if there is a way to use the existing process to format the columns, or if once it is created and loaded, to then format the columns?
Update: What I decided to do is let the OLE process do it's thing and create the file, then write some Interop code to format the file, but it seems that the OLE process is locking the file, possibly with Excel just remaining open in the background. I cannot find any different way to make sure the OLE connection is completely closed. I also tried putting the Interop part in a different task but I got some kind of version error when trying to open the file.
Error: System.Runtime.InteropServices.COMException (0x800A03EC): Cannot access 'TA_Enrollment__106648_20210518.xlsx'. at Microsoft.Office.Interop.Excel._Workbook.SaveAs(Object Filename, Object FileFormat, Object Password, Object WriteResPassword, Object ReadOnlyRecommended, Object CreateBackup, XlSaveAsAccessMode AccessMode, Object ConflictResolution, Object AddToMru, Object TextCodepage, Object TextVisualLayout, Object Local)
Any assistance would be greatly appreciated.
namespace ST_62506028876e4b65a061b3af2dd116ff
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
try
{
//Declare Variables
string EnrollmentExcelName = Dts.Variables["User::EnrollmentExcelName"].Value.ToString();
string EnrollmentExcelFolderPath = Dts.Variables["User::EnrollmentExcelFolderPath"].Value.ToString();
string EnrollmentTable = Dts.Variables["User::EnrollmentTable"].Value.ToString();
string EnrollmentExcelDataSheet = Dts.Variables["User::EnrollmentExcelDataSheet"].Value.ToString();
string FileName = Dts.Variables["User::TRANSAM_Elig_var"].Value.ToString();
int startIndex = 3;
int length = 6;
String substring = FileName.Substring(startIndex, length);
EnrollmentExcelName = EnrollmentExcelName + "_" + substring + "_" + datetime;
string sql1 = @"SELECT BLAH BLAH BLAH FROM ";
string sql2 = @" ORDER BY SSN,SORTORDER";
OleDbConnection Excel_OLE_Con = new OleDbConnection();
OleDbCommand Excel_OLE_Cmd = new OleDbCommand();
//Construct ConnectionString for Excel
string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + EnrollmentExcelFolderPath + EnrollmentExcelName
+ ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";
//drop Excel file if exists
File.Delete(EnrollmentExcelFolderPath + "\\" + EnrollmentExcelName + ".xlsx");
//USE ADO.NET Connection from SSIS Package to get data from table
SqlConnection myADONETConnection = new SqlConnection();
myADONETConnection = (SqlConnection)(Dts.Connections["ADO_DataFeed_DB"].AcquireConnection(Dts.Transaction) as SqlConnection);
//Load Data into DataTable from SQL ServerTable
// Assumes that connection is a valid SqlConnection object.
// ... doing data stuff and things...
}
Excel_OLE_Con.Close();
Dts.TaskResult = (int)ScriptResults.Success;
// Formatting the file once closed...
Microsoft.Office.Interop.Excel.Application excelApplicaiton = new Microsoft.Office.Interop.Excel.Application();
excelApplicaiton.Visible = false;
excelApplicaiton.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Workbook excelWorkbook =
excelApplicaiton.Workbooks.Add(Type.Missing);
excelWorkbook = excelApplicaiton.Workbooks.Open(Filename: EnrollmentExcelFolderPath + "\\" + EnrollmentExcelName + ".xlsx");
// Format columns to numeric B,L,M,S,T,AA,AB,AH,AJ,AK and AM
Range cells = excelWorkbook.Worksheets[1].Cells;
cells[1, 2].EntireColumn.NumberFormat = "#";
cells[1, 12].EntireColumn.NumberFormat = "#";
cells[1, 13].EntireColumn.NumberFormat = "#";
cells[1, 19].EntireColumn.NumberFormat = "#";
cells[1, 20].EntireColumn.NumberFormat = "#";
cells[1, 27].EntireColumn.NumberFormat = "#";
cells[1, 28].EntireColumn.NumberFormat = "#";
cells[1, 34].EntireColumn.NumberFormat = "#";
cells[1, 36].EntireColumn.NumberFormat = "#";
cells[1, 37].EntireColumn.NumberFormat = "#";
cells[1, 39].EntireColumn.NumberFormat = "#";
excelWorkbook.SaveAs(EnrollmentExcelFolderPath + "\\" + EnrollmentExcelName + ".xlsx",
Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excelWorkbook.Close();
excelApplicaiton.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApplicaiton);
}
catch (Exception exception)
{
// Create Log File for Errors
using (StreamWriter sw = File.CreateText(Dts.Variables["User::EnrollmentExcelFolderPath"].Value.ToString() + "\\" +
Dts.Variables["User::EnrollmentExcelName"].Value.ToString() + datetime + ".log"))
{
sw.WriteLine(exception.ToString());
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
}
#region ScriptResults declaration
/// <summary>
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
///
/// This code was generated automatically.
/// </summary>
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}
Based on the C# code you provided, the Script Task is using the System.Data.Oledb namespace classes (OledbCommand, OledbConnection) to connect to the Excel files. OLE DB handles Excel files as a relational database. As an example, it forces a data type for each column while Excel allows multiple data types in each column.
Maybe using System.Data.Oledb namespace classes are the easiest way to read data from Excel, but it has many limitations and it is only used to perform CRUD operations. To change the formatting of the cells, you should use Microsoft.Office.Interop.Excel or a Third-party library.