VS2022实战：NPOI库如何5分钟搞定Excel导入导出（附完整代码）

VS2022极速开发NPOI库高效处理Excel全流程指南在数据处理密集型项目中Excel文件操作几乎是每个C#开发者都会遇到的常规需求。传统方案如OleDb或COM组件不仅依赖Office环境还存在性能瓶颈和兼容性问题。而NPOI作为.NET平台下的Apache POI移植版本真正实现了无需Office环境的Excel高效读写。本文将带您从零开始通过VS2022和NPOI快速构建企业级Excel处理模块。1. 环境配置与基础准备1.1 创建项目与安装NPOI首先在VS2022中新建一个.NET Framework 4.8的Windows窗体应用项目。通过NuGet包管理器安装NPOI核心组件Install-Package NPOI Install-Package NPOI.OOXML # 支持xlsx格式 Install-Package NPOI.OpenXml4Net # 依赖项提示如果项目需要同时处理xls和xlsx格式必须安装NPOI.OOXML包1.2 基础引用配置在代码文件中添加必要的命名空间引用using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; // xlsx处理 using NPOI.HSSF.UserModel; // xls处理 using System.IO; using System.Data;2. Excel读取核心技术实现2.1 通用读取方法封装以下方法支持自动识别Excel版本并转换为DataTablepublic static DataTable ExcelToDataTable(string filePath, bool hasHeader true) { using (var fs new FileStream(filePath, FileMode.Open, FileAccess.Read)) { IWorkbook workbook Path.GetExtension(filePath).ToLower() .xlsx ? new XSSFWorkbook(fs) : new HSSFWorkbook(fs); ISheet sheet workbook.GetSheetAt(0); DataTable dt new DataTable(); // 处理列头 IRow headerRow sheet.GetRow(0); for (int i 0; i headerRow.LastCellNum; i) { dt.Columns.Add(hasHeader ? headerRow.GetCell(i).ToString() : $Column{i}); } // 处理数据行 int startRow hasHeader ? 1 : 0; for (int i startRow; i sheet.LastRowNum; i) { IRow row sheet.GetRow(i); DataRow dr dt.NewRow(); for (int j 0; j dt.Columns.Count; j) { dr[j] GetCellValue(row?.GetCell(j)); } dt.Rows.Add(dr); } return dt; } } private static object GetCellValue(ICell cell) { if (cell null) return null; switch (cell.CellType) { case CellType.Numeric: return DateUtil.IsCellDateFormatted(cell) ? cell.DateCellValue : cell.NumericCellValue; case CellType.Boolean: return cell.BooleanCellValue; default: return cell.ToString(); } }2.2 大数据量读取优化当处理大型Excel文件时可采用分块读取策略public static IEnumerableDataRow StreamExcelData(string filePath, int batchSize 1000) { using (var fs new FileStream(filePath, FileMode.Open, FileAccess.Read)) { IWorkbook workbook Path.GetExtension(filePath).ToLower() .xlsx ? new XSSFWorkbook(fs) : new HSSFWorkbook(fs); ISheet sheet workbook.GetSheetAt(0); DataTable dt new DataTable(); // 初始化列结构同前 // ... for (int i 1; i sheet.LastRowNum; i) { IRow row sheet.GetRow(i); DataRow dr dt.NewRow(); // 填充数据同前 // ... yield return dr; if (i % batchSize 0) { // 可在此处添加批处理逻辑 Console.WriteLine($已处理 {i} 行数据); } } } }3. Excel写入高级技巧3.1 基础数据导出public static void ExportToExcel(DataTable data, string filePath) { IWorkbook workbook Path.GetExtension(filePath).ToLower() .xlsx ? new XSSFWorkbook() : new HSSFWorkbook(); ISheet sheet workbook.CreateSheet(Sheet1); // 创建标题行 IRow headerRow sheet.CreateRow(0); for (int i 0; i data.Columns.Count; i) { headerRow.CreateCell(i).SetCellValue(data.Columns[i].ColumnName); } // 填充数据 for (int i 0; i data.Rows.Count; i) { IRow row sheet.CreateRow(i 1); for (int j 0; j data.Columns.Count; j) { row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString()); } } // 自动调整列宽 for (int i 0; i data.Columns.Count; i) { sheet.AutoSizeColumn(i); } using (var fs new FileStream(filePath, FileMode.Create)) { workbook.Write(fs); } }3.2 样式定制与高级功能public static void ExportWithStyle(DataTable data, string filePath) { IWorkbook workbook new XSSFWorkbook(); ISheet sheet workbook.CreateSheet(Report); // 创建标题样式 ICellStyle headerStyle workbook.CreateCellStyle(); headerStyle.FillForegroundColor IndexedColors.Grey25Percent.Index; headerStyle.FillPattern FillPattern.SolidForeground; IFont headerFont workbook.CreateFont(); headerFont.Boldweight (short)FontBoldWeight.Bold; headerStyle.SetFont(headerFont); // 创建数据样式 ICellStyle dateStyle workbook.CreateCellStyle(); dateStyle.DataFormat workbook.CreateDataFormat().GetFormat(yyyy-MM-dd); // 创建标题行 IRow headerRow sheet.CreateRow(0); for (int i 0; i data.Columns.Count; i) { ICell cell headerRow.CreateCell(i); cell.SetCellValue(data.Columns[i].ColumnName); cell.CellStyle headerStyle; } // 填充数据 for (int i 0; i data.Rows.Count; i) { IRow row sheet.CreateRow(i 1); for (int j 0; j data.Columns.Count; j) { ICell cell row.CreateCell(j); object value data.Rows[i][j]; if (value is DateTime) { cell.SetCellValue((DateTime)value); cell.CellStyle dateStyle; } else { cell.SetCellValue(value.ToString()); } } } // 冻结首行 sheet.CreateFreezePane(0, 1, 0, 1); using (var fs new FileStream(filePath, FileMode.Create)) { workbook.Write(fs); } }4. 实战应用场景4.1 数据报表生成系统结合NPOI的模板功能可以创建复杂的报表模板public static void GenerateReportFromTemplate(string templatePath, string outputPath, Dictionarystring, object data) { using (var fs new FileStream(templatePath, FileMode.Open, FileAccess.Read)) { IWorkbook workbook new XSSFWorkbook(fs); ISheet sheet workbook.GetSheetAt(0); // 替换模板中的占位符 foreach (var pair in data) { var cells sheet.GetRow(0).Cells .Where(c c.ToString().Contains($$[{pair.Key}])); foreach (var cell in cells) { cell.SetCellValue(pair.Value.ToString()); } } // 保存生成的文件 using (var outFs new FileStream(outputPath, FileMode.Create)) { workbook.Write(outFs); } } }4.2 数据库与Excel双向同步public class DataSyncService { public void SyncToDatabase(string excelPath, string connectionString) { DataTable dt ExcelToDataTable(excelPath); using (var conn new SqlConnection(connectionString)) { conn.Open(); // 清空目标表 new SqlCommand(TRUNCATE TABLE TargetTable, conn).ExecuteNonQuery(); // 批量插入数据 using (var bulkCopy new SqlBulkCopy(conn)) { bulkCopy.DestinationTableName TargetTable; bulkCopy.BatchSize 5000; bulkCopy.WriteToServer(dt); } } } public void ExportFromDatabase(string excelPath, string connectionString) { DataTable dt new DataTable(); using (var conn new SqlConnection(connectionString)) { conn.Open(); using (var cmd new SqlCommand(SELECT * FROM SourceTable, conn)) { dt.Load(cmd.ExecuteReader()); } } ExportToExcel(dt, excelPath); } }5. 性能优化与异常处理5.1 内存管理最佳实践public static void ProcessLargeExcel(string filePath) { // 使用事件驱动模型处理大文件 XSSFReader reader new XSSFReader( new OPCPackage(new FileStream(filePath, FileMode.Open))); // 获取共享字符串表 XSSFSharedStringsTable sst reader.GetSharedStringsTable(); // 获取工作表数据 XSSFReader.SheetIterator sheets (XSSFReader.SheetIterator)reader.Sheets; while (sheets.MoveNext()) { Stream sheetStream sheets.Current; using (sheetStream) { XmlReader xmlReader XmlReader.Create(sheetStream); while (xmlReader.Read()) { if (xmlReader.NodeType XmlNodeType.Element xmlReader.Name row) { // 处理行数据 // ... } } } } }5.2 异常处理策略public static DataTable SafeReadExcel(string filePath) { try { if (!File.Exists(filePath)) throw new FileNotFoundException(Excel文件不存在); if (Path.GetExtension(filePath).ToLower() not in (.xls, .xlsx)) throw new ArgumentException(不支持的文件格式); return ExcelToDataTable(filePath); } catch (IOException ex) { // 处理文件被占用等情况 Console.WriteLine($文件访问错误: {ex.Message}); throw; } catch (Exception ex) { // 记录详细错误信息 Logger.Error($Excel读取失败: {ex}); throw new ApplicationException(Excel处理失败, ex); } }6. 扩展功能开发6.1 Excel公式计算public static void CalculateFormulas(string filePath) { using (var fs new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite)) { IWorkbook workbook WorkbookFactory.Create(fs); foreach (ISheet sheet in workbook) { foreach (IRow row in sheet) { foreach (ICell cell in row.Cells) { if (cell.CellType CellType.Formula) { // 计算公式结果 IFormulaEvaluator evaluator WorkbookFactory.CreateFormulaEvaluator(workbook); evaluator.EvaluateFormulaCell(cell); } } } } // 保存计算结果 workbook.Write(fs); } }6.2 图表生成示例public static void CreateChart(string filePath) { IWorkbook workbook new XSSFWorkbook(); ISheet sheet workbook.CreateSheet(ChartSheet); // 创建测试数据 for (int i 0; i 5; i) { IRow row sheet.CreateRow(i); row.CreateCell(0).SetCellValue(i 1); row.CreateCell(1).SetCellValue(Math.Pow(i 1, 2)); } // 创建图表 IDrawing drawing sheet.CreateDrawingPatriarch(); IClientAnchor anchor drawing.CreateAnchor(0, 0, 0, 0, 4, 0, 10, 20); IChart chart drawing.CreateChart(anchor); IChartLegend legend chart.GetOrCreateLegend(); legend.Position LegendPosition.Bottom; // 配置数据系列 ILineChartDatastring, double data chart.ChartDataFactory .CreateLineChartDatastring, double(); IChartAxis bottomAxis chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom); IValueAxis leftAxis chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left); // 设置数据范围 IChartDataSourcestring xs DataSources.FromStringCellRange( sheet, new CellRangeAddress(0, 4, 0, 0)); IChartDataSourcedouble ys DataSources.FromNumericCellRange( sheet, new CellRangeAddress(0, 4, 1, 1)); data.AddSeries(平方数, xs, ys); chart.Plot(data, bottomAxis, leftAxis); // 保存文件 using (var fs new FileStream(filePath, FileMode.Create)) { workbook.Write(fs); } }