昨天留在駐點辦公室驗證需求,客戶端的程式開發人員來討論一個主管交辦的任務,產表時,要多幾個下拉欄位給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));
}
測試結果:
抬頭
花色
Excel資料驗證就和Entities屬性中列舉類型結合在一起了。
天氣好冷,連續上班14天的週末,幸好,川田火鍋好吃!
參考