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