[.NET][C#]NPOI產生Excel報表(三)下拉選單(xlsx)

2018-02-06

3343
0
NPOI

昨天留在駐點辦公室驗證需求，客戶端的程式開發人員來討論一個主管交辦的任務，產表時，要多幾個下拉欄位給user選，而且希望透過程式動態帶出下拉選單的值，趁女兒睡著，來試試用NPOI實現從後端作Excel資料驗證。

改寫Class

為了這個下拉選單任務，我們決定把上一篇Entity類別Poker.cs做一點改寫，把花色(color)和大小(title)改用enum列舉類型。

public class NewPoker
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Title Title { get; set; }
    public Color Color { get; set; }
    public Decimal Balance { get; set; }
}
public enum Title
{
    King,
    Queen
}
public enum Color
{
    Spades,
    Hearts,
    Diamonds,
    Clubs
}

改寫前兩篇的產表方法

這邊我們加在報表資料都產出後，逐一查看Entity內的屬性，若型別若為列舉類型時(property.PropertyType.IsEnum)，自動取得Enum清單(Enum.GetNames(property.PropertyType))來建置資料驗證選單，資料驗證的範圍則為該資料行的最後一列。

public void GenerateWithValidation<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++;
        }
//加這
        for (int i = 0; i < properties.Length; i++)
        {
            PropertyInfo property = properties[i];
            if (property.PropertyType.IsEnum)
            {
                CellRangeAddressList addressList = new CellRangeAddressList(0, sheet.LastRowNum, i, i);
                var helper = sheet.GetDataValidationHelper();
                var constraint = helper.CreateExplicitListConstraint(Enum.GetNames(property.PropertyType));
                var validation = helper.CreateValidation(constraint, addressList);
                sheet.AddValidationData(validation);
            }
        }
        using (FileStream fileOut = new FileStream(ReportPath, FileMode.Create))
        {
            workbook.Write(fileOut);
        }
    }
}

ok，寫完了，來測試看看!

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

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

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

測試結果:

抬頭