格式化SSIS.net脚本任务中的Excel列

Question

我有一个构建Excel文件的脚本任务。业务需要以特定方式格式化某些列。代码如下，我很好奇是否有一种方法可以使用现有的流程来格式化列，或者一旦创建并加载它，就可以格式化列？

更新：我决定让OLE进程做它自己的事情，创建文件，然后编写一些Interop代码来格式化文件，但似乎OLE进程正在锁定文件，可能Excel只是在后台保持打开状态。我找不到任何其他方法来确保OLE连接完全关闭。我还试着把互操作部分放在另一个任务中，但在打开文件时出现了某种版本错误。

错误：System.Runtime.InteropServices.COMException(0x800A03EC(：无法访问"TA_Enrollment__106648_20210518.xlsx"。位于Microsoft.Office.Interop.Excel_Workbook.SaveAs(对象文件名，对象文件格式，Object Password，Object WriteResPassword，Object ReadOnlyRecommended，Object CreateBackup，XlSaveAsAccessMode AccessMode，对象冲突解决方案、对象AddToMru、对象文本代码页、对象文本可视化布局、对象本地(

如有任何协助，我们将不胜感激。

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
}
}

Answer 1

基于您提供的C#代码，脚本任务使用System.Data.Oledb命名空间类(OledbCommand，OledbConnection(连接到Excel文件。OLE DB将Excel文件作为关系数据库处理。例如，它强制每列使用一种数据类型，而Excel允许每列使用多种数据类型。

也许使用System.Data.Oledb命名空间类是从Excel中读取数据的最简单方法，但它有很多局限性，并且仅用于执行CRUD操作。若要更改单元格的格式，应使用Microsoft.Office.Interop.Excel或第三方库。

Interop.Excel入门

• 如何访问Office互操作对象(C#编程指南(
• C#Excel互操作示例
• C#Excel教程

使用Interop.Excel格式化Excel单元格

• 如何使用C格式化Excel文件#