基於NPOI封裝支援多級表頭靈活讀寫的《Soul.XLS》庫

花间岛發表於2024-08-21
封裝

一、先上效果圖

二、上程式碼

  • 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.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>("生日");
 }

相關文章