private void CalculateRententionRate(Excel.Worksheet sheet) { sheet.Cells[FunnelMonthlyReportParameters.RententionRateRow, 2].NumberFormat = "##,##0.00%"; sheet.Cells[FunnelMonthlyReportParameters.FTPPrice, 2] = string.Format("={0}{1}/{0}{2}", "B", FunnelMonthlyReportParameters.OnlineFTPRevenues, FunnelMonthlyReportParameters.ChargedFTPRow); for (int i = 14; i <= 25; i++) { string column = ExcelUtilies.ExcelColumnIndexToName(i); string columnBefore = ExcelUtilies.ExcelColumnIndexToName(i - 1); //Retention Rate sheet.Cells[FunnelMonthlyReportParameters.RententionRateRow, i] = string.Format("={0}{1}/({2}{1} + {2}{3})", column, FunnelMonthlyReportParameters.RenewalRow, columnBefore, FunnelMonthlyReportParameters.ChargedFTPRow); sheet.Cells[FunnelMonthlyReportParameters.RententionRateRow, i].NumberFormat = "##,##0.00%"; //FTP Price sheet.Cells[FunnelMonthlyReportParameters.FTPPrice, i] = string.Format("={0}{1}/{0}{2}", column, FunnelMonthlyReportParameters.OnlineFTPRevenues, FunnelMonthlyReportParameters.ChargedFTPRow); } }
private void FormatFinalReportSheet(Excel.Worksheet sheet) { //Insert a row to set the title ExcelUtilies.InsertRow(sheet, "A1"); sheet.Cells[1, 1] = sheet.Name; //insert two rows, total excel start with Row 3 ExcelUtilies.InsertRow(sheet, "A1"); this.SetBackgroundColor(sheet); this.CalculateRententionRate(sheet); string endColumn = ExcelUtilies.ExcelColumnIndexToName(ExcelUtilies.ExcelColumnNameToIndex(FunnelMonthlyReportParameters.MonthEnd)); string startColumn = this.GetCurrentMonthExcelColumn(); if (ExcelUtilies.ExcelColumnNameToIndex(startColumn) <= ExcelUtilies.ExcelColumnNameToIndex(endColumn)) { ExcelUtilies.DeleteColumns(sheet, startColumn, endColumn); } //for Europe remove the values of Spot Rate and KFR Rate if (sheet.Name == "Europe" || sheet.Name == "Mexico+US") { int endMonthColumn = ExcelUtilies.ExcelColumnNameToIndex(this.GetCurrentMonthExcelColumn()); for (int i = 2; i < endMonthColumn; i++) { //Spot Rate sheet.Cells[4, i] = string.Empty; //KFR Rate sheet.Cells[5, i] = string.Empty; } } this.FormatCells(sheet); this.FreezePanes(sheet); }
public bool MergeFiles(Dictionary <string, ReportContext> MergedFiles, string outputFile, out string mergedFile) { if (this.Logger == null) { this.Logger = new QVConfigLog(); } this.Logger.Message("Beigin to merge funnel reports."); excel = new Excel.Application(); Dictionary <string, string> centerNormalWeeklyReports = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "DetailNormalWeekly"); Dictionary <string, string> centerSpecialWeeklyReports = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "DetailSpecialWeekly"); Dictionary <string, string> centerDailyReports = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "DetailDaily"); Dictionary <string, string> TSNormalWeeklyReports = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "TSNormalWeekly"); Dictionary <string, string> TSDailyReports = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "TSDaily"); string SummaryMonthlyReport = MergedFiles["FunnelCNMini_Summary_Monthly"].OutputFullName; string SummaryDailyReport = MergedFiles["FunnelCNMini_Summary_Daily"].OutputFullName; string SummaryTargetReport = MergedFiles["FunnelCNMini_Summary_Target"].OutputFullName; string SummaryRevenueByPACReport = MergedFiles["FunnelCNMini_Summary_RevenueByPAC"].OutputFullName; string SummaryTargetB2BReport = MergedFiles["FunnelCNMini_Summary_TargetB2B"].OutputFullName; Excel.Workbook bookDest = null; Excel.Workbook tempWorkbook = null; try { tempWorkbook = excel.Workbooks.Add(Missing.Value); //create a new work sheet Excel.Worksheet tempWorkSheet = tempWorkbook.Worksheets[1] as Excel.Worksheet; this.Logger.Message("Copy to tempwork."); #region Summary //Summary summaryBook = excel.Workbooks.Open(SummaryDailyReport); Excel.Worksheet summaryMonthlySheet = summaryBook.Worksheets[1]; summaryMonthlySheet.Name = "Summary"; //copy DetailNormalWeekly report to dest //Column A - EG summaryMonthlySheet.Copy(Missing.Value, tempWorkSheet); Excel.Worksheet summaryDestSheet = tempWorkbook.Worksheets["Summary"]; int rowCount = this.GetSummaryRowCount(summaryDestSheet); //Append summary monthly this.AppendReport(tempWorkbook, summaryDestSheet, "Summary", "A2", "S25", "B" + rowCount, SummaryMonthlyReport, 1); //Append target report int targetStartRow = (rowCount + 2) * 2 + 4; //set label summaryDestSheet.Range["B" + (targetStartRow - 1)].Value = "B2C"; summaryDestSheet.Range["B" + (targetStartRow - 1)].Font.Bold = true; this.AppendReport(tempWorkbook, summaryDestSheet, "Summary", "A1", "K" + (FunnelCNMiniMergeParameters.TotalCity + 4), "B" + targetStartRow, SummaryTargetReport, 2); //Append target B2B int targetB2BStartRow = targetStartRow + FunnelCNMiniMergeParameters.TotalCity + 2 + 4; summaryDestSheet.Range["B" + (targetB2BStartRow - 1)].Value = "B2B"; summaryDestSheet.Range["B" + (targetB2BStartRow - 1)].Font.Bold = true; this.AppendReport(tempWorkbook, summaryDestSheet, "Summary", "A1", "K" + (FunnelCNMiniMergeParameters.TotalCity + 4), "B" + targetB2BStartRow, SummaryTargetB2BReport, 3); this.FormatSummary(summaryDestSheet); #endregion #region Summary Revenue by PAC summaryRevenueByPac = excel.Workbooks.Open(SummaryRevenueByPACReport); Excel.Worksheet revenueByPacSheet = summaryRevenueByPac.Worksheets[1]; revenueByPacSheet.Name = "Mini Center Rev By PAC"; revenueByPacSheet.Copy(Missing.Value, tempWorkSheet); Excel.Worksheet revenueByPacDestSheet = tempWorkbook.Worksheets["Mini Center Rev By PAC"]; this.FormatRevenueByPac(revenueByPacDestSheet); #endregion //Detail foreach (var item in centerNormalWeeklyReports.Keys) { string header = item + " Details"; centerNormalWeeklyBook = excel.Workbooks.Open(centerNormalWeeklyReports[item]); Excel.Worksheet centerNormalWeeklySheet = centerNormalWeeklyBook.Worksheets[1]; centerNormalWeeklySheet.Name = header; //copy DetailNormalWeekly report to dest //Column A - EG centerNormalWeeklySheet.Copy(Missing.Value, tempWorkSheet); Excel.Worksheet copyDestSheet = tempWorkbook.Worksheets[header]; ExcelUtilies.DeleteColumns(copyDestSheet, "A", "A"); //Append DetailSpecialWeekly int normalWeeklyColumnCount = this.GetColumnHasValueEndIndex(copyDestSheet, 2, 1); string specialWeeklyColumnStart = ExcelUtilies.ExcelColumnIndexToName(normalWeeklyColumnCount + 1); this.AppendReport(tempWorkbook, copyDestSheet, header, "B1", "AR" + this.GetWeeks(CurrentDate), specialWeeklyColumnStart + "1", centerSpecialWeeklyReports[item], 1); //if (item == "NJ2") // this.AppendReport(tempWorkbook, copyDestSheet, header, "B1", "AR" + this.GetWeeks(CurrentDate), "CJ1", centerSpecialWeeklyReports[item], 1); //else // this.AppendReport(tempWorkbook, copyDestSheet, header, "B1", "AR" + this.GetWeeks(CurrentDate), "DR1", centerSpecialWeeklyReports[item], 1); //Append Daily report this.AppendReport(tempWorkbook, copyDestSheet, header, "A1", "EG" + this.GetDays(CurrentDate), "A" + (this.GetWeeks(CurrentDate) + 3), centerDailyReports[item], 2); this.FormatDetail(copyDestSheet, item); } //Mini TS foreach (var item in TSNormalWeeklyReports.Keys) { string header = "Mini TS " + item; TSNormalWeeklyBook = excel.Workbooks.Open(TSNormalWeeklyReports[item]); Excel.Worksheet TSNormalWeeklySheet = TSNormalWeeklyBook.Worksheets[1]; TSNormalWeeklySheet.Name = header; //copy TSNormalWeekly report to dest //Column A - EG TSNormalWeeklySheet.Copy(Missing.Value, tempWorkSheet); Excel.Worksheet copyDestSheet = tempWorkbook.Worksheets[header]; ExcelUtilies.DeleteColumns(copyDestSheet, "A", "A"); //Append Daily report this.AppendReport(tempWorkbook, copyDestSheet, header, "A1", "EG" + this.GetDays(CurrentDate), "A" + (this.GetWeeks(CurrentDate) + 3), TSDailyReports[item], 2); this.FormatMiniTS(copyDestSheet, item); } this.Logger.Message("Create dest book."); bookDest = excel.Workbooks.Add(Missing.Value); //create a new work sheet //copy to dest from temp workbook //======================================================================================= //Why here copy all the sheets to another temp workbook? //I want to delete the sheeets like sheet1, sheet2, US1 etc. But it doesn't work if deleting // from tempWorkbook directly. So I copy to a new workbook which can fix the issue. // I don't know why but it does work. //======================================================================================= this.Logger.Message("create a new work sheet from dest book."); Excel.Worksheet sheetDest = bookDest.Worksheets[3] as Excel.Worksheet; foreach (var item in tempWorkbook.Worksheets) { Excel.Worksheet sheet = item as Excel.Worksheet; if (centerNormalWeeklyReports.Keys.Contains(sheet.Name.Replace(" Details", ""))) { sheet.Copy(Missing.Value, sheetDest); } if (TSNormalWeeklyReports.Keys.Contains(sheet.Name.Replace("Mini TS ", ""))) { sheet.Copy(Missing.Value, sheetDest); } if (sheet.Name == "Mini Center Rev By PAC") { sheet.Copy(Missing.Value, sheetDest); } if (sheet.Name == "Summary") { sheet.Copy(Missing.Value, sheetDest); } } //clear the temp workbook this.Logger.Message("clear the temp workbook"); FunnelReportHelper.SaveTempWorkbook(tempWorkbook); //delete sheet1 sheet2 sheet3 this.Logger.Message("delete sheet1 sheet2 sheet3"); bookDest.Sheets["sheet1"].Delete(); bookDest.Sheets["sheet2"].Delete(); bookDest.Sheets["sheet3"].Delete(); //save the dest this.Logger.Message("save the dest."); mergedFile = outputFile.Replace(".xls", "_" + DateTime.Now.ToString("yyyyMMdd") + ".xls"); if (File.Exists(mergedFile)) { File.Delete(mergedFile); } bookDest.SaveAs(mergedFile); bookDest.Close(); //zip the mergefile ZipHelper.ZipFile(mergedFile, mergedFile.Replace(".xls", ".zip"), 1); mergedFile = mergedFile.Replace(".xls", ".zip"); } catch (Exception ex) { this.Logger.Error(ex.Message + "\n" + ex.StackTrace); FunnelReportHelper.SaveTempWorkbook(tempWorkbook); FunnelReportHelper.SaveTempWorkbook(bookDest); throw; } finally { FunnelReportHelper.CloseWorkingWorkbook(centerNormalWeeklyBook); FunnelReportHelper.CloseWorkingWorkbook(TSNormalWeeklyBook); FunnelReportHelper.CloseWorkingWorkbook(summaryBook); FunnelReportHelper.CloseWorkingWorkbook(summaryRevenueByPac); } return(true); }