当前位置： 首页 > news >正文

C# 一个快速读取写入操作execl的方法封装

news 2025/8/24 23:00:58

在这里插入图片描述
在这里插入图片描述
这里封装了3个实用类ExcelDataReaderExtensions，ExcelDataSetConfiguration，ExcelDataTableConfiguration和一个实用代码参考：

using ExcelDataReader;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace ExeclHelper
{/// <summary>/// Processing configuration options and callbacks for AsDataTable()./// </summary>public class ExcelDataTableConfiguration{/// <summary>/// Gets or sets a value indicating the prefix of generated column names./// </summary>public string EmptyColumnNamePrefix { get; set; } = "Column";/// <summary>/// Gets or sets a value indicating whether to use a row from the data as column names./// </summary>public bool UseHeaderRow { get; set; } = false;/// <summary>/// Gets or sets a callback to determine which row is the header row. Only called when UseHeaderRow = true./// </summary>public Action<IExcelDataReader> ReadHeaderRow { get; set; }/// <summary>/// Gets or sets a callback to determine whether to include the current row in the DataTable./// </summary>public Func<IExcelDataReader, bool> FilterRow { get; set; }/// <summary>/// Gets or sets a callback to determine whether to include the specific column in the DataTable. Called once per column after reading the headers./// </summary>public Func<IExcelDataReader, int, bool> FilterColumn { get; set; }}
}
using ExcelDataReader;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace ExeclHelper
{/// <summary>/// ExcelDataReader DataSet extensions/// </summary>public static class ExcelDataReaderExtensions{/// <summary>/// Converts all sheets to a DataSet/// </summary>/// <param name="self">The IExcelDataReader instance</param>/// <param name="configuration">An optional configuration object to modify the behavior of the conversion</param>/// <returns>A dataset with all workbook contents</returns>public static DataSet AsDataSet(this IExcelDataReader self, ExcelDataSetConfiguration configuration = null){if (configuration == null){configuration = new ExcelDataSetConfiguration();}self.Reset();var tableIndex = -1;var result = new DataSet();do{tableIndex++;if (configuration.FilterSheet != null && !configuration.FilterSheet(self, tableIndex)){continue;}var tableConfiguration = configuration.ConfigureDataTable != null? configuration.ConfigureDataTable(self): null;if (tableConfiguration == null){tableConfiguration = new ExcelDataTableConfiguration();}var table = AsDataTable(self, tableConfiguration);result.Tables.Add(table);}while (self.NextResult());result.AcceptChanges();if (configuration.UseColumnDataType){FixDataTypes(result);}self.Reset();return result;}private static string GetUniqueColumnName(DataTable table, string name){var columnName = name;var i = 1;while (table.Columns[columnName] != null){columnName = string.Format("{0}_{1}", name, i);i++;}return columnName;}private static DataTable AsDataTable(IExcelDataReader self, ExcelDataTableConfiguration configuration){var result = new DataTable { TableName = self.Name };result.ExtendedProperties.Add("visiblestate", self.VisibleState);var first = true;var emptyRows = 0;var columnIndices = new List<int>();while (self.Read()){if (first){if (configuration.UseHeaderRow && configuration.ReadHeaderRow != null){configuration.ReadHeaderRow(self);}for (var i = 0; i < self.FieldCount; i++){if (configuration.FilterColumn != null && !configuration.FilterColumn(self, i)){continue;}var name = configuration.UseHeaderRow? Convert.ToString(self.GetValue(i)): null;if (string.IsNullOrEmpty(name)){name = configuration.EmptyColumnNamePrefix + i;}// if a column already exists with the name append _i to the duplicatesvar columnName = GetUniqueColumnName(result, name);var column = new DataColumn(columnName, typeof(object)) { Caption = name };result.Columns.Add(column);columnIndices.Add(i);}result.BeginLoadData();first = false;if (configuration.UseHeaderRow){continue;}}if (configuration.FilterRow != null && !configuration.FilterRow(self)){continue;}if (IsEmptyRow(self)){emptyRows++;continue;}for (var i = 0; i < emptyRows; i++){result.Rows.Add(result.NewRow());}emptyRows = 0;var row = result.NewRow();for (var i = 0; i < columnIndices.Count; i++){var columnIndex = columnIndices[i];var value = self.GetValue(columnIndex);row[i] = value;}result.Rows.Add(row);}result.EndLoadData();return result;}private static bool IsEmptyRow(IExcelDataReader reader){for (var i = 0; i < reader.FieldCount; i++){if (reader.GetValue(i) != null)return false;}return true;}private static void FixDataTypes(DataSet dataset){var tables = new List<DataTable>(dataset.Tables.Count);bool convert = false;foreach (DataTable table in dataset.Tables){if (table.Rows.Count == 0){tables.Add(table);continue;}DataTable newTable = null;for (int i = 0; i < table.Columns.Count; i++){Type type = null;foreach (DataRow row in table.Rows){if (row.IsNull(i))continue;var curType = row[i].GetType();if (curType != type){if (type == null){type = curType;}else{type = null;break;}}}if (type == null)continue;convert = true;if (newTable == null)newTable = table.Clone();newTable.Columns[i].DataType = type;}if (newTable != null){newTable.BeginLoadData();foreach (DataRow row in table.Rows){newTable.ImportRow(row);}newTable.EndLoadData();tables.Add(newTable);}else{tables.Add(table);}}if (convert){dataset.Tables.Clear();dataset.Tables.AddRange(tables.ToArray());}}}
}
using ExcelDataReader;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace ExeclHelper
{/// <summary>/// Processing configuration options and callbacks for IExcelDataReader.AsDataSet()./// </summary>public class ExcelDataSetConfiguration{/// <summary>/// Gets or sets a value indicating whether to set the DataColumn.DataType property in a second pass./// </summary>public bool UseColumnDataType { get; set; } = true;/// <summary>/// Gets or sets a callback to obtain configuration options for a DataTable. /// </summary>public Func<IExcelDataReader, ExcelDataTableConfiguration> ConfigureDataTable { get; set; }/// <summary>/// Gets or sets a callback to determine whether to include the current sheet in the DataSet. Called once per sheet before ConfigureDataTable./// </summary>public Func<IExcelDataReader, int, bool> FilterSheet { get; set; }}
}

运用实例：

  private IList<string> GetTablenames(DataTableCollection tables){var tableList = new List<string>();foreach (var table in tables){tableList.Add(table.ToString());}return tableList;}public void ExportExcel(){try{//创建一个工作簿IWorkbook workbook = new HSSFWorkbook();//创建一个 sheet 表ISheet sheet = workbook.CreateSheet("合并数据");//创建一行IRow rowH = sheet.CreateRow(0);//创建一个单元格ICell cell = null;//创建单元格样式ICellStyle cellStyle = workbook.CreateCellStyle();//创建格式IDataFormat dataFormat = workbook.CreateDataFormat();//设置为文本格式，也可以为 text，即 dataFormat.GetFormat("text");cellStyle.DataFormat = dataFormat.GetFormat("@");//设置列名//foreach (DataColumn col in dt.Columns)//{//    //创建单元格并设置单元格内容//    rowH.CreateCell(col.Ordinal).SetCellValue(col.Caption);//    //设置单元格格式//    rowH.Cells[col.Ordinal].CellStyle = cellStyle;//}for (int i = 0; i < Headers.Count(); i++){rowH.CreateCell(i).SetCellValue(Headers[i]);rowH.Cells[i].CellStyle = cellStyle;}//写入数据for (int i = 0; i < dataModels.Count; i++){//跳过第一行，第一行为列名IRow row = sheet.CreateRow(i + 1);for (int j = 0; j < 11; j++){cell = row.CreateCell(j);if (j == 0)cell.SetCellValue(dataModels[i].title1.ToString());if (j == 1)cell.SetCellValue(dataModels[i].title2.ToString());if (j == 2)cell.SetCellValue(dataModels[i].title3.ToString());if (j == 3)cell.SetCellValue(dataModels[i].title4.ToString());if (j == 4)cell.SetCellValue(dataModels[i].title5.ToString());if (j == 5)cell.SetCellValue(dataModels[i].title6.ToString());if (j == 6)cell.SetCellValue(dataModels[i].title7.ToString());if (j == 7)cell.SetCellValue(dataModels[i].title8.ToString());if (j == 8)cell.SetCellValue(dataModels[i].title9.ToString());if (j == 9)cell.SetCellValue(dataModels[i].title10.ToString());if (j == 10)cell.SetCellValue(dataModels[i].title11.ToString());cell.CellStyle = cellStyle;}}//设置导出文件路径string path = textBox2.Text;//设置新建文件路径及名称string savePath = path + "合并" + DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss") + ".xls";//创建文件FileStream file = new FileStream(savePath, FileMode.CreateNew, FileAccess.Write);//创建一个 IO 流MemoryStream ms = new MemoryStream();//写入到流workbook.Write(ms);//转换为字节数组byte[] bytes = ms.ToArray();file.Write(bytes, 0, bytes.Length);file.Flush();//还可以调用下面的方法，把流输出到浏览器下载//OutputClient(bytes);//释放资源bytes = null;ms.Close();ms.Dispose();file.Close();file.Dispose();workbook.Close();sheet = null;workbook = null;}catch (Exception ex){}}
http://www.lryc.cn/news/291004.html

相关文章：

  • axios结合ts使用，取消请求，全局统一获取数据，抛出错误信息
  • MongoDB：从容器使用到 Mongosh、Python/Node.js 数据操作（结构清晰万字长文）
  • 超越传统—Clean架构打造现代Android架构指南
  • WebGL开发项目的类型
  • CUDA编程- - GPU线程的理解 thread,block,grid - 学习记录
  • yum 报错 ZLIB_1.2.3.3 not defined in file libz.so.1
  • 数字孪生智慧能源电力Web3D可视化云平台合集
  • DataTable.Load(reader)注意事项
  • DC-DNS(域名解析服务)（23国赛真题）
  • 日志之Loki详细讲解
  • Mongodb投射中的$slice,正向反向跳过要搞清楚
  • 类和对象 第六部分 继承 第一部分：继承的语法
  • githacker安装详细教程，linux添加环境变量详细教程（见标题三）
  • 2401Idea用GradleKotlin编译Java控制台中文出乱码解决
  • Day39 62不同路径 63不同路径II 343整数拆分 96不同的二叉搜索树
  • JavaScript 的 ～～ 运算和floor 的性能差异
  • AtCoder Beginner Contest 338F - Negative Traveling Salesman【floyd+状态压缩dp】
  • UDP/TCP协议特点
  • 编程笔记 html5cssjs 059 css多列
  • Facebook的元宇宙探索：虚拟社交的新时代
  • 用React给XXL-JOB开发一个新皮肤（四）：实现用户管理模块
  • 某赛通电子文档安全管理系统 hiddenWatermark/uploadFile 文件上传漏洞复现
  • Redis五种数据类型及应用场景
  • 测试环境搭建整套大数据系统（一：基础配置，修改hostname，hosts，免密）
  • maven helper 解决jar包冲突方法
  • AppSrv-文件共享（23国赛真题）
  • AsyncLocal是如何实现在Thread直接传值的？
  • Flask 入门1：一个简单的 Web 程序
  • 维护管理Harbor，docker容器的重启策略
  • Qt6入门教程 14：QToolButton