基于NPOI封装支持多级表头灵活读写的《Soul.XLS》库

一、先上效果图

二、上代码

• dotnet add package Soul.XLS --version 1.1.0
• 开源地址：https://gitee.com/soul-au/Soul.XLS

//构造一个多级表头
var columns = new ExcelColumnCollection("学生信息")
{
  new ExcelColumn("学生信息")
  {
      Items = new []
      {
          new ExcelColumn("个人信息")
          {
              Items = new []
              {
                  new ExcelColumn("姓名"),
                  new ExcelColumn("生日", style =>
                  {
                      style.ColumnWidth = 14;//可以在叶子节点给整列设置样式
                      style.Alignment = HorizontalAlignment.Center;
                  }),
              }
          },
          new ExcelColumn("家庭信息")
          {
              Items = new []
              {
                  new ExcelColumn("地址"),
                  new ExcelColumn("电话"),
              }
          },
      }
  },
  new ExcelColumn("成绩信息")
  {
      Items = new []
      {
          new ExcelColumn("文综")
          {
              Items = new []
              {
                  new ExcelColumn("语文"),
                  new ExcelColumn("历史"),
              }
          },
          new ExcelColumn("理综")
          {
              Items = new []
              {
                  new ExcelColumn("数学",style=>
                  {
                      style.Format = "0.00";
                  }),
                  new ExcelColumn("物理"),
              }
          }
      }
  }
};
//创建数据table
var table = new ExcelTable("学生信息");
//应用表头
table.ApplyHeaders(columns);
//设置窗口冻结
table.FreezePanes(4, 1);
for (int i = 0; i < 100; i++)
{
    var row = table1.NewRow();
    row["姓名"] = "花间岛" + i;
    row["生日"] = DateTime.Now.ToString();
    row["地址"] = "浙江杭州";
    row["电话"] = "10086";
    row["语文"] = 100;
    row["历史"] = 50;
    row["数学"] = 99;
    row["物理"] = 97;
    table1.Rows.Add(row);
}
var workbook = new ExcelWorkbook();
workbook.Tables.Add(table);//支持多个sheet
workbook.SaveToFile("D:\\demo.xlsx", ExcelFileType.Xlsx);

三、高级玩法，基于ISheet扩展

var table = new ExcelTable("消费信息");
table.ApplyHeaders(new ExcelColumnCollection("消费信息")
{
    new ExcelColumn("商品",style=>
    {
        style.Alignment = HorizontalAlignment.Center;
    }),
    new ExcelColumn("图片",c=>
    {
        c.ColumnWidth = 20 ;
    }),
    new ExcelColumn("价格"),
});
for (int i = 0; i < 10; i++)
{
    var row = table.NewRow();
    row.Height = 800;
    row["商品"] = "哇哈哈";
    row["价格"] = 15.2;
    row["图片"] = Image.Load("D:\\t.png");
    table.Rows.Add(row);
}
var workbook = new ExcelWorkbook(ExcelFileType.Xlsx);
var sheet = workbook.CreateSheet();
//插入到Sheet，并指定起始单元格，因此我们可以在一个sheet中插入多个table
sheet.InsertTable(table,1,1);

//在105行插入一行
sheet.SetRow(105, row =>
{
    //插入合计
    row.SetCell(0, (cell, style) => 
    {
        cell.SetData("合计");
        style.Alignment = HorizontalAlignment.Right;
    });
    row.SetCell(1, (cell, style) => 
    {
        cell.SetData(200.00);
        style.Alignment = HorizontalAlignment.Right;
    });
});

workbook.SaveToFile("D:\\demo2.xlsx");

四、读取数据

 var workbook = new ExcelWorkbook();
 workbook.LoadFormFile("D:\\demo3.xlsx");
 var sheet = workbook.GetSheet(0);
 //提取sheet中存在字段名的table
 var table1 = workbook.ExportTable(sheetIndex: 0, firstRow: 3, firstColumn: 0, columnCount: 4);
 foreach (var item in table1.Rows)
 {
     var a1 = item.GetValue<decimal?>("语文");
     var a2 = item.GetValue<decimal>("历史");
     var a3 = item.GetValue<decimal>("数学");
     var a4 = item.GetValue<DateTime>("生日");
 }
 //提取sheet中没有字段名的table，那么我们可以虚拟出来一个columns来进行读取table
 var columns = new ExcelColumnCollection()
 {
     new ExcelColumn("姓名"),
     new ExcelColumn("生日"),
     new ExcelColumn("地址"),
     new ExcelColumn("电话"),
     new ExcelColumn("语文"),
     new ExcelColumn("历史"),
     new ExcelColumn("数学"),
     new ExcelColumn("物理"),
 };
 var table2 = workbook.ExportTable(0, columns, 4);
 foreach (var item in table1.Rows)
 {
     var a1 = item.GetValue<decimal?>("语文");
     var a2 = item.GetValue<decimal>("历史");
     var a3 = item.GetValue<decimal>("数学");
     var a4 = item.GetValue<DateTime>("生日");
 }