使用NPOI包2.5.4版
思路:简单的导入导出，就是一表、一行、一单元格读写的过程
读取分三种常见情况,1.一次读取全部表的全部数据2.读取单表的所有数据3.按指定数量读取
写入分两种情况,1.一次写入全部数据2.按指定数量写入
使用的时候只需要继承导入或者导出类,并实现数据转换方法,简单的导入导出就OK了
导出的时候用了新版NPOI缓存新方法,不用在怕海量导出时候内存爆炸,新问题是.....C盘要大不然临时缓存会让C盘爆炸
PS:如果是用EF读取数据库的朋友,建议引用Microsoft.EntityFrameworkCore然后再查询的时候调用一下AsNoTracking()方法取消追踪,减少内存占用.
1 /// <summary> 2 /// 需要处理Excel数据导入,继承本类并实现方法 3 /// </summary> 4 public abstract class ExcelImport 5 { 6 ISheet _sheet = null; 7 IWorkbook _workbook = null; 8 int _numberOfSheets = 0; 9 int _sheetsIndexByCount = 0; 10 int _sheetsIndex = 0; 11 int _rowIndex = 0; 12 bool _skipFirst = false; 13 public ExcelImport(string filePath, bool skipFirst = false) 14 { 15 var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read); 16 if (filePath.IndexOf(".xlsx") > 0) // 2007版本 17 _workbook = new XSSFWorkbook(fs); 18 else if (filePath.IndexOf(".xls") > 0) // 2003版本 19 _workbook = new HSSFWorkbook(fs); 20 _numberOfSheets = _workbook.NumberOfSheets; 21 _skipFirst = skipFirst; 22 } 23 /// <summary> 24 /// 处理一行转成string集合的数据 25 /// 原始表格数据,需要重写处理方法 26 /// 返回数据类型必须和读取方法传参的T类型一致 27 /// 可以用{ typeof(T) == typeof(模型类) }判断,用于分别处理不同模型的数据 28 /// </summary> 29 protected abstract Object AnalysisData<T>(List<string> origin) where T : new(); 30 /// <summary> 31 /// 读取自定义数量数据 32 /// 单表数量不足,会读取下一张表 33 /// </summary> 34 public virtual List<T> ReadSheetByCount<T>(int readCount) where T : new() 35 { 36 List<T> data = new(); 37 if (_rowIndex == 0) _rowIndex = Convert.ToInt32(_skipFirst); 38 IRow dataRow; 39 int rowCount = 0;//最后一行的标号(标号从0开始) 40 if (_sheet != null) rowCount = _sheet.LastRowNum + 1; 41 do 42 { 43 if (_sheet == null || rowCount == _rowIndex) 44 { 45 if (_sheetsIndexByCount == _numberOfSheets) break; 46 _sheet = _workbook.GetSheetAt(_sheetsIndexByCount); 47 _sheetsIndexByCount++; 48 _rowIndex = Convert.ToInt32(_skipFirst); 49 rowCount = _sheet.LastRowNum + 1; 50 } 51 52 dataRow = _sheet.GetRow(_rowIndex); 53 _rowIndex++; 54 if (dataRow == null) continue; //没有数据的行默认是null 55 var origin = dataRow.Cells.Select(m => m.StringCellValue.ToTrim()).ToList(); 56 if (origin.Where(m => string.IsNullOrEmpty(m)).Count() == origin.Count) continue; //整行数据都为空 57 data.Add((T)AnalysisData<T>(origin)); 58 } while (readCount > data.Count); 59 60 return data; 61 } 62 /// <summary> 63 /// 处理单表数据 64 /// return null表示没有更多sheet可以读取了 65 /// </summary> 66 public virtual List<T> ReadNextSheet<T>() where T : new() 67 { 68 List<T> data = new(); 69 if (_sheetsIndex < _numberOfSheets) 70 { 71 _sheet = _workbook.GetSheetAt(_sheetsIndex); 72 _sheetsIndex++; 73 } 74 else { return null; } 75 76 int rowIndex = Convert.ToInt32(_skipFirst); 77 IRow dataRow; 78 int rowCount = _sheet.LastRowNum + 1;//最后一行的标号(标号从0开始) 79 do 80 { 81 dataRow = _sheet.GetRow(rowIndex); 82 rowIndex++; 83 if (dataRow == null) continue; //没有数据的行默认是null 84 var origin = dataRow.Cells.Select(m => m.StringCellValue.ToTrim()).ToList(); 85 if (origin.Where(m => string.IsNullOrEmpty(m)).Count() == origin.Count) continue; //整行数据都为空 86 data.Add((T)AnalysisData<T>(origin)); 87 } while (rowIndex < rowCount); 88 return data; 89 } 90 /// <summary> 91 /// 一次性处理全部sheet数据 92 /// *不建议,过量数据可能导致内存溢出 93 /// </summary> 94 public virtual List<T> ReadAllSheet<T>() where T : new() 95 { 96 List<T> data = new(); 97 do 98 { 99 data.AddRange(ReadNextSheet<T>()); 100 } while (_sheetsIndex < _numberOfSheets); 101 return data; 102 } 103 } 104 /// <summary> 105 /// 需要处理Excel数据导出,继承本类并实现方法 106 /// </summary> 107 public abstract class ExcelExport 108 { 109 SXSSFWorkbook _workbook;// 虚拟工作薄 110 SXSSFSheet _sheet;// 虚拟工作表 111 IRow _row; 112 ICell _newCell; 113 int _rowIndex = 0; 114 int _rowMax = 1024 * 1024;//默认单表最大行数 115 string _filePath; 116 117 ICellStyle Title { get; set; } 118 ICellStyle Cell { get; set; } 119 120 /// <summary> 121 /// 导出Excel 122 /// </summary> 123 /// <param name="filePath">保存文件路径(不带文件名)</param> 124 /// <param name="maxRowCount">单表最大数据行,默认1W条</param> 125 public ExcelExport(string directory, int maxRowCount = 10000) 126 { 127 _filePath = directory + "\" + Guid.NewGuid().ToString("N").ToLower() + ".xlsx"; 128 if (!Directory.Exists(directory)) Directory.CreateDirectory(directory); 129 if (System.IO.File.Exists(_filePath)) System.IO.File.Delete(_filePath); 130 // 需要先创建一个xlsx文件不然后面会报格式错误 131 using (FileStream fs = new FileStream(_filePath, FileMode.Create, FileAccess.ReadWrite)) 132 { 133 var wb = new XSSFWorkbook(); 134 wb.Write(fs); 135 wb.Close(); 136 } 137 138 _workbook = new SXSSFWorkbook(new XSSFWorkbook(), 500);// 创建一个xlsx格式的虚拟表,500表示每500条数据写入硬盘缓存,释放内存 139 140 if (maxRowCount <= _rowMax) _rowMax = maxRowCount; 141 142 Title = _workbook.CreateCellStyle(); 143 Title.Alignment = HorizontalAlignment.Center; 144 Title.VerticalAlignment = VerticalAlignment.Center; 145 IFont font = _workbook.CreateFont(); 146 font.Color = IndexedColors.Red.Index; 147 Title.SetFont(font); 148 149 Cell = _workbook.CreateCellStyle(); 150 Cell.Alignment = HorizontalAlignment.Justify;//两端自动对齐（自动换行） 151 Cell.VerticalAlignment = VerticalAlignment.Center; 152 } 153 /// <summary> 154 /// 处理一个实体对象 155 /// 将实体对象数据转换成导入Excel用得数据结构 156 /// 可以用{ typeof(T) == typeof(模型类) }判断 157 /// </summary> 158 protected abstract List<ExcelCell> ConvertData<T>(T origin) where T : new(); 159 /// <summary> 160 /// 写入数据(在虚拟工作薄中操作) 161 /// 单表写满,会创建新表 162 /// 如果出现转换失败的数据会出现在返回的List<T>集合中 163 /// </summary> 164 public virtual List<T> WriteByCount<T>(List<T> data, List<string> title = null) where T : new() 165 { 166 if (data == null && data.Count == 0) 167 return default; 168 169 List<T> error = new(); 170 int columnNum = 0, dataIndex = 0; 171 if (title == null) columnNum = ConvertData<T>(data[0]).Count; 172 else columnNum = title.Count; 173 174 do 175 { 176 if (_rowIndex == 0 || _rowIndex == _rowMax) 177 { 178 _sheet = _workbook.CreateSheetByAutoWidth(columnNum); 179 _rowIndex = 0; 180 if (title != null) 181 { 182 _row = _sheet.CreateRowByHeight(_rowIndex); 183 _rowIndex++; 184 for (int j = 0; j < title.Count; j++) 185 { 186 _newCell = _row.CreateCell(j); 187 _newCell.SetCellValue(title[j]); 188 _newCell.CellStyle = Title; 189 } 190 } 191 } 192 193 _row = _sheet.CreateRowByHeight(_rowIndex); 194 try 195 { 196 var cellData = ConvertData<T>(data[dataIndex]); 197 for (int i = 0; i < cellData.Count; i++) 198 { 199 _newCell = _row.CreateCell(i); 200 _newCell.CellStyle = Cell; 201 switch (cellData[i].CellType) 202 { 203 case ExcelCellType.Boolean: 204 _newCell.SetCellValue(bool.Parse(cellData[i].Value)); 205 break; 206 case ExcelCellType.DateTime: 207 _newCell.SetCellValue(DateTime.Parse(cellData[i].Value)); 208 break; 209 case ExcelCellType.Number: 210 _newCell.SetCellValue(double.Parse(cellData[i].Value)); 211 break; 212 case ExcelCellType.String: 213 _newCell.SetCellValue(cellData[i].Value); 214 break; 215 default: 216 _newCell.SetCellValue(cellData[i].Value); 217 break; 218 } 219 } 220 _rowIndex++; 221 } 222 catch 223 { 224 error.Add(data[dataIndex]); 225 } 226 finally 227 { 228 dataIndex++; 229 } 230 } while (data.Count > dataIndex); 231 return error; 232 } 233 /// <summary> 234 /// 单表写入数据(在虚拟工作薄中操作) 235 /// 数据总数超过单表最大的行数剩下部分不会写入 236 /// 如果出现转换失败的数据会出现在返回的List<T>集合中 237 /// </summary> 238 public virtual List<T> WriteBySheet<T>(List<T> data, List<string> title = null) where T : new() 239 { 240 if (data == null && data.Count == 0) 241 return default; 242 243 List<T> error = new(); 244 int columnNum = 0, dataIndex = 0, rowIndex = 0; 245 246 if (title == null) columnNum = ConvertData<T>(data[0]).Count; 247 else columnNum = title.Count; 248 249 _sheet = _workbook.CreateSheetByAutoWidth(columnNum); 250 if (title != null) 251 { 252 _row = _sheet.CreateRowByHeight(rowIndex); 253 rowIndex++; 254 for (int i = 0; i < title.Count; i++) 255 { 256 _newCell = _row.CreateCell(i); 257 _newCell.SetCellValue(title[i]); 258 _newCell.CellStyle = Title; 259 } 260 } 261 262 do 263 { 264 if (rowIndex == 1024 * 1024) break;// 超出单表最大行数 265 _row = _sheet.CreateRowByHeight(rowIndex); 266 try 267 { 268 var cellData = ConvertData<T>(data[dataIndex]); 269 for (int i = 0; i < cellData.Count; i++) 270 { 271 _newCell = _row.CreateCell(i); 272 _newCell.CellStyle = Cell; 273 switch (cellData[i].CellType) 274 { 275 case ExcelCellType.Boolean: 276 _newCell.SetCellValue(bool.Parse(cellData[i].Value)); 277 break; 278 case ExcelCellType.DateTime: 279 _newCell.SetCellValue(DateTime.Parse(cellData[i].Value)); 280 break; 281 case ExcelCellType.Number: 282 _newCell.SetCellValue(double.Parse(cellData[i].Value)); 283 break; 284 case ExcelCellType.String: 285 _newCell.SetCellValue(cellData[i].Value); 286 break; 287 default: 288 _newCell.SetCellValue(cellData[i].Value); 289 break; 290 } 291 } 292 rowIndex++; 293 } 294 catch { error.Add(data[dataIndex]); } 295 finally { dataIndex++; } 296 } while (data.Count > dataIndex); 297 return error; 298 } 299 /// <summary> 300 /// 完成写入获取导出文件路径,把硬盘上的临时文件数据打包成xlsx文件 301 /// </summary> 302 public virtual string WriteEnd() 303 { 304 using (FileStream fs = File.Open(_filePath, FileMode.Open)) 305 { 306 _workbook.Write(fs); 307 _workbook.Dispose(); 308 return _filePath; 309 } 310 } 311 } 312 /// <summary> 313 /// 扩展 314 /// </summary> 315 public static class ISheetExt 316 { 317 public static IRow CreateRowByHeight(this ISheet sheet, int rownum) 318 { 319 var row = sheet.CreateRow(rownum); 320 row.HeightInPoints = 30; 321 return row; 322 } 323 public static SXSSFSheet CreateSheetByWidth(this IWorkbook workbook, int columnnum, int width) 324 { 325 width = width * 256;// Excel表格宽度单位跟像素不一样 326 var sheet = (SXSSFSheet)workbook.CreateSheet(); 327 for (int i = 0; i < columnnum; i++) 328 sheet.SetColumnWidth(i, width); 329 return sheet; 330 } 331 public static SXSSFSheet CreateSheetByAutoWidth(this IWorkbook workbook, int columnnum) 332 { 333 var sheet = (SXSSFSheet)workbook.CreateSheet(); 334 for (int i = 0; i < columnnum; i++) 335 { 336 sheet.TrackColumnForAutoSizing(i); 337 sheet.AutoSizeColumn(i); 338 } 339 return sheet; 340 } 341 } 342 public class ExcelCell 343 { 344 public ExcelCellType CellType { get; set; } 345 public string Value { get; set; } 346 } 347 public enum ExcelCellType 348 { 349 Boolean, 350 DateTime, 351 Number, 352 String 353 }