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