第28天_数据导出进阶_NPOI → .xlsx

第 {day} 天 · {title}

🎯 阶段定位：中级阶段第 {stage} 天。{desc}

📸 运行预览：{pv}

一、今日目标

二、核心概念

2.1 为什么选 NPOI

Microsoft.Office.Interop.Excel 需要在目标机器装 Excel，而且启动慢、资源释放麻烦。NPOI 纯 .NET 实现，无外部依赖，部署简单。

2.2 SXSSFWorkbook：写给大文件

普通 XSSFWorkbook 把整份文档放内存，十几万行就 OOM。SXSSFWorkbook 只保留滑动窗口内的行，其余刷到磁盘临时文件。

2.3 流式写 + 进度

写大文件要给用户反馈。NPOI 不提供进度回调，我们自己按行数算百分比，用 IProgress<int> 上报。

三、控件清单

四、完整代码

4.1 ExcelExporter.cs

csharp{r}using NPOI.SS.UserModel;{r}using NPOI.SS.Util;{r}using NPOI.XSSF.Streaming;{r}using System;{r}using System.Collections.Generic;{r}using System.IO;{r}using System.Linq;{r}using System.Threading;{r}using System.Threading.Tasks;{r}{r}namespace Day28_ExportExcel{r}{{{r} public sealed class ExcelExporter{r} {{{r} private static readonly string[] AllColumns = {{ "时间戳", "设备名称", "从站地址", "温度", "压力", "流量", "CRC 校验" }};{r}{r} public static List<string> DefaultColumns => new(AllColumns);{r}{r} public static async Task ExportAsync(string path, IEnumerable<string> columns,{r} DateTime start, DateTime end, bool mergeHeader, bool autoWidth,{r} IProgress<int>? progress, CancellationToken ct){r} {{{r} await Task.Run(() =>{r} {{{r} using var wb = new SXSSFWorkbook(null, 100, true, true); // 窗口 100 行{r} ISheet sheet = wb.CreateSheet("数据");{r} var headerStyle = wb.CreateCellStyle();{r} var font = wb.CreateFont();{r} font.IsBold = true;{r} headerStyle.SetFont(font);{r} headerStyle.Alignment = HorizontalAlignment.Center;{r}{r} int rowIndex = 0;{r} var headerRow = sheet.CreateRow(rowIndex++);{r} for (int c = 0; c < columns.Count(); c++){r} {{{r} var cell = headerRow.CreateCell(c);{r} cell.SetCellValue(columns.ElementAt(c));{r} cell.CellStyle = headerStyle;{r} }}{r} if (mergeHeader && columns.Count() > 1){r} sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, columns.Count() – 1));{r}{r} sheet.CreateFreezePane(0, 1); // 冻结首行{r}{r} // 模拟数据源 —— 实际替换为真实查询{r} var data = GenerateFakeData(start, end);{r} long total = data.Count, done = 0;{r} foreach (var rec in data){r} {{{r} ct.ThrowIfCancellationRequested();{r} var row = sheet.CreateRow(rowIndex++);{r} int c = 0;{r} if (columns.Contains("时间戳")) row.CreateCell(c++).SetCellValue(rec.Time.ToString("yyyy-MM-dd HH:mm:ss"));{r} if (columns.Contains("设备名称")) row.CreateCell(c++).SetCellValue(rec.Device);{r} if (columns.Contains("从站地址")) row.CreateCell(c++).SetCellValue(rec.Address);{r} if (columns.Contains("温度")) row.CreateCell(c++).SetCellValue(rec.Temperature);{r} if (columns.Contains("压力")) row.CreateCell(c++).SetCellValue(rec.Pressure);{r} if (columns.Contains("流量")) row.CreateCell(c++).SetCellValue(rec.Flow);{r} if (columns.Contains("CRC 校验")) row.CreateCell(c++).SetCellValue(rec.CrcOk ? "OK" : "FAIL");{r} done++;{r} if (done % 1000 == 0) progress?.Report((int)(done * 100 / total));{r} }}{r}{r} if (autoWidth){r} for (int c = 0; c < columns.Count(); c++){r} sheet.AutoSizeColumn(c);{r}{r} using var fs = new FileStream(path, FileMode.Create, FileAccess.Write);{r} wb.Write(fs);{r} wb.Dispose(); // 删除临时文件{r} progress?.Report(100);{r} }}, ct);{r} }}{r}{r} private static List<Record> GenerateFakeData(DateTime s, DateTime e){r} {{{r} var list = new List<Record>();{r} for (var t = s; t <= e; t = t.AddMinutes(1)){r} list.Add(new Record(t, "温压变送器1", 1, 25.0 + (t.Minute % 10), 1.02, 12.4, true));{r} return list;{r} }}{r}{r} public record Record(DateTime Time, string Device, byte Address, double Temperature,{r} double Pressure, double Flow, bool CrcOk);{r} }}{r}}}{r}

4.2 Form1.cs（核心片段）

csharp{r}public partial class Form1 : Form{r}{{{r} private async void btnExportXlsx_Click(object? sender, EventArgs e){r} {{{r} var cols = checkedListBox1.CheckedItems.Cast<string>().ToList();{r} if (!cols.Any()) {{ MessageBox.Show("请至少选择一列"); return; }}{r} using var sfd = new SaveFileDialog {{ Filter = "Excel|*.xlsx", FileName = "数据导出.xlsx" }};{r} if (sfd.ShowDialog() != DialogResult.OK) return;{r} var progress = new Progress<int>(p => toolStripStatusLabel1.Text = $"导出中 {p}%");{r} try{r} {{{r} await ExcelExporter.ExportAsync(sfd.FileName, cols, dtpStart.Value, dtpEnd.Value,{r} chkMerge.Checked, chkAutoWidth.Checked, progress, CancellationToken.None);{r} toolStripStatusLabel1.Text = "导出完成";{r} }}{r} catch (OperationCanceledException) {{ toolStripStatusLabel1.Text = "已取消"; }}{r} }}{r}{r} private void btnExportCsv_Click(object? sender, EventArgs e){r} {{{r} var cols = checkedListBox1.CheckedItems.Cast<string>().ToList();{r} using var sfd = new SaveFileDialog {{ Filter = "CSV|*.csv" }};{r} if (sfd.ShowDialog() != DialogResult.OK) return;{r} using var w = new StreamWriter(sfd.FileName, false, System.Text.Encoding.UTF8);{r} w.WriteLine(string.Join(",", cols));{r} foreach (var rec in ExcelExporter.GenerateFakeData(dtpStart.Value, dtpEnd.Value)){r} w.WriteLine($"{rec.Time:yyyy-MM-dd HH:mm:ss},{rec.Device},{rec.Address},{rec.Temperature}");{r} }}{r}}}{r}

4.3 Day28_ExportExcel.csproj

xml{r}<Project Sdk="Microsoft.NET.Sdk">{r} <PropertyGroup>{r} <OutputType>WinExe</OutputType>{r} <TargetFramework>net8.0-windows</TargetFramework>{r} <UseWindowsForms>true</UseWindowsForms>{r} <Nullable>disable</Nullable>{r} <RootNamespace>Day28_ExportExcel</RootNamespace>{r} </PropertyGroup>{r} <ItemGroup>{r} <PackageReference Include="NPOI" Version="2.7.2" />{r} </ItemGroup>{r}</Project>{r}

五、重点理解

• SXSSFWorkbook 第四个参数 true：启用临时文件压缩，磁盘占用更小。
• Dispose 会删临时文件：wb.Dispose() 必须调，否则 %temp% 爆满。
• CSV 用 UTF-8 BOM：Excel 打开才不乱码，传 Encoding.UTF8 时带 BOM（NPOI 的 StreamWriter 用 new UTF8Encoding(true)）。

六、常见问题排查

下一篇预告

第 29 天：OPC UA 服务器对接

📚 返回 {prev_link_} · 查看 45 天课程规划