NPOI操作Execel

【核心代码】

#region 把Sheet中的数据转换为DataTable

        /// <summary>
        /// 把Sheet中的数据转换为DataTable
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        private static DataTable ExportToDataTable(ISheet sheet)
        {
            DataTable dt = new DataTable();

            //默认，第一行是字段
            IRow headRow = sheet.GetRow(0);

            //设置datatable字段
            for (int i = headRow.FirstCellNum, len = headRow.LastCellNum; i < len; i  )
            {
                dt.Columns.Add(headRow.Cells[i].StringCellValue);
            }
            //遍历数据行
            for (int i = (sheet.FirstRowNum   1), len = sheet.LastRowNum   1; i < len; i  )
            {
                IRow tempRow = sheet.GetRow(i);
                DataRow dataRow = dt.NewRow();

                //遍历一行的每一个单元格
                for (int r = 0, j = tempRow.FirstCellNum, len2 = tempRow.LastCellNum; j < len2; j  , r  )
                {
                    ICell cell = tempRow.GetCell(j);

                    if (cell != null)
                    {
                        switch (cell.CellType)
                        {
                            case CellType.STRING:
                                dataRow[r] = cell.StringCellValue;
                                break;
                            case CellType.NUMERIC:
                                dataRow[r] = cell.NumericCellValue;
                                break;
                            case CellType.BOOLEAN:
                                dataRow[r] = cell.BooleanCellValue;
                                break;
                            /*default: dataRow[r] = "";
                                break;*/
                        }
                    }
                }
                if (!dataRow.IsNull(dt.Columns[0]))
                    dt.Rows.Add(dataRow);
            }
            return dt;
        }

        #endregion