protected bool IsSpecialRangeCell(IXLCell cell) { var cellRow = cell.WorksheetRow().RowNumber(); var cellClmn = cell.WorksheetColumn().ColumnNumber(); return(cellRow == RangeOptionsRow?.LastAddress.RowNumber && cellClmn == RangeOptionsRow.FirstAddress.ColumnNumber); }
public IList <House> GetHouses(string path) { XLWorkbook wb = new XLWorkbook(path); IXLWorksheet sheet = wb.Worksheets.First(); IXLRange RangeSheet = sheet.RangeUsed(); int rightBorder = RangeSheet.LastColumn().ColumnNumber(); //установим правую границу данных int downBorder = RangeSheet.LastRow().RowNumber(); //установим нижнюю границу данных List <IXLCell> Xlhouses = sheet.Cells().Where(c => c.GetValue <string>().Contains("Дом")).ToList(); //получим ячейки, в которых номера домов List <House> houses = new List <House>(); foreach (var Xlhouse in Xlhouses) { House house = new House(); house.Name = Xlhouse.GetValue <string>(); house.Flats = new List <Flat>(); IXLCell cell = Xlhouse.CellBelow(); while (!(cell.GetValue <string>() == "" && cell.CellBelow().GetValue <string>() == "" && cell.CellRight().GetValue <string>() == "" && cell.CellRight().CellBelow().GetValue <string>() == "" ) && cell.WorksheetColumn().ColumnNumber() <= rightBorder ) //рассматриваем, есть ли значение в ячейке под названием дома, а также в ближайших от нее соседей { IXLCell cellBellowHouse = cell; while (!(cell.GetValue <string>() == "" && cell.CellBelow().GetValue <string>() == "" ) && cell.WorksheetRow().RowNumber() <= downBorder ) //смотрим есть ли в текущей ячейке значение или той, которая находится снизу { if (cell.GetValue <string>().Contains("№")) { Flat flat = new Flat(); flat.Number = cell.GetValue <string>().Substring(1); flat.Price = cell.CellBelow().GetValue <string>(); house.Flats.Add(flat); } cell = cell.CellBelow(); } cell = cellBellowHouse.CellRight(); //после того, как закончили с колонкой, переходим в следующую колонку //самую верхнюю под номером дома } houses.Add(house); } return(houses); //throw new NotImplementedException(); }
/// <summary> /// ''' TODO AGGIUNGERE PARAMETRO ROUNDING OPPURE CELLFORMAT /// ''' </summary> /// ''' <param name="sheetName"></param> /// ''' <param name="labelCell"></param> /// ''' <param name="labelCellValue"></param> /// ''' <param name="excludeHeaders"></param> /// ''' <returns></returns> public ExcelHelper AddRowTotal(string sheetName, string labelCell = "A", string labelCellValue = "TOT.", bool excludeHeaders = true) { IXLRange range = this.GetRangeUsed(sheetName); if (range == null) { //SD.Log("range not found in " + sheetName, SD.LogLevel.Error); return(this); } int rowsNumber = range.RowCount(); IXLCell lastCellUsed = range.LastCellUsed(); IXLColumn lastColUsed = lastCellUsed.WorksheetColumn(); IXLRow lastRowUsed = lastCellUsed.WorksheetRow(); string lastColLetter = lastColUsed.ColumnLetter(); int lastRowNumber = lastRowUsed.RowNumber(); IXLRows rows = lastRowUsed.InsertRowsBelow(1); //IXLRow newRow = rows.Last(); var ws = this._workbook.Worksheets.Worksheet(sheetName); IXLRow newRow = ws.LastRowUsed().RowBelow(); if (labelCell.Trim() != "") { newRow.Cell(labelCell).Value = labelCellValue; newRow.Cell(labelCell).Style.Font.Bold = true; } var firstTotalCellAddress = newRow.FirstCell().CellRight().Address; var lastTotalCellAddress = newRow.Cell(lastColLetter).Address; IXLRange rangeTotal = this.GetRangeUsed(sheetName, firstTotalCellAddress, lastTotalCellAddress); //int i = rangeTotal.Cells().Count() + 1; int i = rangeTotal.ColumnCount() + 1; int firstDataRowIndex = 0; // escludo la riga delle intestazioni if (excludeHeaders) { firstDataRowIndex = 2; } for (int k = 1; k <= i; k++) { XLDataType colDataType = newRow.Cell(k).CellAbove(1).DataType; if (colDataType == XLDataType.Number) { string colLetter = newRow.Cell(k).Address.ColumnLetter; string formula = "=SUM(" + colLetter + firstDataRowIndex.ToString() + ":" + colLetter + rowsNumber.ToString() + ")"; this.AddFormula(sheetName, newRow.Cell(k).Address, formula); } } newRow.AsRange().RangeUsed().Style.Border.TopBorder = XLBorderStyleValues.Thick; return(this); }
/// <summary> /// check the all horizontal cells with chkstrs /// </summary> /// <param name="cells"></param> /// <returns></returns> public static bool CheckCellHorzStrings(IXLCell cell, StringComparison comparison, params string[] chkstrs) { var row = cell.WorksheetRow(); var colno = cell.WorksheetColumn().ColumnNumber(); for (int i = 0; i < chkstrs.Length; i++) { var celstr = DbUtil.ToString(Cell(row, colno + i), ""); if (chkstrs[i].Equals(celstr, comparison) == false) { return(false); } } return(true); }
/// <summary> /// 獲取cell所在列的第一個cell的值,和列名進行match and fill JobRequestInfo的屬性值 /// </summary> /// <param name="cell"></param> /// <param name="job"></param> private void FillJobRequestInfo(IXLCell cell, ref JobRequest job) { try { switch (cell.WorksheetColumn().FirstCell().GetValue <string>().Trim()) { case "ContactPerson": job.ContactPerson = cell.GetValue <string>(); break; case "Location": job.Location = cell.GetValue <string>(); break; case "Company": job.Company = cell.GetValue <string>(); break; case "RequestType": job.RequestType = cell.GetValue <string>(); break; case "Symptom": job.Symptom = cell.GetValue <string>(); break; case "ScheduleTime": job.ScheduleTime = cell.GetValue <string>(); break; case "ServeTime1": job.ServeTime1 = cell.GetValue <string>(); break; case "ServeTime2": job.ServeTime2 = cell.GetValue <string>(); break; case "ServiceDescription": job.ServiceDescription = cell.GetValue <string>(); break; } } catch (Exception) { throw; } }
private void MatchCallInfoListName(IXLCell cell, ref CallInfoList listInfo, ref CallInfoSymptom symptom, ref CallInfoTime time) { try { switch (cell.WorksheetColumn().FirstCell().GetString().Trim().ToLower()) { case "contactperson": listInfo.ContactPerson.Add(HttpUtility.UrlEncode(cell.GetString())); break; case "location": listInfo.Location.Add(HttpUtility.UrlEncode(cell.GetString())); break; case "company": listInfo.Company.Add(HttpUtility.UrlEncode(cell.GetString())); break; case "requesttype": symptom.RequestType = HttpUtility.UrlEncode(cell.GetString()); break; case "symptom": symptom.Symptom = HttpUtility.UrlEncode(cell.GetString()); break; case "scheduletime": time.ScheduleTime = HttpUtility.UrlEncode(cell.GetString()); break; case "servetime1": time.ServeTime1 = HttpUtility.UrlEncode(cell.GetString()); break; case "servetime2": time.ServeTime2 = HttpUtility.UrlEncode(cell.GetString()); break; case "servicedescription": symptom.ServiceDescription = HttpUtility.UrlEncode(cell.GetString()); break; } } catch (Exception) { throw; } }
public static void AreWorksheetsContentEquals(IXLWorksheet expected, IXLWorksheet actual) { if (expected == actual) { return; } Assert.AreEqual(expected.CellsUsed(XLCellsUsedOptions.All).Count(), actual.CellsUsed(XLCellsUsedOptions.All).Count(), "Cells used count failed"); IXLCell expectedFirstCellUsed = expected.FirstCellUsed(XLCellsUsedOptions.All); IXLCell actualFirstCellUsed = actual.FirstCellUsed(XLCellsUsedOptions.All); Assert.AreEqual(expectedFirstCellUsed.Address, actualFirstCellUsed.Address, "First cell used failed"); IXLCell expectedLastCellUsed = expected.LastCellUsed(XLCellsUsedOptions.All); IXLCell actualLastCellUsed = actual.LastCellUsed(XLCellsUsedOptions.All); Assert.AreEqual(expectedLastCellUsed.Address, actualLastCellUsed.Address, "Last cell used failed"); IXLRange range = expected.Range(expectedFirstCellUsed, expectedLastCellUsed); foreach (IXLCell expectedCell in range.Cells()) { IXLCell actualCell = actual.Cell(expectedCell.Address); if (expectedCell.HasFormula) { Assert.AreEqual(expectedCell.FormulaA1, actualCell.FormulaA1, $"Cell {expectedCell.Address} FormulaA1 failed."); //// For some reason sometimes the formulas "FormulaR1C1" are different although the formulas "FormulaA1" are match //Assert.AreEqual(expectedCell.FormulaR1C1, actualCell.FormulaR1C1, $"Cell {expectedCell.Address} FormulaR1C1 failed."); Assert.AreEqual(expectedCell.FormulaReference, actualCell.FormulaReference, $"Cell {expectedCell.Address} FormulaReference failed."); } else { Assert.AreEqual(expectedCell.Value, actualCell.Value, $"Cell {expectedCell.Address} Value failed."); } Assert.AreEqual(expectedCell.DataType, actualCell.DataType, $"Cell {expectedCell.Address} DataType failed."); Assert.AreEqual(expectedCell.Active, actualCell.Active, $"Cell {expectedCell.Address} Active failed."); AreColumnsEquals(expectedCell.WorksheetColumn(), actualCell.WorksheetColumn(), $"Column {actualCell.WorksheetColumn().RangeAddress} {{0}} failed."); AreRowEquals(expectedCell.WorksheetRow(), actualCell.WorksheetRow(), $"Row {actualCell.WorksheetRow().RangeAddress} {{0}} failed."); AreCellsStyleEquals(expectedCell.Style, actualCell.Style, $"Cell {expectedCell.Address} Style {{0}} failed."); AreCellsCommentEquals(expectedCell.Comment, actualCell.Comment, $"Cell {expectedCell.Address} Comment {{0}} failed."); } AreMergedRangesEquals(expected.MergedRanges, actual.MergedRanges); AreNamedRangesEquals(expected.NamedRanges, actual.NamedRanges); ArePageSetupEquals(expected.PageSetup, actual.PageSetup, "PageSetup {0} failed."); }
public static XLWorkbook FillAnalyticOfPoint(Manager m, Manager PreLastMonthManager, bool Belfan = false, bool RNR = false) { XLWorkbook wb = new XLWorkbook(m.FilePath); foreach (var stage in m.getStages()) { var dictPoints = stage.getStatisticOfPoints(Belfan, RNR); var page = wb.Worksheet(stage.name); var table = page.RangeUsed(); int curCol = table.LastColumn().ColumnNumber() + 2; const int numColPoint = 4; IXLCell CellStartCaption = page.Cell(2, curCol); if (dictPoints.Count > 0) { double widthCellCaption = 13.3; CellStartCaption.Value = "Количество некорректных пунктов"; CellStartCaption.WorksheetColumn().Width = widthCellCaption; CellStartCaption.CellRight().Value = "Количество прохождений пункта"; CellStartCaption.CellRight().WorksheetColumn().Width = widthCellCaption; CellStartCaption.CellRight().CellRight().Value = "Количество корректных пунктов"; CellStartCaption.CellRight().CellRight().WorksheetColumn().Width = widthCellCaption; CellStartCaption.CellRight().CellRight().CellRight().Value = "% Выполнения"; CellStartCaption.CellRight().CellRight().CellRight().WorksheetColumn().Width = widthCellCaption; var lastCellCaption = CellStartCaption.CellRight().CellRight().CellRight(); Dictionary <string, KeyValuePair <int, int> > dictPointsPreLastMonth = new Dictionary <string, KeyValuePair <int, int> >(); var prelastMonthCell = CellStartCaption.CellRight().CellRight().CellRight().CellRight(); var howChangeCell = prelastMonthCell.CellRight(); var totalCompareLastMonthsCell = howChangeCell.CellRight().CellBelow(); var totalCompareRange = page.Range(totalCompareLastMonthsCell, totalCompareLastMonthsCell.CellRight().CellRight()); var QtyWorseCell = totalCompareLastMonthsCell.CellBelow(); var QtyBetterCell = QtyWorseCell.CellRight(); var QtyNoChangeCell = QtyBetterCell.CellRight(); var totalMonthCell = QtyWorseCell.CellBelow().CellBelow().CellBelow(); var totalAVGLastMonthCell = totalMonthCell.CellBelow(); var totalAVGPreLastMonthCell = totalAVGLastMonthCell.CellBelow(); if (PreLastMonthManager != null && PreLastMonthManager.Name == m.Name && PreLastMonthManager.getStages().Exists(s => s.name.Trim() == stage.name.Trim()) && PreLastMonthManager.getStages().Where(s => s.name.Trim() == stage.name.Trim()).First().calls.Count > 0) { prelastMonthCell.Value = PreLastMonthManager.month; var preLastMonthCurStage = PreLastMonthManager.getStages().Where(s => s.name == stage.name).First(); dictPointsPreLastMonth = preLastMonthCurStage.getStatisticOfPoints(Belfan, RNR); howChangeCell.Value = "Как изменилось по сравнению с прошлым месяцем"; howChangeCell.WorksheetColumn().Width = 18; lastCellCaption = howChangeCell; totalCompareLastMonthsCell.Value = "Итоги сравнения с прошлым месяцем"; totalCompareRange.Merge(); QtyWorseCell.Value = "Всего Ухудшилось"; QtyBetterCell.Value = "Всего Улучшилось"; QtyNoChangeCell.Value = "Без изменения"; QtyWorseCell = QtyWorseCell.CellBelow(); QtyBetterCell = QtyBetterCell.CellBelow(); QtyNoChangeCell = QtyNoChangeCell.CellBelow(); totalCompareRange = page.Range(totalCompareLastMonthsCell, QtyNoChangeCell); Range(ref totalCompareRange); totalMonthCell.Value = "Месяц"; totalMonthCell.CellRight().Value = "Средний процент выполнения пунктов"; totalAVGLastMonthCell.Value = m.month; totalAVGPreLastMonthCell.Value = PreLastMonthManager.month; totalAVGLastMonthCell = totalAVGLastMonthCell.CellRight(); totalAVGPreLastMonthCell = totalAVGPreLastMonthCell.CellRight(); var totalrng = page.Range(totalMonthCell, totalAVGPreLastMonthCell); Range(ref totalrng); page.Columns(QtyWorseCell.Address.ColumnNumber, QtyNoChangeCell.Address.ColumnNumber).AdjustToContents(); } var Caption = page.Range(CellStartCaption, lastCellCaption); Caption.Style.Alignment.WrapText = true; //if (page.Cell(3, 4).IsMerged()) //{ Caption = page.Range(CellStartCaption.CellLeft(), lastCellCaption.CellBelow()); MergeRange(ref Caption); //} Caption.Style.Alignment.WrapText = true; Caption.Style.Font.Bold = true; Regex rComment = new Regex(@"КОРРЕКЦИИ"); int corrRow = 5; Match Mcomment = rComment.Match(page.Cell(corrRow, 1).GetString().ToUpper()); while (!Mcomment.Success) { corrRow++; Mcomment = rComment.Match(page.Cell(corrRow, 1).GetString().ToUpper()); } var CellPoint = CellStartCaption.CellBelow(); IXLCell lastCell = CellPoint; double totalSumLast = 0; double totalSumPreLast = 0; int totalQtyPointsLast = 0; int totalQtyPointsPreLast = 0; int qtyNoChange = 0; int qtyBetter = 0; int qtyWorse = 0; while (CellPoint.Address.RowNumber < corrRow - 4) { var CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint); if (dictPoints.ContainsKey(CellNamePoint.GetString() + (Belfan ? CellPoint.Address.RowNumber.ToString(): "") + (RNR ? page.Cell(CellPoint.Address.RowNumber, numColPoint).Style.Fill.BackgroundColor.ToString() : ""))) { int qtyRed = dictPoints[CellNamePoint.GetString() + (Belfan ? CellPoint.Address.RowNumber.ToString() : "") + (RNR ? page.Cell(CellPoint.Address.RowNumber, numColPoint).Style.Fill.BackgroundColor.ToString() : "")].Key; int qtyAll = dictPoints[CellNamePoint.GetString() + (Belfan ? CellPoint.Address.RowNumber.ToString() : "") + (RNR ? page.Cell(CellPoint.Address.RowNumber, numColPoint).Style.Fill.BackgroundColor.ToString() : "")].Value; CellPoint.CellLeft().Style.Fill.BackgroundColor = XLColor.Red; CellPoint.Value = qtyRed; CellPoint.Style.NumberFormat.NumberFormatId = OutPutDoc.getFormatData(DataForPrint.Estimate.qty); CellPoint.CellRight().Value = qtyAll; CellPoint.CellRight().Style.NumberFormat.NumberFormatId = OutPutDoc.getFormatData(DataForPrint.Estimate.qty); CellPoint.CellRight().CellRight().Value = qtyAll - qtyRed; CellPoint.CellRight().CellRight().Style.NumberFormat.NumberFormatId = OutPutDoc.getFormatData(DataForPrint.Estimate.qty); double AVGLast = (double)(qtyAll - qtyRed) / qtyAll; CellPoint.CellRight().CellRight().CellRight().Value = AVGLast; CellPoint.CellRight().CellRight().CellRight().Style.NumberFormat.NumberFormatId = OutPutDoc.getFormatData(DataForPrint.Estimate.AVG); if (AVGLast < 1) { CellPoint.CellRight().CellRight().CellRight().Style.Fill.BackgroundColor = XLColor.Red; } totalSumLast += AVGLast; totalQtyPointsLast++; lastCell = CellPoint.CellRight().CellRight().CellRight(); if (PreLastMonthManager != null && PreLastMonthManager.Name == m.Name && PreLastMonthManager.getStages().Exists(s => s.name == stage.name) && PreLastMonthManager.getStages().Where(s => s.name == stage.name).First().calls.Count > 0) { howChangeCell = page.Cell(CellPoint.Address.RowNumber, howChangeCell.Address.ColumnNumber); lastCell = howChangeCell; if (dictPointsPreLastMonth.ContainsKey(CellNamePoint.GetString() + (Belfan ? CellPoint.Address.RowNumber.ToString() : "") + (RNR ? page.Cell(CellPoint.Address.RowNumber, numColPoint).Style.Fill.BackgroundColor.ToString() : "")) && PreLastMonthManager.getStages().Where(s => s.name == stage.name).First().calls.Count > 0) { prelastMonthCell = page.Cell(CellPoint.Address.RowNumber, prelastMonthCell.Address.ColumnNumber); int qtyAllPreLast = dictPointsPreLastMonth[CellNamePoint.GetString() + (Belfan ? CellPoint.Address.RowNumber.ToString() : "") + (RNR ? page.Cell(CellPoint.Address.RowNumber, numColPoint).Style.Fill.BackgroundColor.ToString() : "")].Value; int qtyRedPreLast = dictPointsPreLastMonth[CellNamePoint.GetString() + (Belfan ? CellPoint.Address.RowNumber.ToString() : "") + (RNR ? page.Cell(CellPoint.Address.RowNumber, numColPoint).Style.Fill.BackgroundColor.ToString() : "")].Key; double AvgPreLast = (double)(qtyAllPreLast - qtyRedPreLast) / qtyAllPreLast; prelastMonthCell.Value = AvgPreLast; prelastMonthCell.Style.NumberFormat.NumberFormatId = OutPutDoc.getFormatData(DataForPrint.Estimate.AVG); totalSumPreLast += AvgPreLast; totalQtyPointsPreLast++; if (AvgPreLast < 1) { prelastMonthCell.Style.Fill.BackgroundColor = XLColor.Red; } if (AVGLast < AvgPreLast) { howChangeCell.Value = "Ухудшилось"; howChangeCell.Style.Fill.BackgroundColor = XLColor.Red; qtyWorse++; } if (AVGLast > AvgPreLast) { howChangeCell.Value = "Улучшилось"; howChangeCell.Style.Fill.BackgroundColor = XLColor.BrightGreen; qtyBetter++; } if (AVGLast == AvgPreLast) { howChangeCell.Value = "Не изменилось"; qtyNoChange++; } } else { if (PreLastMonthManager.getStages().Where(s => s.name == stage.name).First().calls.Count > 0) { howChangeCell.Value = "Критерий изменился"; } } } } CellPoint = CellPoint.CellBelow(); } if (PreLastMonthManager != null && PreLastMonthManager.Name == m.Name && PreLastMonthManager.getStages().Exists(s => s.name == stage.name) && PreLastMonthManager.getStages().Where(s => s.name == stage.name).First().calls.Count > 0) { QtyNoChangeCell.Value = qtyNoChange; QtyNoChangeCell.Style.NumberFormat.NumberFormatId = OutPutDoc.getFormatData(DataForPrint.Estimate.qty); QtyWorseCell.Value = qtyWorse; QtyWorseCell.Style.NumberFormat.NumberFormatId = OutPutDoc.getFormatData(DataForPrint.Estimate.qty); QtyBetterCell.Value = qtyBetter; QtyBetterCell.Style.NumberFormat.NumberFormatId = OutPutDoc.getFormatData(DataForPrint.Estimate.qty); totalAVGLastMonthCell.Value = totalSumLast / totalQtyPointsLast; totalAVGLastMonthCell.Style.NumberFormat.NumberFormatId = OutPutDoc.getFormatData(DataForPrint.Estimate.AVG); totalAVGPreLastMonthCell.Value = totalSumPreLast / totalQtyPointsPreLast; totalAVGPreLastMonthCell.Style.NumberFormat.NumberFormatId = OutPutDoc.getFormatData(DataForPrint.Estimate.AVG); } else { var CellAVGLastMonth = CellPoint.CellRight().CellRight().CellRight().CellBelow(); CellAVGLastMonth.Value = totalSumLast / totalQtyPointsLast; CellAVGLastMonth.Style.NumberFormat.NumberFormatId = OutPutDoc.getFormatData(DataForPrint.Estimate.AVG); CellAVGLastMonth.CellLeft().Value = "Средний %"; var rngavg = page.Range(CellAVGLastMonth.CellLeft(), CellAVGLastMonth); Range(ref rngavg); } var rngTable = page.Range(CellStartCaption.CellLeft(), lastCell); //rngTable.Style.Border.RightBorder = XLBorderStyleValues.Thin; rngTable.Style.Border.InsideBorder = XLBorderStyleValues.Thin; rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;; rngTable.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; } } return(wb); }
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; }
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; }
/// <summary> /// Return true if it have at least 1 DTOValidacionArchivo object instance. Q: Have any format error? YES(TRUE) or NO(FALSE) /// </summary> /// <param name="template">Template format object</param> /// <param name="cell">IXL Cell objet</param> /// <returns>True for any new instance DTOValidacionArchivo. If DTOValidacionArchivo is NULL then return false</returns> private bool Validator_Cell(TemplateFormatCAC template, IXLCell cell) { bool flag = false; try { DTOValidacionArchivo validation = null; #region Validacion si es null if (cell.IsEmpty() == true && template.Nullable == false) { validation = new DTOValidacionArchivo() { FechaCreacion = DateTime.Now.ToString(Configuration.GetValueConf(Constants.DateFormat)), Descripcion = string.Format(Resource_DefaultMessage.ERROR_CELL_EMPTY_OR_NULL, cell.WorksheetColumn().ColumnLetter(), cell.WorksheetRow().RowNumber(), template.Name), Valor = cell.GetString(), Celda = $"{cell.WorksheetColumn().ColumnLetter()}{cell.WorksheetRow().RowNumber()}", Fila = $"{cell.WorksheetRow().RowNumber()}", Columna = $"{cell.WorksheetColumn().ColumnLetter()}" }; Auditor.SaveLog(string.Format(Resource_DefaultMessage.CONTROL_VALUE, nameof(FileProcessBP.Validator_Cell), validation.Columna, validation.Valor, "ERROR_CELL_EMPTY_OR_NULL", validation.ToString(), cell.Worksheet.Name)); validator_result.Add(validation); } #endregion #region Si no es null or empty else if (cell.IsEmpty() == false) { bool hasFormula = cell.HasFormula; if (hasFormula == true) { #region Si tiene formula validation = new DTOValidacionArchivo() { FechaCreacion = DateTime.Now.ToString(Configuration.GetValueConf(Constants.DateFormat)), Descripcion = string.Format(Resource_DefaultMessage.ERROR_CELL_HAVE_FORMULA, cell.WorksheetColumn().ColumnLetter(), cell.WorksheetRow().RowNumber(), template.Name), Valor = cell.GetString(), Celda = $"{cell.WorksheetColumn().ColumnLetter()}{cell.WorksheetRow().RowNumber()}", Fila = $"{cell.WorksheetRow().RowNumber()}", Columna = $"{cell.WorksheetColumn().ColumnLetter()}" }; Auditor.SaveLog(string.Format(Resource_DefaultMessage.CONTROL_VALUE, nameof(FileProcessBP.Validator_Cell), validation.Columna, validation.Valor, "ERROR_CELL_HAVE_FORMULA", validation.ToString(), cell.Worksheet.Name)); validator_result.Add(validation); #endregion Si tiene formula } else { #region Validacion del tipo de dato var cell_datatype = cell.DataType.ToString(); if (template.Type.Contains(cell_datatype) == false) { validation = new DTOValidacionArchivo() { FechaCreacion = DateTime.Now.ToString(Configuration.GetValueConf(Constants.DateFormat)), Descripcion = string.Format(Resource_DefaultMessage.ERROR_CELL_NOT_VALID_TYPE, cell.WorksheetColumn().ColumnLetter(), cell.WorksheetRow().RowNumber(), template.Type, cell_datatype, cell.Value, template.Name), Valor = cell.GetString(), Celda = $"{cell.WorksheetColumn().ColumnLetter()}{cell.WorksheetRow().RowNumber()}", Fila = $"{cell.WorksheetRow().RowNumber()}", Columna = $"{cell.WorksheetColumn().ColumnLetter()}" }; Auditor.SaveLog(string.Format(Resource_DefaultMessage.CONTROL_VALUE, nameof(FileProcessBP.Validator_Cell), validation.Columna, validation.Valor, "ERROR_CELL_NOT_VALID_TYPE", validation.ToString(), cell.Worksheet.Name)); validator_result.Add(validation); } #endregion #region Validacion si el valor está contenido en la lista predeterminado y si es texto if (template.SelectList != null && template.SelectList.Count > 0) { bool isContained = false; try { isContained = template.SelectList.Where(m => m.Value == cell.GetValue <string>()).Count() > 0 ? true : false; } catch (Exception) { isContained = false; } if (isContained == false) { validation = new DTOValidacionArchivo() { FechaCreacion = DateTime.Now.ToString(Configuration.GetValueConf(Constants.DateFormat)), Descripcion = string.Format(Resource_DefaultMessage.ERROR_CELL_DO_NOT_LIST_VALUE, cell.WorksheetColumn().ColumnLetter(), cell.WorksheetRow().RowNumber(), cell.Value, template.Name), Valor = cell.GetString(), Celda = $"{cell.WorksheetColumn().ColumnLetter()}{cell.WorksheetRow().RowNumber()}", Fila = $"{cell.WorksheetRow().RowNumber()}", Columna = $"{cell.WorksheetColumn().ColumnLetter()}" }; Auditor.SaveLog(string.Format(Resource_DefaultMessage.CONTROL_VALUE, nameof(FileProcessBP.Validator_Cell), validation.Columna, validation.Valor, "ERROR_CELL_DO_NOT_LIST_VALUE", validation.ToString(), cell.Worksheet.Name)); validator_result.Add(validation); } } #endregion #region Si es fecha y obtener el valor segun el tipo de dato var cell_type = cell.Value.GetType(); if (typeof(DateTime).ToString().Contains(cell_type.ToString()) == true || template.Type.Contains(typeof(DateTime).Name)) { bool regexResult = false; try { DateTime cell_datetime; if (cell.TryGetValue <DateTime>(out cell_datetime) == true) { string datetime_to_comparer = cell_datetime.ToString(template.Format); string regex = @"^\d{4}-((0\d)|(1[012]))-(([012]\d)|3[01])$"; regexResult = Regex.Match(datetime_to_comparer, regex).Success; } else { regexResult = false; } } catch (Exception) { regexResult = false; } if (regexResult == false) { validation = new DTOValidacionArchivo() { FechaCreacion = DateTime.Now.ToString(Configuration.GetValueConf(Constants.DateFormat)), Descripcion = string.Format(Resource_DefaultMessage.ERROR_CELL_NOT_FORMAT, cell.WorksheetColumn().ColumnLetter(), cell.WorksheetRow().RowNumber(), template.Format, template.Name), Valor = cell.GetString(), Celda = $"{cell.WorksheetColumn().ColumnLetter()}{cell.WorksheetRow().RowNumber()}", Fila = $"{cell.WorksheetRow().RowNumber()}", Columna = $"{cell.WorksheetColumn().ColumnLetter()}" }; Auditor.SaveLog(string.Format(Resource_DefaultMessage.CONTROL_VALUE, nameof(FileProcessBP.Validator_Cell), validation.Columna, validation.Valor, "ERROR_CELL_NOT_FORMAT", validation.ToString(), cell.Worksheet.Name)); validator_result.Add(validation); } } #endregion } } #endregion flag = validation == null ? false : true; } catch (Exception ex) { validator_result.Add(ExceptionWriter(ex)); flag = true; throw ex; } template = null; cell = null; return(flag); }