NPOI Generate Excel Template for Data Input

Kadang template excel perlu digenerate oleh program. Template dapat digenerate menggunakan NPOI. Berikut contohnya:

Controller
[code language=”csharp”]
public ActionResult GetTemplate(int companyId)
{
company company = RepoCompany.FindByPk(companyId);
string filename = company.name + "-operation-rkap-template.xlsx";

Business.Infrastructure.FilterInfo filters = new Business.Infrastructure.FilterInfo { Filters = new List<Business.Infrastructure.FilterInfo> { new Business.Infrastructure.FilterInfo { Field = "company_id", Operator = "eq", Value = companyId.ToString() } } };
byte[] excel = new OperationImportStub().GenerateTemplate(RepoOperation.FindAllAccount(null, null, null, filters), company.name); //"~/App_Data/template/licensor.xls"

return File(excel, "application/x-msexcel", filename);
}
[/code]

Model
[code language=”csharp”]
public byte[] GenerateTemplate(List<account_operation> companyAccounts, string companyName)
{
//culture
Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); //supaya file tidak corrupt
//Thread.CurrentThread.CurrentCulture = CultureInfo.InstalledUICulture;

//kamus
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet; XSSFRow row; XSSFCell cell;
XSSFCellStyle style; XSSFFont font;
CellRangeAddressList addressList; XSSFDataValidationHelper dvHelper; XSSFDataValidationConstraint constraint; XSSFDataValidation validation;
XSSFRow row2; XSSFCell cell2; //untuk di header

int col = 0; List<OperationImportAccountModel> accounts; int childColIndex;

//algoritma
sheet = (XSSFSheet)workbook.CreateSheet(companyName);

if (companyAccounts.Count() > 0)
{
//create row (header)
row = (XSSFRow)sheet.CreateRow((short)0);
row2 = (XSSFRow)sheet.CreateRow((short)1);

//header style
style = (XSSFCellStyle)workbook.CreateCellStyle();
font = (XSSFFont)workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold; ;
style.SetFont(font);

//header data
accounts = new OperationImportAccountModel().MapList(companyAccounts);
foreach (OperationImportAccountModel account in accounts)
{
cell = (XSSFCell)row.CreateCell(col);
cell.SetCellValue(account.Name);
cell.CellStyle = style;

//merge cells + set col width
if (account.Children.Count() == 0) //account ga ada child
{
cell2 = (XSSFCell)row2.CreateCell(col);
sheet.AddMergedRegion(new CellRangeAddress(0, 1, col, col));

sheet.SetColumnWidth(col, (30 * 256));
++col;
}
else //account ada children
{
for (int childIndex = 0; childIndex < account.Children.Count(); childIndex++)
{
childColIndex = col + childIndex;

cell2 = (XSSFCell)row2.CreateCell(childColIndex);
cell2.SetCellValue(account.Children[childIndex].Name);
cell2.CellStyle = style;

sheet.SetColumnWidth(childColIndex, (30 * 256));

if (childIndex > 0)
cell2 = (XSSFCell)row.CreateCell(childColIndex);
}

sheet.AddMergedRegion(new CellRangeAddress(0, 0, col, (col + account.Children.Count() – 1)));
col += account.Children.Count();
}
}

//create row (isian user)
row = (XSSFRow)sheet.CreateRow((short)2);

//currency
style = (XSSFCellStyle)workbook.CreateCellStyle();
style.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00_);(#,##0.00)");
style.IsLocked = false;
for (int colIndex = 0; colIndex < col; ++colIndex)
{
cell = (XSSFCell)row.CreateCell(colIndex);
cell.CellStyle = style;
}

//create row (footer)
row = (XSSFRow)sheet.CreateRow((short)3);

//background
style = (XSSFCellStyle)workbook.CreateCellStyle();
//style.SetFillBackgroundColor(IndexedColors.Grey80Percent);
style.FillForegroundColor = (short)IndexedColors.Grey50Percent.Index;
style.FillPattern = FillPattern.ThinForwardDiagonals;
for (int colIndex = 0; colIndex < col; ++colIndex)
{
cell = (XSSFCell)row.CreateCell(colIndex);
cell.CellStyle = style;
}

//data validation
addressList = new CellRangeAddressList(2, 2, 0, (col – 1));
dvHelper = new XSSFDataValidationHelper(sheet);
constraint = (XSSFDataValidationConstraint)dvHelper.CreateNumericConstraint(ValidationType.INTEGER, OperatorType.BETWEEN, "0", "1000000000000000000");
validation = (XSSFDataValidation)dvHelper.CreateValidation(constraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Kesalahan Pengisian Data", "Nilai harus berupa angka, maksimal 1.000.000.000.000.000.000");
sheet.AddValidationData(validation);

sheet.ProtectSheet("mobidig");
}

//write to byte[]
MemoryStream ms = new MemoryStream();
workbook.Write(ms);

return ms.ToArray();
}

[/code]