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 using NPOI.SS.Util; 11 12 namespace Common 13 { 14 public class ExcelHelper : IDisposable 15 { 16 private string fileName = null; //文件名 17 private IWorkbook workbook = null; 18 private FileStream fs = null; 19 private bool disposed; 20 21 public ExcelHelper(string fileName) 22 { 23 this.fileName = fileName; 24 disposed = false; 25 } 26 /// <summary> 27 /// 生成Sheet对象 28 /// </summary> 29 /// <param name="fileName">导出Excel文件完整文件名</param> 30 /// <param name="sheetName">要导入的excel的sheet的名称</param> 31 /// <returns></returns> 32 public ISheet GenerateSheet(string sheetName) 33 { 34 ISheet sheet = null; 35 36 fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite); 37 if (fileName.IndexOf(".xlsx") > 0) // 2007版本 38 workbook = new XSSFWorkbook(); 39 else if (fileName.IndexOf(".xls") > 0) // 2003版本 40 workbook = new HSSFWorkbook(); 41 if (workbook != null) 42 { 43 sheet = workbook.CreateSheet(sheetName); 44 } 45 return sheet; 46 } 47 /// <summary> 48 /// 将DataTable数据导入到excel中 49 /// </summary> 50 /// <param name="sheet">所要操作Excel的Sheet对象</param> 51 /// <param name="data">要导入的数据</param> 52 /// <param name="dataRowStart">数据起始行索引</param> 53 /// <returns>导入数据行数(包含列名那一行)</returns> 54 public int DataTableToExcel(ISheet sheet, DataTable data, int dataRowStart) 55 { 56 int i = 0; 57 int j = 0; 58 try 59 { 60 if (sheet != null) 61 { 62 for (i = 0; i < data.Rows.Count; ++i) 63 { 64 IRow row = sheet.CreateRow(dataRowStart); 65 for (j = 0; j < data.Columns.Count; ++j) 66 { 67 row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString()); 68 } 69 ++dataRowStart; 70 } 71 workbook.Write(fs); //写入到excel 72 } 73 return dataRowStart; 74 } 75 catch (Exception ex) 76 { 77 Console.WriteLine("Exception: " + ex.Message); 78 return -1; 79 } 80 } 81 82 /// <summary> 83 /// 将excel中的数据导入到DataTable中 84 /// </summary> 85 /// <param name="sheetName">excel工作薄sheet的名称</param> 86 /// <param name="dataRowStart">数据起始行索引</param> 87 /// <returns>返回的DataTable</returns> 88 public DataTable ExcelToDataTable(DataTable tableModel, string sheetName, int dataRowStart) 89 { 90 ISheet sheet = null; 91 DataTable data = tableModel.Clone(); 92 try 93 { 94 fs = new FileStream(fileName, FileMode.Open, FileAccess.Read); 95 if (fileName.IndexOf(".xlsx") > 0) // 2007版本 96 workbook = new XSSFWorkbook(fs); 97 else if (fileName.IndexOf(".xls") > 0) // 2003版本 98 workbook = new HSSFWorkbook(fs); 99 100 if (sheetName != null) 101 { 102 sheet = workbook.GetSheet(sheetName); 103 if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet 104 { 105 sheet = workbook.GetSheetAt(0); 106 } 107 } 108 else 109 { 110 sheet = workbook.GetSheetAt(0); 111 } 112 if (sheet != null) 113 { 114 IRow firstRow = sheet.GetRow(dataRowStart); 115 int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数 116 //最后一列的标号 117 int rowCount = sheet.LastRowNum; 118 for (int i = dataRowStart; i <= rowCount; ++i) 119 { 120 IRow row = sheet.GetRow(i); 121 if (row == null) continue; //没有数据的行默认是null 122 123 DataRow dataRow = data.NewRow(); 124 for (int j = row.FirstCellNum; j < cellCount; ++j) 125 { 126 if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null 127 dataRow[j] = row.GetCell(j).ToString(); 128 } 129 data.Rows.Add(dataRow); 130 } 131 } 132 133 return data; 134 } 135 catch (Exception ex) 136 { 137 Console.WriteLine("Exception: " + ex.Message); 138 return null; 139 } 140 } 141 /// <summary> 142 /// 设置表头(支持单元格合并) 143 /// </summary> 144 /// <param name="sheet">要合并单元格所在的sheet</param> 145 /// <param name="mergeArrayList">需要进行单元格合并的单元格索引数组,数组长度为4,第一个为行起始位置,第二个为行结束为止,第三个为列起始位置,第四个为列结束为止</param> 146 /// <param name="valueList">单元格合并后值的集合,与合并单元格索引数组集合一一对应</param> 147 //public void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend, string value) 148 public void SetExcelHeader(ISheet sheet, IList<int[]> mergeArrayList, IList<string> valueList) 149 { 150 for (int m = 0; m < mergeArrayList.Count; m++) 151 { 152 if (mergeArrayList[m].Length > 4) 153 { 154 return; 155 } 156 int rowstart = mergeArrayList[m][0]; 157 int rowend = mergeArrayList[m][1]; 158 int colstart = mergeArrayList[m][2]; 159 int colend = mergeArrayList[m][3]; 160 string value = ""; 161 if (m < valueList.Count) 162 { 163 value = valueList[m]; 164 } 165 ICellStyle style = workbook.CreateCellStyle(); 166 style.Alignment = HorizontalAlignment.Center; 167 IFont font = workbook.CreateFont(); 168 font.Boldweight = 700; 169 font.FontHeight = 20 * 20; 170 style.SetFont(font); 171 for (int i = rowstart; i < rowend + 1; i++) 172 { 173 IRow row = sheet.GetRow(i); 174 if (row==null) 175 { 176 row = sheet.CreateRow(i); 177 } 178 for (int j = colstart; j < colend + 1; j++) 179 { 180 ICell cell = row.CreateCell(j); 181 cell.SetCellValue(value); 182 cell.CellStyle = style; 183 } 184 } 185 if (rowend - rowstart > 0 || colend - colstart > 0) 186 { 187 CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend); 188 sheet.AddMergedRegion(cellRangeAddress); 189 } 190 } 191 //workbook.Write(fs); 192 } 193 194 public void Dispose() 195 { 196 Dispose(true); 197 GC.SuppressFinalize(this); 198 } 199 200 protected virtual void Dispose(bool disposing) 201 { 202 if (!this.disposed) 203 { 204 if (disposing) 205 { 206 if (fs != null) 207 fs.Close(); 208 } 209 210 fs = null; 211 disposed = true; 212 } 213 } 214 } 215 }