SSIS脚本任务中的C#脚本转换“文本"中的Excel列格式为“一般" [英] C# Script in SSIS Script Task to convert Excel Column in "Text" Format to "General"
问题描述
我正在使用 SSIS 数据流任务将数据从 SQL Server 导出到 Excel.尽管导出格式，这里所有列都显示为文本.因此，我需要开发一个 SSIS 脚本任务来进行必要的转换.我在开发脚本时遇到了麻烦.
格式化前的 Excel 工作簿
看，Excel单元格没有撇号，数字类型也是常规"，但消息说此单元格中的数字格式为文本或前面有撇号
我尝试了互联网上可用的不同选项，但没有成功.
#region 命名空间使用系统；使用 System.Collections.Generic;使用 System.Linq；使用 System.Text;使用 System.Threading.Tasks;使用 System.Text.RegularExpressions;使用 System.Data;使用 Microsoft.SqlServer.Dts.Runtime；使用 System.Windows.Forms;使用 System.Runtime.InteropServices；使用 Excel = Microsoft.Office.Interop.Excel;#endregion命名空间 ST_de899f405b7b4083b0ad8cba6b3df2e3{[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]公共部分类 ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase{公共无效主(){string inputFile = (string)Dts.Variables["Target_FullFilePath"].Value;Excel.Application ExcelApp = new Excel.Application();Excel.Workbook ExcelWorkbook = ExcelApp.Workbooks.Open(inputFile);Excel.Range 格式Range;ExcelApp.Visible = true;foreach(ExcelWorkbook.Sheets 中的 Excel.Worksheet ExcelWorksheet){ExcelWorksheet.Select(Type.Missing);ExcelWorksheet.Columns[2].NumberFormat = "";ExcelWorksheet.Columns[3].NumberFormat = "";ExcelWorksheet.Columns[4].NumberFormat = "0.00000";ExcelWorksheet.Columns[5].NumberFormat = "yyyy-MM-dd";}ExcelWorkbook.Save();GC.Collect();GC.WaitForPendingFinalizers();ExcelWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);Marshal.FinalReleaseComObject(ExcelWorkbook);ExcelApp.退出();Marshal.FinalReleaseComObject(ExcelApp);}枚举脚本结果{成功 = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success，失败 = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure};#endregion}}预期结果:列编号 B、C、D 到 看起来像十进制/整数，并且也进行了类似的过滤.列 E 看起来像 Date 并且也类似地过滤.
这就是我希望 Excel 文件在通过 SSIS 格式化后的样子
我确认相应的列只有除列标题外的相关值.
在提供解决方案之前，我必须解释一些关于Excel数字格式的要点
什么是数字格式属性?
参考
我使用以下代码创建了一个控制台应用程序:
使用系统；使用 System.Collections.Generic;使用 System.Linq；使用 System.Text;使用 System.Threading.Tasks;使用 System.Text.RegularExpressions;使用 Excel = Microsoft.Office.Interop.Excel;使用 System.Runtime.InteropServices；命名空间 ConsoleApp1{课程计划{静态无效主(字符串 [] args){string inputFile = @"D:Test.xlsx";Excel.Application ExcelApp = new Excel.Application();Excel.Workbook ExcelWorkbook = ExcelApp.Workbooks.Open(inputFile);ExcelApp.Visible = true;foreach(ExcelWorkbook.Sheets 中的 Excel.Worksheet ExcelWorksheet){ExcelWorksheet.Select(Type.Missing);ExcelWorksheet.Columns[1].NumberFormat = "";ExcelWorksheet.Columns[2].NumberFormat = "yyyy-MM-dd";//将格式转换为日期ExcelWorksheet.Columns[2].NumberFormat = "";ExcelWorksheet.Columns[3].NumberFormat = "0.00000";//将格式转换为 5 位十进制数的十进制数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.退出();Marshal.FinalReleaseComObject(ExcelApp);}}}执行应用程序后，Excel 如下所示:
讨论与结论
从上图我们可以看到，所有的列都变成了通用数字格式，但是如果值存储为数字，它们将在存储时显示:日期值显示为 Excel 序列(数字)，十进制值显示只有一位十进制数字，即使我们在将格式重置为常规之前将格式更改为五位也是如此.
简而言之，当数字格式为常规"时，您无法处理值的显示方式，如果您需要将值显示为日期，则必须将数字格式设置为 yyyy-MM-dd 或任何其他日期格式.
参考
<小时>更新 1
不要使用 ExcelWorksheet.Columns[1].NumberFormat，而是尝试使用以下代码:
ExcelWorksheet.Cells[1,1].EntireColumn.NumberFormat = "";ExcelWorksheet.Cells[1,2].EntireColumn.NumberFormat = "";I am exporting Data from SQL Server to Excel, 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.
Excel Workbook before Formatting
See, the Excel Cell has no Apostrophe and the Number type is also "General" but the message says The number in this cell is formatted as text or preceded by an apostrophe
I have Tried different options available in the internet, but unsuccessfully.
#region Namespaces
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Text.RegularExpressions;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
#endregion
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[2].NumberFormat = "";
ExcelWorksheet.Columns[3].NumberFormat = "";
ExcelWorksheet.Columns[4].NumberFormat = "0.00000";
ExcelWorksheet.Columns[5].NumberFormat = "yyyy-MM-dd";
}
ExcelWorkbook.Save();
GC.Collect();
GC.WaitForPendingFinalizers();
ExcelWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
Marshal.FinalReleaseComObject(ExcelWorkbook);
ExcelApp.Quit();
Marshal.FinalReleaseComObject(ExcelApp);
}
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}
Expected Result: Columns numbered B, C, D to look like decimal/integer numbers and also similarly filtered. Column E to look like Date and also similarly filtered.
This is how I want Excel file to look like, after formatting through SSIS
I confirm the corresponding columns have relevant values only except column header.
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);
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
Update 1
Instead of using ExcelWorksheet.Columns[1].NumberFormat, try using the following code:
ExcelWorksheet.Cells[1,1].EntireColumn.NumberFormat = "";
ExcelWorksheet.Cells[1,2].EntireColumn.NumberFormat = "";
这篇关于SSIS脚本任务中的C#脚本转换“文本"中的Excel列格式为“一般"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
