private void AppendReport(Excel.Workbook destWorkbook, Excel.Worksheet copyDestSheet, string sheetName, string sourceBeginCell, string sourceEndCell, string destBeginCell, string reportFile, int sequence) { if (File.Exists(reportFile)) { Excel.Workbook sourceBook = null; try { sourceBook = excel.Workbooks.Open(reportFile); Excel.Worksheet workingSheet = sourceBook.Worksheets[1]; workingSheet.Name = sheetName + sequence; Excel.Worksheet tempWorkSheet = destWorkbook.Sheets[1] as Excel.Worksheet; workingSheet.Copy(Missing.Value, tempWorkSheet); Excel.Worksheet copySourceSheet = destWorkbook.Worksheets[sheetName + sequence]; ExcelUtilies.CopyRange(copySourceSheet, sourceBeginCell, sourceEndCell, copyDestSheet, destBeginCell); } catch (Exception ex) { this.Logger.Error(string.Format("Append {0} report failed! \n {1}", sheetName, ex.StackTrace)); throw; } finally { FunnelReportHelper.CloseWorkingWorkbook(sourceBook); } } }
private void FormatRevenueByPac(Worksheet revenueByPacDestSheet) { if (revenueByPacDestSheet.Cells[1, 4].Text == "DataSource") { ExcelUtilies.DeleteColumns(revenueByPacDestSheet, "D", "D"); } if (revenueByPacDestSheet.Cells[1, 1].Text == "YearMonth") { ExcelUtilies.DeleteColumns(revenueByPacDestSheet, "A", "A"); } if (revenueByPacDestSheet.Cells[1, 2].Text == "CityName") { revenueByPacDestSheet.Range["B1"].Value = ""; } if (revenueByPacDestSheet.Cells[1, 1].Text == "ProductCategory") { revenueByPacDestSheet.Range["A1"].Value = "MTD"; int endRow = 2; string label = revenueByPacDestSheet.Cells[endRow, 1].Text; while (!string.IsNullOrWhiteSpace(label)) { endRow++; label = revenueByPacDestSheet.Cells[endRow, 1].Text; } endRow--; Range range = revenueByPacDestSheet.Range["A2", "A" + endRow]; this.MergeCells(range, "Mini Center"); } revenueByPacDestSheet.Range["A1", "H12"].Interior.ColorIndex = 0; //revenueByPacDestSheet.Range["A1", "F1"].Interior.Color = this.ColumnColor; revenueByPacDestSheet.Range["A1", "F1"].Font.Bold = true; ExcelUtilies.InsertRow(revenueByPacDestSheet, "A1"); ExcelUtilies.InsertRow(revenueByPacDestSheet, "A1"); ExcelUtilies.InsertRow(revenueByPacDestSheet, "A1"); revenueByPacDestSheet.Range["A1"].Value = "Currency"; revenueByPacDestSheet.Range["A2"].Value = "RMB"; revenueByPacDestSheet.Range["A2"].Font.Bold = true; revenueByPacDestSheet.Range["A2"].Interior.Color = this.ColumnColor; this.MergeCells(revenueByPacDestSheet.Range["A3", "C3"], "Mini Center Rev not include Mini TS and B2B"); }
public static void CloseWorkingWorkbook(Excel.Workbook workbook) { if (workbook != null) { if (!workbook.Saved) { workbook.Save(); } workbook.Close(); ExcelUtilies.ReleaseComObject(workbook); } }
private void FormatSummary(Excel.Worksheet summaryDestSheet) { summaryDestSheet.Range["A1", "AZ100"].Interior.ColorIndex = 0; this.MergeCellsForSummary(summaryDestSheet); ExcelUtilies.InsertRow(summaryDestSheet, "A1"); ExcelUtilies.InsertRow(summaryDestSheet, "A1"); ExcelUtilies.InsertRow(summaryDestSheet, "A1"); summaryDestSheet.Range["A4", "AX4"].Font.Bold = true; summaryDestSheet.Range["A3", "AX3"].Merge(); summaryDestSheet.Range["A3", "AX3"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; summaryDestSheet.Range["A3", "AX3"].VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; summaryDestSheet.Range["A3", "AX3"].Value = "Funnel Performance"; summaryDestSheet.Range["A3", "AX3"].Font.Color = Color.White; summaryDestSheet.Range["A3", "AX3"].Font.Bold = true; summaryDestSheet.Range["A3", "AX3"].Interior.Color = Color.DarkBlue; summaryDestSheet.Range["A1", "C1"].Merge(); summaryDestSheet.Range["A1", "C1"].Value = "Center Month To Date Summary as"; summaryDestSheet.Range["A1", "K1"].Font.Bold = true; summaryDestSheet.Range["F1", "H1"].Merge(); summaryDestSheet.Range["F1", "H1"].Value = "Total days of this month"; summaryDestSheet.Range["D1"].Value = DateTime.Today.ToString("yyyy/MM/dd"); summaryDestSheet.Range["D1"].Font.Color = Color.Red; summaryDestSheet.Range["D1"].ColumnWidth = 10; DateTime date = DateTime.Today; summaryDestSheet.Range["I1"].Value = DateTime.DaysInMonth(date.Year, date.Month); summaryDestSheet.Range["I1"].Font.Color = Color.Red; summaryDestSheet.Range["K1", "L1"].Merge(); summaryDestSheet.Range["K1", "L1"].Value = "Time Ratio"; summaryDestSheet.Range["M1", "N1"].Merge(); summaryDestSheet.Range["M1", "N1"].Value = string.Format("{0:N2}%", ((float)date.Day / (float)DateTime.DaysInMonth(date.Year, date.Month)) * 100); summaryDestSheet.Range["M1", "N1"].Font.Color = Color.Red; ExcelUtilies.FreezePanes(summaryDestSheet, 2, 4); }
private void AppendReport(Excel.Workbook destWorkbook, Excel.Worksheet copyDestSheet, string sheetName, string column, string columnHeader, string reportFile, int sequence) { if (File.Exists(reportFile)) { Excel.Workbook sourceBook = null; try { sourceBook = excel.Workbooks.Open(reportFile); Excel.Worksheet workingSheet = sourceBook.Worksheets[1]; workingSheet.Name = sheetName + sequence; Excel.Worksheet tempWorkSheet = destWorkbook.Sheets[1] as Excel.Worksheet; workingSheet.Copy(Missing.Value, tempWorkSheet); Excel.Worksheet copySourceSheet = destWorkbook.Worksheets[sheetName + sequence]; //copy week report to dest if (copySourceSheet.Cells[1, 1].Text == "Spot Rate") { ExcelUtilies.CopyRange(copySourceSheet, "B1", "B" + FunnelMonthlyReportParameters.RowCount, copyDestSheet, column + "2"); } else { ExcelUtilies.CopyRange(copySourceSheet, "B1", "B" + FunnelMonthlyReportParameters.RowCount, copyDestSheet, column + "1"); } } catch (Exception ex) { this.Logger.Error(string.Format("Append {0} report failed! \n {1}", sheetName, ex.StackTrace)); throw; } finally { FunnelReportHelper.CloseWorkingWorkbook(sourceBook); } } copyDestSheet.Cells[1, column] = columnHeader; copyDestSheet.Cells[1, column].Font.Bold = true; copyDestSheet.Cells[1, column].Font.Size = 10; copyDestSheet.Columns[column].ColumnWidth = 15; }
public static void SaveTempWorkbook(Excel.Workbook tempWorkbook) { if (tempWorkbook != null) { //string folder = System.Reflection.Assembly.GetExecutingAssembly().CodeBase.Remove(System.Reflection.Assembly.GetExecutingAssembly().CodeBase.LastIndexOf("/")); //folder = folder.Replace(@"file:///", ""); //if (!Directory.Exists(folder + "/temp")) // Directory.CreateDirectory(folder + "/temp"); //tempWorkbook.SaveAs(folder + "/temp/book1.xls"); tempWorkbook.SaveAs("book1.xls"); string fullname = tempWorkbook.FullName; tempWorkbook.Close(); if (File.Exists(fullname)) { File.Delete(fullname); } ExcelUtilies.ReleaseComObject(tempWorkbook); } }
private void FormatSummary(Excel.Worksheet summaryDestSheet) { summaryDestSheet.Range["A1", "T100"].Interior.ColorIndex = 0; this.MergeCellsForSummary(summaryDestSheet); ExcelUtilies.InsertRow(summaryDestSheet, "A1"); ExcelUtilies.InsertRow(summaryDestSheet, "A1"); ExcelUtilies.InsertRow(summaryDestSheet, "A1"); summaryDestSheet.Range["A4", "T4"].Font.Bold = true; summaryDestSheet.Range["A3", "T3"].Merge(); summaryDestSheet.Range["A3", "T3"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; summaryDestSheet.Range["A3", "T3"].VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; summaryDestSheet.Range["A3", "T3"].Value = "Funnel Performance"; summaryDestSheet.Range["A3", "T3"].Font.Color = Color.White; summaryDestSheet.Range["A3", "T3"].Font.Bold = true; summaryDestSheet.Range["A3", "T3"].Interior.Color = Color.DarkBlue; summaryDestSheet.Range["A1", "C1"].Merge(); summaryDestSheet.Range["A1", "C1"].Value = "Mini Center Month To Date Summary as"; summaryDestSheet.Range["A1", "H1"].Font.Bold = true; summaryDestSheet.Range["F1", "G1"].Merge(); summaryDestSheet.Range["F1", "G1"].Value = "Total days of this month"; summaryDestSheet.Range["D1"].Value = summaryDestSheet.Cells[5, 1].Text; summaryDestSheet.Range["D1"].Font.Color = Color.Red; DateTime date; if (DateTime.TryParse(summaryDestSheet.Cells[1, 4].Text, out date)) { summaryDestSheet.Range["H1"].Value = DateTime.DaysInMonth(date.Year, date.Month); } else { summaryDestSheet.Range["H1"].Value = DateTime.DaysInMonth(DateTime.Now.AddDays(-1).Year, DateTime.Now.AddDays(-1).Month); } summaryDestSheet.Range["H1"].Font.Color = Color.Red; ExcelUtilies.FreezePanes(summaryDestSheet, 3, 4); }
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); }
private void DeleteEmptyRows(Worksheet copyDestSheet) { int row = 1; string str = copyDestSheet.Cells[row, 1].Text; while (!string.IsNullOrWhiteSpace(str)) { row++; str = copyDestSheet.Cells[row, 1].Text; } copyDestSheet.Range["A" + row].Value = "Total"; row = row + 4; str = copyDestSheet.Cells[row, 1].Text; while (string.IsNullOrWhiteSpace(str)) { ExcelUtilies.DeleteRow(copyDestSheet, "A" + row); str = copyDestSheet.Cells[row, 1].Text; } }
private void AppendReport(Excel.Workbook destWorkbook, Excel.Worksheet copyDestSheetBefore, string sheetName, string column, string reportFile, int sequence) { if (File.Exists(reportFile)) { Excel.Workbook sourceBook = null; try { sourceBook = destWorkbook.Application.Workbooks.Open(reportFile); Excel.Worksheet workingSheet = sourceBook.Worksheets[1]; workingSheet.Name = sheetName + sequence; Excel.Worksheet tempWorkSheet = destWorkbook.Sheets[1] as Excel.Worksheet; workingSheet.Copy(Missing.Value, tempWorkSheet); Excel.Worksheet copySourceSheet = destWorkbook.Worksheets[sheetName + sequence]; //copy week report to dest if (string.IsNullOrWhiteSpace(copySourceSheet.Cells[1, 1].Text)) { ExcelUtilies.CopyRange(copySourceSheet, "B2", "B" + FunnelMonthlyReportParameters.OutputRowCount, copyDestSheetBefore, column + "1"); } else { ExcelUtilies.CopyRange(copySourceSheet, "B1", "B" + FunnelMonthlyReportParameters.OutputRowCount, copyDestSheetBefore, column + "1"); } } catch (Exception ex) { this.Logger.Error(string.Format("Append {0} report failed! \n {1}", sheetName, ex.StackTrace)); throw; } finally { FunnelReportHelper.CloseWorkingWorkbook(sourceBook); } } }
private void FormatMiniTS(Excel.Worksheet copyDestSheet, string header) { copyDestSheet.Range["A1", "GI500"].Interior.ColorIndex = 0; int endColum = this.GetColumnHasValueEndIndex(copyDestSheet, 1, 2); this.MergeHeader(copyDestSheet, 1, 2, endColum - 1, "Mini TS " + header); Range range = copyDestSheet.Range["A1", copyDestSheet.Cells[2, endColum - 1]]; range.Borders.Weight = 2; range.Borders.Color = Color.Black; //color column A copyDestSheet.Range["A1"].EntireColumn.Interior.ColorIndex = 0; int begin = this.GetWeeks(CurrentDate) + 3; int end = begin + this.GetDays(CurrentDate); ExcelUtilies.DeleteRow(copyDestSheet, "A" + begin); ExcelUtilies.DeleteRow(copyDestSheet, "A" + begin); copyDestSheet.Range["A" + begin, "A" + end].Interior.Color = this.ColumnColor; this.DeleteEmptyRows(copyDestSheet); //Set currency label ExcelUtilies.InsertRow(copyDestSheet, "A1"); ExcelUtilies.InsertRow(copyDestSheet, "A1"); ExcelUtilies.InsertRow(copyDestSheet, "A1"); copyDestSheet.Range["A1"].Value = "Currency"; copyDestSheet.Range["A2"].Value = "RMB"; copyDestSheet.Range["A2"].Font.Bold = true; copyDestSheet.Range["A2"].Interior.Color = this.ColumnColor; ExcelUtilies.FreezePanes(copyDestSheet, 1, 5); }
public bool MergeFiles(Dictionary <string, ReportContext> MergedFiles, string outputFile, out string mergedFile) { mergedFile = string.Empty; if (this.Logger == null) { this.Logger = new QVConfigLog(); } this.Logger.Message("Beigin to merge funnel revenue reports."); excel = new Excel.Application(); Dictionary <string, string> totalReports = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "Total"); Dictionary <string, string> tsReports = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "TS"); Dictionary <string, string> onlineReports = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "Online"); Dictionary <string, string> partnerReports = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "Partner"); Dictionary <string, string> smeReports = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "SME"); Excel.Workbook tempWorkbook = null; Excel.Workbook bookDest = 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."); foreach (var item in totalReports.Keys) { totalBook = excel.Workbooks.Open(totalReports[item]); Excel.Worksheet totalSheet = totalBook.Worksheets[1]; totalSheet.Name = item; //copy month report to dest //Column totalSheet.Copy(Missing.Value, tempWorkSheet); Excel.Worksheet copyDestSheet = tempWorkbook.Worksheets[item]; //append TS reports //Column if (tsReports.ContainsKey(item)) { TSBook = excel.Workbooks.Open(tsReports[item]); Excel.Worksheet weekSheet = TSBook.Worksheets[1]; weekSheet.Name = item + "1"; weekSheet.Copy(Missing.Value, tempWorkSheet); Excel.Worksheet copySourceSheet = tempWorkbook.Worksheets[item + "1"]; //copy week report to dest ExcelUtilies.CopyRange(copySourceSheet, "A1", WeeklyStatisticReportParameters.TSColumn + WeeklyStatisticReportParameters.TSRowCount, copyDestSheet, "A22"); //ExcelUtilies.ReleaseComObject(weekSheet); } //append Online report //Column if (onlineReports.ContainsKey(item)) { onlineBook = excel.Workbooks.Open(onlineReports[item]); Excel.Worksheet weekSheet = onlineBook.Worksheets[1]; weekSheet.Name = item + "2"; weekSheet.Copy(Missing.Value, tempWorkSheet); Excel.Worksheet copySourceSheet = tempWorkbook.Worksheets[item + "2"]; //copy week report to dest ExcelUtilies.CopyRange(copySourceSheet, "A1", WeeklyStatisticReportParameters.OnlineColumn + WeeklyStatisticReportParameters.OnlineRowCount, copyDestSheet, "A40"); //ExcelUtilies.ReleaseComObject(weekSheet); } //append Partner revenue //Column this.Logger.Message("append Partner revenue," + partnerReports.Count); if (partnerReports.ContainsKey(item)) { this.Logger.Message("append Partner revenue"); partnerBook = excel.Workbooks.Open(partnerReports[item]); Excel.Worksheet weekSheet = partnerBook.Worksheets[1]; weekSheet.Name = item + "3"; weekSheet.Copy(Missing.Value, tempWorkSheet); Excel.Worksheet copySourceSheet = tempWorkbook.Worksheets[item + "3"]; //copy week report to dest ExcelUtilies.CopyRange(copySourceSheet, "A1", WeeklyStatisticReportParameters.PartnerColumn + WeeklyStatisticReportParameters.PartnerRowCount, copyDestSheet, "A62"); //ExcelUtilies.ReleaseComObject(weekSheet); } //append SME revenue //Column if (smeReports.ContainsKey(item)) { this.Logger.Message("append SME revenue"); smeBook = excel.Workbooks.Open(smeReports[item]); Excel.Worksheet weekSheet = smeBook.Worksheets[1]; weekSheet.Name = item + "4"; weekSheet.Copy(Missing.Value, tempWorkSheet); Excel.Worksheet copySourceSheet = tempWorkbook.Worksheets[item + "4"]; //copy week report to dest ExcelUtilies.CopyRange(copySourceSheet, "A1", WeeklyStatisticReportParameters.SMEColumn + WeeklyStatisticReportParameters.SMERowCount, copyDestSheet, "A85"); //ExcelUtilies.ReleaseComObject(weekSheet); } copyDestSheet.Range["A1", WeeklyStatisticReportParameters.TotalColumn + "110"].Interior.ColorIndex = 0; Excel.Range range = copyDestSheet.Range["A1"]; range.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown); //ExcelUtilies.ReleaseComObject(range); this.SetTitle(copyDestSheet, 1, 1, "Total by product"); this.SetTitle(copyDestSheet, 22, 1, "Telesales"); this.SetTitle(copyDestSheet, 40, 1, "Online"); this.SetTitle(copyDestSheet, 62, 1, "Partner revenue"); this.SetTitle(copyDestSheet, 85, 1, "SME revenue"); if (GeneralParameters.KFR2 == false && GeneralParameters.KFR1 == false && GeneralParameters.KFR3 == false) { ExcelUtilies.DeleteColumns(copyDestSheet, "U", "AC"); ExcelUtilies.DeleteColumns(copyDestSheet, "I", "N"); } else if (GeneralParameters.KFR1 == true && GeneralParameters.KFR2 == false && GeneralParameters.KFR3 == false) { ExcelUtilies.DeleteColumns(copyDestSheet, "X", "AC"); ExcelUtilies.DeleteColumns(copyDestSheet, "K", "N"); } else if (GeneralParameters.KFR1 == true && GeneralParameters.KFR2 == true && GeneralParameters.KFR3 == false) { ExcelUtilies.DeleteColumns(copyDestSheet, "AA", "AC"); ExcelUtilies.DeleteColumns(copyDestSheet, "M", "N"); } //ExcelUtilies.ReleaseComObject(copyDestSheet); //close working workbook this.CloseSourceReportsExcel(); } //Copy KFR revenue to tempWorkSheet this.CopyKFRRevenue2SheetDest(tempWorkbook, MergedFiles); this.Logger.Message("Create dest book."); bookDest = excel.Workbooks.Add(Missing.Value); //get the first sheet of dest book this.Logger.Message("get the first sheet of dest book"); Excel.Worksheet sheetDest = bookDest.Worksheets[1] as Excel.Worksheet; //copy to dest from temp workbook this.Logger.Message("copy to dest from temp workbook."); foreach (var item in tempWorkbook.Worksheets) { Excel.Worksheet sheet = item as Excel.Worksheet; if (totalReports.Keys.Contains(sheet.Name)) { sheet.Copy(Missing.Value, sheetDest); } else if (sheet.Name == "KFR Revenue") { sheet.Copy(sheetDest, Missing.Value); } } //delete sheet1 sheet2 sheet3 excel.DisplayAlerts = false; this.Logger.Message("delete sheet1 sheet2 sheet3"); foreach (var item in bookDest.Worksheets) { Excel.Worksheet sheet = item as Excel.Worksheet; if (!totalReports.Keys.Contains(sheet.Name) && sheet.Name != "KFR Revenue") { sheet.Delete(); } } //clear the temp workbook this.Logger.Message("clear the temp workbook"); FunnelReportHelper.SaveTempWorkbook(tempWorkbook); //save the dest this.Logger.Message("save the dest."); mergedFile = outputFile.Replace(".xls", "_" + DateTime.Now.LastWeekendDate().WeekOfYearString() + ".xls"); //mergedFile = outputFile.Replace(".xls", "_" + DateTime.Now.LatestTwoWeeksEndDate().WeekOfYearString() + ".xls"); if (File.Exists(mergedFile)) { File.Delete(mergedFile); } bookDest.SaveAs(mergedFile); bookDest.Close(); excel.DisplayAlerts = true; } catch (Exception ex) { this.Logger.Error(ex.Message + "\n" + ex.StackTrace); excel.DisplayAlerts = false; this.CloseSourceReportsExcel(); FunnelReportHelper.SaveTempWorkbook(tempWorkbook); excel.DisplayAlerts = true; throw; } finally { excel.Application.Quit(); //ExcelUtilies.ReleaseComObject(excel); //excel = null; //System.GC.Collect(); ExcelUtilies.Kill(excel); } return(true); }
private void CopyKFRRevenue2SheetDest(Excel.Workbook tempWorkbook, Dictionary <string, ReportContext> MergedFiles) { Dictionary <string, string> KFRRevenueReports = FunnelReportHelper.GetMergedFilesByCategory(MergedFiles, "KFRRevenue"); if (MergedFiles.ContainsKey("FunnelRevenue_KFRRevenue_NotSet")) { ReportContext report = MergedFiles["FunnelRevenue_KFRRevenue_NotSet"]; Excel.Worksheet tempWorkSheet = tempWorkbook.Worksheets[1] as Excel.Worksheet; totalBook = excel.Workbooks.Open(report.OutputFullName); Excel.Worksheet totalSheet = totalBook.Worksheets[1]; totalSheet.Name = "KFR Revenue"; //copy month report to dest //Column totalSheet.Copy(Missing.Value, tempWorkSheet); Excel.Worksheet copyDestSheet = tempWorkbook.Worksheets["KFR Revenue"]; //append TS reports //Column if (MergedFiles.ContainsKey("FunnelRevenue_KFRRevenue_CH157")) { report = MergedFiles["FunnelRevenue_KFRRevenue_CH157"]; TSBook = excel.Workbooks.Open(report.OutputFullName); Excel.Worksheet weekSheet = TSBook.Worksheets[1]; weekSheet.Name = report.Description; weekSheet.Copy(Missing.Value, tempWorkSheet); Excel.Worksheet copySourceSheet = tempWorkbook.Worksheets[report.Description]; //copy week report to dest ExcelUtilies.CopyRange(copySourceSheet, "A1", WeeklyStatisticReportParameters.KFRRevenueColumn + WeeklyStatisticReportParameters.KFRRevenueRowCount, copyDestSheet, "A21"); //ExcelUtilies.ReleaseComObject(weekSheet); } //append Online report //Column if (MergedFiles.ContainsKey("FunnelRevenue_KFRRevenue_CH157")) { report = MergedFiles["FunnelRevenue_KFRRevenue_CH159"]; onlineBook = excel.Workbooks.Open(report.OutputFullName); Excel.Worksheet weekSheet = onlineBook.Worksheets[1]; weekSheet.Name = report.Description; weekSheet.Copy(Missing.Value, tempWorkSheet); Excel.Worksheet copySourceSheet = tempWorkbook.Worksheets[report.Description]; //copy week report to dest ExcelUtilies.CopyRange(copySourceSheet, "A1", WeeklyStatisticReportParameters.KFRRevenueColumn + WeeklyStatisticReportParameters.KFRRevenueRowCount, copyDestSheet, "A38"); //ExcelUtilies.ReleaseComObject(weekSheet); } //append Partner report //Column if (MergedFiles.ContainsKey("FunnelRevenue_KFRRevenue_CH184")) { report = MergedFiles["FunnelRevenue_KFRRevenue_CH184"]; partnerBook = excel.Workbooks.Open(report.OutputFullName); Excel.Worksheet weekSheet = partnerBook.Worksheets[1]; weekSheet.Name = report.Description; weekSheet.Copy(Missing.Value, tempWorkSheet); Excel.Worksheet copySourceSheet = tempWorkbook.Worksheets[report.Description]; //copy week report to dest ExcelUtilies.CopyRange(copySourceSheet, "A1", WeeklyStatisticReportParameters.KFRRevenueColumn + WeeklyStatisticReportParameters.KFRRevenueRowCount, copyDestSheet, "A58"); //ExcelUtilies.ReleaseComObject(weekSheet); } //append SME report //Column if (MergedFiles.ContainsKey("FunnelRevenue_KFRRevenue_CH185")) { report = MergedFiles["FunnelRevenue_KFRRevenue_CH185"]; smeBook = excel.Workbooks.Open(report.OutputFullName); Excel.Worksheet weekSheet = smeBook.Worksheets[1]; weekSheet.Name = report.Description; weekSheet.Copy(Missing.Value, tempWorkSheet); Excel.Worksheet copySourceSheet = tempWorkbook.Worksheets[report.Description]; //copy week report to dest ExcelUtilies.CopyRange(copySourceSheet, "A1", WeeklyStatisticReportParameters.KFRRevenueColumn + WeeklyStatisticReportParameters.KFRRevenueRowCount, copyDestSheet, "A78"); //ExcelUtilies.ReleaseComObject(weekSheet); } copyDestSheet.Range["A1", WeeklyStatisticReportParameters.TotalColumn + "106"].Interior.ColorIndex = 0; if (GeneralParameters.KFR2 == false && GeneralParameters.KFR1 == false && GeneralParameters.KFR3 == false) { ExcelUtilies.DeleteColumns(copyDestSheet, "S", "AA"); ExcelUtilies.DeleteColumns(copyDestSheet, "G", "L"); } else if (GeneralParameters.KFR1 == true && GeneralParameters.KFR2 == false && GeneralParameters.KFR3 == false) { ExcelUtilies.DeleteColumns(copyDestSheet, "V", "AA"); ExcelUtilies.DeleteColumns(copyDestSheet, "I", "L"); } else if (GeneralParameters.KFR1 == true && GeneralParameters.KFR2 == true && GeneralParameters.KFR3 == false) { ExcelUtilies.DeleteColumns(copyDestSheet, "Y", "AA"); ExcelUtilies.DeleteColumns(copyDestSheet, "K", "L"); } //ExcelUtilies.ReleaseComObject(copyDestSheet); this.CloseSourceReportsExcel(); } }
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); }
private void FormatDetail(Excel.Worksheet copyDestSheet, string header) { copyDestSheet.Range["A1", "GI500"].Interior.ColorIndex = 0; this.FormatDetailHeaders(copyDestSheet, header); //Range range = copyDestSheet.Range[FunnelCNMiniMergeParameters.Online_BeginCell, FunnelCNMiniMergeParameters.Online_EndCell]; //this.MergeCells(range, header + " Online"); //range = copyDestSheet.Range[FunnelCNMiniMergeParameters.IB_BeginCell, FunnelCNMiniMergeParameters.IB_EndCell]; //this.MergeCells(range, header + " IB"); //range = copyDestSheet.Range[FunnelCNMiniMergeParameters.WI_BeginCell, FunnelCNMiniMergeParameters.WI_EndCell]; //this.MergeCells(range, header + " WI"); //range = copyDestSheet.Range[FunnelCNMiniMergeParameters.Offline_BeginCell, FunnelCNMiniMergeParameters.Offline_EndCell]; //this.MergeCells(range, header + " Offline"); //if (copyDestSheet.Name == "NJ2 Details") //{ // //No B2B and Others // //range = copyDestSheet.Range[FunnelCNMiniMergeParameters.B2B_BeginCell, FunnelCNMiniMergeParameters.B2B_EndCell]; // //this.MergeCells(range, header + " B2B"); // //range = copyDestSheet.Range[FunnelCNMiniMergeParameters.Others_BeginCell, FunnelCNMiniMergeParameters.Others_EndCell]; // //this.MergeCells(range, header + " Others"); // range = copyDestSheet.Range[FunnelCNMiniMergeParameters.B2B_BeginCell, FunnelCNMiniMergeParameters.B2B_EndCell]; // this.MergeCells(range, header + " Total"); // range = copyDestSheet.Range[FunnelCNMiniMergeParameters.Online_BeginCell, FunnelCNMiniMergeParameters.B2B_EndCell.Replace("1", "2")]; // range.Interior.Color = this.HeaderColor; // range = copyDestSheet.Range[FunnelCNMiniMergeParameters.B2B_BeginCell, FunnelCNMiniMergeParameters.B2B_EndCell.Replace("1", "2")]; // range.Interior.Color = Color.Gray; // //merge cells for special weekly // int startColumn = ExcelUtilies.ExcelColumnNameToIndex("CK"); // int endColum = this.MergeHeader(copyDestSheet, 1, startColumn, header); // range = copyDestSheet.Range["A1", copyDestSheet.Cells[2, endColum - 2]]; // range.Borders.Weight = 2; // range.Borders.Color = Color.Black; //} //else //{ // range = copyDestSheet.Range[FunnelCNMiniMergeParameters.B2B_BeginCell, FunnelCNMiniMergeParameters.B2B_EndCell]; // this.MergeCells(range, header + " B2B"); // range = copyDestSheet.Range[FunnelCNMiniMergeParameters.Others_BeginCell, FunnelCNMiniMergeParameters.Others_EndCell]; // this.MergeCells(range, header + " Others"); // range = copyDestSheet.Range[FunnelCNMiniMergeParameters.Online_BeginCell, FunnelCNMiniMergeParameters.Total_EndCell.Replace("1", "2")]; // range.Interior.Color = this.HeaderColor; // range = copyDestSheet.Range[FunnelCNMiniMergeParameters.Total_BeginCell, FunnelCNMiniMergeParameters.Total_EndCell]; // this.MergeCells(range, header + " Total"); // range = copyDestSheet.Range[FunnelCNMiniMergeParameters.Total_BeginCell, FunnelCNMiniMergeParameters.Total_EndCell.Replace("1", "2")]; // range.Interior.Color = Color.Gray; // //merge cells for special weekly // int startColumn = ExcelUtilies.ExcelColumnNameToIndex("DS"); // int endColum = this.MergeHeader(copyDestSheet, 1, startColumn, header); // range = copyDestSheet.Range["A1", copyDestSheet.Cells[2, endColum - 2]]; // range.Borders.Weight = 2; // range.Borders.Color = Color.Black; //} //color column A copyDestSheet.Range["A1"].EntireColumn.Interior.ColorIndex = 0; int begin = this.GetWeeks(CurrentDate) + 3; int end = begin + this.GetDays(CurrentDate); //delete daily headers ExcelUtilies.DeleteRow(copyDestSheet, "A" + begin); ExcelUtilies.DeleteRow(copyDestSheet, "A" + begin); copyDestSheet.Range["A" + begin, "A" + end].Interior.Color = this.ColumnColor; this.DeleteEmptyRows(copyDestSheet); //Set currency label ExcelUtilies.InsertRow(copyDestSheet, "A1"); ExcelUtilies.InsertRow(copyDestSheet, "A1"); ExcelUtilies.InsertRow(copyDestSheet, "A1"); copyDestSheet.Range["A1"].Value = "Currency"; copyDestSheet.Range["A2"].Value = "RMB"; copyDestSheet.Range["A2"].Font.Bold = true; copyDestSheet.Range["A2"].Interior.Color = this.ColumnColor; ExcelUtilies.FreezePanes(copyDestSheet, 1, 5); }
private Excel.Workbook MergedOutputFiles(Excel.Application excel, Dictionary <string, ReportContext> MergedFiles) { Dictionary <string, string> thisMonthReports = FunnelReportHelper.GetOutputMergedFilesByCategory(MergedFiles, "ThisMonth"); Dictionary <string, string> monthReports = FunnelReportHelper.GetOutputMergedFilesByCategory(MergedFiles, "1011Month"); Dictionary <string, string> monthDiffReports = FunnelReportHelper.GetOutputMergedFilesByCategory(MergedFiles, "MonthDiff"); Dictionary <string, string> actualReports = FunnelReportHelper.GetOutputMergedFilesByCategory(MergedFiles, "Actuals"); Dictionary <string, string> KFRxReports = FunnelReportHelper.GetOutputMergedFilesByCategory(MergedFiles, "KFRx"); Dictionary <string, string> DiffReports = FunnelReportHelper.GetOutputMergedFilesByCategory(MergedFiles, "Diff"); Dictionary <string, string> PriorYearReports = FunnelReportHelper.GetOutputMergedFilesByCategory(MergedFiles, "PriorYear"); Dictionary <string, string> PriorYearDiffReport = FunnelReportHelper.GetOutputMergedFilesByCategory(MergedFiles, "PriorYearDiff"); if (thisMonthReports.Count == 0) { return(null); } Excel.Workbook tempWorkbook = null; try { this.Logger.Message("Create tempWorkbook."); tempWorkbook = excel.Workbooks.Add(Missing.Value); //this.AddCategoryOutputSheet(tempWorkbook); this.Logger.Message("Create Output Sheets."); Excel.Worksheet outputTotalSheet = tempWorkbook.Sheets.Add(); outputTotalSheet.Name = OutputTotal; Excel.Worksheet outputJapanSheet = tempWorkbook.Sheets.Add(); outputJapanSheet.Name = OutputJPKR; Excel.Worksheet outputBrazilSheet = tempWorkbook.Sheets.Add(); outputBrazilSheet.Name = OutputBrazil; Excel.Worksheet outputEuropeSheet = tempWorkbook.Sheets.Add(); outputEuropeSheet.Name = OutputEurope; Excel.Worksheet outputMXUSSheet = tempWorkbook.Sheets.Add(); outputMXUSSheet.Name = OutputMXUS; Excel.Worksheet outputOthersSheet = tempWorkbook.Sheets.Add(); outputOthersSheet.Name = OutputOthers; //create a new work sheet Excel.Worksheet tempWorkSheet = tempWorkbook.Worksheets[1] as Excel.Worksheet; this.Logger.Message("Copy to tempwork."); foreach (var item in actualReports.Keys) { thisMonthBook = excel.Workbooks.Open(thisMonthReports[item]); Excel.Worksheet thisMonthSheet = thisMonthBook.Worksheets[1]; thisMonthSheet.Name = item; //copy month report to dest //Column B - M thisMonthSheet.Copy(Missing.Value, tempWorkSheet); Excel.Worksheet copyDestSheet = tempWorkbook.Worksheets[item]; if (string.IsNullOrWhiteSpace(copyDestSheet.Cells[1, 1].Text)) { ExcelUtilies.DeleteRow(copyDestSheet, "A1"); } //month if (monthReports.ContainsKey(item)) { this.AppendReport(tempWorkbook, copyDestSheet, item, FunnelMonthlyReportParameters.OutputMonth, monthReports[item], 1); } //monthDiff if (monthDiffReports.ContainsKey(item)) { this.AppendReport(tempWorkbook, copyDestSheet, item, FunnelMonthlyReportParameters.OutputMonthDiff, monthDiffReports[item], 2); } //Actuals if (actualReports.ContainsKey(item)) { this.AppendReport(tempWorkbook, copyDestSheet, item, FunnelMonthlyReportParameters.OutputActuals, actualReports[item], 3); } //KFR1 if (KFRxReports.ContainsKey(item)) { this.AppendReport(tempWorkbook, copyDestSheet, item, FunnelMonthlyReportParameters.OutputKFR1, KFRxReports[item], 4); } //Diff if (DiffReports.ContainsKey(item)) { this.AppendReport(tempWorkbook, copyDestSheet, item, FunnelMonthlyReportParameters.OutputDiff, DiffReports[item], 5); } //PriorYear if (PriorYearReports.ContainsKey(item)) { this.AppendReport(tempWorkbook, copyDestSheet, item, FunnelMonthlyReportParameters.OutputPriorYear, PriorYearReports[item], 6); } //PriorYearDiff if (PriorYearDiffReport.ContainsKey(item)) { this.AppendReport(tempWorkbook, copyDestSheet, item, FunnelMonthlyReportParameters.OutputPriorYearDiff, PriorYearDiffReport[item], 7); } if (!string.IsNullOrWhiteSpace(copyDestSheet.Cells[1, 1].Text)) { ExcelUtilies.InsertRow(copyDestSheet, "A1"); } this.SetHeaderTitle(copyDestSheet); this.CloseOutputSourceReportsExcel(); #region Category output Logger.Message("Category output " + item); this.FormatOutputCells(copyDestSheet); copyDestSheet.Activate(); if (item == "Japan") { ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputJapanSheet, "A3"); outputJapanSheet.Cells[2, 1] = item; } else if (item == "Korea") { ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputJapanSheet, "A21"); outputJapanSheet.Cells[20, 1] = item; } else if (item == "Total") { ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputTotalSheet, "A3"); outputTotalSheet.Cells[2, 1] = item; } else if (item == "Brazil") { ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputBrazilSheet, "A3"); outputBrazilSheet.Cells[2, 1] = item; } else if (item == "Europe") { ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputEuropeSheet, "A3"); outputEuropeSheet.Cells[2, 1] = item; } else if (item == "Spain") { ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputEuropeSheet, "A19"); outputEuropeSheet.Cells[18, 1] = item; } else if (item == "MEAST") { ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputEuropeSheet, "A37"); outputEuropeSheet.Cells[36, 1] = item; } else if (item == "ROE") { ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputEuropeSheet, "A55"); outputEuropeSheet.Cells[54, 1] = item; } else if (item == "Italy") { ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputEuropeSheet, "A73"); outputEuropeSheet.Cells[72, 1] = item; } else if (item == "Germany") { ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputEuropeSheet, "A91"); outputEuropeSheet.Cells[90, 1] = item; } else if (item == "France") { ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputEuropeSheet, "A109"); outputEuropeSheet.Cells[108, 1] = item; } else if (item == "Mexico+US+ROLA") { ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputMXUSSheet, "A3"); outputMXUSSheet.Cells[2, 1] = item; } else if (item == "US") { ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputMXUSSheet, "A19"); outputMXUSSheet.Cells[18, 1] = item; } else if (item == "Mexico") { ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputMXUSSheet, "A37"); outputMXUSSheet.Cells[36, 1] = item; } else if (item == "ROLA") { ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputMXUSSheet, "A55"); outputMXUSSheet.Cells[54, 1] = item; } else if (item == "ROA") { ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputOthersSheet, "A3"); outputOthersSheet.Cells[2, 1] = item; } else if (item == "ROW") { ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputOthersSheet, "A19"); outputOthersSheet.Cells[18, 1] = item; } //else if (item == "Thailand") //{ // ExcelUtilies.CopyRange(copyDestSheet, "A1", "I13", outputOthersSheet, "A37"); // outputOthersSheet.Cells[36, 1] = item; //} #endregion } this.FormatOutputColumnWidth(outputJapanSheet); this.FormatOutputColumnWidth(outputTotalSheet); this.FormatOutputColumnWidth(outputMXUSSheet); this.FormatOutputColumnWidth(outputOthersSheet); this.FormatOutputColumnWidth(outputBrazilSheet); this.FormatOutputColumnWidth(outputEuropeSheet); } catch (Exception ex) { this.CloseOutputSourceReportsExcel(); throw; } return(tempWorkbook); }
private void InsertBlankRow(Excel.Worksheet sheet, string cell) { ExcelUtilies.InsertRow(sheet, cell); sheet.Range[cell].EntireRow.Interior.ColorIndex = -1; sheet.Range[cell].EntireRow.RowHeight = sheet.Range[cell].EntireRow.RowHeight / 2; }
private void FormatCells(Excel.Worksheet sheet) { //Line name - Example //Visits(in '000) sheet.Range["A7"].EntireRow.NumberFormat = "#,"; string rateFormat = "#,##0%"; //Pay rate % - 40% sheet.Range["A11"].EntireRow.NumberFormat = rateFormat; //Appt Rate% - 45% sheet.Range["A18"].EntireRow.NumberFormat = rateFormat; //ShowUp% - 80% sheet.Range["A20"].EntireRow.NumberFormat = rateFormat; //Retention Tate% - 40% sheet.Range["A15"].EntireRow.NumberFormat = rateFormat; //Close rate% - 40% sheet.Range["A24"].EntireRow.NumberFormat = rateFormat; //Direct Costs% - 15% sheet.Range["A41"].EntireRow.NumberFormat = rateFormat; //Total Marketin Costs% - 14% sheet.Range["A43"].EntireRow.NumberFormat = rateFormat; //Sales Costs/Net Telesales revenue% - 24% sheet.Range["A46"].EntireRow.NumberFormat = rateFormat; //Sales Costs/Total Net revenue% - 21% sheet.Range["A47"].EntireRow.NumberFormat = rateFormat; //Cancellation Provision% - 14.0% sheet.Range["A54"].EntireRow.NumberFormat = rateFormat; //Bad debt Provision% - 7.3% sheet.Range["A56"].EntireRow.NumberFormat = rateFormat; //insert two columns before the first column sheet.Range["A1"].EntireColumn.Insert(); sheet.Range["A1"].EntireColumn.Interior.ColorIndex = -1; sheet.Range["A1"].EntireColumn.ColumnWidth = 20; sheet.Range["A1"].EntireColumn.Insert(); sheet.Range["A1"].EntireColumn.Interior.ColorIndex = -1; sheet.Range["A1"].EntireColumn.ColumnWidth = 15; //# of CR - Other Costs this.InsertTitleRow(sheet, "Other Costs", 48); //# of Sales Staff - Salse Costs this.InsertTitleRow(sheet, "Sales Costs", 44); //Total Marketing Costs - Martketing Costs this.InsertTitleRow(sheet, "Martketing Costs", 42); this.InsertBlankRow(sheet, "A41"); //Revenue(KFR rate) this.InsertTitleRow(sheet, "Revenue(KFR rate)", 38); //Revenue(Spot rate) this.InsertTitleRow(sheet, "Revenue(Spot rate)", 25); //Funnel KPI ExcelUtilies.DeleteRow(sheet, "A6"); this.InsertTitleRow(sheet, "Funnel KPI", 6); //Set sheet title sheet.Range["A7"].Value = sheet.Name; sheet.Range["A7"].Font.Bold = true; //Copy header above on Funnuel KPI sheet.Range["A3"].EntireRow.Cut(); sheet.Range["A7"].Insert(Excel.XlInsertShiftDirection.xlShiftDown); //Delete the title line added before ExcelUtilies.DeleteRow(sheet, "A2"); sheet.Range["A2"].EntireRow.Interior.ColorIndex = -1; sheet.Range["A3"].EntireRow.Interior.ColorIndex = -1; sheet.Range["A4"].EntireRow.RowHeight = sheet.Range["A5"].EntireRow.RowHeight * 2; sheet.Range["A5"].EntireRow.RowHeight = sheet.Range["A5"].EntireRow.RowHeight * 2; sheet.Range["A5"].Value = "Country"; sheet.Range["A5"].Font.Bold = true; sheet.Range["C5"].Value = "Funnel performance"; sheet.Range["C5"].Font.Bold = true; sheet.Range["C5"].Interior.ColorIndex = -1; sheet.Range["C5"].EntireColumn.ColumnWidth = 25; //Add Border line sheet.Range["B5"].EntireRow.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).Weight = 2; sheet.Range["B5"].EntireRow.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous; sheet.Range["B5"].EntireColumn.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).Weight = 2; sheet.Range["B5"].EntireColumn.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous; }