你的位置:首页 > ASP.net教程

[ASP.net教程]NPOI帮助类(Excel转DataTable、DataTable转Excel)


 1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using NPOI.SS.UserModel; 6 using NPOI.XSSF.UserModel; 7 using NPOI.HSSF.UserModel; 8 using System.IO; 9 using System.Data; 10  11 namespace NetUtilityLib 12 { 13   public class ExcelHelper : IDisposable 14   { 15     private string fileName = null; //文件名 16     private IWorkbook workbook = null; 17     private FileStream fs = null; 18     private bool disposed; 19  20     public ExcelHelper(string fileName) 21     { 22       this.fileName = fileName; 23       disposed = false; 24     } 25  26     /// <summary> 27     /// 将DataTable数据导入到excel中 28     /// </summary> 29     /// <param name="data">要导入的数据</param> 30     /// <param name="isColumnWritten">DataTable的列名是否要导入</param> 31     /// <param name="sheetName">要导入的excel的sheet的名称</param> 32     /// <returns>导入数据行数(包含列名那一行)</returns> 33     public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten) 34     { 35       int i = 0; 36       int j = 0; 37       int count = 0; 38       ISheet sheet = null; 39  40       fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite); 41       if (fileName.IndexOf(".xlsx") > 0) // 2007版本 42         workbook = new XSSFWorkbook(); 43       else if (fileName.IndexOf(".xls") > 0) // 2003版本 44         workbook = new HSSFWorkbook(); 45  46       try 47       { 48         if (workbook != null) 49         { 50           sheet = workbook.CreateSheet(sheetName); 51         } 52         else 53         { 54           return -1; 55         } 56  57         if (isColumnWritten == true) //写入DataTable的列名 58         { 59           IRow row = sheet.CreateRow(0); 60           for (j = 0; j < data.Columns.Count; ++j) 61           { 62             row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName); 63           } 64           count = 1; 65         } 66         else 67         { 68           count = 0; 69         } 70  71         for (i = 0; i < data.Rows.Count; ++i) 72         { 73           IRow row = sheet.CreateRow(count); 74           for (j = 0; j < data.Columns.Count; ++j) 75           { 76             row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString()); 77           } 78           ++count; 79         } 80         workbook.Write(fs); //写入到excel 81         return count; 82       } 83       catch (Exception ex) 84       { 85         Console.WriteLine("Exception: " + ex.Message); 86         return -1; 87       } 88     } 89  90     /// <summary> 91     /// 将excel中的数据导入到DataTable中 92     /// </summary> 93     /// <param name="sheetName">excel工作薄sheet的名称</param> 94     /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param> 95     /// <returns>返回的DataTable</returns> 96     public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn) 97     { 98       ISheet sheet = null; 99       DataTable data = new DataTable();100       int startRow = 0;101       try102       {103         fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);104         if (fileName.IndexOf(".xlsx") > 0) // 2007版本105           workbook = new XSSFWorkbook(fs);106         else if (fileName.IndexOf(".xls") > 0) // 2003版本107           workbook = new HSSFWorkbook(fs);108 109         if (sheetName != null)110         {111           sheet = workbook.GetSheet(sheetName);112           if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet113           {114             sheet = workbook.GetSheetAt(0);115           }116         }117         else118         {119           sheet = workbook.GetSheetAt(0);120         }121         if (sheet != null)122         {123           IRow firstRow = sheet.GetRow(0);124           int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数125 126           if (isFirstRowColumn)127           {128             for (int i = firstRow.FirstCellNum; i < cellCount; ++i)129             {130               ICell cell = firstRow.GetCell(i);131               if (cell != null)132               {133                 string cellValue = cell.StringCellValue;134                 if (cellValue != null)135                 {136                   DataColumn column = new DataColumn(cellValue);137                   data.Columns.Add(column);138                 }139               }140             }141             startRow = sheet.FirstRowNum + 1;142           }143           else144           {145             startRow = sheet.FirstRowNum;146           }147 148           //最后一列的标号149           int rowCount = sheet.LastRowNum;150           for (int i = startRow; i <= rowCount; ++i)151           {152             IRow row = sheet.GetRow(i);153             if (row == null) continue; //没有数据的行默认是null       154 155             DataRow dataRow = data.NewRow();156             for (int j = row.FirstCellNum; j < cellCount; ++j)157             {158               if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null159                 dataRow[j] = row.GetCell(j).ToString();160             }161             data.Rows.Add(dataRow);162           }163         }164 165         return data;166       }167       catch (Exception ex)168       {169         Console.WriteLine("Exception: " + ex.Message);170         return null;171       }172     }173 174     public void Dispose()175     {176       Dispose(true);177       GC.SuppressFinalize(this);178     }179 180     protected virtual void Dispose(bool disposing)181     {182       if (!this.disposed)183       {184         if (disposing)185         {186           if (fs != null)187             fs.Close();188         }189 190         fs = null;191         disposed = true;192       }193     }194   }195 }