using System; using System.Collections.Generic; using System.Globalization; using System.IO; using System.IO.Compression; using System.Linq; using System.Net; using System.Net.Http; using System.Security.Cryptography; using System.Threading.Tasks; using Microsoft.AspNetCore.Http; using Microsoft.AspNetCore.Mvc; using SuperCms.Database; using SuperCms.Models; using Syncfusion.Drawing; using Syncfusion.XlsIO; namespace SuperCms.Controllers { public class BaseController : Controller { private static readonly log4net.ILog log = log4net.LogManager.GetLogger(typeof(Program)); protected string convertToDateTimeServer(String date) { // date: DateTime oDateFrom = DateTime.Parse(date); string hour = oDateFrom.Hour < 10 ? "0" + oDateFrom.Hour : oDateFrom.Hour.ToString(); string minute = oDateFrom.Minute < 10 ? "0" + oDateFrom.Minute : oDateFrom.Minute.ToString(); string second = oDateFrom.Second < 10 ? "0" + oDateFrom.Second : oDateFrom.Second.ToString(); string month = oDateFrom.Month < 10 ? "0" + oDateFrom.Month : oDateFrom.Month.ToString(); string day = oDateFrom.Day < 10 ? "0" + oDateFrom.Day : oDateFrom.Day.ToString(); string fromCheck = month + "/" + day + "/" + oDateFrom.Year + " " + hour + ":" + minute + ":" + second; return fromCheck; //yyyy-MM-dd HH24:mm:ss } protected string getStartByMonthToString(int monthLeft) { DateTime now = DateTime.Now; DateTime time = new DateTime(now.Year, now.Month, 1, 0, 0, 0); DateTime timeB = time.AddMonths(monthLeft); return timeB.ToString("MM/dd/yyyy HH:mm:ss"); } protected DateTime getStartByMonthToDate(int monthLeft) { DateTime now = DateTime.Now; DateTime time = new DateTime(now.Year, now.Month, 1, 0, 0, 0); DateTime timeB = time.AddMonths(monthLeft); return timeB; } protected DateTime getEndByMonthToDate(int monthLeft) { DateTime now = DateTime.Now; DateTime time = new DateTime(now.Year, now.Month, 1, 23, 59, 59); DateTime timeB = time.AddMonths(monthLeft + 1).AddDays(-1); return timeB; } protected DateTime getStartByMonthToDate(DateTime now, int monthLeft) { DateTime time = new DateTime(now.Year, now.Month, 1, 0, 0, 0); DateTime timeB = time.AddMonths(monthLeft); return timeB; } protected DateTime getEndByMonthToDate(DateTime now, int monthLeft) { DateTime time = new DateTime(now.Year, now.Month, 1, 23, 59, 59); DateTime timeB = time.AddMonths(monthLeft + 1).AddDays(-1); return timeB; } protected string getStartByDayToString() { DateTime now = DateTime.Now; DateTime date = new DateTime(now.Year, now.Month, now.Day, 0, 0, 0); //int dayOfWeek = (int)now.DayOfWeek; return date.AddDays(-(7)).ToString("MM/dd/yyyy HH:mm:ss"); } protected String getEndByDayToString() { // mm/dd/yyyy hh:MM:ss PT //DateTime now = DateTime.Now; // convert to dd/mm/yyyy //return DateTime.Now.ToString("dd/MM/yyyy"); DateTime now = DateTime.Now; DateTime date = new DateTime(now.Year, now.Month, now.Day, 23, 59, 59); return date.AddDays(0).ToString("MM/dd/yyyy HH:mm:ss"); } protected DateTime getStartByDayToDate() { DateTime now = DateTime.Now; DateTime date = new DateTime(now.Year, now.Month, now.Day, 0, 0, 0); //int dayOfWeek = (int)now.DayOfWeek; return date.AddDays(-(7)); } protected DateTime getEndByDayToDate() { // mm/dd/yyyy hh:MM:ss PT //DateTime now = DateTime.Now; // convert to dd/mm/yyyy //return DateTime.Now.ToString("dd/MM/yyyy"); DateTime now = DateTime.Now; DateTime date = new DateTime(now.Year, now.Month, now.Day, 23, 59, 59); return date.AddDays(0); } protected List getRangeTime(DateTime start, DateTime end) { TimeSpan difference = end - start; List days = new List(); //days.Add(DateTime.Now); for (int i = difference.Days; i >= 0; i--) { DateTime dateG = start.AddDays(i); DateTime date = new DateTime(dateG.Year, dateG.Month, dateG.Day, 0, 0, 0); days.Add(date); } return days; } protected List getRangeTimeForExport(DateTime start, DateTime end) { TimeSpan difference = end - start; List days = new List(); days.Add(end); for (int i = difference.Days; i >= 0; i--) { DateTime dateG = start.AddDays(i); DateTime date = new DateTime(dateG.Year, dateG.Month, dateG.Day, 0, 0, 0); days.Add(date); } return days; } protected List getRangeMonth(DateTime start, DateTime end) { DateTime startD = new DateTime(start.Year, start.Month, 1); DateTime endD = new DateTime(end.Year, end.Month, 1); int diffMonths = (endD.Month + endD.Year * 12) - (startD.Month + startD.Year * 12); List days = new List(); days.Add(end); for (int i = diffMonths; i >= 0; i--) { DateTime dateG = start.AddMonths(i); DateTime date; if (i == 0) { date = new DateTime(dateG.Year, dateG.Month, dateG.Day, 0, 0, 0); } else { date = new DateTime(dateG.Year, dateG.Month, 1, 0, 0, 0); } days.Add(date); } return days; } protected List distributedByTelcoAndCompany(ConnTelcos telcos, ConnCompanies companies, ConnConfigs services, int count, List result) { if (count >= services.data.Count) { return result; } else { ConnConfig service = services.data[count]; if (service.companyID == null || service.telcoID == null) { return distributedByTelcoAndCompany(telcos, companies, services, count + 1, result); } else { log.Info(service.telcoID + " " + service.serviceName); List resultt = result; DistributedByTelcoAndCompany dis = result.Find(x => x.telco.id == service.telcoID); if (dis == null) { // add telco to result DistributedByTelcoAndCompany newEle = new DistributedByTelcoAndCompany(); newEle.telco = telcos.data.Find(x => x.id == service.telcoID); // add company ConnCompany company = companies.data.Find(x => x.id == service.companyID); newEle.companies = new ConnCompanies(); newEle.companies.data = new List(); newEle.companies.data.Add(company); // add service newEle.services = new ConnConfigs(); newEle.services.data = new List(); newEle.services.data.Add(service); result.Add(newEle); return distributedByTelcoAndCompany(telcos, companies, services, count + 1, result); } else { // telco existed, get telco DistributedByTelcoAndCompany disres = result.Find(x => x.telco.id == service.telcoID); ConnTelco telco = disres.telco; // check company existed ??? ConnCompany com = disres.companies.data.Find(x => x.id == service.companyID); if (com == null) { // add company ConnCompany company = companies.data.Find(x => x.id == service.companyID); result.Where(S => S.telco.id == service.telcoID).Select(S => { S.companies.data.Add(company); return S; }).ToList(); } // check service existed ??? ConnConfig ser = disres.services.data.Find(x => x.id == service.id); if (ser == null) { // add company result.Where(S => S.telco.id == service.telcoID).Select(S => { S.services.data.Add(service); return S; }).ToList(); } return distributedByTelcoAndCompany(telcos, companies, services, count + 1, result); } } } } protected IActionResult ToExcelStock(List listdate, Dictionary> distributionData) { try { using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2013; IWorkbook workbook = application.Workbooks.Create(1); IWorksheet worksheet = workbook.Worksheets[0]; List rowAttributedList = new List(); int rowCount = 1; // for header worksheet.Range["A1"].Text = "NO"; worksheet.Range["B1"].Text = "NGÀNH"; worksheet.Range["C1"].Text = "CHỈ SỐ"; for (int d = 0; d < listdate.Count; d++) { worksheet.Range[UtilsController.GetExcelColumnName(4 + d * 2) + rowCount.ToString()].Text = listdate[d].ToString("dd/MM"); worksheet.Range[UtilsController.GetExcelColumnName(5 + d * 2) + rowCount.ToString()].Text = "%"; } rowCount++; List listTongGiaTri = new List(); List listTongKhoiLuong = new List(); List listTongThayDoi = new List(); List listTongMua = new List(); List listTongBan = new List(); for (int d = 0; d < listdate.Count; d++) { // tong sum double tongGiaTri = 0.0; double tongThayDoi = 0.0; double tongKhoiLuong = 0.0; double tongMua = 0.0; double tongBan = 0.0; for (int k = 0; k < distributionData.Count; k++) { Dictionary dataDateList = distributionData.ElementAt(k).Value; ConnCkData ckData = dataDateList.ElementAt(d).Value; if (ckData != null) { tongBan += Decimal.ToDouble(ckData.TnnBan ?? 0); tongMua += Decimal.ToDouble(ckData.TnnMua ?? 0); tongKhoiLuong += Decimal.ToDouble(ckData.KhoiLuong ?? 0); //tongThayDoi += Double.Parse(ckData.ThayDoi); tongGiaTri += Decimal.ToDouble(ckData.GiaTri ?? 0); } } listTongGiaTri.Add(tongGiaTri); listTongKhoiLuong.Add(tongKhoiLuong); listTongMua.Add(tongMua); listTongBan.Add(tongBan); //listTongThayDoi.Add(tongThayDoi); } for (int i = 0; i < distributionData.Count; i++) { ConnCkNganh nganh = distributionData.ElementAt(i).Key; worksheet.Range[UtilsController.GetExcelColumnName(1) + rowCount.ToString()].Text = (i + 1).ToString(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(1), rowCount.ToString(), true, null, Color.FromArgb(247, 220, 111), 1, 1, 1, 1, null)); worksheet.Range[UtilsController.GetExcelColumnName(2) + rowCount.ToString()].Text = nganh.Name.ToString(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(2), rowCount.ToString(), true, null, Color.FromArgb(247, 220, 111), 1, 1, 1, 1, null)); Dictionary dataCkList = distributionData.ElementAt(i).Value; // chi so tang giam worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = "Tăng / Giảm"; rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(3), rowCount.ToString(), true, null, Color.FromArgb(247, 220, 111), 1, 1, 1, 1, null)); for (int j = 0; j < dataCkList.Count; j++) { ConnCkData dataCk = dataCkList.ElementAt(j).Value; rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(j * 2 + 4), rowCount.ToString(), true, null, Color.FromArgb(247, 220, 111), 1, 1, 1, 1, null)); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(j * 2 + 5), rowCount.ToString(), true, null, Color.FromArgb(247, 220, 111), 1, 1, 1, 1, null)); if (dataCk != null) { worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 4) + rowCount.ToString()].Text = dataCk.TangGiam.ToString(); } } rowCount++; // chi so tang giam worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = "Thay đổi"; for (int j = 0; j < dataCkList.Count; j++) { ConnCkData dataCk = dataCkList.ElementAt(j).Value; if (dataCk != null) { worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 4) + rowCount.ToString()].Text = dataCk.ThayDoi.ToString(); } } rowCount++; // chi so tang giam worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = "Khối lượng"; for (int j = 0; j < dataCkList.Count; j++) { ConnCkData dataCk = dataCkList.ElementAt(j).Value; if (dataCk != null) { double percent = Math.Round(Decimal.ToDouble(dataCk.KhoiLuong ?? 0) * 100 / listTongKhoiLuong[j], 2); worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 4) + rowCount.ToString()].Text = dataCk.KhoiLuong.ToString(); worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 5) + rowCount.ToString()].Text = percent.ToString() + "%"; } } rowCount++; // chi so tang giam worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = "Giá trị"; for (int j = 0; j < dataCkList.Count; j++) { ConnCkData dataCk = dataCkList.ElementAt(j).Value; if (dataCk != null) { worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 4) + rowCount.ToString()].Text = dataCk.GiaTri.ToString(); double percent = Math.Round(Decimal.ToDouble(dataCk.GiaTri ?? 0) * 100 / listTongGiaTri[j], 2); worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 4) + rowCount.ToString()].Text = dataCk.GiaTri.ToString(); worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 5) + rowCount.ToString()].Text = percent.ToString() + "%"; } } rowCount++; // chi so tang giam worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = "Mua"; for (int j = 0; j < dataCkList.Count; j++) { ConnCkData dataCk = dataCkList.ElementAt(j).Value; if (dataCk != null) { double percent = Math.Round(Decimal.ToDouble(dataCk.TnnMua ?? 0) * 100 / listTongMua[j], 2); worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 4) + rowCount.ToString()].Text = dataCk.TnnMua.ToString(); worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 5) + rowCount.ToString()].Text = percent.ToString() + "%"; } } rowCount++; // chi so tang giam worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = "Bán"; for (int j = 0; j < dataCkList.Count; j++) { ConnCkData dataCk = dataCkList.ElementAt(j).Value; if (dataCk != null) { double percent = Math.Round(Decimal.ToDouble(dataCk.TnnBan ?? 0) * 100 / listTongBan[j], 2); worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 4) + rowCount.ToString()].Text = dataCk.TnnBan.ToString(); worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 5) + rowCount.ToString()].Text = percent.ToString() + "%"; } } rowCount++; } // TONG worksheet.Range[UtilsController.GetExcelColumnName(1) + rowCount.ToString()].Text = (distributionData.Count).ToString(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(1), rowCount.ToString(), true, null, Color.FromArgb(247, 220, 111), 1, 1, 1, 1, null)); worksheet.Range[UtilsController.GetExcelColumnName(2) + rowCount.ToString()].Text = "TỔNG".ToString(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(2), rowCount.ToString(), true, null, Color.FromArgb(247, 220, 111), 1, 1, 1, 1, null)); // chi so tang giam worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = "Khối lượng"; rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(3), rowCount.ToString(), true, null, Color.FromArgb(247, 220, 111), 1, 1, 1, 1, null)); for (int j = 0; j < listTongKhoiLuong.Count; j++) { worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 4) + rowCount.ToString()].Text = listTongKhoiLuong[j].ToString(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(j * 2 + 4), rowCount.ToString(), true, null, Color.FromArgb(247, 220, 111), 1, 1, 1, 1, null)); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(j * 2 + 5), rowCount.ToString(), true, null, Color.FromArgb(247, 220, 111), 1, 1, 1, 1, null)); } rowCount++; // chi so tang giam worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = "Giá trị"; for (int j = 0; j < listTongGiaTri.Count; j++) { worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 4) + rowCount.ToString()].Text = listTongGiaTri[j].ToString(); } rowCount++; // chi so tang giam worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = "Mua"; for (int j = 0; j < listTongMua.Count; j++) { worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 4) + rowCount.ToString()].Text = listTongMua[j].ToString(); } rowCount++; // chi so tang giam worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = "Bán"; for (int j = 0; j < listTongBan.Count; j++) { worksheet.Range[UtilsController.GetExcelColumnName(j * 2 + 4) + rowCount.ToString()].Text = listTongBan[j].ToString(); } rowCount++; //Formatting //Global styles should be used when the same style needs to be applied to more than one cell. This usage of a global style reduces memory usage. //Add custom colors to the palette workbook.SetPaletteColor(8, Color.FromArgb(255, 174, 33)); //Defining header style IStyle headerStyle = workbook.Styles.Add("HeaderStyle" + 0.ToString()); headerStyle.BeginUpdate(); headerStyle.Color = Color.FromArgb(82, 190, 128); headerStyle.Font.Bold = true; headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; headerStyle.EndUpdate(); //Apply Header style worksheet.Rows[0].CellStyle = headerStyle; for (int g = 0; g < rowAttributedList.Count; g++) { worksheet.Range[rowAttributedList[g].character + rowAttributedList[g].number].CellStyle = setStyleRowExcel(workbook, 0, rowAttributedList[g]); } //Auto-fit the columns worksheet.UsedRange.AutofitColumns(); //Saving the workbook as stream //FileStream stream = new FileStream("GlobalStyles.xlsx", FileMode.Create, FileAccess.ReadWrite); //workbook.SaveAs(stream); //stream.Dispose(); using (var stream = new MemoryStream()) { workbook.SaveAs(stream); var content = stream.ToArray(); return File( content, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "stock_" + DateTime.Now.ToString("dd_MM") + ".xlsx"); } } } catch (Exception ex) { log.Error("Exception: ", ex); return Redirect("/Home"); } } protected IActionResult Excel(DateTime endDateTime, List dataList, List dataAll) { try { using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2013; IWorkbook workbook = application.Workbooks.Create(dataList.Count); for (int i = 0; i < dataList.Count; i++) { int rowCount = 1; List distributedByTelcoAndCompanyT = new List(); List disByTelcoAndCompany = distributedByTelcoAndCompany(dataList[i].telcos, dataList[i].companies, dataList[i].services, 0, distributedByTelcoAndCompanyT); IWorksheet worksheet = workbook.Worksheets[i]; List rowAttributedList = new List(); // for header worksheet.Range["A1"].Text = "NO"; worksheet.Range["B1"].Text = "VAS CODE"; worksheet.Range["C1"].Text = "Acc now"; worksheet.Range["D1"].Text = "Acc last month"; worksheet.Range["E1"].Text = "+/-"; worksheet.Range["F1"].Text = "%"; rowCount++; disByTelcoAndCompany.Sort(delegate (DistributedByTelcoAndCompany x, DistributedByTelcoAndCompany y) { if (x.telco.sequence == null && y.telco.sequence == null) return 0; else if (x.telco.sequence == null) return -1; else if (y.telco.sequence == null) return 1; else return int.Parse(x.telco.sequence).CompareTo(int.Parse(y.telco.sequence)); }); // FOR MONEY TABLES for (int k = 0; k < disByTelcoAndCompany.Count; k++) { if (k != 0) { rowCount++; } // show telco name worksheet.Range[UtilsController.GetExcelColumnName(1) + rowCount.ToString()].Text = disByTelcoAndCompany[k].telco.telcoName; worksheet.Range[UtilsController.GetExcelColumnName(1) + rowCount.ToString() + ":" + UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Merge(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(1), rowCount.ToString(), true, null, Color.FromArgb(247, 220, 111), 1, 1, 1, 1, 1)); rowCount++; disByTelcoAndCompany[k].companies.data.Sort(delegate (ConnCompany x, ConnCompany y) { if (x.sequence == null && y.sequence == null) return 0; else if (x.sequence == null) return -1; else if (y.sequence == null) return 1; else return int.Parse(x.sequence).CompareTo(int.Parse(y.sequence)); }); for (int c = 0; c < disByTelcoAndCompany[k].companies.data.Count; c++) { // show company name ConnCompany company = disByTelcoAndCompany[k].companies.data[c]; worksheet.Range[UtilsController.GetExcelColumnName(1) + rowCount.ToString()].Text = company.name; worksheet.Range[UtilsController.GetExcelColumnName(1) + rowCount.ToString() + ":" + UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Merge(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(1), rowCount.ToString(), true, null, Color.FromArgb(236, 112, 99), 1, 1, 1, 1, 1)); rowCount++; // show all services which is belong this this company List serviceBelong = disByTelcoAndCompany[k].services.data.FindAll(x => x.companyID == company.id); serviceBelong.Sort(delegate (ConnConfig x, ConnConfig y) { if (x.sequence == null && y.sequence == null) return 0; else if (x.sequence == null) return -1; else if (y.sequence == null) return 1; else return int.Parse(x.sequence).CompareTo(int.Parse(y.sequence)); }); for (int s = 0; s < serviceBelong.Count; s++) { ConnConfig service = serviceBelong[s]; //worksheet.Range[GetExcelColumnName(1) + rowCount.ToString()].Text = service.serviceName; //serviceRowList.Add(rowCount); //rowCount++; // get the data of this service List dataService = dataList[i].distributedByTimes.FindAll(x => x.service.id == service.id); DateTime now = DateTime.Now.AddDays(-1); // get date now DistributedByTime dataNow = dataService.Find(x => x.time.ToString("dd/MM/yyyy") == endDateTime.ToString("dd/MM/yyyy")); if (dataService.Count > 0) { // check if (dataService[0].time.Month == now.Month) { for (int d = 1; d < dataList[i].time.Count; d++) { worksheet.Range[UtilsController.GetExcelColumnName(d + 6) + "1"].Text = dataList[i].time[d].ToString("dd/MM"); } } else { for (int d = 2; d < dataList[i].time.Count; d++) { worksheet.Range[UtilsController.GetExcelColumnName(d + 5) + "1"].Text = dataList[i].time[d].ToString("dd/MM"); } } } // get all data in the last month // get month now DateTime startLastMonth = getStartByMonthToDate(dataList[i].time[dataList[i].time.Count - 1], -1); DateTime stopLastMonth = getEndByMonthToDate(dataList[i].time[dataList[i].time.Count - 1], -1); worksheet.Name = getStartByMonthToDate(dataList[i].time[dataList[i].time.Count - 1], 0).ToString("MM yyyy"); List dataLastMonth = dataAll.FindAll(x => DateTime.Compare(startLastMonth, x.time) <= 0 && DateTime.Compare(x.time, stopLastMonth) <= 0 && x.service.id == service.id); //DateTime startNow = getStartByMonthToDate(0); //DateTime stopNow = getEndByDayToDate(); //List dataThisMonth = dataService.FindAll(x => // DateTime.Compare(startNow, x.time) <= 0 && DateTime.Compare(x.time, stopNow) <= 0); // remove the first element List dataThisMonth = new List(); for (int h = dataNow != null && dataNow.connDatas != null ? 1 : 0; h < dataService.Count; h++) { dataThisMonth.Add(dataService[h]); } double newSub = 0, newSubBefore = 0; double deactive = 0, deactiveBefore = 0; double revenue = 0, revenueBefore = 0; double expense = 0, expenseBefore = 0; double totalSub = 0, totalSubBefore = 0; double benefit = 0, benefitBefore = 0; double benefitP = 0, revenueP = 0, expenseP = 0; double benefitD = 0, revenueD = 0, expenseD = 0; int dayMonthNow = dataThisMonth.Count; //for (int y = 0; y < dataLastMonth.Count; y++) //{ // if (dataLastMonth[y].connDatas.data.Count > 0) // { // for (int q = 0; q < dayMonthNow; q++) // { // newSubBefore += int.Parse(dataLastMonth[q].connDatas.data[0].registerNew); // deactiveBefore += int.Parse(dataLastMonth[q].connDatas.data[0].deactive); // revenueBefore += int.Parse(dataLastMonth[q].connDatas.data[0].revenue); // expenseBefore += int.Parse(dataLastMonth[q].connDatas.data[0].expense); // totalSubBefore += int.Parse(dataLastMonth[q].connDatas.data[0].totalSub); // } // } //} if (dataLastMonth.Count > 0 && dayMonthNow <= dataLastMonth.Count - 1) { for (int q = 0; q < dayMonthNow; q++) { if (dataLastMonth[dataLastMonth.Count - 1 - q].connDatas.data.Count > 0) { if (dataLastMonth[dataLastMonth.Count - 1 - q].connDatas.data[0].registerNew != null) { newSubBefore += double.Parse(dataLastMonth[dataLastMonth.Count - 1 - q].connDatas.data[0].registerNew); } if (dataLastMonth[dataLastMonth.Count - 1 - q].connDatas.data[0].deactive != null) { deactiveBefore += double.Parse(dataLastMonth[dataLastMonth.Count - 1 - q].connDatas.data[0].deactive); } if (dataLastMonth[dataLastMonth.Count - 1 - q].connDatas.data[0].revenue != null) { revenueBefore += double.Parse(dataLastMonth[dataLastMonth.Count - 1 - q].connDatas.data[0].revenue); } if (dataLastMonth[dataLastMonth.Count - 1 - q].connDatas.data[0].expense != null) { expenseBefore += double.Parse(dataLastMonth[dataLastMonth.Count - 1 - q].connDatas.data[0].expense); } if (dataLastMonth[dataLastMonth.Count - 1 - q].connDatas.data[0].totalSub != null) { totalSubBefore += double.Parse(dataLastMonth[dataLastMonth.Count - 1 - q].connDatas.data[0].totalSub); } } } } benefitBefore = revenueBefore - Math.Abs(expenseBefore); for (int y = 0; y < dataThisMonth.Count; y++) { if (dataThisMonth[y].connDatas.data.Count > 0) { if (dataThisMonth[y].connDatas.data[0].registerNew != null) { newSub += double.Parse(dataThisMonth[y].connDatas.data[0].registerNew); } if (dataThisMonth[y].connDatas.data[0].deactive != null) { deactive += double.Parse(dataThisMonth[y].connDatas.data[0].deactive); } if (dataThisMonth[y].connDatas.data[0].revenue != null) { revenue += double.Parse(dataThisMonth[y].connDatas.data[0].revenue); } if (dataThisMonth[y].connDatas.data[0].expense != null) { expense += double.Parse(dataThisMonth[y].connDatas.data[0].expense); } if (dataThisMonth[y].connDatas.data[0].totalSub != null) { totalSub += double.Parse(dataThisMonth[y].connDatas.data[0].totalSub); } } } benefit = revenue - Math.Abs(expense); revenueD = revenue - revenueBefore; expenseD = Math.Abs(expense) - Math.Abs(expenseBefore); benefitD = benefit - benefitBefore; revenueP = revenueBefore != 0 ? Math.Round(revenueD * 100 / revenueBefore, 2) : 100; expenseP = expenseBefore != 0 ? Math.Round(expenseD * 100 / expenseBefore, 2) : 100; benefitP = benefitBefore != 0 ? Math.Round(benefitD * 100 / benefitBefore, 2) : 100; // show worksheet.Range[UtilsController.GetExcelColumnName(2) + rowCount.ToString()].Text = service.serviceName + " NET"; RowAttributed r3 = new RowAttributed(UtilsController.GetExcelColumnName(2), rowCount.ToString(), true, Color.FromArgb(255, 0, 0), Color.FromArgb(215, 189, 226), 1, 1, 1, 1, null); rowAttributedList = rowHandler(rowAttributedList, r3); worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = string.Format("{0:#,0}", benefit).ToString(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(3), rowCount.ToString(), true, Color.FromArgb(255, 0, 0), null, 1, 1, 1, 1, null)); worksheet.Range[UtilsController.GetExcelColumnName(4) + rowCount.ToString()].Text = string.Format("{0:#,0}", benefitBefore).ToString(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(4), rowCount.ToString(), true, Color.FromArgb(255, 0, 0), null, 1, 1, 1, 1, null)); if (benefitD < 0) { worksheet.Range[UtilsController.GetExcelColumnName(5) + rowCount.ToString()].Text = "(" + string.Format("{0:#,0}", Math.Abs(benefitD)).ToString() + ")"; rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(5), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null)); worksheet.Range[UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Text = string.Format("{0:#,0}", benefitP).ToString() + "%"; rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(6), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null)); } else { worksheet.Range[UtilsController.GetExcelColumnName(5) + rowCount.ToString()].Text = string.Format("{0:#,0}", benefitD).ToString(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(5), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null)); worksheet.Range[UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Text = string.Format("{0:#,0}", benefitP).ToString() + "%"; rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(6), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null)); } if (dataNow != null && dataNow.connDatas != null && dataNow.connDatas.data.Count > 0) { double benefitN = 0; if (dataNow.connDatas.data[0].revenue != null && dataNow.connDatas.data[0].expense != null) { benefitN = double.Parse(dataNow.connDatas.data[0].revenue) - Math.Abs(double.Parse(dataNow.connDatas.data[0].expense)); } worksheet.Range[UtilsController.GetExcelColumnName(7) + rowCount.ToString()].Text = string.Format("{0:#,0}", benefitN).ToString(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(7), rowCount.ToString(), true, Color.FromArgb(255, 0, 0), null, 1, 1, 1, 1, null)); } for (int l = 0; l < dataThisMonth.Count; l++) { if (dataThisMonth[l].connDatas.data.Count > 0) { double benefitT = 0; if (dataThisMonth[l].connDatas.data[0].revenue != null && dataThisMonth[l].connDatas.data[0].expense != null) { benefitT = double.Parse(dataThisMonth[l].connDatas.data[0].revenue) - Math.Abs(double.Parse(dataThisMonth[l].connDatas.data[0].expense)); } worksheet.Range[UtilsController.GetExcelColumnName(dataNow != null ? 7 + l + 1 : 7 + l) + rowCount.ToString()].Text = string.Format("{0:#,0}", benefitT).ToString(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(dataNow != null ? 7 + l + 1 : 7 + l), rowCount.ToString(), true, Color.FromArgb(255, 0, 0), null, 1, 1, 1, 1, null)); } } rowCount++; if (service.state == UtilsController.Constant.ALL_REVEUNUE) { worksheet.Range[UtilsController.GetExcelColumnName(2) + rowCount.ToString()].Text = service.serviceName; rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(2), rowCount.ToString(), true, null, Color.FromArgb(215, 189, 226), 1, 1, 1, 1, null)); worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = string.Format("{0:#,0}", revenue).ToString(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(3), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null)); worksheet.Range[UtilsController.GetExcelColumnName(4) + rowCount.ToString()].Text = string.Format("{0:#,0}", revenueBefore).ToString(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(4), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null)); if (revenueD < 0) { worksheet.Range[UtilsController.GetExcelColumnName(5) + rowCount.ToString()].Text = "(" + string.Format("{0:#,0}", Math.Abs(revenueD)).ToString() + ")"; rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(5), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null)); worksheet.Range[UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Text = string.Format("{0:#,0}", revenueP).ToString() + "%"; rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(6), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null)); } else { worksheet.Range[UtilsController.GetExcelColumnName(5) + rowCount.ToString()].Text = string.Format("{0:#,0}", revenueD).ToString(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(5), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null)); worksheet.Range[UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Text = string.Format("{0:#,0}", revenueP).ToString() + "%"; rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(6), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null)); } if (dataNow != null && dataNow.connDatas != null && dataNow.connDatas.data.Count > 0) { worksheet.Range[UtilsController.GetExcelColumnName(7) + rowCount.ToString()].Text = string.Format("{0:#,0}", double.Parse(dataNow.connDatas.data[0].revenue)).ToString(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(7), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null)); } for (int l = 0; l < dataThisMonth.Count; l++) { if (dataThisMonth[l].connDatas.data.Count > 0) { worksheet.Range[UtilsController.GetExcelColumnName(dataNow != null ? 7 + l + 1 : 7 + l) + rowCount.ToString()].Text = string.Format("{0:#,0}", double.Parse(dataThisMonth[l].connDatas.data[0].revenue)).ToString(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(dataNow != null ? 7 + l + 1 : 7 + l), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null)); } } rowCount++; worksheet.Range[UtilsController.GetExcelColumnName(2) + rowCount.ToString()].Text = service.serviceName + " COST"; rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(2), rowCount.ToString(), true, null, Color.FromArgb(215, 189, 226), 1, 1, 1, 1, null)); worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = string.Format("{0:#,0}", Math.Abs(expense)).ToString(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(3), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null)); worksheet.Range[UtilsController.GetExcelColumnName(4) + rowCount.ToString()].Text = string.Format("{0:#,0}", Math.Abs(expenseBefore)).ToString(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(4), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null)); if (expenseD < 0) { worksheet.Range[UtilsController.GetExcelColumnName(5) + rowCount.ToString()].Text = "(" + string.Format("{0:#,0}", Math.Abs(expenseD)).ToString() + ")"; rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(5), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null)); worksheet.Range[UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Text = string.Format("{0:#,0}", expenseP).ToString() + "%"; rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(6), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null)); } else { worksheet.Range[UtilsController.GetExcelColumnName(5) + rowCount.ToString()].Text = string.Format("{0:#,0}", Math.Abs(expenseD)).ToString(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(5), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null)); worksheet.Range[UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Text = string.Format("{0:#,0}", expenseP).ToString() + "%"; rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(6), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null)); } if (dataNow != null && dataNow.connDatas != null && dataNow.connDatas.data.Count > 0) { String expenseCheck1 = dataNow.connDatas.data[0].expense != null ? dataNow.connDatas.data[0].expense : "0"; worksheet.Range[UtilsController.GetExcelColumnName(7) + rowCount.ToString()].Text = string.Format("{0:#,0}", Math.Abs(double.Parse(expenseCheck1))).ToString(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(7), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null)); } for (int l = 0; l < dataThisMonth.Count; l++) { if (dataThisMonth[l].connDatas.data.Count > 0) { String expenseCheck = dataThisMonth[l].connDatas.data[0].expense != null ? dataThisMonth[l].connDatas.data[0].expense : "0"; worksheet.Range[UtilsController.GetExcelColumnName(dataNow != null ? 7 + l + 1 : 7 + l) + rowCount.ToString()].Text = string.Format("{0:#,0}", Math.Abs(double.Parse(expenseCheck))).ToString(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(dataNow != null ? 7 + l + 1 : 7 + l), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null)); } } rowCount++; } } worksheet.Range[UtilsController.GetExcelColumnName(2) + rowCount.ToString()].Text = "TOTAL"; rowCount++; } } // FOR SUBSCRIBERS TABLES rowCount += 10; worksheet.Range[UtilsController.GetExcelColumnName(1) + rowCount.ToString()].Text = "TOTAL SUBSCRIBERS COMPARE LAST MONTH"; worksheet.Range[UtilsController.GetExcelColumnName(1) + rowCount.ToString() + ":" + UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Merge(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(1), rowCount.ToString(), true, Color.FromArgb(0, 0, 0), Color.FromArgb(255, 174, 33), 1, 1, 1, 1, 1)); rowCount++; // for header worksheet.Range[UtilsController.GetExcelColumnName(1) + rowCount.ToString()].Text = "NO"; worksheet.Range[UtilsController.GetExcelColumnName(2) + rowCount.ToString()].Text = "VAS CODE"; worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = "Acc now"; worksheet.Range[UtilsController.GetExcelColumnName(4) + rowCount.ToString()].Text = "Acc last month"; worksheet.Range[UtilsController.GetExcelColumnName(5) + rowCount.ToString()].Text = "+/-"; worksheet.Range[UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Text = "%"; //for (int d = 1; d < dataList[i].time.Count; d++) //{ // worksheet.Range[GetExcelColumnName(d + 6) + "1"].Text = dataList[i].time[d].ToString("dd/MM"); //} //rowCount++; for (int k = 0; k < disByTelcoAndCompany.Count; k++) { if (k != 0) { rowCount++; } // show telco name worksheet.Range[UtilsController.GetExcelColumnName(1) + rowCount.ToString()].Text = disByTelcoAndCompany[k].telco.telcoName; worksheet.Range[UtilsController.GetExcelColumnName(1) + rowCount.ToString() + ":" + UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Merge(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(1), rowCount.ToString(), true, null, Color.FromArgb(247, 220, 111), 1, 1, 1, 1, 1)); rowCount++; disByTelcoAndCompany[k].companies.data.Sort(delegate (ConnCompany x, ConnCompany y) { if (x.sequence == null && y.sequence == null) return 0; else if (x.sequence == null) return -1; else if (y.sequence == null) return 1; else return int.Parse(x.sequence).CompareTo(int.Parse(y.sequence)); }); for (int c = 0; c < disByTelcoAndCompany[k].companies.data.Count; c++) { // show company name ConnCompany company = disByTelcoAndCompany[k].companies.data[c]; worksheet.Range[UtilsController.GetExcelColumnName(1) + rowCount.ToString()].Text = company.name; worksheet.Range[UtilsController.GetExcelColumnName(1) + rowCount.ToString() + ":" + UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Merge(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(1), rowCount.ToString(), true, null, Color.FromArgb(236, 112, 99), 1, 1, 1, 1, 1)); rowCount++; // show all services which is belong this this company List serviceBelong = disByTelcoAndCompany[k].services.data.FindAll(x => x.companyID == company.id); serviceBelong.Sort(delegate (ConnConfig x, ConnConfig y) { if (x.sequence == null && y.sequence == null) return 0; else if (x.sequence == null) return -1; else if (y.sequence == null) return 1; else return int.Parse(x.sequence).CompareTo(int.Parse(y.sequence)); }); for (int s = 0; s < serviceBelong.Count; s++) { ConnConfig service = serviceBelong[s]; //worksheet.Range[GetExcelColumnName(1) + rowCount.ToString()].Text = service.serviceName; //serviceRowList.Add(rowCount); //rowCount++; // get the data of this service List dataService = dataList[i].distributedByTimes.FindAll(x => x.service.id == service.id); DateTime now = DateTime.Now.AddDays(-1); // get date now DistributedByTime dataNow = dataService.Find(x => x.time.ToString("dd/MM/yyyy") == now.ToString("dd/MM/yyyy")); // get all data in the last month // get month now DateTime startLastMonth = getStartByMonthToDate(dataList[i].time[dataList[i].time.Count - 1], -1); DateTime stopLastMonth = getEndByMonthToDate(dataList[i].time[dataList[i].time.Count - 1], -1); worksheet.Name = getStartByMonthToDate(dataList[i].time[dataList[i].time.Count - 1], 0).ToString("MM yyyy"); List dataLastMonth = dataAll.FindAll(x => DateTime.Compare(startLastMonth, x.time) <= 0 && DateTime.Compare(x.time, stopLastMonth) <= 0 && x.service.id == service.id); //DateTime startNow = getStartByMonthToDate(0); //DateTime stopNow = getEndByDayToDate(); //List dataThisMonth = dataService.FindAll(x => // DateTime.Compare(startNow, x.time) <= 0 && DateTime.Compare(x.time, stopNow) <= 0); //List dataThisMonth = dataService; List dataThisMonth = new List(); for (int h = dataNow != null && dataNow.connDatas != null ? 1 : 0; h < dataService.Count; h++) { dataThisMonth.Add(dataService[h]); } double totalSub = 0, totalSubBefore = 0; double totalSubD = 0, totalSubP = 0, totalSubN = 0; if (dataLastMonth.Count > 0 && dataLastMonth[0].connDatas.data.Count > 0) { totalSubBefore = int.Parse(dataLastMonth[0].connDatas.data[0].totalSub); } if (dataThisMonth.Count > 0 && dataThisMonth[0].connDatas.data.Count > 0) { totalSub = int.Parse(dataThisMonth[0].connDatas.data[0].totalSub); } totalSubD = totalSub - totalSubBefore; totalSubP = totalSubBefore != 0 ? Math.Round(totalSubD * 100 / totalSubBefore, 2) : 100; // show worksheet.Range[UtilsController.GetExcelColumnName(2) + rowCount.ToString()].Text = service.serviceName; RowAttributed r3 = new RowAttributed(UtilsController.GetExcelColumnName(2), rowCount.ToString(), true, Color.FromArgb(255, 0, 0), Color.FromArgb(215, 189, 226), 1, 1, 1, 1, null); rowAttributedList = rowHandler(rowAttributedList, r3); worksheet.Range[UtilsController.GetExcelColumnName(3) + rowCount.ToString()].Text = string.Format("{0:#,0}", totalSub).ToString(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(3), rowCount.ToString(), true, Color.FromArgb(255, 0, 0), null, 1, 1, 1, 1, null)); worksheet.Range[UtilsController.GetExcelColumnName(4) + rowCount.ToString()].Text = string.Format("{0:#,0}", totalSubBefore).ToString(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(4), rowCount.ToString(), true, Color.FromArgb(255, 0, 0), null, 1, 1, 1, 1, null)); if (totalSubD < 0) { worksheet.Range[UtilsController.GetExcelColumnName(5) + rowCount.ToString()].Text = "(" + string.Format("{0:#,0}", Math.Abs(totalSubD)).ToString() + ")"; rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(5), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null)); worksheet.Range[UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Text = string.Format("{0:#,0}", totalSubP).ToString() + "%"; rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(6), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null)); } else { worksheet.Range[UtilsController.GetExcelColumnName(5) + rowCount.ToString()].Text = string.Format("{0:#,0}", totalSubD).ToString(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(5), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null)); worksheet.Range[UtilsController.GetExcelColumnName(6) + rowCount.ToString()].Text = string.Format("{0:#,0}", totalSubP).ToString() + "%"; rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(6), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null)); } if (dataNow != null && dataNow.connDatas.data.Count > 0) { worksheet.Range[UtilsController.GetExcelColumnName(7) + rowCount.ToString()].Text = string.Format("{0:#,0}", double.Parse(dataNow.connDatas.data[0].totalSub)).ToString(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(7), rowCount.ToString(), true, null, null, 1, 1, 1, 1, null)); } for (int l = 0; l < dataThisMonth.Count; l++) { if (dataThisMonth[l].connDatas.data.Count > 0) { worksheet.Range[UtilsController.GetExcelColumnName(dataNow != null ? 7 + l + 1 : 7 + l) + rowCount.ToString()].Text = string.Format("{0:#,0}", double.Parse(dataThisMonth[l].connDatas.data[0].totalSub)).ToString(); rowAttributedList = rowHandler(rowAttributedList, new RowAttributed(UtilsController.GetExcelColumnName(dataNow != null ? 7 + l + 1 : 7 + l), rowCount.ToString(), true, Color.FromArgb(255, 0, 0), null, 1, 1, 1, 1, null)); } } rowCount++; } worksheet.Range[UtilsController.GetExcelColumnName(2) + rowCount.ToString()].Text = "TOTAL"; rowCount++; } } //Formatting //Global styles should be used when the same style needs to be applied to more than one cell. This usage of a global style reduces memory usage. //Add custom colors to the palette workbook.SetPaletteColor(8, Color.FromArgb(255, 174, 33)); //Defining header style IStyle headerStyle = workbook.Styles.Add("HeaderStyle" + i.ToString()); headerStyle.BeginUpdate(); headerStyle.Color = Color.FromArgb(82, 190, 128); headerStyle.Font.Bold = true; headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; headerStyle.EndUpdate(); //Apply Header style worksheet.Rows[0].CellStyle = headerStyle; for (int g = 0; g < rowAttributedList.Count; g++) { worksheet.Range[rowAttributedList[g].character + rowAttributedList[g].number].CellStyle = setStyleRowExcel(workbook, i, rowAttributedList[g]); } //Auto-fit the columns worksheet.UsedRange.AutofitColumns(); } //Saving the workbook as stream //FileStream stream = new FileStream("GlobalStyles.xlsx", FileMode.Create, FileAccess.ReadWrite); //workbook.SaveAs(stream); //stream.Dispose(); using (var stream = new MemoryStream()) { workbook.SaveAs(stream); var content = stream.ToArray(); return File( content, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "report_" + DateTime.Now.ToString("dd_MM") + ".xlsx"); } } } catch (Exception ex) { log.Error("Exception: ", ex); return Redirect("/Home"); } } private IStyle setStyleRowExcel(IWorkbook workbook, int worksheet, RowAttributed row) { IStyle bodyStyle = workbook.Styles.Add(row.character + row.number + "Style" + worksheet.ToString()); bodyStyle.BeginUpdate(); if (row.color != null) { bodyStyle.Font.RGBColor = (Color)row.color; } if (row.bold != null) { bodyStyle.Font.Bold = row.bold; } if (row.background != null) { bodyStyle.Color = (Color)row.background; } if (row.thinLeft != null) { bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin; } if (row.thinRight != null) { bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin; } if (row.thinTop != null) { bodyStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin; } if (row.thinBottom != null) { bodyStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin; } if (row.center != null) { bodyStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter; } else { bodyStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft; } bodyStyle.EndUpdate(); return bodyStyle; } private List rowHandler(List rowList, RowAttributed row) { RowAttributed getRow = rowList.Find(x => x.character == row.character && x.number == row.number); if (getRow != null) { rowList.Where(S => S.character == row.character && S.number == row.number).Select(S => { S.color = row.color != null ? row.color : S.color; S.bold = row.bold != null ? row.bold : S.bold; S.background = row.background != null ? row.background : S.background; S.thinLeft = row.thinLeft != null ? row.thinLeft : S.thinLeft; S.thinRight = row.thinRight != null ? row.thinRight : S.thinRight; S.thinTop = row.thinTop != null ? row.thinTop : S.thinTop; S.thinBottom = row.thinBottom != null ? row.thinBottom : S.thinBottom; return S; }).ToList(); } else { rowList.Add(row); } return rowList; } protected void CreateAuthToken() { // create session authen // Create the random value we will use to secure the session. string authId = GenerateAuthId(); // Store the value in both our Session and a Cookie. HttpContext.Session.SetString("AuthorizationCookieId", authId); string sessionValue = HttpContext.Session.GetString("AuthorizationCookieId"); //CookieOptions option = new CookieOptions //{ // Expires = DateTime.Now.AddMinutes(1) //}; //Response.Cookies.Append("Key Name", "Value", option); CookieOptions options = new CookieOptions() { //Path = "/", //HttpOnly = true, //Secure = false, //SameSite = SameSiteMode.None Expires = DateTime.Now.AddMinutes(60) }; HttpContext.Response.Cookies.Append("AuthorizationCookie", authId, options); string cookieValue = HttpContext.Request.Cookies["AuthorizationCookie"]; } protected bool CheckAuthToken() { string cookieValue = HttpContext.Request.Cookies["AuthorizationCookie"]; string sessionValue = HttpContext.Session.GetString("AuthorizationCookieId"); if (cookieValue == null || sessionValue == null || cookieValue != sessionValue) { // Invalidate the session and log out the current user. return false; } else { return true; } } protected bool ClearCache() { HttpContext.Session.Clear(); foreach (var cookieKey in HttpContext.Request.Cookies.Keys) { HttpContext.Response.Cookies.Delete(cookieKey); } return true; } private string GenerateAuthId() { using (RandomNumberGenerator rng = new RNGCryptoServiceProvider()) { byte[] tokenData = new byte[32]; rng.GetBytes(tokenData); return Convert.ToBase64String(tokenData); } } } }