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