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