private void GenerateWorkbook(List <SalesViewModel> list, string ReportName, HttpApplication application)
{ // TODO: Replace with List<Product> or else suitable
using (ExcelEngine excelEngine = new ExcelEngine())
{
excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016;
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
#region Cell Styles
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.Font.Bold = true;
headerStyle.Font.Size = 12;
headerStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
headerStyle.Color = Color.FromArgb(255, 174, 33);
IStyle contentStyle = workbook.Styles.Add("ContentStyle");
contentStyle.VerticalAlignment = ExcelVAlign.VAlignTop;
#endregion
#region Header Cells
worksheet.Range[1, 1].Text = "ID";
worksheet.Range[1, 1].CellStyle = headerStyle;
worksheet.SetColumnWidth(1, 4.0);
worksheet.Range[1, 2].Text = "Name";
worksheet.Range[1, 2].CellStyle = headerStyle;
worksheet.SetColumnWidth(2, 28.0);
worksheet.Range[1, 3].Text = "Description";
worksheet.Range[1, 3].CellStyle = headerStyle;
worksheet.SetColumnWidth(3, 38.0);
worksheet.Range[1, 4].Text = "Price";
worksheet.Range[1, 4].CellStyle = headerStyle;
worksheet.SetColumnWidth(4, 16.0);
worksheet.Range[1, 5].Text = "Image";
worksheet.Range[1, 5].CellStyle = headerStyle;
worksheet.SetColumnWidth(5, 27.86);
worksheet.Range[1, 6].Text = "Category";
worksheet.Range[1, 6].CellStyle = headerStyle;
worksheet.SetColumnWidth(6, 24.0);
worksheet.Range[1, 7].Text = "Status";
worksheet.Range[1, 7].CellStyle = headerStyle;
worksheet.SetColumnWidth(7, 8.0);
worksheet.Range[1, 8].Text = "Sellings";
worksheet.Range[1, 8].CellStyle = headerStyle;
worksheet.SetColumnWidth(8, 8.0);
#endregion
int startRowIndex = 2;
for (int i = startRowIndex; i < (list.Count + startRowIndex); i++) // rows
{
#region Feeding Content
worksheet.Range[i, 1].Text = list[i - startRowIndex].ID.ToString();
worksheet.Range[i, 1].CellStyle = contentStyle;
worksheet.Range[i, 1].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
worksheet.Range[i, 2].Text = list[i - startRowIndex].Name;
worksheet.Range[i, 2].CellStyle = contentStyle;
worksheet.Range[i, 3].Text = list[i - startRowIndex].Description;
worksheet.Range[i, 3].CellStyle = contentStyle;
worksheet.Range[i, 4].Text = Func.Currencyfy(list[i - startRowIndex].Price);
worksheet.Range[i, 4].CellStyle = contentStyle;
worksheet.Range[i, 4].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
System.Drawing.Image image = System.Drawing.Image.FromFile(PathUtility.CombinePaths(Config.StoragePathProduct,
list[i - startRowIndex].ID.ToString(),
list[i - startRowIndex].ImageName));
System.Drawing.Image image_r = Imager.Resize(image, 200, 150, true);
IPictureShape shape = worksheet.Pictures.AddPicture(i, 5, image_r);
worksheet.SetRowHeightInPixels(i, image_r.Height);
worksheet.Range[i, 6].Text = list[i - startRowIndex].Category;
worksheet.Range[i, 6].CellStyle = contentStyle;
worksheet.Range[i, 7].Text = (list[i - startRowIndex].Status) ? "Active" : "Inactive";
worksheet.Range[i, 7].CellStyle = contentStyle;
worksheet.Range[i, 7].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
worksheet.Range[i, 8].Text = list[i - startRowIndex].Sellings.ToString();
worksheet.Range[i, 8].CellStyle = contentStyle;
worksheet.Range[i, 8].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
#endregion
}
workbook.SaveAs($"Report - {ReportName}.xlsx", application.Response, ExcelDownloadType.Open);
}
}
public async Task <IActionResult> mamxl(int id)
{
var user = await _userManager.FindByNameAsync(User.Identity.Name);
var items = _context.MamreqDetails.Include(mamStock => mamStock.R).Where(m => m.Rid.Equals(id) && (m.R.UserName.Equals(User.Identity.Name) || m.R.Tenant.Equals(user.TenantId))).Include(m => m.SId).AsNoTracking()
.Select(m => new mamxlVm()
{
item = m.SId.Item,
buffer = m.SId.Buffer,
formname = m.FormName,
benefactories = m.NoOfBenificiaries.GetValueOrDefault(),
zarib = m.SId.Zarib,
adj = m.Adjustment.GetValueOrDefault(),
balance = m.CurrentBalance.GetValueOrDefault(),
comment = m.AdjustmentComment,
}).ToList();
var report = _context.Mamreq.Where(m => m.Rid.Equals(id) && (m.UserName.Equals(User.Identity.Name) || m.Tenant.Equals(user.TenantId))).SingleOrDefault();
if (!items.Any())
{
return(BadRequest());
}
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.IsGridLinesVisible = false;
FileStream file = new FileStream(@"Data/pic.png", FileMode.Open, FileAccess.Read);
byte[] byteArray = new byte[file.Length];
file.Read(byteArray, 0, (int)file.Length);
file.Dispose();
if (byteArray != null)
{
MemoryStream s = new MemoryStream(byteArray);
Syncfusion.Drawing.Image img = Syncfusion.Drawing.Image.FromStream(s);
IPictureShape shape = sheet.Pictures.AddPicture(2, 11, img);
}
sheet["A3:B3"].Merge();
sheet["C3:D3"].Merge();
sheet["A3"].Text = "Province";
sheet.Range["C3"].Text = report.ProvCode;
sheet.Range["C3"].CellStyle.Font.Italic = true;
sheet["A4:B4"].Merge();
sheet["C4:D4"].Merge();
sheet["A4"].Text = "Implementing Agency";
sheet.Range["C4"].Text = report.ImpCode;
sheet.Range["C4"].CellStyle.Font.Italic = true;
sheet["A5:B5"].Merge();
sheet["C5:D5"].Merge();
sheet["A5"].Text = "Request Year";
sheet.Range["C5"].Value = report.ReqYear.ToString();
sheet.Range["C5"].CellStyle.Font.Italic = true;
sheet["A6:B6"].Merge();
sheet["C6:D6"].Merge();
sheet["A6"].Text = "Request Month";
sheet.Range["C6"].Value = report.ReqMonth.ToString();
sheet.Range["C6"].CellStyle.Font.Italic = true;
sheet.Range["A3:D6"].BorderAround();
sheet.Range["A3:D6"].BorderInside(ExcelLineStyle.Thin, ExcelKnownColors.Black);
sheet["T4:U4"].Merge();
sheet["V4:W4"].Merge();
sheet["T4"].Text = "No of months";
sheet.Range["V4"].Value = report.Month.ToString();
sheet.Range["V4"].CellStyle.Font.Italic = true;
sheet["T5:U5"].Merge();
sheet["V5:W5"].Merge();
sheet["T5"].Text = "Requested by";
sheet.Range["V5"].Text = report.ReqBy;
sheet.Range["V5"].CellStyle.Font.Italic = true;
sheet["T6:U6"].Merge();
sheet["V6:W6"].Merge();
sheet["T6"].Text = "Request Date";
sheet.Range["V6"].Text = report.UpdateDate.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture);;
sheet.Range["V6"].CellStyle.Font.Italic = true;
sheet.Range["T4:W6"].BorderAround();
sheet.Range["T4:W6"].BorderInside(ExcelLineStyle.Thin, ExcelKnownColors.Black);
sheet["J5:L5"].Merge();
sheet["J5"].Text = " Ministry of Public Health";
sheet["I6:M6"].Merge();
sheet["I6"].Text = " General Directorate of Preventive Medicine";
sheet["j7:L7"].Merge();
sheet["j7"].Text = " Public Nutrition Directorate ";
sheet["j8:L8"].Merge();
sheet["j8"].Text = " MAM Monthly Request Form";
sheet["A10:C10"].Merge();
sheet["A10"].Text = "Number of Health Facilities Covered ";
sheet["A11:B11"].Merge();
sheet["A12:B12"].Merge();
sheet["A11"].Text = "PH";
sheet["A11"].CellStyle.ColorIndex = ExcelKnownColors.Aqua;
sheet["A12"].Value = report.Ph.ToString();
sheet["C11:D11"].Merge();
sheet["C12:D12"].Merge();
sheet["C11"].Text = "DH";
sheet["C11"].CellStyle.ColorIndex = ExcelKnownColors.Aqua;
sheet["C12"].Value = report.Dh.ToString();
sheet["E11:F11"].Merge();
sheet["E12:F12"].Merge();
sheet["E11"].Text = "CHC";
sheet["E11"].CellStyle.ColorIndex = ExcelKnownColors.Aqua;
sheet["E12"].Value = report.Chc.ToString();
sheet["G11:H11"].Merge();
sheet["G12:H12"].Merge();
sheet["G11"].Text = "BHC";
sheet["G11"].CellStyle.ColorIndex = ExcelKnownColors.Aqua;
sheet["G12"].Value = report.Bhc.ToString();
sheet["I11:J11"].Merge();
sheet["I12:J12"].Merge();
sheet["I11"].Text = "SHC";
sheet["I11"].CellStyle.ColorIndex = ExcelKnownColors.Aqua;
sheet["I12"].Value = report.Shc.ToString();
sheet["K11:L11"].Merge();
sheet["K12:L12"].Merge();
sheet["K11"].Text = "MHT";
sheet["K11"].CellStyle.ColorIndex = ExcelKnownColors.Aqua;
sheet["K12"].Value = report.Mht.ToString();
sheet.Range["A11:L12"].BorderAround();
sheet["A16:F16"].Merge();
sheet["A16"].Text = "Requested supply for this period";
sheet["A17:C17"].Merge();
sheet["A17:C17"].CellStyle.ColorIndex = ExcelKnownColors.Aqua;
sheet["A17"].Text = "Requested Item";
sheet["D17:E17"].Merge();
sheet["D17:E17"].CellStyle.ColorIndex = ExcelKnownColors.Aqua;
sheet["D17"].Text = "Program";
sheet["F17:H17"].Merge();
sheet["F17:H17"].CellStyle.ColorIndex = ExcelKnownColors.Aqua;
sheet["F17"].Text = "Balance from Last Release";
sheet["I17:K17"].Merge();
sheet["I17:K17"].CellStyle.ColorIndex = ExcelKnownColors.Aqua;
sheet["I17"].Text = "# of beneficiaries";
sheet["L17:N17"].Merge();
sheet["L17:N17"].CellStyle.ColorIndex = ExcelKnownColors.Aqua;
sheet["L17"].Text = "Needed Quantity for Next 1 Month";
sheet["O17:P17"].Merge();
sheet["O17:P17"].CellStyle.ColorIndex = ExcelKnownColors.Aqua;
sheet["O17"].Text = "Buffer (%)";
sheet["Q17:R17"].Merge();
sheet["Q17:R17"].CellStyle.ColorIndex = ExcelKnownColors.Aqua;
sheet["Q17"].Text = "Adjustment";
sheet["S17"].CellStyle.ColorIndex = ExcelKnownColors.Aqua;
sheet["S17"].Text = "Total Qty Needed";
sheet["T17:U17"].CellStyle.ColorIndex = ExcelKnownColors.Aqua;
sheet["T17"].Text = "Comment/Remarks";
int i = 1;
foreach (var item in items)
{
string c = (17 + i).ToString();
sheet[$"A{c}:C{c}"].Merge();
sheet[$"A{c}"].Text = item.item;
sheet[$"D{c}:E{c}"].Merge();
sheet[$"D{c}"].Text = item.formname;
sheet[$"F{c}:H{c}"].Merge();
sheet[$"F{c}"].Value = item.balance.ToString();
sheet[$"I{c}:K{c}"].Merge();
sheet[$"I{c}"].Value = item.benefactories.ToString();
float needed = item.benefactories * item.zarib;
sheet[$"L{c}:N{c}"].Merge();
sheet[$"L{c}"].Value = (needed).ToString();
sheet[$"O{c}:P{c}"].Merge();
sheet[$"O{c}"].Value = "0";
sheet[$"Q{c}:R{c}"].Merge();
sheet[$"Q{c}"].Value = item.adj.ToString();
sheet[$"S{c}"].Value = (needed + item.balance + item.adj).ToString();
sheet[$"T{c}:U{c}"].Merge();
sheet[$"T{c}"].Text = item.comment;
i++;
}
sheet.SetRowHeight(17, 35);
sheet.Range["A17:U17"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
string rows = (items.Count() + 17).ToString();
sheet.Range[$"A17:U{rows}"].BorderAround();
sheet.Range[$"A17:U{rows}"].BorderInside(ExcelLineStyle.Thin, ExcelKnownColors.Black);
string ContentType = "Application/msexcel";
string filename = "mamreq.xlsx";
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
workbook.Close();
excelEngine.Dispose();
return(File(ms, ContentType, filename));
}
