SSIS 中将csv 文件批量导出到excel 文件，并设置excel 文件中某些列的data column format 为Text

 

csv 文件是文本文件类型，但是打开csv 文件后（默认使用本地已经安装的excel 来打开excel 文件），默认显示出来的是general 类型（column data format）的数据,

这样就有一个问题：如果csv 文件中单元格里存储的是这样的数据：00012345，那么在excel 中现实的确是：12345.

当然，如果使用 TXT来打开csv　文件的话，是可以正常现实出来"00012345" 的。

 

所以需求就是如何将csv文件中中的数据导出到excel 中（因为excel 可以有格式化的功能，将某列的 column data format设置为Text,而不是默认的General; 而csv　文件则没有类似格式化的功能），同时如果原来的csv 文件中有巨量　数据（超过100 万条记录）。

 

具体做法：

　 逐行读取csv 文件，保存到一个多维数组中，然后将数组赋值给excel.

 public void Main()
        {

            
            string fileName = this.ReadVariable("source_csv_file_path").Value.ToString();
         
           
            // 定义数组的最大容量
            int totalRecords = 300000;
           
            string excelFileName = fileName.Replace(".csv", ".xlsx");

             // 新建excel文件

            Microsoft.Office.Interop.Excel.Application statusExcel = new Microsoft.Office.Interop.Excel.Application();

            Workbook statusWorkbook = statusExcel.Application.Workbooks.Add(true);
            try
            {

                Worksheet wsStatusSheet = (Worksheet)statusWorkbook.Worksheets.Add(statusWorkbook.Sheets[1], Type.Missing, Type.Missing, Type.Missing);

                ((Worksheet)statusWorkbook.Sheets["Sheet1"]).Delete();

                wsStatusSheet.Name = "destinationExcelFile";

                // 原来的CSV 中有５列，比如现在需要设置excel中第一列的 column data format为Text

                Range firstHeaderRow = wsStatusSheet.get_Range("A1:E1", System.Type.Missing);

                
                Range statusSheetSSNColumn = wsStatusSheet.get_Range("A:A", System.Type.Missing);
                statusSheetSSNColumn.EntireColumn.Cells.NumberFormat = "@";

              

                int excelCurrRow = 0;

                // 逐行读取csv文件
                using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
                {
                    StreamReader reader = new StreamReader(fs, System.Text.Encoding.UTF8);
                    string line = "";

                    string[] colArray = null;
                    object[,] cellData = new object[totalRecords, 5];
                    while ((line = reader.ReadLine()) != null)
                    {
                        colArray = line.Split('\t');

                        for (int iCol = 0; iCol < 5; iCol++)
                        {
                            cellData[excelCurrRow, iCol] = colArray[iCol];
                        }
                        excelCurrRow++;
                    }

                    // 将数组cellData［，］中的值批量赋给excel
                    wsStatusSheet.get_Range(wsStatusSheet.Cells[1, 1], wsStatusSheet.Cells[excelCurrRow, ５]).set_Value(Type.Missing, cellData);
                }

                
                // 保存excel
                object missing = System.Reflection.Missing.Value;

                statusWorkbook.SaveAs(excelFileName, XlFileFormat.xlOpenXMLWorkbook, missing, missing, false, false, XlSaveAsAccessMode.xlNoChange,
       XlSaveConflictResolution.xlUserResolution, true, missing, missing, missing);

                statusWorkbook.Close(null, excelFileName, null);
                statusExcel.Quit();

             
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception ex)
            {
                statusWorkbook.Close(null, excelFileName, null);
                statusExcel.Quit();
            }
        }

 

 最终生成的excel 中的内容类似这样：

代码	名称	类别	日期	产地
0018338152	苹果	水果	20190124	烟台
0018338160	梨子	水果	20190124	莱阳
0018338186	水杯	家居用品	20190124	广东珠海
0018338228	小米手机	电子产品	20190124	北京
0018338236	荣耀手表	电子产品	20190124	上海