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
{
    /// <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 duplicates
                        var 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)
      {

      }
  }