internal static void SetConditionalFormatting(IXLCell cell) { cell.CellLeft(1); var formula = $"={cell.Address}<>{cell.CellLeft(1).Address}"; cell.AddConditionalFormat().WhenIsTrue(formula) .Fill.SetBackgroundColor(XLColor.FromHtml("#FFC1B4")); }
public static IXLCell SetGreenIfEqualToPrevious(this IXLCell cell) { cell.CellLeft(1); var formula = $"={cell.Address}={cell.CellLeft(1).Address}"; cell.AddConditionalFormat().WhenIsTrue(formula) .Fill.SetBackgroundColor(XLColor.FromHtml("#B4FFC8")); return(cell); }
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); } }
private void WriteRowData(IEnumerable <IDictionary <string, object> > datas, ref int rowNumber, ref IXLCell cell) { foreach (var dataRow in datas) { if (rowNumber > 0) { cell.CellLeft().Value = rowNumber; rowNumber = rowNumber + 1; } foreach (var dataCell in dataRow) { cell.Value = dataCell.Value; cell = cell.CellRight(); } cell = cell.CellLeft(dataRow.Count).CellBelow(); } }
public static IXLCell SetRedIfFalse(this IXLCell cell) { cell.CellLeft(1); var formula = $"={cell.Address}=False"; cell.AddConditionalFormat().WhenIsTrue(formula) .Fill.SetBackgroundColor(XLColor.FromHtml("#FFC1B4")); return(cell); }
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); }
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); }