public void WriteOutNumberOfEntriesPerOrtsTeil(IXLWorksheet workSheet, ZustaendigAgOrtsTeilOrt ZustaendigAgOrtsTeilOrtInstance, StatistikRootClass.DistributionCountTyp DistTyp, int StartLine) { switch (DistTyp) { case StatistikRootClass.DistributionCountTyp.AllTogether: { foreach (AGCounter agCounter in ZustaendigAgOrtsTeilOrtInstance.Children) { workSheet.Cell($"B{StartLine}").Value = agCounter.ArbeitsGruppeDaten.NameId; IXLRange AGNameRange = workSheet.Range($"C{StartLine}:D{StartLine}").Merge(); AGNameRange.Style.Alignment.WrapText = true; AGNameRange.Style.Alignment.SetVertical(XLAlignmentVerticalValues.Justify); AGNameRange.Value = agCounter.ArbeitsGruppeDaten.Beschreibung; StartLine++; StartLine++; foreach (OrtsTeilCounter ortsTeilCounter in agCounter.Children) { workSheet.Cell($"C{StartLine}").Value = ortsTeilCounter.OrtsTeilDaten.NameId; StartLine++; foreach (OrtsCounter ortsCounter in ortsTeilCounter.Children) { workSheet.Cell($"D{StartLine}").Value = ortsCounter.OrtDaten.Bezeichnung; workSheet.Cell($"E{StartLine}").SetDataType(XLCellValues.Number); workSheet.Cell($"F{StartLine}").SetDataType(XLCellValues.Number); workSheet.Cell($"E{StartLine}").Value = ortsCounter.Counter.NumberOfVorschlaege; workSheet.Cell($"F{StartLine}").Value = ortsCounter.Counter.NumberOfIdeen; StartLine++; } workSheet.Cell($"D{StartLine}").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right); workSheet.Cell($"D{StartLine}").Value = ortsTeilCounter.OrtsTeilDaten.NameId + " Summen"; workSheet.Cell($"E{StartLine}").SetDataType(XLCellValues.Number); workSheet.Cell($"F{StartLine}").SetDataType(XLCellValues.Number); workSheet.Cell($"E{StartLine}").Style.Fill.BackgroundColor = XLColor.LightGray; workSheet.Cell($"F{StartLine}").Style.Fill.BackgroundColor = XLColor.LightGray; workSheet.Cell($"E{StartLine}").Value = ortsTeilCounter.Counter.NumberOfVorschlaege; workSheet.Cell($"F{StartLine}").Value = ortsTeilCounter.Counter.NumberOfIdeen; StartLine++; StartLine++; } StartLine++; workSheet.Cell($"D{StartLine}").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right); workSheet.Cell($"D{StartLine}").Value = agCounter.ArbeitsGruppeDaten.NameId + " Summen"; workSheet.Cell($"E{StartLine}").SetDataType(XLCellValues.Number); workSheet.Cell($"F{StartLine}").SetDataType(XLCellValues.Number); workSheet.Cell($"E{StartLine}").Style.Fill.BackgroundColor = XLColor.LightGray; workSheet.Cell($"F{StartLine}").Style.Fill.BackgroundColor = XLColor.LightGray; workSheet.Cell($"E{StartLine}").Value = agCounter.Counter.NumberOfVorschlaege; workSheet.Cell($"F{StartLine}").Value = agCounter.Counter.NumberOfIdeen; StartLine++; StartLine++; } StartLine++; } break; case StatistikRootClass.DistributionCountTyp.PerAG: { StartLine++; foreach (AGCounter agCounter in ZustaendigAgOrtsTeilOrtInstance.Children) { workSheet.Cell($"B{StartLine}").Value = agCounter.ArbeitsGruppeDaten.NameId; IXLRange AGNameRange = workSheet.Range($"C{StartLine}:D{StartLine}").Merge(); AGNameRange.Style.Alignment.WrapText = true; AGNameRange.Style.Alignment.SetVertical(XLAlignmentVerticalValues.Justify); AGNameRange.Value = agCounter.ArbeitsGruppeDaten.Beschreibung; workSheet.Cell($"E{StartLine}").SetDataType(XLCellValues.Number); workSheet.Cell($"F{StartLine}").SetDataType(XLCellValues.Number); workSheet.Cell($"E{StartLine}").Style.Fill.BackgroundColor = XLColor.LightGray; workSheet.Cell($"F{StartLine}").Style.Fill.BackgroundColor = XLColor.LightGray; workSheet.Cell($"E{StartLine}").Value = agCounter.Counter.NumberOfProjekte; workSheet.Cell($"F{StartLine}").Value = agCounter.Counter.NumberOfIdeen; StartLine++; StartLine++; } StartLine++; } break; case StatistikRootClass.DistributionCountTyp.PerOrtsTeil: { foreach (AGCounter agCounter in ZustaendigAgOrtsTeilOrtInstance.Children) { StartLine++; foreach (OrtsTeilCounter ortsTeilCounter in agCounter.Children) { workSheet.Cell($"C{StartLine}").Value = ortsTeilCounter.OrtsTeilDaten.NameId; workSheet.Cell($"E{StartLine}").SetDataType(XLCellValues.Number); workSheet.Cell($"F{StartLine}").SetDataType(XLCellValues.Number); workSheet.Cell($"E{StartLine}").Style.Fill.BackgroundColor = XLColor.LightGray; workSheet.Cell($"F{StartLine}").Style.Fill.BackgroundColor = XLColor.LightGray; workSheet.Cell($"E{StartLine}").Value = ortsTeilCounter.Counter.NumberOfProjekte; workSheet.Cell($"F{StartLine}").Value = ortsTeilCounter.Counter.NumberOfIdeen; StartLine++; StartLine++; } } StartLine++; } break; } }
public List<string> ProjektCounterColumns(StatistikRootClass.DistributionCountTyp DistTyp) { switch (DistTyp) { case StatistikRootClass.DistributionCountTyp.AllTogether: return new List<string> { "AG", "OrtsTeil", "Ort", "Projekte", "Ideen" }; break; case StatistikRootClass.DistributionCountTyp.PerAG: return new List<string> { "AG", "", "", "Projekt", "Ideen" }; break; case StatistikRootClass.DistributionCountTyp.PerOrtsTeil: return new List<string> { "AG", "OrtsTeil", "", "Projekt", "Ideen" }; break; default: return new List<string> { "AG", "OrtsTeil", "Ort", "Projekt", "Ideen" }; break; } }
private int CreateXLSXHeadLines(IXLWorksheet workSheet, IXLCell StartTableCell, StatistikRootClass.StatistikDataSelectionTypes writeOutType) { workSheet.Column("A").Width = 2; int LineNumber = StartTableCell.WorksheetRow().RowNumber(); int StartColumnIndex = StartTableCell.WorksheetColumn().ColumnNumber(); // FullTable.Theme = XLTableTheme.TableStyleLight20; CreateGroupHeader(workSheet, "AG's, OrtsTeile, Orte", LineNumber, StartColumnIndex, 6); workSheet.Column(StartColumnIndex).Width = 3; workSheet.Column(StartColumnIndex + 1).Width = 3; workSheet.Column(StartColumnIndex + 2).Width = 3; int OldStartColumnIndex = StartColumnIndex; foreach (string LevelText in StandardCounter.AllHeadLines["LevelHeadLines"]) { if ((LevelText == "Gesamt") || (LevelText == "AddProcesses") || (LevelText == "Beschreibung") ) continue; CreateVerticalNumberHeader(workSheet, LevelText, LineNumber + 1, StartColumnIndex++); } workSheet.Column(OldStartColumnIndex + 3).Width = 20; workSheet.Column(StartColumnIndex).Width = 1; workSheet.Cell(LineNumber, StartColumnIndex++).Style.Fill.SetBackgroundColor(XLColor.White); CreateGroupHeader(workSheet, "WMU Punkte", LineNumber, StartColumnIndex, 3); // StartColumnIndex = 8; foreach (string LevelText in StandardCounter.AllHeadLines["WMUHeadLines"]) { CreateVerticalNumberHeader(workSheet, LevelText.Replace("W", "Weiblich").Replace("M", "Männlich").Replace("U", "Unbekannt"), LineNumber + 1, StartColumnIndex++); } workSheet.Column(StartColumnIndex).Width = 1; workSheet.Cell(LineNumber, StartColumnIndex++).Style.Fill.SetBackgroundColor(XLColor.White); CreateGroupHeader(workSheet, "Projekt Phasen", LineNumber, StartColumnIndex, 5); foreach (string LevelText in StandardCounter.AllHeadLines["ProjektPhasenHeadLines"]) { CreateVerticalNumberHeader(workSheet, LevelText, LineNumber + 1, StartColumnIndex++); } // StandardCounter.AllHeadLines IXLCell StartCell = workSheet.Cell(LineNumber, StartColumnIndex); int RunningColumn = StartCell.Address.ColumnNumber; int RunningRow = StartCell.Address.RowNumber; foreach (string writeOutSpecificHeadLine in StandardCounter.GetWriteOutSpecificHeadLines(writeOutType).Keys) { workSheet.Column(RunningColumn).Width = 1; workSheet.Cell(RunningRow, RunningColumn++).Style.Fill.SetBackgroundColor(XLColor.White); CreateGroupHeader(workSheet, writeOutSpecificHeadLine, LineNumber, RunningColumn, StandardCounter.GetWriteOutSpecificHeadLines(writeOutType)[writeOutSpecificHeadLine].Count); foreach (string ColumText in StandardCounter.GetWriteOutSpecificHeadLines(writeOutType)[writeOutSpecificHeadLine]) { CreateVerticalNumberHeader(workSheet, ColumText, RunningRow + 1, RunningColumn++); } } return 0; }
public int WriteOutNumberOfEntriesPerOrtsTeil(IXLWorksheet workSheet, StatistikRootClass.DistributionCountTyp DistTyp) { IXLRange headerRng = workSheet.Range("B2:F2").Merge(); headerRng.Style .Font.SetBold() .Fill.SetBackgroundColor(XLColor.AliceBlue) .Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center) .Alignment.WrapText = true; headerRng.Value = "Vorschläge / Ideen Anzahlen " + DistTyp.GetDescription(); switch (DistTyp) { case StatistikRootClass.DistributionCountTyp.AllTogether: { workSheet.Column("A").Width = 2; workSheet.Column("G").Width = 2; IXLRange CounterHeaderRange = workSheet.Range("E4:F4").Merge().SetDataType(XLCellValues.Text); CounterHeaderRange.Value = "Anzahlen"; CounterHeaderRange.Style .Font.SetBold() .Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center) .Alignment.WrapText = true; int ColumnNumber = 0; foreach (string Col in ProjektCounterColumns(DistTyp)) workSheet.Cell($"{ColumnNameTable[ColumnNumber++ + 1]}5").Value = Col; workSheet.Column("D").Width = 40; return 7; } break; case StatistikRootClass.DistributionCountTyp.PerAG: { workSheet.Column("A").Width = 2; workSheet.Column("G").Width = 2; IXLRange CounterHeaderRange = workSheet.Range("E4:F4").Merge().SetDataType(XLCellValues.Text); CounterHeaderRange.Value = "Anzahlen"; CounterHeaderRange.Style .Font.SetBold() .Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center) .Alignment.WrapText = true; int ColumnNumber = 0; foreach (string Col in ProjektCounterColumns(DistTyp)) workSheet.Cell($"{ColumnNameTable[ColumnNumber++ + 1]}5").Value = Col; workSheet.Column("D").Width = 40; return 7; } break; case StatistikRootClass.DistributionCountTyp.PerOrtsTeil: { workSheet.Column("A").Width = 2; workSheet.Column("G").Width = 2; IXLRange CounterHeaderRange = workSheet.Range("E4:F4").Merge().SetDataType(XLCellValues.Text); CounterHeaderRange.Value = "Anzahlen"; CounterHeaderRange.Style .Font.SetBold() .Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center) .Alignment.WrapText = true; int ColumnNumber = 0; foreach (string Col in ProjektCounterColumns(DistTyp)) workSheet.Cell($"{ColumnNameTable[ColumnNumber++ + 1]}5").Value = Col; workSheet.Column("D").Width = 40; return 7; } break; default: return 7; } }
private IXLCell CreateXLSXContentLines(ZustaendigAgOrtsTeilOrt ZustaendigAgOrtsTeilOrtInstance, IXLWorksheet workSheet, IXLCell StartCell, StatistikRootClass.StatistikDataSelectionTypes writeOutType, bool StandardContent = true, bool ProcessOrte = true) { int ActuallRow = StartCell.WorksheetRow().RowNumber() + 1; int FirstCellNumber = StartCell.WorksheetColumn().ColumnNumber(); IXLCell LastCell = null; foreach (AGCounter agCounter in ZustaendigAgOrtsTeilOrtInstance.Children) { if (StandardContent) { workSheet.Cell(ActuallRow, FirstCellNumber).Value = agCounter.ArbeitsGruppeDaten.NameId; workSheet.Cell(ActuallRow, FirstCellNumber + 1).Value = agCounter.ArbeitsGruppeDaten.Beschreibung; } ActuallRow++; foreach (OrtsTeilCounter ortsTeilCounter in agCounter.Children) { if (StandardContent) { workSheet.Cell(ActuallRow, FirstCellNumber + 1).Value = ortsTeilCounter.OrtsTeilDaten.NameId; } if (ProcessOrte) { ActuallRow++; if (StandardContent) { foreach (OrtsCounter ortsCounter in ortsTeilCounter.Children) { workSheet.Cell(ActuallRow, FirstCellNumber + 2).Value = ortsCounter.OrtDaten.Bezeichnung; LastCell = ShowStandardDataPerEntry(workSheet, ortsCounter.Counter, ActuallRow, 6); ActuallRow = LastCell.Address.RowNumber + 1; } if (ProcessOrte) { IXLRange OrtsTeilLine = workSheet.Range(ActuallRow, FirstCellNumber + 2, ActuallRow, FirstCellNumber + 3).Merge(); OrtsTeilLine.Style.Font.SetBold(); OrtsTeilLine.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right); OrtsTeilLine.Value = ortsTeilCounter.OrtsTeilDaten.NameId + " - Summe"; LastCell = ShowStandardDataPerEntry(workSheet, ortsTeilCounter.Counter, ActuallRow, 6); ActuallRow = LastCell.Address.RowNumber; } } else { foreach (OrtsCounter ortsCounter in ortsTeilCounter.Children) { LastCell = ShowDataPerEntry(workSheet, writeOutType, ortsCounter.Counter, ActuallRow, StartCell.Address.ColumnNumber); ActuallRow = LastCell.Address.RowNumber + 1; } if (ProcessOrte) { LastCell = ShowDataPerEntry(workSheet, writeOutType, ortsTeilCounter.Counter, ActuallRow, StartCell.Address.ColumnNumber); ActuallRow = LastCell.Address.RowNumber; } } ActuallRow++; } else { if (StandardContent) { LastCell = ShowStandardDataPerEntry(workSheet, ortsTeilCounter.Counter, ActuallRow, 6); } else { LastCell = ShowDataPerEntry(workSheet, writeOutType, ortsTeilCounter.Counter, ActuallRow, StartCell.Address.ColumnNumber); } ActuallRow = LastCell.Address.RowNumber + 1; } } } return LastCell; }
private IXLCell ShowDataPerEntry(IXLWorksheet workSheet, StatistikRootClass.StatistikDataSelectionTypes writeOutType, StandardCounter Counter, int StartRowIndex, int StartColumnIndex, bool showZeros = true) { int ActuallRow = StartRowIndex; int ColumnIndex = StartColumnIndex; foreach (string writeOutSpecificHeadLine in StandardCounter.GetWriteOutSpecificHeadLines(writeOutType).Keys) { workSheet.Column(ColumnIndex).Width = 1; workSheet.Cell(ActuallRow, ColumnIndex++).Style.Fill.SetBackgroundColor(XLColor.White); foreach (string columnText in StandardCounter.GetWriteOutSpecificHeadLines(writeOutType)[writeOutSpecificHeadLine]) { ColumnIndex = SetOneValue(workSheet, ActuallRow, ColumnIndex, Counter.GetWriteOutSpecificValues (writeOutType)[writeOutSpecificHeadLine][columnText], showZeros); } } return workSheet.Cell(ActuallRow, ColumnIndex); }
private void WriteOutZustaendigAGOrtsTeilOrt(ZustaendigAgOrtsTeilOrt ZustaendigAgOrtsTeilOrtInstance, StatistikRootClass.StatistikDataSelectionTypes writeOutType, IXLWorksheet workSheet, bool ProcessOrte) { IXLCell StartCell = workSheet.Cell(5, 2); IXLCell StartTableCell = workSheet.Cell(4, 2); IXLCell EndCell = CreateXLSXContentLines(ZustaendigAgOrtsTeilOrtInstance, workSheet, StartCell, writeOutType, true, ProcessOrte); CreateXLSXHeadLines(workSheet, StartTableCell, writeOutType); IXLCell StartXLSXContent = workSheet.Cell(4, EndCell.Address.ColumnNumber); StartCell = workSheet.Cell(5, EndCell.Address.ColumnNumber); EndCell = CreateXLSXContentLines(ZustaendigAgOrtsTeilOrtInstance, workSheet, StartCell, writeOutType, false, ProcessOrte); }
private void WriteOutZustaendigAGOrtsTeilOrt(ZustaendigAgOrtsTeilOrt ZustaendigAgOrtsTeilOrtInstance, StatistikRootClass.StatistikDataSelectionTypes writeOutType, string OutputFileName) { XLWorkbook workBook = new XLWorkbook(); IXLWorksheet workSheetOhneOrte = workBook.Worksheets.Add(writeOutType.ToString()); WriteOutZustaendigAGOrtsTeilOrt(ZustaendigAgOrtsTeilOrtInstance, writeOutType, workSheetOhneOrte, false); IXLWorksheet workSheetMitOrte = workBook.Worksheets.Add(writeOutType + "_inclOrte"); WriteOutZustaendigAGOrtsTeilOrt(ZustaendigAgOrtsTeilOrtInstance, writeOutType, workSheetMitOrte, true); workBook.SaveAs(new FileStream(OutputFileName, FileMode.Create, FileAccess.Write, FileShare.ReadWrite)); workBook.Dispose(); }
public static Dictionary<String, List<String>> GetWriteOutSpecificHeadLines (StatistikRootClass.StatistikDataSelectionTypes writeOutType) { Dictionary<String, List<String>> Result = new Dictionary<string, List<String>>(); switch (writeOutType) { case StatistikRootClass.StatistikDataSelectionTypes.All: Result[StatistikRootClass.StatistikDataSelectionTypes.OnlySchools.ToString()] = GetSchulenCodeNames(); Result[StatistikRootClass.StatistikDataSelectionTypes.OnlyResponsibilities.ToString()] = GetResposibilityNames(); Result[StatistikRootClass.StatistikDataSelectionTypes.OnlyTypes.ToString()] = GetProjektTypeNames(); break; case StatistikRootClass.StatistikDataSelectionTypes.OnlySchools: Result[StatistikRootClass.StatistikDataSelectionTypes.OnlySchools.ToString()] = GetSchulenCodeNames(); break; case StatistikRootClass.StatistikDataSelectionTypes.OnlyResponsibilities: Result[StatistikRootClass.StatistikDataSelectionTypes.OnlyResponsibilities.ToString()] = GetResposibilityNames(); break; case StatistikRootClass.StatistikDataSelectionTypes.OnlyTypes: Result[StatistikRootClass.StatistikDataSelectionTypes.OnlyTypes.ToString()] = GetProjektTypeNames(); break; } return Result; }
public String WriteCSVData(StatistikRootClass.StatistikDataSelectionTypes writeOutType, String Level0, String Level1 = "", String Level2 = "", String Level3 = "", String Level4 = "", String Level5 = "", String Level6 = "") { StatistikDataSelection = writeOutType; List<String> ColumnContentElements = new List<string>(); ColumnContentElements.Add("\"" + Level0 + "\""); ColumnContentElements.Add("\"" + Level1 + "\""); ColumnContentElements.Add("\"" + Level2 + "\""); ColumnContentElements.Add("\"" + Level3 + "\""); ColumnContentElements.Add("\"" + Level4 + "\""); ColumnContentElements.Add("\"" + Level5 + "\""); ColumnContentElements.Add("\"" + Level6 + "\""); ColumnContentElements.Add(NumberOfChildren.ToString()); ColumnContentElements.Add(NumberOfAddProcesses.ToString()); ColumnContentElements.Add(NumberOfKlassen.ToString()); ColumnContentElements.Add(SummeW.ToString()); ColumnContentElements.Add(SummeM.ToString()); ColumnContentElements.Add(SummeU.ToString()); ColumnContentElements.Add(NumberOfIdeen.ToString()); ColumnContentElements.Add(NumberOfVorschlaege.ToString()); ColumnContentElements.Add(NumberOfPlanungen.ToString()); ColumnContentElements.Add(NumberOfProjekte.ToString()); ColumnContentElements.Add(NumberOfRealisierungen.ToString()); if ((writeOutType == StatistikRootClass.StatistikDataSelectionTypes.All) || (writeOutType == StatistikRootClass.StatistikDataSelectionTypes.OnlyResponsibilities)) foreach (Zustaendigkeit Responsibility in Data.DbServer3.WordUp.Zustaendigkeiten.OrderBy(ord => ord.NameId)) { if (!Zustaendigkeiten.ContainsKey(Responsibility)) Zustaendigkeiten[Responsibility] = 0; ColumnContentElements.Add(Zustaendigkeiten[Responsibility].ToString()); } if ((writeOutType == StatistikRootClass.StatistikDataSelectionTypes.All) || (writeOutType == StatistikRootClass.StatistikDataSelectionTypes.OnlySchools)) foreach (Organisation Schule in Data.DbServer3.WordUp.Organisationen.AktuelleSchulen) { if (!InvolvedSchulen.ContainsKey(Schule)) InvolvedSchulen[Schule] = 0; ColumnContentElements.Add(InvolvedSchulen[Schule].ToString()); } if ((writeOutType == StatistikRootClass.StatistikDataSelectionTypes.All) || (writeOutType == StatistikRootClass.StatistikDataSelectionTypes.OnlyTypes)) foreach (Typ ProjektType in Data.DbServer3.WordUp.Typen.AllTypForProjekte.OrderBy(ord => ord.TypNameId)) { if (!ProjektTypen.ContainsKey(ProjektType)) ProjektTypen[ProjektType] = 0; ColumnContentElements.Add(ProjektTypen[ProjektType].ToString()); } return String.Join(";", ColumnContentElements); }
public String WriteCSVData(StatistikRootClass.StatistikDataSelectionTypes writeOutType) { StatistikDataSelection = writeOutType; List<String> standards = new List<string>(); standards.AddRange(GetLevelHeadLineStrings()); standards.AddRange(GetWMUHeadLineStrings()); standards.AddRange(GetProjektPhasenHeadLineStrings()); switch (StatistikDataSelection) { case StatistikRootClass.StatistikDataSelectionTypes.All: standards.AddRange(GetResposibilityNames()); standards.AddRange(GetSchulenCodeNames()); standards.AddRange(GetProjektTypeNames()); break; case StatistikRootClass.StatistikDataSelectionTypes.OnlySchools: standards.AddRange(GetSchulenCodeNames()); break; case StatistikRootClass.StatistikDataSelectionTypes.OnlyResponsibilities: standards.AddRange(GetResposibilityNames()); break; case StatistikRootClass.StatistikDataSelectionTypes.OnlyTypes: standards.AddRange(GetProjektTypeNames()); break; } return String.Join(";", standards); }