[.NET][C#]NPOI產生Excel報表(一)列印表頭資訊(xlsx)

2018-01-24

5337
0
NPOI

同事剛加入急急如律令的專案幫忙開發報表，使用者有一項需求是列印報表時，表頭要出現頁數、標題、欄位名稱等，專案中使用的是NPOI，之前自己參加專案時都是同事寫好了元件，對NPOI操作不太熟悉，這次捲起袖子來試試。

同事的任務會讀取報表範本，然後把Row Data塞到Excel上(xlsx)。待會我們依序安裝NPOI、撰寫測試程式、製作報表範本，最後依序執行對照組及實驗組的測試。

What's NPOI

NPOI是Java POI的.NET版本，作者是對岸上海的Tony，使用的版權是Apache License 2.0 (Apache)，可以商業使用。

This project is the .NET version of POI Java project at http://poi.apache.org/. POI is an open source project which can help you read/write xls, doc, ppt files. It has a wide application.

NPOI安裝

先新增一個測試專案，接著到Nuget套件管理員安裝NPOI。

配合專案，我們選2.1.3.1版本

撰寫測試程式

新增Poker.cs類別，待會測試丟這個Entity產表。

public class Poker
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Title { get; set; }
    public string Color { get; set; }
    public Decimal Balance { get; set; }
}

打開UnitTest1.cs程式

簡單寫一段使用NPOI產生報表的方法，因為要接不同的Entity，用一點反射來處理。另外因為報表中數值，方法只先處理金額和字串。

public void Generate<T>(string TemplatePath, string ReportPath, List<T> entities, int Offset, int PageSize)
{
    using (FileStream fileStream = new FileStream(TemplatePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
    {
        IWorkbook workbook = WorkbookFactory.Create(fileStream);
        ISheet sheet = workbook.GetSheetAt(0);
        List<ICell> TemplateCells = new List<ICell>();
        for (int i = 0; i < sheet.GetRow(Offset).Cells.Count; i++)
        {
            TemplateCells.Add(sheet.GetRow(Offset).GetCell(i));
        }

        PropertyInfo[] properties = typeof(T).GetProperties();
        foreach (var entity in entities)
        {
            sheet.CreateRow(Offset);
            int CellInRow = 0;
            foreach (var property in properties)
            {
                ICell cell = sheet.GetRow(Offset).CreateCell(CellInRow);
                cell.CellStyle = TemplateCells[CellInRow].CellStyle;
                cell.SetCellType(TemplateCells[CellInRow].CellType);
                if (TemplateCells[CellInRow].CellType.Equals(CellType.Numeric))
                {
                    cell.SetCellValue(Convert.ToDouble(property.GetValue(entity, null)));
                }
                else
                {
                    cell.SetCellValue(Convert.ToString(property.GetValue(entity, null)));
                }
                CellInRow++;
            }
            Offset++;
        }
        using (FileStream fileOut = new FileStream(ReportPath, FileMode.Create))
        {
            workbook.Write(fileOut);
        }
    }
}

繼續在測試類別UnitTest1.cs加入以下測試程式碼

[TestMethod]
public void TestGererate()
{
    //(1)製造測試資料
    List<Poker> Pokers = new List<Poker>();
    for (int i = 0; i < 10; i++)
    {
        Pokers.Add(new Poker { Id = 1, Name = "David", Title = "King", Color = "Spades", Balance = 1000 });
        Pokers.Add(new Poker { Id = 2, Name = "Charlemagne", Title = "King", Color = "Hearts", Balance = 2000 });
        Pokers.Add(new Poker { Id = 3, Name = "Caesar", Title = "King", Color = "Diamonds", Balance = 3000 });
        Pokers.Add(new Poker { Id = 4, Name = "Alexander", Title = "King", Color = "Clubs", Balance = 4000 });
        Pokers.Add(new Poker { Id = 5, Name = "Athena", Title = "Queen", Color = "Spades", Balance = 5000 });
        Pokers.Add(new Poker { Id = 6, Name = "Judith", Title = "Queen", Color = "Hearts", Balance = 6000 });
        Pokers.Add(new Poker { Id = 7, Name = "Rachel", Title = "Queen", Color = "Diamonds", Balance = 7000 });
        Pokers.Add(new Poker { Id = 8, Name = "Argine", Title = "Queen", Color = "Clubs", Balance = 8000 });
    }

    //(2)產生報表
    string TemplatePath = @"D:\AP\Report\template\pokers.xlsx";
    string ReportPath = @"D:\AP\Report\pokers.xlsx";
    Generate(TemplatePath, ReportPath, Pokers, 1, 25);

    Assert.AreEqual(true, File.Exists(ReportPath));
}