private void WriteTemplateData(IXLCell cell, List <Dictionary <string, object> > datas, MAINDATASET dataSetDesc, ref int shiftRanges)
{
if (datas.Count > 1)
{
shiftRanges = shiftRanges + datas.Count - 1;
cell.WorksheetRow().InsertRowsBelow(datas.Count - 1);
}
var rowNumber = cell.Address.ColumnNumber > 1 && cell.CellLeft().Value.ToString() == DATASET_INDEX ? 1 : 0;
if (dataSetDesc.GROUP?.Length > 0 && datas.Count > 1)
{
var groupFields = new Stack <string>(dataSetDesc.GROUP.Select(x => x.GroupField));
int countGroup = 0;
var groupDatas = GetGroupDatas(datas.Select(x => new DataGroupWrapper {
Data = x
}), groupFields, ref countGroup).ToList();
shiftRanges = shiftRanges + countGroup - dataSetDesc.GROUP.Length;
cell.WorksheetRow().InsertRowsBelow(countGroup + 1);
var beginGroup = cell;
var fieldIndexes = datas.First()
.Select((x, i) => new { field = x.Key, index = i })
.ToDictionary(x => x.field, x => x.index);
this.WriteGroupDatas(groupDatas, dataSetDesc.GROUP, 0, fieldIndexes, ref rowNumber, ref cell);
var groupCell = GroupData(dataSetDesc.GROUP, 0, fieldIndexes, cell, beginGroup);
groupCell.Value = "Общий итог";
}
else
{
this.WriteRowData(datas, ref rowNumber, ref cell);
}
}
protected bool IsSpecialRangeCell(IXLCell cell)
{
var cellRow = cell.WorksheetRow().RowNumber();
var cellClmn = cell.WorksheetColumn().ColumnNumber();
return(cellRow == RangeOptionsRow?.LastAddress.RowNumber && cellClmn == RangeOptionsRow.FirstAddress.ColumnNumber);
}
private MoveData CreateMoveTask(int groupColumn, string title, IXLCell firstCell, IXLCell lastCell, RangeType rangeType)
{
var groupRng = _range.Range(firstCell, lastCell).Unsubscribed();
var level = firstCell.WorksheetRow().Unsubscribed().OutlineLevel;
var group = new MoveData(groupRng.RangeAddress, rangeType, title, level)
{
GroupColumn = groupColumn
};
return(group);
}
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);
}
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.");
}
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);
}
public async Task <bool> ExportToExcelFileAsync(DataToExport dataToExport, string filePath)
{
bool success = false;
await Task.Run(() =>
{
try
{
using var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet();
string canalType = dataToExport.CanalCharacteristics.canalType;
IList <Parameter> canalGeometryParameters = dataToExport.CanalCharacteristics.Item2;
ws.Cell("A2").SetValue("Тип канала");
ws.Cell("A3").SetValue(canalType);
for (int i = 0; i < canalGeometryParameters.Count; i++)
{
ws.Cell(2, i + 4).SetValue(canalGeometryParameters[i].Name);
ws.Cell(3, i + 4).SetValue(canalGeometryParameters[i].Value + $" {canalGeometryParameters[i].MeasureUnit}");
}
string materialType = dataToExport.MaterialCharacteristics.materialType;
IList <Parameter> materialPropertyParameters = dataToExport.MaterialCharacteristics.Item2;
ws.Cell("A5").SetValue("Тип материала");
ws.Cell("A6").SetValue(materialType);
for (int i = 0; i < materialPropertyParameters.Count; i++)
{
ws.Cell(5, i + 4).SetValue(materialPropertyParameters[i].Name);
ws.Cell(6, i + 4).SetValue(materialPropertyParameters[i].Value + $" {materialPropertyParameters[i].MeasureUnit}");
}
IXLColumn lastMergedColumn = ws.LastColumnUsed();
ws.Range(ws.Cell(1, 1), ws.LastColumnUsed().Cell(1)).Merge().SetValue("Входные параметры");
IXLCell xLCell = ws.LastColumnUsed().ColumnRight().ColumnRight().Cell(2);
IList <Parameter> variableParameters = dataToExport.VariableParameters;
for (int i = 0; i < variableParameters.Count; i++)
{
xLCell.SetValue(variableParameters[i].Name);
xLCell = xLCell.CellBelow().SetValue(variableParameters[i].Value + $" {variableParameters[i].MeasureUnit}");
xLCell = xLCell.CellRight().CellAbove();
}
ws.Range(lastMergedColumn.ColumnRight().ColumnRight().Cell(1),
ws.LastColumnUsed().Cell(1)).Merge().SetValue("Варьируемые параметры");
lastMergedColumn = ws.LastColumnUsed();
xLCell = ws.LastColumnUsed().ColumnRight().ColumnRight().Cell(2);
IList <Parameter> empiricalParametersOfMathModel = dataToExport.EmpiricalParametersOfMathModel;
for (int i = 0; i < empiricalParametersOfMathModel.Count; i++)
{
xLCell.SetValue(empiricalParametersOfMathModel[i].Name);
xLCell = xLCell.CellBelow().SetValue(empiricalParametersOfMathModel[i].Value + $" {empiricalParametersOfMathModel[i].MeasureUnit}");
xLCell = xLCell.CellRight().CellAbove();
}
ws.Range(lastMergedColumn.ColumnRight().ColumnRight().Cell(1),
ws.LastColumnUsed().Cell(1)).Merge().SetValue("Эмпирические коэффициенты математической модели");
IDictionary <string, IList <Parameter> > discreteOutputParameters = dataToExport.DiscreteOutputParameters;
xLCell = xLCell.CellBelow().CellBelow().CellBelow().CellBelow().CellBelow().CellBelow().CellBelow().CellBelow()
.WorksheetRow().Cell(1);
foreach (KeyValuePair <string, IList <Parameter> > keyValuePair in discreteOutputParameters)
{
IXLCell firstCell = xLCell;
foreach (Parameter parameter in keyValuePair.Value)
{
xLCell.SetValue(parameter.Name);
xLCell = xLCell.CellBelow().SetValue($"{parameter.Value} {parameter.MeasureUnit}");
xLCell = xLCell.CellAbove().CellRight();
}
if (keyValuePair.Value.Count <= 0)
{
continue;
}
if (keyValuePair.Value.Count == 1)
{
xLCell = xLCell.CellRight();
continue;
}
ws.Range(firstCell.CellAbove(), ws.LastRowUsed().LastCellUsed().CellAbove().CellAbove()).
Merge().SetValue(keyValuePair.Key);
xLCell = xLCell.CellRight();
}
xLCell = xLCell.CellLeft().CellLeft().CellAbove().CellAbove();
ws.Range(xLCell, xLCell.WorksheetRow().Cell(1)).Merge().SetValue("Результаты");
xLCell = ws.LastRowUsed().Cell(1).CellBelow().CellBelow();
ws.Range(xLCell, xLCell.CellRight().CellRight().CellRight()).Merge().SetValue("Таблица результатов");
IList <(Parameter coordinate, Parameter temperature, Parameter viscosity)> resultsTable = dataToExport.ContiniousResults;
xLCell.CellBelow().SetValue($"{resultsTable[0].coordinate.Name}, {resultsTable[0].coordinate.MeasureUnit}")
.CellRight().SetValue($"{resultsTable[0].temperature.Name}, {resultsTable[0].temperature.MeasureUnit}")
.CellRight().SetValue($"{resultsTable[0].viscosity.Name}, {resultsTable[0].viscosity.MeasureUnit}");
xLCell = xLCell.CellBelow().CellBelow();
NumberFormatInfo nfi = new NumberFormatInfo
{
NumberDecimalSeparator = "."
};
foreach (var(coordinate, temperature, viscosity) in resultsTable)
{
xLCell.SetValue(((double)coordinate.Value)
.ToString($"F{dataToExport.CoordinatePrecision}", nfi))
.SetDataType(XLDataType.Number)
.CellRight().SetValue(((double)temperature.Value).ToString($"F2", nfi))
.SetDataType(XLDataType.Number)
.CellRight().SetValue(((double)viscosity.Value).ToString("F2", nfi))
.SetDataType(XLDataType.Number);
xLCell = xLCell.CellBelow();
}
//ws.RowsUsed().AdjustToContents();
//ws.ColumnsUsed().AdjustToContents();
//using var stream = new MemoryStream();
//dataToExport.TemperaturePlot.Save(stream, ImageFormat.Png);
//ws.AddPicture(stream).MoveTo(ws.Cell("H8"))
// .WithSize(dataToExport.TemperaturePlot.Width, dataToExport.TemperaturePlot.Height);
//using var stream1 = new MemoryStream();
//dataToExport.ViscosityPlot.Save(stream1, ImageFormat.Png);
//ws.AddPicture(stream1).MoveTo(ws.Cell("O8"))
// .WithSize(dataToExport.ViscosityPlot.Width, dataToExport.ViscosityPlot.Height);
wb.SaveAs(filePath);
success = true;
}
catch { success = false; }
});
return(success);
}
internal bool TryParse(IXLCell cell, IXLCell cell2, ref IEntry entry)
{
string stringValue = cell.GetString();
string stringValue2 = cell2.GetString();
if (string.IsNullOrWhiteSpace(stringValue))
{
return(false);
}
if (entry.DataType == SPSValue.PreDefinedNodeID || entry.DataType == SPSEvent.PreDefinedNodeID)
{
bool value;
try
{
value = cell.GetValue <bool>();
}
catch (FormatException exception)
{
Logger.Error($"Unable to parse '{stringValue}' in row {cell.WorksheetRow().RowNumber()} as bool.", exception);
return(false);
}
entry.Value = value;
return(true);
}
if (entry.DataType == DPSValue.PreDefinedNodeID || entry.DataType == DPSEvent.PreDefinedNodeID)
{
byte value;
try
{
value = cell.GetValue <byte>();
}
catch (FormatException exception)
{
Logger.Error($"Unable to parse '{stringValue}' in row {cell.WorksheetRow().RowNumber()} as byte.", exception);
return(false);
}
entry.Value = value;
return(true);
}
if (entry.DataType == MeasuredValue.PreDefinedNodeID || entry.DataType == MeasuredValueEvent.PreDefinedNodeID)
{
float value;
try
{
value = cell.GetValue <float>();
}
catch (FormatException exception)
{
Logger.Error($"Unable to parse '{stringValue}' in row {cell.WorksheetRow().RowNumber()} as float.", exception);
return(false);
}
entry.Value = value;
return(true);
}
if (entry.DataType == StepPosValue.PreDefinedNodeID || entry.DataType == StepPosEvent.PreDefinedNodeID)
{
int value;
bool value2;
try
{
value = cell.GetValue <int>();
value2 = cell2.GetValue <bool>();
}
catch (FormatException exception)
{
Logger.Error($"Unable to parse '{stringValue}' and '{stringValue2}' in row {cell.WorksheetRow().RowNumber()} as float and bool.", exception);
return(false);
}
entry.Value = value;
entry.Value2 = value2;
return(true);
}
if (entry.DataType == CounterValue.PreDefinedNodeID)
{
long value;
float value2;
try
{
value = cell.GetValue <int>();
value2 = cell2.GetValue <float>();
}
catch (FormatException exception)
{
Logger.Error($"Unable to parse '{stringValue}' and '{stringValue2}' in row {cell.WorksheetRow().RowNumber()} as int and float.", exception);
return(false);
}
entry.Value = value;
entry.Value2 = value2;
return(true);
}
if (entry.DataType == ComplexMeasuredValue.PreDefinedNodeID)
{
float value;
float value2;
try
{
value = cell.GetValue <float>();
value2 = cell2.GetValue <float>();
}
catch (FormatException exception)
{
Logger.Error($"Unable to parse '{stringValue}' and '{stringValue2}' in row {cell.WorksheetRow().RowNumber()} as float and float.", exception);
return(false);
}
entry.Value = value;
entry.Value2 = value2;
return(true);
}
if (entry.DataType == StringEvent.PreDefinedNodeID)
{
string value;
try
{
value = cell.GetValue <string>();
}
catch (FormatException exception)
{
Logger.Error($"Unable to parse '{stringValue}' in row {cell.WorksheetRow().RowNumber()} as string.", exception);
return(false);
}
entry.Value = value;
return(true);
}
if (ProcessValueFactory.GetNodeIDType(entry.DataType) == NodeIDType.GroupDataTypeTimeSeries)
{
float value;
try
{
value = cell.GetValue <float>();
}
catch (FormatException exception)
{
Logger.Error($"Unable to parse '{stringValue}' in row {cell.WorksheetRow().RowNumber()} as string.", exception);
return(false);
}
entry.Value = value;
return(true);
}
// For everything else we assume it's an Integer or an Enum
int enumValue;
try
{
enumValue = cell.GetValue <int>();
}
catch (FormatException exception)
{
Logger.Error($"Unable to parse '{stringValue}' in row {cell.WorksheetRow().RowNumber()} as int.", exception);
return(false);
}
entry.Value = enumValue;
return(true);
}
public MergedRowCollection(IXLCell fromCell)
{
_fromCell = fromCell;
_nextRow = fromCell.WorksheetRow();
}