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