使用NPOI读取Excel的数据，插入到DataTable中

笔者最近遇到一个项目，需要做一个SSIS Package，修改Excel的列名，并在ssms中创建job自动执行，项目需求比较简单，由于笔者做过的Package比较少，所以经历了一番曲折。

我首先想到的是使用NPOI，在Script task中使用NPOI.dll，但是在使用NPOI时，Script task不能将NPOI自动导入到.net framework，需要执行一个gacutil的脚本，有点麻烦，放弃。

其次，我想到 Microsoft.Office.Interop.Excel.dll ，这个类库是MS提供的用于操作Excel的类库，既然Microsoft.Office.Interop.Excel.dll是微软的东西，肯定是已经集成到.net framework中，免去了导入的麻烦。但是在Script Task中使用Microsoft.Office.Interop.Excel.dll仍然出错，这可能跟Script Task 的内部实现机制有关系，不允许创建Excel的process，放弃。

然后，我使用了Execute Process Task 来引用一个Console Application，在BIDS中调试运行是没有任何问题的，但是在SSMS中排job，job不能正常执行，返回异常，并且不能Kill Excel的Prcess，长久下去，Excel的process会耗尽所有的内存，放弃。

 最后，还是决定使用Execute Process Task来引用一个Console Application，使用NPOI来操作Excel，最终解决了问题。

 

以下示例代码就是使用NPOI的Console Application，是项目的一段测试代码的，主要实现的功能修改Excel的ColumnName，并从Excel的两个sheet中读取数据到DataTable中。

class Program
    {

        static Hashtable ht1 = new Hashtable();
        static Hashtable ht2 = new Hashtable();
        static DataTable dt = new DataTable("dt");
        static string strExcelPath = @"C:\ExcelFile\test.xlsx";
        static void Main(string[] args)
        {
            Console.Write("begin");

            GetDataFromExcel();

            Console.Write("end");
            System.Console.Read();
        }

        static void CreateDataTable()
        {
            dt.Columns.Add("XCode");
            dt.Columns.Add("DescriptionOrg");
            dt.Columns.Add("Country");

            dt.Columns.Add("tx");
            dt.Columns.Add("BG");
            dt.Columns.Add("Cell");
            dt.Columns.Add("Sheet", typeof(int));
        }

        public static void SetMapFromExcelHeadColumn(int sheet,string key,int keyIdx)
        {
            int idx = 0;

            if (sheet==1)
            {
                switch(key.Trim().ToLower())
                {
                    case "country":
                        idx = 2;
                        break;
                    case "x-code":
                        idx = 0;
                        break;
                    case "bg":
                        idx = 4;
                        break;
                    case "description":
                        idx = 1;
                        break;
                    case "tx":
                        idx = 3;
                        break;
                    default:
                        idx = 7;
                        break;
                }
                ht1.Add(keyIdx, idx);
            }
            else
            {
                switch (key.Trim().ToLower())
                {
                    case "country":
                        idx = 2;
                        break;
                    case "xcode":
                        idx = 0;
                        break;
                    case "cell":
                        idx = 5;
                        break;
                    default:
                        idx = 7;
                        break;
                }
                ht2.Add(keyIdx, idx);
            }
        }

        static public void GetDataFromExcel()
        {
            XSSFWorkbook workbook;

            using (FileStream file = new FileStream(strExcelPath, FileMode.Open, FileAccess.Read))
            {
                workbook = new XSSFWorkbook(file);
                file.Close();
                file.Dispose();
            }

            CreateDataTable();

            //read sheet1
            ISheet sheet = workbook.GetSheetAt(0);
           
            int iRowCnt, iCellCnt;
            iRowCnt = sheet.LastRowNum;
            //iCellCnt = sheet.GetRow(0).LastCellNum;
            iCellCnt = 9;

            for(int i=0;i<iCellCnt;i++)
            {
                SetMapFromExcelHeadColumn(1,sheet.GetRow(0).Cells[i].StringCellValue.Trim().ToLower(),i);
            }

            for (int i = 1; i < iRowCnt; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dr = dt.NewRow();
                for (int j = 0; j < iCellCnt; j++)
                {
                    ICell cell = row.GetCell(j);
                    if (cell == null)
                        continue;
                  
                    string strCellValue=cell.ToString();
                    int idx=(int)ht1[j];
                    if(idx<7)
                    {
                        dr[idx] = strCellValue;
                    }                                   
                }
                dr[6] = "1";
                dt.Rows.Add(dr);
            }

            //read sheet2
            sheet = workbook.GetSheetAt(1);
            iRowCnt = sheet.LastRowNum;
            //iCellCnt = sheet.GetRow(0).LastCellNum;
            iCellCnt = 4;

            for(int i=0;i<iCellCnt;i++)
            {
                SetMapFromExcelHeadColumn(2,sheet.GetRow(0).Cells[i].ToString().Trim().ToLower(),i+1);
            }

            for (int i = 1; i < iRowCnt; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dr = dt.NewRow();

                for (int j = 0; j < iCellCnt; j++)
                {
                    ICell cell = row.GetCell(j);
                    if (cell == null)
                        continue;

                    string strCellValue = cell.ToString();

                    int idx = (int)ht2[j];
                    if (idx < 7)
                    {
                        dr[idx] = strCellValue;
                    }  
                }
                dr[6] = "2";
                dt.Rows.Add(dr);
            }

            int iTotalRowCnt = dt.Rows.Count;
        }
        public static void UpdateExcelColumnNameSimplify()
        {
            XSSFWorkbook workbook;

            using (FileStream file = new FileStream(strExcelPath, FileMode.Open, FileAccess.ReadWrite))
            {
                workbook = new XSSFWorkbook(file);

                File.Delete(strExcelPath);
                file.Close();
                file.Dispose();
            }

            ISheet sheet = workbook.GetSheetAt(0);
            IRow row = sheet.GetRow(0);
            for (int j = 0; j < row.LastCellNum; j++)
            {
                ICell cell = row.GetCell(j);
                if (cell == null)
                    continue;

                cell.SetCellValue(cell.ToString().Trim());
            }

            sheet = workbook.GetSheetAt(1);
            row = sheet.GetRow(0);
            for (int j = 0; j < row.LastCellNum; j++)
            {
                ICell cell = row.GetCell(j);
                if (cell == null)
                    continue;

                cell.SetCellValue(cell.ToString().Trim());
            }

            using (FileStream file = new FileStream(strExcelPath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                workbook.Write(file);
                file.Close();
                file.Dispose();
            }
        }
    }