/// <summary> /// 将数据生成为excel文件 /// <param name="info">配置信息</param> /// <param name="fileName">文件名</param> /// </summary> public void WriteWorkbook(SheetInfo info, string fileName) { ISheet sheet = null; XSSFWorkbook workbook1 = null; HSSFWorkbook workbook2 = null; try { //创建工作簿 if (string.IsNullOrEmpty(info.TemplateFile) == true) { //不使用模板 if (fileName.IndexOf(".xlsx") > 0) { workbook1 = new XSSFWorkbook(); sheet = workbook1.CreateSheet(info.SheetName); } else { workbook2 = new HSSFWorkbook(); sheet = workbook2.CreateSheet(info.SheetName); } } else { using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read)) { //使用模板 if (fileName.IndexOf(".xlsx") > 0) { workbook1 = new XSSFWorkbook(fs); sheet = workbook1.GetSheet(info.SheetName); } else { workbook2 = new HSSFWorkbook(fs); sheet = workbook2.GetSheet(info.SheetName); } } File.Delete(fileName); } //写入文件 if (string.IsNullOrEmpty(info.TemplateFile) == true) { FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite); try { #region 无模板 //写入列标题 IRow headRow = sheet.CreateRow(0); for (int i = 0; i < info.ColInfos.Count; i++) { headRow.CreateCell(i).SetCellValue(info.ColInfos[i].ColumnTitle); headRow.Cells[i].CellStyle.Alignment = HorizontalAlignment.Center; if (info.ColInfos[i].ColumnWidth != 0) { sheet.SetColumnWidth(i, Convert.ToInt32((info.ColInfos[i].ColumnWidth / 3) * 0.4374 * 256)); } } //写入行数据 int rowsNum = 1; IRow dataRow = null; foreach (List <CellInfo> items in info.DataArray) { dataRow = sheet.CreateRow(rowsNum); for (int i = 0; i < items.Count; i++) { dataRow.CreateCell(i).SetCellValue(items[i].Value); } rowsNum++; } #endregion info.DataArray.Clear(); info.Dispose(); if (fileName.IndexOf(".xlsx") > 0) { workbook1.Write(fs); } else { workbook2.Write(fs); } } catch (Exception ex) { throw ex; } finally { fs.Close(); fs.Dispose(); GC.Collect(); } } else { FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite); try { #region 模板 int rowIndex = 0; List <ICell> firstCells = new List <ICell>(); int count = info.DataArray.Count; for (int k = 0; k < count; k++) { List <CellInfo> items = info.DataArray[0] as List <CellInfo>; rowIndex++; for (int i = 0; i < items.Count; i++) { IRow row = sheet.GetRow(int.Parse(items[i].YPosition)); if (row == null) { row = sheet.CreateRow(int.Parse(items[i].YPosition)); } ICell cell = row.GetCell(int.Parse(items[i].XPosition)); if (rowIndex == 1) { firstCells.Add(cell); } if (cell == null) { cell = row.CreateCell(int.Parse(items[i].XPosition)); } if (firstCells[i] != null) { cell.CellStyle = firstCells[i].CellStyle; } if (items[i].Value != "") { switch (items[i].DataType.ToLower()) { case "datetime": cell.SetCellValue(DateTime.Parse(items[i].Value)); break; case "number": cell.SetCellValue(double.Parse(items[i].Value)); break; default: cell.SetCellValue(items[i].Value); break; } } } items.Clear(); items = null; info.DataArray.RemoveAt(0); #region 分段保存 if ((rowIndex % 20000) == 0) { if (fileName.IndexOf(".xlsx") > 0) { workbook1.Write(fs); } else { workbook2.Write(fs); } fs.Close(); fs.Dispose(); GC.Collect(); if (k <= count - 1) { fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite); } } #endregion } #endregion info.DataArray.Clear(); info.Dispose(); if (fileName.IndexOf(".xlsx") > 0) { workbook1.Write(fs); } else { workbook2.Write(fs); } } catch (Exception ex) { throw ex; } finally { if (fs != null) { fs.Close(); fs.Dispose(); GC.Collect(); } } } } catch (Exception ex) { throw ex; } }