Notes

public async Task<IActionResult> PrintSalaryExcelForInvoice(string startDate, string endDate, string companyId, int invoiceNumber)
{
try
{

InvoiceMasterModelForSalariedEmployee model;
model = await invoiceService.GetInvoiceForSalariedEmployee(startDate, endDate, companyId, invoiceNumber);

DataTable dt = new DataTable();
if (model.InvoiceModelListForSalariedEmployee != null)
{
dt = excelService.ToConvertDataTable(model.InvoiceModelListForSalariedEmployee);
}
using (XLWorkbook wb = new XLWorkbook())
{
var worksheet = wb.Worksheets.Add(InvoiceConstants.SalaryInvoicesWorksheet);
worksheet.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
var dataRange = worksheet.Range("A1:U1");
dataRange.SetAutoFilter();
worksheet.SheetView.Freeze(1, 0);
int columnIndex1 = 1;
int columnIndex2 = 2;
int columnIndex3 = 3;
int columnIndex4 = 4;
int columnIndex5 = 5;
int columnIndex6 = 6;
int columnIndex7 = 7;
int columnIndex8 = 8;
int columnIndex9 = 9;
int columnIndex10 = 10;
int columnIndex11 = 11;
int columnIndex12 = 12;
int columnIndex13 = 13;
int columnIndex14 = 14;
int columnIndex15 = 15;
int columnIndex16 = 16;
int columnIndex17 = 17;
int columnIndex18 = 18;
int columnIndex19 = 19;
int columnIndex20 = 20;
int columnIndex21 = 21;

double rowWidth1 = 20;
double rowWidth2 = 10;
double rowWidth3 = 24;
double rowWidth4 = 21;
double rowWidth5 = 35;
double rowWidth6 = 14;
double rowWidth7 = 15;
double rowWidth8 = 16;
double rowWidth9 = 24;
double rowWidth10 = 14;
double rowWidth11 = 15;
double rowWidth12 = 28;
double rowWidth13 = 15;
double rowWidth14 = 16;
double rowWidth15 = 16;
double rowWidth16 = 32;
double rowWidth17 = 35;
double rowWidth18 = 14;
double rowWidth19 = 14;
double rowWidth20 = 18;
double rowWidth21 = 16;

worksheet.Column(columnIndex1).Width = rowWidth1;
worksheet.Column(columnIndex2).Width = rowWidth2;
worksheet.Column(columnIndex3).Width = rowWidth3;
worksheet.Column(columnIndex4).Width = rowWidth4;
worksheet.Column(columnIndex5).Width = rowWidth5;
worksheet.Column(columnIndex6).Width = rowWidth6;
worksheet.Column(columnIndex7).Width = rowWidth7;
worksheet.Column(columnIndex8).Width = rowWidth8;
worksheet.Column(columnIndex9).Width = rowWidth9;
worksheet.Column(columnIndex10).Width = rowWidth10;
worksheet.Column(columnIndex11).Width = rowWidth11;
worksheet.Column(columnIndex12).Width = rowWidth12;
worksheet.Column(columnIndex13).Width = rowWidth13;
worksheet.Column(columnIndex14).Width = rowWidth14;
worksheet.Column(columnIndex15).Width = rowWidth15;
worksheet.Column(columnIndex16).Width = rowWidth16;
worksheet.Column(columnIndex17).Width = rowWidth17;
worksheet.Column(columnIndex18).Width = rowWidth18;
worksheet.Column(columnIndex19).Width = rowWidth19;
worksheet.Column(columnIndex20).Width = rowWidth20;
worksheet.Column(columnIndex21).Width = rowWidth21;

worksheet.TabColor = XLColor.WhiteSmoke;

worksheet.Cell("A1").Value = InvoiceConstants.Form;
worksheet.Cell("B1").Value = InvoiceConstants.Status;
worksheet.Cell("C1").Value = InvoiceConstants.Customer;
worksheet.Cell("D1").Value = InvoiceConstants.LastName;
worksheet.Cell("E1").Value = InvoiceConstants.ITEM;
worksheet.Cell("F1").Value = InvoiceConstants.Quantity;
worksheet.Cell("G1").Value = InvoiceConstants.Price;
worksheet.Cell("H1").Value = InvoiceConstants.Amount;
worksheet.Cell("I1").Value = InvoiceConstants.LineTaxCodeName;
worksheet.Cell("J1").Value = InvoiceConstants.TAXS;
worksheet.Cell("K1").Value = InvoiceConstants.PONumber;
worksheet.Cell("L1").Value = InvoiceConstants.Contact;
worksheet.Cell("M1").Value = InvoiceConstants.InvoiceNumber;
worksheet.Cell("N1").Value = InvoiceConstants.InvoiceDate;
worksheet.Cell("O1").Value = InvoiceConstants.ServiceDate;
worksheet.Cell("P1").Value = InvoiceConstants.LineDescription;
worksheet.Cell("Q1").Value = InvoiceConstants.ARAccount;
worksheet.Cell("R1").Value = InvoiceConstants.Terms;
worksheet.Cell("S1").Value = InvoiceConstants.ClassName;
worksheet.Cell("T1").Value = InvoiceConstants.AcceptanceCode;
worksheet.Cell("U1").Value = InvoiceConstants.EmailCC;

var currentRow = 1;
if (model.InvoiceModelListForSalariedEmployee != null)
{
if (model.InvoiceModelListForSalariedEmployee.Any())
{
foreach (var user in model.InvoiceModelListForSalariedEmployee)
{
currentRow++;
worksheet.Cell(currentRow, 1).Value = user.Form;
worksheet.Cell(currentRow, 2).Value = user.Status;
worksheet.Cell(currentRow, 3).Value = user.Customer;
worksheet.Cell(currentRow, 4).Value = user.LastName;
worksheet.Cell(currentRow, 5).Value = user.ITEM;
worksheet.Cell(currentRow, 6).Value = Convert.ToDecimal(user.QuantityTotalHours);
worksheet.Cell(currentRow, 7).Value = user.PriceBillRate;
worksheet.Cell(currentRow, 8).Value = user.Amount;
worksheet.Cell(currentRow, 9).Value = user.LineTaxCodeName;
worksheet.Cell(currentRow, 10).Value = user.Tax;
worksheet.Cell(currentRow, 11).Value = user.PONumber;
worksheet.Cell(currentRow, 12).Value = user.ClientContact;
worksheet.Cell(currentRow, 13).Value = Convert.ToInt32(user.InvoiceNumber);
worksheet.Cell(currentRow, 14).Value = user.InvoiceDate;
worksheet.Cell(currentRow, 15).Value = user.ServiceDate;
worksheet.Cell(currentRow, 16).Value = user.LineDescription;
worksheet.Cell(currentRow, 17).Value = user.ARAccount;
worksheet.Cell(currentRow, 18).Value = user.Terms;
worksheet.Cell(currentRow, 19).Value = user.ClassName;
worksheet.Cell(currentRow, 20).Value = user.AcceptanceCode;
worksheet.Cell(currentRow, 21).Value = user.EmailCC;
}

int lastRow = worksheet.LastRowUsed().RowNumber();
var totalRow = worksheet.Row(lastRow + 1);
totalRow.Cell("A").Value = AppConstants.Total;
totalRow.Style.Font.SetBold().Font.FontSize = 12;
totalRow.Style.NumberFormat.Format = AppConstants.DollerThreeDigits;

decimal QuantityTotal = 0;
decimal PriceTotal = 0;
decimal AmountTotal = 0;
decimal TaxTotal = 0;

for (int i = 0; i < dt.Rows.Count; i++)
{
if (!string.IsNullOrEmpty(dt.Rows[i][6].ToString()))
{
QuantityTotal = QuantityTotal + Convert.ToDecimal(dt.Rows[i][6].ToString());
}
if (!string.IsNullOrEmpty(dt.Rows[i][7].ToString()))
{
PriceTotal = PriceTotal + Convert.ToDecimal(dt.Rows[i][7].ToString()?.Replace(AppConstants.Doller, string.Empty));
}
if (!string.IsNullOrEmpty(dt.Rows[i][8].ToString()))
{
AmountTotal = AmountTotal + Convert.ToDecimal(dt.Rows[i][8].ToString()?.Replace(AppConstants.Doller, string.Empty));
}
if (!string.IsNullOrEmpty(dt.Rows[i][10].ToString()))
{
TaxTotal = TaxTotal + Convert.ToDecimal(dt.Rows[i][10].ToString()?.Replace(AppConstants.Doller, string.Empty));
}
}
totalRow.Cell("F").Value = QuantityTotal.ToString(AppConstants.DefaultFiveDigits)?.Replace(AppConstants.Doller, string.Empty);
totalRow.Cell("G").Value = PriceTotal;
totalRow.Cell("H").Value = AmountTotal;
totalRow.Cell("J").Value = TaxTotal;
using (MemoryStream stream = new MemoryStream())
{
wb.SaveAs(stream);
string fileName = InvoiceConstants.SalaryInvoicesFileName;
return File(stream.ToArray(), AppConstants.OpenXMLFormats, fileName);
}
}
}
return View(model);
}
}
catch (Exception ex)
{
Log.Error(ex.Message);
throw;
}
}