Notes

Web Controller for Export Excel
/// <summary>
/// Open pop-up Hourly Excel For Invoice
/// </summary>
/// <returns></returns>
public IActionResult HourlyExcelForInvoice()
{
return PartialView(APIUrls.HourlyExcelForInvoice);
}

/// <summary>
/// Print Hourly Excel For Invoice
/// </summary>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <returns></returns>
[HttpGet]
public async Task<IActionResult> PrintHourlyExcelForInvoice(string startDate, string endDate)
{
try
{

InvoiceMasterModelForHourlyEmployee model;
model = await invoiceService.GetInvoiceForHourlyEmployee(startDate, endDate);

DataTable dt = new DataTable();
if (model.InvoiceModelListForHourlyEmployee != null)
{
dt = excelService.ToConvertDataTable(model.InvoiceModelListForHourlyEmployee);
}
using (XLWorkbook wb = new XLWorkbook())
{
var worksheet = wb.Worksheets.Add(InvoiceConstants.HourlyInvoicesWorksheet);
worksheet.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
var dataRange = worksheet.Range("A1:AC1");
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;
int columnIndex22 = 22;
int columnIndex23 = 23;
int columnIndex24 = 24;
int columnIndex25 = 25;
int columnIndex26 = 26;
int columnIndex27 = 27;
int columnIndex28 = 28;
int columnIndex29 = 29;
//int columnIndex30 = 30;

double rowWidth1 = 17;
double rowWidth2 = 11;
double rowWidth3 = 28;
double rowWidth4 = 23;
double rowWidth5 = 36;
double rowWidth6 = 28;
double rowWidth7 = 28;
double rowWidth8 = 28;
double rowWidth9 = 28;
double rowWidth10 = 28;
double rowWidth11 = 28;
double rowWidth12 = 28;
double rowWidth13 = 18;
double rowWidth14 = 14;
double rowWidth15 = 14;
double rowWidth16 = 16;
double rowWidth17 = 23;
double rowWidth18 = 16;
double rowWidth19 = 19;
double rowWidth20 = 25;
double rowWidth21 = 15;
double rowWidth22 = 17;
double rowWidth23 = 17;
double rowWidth24 = 32;
double rowWidth25 = 35;
double rowWidth26 = 15;
double rowWidth27 = 15;
double rowWidth28 = 19;
double rowWidth29 = 16;
//double rowWidth30 = 37;

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.Column(columnIndex22).Width = rowWidth22;
worksheet.Column(columnIndex23).Width = rowWidth23;
worksheet.Column(columnIndex24).Width = rowWidth24;
worksheet.Column(columnIndex25).Width = rowWidth25;
worksheet.Column(columnIndex26).Width = rowWidth26;
worksheet.Column(columnIndex27).Width = rowWidth27;
worksheet.Column(columnIndex28).Width = rowWidth28;
worksheet.Column(columnIndex29).Width = rowWidth29;
//worksheet.Column(columnIndex30).Width = rowWidth30;

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.RegularHrs1;
worksheet.Cell("G1").Value = InvoiceConstants.RegularHrs2;
worksheet.Cell("H1").Value = InvoiceConstants.RegularHrs3;
worksheet.Cell("I1").Value = InvoiceConstants.RegularHrs4;
worksheet.Cell("J1").Value = InvoiceConstants.RegularHrs5;
worksheet.Cell("K1").Value = InvoiceConstants.RegularHrs6;
worksheet.Cell("L1").Value = InvoiceConstants.RegularHrs7;
worksheet.Cell("M1").Value = InvoiceConstants.VariableHours;
worksheet.Cell("N1").Value = InvoiceConstants.Quantity;
worksheet.Cell("O1").Value = InvoiceConstants.Price;
worksheet.Cell("P1").Value = InvoiceConstants.Amount;
worksheet.Cell("Q1").Value = InvoiceConstants.LineTaxCodeName;
worksheet.Cell("R1").Value = InvoiceConstants.TAXS;
worksheet.Cell("S1").Value = InvoiceConstants.PONumber;
worksheet.Cell("T1").Value = InvoiceConstants.Contact;
worksheet.Cell("U1").Value = InvoiceConstants.InvoiceNumber;
worksheet.Cell("V1").Value = InvoiceConstants.InvoiceDate;
worksheet.Cell("W1").Value = InvoiceConstants.ServiceDate;
worksheet.Cell("X1").Value = InvoiceConstants.LineDescription;
worksheet.Cell("Y1").Value = InvoiceConstants.ARAccount;
worksheet.Cell("Z1").Value = InvoiceConstants.Terms;
worksheet.Cell("AA1").Value = InvoiceConstants.ClassName;
worksheet.Cell("AB1").Value = InvoiceConstants.AcceptanceCode;
worksheet.Cell("AC1").Value = InvoiceConstants.EmailCC;
//worksheet.Cell("AD1").Value = "Email";

var currentRow = 1;
if (model.InvoiceModelListForHourlyEmployee != null)
{
if (model.InvoiceModelListForHourlyEmployee.Any())
{
foreach (var user in model.InvoiceModelListForHourlyEmployee)
{
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.RegularHrs1);
worksheet.Cell(currentRow, 7).Value = Convert.ToDecimal(user.RegularHrs2);
worksheet.Cell(currentRow, 8).Value = Convert.ToDecimal(user.RegularHrs3);
worksheet.Cell(currentRow, 9).Value = Convert.ToDecimal(user.RegularHrs4);
worksheet.Cell(currentRow, 10).Value = Convert.ToDecimal(user.RegularHrs5);
worksheet.Cell(currentRow, 11).Value = Convert.ToDecimal(user.RegularHrs6);
worksheet.Cell(currentRow, 12).Value = Convert.ToDecimal(user.RegularHrs7);
worksheet.Cell(currentRow, 13).Value = user.VariableHours;
worksheet.Cell(currentRow, 14).Value = Convert.ToDecimal(user.QuantityTotalHours);
worksheet.Cell(currentRow, 15).Value = user.PriceBillRate;
worksheet.Cell(currentRow, 16).Value = user.Amount;
worksheet.Cell(currentRow, 17).Value = user.LineTaxCodeName;
worksheet.Cell(currentRow, 18).Value = user.Tax;
worksheet.Cell(currentRow, 19).Value = user.PONumber;
worksheet.Cell(currentRow, 20).Value = user.ClientContact;
worksheet.Cell(currentRow, 21).Value = Convert.ToInt32(user.InvoiceNumber);
worksheet.Cell(currentRow, 22).Value = user.InvoiceDate;
worksheet.Cell(currentRow, 23).Value = user.ServiceDate;
worksheet.Cell(currentRow, 24).Value = user.LineDescription;
worksheet.Cell(currentRow, 25).Value = user.ARAccount;
worksheet.Cell(currentRow, 26).Value = user.Terms;
worksheet.Cell(currentRow, 27).Value = user.ClassName;
worksheet.Cell(currentRow, 28).Value = user.AcceptanceCode;
worksheet.Cell(currentRow, 29).Value = user.EmailCC;
//worksheet.Cell(currentRow, 30).Value = user.Email;
}

if (startDate != null && endDate != null)
{
for (int i = 0; i < 1; i++)
{
currentRow++;
worksheet.Cell("F1").Value = model.InvoiceModelListForHourlyEmployee[0].FirstStartDate?.Replace(AppConstants.Dash, AppConstants.Slash) + AppConstants.To + model.InvoiceModelListForHourlyEmployee[0].FirstEndDate?.Replace(AppConstants.Dash, AppConstants.Slash);

var secondStartDate = model.InvoiceModelListForHourlyEmployee[0].SecondStartDate?.Replace(AppConstants.Dash, AppConstants.Slash);
var secondEndDate = model.InvoiceModelListForHourlyEmployee[0].SecondEndDate?.Replace(AppConstants.Dash, AppConstants.Slash);
if (secondStartDate != AppConstants.DefaultDate && secondEndDate != AppConstants.DefaultDate)
{
worksheet.Cell("G1").Value = secondStartDate + AppConstants.To + secondEndDate;
}
var thirdStartDate = model.InvoiceModelListForHourlyEmployee[0].ThirdStartDate?.Replace(AppConstants.Dash, AppConstants.Slash);
var thirdEndDate = model.InvoiceModelListForHourlyEmployee[0].ThirdEndDate?.Replace(AppConstants.Dash, AppConstants.Slash);
if (thirdStartDate != AppConstants.DefaultDate && thirdEndDate != AppConstants.DefaultDate)
{
worksheet.Cell("H1").Value = thirdStartDate + AppConstants.To + thirdEndDate;
}
var fourthStartDate = model.InvoiceModelListForHourlyEmployee[0].FourthStartDate?.Replace(AppConstants.Dash, AppConstants.Slash);
var fourthEndDate = model.InvoiceModelListForHourlyEmployee[0].FourthEndDate?.Replace(AppConstants.Dash, AppConstants.Slash);
if (fourthStartDate != AppConstants.DefaultDate && fourthEndDate != AppConstants.DefaultDate)
{
worksheet.Cell("I1").Value = fourthStartDate + AppConstants.To + fourthEndDate;
}
var fifthStartDate = model.InvoiceModelListForHourlyEmployee[0].FifthStartDate?.Replace(AppConstants.Dash, AppConstants.Slash);
var fifthEndDate = model.InvoiceModelListForHourlyEmployee[0].FifthEndDate?.Replace(AppConstants.Dash, AppConstants.Slash);
if (fifthStartDate != AppConstants.DefaultDate && fifthEndDate != AppConstants.DefaultDate)
{
worksheet.Cell("J1").Value = fifthStartDate + AppConstants.To + fifthEndDate;
}
var sixthStartDate = model.InvoiceModelListForHourlyEmployee[0].SixthStartDate?.Replace(AppConstants.Dash, AppConstants.Slash);
var sixthEndDate = model.InvoiceModelListForHourlyEmployee[0].SixthEndDate?.Replace(AppConstants.Dash, AppConstants.Slash);
if (sixthStartDate != AppConstants.DefaultDate && sixthEndDate != AppConstants.DefaultDate)
{
worksheet.Cell("K1").Value = sixthStartDate + AppConstants.To + sixthEndDate;
}
var seventhStartDate = model.InvoiceModelListForHourlyEmployee[0].SeventhStartDate?.Replace(AppConstants.Dash, AppConstants.Slash);
var seventhEndDate = model.InvoiceModelListForHourlyEmployee[0].SeventhEndDate?.Replace(AppConstants.Dash, AppConstants.Slash);
if (seventhStartDate != AppConstants.DefaultDate && seventhEndDate != AppConstants.DefaultDate)
{
worksheet.Cell("L1").Value = seventhStartDate + AppConstants.To + seventhEndDate;
}
}
}
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 RegularHrs1Total = 0;
decimal RegularHrs2Total = 0;
decimal RegularHrs3Total = 0;
decimal RegularHrs4Total = 0;
decimal RegularHrs5Total = 0;
decimal RegularHrs6Total = 0;
decimal RegularHrs7Total = 0;
decimal VaribleHoursTotal = 0;
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()))
{
RegularHrs1Total = RegularHrs1Total + Convert.ToDecimal(dt.Rows[i][6].ToString());
}
if (!string.IsNullOrEmpty(dt.Rows[i][7].ToString()))
{
RegularHrs2Total = RegularHrs2Total + Convert.ToDecimal(dt.Rows[i][7].ToString());
}
if (!string.IsNullOrEmpty(dt.Rows[i][8].ToString()))
{
RegularHrs3Total = RegularHrs3Total + Convert.ToDecimal(dt.Rows[i][8].ToString());
}
if (!string.IsNullOrEmpty(dt.Rows[i][9].ToString()))
{
RegularHrs4Total = RegularHrs4Total + Convert.ToDecimal(dt.Rows[i][9].ToString());
}
if (!string.IsNullOrEmpty(dt.Rows[i][10].ToString()))
{
RegularHrs5Total = RegularHrs5Total + Convert.ToDecimal(dt.Rows[i][10].ToString());
}
if (!string.IsNullOrEmpty(dt.Rows[i][11].ToString()))
{
RegularHrs6Total = RegularHrs6Total + Convert.ToDecimal(dt.Rows[i][11].ToString());
}
if (!string.IsNullOrEmpty(dt.Rows[i][12].ToString()))
{
RegularHrs7Total = RegularHrs7Total + Convert.ToDecimal(dt.Rows[i][12].ToString());
}
if (!string.IsNullOrEmpty(dt.Rows[i][13].ToString()))
{
VaribleHoursTotal = VaribleHoursTotal + Convert.ToDecimal(dt.Rows[i][13].ToString());
}
if (!string.IsNullOrEmpty(dt.Rows[i][14].ToString()))
{
QuantityTotal = QuantityTotal + Convert.ToDecimal(dt.Rows[i][14].ToString());
}
if (!string.IsNullOrEmpty(dt.Rows[i][15].ToString()))
{
PriceTotal = PriceTotal + Convert.ToDecimal(dt.Rows[i][15].ToString()?.Replace(AppConstants.Doller, string.Empty));
}
if (!string.IsNullOrEmpty(dt.Rows[i][16].ToString()))
{
AmountTotal = AmountTotal + Convert.ToDecimal(dt.Rows[i][16].ToString()?.Replace(AppConstants.Doller, string.Empty));
}
if (!string.IsNullOrEmpty(dt.Rows[i][18].ToString()))
{
TaxTotal = TaxTotal + Convert.ToDecimal(dt.Rows[i][18].ToString()?.Replace(AppConstants.Doller, string.Empty));
}
}
totalRow.Cell("F").Value = RegularHrs1Total.ToString(AppConstants.DefaultFiveDigits)?.Replace(AppConstants.Doller, string.Empty);
totalRow.Cell("G").Value = RegularHrs2Total.ToString(AppConstants.DefaultFiveDigits)?.Replace(AppConstants.Doller, string.Empty);
totalRow.Cell("H").Value = RegularHrs3Total.ToString(AppConstants.DefaultFiveDigits)?.Replace(AppConstants.Doller, string.Empty);
totalRow.Cell("I").Value = RegularHrs4Total.ToString(AppConstants.DefaultFiveDigits)?.Replace(AppConstants.Doller, string.Empty);
totalRow.Cell("J").Value = RegularHrs5Total.ToString(AppConstants.DefaultFiveDigits)?.Replace(AppConstants.Doller, string.Empty);
totalRow.Cell("K").Value = RegularHrs6Total.ToString(AppConstants.DefaultFiveDigits)?.Replace(AppConstants.Doller, string.Empty);
totalRow.Cell("L").Value = RegularHrs7Total.ToString(AppConstants.DefaultFiveDigits)?.Replace(AppConstants.Doller, string.Empty);
totalRow.Cell("M").Value = VaribleHoursTotal.ToString(AppConstants.DefaultFiveDigits)?.Replace(AppConstants.Doller, string.Empty);
totalRow.Cell("N").Value = QuantityTotal.ToString(AppConstants.DefaultFiveDigits)?.Replace(AppConstants.Doller, string.Empty);
totalRow.Cell("O").Value = PriceTotal;
totalRow.Cell("P").Value = AmountTotal;
totalRow.Cell("R").Value = TaxTotal;

using (MemoryStream stream = new MemoryStream())
{
wb.SaveAs(stream);
string fileName = InvoiceConstants.HoursInvoicesFileName;
return File(stream.ToArray(), AppConstants.OpenXMLFormats, fileName);
}
}
}
return View(model);
}
}
catch (Exception ex)
{
Log.Error(ex.Message);
throw;
}
}

//Sevice Class
public async Task<InvoiceMasterModelForHourlyEmployee> GetInvoiceForHourlyEmployee(string startDate, string endDate)
{
try
{
InvoiceMasterModelForHourlyEmployee invoiceMasterModelForHourlyEmployee = new();
if (startDate == null && endDate == null)
{
startDate = AppConstants.ServiceDefaultDate;
endDate = AppConstants.ServiceDefaultDate;
}
var apiUrl = InvoiceConstants.GetInvoiceForHourlyEmployee + startDate + InvoiceConstants.EndDate + endDate;
var content = new StringContent(JsonConvert.SerializeObject(invoiceMasterModelForHourlyEmployee), Encoding.UTF8, AppURL.ApplicationJsonURL);

using (HttpResponseMessage response = await Client.GetAsync(apiUrl))

if (response.StatusCode == HttpStatusCode.OK)
{
string apiresonse = await response.Content.ReadAsStringAsync();
invoiceMasterModelForHourlyEmployee = JsonConvert.DeserializeObject<InvoiceMasterModelForHourlyEmployee>(apiresonse.ToString());
}
return invoiceMasterModelForHourlyEmployee;
}
catch (HttpRequestException ex)
{
throw new Exception(AppConstants.APIErrorCalling + ex.Message);
}
}