-
-
Save CharlesOkwuagwu/a9a34647070b87a86a170875a8468ced to your computer and use it in GitHub Desktop.
Using NPOI, create Excel file from an existing file, retaining formatting
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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