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 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 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);
}
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);
}
