private void AddRawSheetConfig(RawSheetConfig rawSheetConfig)
{
rawSheetConfigs.Add(rawSheetConfig);
}
private void FillRawSheetConfigs(XDocument doc, XNamespace ns)
{
var sheetElements = doc.Root.GetChild("RawSheets", ns).GetDescendants("Sheet", ns);
Func<XElement, RawSheetDataColumnStyle> GetColumnStyle = (colElement =>
{
RawSheetDataColumnStyle colStyle = null;
var styleElement = colElement.GetChild("Style", ns);
if (styleElement == null)
return colStyle;
else
{
colStyle = new RawSheetDataColumnStyle
{
HAlign = styleElement.GetEnumValue<TextAlignmentType>("HAlign", TextAlignmentType.General),
VAlign = styleElement.GetEnumValue<TextAlignmentType>("VAlign", TextAlignmentType.General),
CustomFormat = styleElement.GetStringValue("CustomFormat", null)
};
return colStyle;
}
});
foreach (var e in sheetElements)
{
var rawSheetConfig = new RawSheetConfig()
{
Name = e.GetStringValue("Name"),
TableName = e.GetStringValue("TableName"),
RawSheetIndex = e.GetValue<Int32>("RawSheetIndex"),
FileType = e.GetEnumValue<RawSheetFileType>("FileType"),
TabColor = e.GetStringValue("TabColor", string.Empty),
KPISheetIndex = e.GetNullableValue<Int32>("KPISheetIndex"),
FilterScript = e.GetStringValue("FilterScript", null),
FilterMethod = e.GetStringValue("FilterMethod", null)
};
var rawDataElement = e.GetChild("RawData", ns);
if (rawDataElement != null)
{
rawSheetConfig.GetStartRow = rawDataElement.GetValue<Int32>("GetStartRow");
rawSheetConfig.SetStartRow = rawDataElement.GetNullableValue<Int32>("SetStartRow");
rawSheetConfig.CheckEndCell = rawDataElement.GetStringValue("CheckEndCell", null);
rawSheetConfig.CheckEndValue = rawDataElement.GetStringValue("CheckEndValue", string.Empty);
rawSheetConfig.NullSkipColumn = rawDataElement.GetStringValue("NullSkipColumn", null);
var colElements = rawDataElement.GetDescendants("Column", ns);
foreach (XElement colElement in colElements)
{
rawSheetConfig.DataColumns.Add(new RawSheetDataColumn
{
Name = colElement.GetStringValue("Name"),
GetCell = colElement.GetStringValue("GetCell"),
SetCell = colElement.GetStringValue("SetCell", string.Empty),
DataType = colElement.GetEnumValue<DataType>("DataType", DataType.String),
ContainFormula = colElement.GetValue<Boolean>("ContainFormula", false),
Style = GetColumnStyle(colElement)
});
}
}
var titleDataElement = e.GetChild("TitleData", ns);
if (titleDataElement != null)
{
var colElements = titleDataElement.GetDescendants("Column", ns);
foreach (XElement colElement in colElements)
{
rawSheetConfig.TitleDataColumns.Add(new RawSheetDataColumn
{
Name = colElement.GetStringValue("Name"),
GetCell = colElement.GetStringValue("GetCell"),
SetCell = colElement.GetStringValue("SetCell"),
DataType = colElement.GetEnumValue<DataType>("DataType", DataType.String),
Style = GetColumnStyle(colElement)
});
}
}
AddRawSheetConfig(rawSheetConfig);
}
}
private void SetOracleIDCSumData(Worksheet sheet, RawSheetConfig sheetConfig, int lastRow)
{
Style style1 = sheet.Workbook.CreateStyle();
style1.Font.IsBold = true;
style1.Custom = "_ * #,##0_ ;_ * -#,##0_ ;_ * \" - \"??_ ;_ @_ ";
new List<string> { "K", "L", "M", "N", "O" }
.ForEach(c =>
{
sheet.Cells[string.Concat(c, lastRow + 1)].Formula = string.Format("=SUM({0}{1}:{0}{2})", c, sheetConfig.SetStartRow, lastRow);
sheet.Cells[string.Concat(c, lastRow + 1)].SetStyle(style1);
});
Style baseStyle = sheet.Workbook.CreateStyle();
baseStyle.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
baseStyle.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
baseStyle.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
baseStyle.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
baseStyle.Borders.DiagonalColor = System.Drawing.Color.Black;
Style style2 = sheet.Workbook.CreateStyle();
style2.Copy(baseStyle);
style2.Font.IsBold = true;
style2.Pattern = BackgroundType.Solid;
style2.ForegroundColor = System.Drawing.ColorTranslator.FromHtml("#FFFF00");
sheet.Cells[string.Concat("I", lastRow + 10)].PutValue("IDC from Supplier invoice");
sheet.Cells[string.Concat("I", lastRow + 10)].SetStyle(style2);
Style style3 = sheet.Workbook.CreateStyle();
style3.Copy(style2);
style3.Custom = "_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * \" - \"??_ ;_ @_ ";
DataTable oracleHeadCountTable = KPIReportData.RawDatas.Tables["OracleHeadCount"];
sheet.Cells[string.Concat("J", lastRow + 10)].PutValue(Convert.ToDouble(oracleHeadCountTable.Rows[0][1]));
sheet.Cells[string.Concat("J", lastRow + 10)].SetStyle(style3);
Style style4 = sheet.Workbook.CreateStyle();
style4.Copy(baseStyle);
Style style5 = sheet.Workbook.CreateStyle();
style5.Copy(baseStyle);
style5.Custom = "_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * \" - \"??_ ;_ @_ ";
sheet.Cells[string.Concat("I", lastRow + 11)].SetStyle(baseStyle);
sheet.Cells[string.Concat("J", lastRow + 11)].SetStyle(baseStyle);
sheet.Cells[string.Concat("I", lastRow + 12)].PutValue("Headcount of ORACLE_EBS");
sheet.Cells[string.Concat("I", lastRow + 12)].SetStyle(style4);
sheet.Cells[string.Concat("J", lastRow + 12)].PutValue(Convert.ToInt32(oracleHeadCountTable.Rows[1][1]));
sheet.Cells[string.Concat("J", lastRow + 12)].SetStyle(style4);
sheet.Cells[string.Concat("I", lastRow + 13)].PutValue("Headcount of ORACLE_OTH_HCM");
sheet.Cells[string.Concat("I", lastRow + 13)].SetStyle(style4);
sheet.Cells[string.Concat("J", lastRow + 13)].PutValue(Convert.ToInt32(oracleHeadCountTable.Rows[2][1]));
sheet.Cells[string.Concat("J", lastRow + 13)].SetStyle(style4);
sheet.Cells[string.Concat("I", lastRow + 14)].PutValue("Headcount of ORACLE_OTH_HYPERION");
sheet.Cells[string.Concat("I", lastRow + 14)].SetStyle(style4);
sheet.Cells[string.Concat("J", lastRow + 14)].PutValue(Convert.ToInt32(oracleHeadCountTable.Rows[3][1]));
sheet.Cells[string.Concat("J", lastRow + 14)].SetStyle(style4);
sheet.Cells[string.Concat("I", lastRow + 15)].PutValue("Headcount of ORACLE_OTH_SIEBEL");
sheet.Cells[string.Concat("I", lastRow + 15)].SetStyle(style4);
sheet.Cells[string.Concat("J", lastRow + 15)].PutValue(Convert.ToInt32(oracleHeadCountTable.Rows[4][1]));
sheet.Cells[string.Concat("J", lastRow + 15)].SetStyle(style4);
sheet.Cells[string.Concat("I", lastRow + 16)].PutValue("Headcount of ORACLE_OTH_BI");
sheet.Cells[string.Concat("I", lastRow + 16)].SetStyle(style4);
sheet.Cells[string.Concat("J", lastRow + 16)].PutValue(Convert.ToInt32(oracleHeadCountTable.Rows[5][1]));
sheet.Cells[string.Concat("J", lastRow + 16)].SetStyle(style4);
sheet.Cells[string.Concat("I", lastRow + 17)].PutValue("IDC to EBS");
sheet.Cells[string.Concat("I", lastRow + 17)].SetStyle(style4);
sheet.Cells[string.Concat("J", lastRow + 17)].Formula = string.Format("=J{0}*J{1}/SUM(J{2}:J{3})", (lastRow + 10), (lastRow + 12), (lastRow + 12), (lastRow + 16));
sheet.Cells[string.Concat("J", lastRow + 17)].SetStyle(style5);
sheet.Cells[string.Concat("I", lastRow + 18)].PutValue("IDC to HCM");
sheet.Cells[string.Concat("I", lastRow + 18)].SetStyle(style4);
sheet.Cells[string.Concat("J", lastRow + 18)].Formula = string.Format("=J{0}*J{1}/SUM(J{2}:J{3})", (lastRow + 10), (lastRow + 13), (lastRow + 12), (lastRow + 16));
sheet.Cells[string.Concat("J", lastRow + 18)].SetStyle(style5);
sheet.Cells[string.Concat("I", lastRow + 19)].PutValue("IDC to Hyperion");
sheet.Cells[string.Concat("I", lastRow + 19)].SetStyle(style4);
sheet.Cells[string.Concat("J", lastRow + 19)].Formula = string.Format("=J{0}*J{1}/SUM(J{2}:J{3})", (lastRow + 10), (lastRow + 14), (lastRow + 12), (lastRow + 16));
sheet.Cells[string.Concat("J", lastRow + 19)].SetStyle(style5);
sheet.Cells[string.Concat("I", lastRow + 20)].PutValue("IDC to SIEBEL");
sheet.Cells[string.Concat("I", lastRow + 20)].SetStyle(style4);
sheet.Cells[string.Concat("J", lastRow + 20)].Formula = string.Format("=J{0}*J{1}/SUM(J{2}:J{3})", (lastRow + 10), (lastRow + 15), (lastRow + 12), (lastRow + 16));
sheet.Cells[string.Concat("J", lastRow + 20)].SetStyle(style5);
sheet.Cells[string.Concat("I", lastRow + 21)].PutValue("IDC to BI");
sheet.Cells[string.Concat("I", lastRow + 21)].SetStyle(style4);
sheet.Cells[string.Concat("J", lastRow + 21)].Formula = string.Format("=J{0}*J{1}/SUM(J{2}:J{3})", (lastRow + 10), (lastRow + 16), (lastRow + 12), (lastRow + 16));
sheet.Cells[string.Concat("J", lastRow + 21)].SetStyle(style5);
}
private void SetProjectContributionSumData(Worksheet sheet, RawSheetConfig sheetConfig, int lastRow)
{
Style style1 = sheet.Workbook.CreateStyle();
style1.Font.IsBold = true;
style1.Custom = "_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * \" - \"??_ ;_ @_ ";
Style style2 = sheet.Workbook.CreateStyle();
style2.Font.IsBold = true;
style2.Custom = "0.00%";
new List<string> { "H", "I", "J", "K", "L", "M", "N", "P" }
.ForEach(c =>
{
sheet.Cells[string.Concat(c, lastRow + 1)].Formula = string.Format("=SUM({0}{1}:{0}{2})", c, sheetConfig.SetStartRow, lastRow);
sheet.Cells[string.Concat(c, lastRow + 1)].SetStyle(style1);
});
sheet.Cells[string.Concat("O", lastRow + 1)].Formula = string.Format("=N{0}/M{0}", lastRow + 1);
sheet.Cells[string.Concat("O", lastRow + 1)].SetStyle(style2);
sheet.Cells[string.Concat("Q", lastRow + 1)].Formula = string.Format("=P{0}/M{0}", lastRow + 1);
sheet.Cells[string.Concat("Q", lastRow + 1)].SetStyle(style2);
}
private void SetIDCSumData(Worksheet sheet, RawSheetConfig sheetConfig, int lastRow)
{
Style style1 = sheet.Workbook.CreateStyle();
style1.Font.IsBold = true;
style1.Font.Color = System.Drawing.Color.White;
style1.Pattern = BackgroundType.Solid;
style1.ForegroundColor = System.Drawing.ColorTranslator.FromHtml("#003366");
style1.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style1.Borders.DiagonalColor = System.Drawing.Color.Black;
sheet.Cells[string.Concat("G", lastRow + 1)].PutValue("Total in RMB");
sheet.Cells[string.Concat("G", lastRow + 1)].SetStyle(style1);
Style style2 = sheet.Workbook.CreateStyle();
style2.Copy(style1);
style2.Custom = "_ * #,##0_ ;_ * -#,##0_ ;_ * \" - \"??_ ;_ @_ ";
new List<string> { "H", "I", "J" }
.ForEach(c =>
{
sheet.Cells[string.Concat(c, lastRow + 1)].Formula = string.Format("=SUM({0}{1}:{0}{2})", c, sheetConfig.SetStartRow, lastRow);
sheet.Cells[string.Concat(c, lastRow + 1)].SetStyle(style2);
});
}
private void SetCashCollectionSumData(Worksheet sheet, RawSheetConfig sheetConfig, int lastRow)
{
Style style1 = sheet.Workbook.CreateStyle();
style1.Font.Color = System.Drawing.Color.White;
style1.Font.IsBold = true;
style1.Pattern = BackgroundType.Solid;
style1.ForegroundColor = System.Drawing.ColorTranslator.FromHtml("#003366");
style1.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style1.Borders.DiagonalColor = System.Drawing.Color.Black;
sheet.Cells[string.Concat("K", lastRow + 1)].PutValue("Monthly Total in RMB:");
sheet.Cells[string.Concat("K", lastRow + 1)].SetStyle(style1);
Style style2 = workbook.CreateStyle();
style2.Copy(style1);
style2.Custom = "_ * #,##0_ ;_ * -#,##0_ ;_ * \"-\"??_ ;_ @_ ";
sheet.Cells[string.Concat("M", lastRow + 1)].Formula = string.Format("=SUM(M{0}:M{1})", sheetConfig.SetStartRow, lastRow);
sheet.Cells[string.Concat("M", lastRow + 1)].SetStyle(style2);
}
private void SetBookingRawSumData(Worksheet sheet, RawSheetConfig sheetConfig, int lastRow, int reportYear, int reportMonth)
{
Style style1 = sheet.Workbook.CreateStyle();
style1.VerticalAlignment = TextAlignmentType.Center;
style1.HorizontalAlignment = TextAlignmentType.Center;
style1.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style1.Borders.DiagonalColor = System.Drawing.Color.Black;
Range range = sheet.Cells.CreateRange(string.Concat("J", lastRow + 2), string.Concat("K", lastRow + 2));
range.Merge();
DateTime date = new DateTime(reportYear, reportMonth, 1);
range.Value = string.Format("Total Y{0} Booking in CNY", date.ToString("yy"));
range.SetStyle(style1);
sheet.Cells[string.Concat("L", lastRow + 2)].Formula = string.Format("=SUM(L{0}:L{1})", sheetConfig.SetStartRow, lastRow);
Style style2 = sheet.Workbook.CreateStyle();
style2.Copy(style1);
style2.Custom = "_(* #,##0_);_(* (#,##0);_(* \"-\"??_);_(@_)";
sheet.Cells[string.Concat("L", lastRow + 2)].SetStyle(style2);
range = sheet.Cells.CreateRange(string.Concat("J", lastRow + 4), string.Concat("K", lastRow + 4));
range.Merge();
range.Value = string.Format("Total Y{0} Booking in KEUR", date.ToString("yy"));
range.SetStyle(style1);
sheet.Cells[string.Concat("L", lastRow + 4)].Formula = string.Format("=L{0}*0.1185/1000", lastRow + 2);
Style style3 = sheet.Workbook.CreateStyle();
style3.Copy(style2);
style3.Pattern = BackgroundType.Solid;
style3.ForegroundColor = System.Drawing.ColorTranslator.FromHtml("#FFCC00");
sheet.Cells[string.Concat("L", lastRow + 4)].SetStyle(style3);
}
public void SetRawData(RawSheetConfig sheetConfig, DataTable rawData, DataTable rawFormulaData, DataTable rawTitleData, int reportYear, int reportMonth)
{
try
{
FinaChanLogger.Info("Fill raw data {0} start", sheetConfig.Name);
Worksheet worksheet = workbook.Worksheets[sheetConfig.KPISheetIndex.Value];
#region Set Data
var columns = sheetConfig.DataColumns;
int rowIndex = sheetConfig.SetStartRow.Value;
int dataRowIndex = 0;
string cellName;
foreach (DataRow row in rawData.Rows)
{
foreach (RawSheetDataColumn column in columns)
{
cellName = string.Concat(column.SetCell, rowIndex);
if (column.ContainFormula &&
rawFormulaData != null &&
rawFormulaData.Rows[dataRowIndex][column.Name] != DBNull.Value)
{
SetCellFormula(worksheet, cellName, (string)rawFormulaData.Rows[dataRowIndex][column.Name]);
}
else
{
SetCellValue(worksheet, cellName, row[column.Name]);
}
var style = worksheet.Cells[cellName].GetStyle();
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].Color = System.Drawing.Color.Black;
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.TopBorder].Color = System.Drawing.Color.Black;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.LeftBorder].Color = System.Drawing.Color.Black;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].Color = System.Drawing.Color.Black;
if (column.Style != null)
{
if (column.Style.HAlign != TextAlignmentType.General)
style.HorizontalAlignment = column.Style.HAlign;
if (column.Style.VAlign != TextAlignmentType.General)
style.VerticalAlignment = column.Style.VAlign;
if (!string.IsNullOrEmpty(column.Style.CustomFormat))
style.Custom = column.Style.CustomFormat;
}
worksheet.Cells[cellName].SetStyle(style);
}
rowIndex++;
dataRowIndex++;
}
#endregion
#region Set Title Datas
var titleColumns = sheetConfig.TitleDataColumns;
foreach (DataRow row in rawTitleData.Rows)
{
foreach (RawSheetDataColumn column in titleColumns)
{
worksheet.Cells[column.SetCell].PutValue(row[column.Name]);
var style = worksheet.Cells[column.SetCell].GetStyle();
if (column.Style != null)
{
if (column.Style.HAlign != TextAlignmentType.General)
style.HorizontalAlignment = column.Style.HAlign;
if (column.Style.VAlign != TextAlignmentType.General)
style.VerticalAlignment = column.Style.VAlign;
if (!string.IsNullOrEmpty(column.Style.CustomFormat))
style.Custom = column.Style.CustomFormat;
}
worksheet.Cells[column.SetCell].SetStyle(style);
}
}
#endregion
if (sheetConfig.KPISheetIndex == 16)
{
SetBookingRawSumData(worksheet, sheetConfig, rowIndex - 1, reportYear, reportMonth);
}
else if (sheetConfig.KPISheetIndex == 23)
{
SetCashCollectionSumData(worksheet, sheetConfig, rowIndex - 1);
}
else if (sheetConfig.KPISheetIndex == 19)
{
SetBDSumData(worksheet, sheetConfig, rowIndex - 1);
}
else if (sheetConfig.KPISheetIndex == 20 || sheetConfig.KPISheetIndex == 21)
{
SetIDCSumData(worksheet, sheetConfig, rowIndex - 1);
}
else if (sheetConfig.KPISheetIndex == 22)
{
SetOracleIDCSumData(worksheet, sheetConfig, rowIndex - 1);
}
else if (sheetConfig.KPISheetIndex == 17 || sheetConfig.KPISheetIndex == 18)
{
SetProjectContributionSumData(worksheet, sheetConfig, rowIndex - 1);
}
}
catch (System.Exception ex)
{
FinaChanLogger.Info("Occured error when fill raw data {0}, caused by: {1}, details: {2}", sheetConfig.Name, ex.Message, ex.StackTrace);
throw ex;
}
finally
{
FinaChanLogger.Info("Fill raw data {0} end", sheetConfig.Name);
}
}
public void GetRawSheetData(RawSheetConfig sheetConfig, DataTable rawSheetData, DataTable rawFormulaTable, DataTable rawTitleData, AsmHelper filterScript)
{
try
{
Worksheet sheet = workbook.Worksheets[sheetConfig.RawSheetIndex];
if (sheet == null)
return;
#region Read Data
int rowIndex = sheetConfig.GetStartRow;
var columns = sheetConfig.DataColumns;
var nullSkipColumnName = sheetConfig.NullSkipColumn;
string checkEndCellName;
string checkEndValue;
RawSheetDataColumn nullSkipColumn;
string nullSkipCellName;
string nullSkipCellValue;
string cellName;
object cellValue;
while (true)
{
checkEndCellName = string.Concat(sheetConfig.CheckEndCell, rowIndex);
checkEndValue = GetCellStringValue(sheet, checkEndCellName);
if (checkEndValue.Trim().ToUpper() == sheetConfig.CheckEndValue.Trim().ToUpper())
break;
if (!string.IsNullOrEmpty(nullSkipColumnName))
{
nullSkipColumn = columns.Single(c => c.Name == nullSkipColumnName);
if (nullSkipColumn != null)
{
nullSkipCellName = string.Concat(nullSkipColumn.GetCell, rowIndex);
nullSkipCellValue = GetCellStringValue(sheet, nullSkipCellName);
if (string.IsNullOrEmpty(nullSkipCellValue.Trim()))
{
rowIndex++;
continue;
}
}
}
DataRow dr = rawSheetData.NewRow();
DataRow formulaDR = null;
if (rawFormulaTable != null)
{
formulaDR = rawFormulaTable.NewRow();
}
foreach (var column in columns)
{
cellName = string.Concat(column.GetCell, rowIndex);
cellValue = GetCellValue(sheet, column, cellName);
dr[column.Name] = cellValue;
if (column.ContainFormula && formulaDR != null)
{
formulaDR[column.Name] = GetCellFormula(sheet, cellName);
}
}
if (filterScript != null)
{
var filterResult = (bool)filterScript.Invoke(sheetConfig.FilterMethod, new object[] { dr });
if (true == filterResult)
{
rawSheetData.Rows.Add(dr);
if (rawFormulaTable != null && formulaDR != null)
{
rawFormulaTable.Rows.Add(formulaDR);
}
}
}
else
{
rawSheetData.Rows.Add(dr);
if (rawFormulaTable != null && formulaDR != null)
{
rawFormulaTable.Rows.Add(formulaDR);
}
}
rowIndex++;
}
#endregion
#region Read Title Data
if (rawTitleData != null)
{
var titleColumns = sheetConfig.TitleDataColumns;
DataRow dr = rawTitleData.NewRow();
foreach (var column in titleColumns)
{
dr[column.Name] = GetCellValue(sheet, column, column.GetCell);
}
rawTitleData.Rows.Add(dr);
}
#endregion
}
catch (System.Exception ex)
{
FinaChanLogger.Info("Occured error when load raw data of {0} worksheet, caused by: {1}, details: {2}", sheetConfig.Name, ex.Message, ex.StackTrace);
throw ex;
}
}
