前言
在这我们要先了解Excel2003(xls)和Excel2007(xlsx)这两者的最大行列。其中
Excel2003的最大行列是65536行,256列,
Excel2007之后的版本最大行列是1048576行,16384列。
这里我们选择使用NPOI提供的SXSSFWorkbook方式解决导出百万数据Excel占内存和慢的问题
一、示例代码
这里做了个控制台例子,大家可以本地测试一遍,可以多测几组数据,只有数据量大时新模式才有优势。
using System;
using NPOI.SS.UserModel;
using NPOI.XSSF.Streaming;
using NPOI.XSSF.UserModel;
using System.IO;
namespace excelPro
{
class Program
{
static void Main(string[] args)
{
bool shouldContinue = true;
while (shouldContinue)
{
Console.WriteLine("请输入导出行数:");
string rowCountInput = Console.ReadLine();
int rowCount = Convert.ToInt32(rowCountInput);
Console.WriteLine("请输入导出列数:");
string columnCountInput = Console.ReadLine();
int columnCount = Convert.ToInt32(columnCountInput);
IWorkbook workbook = new SXSSFWorkbook(new XSSFWorkbook());
ISheet sheet = workbook.CreateSheet("新模式数据列表");
IRow rowNew = null;
var stopwatch = new System.Diagnostics.Stopwatch();
stopwatch.Start();
Console.WriteLine("----------------------------【新模式】开始执行--------------------------------");
//遍历组装串
for (var row = 0; row < rowCount; row++)
{
rowNew = sheet.CreateRow(row);
int index = 0;
//遍历属性的集合,创建DataTable
for (int column = 0; column < columnCount; column++)
{
rowNew.CreateCell(column).SetCellValue($"第{row}行{column}列");
index++;
}
}
Save("新模式.xlsx", workbook);
double allTimeByNew = stopwatch.ElapsedMilliseconds / 1000;
Console.WriteLine($"【新模式】执行用时:{allTimeByNew}秒");
Console.WriteLine("----------------------------【新模式】执行结束!!!--------------------------");
Console.WriteLine("------------------------------------------------------------------------------");
Console.WriteLine("------------------------------------------------------------------------------");
Console.WriteLine("------------------------------------------------------------------------------");
stopwatch.Restart();
IWorkbook workbookOld = new XSSFWorkbook();
ISheet sheetOld = workbookOld.CreateSheet("老模式数据列表");
IRow rowOld = null;
stopwatch.Start();
Console.WriteLine("----------------------------【老模式】开始执行--------------------------------");
//遍历组装串
for (var row = 0; row < rowCount; row++)
{
rowOld = sheetOld.CreateRow(row);
int index = 0;
//遍历属性的集合,创建DataTable
for (int column = 0; column < columnCount; column++)
{
rowOld.CreateCell(column).SetCellValue($"第{row}行{column}列");
index++;
}
}
Save("老模式.xlsx", workbookOld);
double allTimeByOld = stopwatch.ElapsedMilliseconds / 1000;
Console.WriteLine($"【老模式】执行用时:{allTimeByOld}秒");
Console.WriteLine("----------------------------【老模式】执行结束!!!--------------------------");
// 询问是否继续循环
Console.WriteLine("是否继续执行?(y/n)");
string input = Console.ReadLine();
// 根据输入决定是否继续循环
shouldContinue = (input.ToLower() == "y");
}
Console.WriteLine("程序结束!");
Console.ReadKey();
}
/// <summary>
/// 保存
/// </summary>
/// <param name="fullName"></param>
/// <param name="workbook"></param>
private static void Save(string fullName, IWorkbook workbook)
{
FileStream file = null;
System.IO.FileInfo fileInfo = new FileInfo(fullName);
string dreict = fileInfo.DirectoryName;
if (!Directory.Exists(dreict))
{
Directory.CreateDirectory(dreict);
}
try
{
file = new FileStream(fullName, FileMode.Create);
workbook.Write(file, true);
}
finally
{
if (file != null)
{
file.Close();
file.Dispose();
}
}
}
}
}
二、情景实战
情景业务实战后续会更新,这段时间我会抽空更新,(2023年7月11日留)
aa
十万行数据模拟导出用时
三、致谢
感谢大家的阅读与支持,祝大家生活愉快!