Skip to content

Instantly share code, notes, and snippets.

@CharlesOkwuagwu
Last active November 3, 2017 16:18
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save CharlesOkwuagwu/a9a34647070b87a86a170875a8468ced to your computer and use it in GitHub Desktop.
Save CharlesOkwuagwu/a9a34647070b87a86a170875a8468ced to your computer and use it in GitHub Desktop.
Using NPOI, create Excel file from an existing file, retaining formatting
private void CreateExcel(SalarySummary o, List<SalaryInfo> l, string path)
{
try
{
var temp = $"{path}salary-template.xlsx";
var nFile = $"{path}{o.period}.xlsx";
System.IO.File.Copy(temp, nFile, true);
//using (var fs = new FileStream(nFile, FileMode.OpenOrCreate, FileAccess.ReadWrite))
using (var fs = System.IO.File.OpenRead(nFile))
{
var wb = new XSSFWorkbook(fs);
var ws = wb.GetSheetAt(0);
var ix = 6;
ws.GetRow(1).GetCell(8).SetCellValue(o.period);
var f_sn = ws.GetRow(ix + 1).GetCell(1).CellStyle;
var f_name = ws.GetRow(ix + 1).GetCell(2).CellStyle;
var f_acc = ws.GetRow(ix + 1).GetCell(3).CellStyle;
var f_amt1 = ws.GetRow(ix + 1).GetCell(4).CellStyle;
var f_amt2 = ws.GetRow(ix + 1).GetCell(8).CellStyle;
foreach (var x in l)
{
ix++;
var r = ws.CreateRow(ix);
var c1 = r.CreateCell(1); c1.SetCellValue(ix - 7); c1.CellStyle = f_sn; // S/N
var c2 = r.CreateCell(2); c2.SetCellValue(x.name); c2.CellStyle = f_name; // Guard's Name
var c3 = r.CreateCell(3); c3.SetCellValue(x.bank); c3.CellStyle = f_name; // Bank
var c4 = r.CreateCell(4); c4.SetCellValue(x.account); c4.CellStyle = f_acc; // Account
var c5 = r.CreateCell(5); c5.SetCellValue((double)x.salary); c5.CellStyle = f_amt1; // Salary
var c6 = r.CreateCell(5); c6.SetCellValue((double)x.overtime); c6.CellStyle = f_amt1; // Overtime
var c7 = r.CreateCell(5); c7.SetCellValue((double)x.bonus); c7.CellStyle = f_amt1; // Bonus
var c8 = r.CreateCell(5); c8.SetCellValue((double)x.offences); c8.CellStyle = f_amt2; // Penalty
var c9 = r.CreateCell(5); c9.SetCellValue((double)x.absent); c9.CellStyle = f_amt2; // Absent Fee
var c0 = r.CreateCell(5); c0.SetCellValue((double)x.net); c0.CellStyle = f_amt1; // Payable Salary
}
wb.Write(fs);
}
}
catch
{
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment