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

这里封装了3个实用类ExcelDataReaderExtensions，ExcelDataSetConfiguration，ExcelDataTableConfiguration和一个实用代码参考：

using ExcelDataReader;using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;namespace ExeclHelper{/// 
/// Processing configuration options and callbacks for AsDataTable()./// public class ExcelDataTableConfiguration{/// 
/// Gets or sets a value indicating the prefix of generated column names./// public string EmptyColumnNamePrefix { get; set; } = "Column";/// 
/// Gets or sets a value indicating whether to use a row from the data as column names./// public bool UseHeaderRow { get; set; } = false;/// 
/// Gets or sets a callback to determine which row is the header row. Only called when UseHeaderRow = true./// public Action<IExcelDataReader> ReadHeaderRow { get; set; }/// 
/// Gets or sets a callback to determine whether to include the current row in the DataTable./// public Func<IExcelDataReader, bool> FilterRow { get; set; }/// 
/// Gets or sets a callback to determine whether to include the specific column in the DataTable. Called once per column after reading the headers./// 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{/// 
/// ExcelDataReader DataSet extensions/// public static class ExcelDataReaderExtensions{/// 
/// Converts all sheets to a DataSet/// /// The IExcelDataReader instance/// An optional configuration object to modify the behavior of the conversion/// A dataset with all workbook contentspublic 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" />.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{/// 
/// Processing configuration options and callbacks for IExcelDataReader.AsDataSet()./// public class ExcelDataSetConfiguration{/// 
/// Gets or sets a value indicating whether to set the DataColumn.DataType property in a second pass./// public bool UseColumnDataType { get; set; } = true;/// 
/// Gets or sets a callback to obtain configuration options for a DataTable. /// public Func<IExcelDataReader, ExcelDataTableConfiguration> ConfigureDataTable { get; set; }/// 
/// Gets or sets a callback to determine whether to include the current sheet in the DataSet. Called once per sheet before ConfigureDataTable./// 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){}}