c# npoi分批往excel追加数据
直接贴代码:
using DongYang.Core.Model.Domain; using DongYang.Core.Utils; using NLog; using NPOI.XSSF.UserModel; using System; using System.Collections.Generic; using System.Data; using System.Diagnostics; using System.IO; using System.Threading; namespace DongYang.Core.Service { public class DYTrackANODetail700013TodayNewService { private readonly Logger _logger = LogManager.GetCurrentClassLogger();//日志组件 /// <summary> /// 导出excel /// </summary> /// <param name="anomateexcels"></param> /// <param name="currentTime"></param> public void ExportToExcel(List<Anomateexcel> anomateexcels, DateTime currentTime) { Stopwatch sw = new Stopwatch(); sw.Start(); FileStream file = null; string strBeginTime = string.Empty;//查询开始时间 string strEndTime = string.Empty;//查询结束时间 try { //模板文件 string templateFileName = AppDomain.CurrentDomain.BaseDirectory + "\\template.xlsx"; //导出文件 string reportFileName = FileHelper.GetExportFilePath(currentTime) + $"\\{currentTime.ToString("yyyyMMdd_HHmmss")}.xlsx"; //获取查询日期 this.GetTime(currentTime, out strBeginTime, out strEndTime); //查询数据总数 int count = this.GetDataTableCount(strBeginTime, strEndTime); if (count == 0) return; //先拷贝空文件 File.Copy(templateFileName, reportFileName); //分页查询数据,在循环里面,打开拷贝的文件并追加数据,最后关闭文件句柄 var pages = Math.Ceiling(Convert.ToDouble(count) / ConfigHelper.PageSize); for (int pageIndex = 1; pageIndex <= pages; pageIndex++) { var startRow = (pageIndex - 1) * ConfigHelper.PageSize + 1; var endRow = pageIndex * ConfigHelper.PageSize; var dt = this.GetDataTable(strBeginTime, strEndTime, startRow, endRow); file = new FileStream(reportFileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite); XSSFWorkbook xssfworkbook = new XSSFWorkbook(file);//将文件读到内存,在内存中操作excel file.Close(); XSSFSheet xssfsheet = xssfworkbook.GetSheet(ConfigHelper.WorkSheetName) as XSSFSheet; var beginRow = 3 + startRow - 1;//这里的beginRow这么处理,是因为我的Excel标题是在第三行 for (var i = 0; i < dt.Rows.Count; i++) { var excelRow = xssfsheet.CreateRow(beginRow++); foreach (var anomateexcel in anomateexcels) { var excelCell = excelRow.CreateCell(anomateexcel.Excellist.ToInt()); excelCell.SetCellType(NPOI.SS.UserModel.CellType.String); var value = dt.Rows[i][anomateexcel.Anofield].ToString(); excelCell.SetCellValue(value); } } xssfsheet.ForceFormulaRecalculation = true; //将内存数据写到文件 using (FileStream fs = File.OpenWrite(reportFileName)) { xssfworkbook.Write(fs); xssfworkbook.Close(); } Thread.Sleep(100); } } catch (Exception ex) { _logger.Error($"导出数据出错,message:{ex.Message},stackTrace:{ex.StackTrace}"); } finally { if (file != null) file.Close(); } sw.Stop(); _logger.Info($"日期:{currentTime},耗时{sw.Elapsed.TotalSeconds}秒"); } #region 获取开始时间和结束时间 /// <summary> /// 获取开始时间和结束时间 /// </summary> /// <param name="currentTime"></param> /// <param name="strBeginTime"></param> /// <param name="strEndTime"></param> private void GetTime(DateTime currentTime, out string strBeginTime, out string strEndTime) { DateTime beginTime; DateTime endTime; if (currentTime < currentTime.Date.AddHours(7.5)) { beginTime = currentTime.Date.AddDays(-1).AddHours(7.5);//前天7:30 endTime = currentTime.Date.AddHours(7.5);//当天的7:30 } else { beginTime = currentTime.Date.AddHours(7.5); endTime = currentTime.Date.AddDays(1).AddHours(7.5); } strBeginTime = beginTime.ToString("yyyy-MM-dd HH:mm"); strEndTime = endTime.ToString("yyyy-MM-dd HH:mm"); } #endregion #region 查询数据 /// <summary> /// 查询数据 /// </summary> /// <param name="strBeginTime"></param> /// <param name="strEndTime"></param> /// <param name="startRow"></param> /// <param name="endRow"></param> /// <returns></returns> private DataTable GetDataTable(string strBeginTime, string strEndTime, int startRow, int endRow) { var sql = $@" select * from ( select row_number() over(order by A asc,AB asc,AC asc) as rownumber, * from {ConfigHelper.ExcelExportTableName} where E between '{strBeginTime}' and '{strEndTime}' ) as t where rownumber between {startRow} and {endRow} "; DataTable dt = DapperSqlHelper.QueryDataTable(sql); return dt; } #endregion #region 查询总数 /// <summary> /// 查询总数 /// </summary> /// <param name="strBeginTime"></param> /// <param name="strEndTime"></param> /// <returns></returns> private int GetDataTableCount(string strBeginTime, string strEndTime) { var sql = $"select count(1) from {ConfigHelper.ExcelExportTableName} where E between '{strBeginTime}' and '{strEndTime}'"; DataTable dt = DapperSqlHelper.QueryDataTable(sql); var count = Convert.ToInt32(dt.Rows[0][0]); return count; } #endregion } }
一开始是一次性读取符合条件的数据,因为数据量大,大概七八千条,而且每条记录400个字段。所以后面优化成了分页读取,每次取100条数据,然后再往excel插入数据。