public static IWorkbook ExportNpoi(string[] fieldName, List <Cnf05> cnf05List, int excelVersion)
{
IWorkbook workbook = null;
if (excelVersion == 2003)
{
workbook = new HSSFWorkbook();
}
else
{
workbook = new XSSFWorkbook();
}
ISheet sheet = workbook.CreateSheet("Sheet1");
IRow headRow = sheet.CreateRow(0);
for (var i = 0; i < fieldName.Length; i++)
{
if (String.Equals("cnf0501_file", fieldName[i]))
{
headRow.CreateCell(i).SetCellValue("檔案代號");
}
else if (String.Equals("cnf0502_field", fieldName[i]))
{
headRow.CreateCell(i).SetCellValue("欄位名稱");
}
else if (String.Equals("cnf0503_fieldname_tw", fieldName[i]))
{
headRow.CreateCell(i).SetCellValue("中文說明-繁體");
}
else if (String.Equals("cnf0506_program", fieldName[i]))
{
headRow.CreateCell(i).SetCellValue("程式代號");
}
else if (String.Equals("adddate", fieldName[i]))
{
headRow.CreateCell(i).SetCellValue("新增日期");
}
else if (String.Equals("adduser", fieldName[i]))
{
headRow.CreateCell(i).SetCellValue("新增者");
}
else if (String.Equals("moddate", fieldName[i]))
{
headRow.CreateCell(i).SetCellValue("修改日期");
}
else if (String.Equals("moduser", fieldName[i]))
{
headRow.CreateCell(i).SetCellValue("修改者");
}
else if (String.Equals("cnf0504_fieldname_cn", fieldName[i]))
{
headRow.CreateCell(i).SetCellValue("中文說明-簡體");
}
else if (String.Equals("cnf0505_fieldname_en", fieldName[i]))
{
headRow.CreateCell(i).SetCellValue("英文說明");
}
}
for (int i = 0; i < cnf05List.Count; i++)
{
IRow excelRow = sheet.CreateRow(i + 1);
Cnf05 cnf05Item = cnf05List[i];
for (var j = 0; j < fieldName.Length; j++)
{
var val = Cnf05.GetPropValue(cnf05Item, fieldName[j]);
var excelCell = excelRow.CreateCell(j);
excelCell.SetCellValue(val != null ? val.ToString() : "");
if (val is DateTime)
{
excelCell.SetCellValue(((DateTime)val).ToString("yyyy/MM/dd"));
}
}
}
for (var i = 0; i < fieldName.Length; i++)
{
sheet.AutoSizeColumn(i);
}
return(workbook);
}
public static ExcelPackage Export(string[] fieldName, List <Cnf05> cnf05List)
{
var excel = new ExcelPackage();
var sheet = excel.Workbook.Worksheets.Add("Sheet1");
var title = 1;
for (var i = 0; i < fieldName.Length; i++)
{
if (String.Equals("cnf0501_file", fieldName[i]))
{
sheet.Cells[title, i + 1].Value = "檔案代號";
}
else if (String.Equals("cnf0502_field", fieldName[i]))
{
sheet.Cells[title, i + 1].Value = "欄位名稱";
}
else if (String.Equals("cnf0503_fieldname_tw", fieldName[i]))
{
sheet.Cells[title, i + 1].Value = "中文說明-繁體";
}
else if (String.Equals("cnf0506_program", fieldName[i]))
{
sheet.Cells[title, i + 1].Value = "程式代號";
}
else if (String.Equals("adddate", fieldName[i]))
{
sheet.Cells[title, i + 1].Value = "新增日期";
}
else if (String.Equals("adduser", fieldName[i]))
{
sheet.Cells[title, i + 1].Value = "新增者";
}
else if (String.Equals("moddate", fieldName[i]))
{
sheet.Cells[title, i + 1].Value = "修改日期";
}
else if (String.Equals("moduser", fieldName[i]))
{
sheet.Cells[title, i + 1].Value = "修改者";
}
else if (String.Equals("cnf0504_fieldname_cn", fieldName[i]))
{
sheet.Cells[title, i + 1].Value = "中文說明-簡體";
}
else if (String.Equals("cnf0505_fieldname_en", fieldName[i]))
{
sheet.Cells[title, i + 1].Value = "英文說明";
}
}
for (int i = 0; i < cnf05List.Count; i++)
{
Cnf05 cnf05Item = cnf05List[i];
for (var j = 0; j < fieldName.Length; j++)
{
sheet.Cells[i + 1 + title, j + 1].Value = Cnf05.GetPropValue(cnf05Item, fieldName[j]);
if (sheet.Cells[i + 1 + title, j + 1].Value is DateTime)
{
sheet.Cells[i + 1 + title, j + 1].Style.Numberformat.Format = "yyyy/MM/dd";
}
}
}
FitExcelColumnWidth(sheet);
return(excel);
}
