使用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();
            }
        }
    }

 

posted @ 2015-06-03 10:37  悦光阴  阅读(734)  评论(0编辑  收藏