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();
}
public void Validation_1()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Data Validation Issue");
IXLCell cell = ws.Cell("E1");
cell.SetValue("Value 1");
cell = cell.CellBelow();
cell.SetValue("Value 2");
cell = cell.CellBelow();
cell.SetValue("Value 3");
cell = cell.CellBelow();
cell.SetValue("Value 4");
cell = cell.CellBelow();
ws.Cell("A1").SetValue("Cell below has Validation Only.");
cell = ws.Cell("A2");
cell.DataValidation.List(ws.Range("$E$1:$E$4"));
ws.Cell("B1").SetValue("Cell below has Validation with a title.");
cell = ws.Cell("B2");
cell.DataValidation.List(ws.Range("$E$1:$E$4"));
cell.DataValidation.InputTitle = "Title for B2";
Assert.AreEqual(cell.DataValidation.AllowedValues, XLAllowedValues.List);
Assert.AreEqual(cell.DataValidation.Value, "'Data Validation Issue'!$E$1:$E$4");
Assert.AreEqual(cell.DataValidation.InputTitle, "Title for B2");
ws.Cell("C1").SetValue("Cell below has Validation with a message.");
cell = ws.Cell("C2");
cell.DataValidation.List(ws.Range("$E$1:$E$4"));
cell.DataValidation.InputMessage = "Message for C2";
Assert.AreEqual(cell.DataValidation.AllowedValues, XLAllowedValues.List);
Assert.AreEqual(cell.DataValidation.Value, "'Data Validation Issue'!$E$1:$E$4");
Assert.AreEqual(cell.DataValidation.InputMessage, "Message for C2");
ws.Cell("D1").SetValue("Cell below has Validation with title and message.");
cell = ws.Cell("D2");
cell.DataValidation.List(ws.Range("$E$1:$E$4"));
cell.DataValidation.InputTitle = "Title for D2";
cell.DataValidation.InputMessage = "Message for D2";
Assert.AreEqual(cell.DataValidation.AllowedValues, XLAllowedValues.List);
Assert.AreEqual(cell.DataValidation.Value, "'Data Validation Issue'!$E$1:$E$4");
Assert.AreEqual(cell.DataValidation.InputTitle, "Title for D2");
Assert.AreEqual(cell.DataValidation.InputMessage, "Message for D2");
}
private void ParseValueItem(ValuesBunch valueItems, string type, IXLCell currentValueCell, ref int lastRowNum)
{
while (currentValueCell.IsEmpty() == false)
{
if (DateTime.TryParse(currentValueCell.CachedValue.ToString(), out var d))
{
return;
}
if (decimal.TryParse(currentValueCell.CachedValue?.ToString(), out var num))
{
try
{
var value = new ValueItem(num, new StatEnumItem(type));
valueItems.Add(value);
}
catch
{
continue;
}
}
lastRowNum = Math.Max(lastRowNum, currentValueCell.Address.RowNumber);
currentValueCell = currentValueCell.CellBelow();
}
}
private void AddWorkItems(IEnumerable <TrackedTimeNode> groupedItems, ref IXLCell frontCell, ref IXLCell lastDataCell, int level)
{
foreach (var item in groupedItems)
{
var firstCell = frontCell;
var row = item;
var currentCell = frontCell;
// 3 lots par WorkItem :
// 1/ Element récapitulatif pour le WI
currentCell = AddValues(level, row, currentCell, null,
row.TotalDurationWithChildrenInMin / 60d, null, null);
lastDataCell = currentCell;
frontCell = frontCell.CellBelow();
// 3/ Liste des saisies directes sur le WI
if (row.FirstTrackedTimeRow != null)
{
// plusieurs saisies des temps directes sur cet item :
foreach (var workItemTimes in row.DirectTrackedTimeRows)
{
currentCell = frontCell;
currentCell = AddValues(level + 1, row, currentCell, workItemTimes.TeamMember,
null, workItemTimes.DurationInSeconds / 60d, workItemTimes.RecordDate.Date);
lastDataCell = currentCell;
frontCell = frontCell.CellBelow();
}
}
// 3/ Liste des WI enfants
AddWorkItems(row.Childs, ref frontCell, ref lastDataCell, level + 1);
if (firstCell.CellBelow() != frontCell)
{
// regrouper car il y a plus d'une ligne sur cet item:
// cf https://stackoverflow.com/questions/25756741/closedxml-outline pour identifier quelles lignes utiliser pour appeler Group().
var rowsToGroup = firstCell.Worksheet.Rows(firstCell.CellBelow().Address.RowNumber, frontCell.CellAbove().Address.RowNumber);
excelGroupingActions.Push(() =>
{
rowsToGroup.Group(level); // Create an outline
});
}
}
}
/// <summary>
/// Writes a list of values to the current row
/// </summary>
/// <param name="row"></param>
public void WriteRow(List <string> row)
{
IXLCell start = current;
foreach (string value in row)
{
current.Value = value;
current = current.CellRight();
}
current = start.CellBelow();
}
/// <summary>
/// Reads a row of values from the current position and moves down a row
/// </summary>
/// <returns></returns>
public List <string> ReadRow()
{
IXLCell position = current;
var values = new List <string>();
string value = current.GetString();
while (!string.IsNullOrEmpty(value))
{
values.Add(value);
current = current.CellRight();
value = current.GetString();
}
current = position.CellBelow();
return(values);
}
private int ParseValueItems(ValuesBunch valueItems, IXLCell currentTypeCell)
{
int lastRowNum = currentTypeCell.Address.RowNumber;
while (currentTypeCell.IsEmpty() == false)
{
var type = currentTypeCell.CachedValue.ToString();
if (DataValidation.IsNameValid(type))
{
ParseValueItem(valueItems, type, currentTypeCell.CellBelow(), ref lastRowNum);
}
currentTypeCell = currentTypeCell.CellRight();
}
return(lastRowNum);
}
private void WriteGroupDatas(IEnumerable <DataGroupWrapper> dataWrappers, MAINDATASETGROUP[] groups, int indexGroup,
Dictionary <string, int> fieldIndexes, ref int rowNumber, ref IXLCell cell)
{
foreach (var dataWrapper in dataWrappers)
{
var beginGroup = cell;
if (groups.Length - 1 > indexGroup)
{
this.WriteGroupDatas(dataWrapper.Groups, groups, indexGroup + 1, fieldIndexes, ref rowNumber, ref cell);
}
else
{
this.WriteRowData(dataWrapper.Groups.Select(x => x.Data), ref rowNumber, ref cell);
}
var groupCell = GroupData(groups, indexGroup, fieldIndexes, cell, beginGroup);
groupCell.Value = $"{groupCell.CellAbove(groups.Length - indexGroup).Value} Итог";
cell = cell.CellBelow();
}
}
public void ParserCheckLists(IEnumerable <IFormFile> files)
{
using (var stream = files.First().OpenReadStream())
{
XLWorkbook wb = new XLWorkbook(stream);
FillStageDictionary(wb);
}
foreach (var file in files)
{
string Manager = Regex.Match(file.FileName, @"(\w+)").Groups[1].Value;
using (var stream = file.OpenReadStream())
{
XLWorkbook wb = new XLWorkbook(stream);
foreach (var page in wb.Worksheets)
{
var statisticMatch = Regex.Match(page.Name.ToUpper().Trim(), "СТАТИСТИК");
var LastTableMatch = Regex.Match(page.Name.ToUpper().Trim(), "СВОДН");
if (!statisticMatch.Success && !LastTableMatch.Success)
{
IXLCell cell = page.Cell(1, 5);
DateTime curDate;
bool normalDate = false;
if (cell.DataType == XLDataType.DateTime)
{
curDate = cell.GetDateTime();
normalDate = true;
}
else
{
if (!DateTime.TryParse(cell.GetString(), new CultureInfo("ru-RU"), DateTimeStyles.None, out curDate))
{
normalDate = DateTime.TryParse(cell.GetString(), new CultureInfo("en-US"), DateTimeStyles.None, out curDate);
}
else
{
normalDate = true;
}
}
string phoneNumber;
int corrRow = 5;
Match Mcomment = Regex.Match(page.Cell(corrRow, 1).GetString().ToUpper(), @"КОРРЕКЦИИ");
while (!Mcomment.Success)
{
corrRow++;
Mcomment = Regex.Match(page.Cell(corrRow, 1).GetString().ToUpper(), @"КОРРЕКЦИИ");
}
while (!(cell.CellBelow().IsEmpty() && cell.CellBelow().CellRight().IsEmpty() && cell.CellBelow().CellBelow().IsEmpty() && cell.CellBelow().CellBelow().CellRight().IsEmpty()))
{
if (cell.GetValue <string>() != "")
{
if (cell.DataType == XLDataType.DateTime)
{
curDate = cell.GetDateTime();
}
else
{
if (!DateTime.TryParse(cell.GetString(), new CultureInfo("ru-RU"), DateTimeStyles.None, out curDate))
{
DateTime.TryParse(cell.GetString(), new CultureInfo("en-US"), DateTimeStyles.None, out curDate);
}
}
}
phoneNumber = cell.CellBelow().GetValue <string>().ToUpper().Trim();
var CellPhoneNumber = cell.CellBelow();
string link;
if (CellPhoneNumber.HasHyperlink)
{
link = CellPhoneNumber.GetHyperlink().ExternalAddress.AbsoluteUri;
}
else
{
link = "";
}
if (link == "")
{
}
if (phoneNumber != "")
{
Regex rx = new Regex("ВХОДЯЩ");
Match m = rx.Match(page.Name.ToUpper().Trim());
var exCallSeq = processedCalls.Where(c => (c.Client == phoneNumber && link == "") || (c.Link == link && link != ""));
var exCall = new ProcessedCall();
//exCall.StartDateAnalyze = curDate.AddDays(-1);
if (exCallSeq.Count() > 0)
{
exCall = exCallSeq.First();
//exCall.StartDateAnalyze = curDate.AddDays(-1);
}
else
{
exCall.ClientState = "";
exCall.StartDateAnalyze = DateTime.MinValue;
}
if ((curDate > exCall.StartDateAnalyze ||
(
exCall.ClientState.ToUpper() == "В РАБОТЕ") &&
exCall.StartDateAnalyze < DateTime.Today.AddDays(1)
) && normalDate)
{
DateTime DateNext = new DateTime();
var NextContactCell = page.Cell(corrRow + 6, cell.Address.ColumnNumber);
if (NextContactCell.GetString() != "")
{
if (NextContactCell.DataType == XLDataType.DateTime)
{
DateNext = NextContactCell.GetDateTime();
}
else
{
if (!DateTime.TryParse(NextContactCell.GetString(), new CultureInfo("ru-RU"), DateTimeStyles.None, out DateNext))
{
DateTime.TryParse(NextContactCell.GetString(), new CultureInfo("en-US"), DateTimeStyles.None, out DateNext);
}
}
}
if (curDate > new DateTime(2020, 5, 5))
{
phones.AddCall(new FullCall(phoneNumber, link, page.Name.ToUpper().Trim(), curDate, !m.Success, page.Cell(corrRow, cell.Address.ColumnNumber).GetString(), Manager, page.Cell(corrRow + 5, cell.Address.ColumnNumber).GetString(), DateNext));
}
else
{
phones.AddCall(new FullCall(phoneNumber, link, page.Name.ToUpper().Trim(), curDate, !m.Success, page.Cell(corrRow, cell.Address.ColumnNumber).GetString(), Manager));
}
}
}
cell = cell.CellRight();
}
phones.CleanSuccess(ref processedCalls);
}
}
}
}
}
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 new void Processing()
{
XLWorkbook wb = new XLWorkbook(FilePath);
foreach (var page in wb.Worksheets)
{
if (page.Name.ToUpper().Trim() != "СТАТИСТИКА" && page.Name.ToUpper().Trim() != "СВОДНЫЕ" && page.Name.ToUpper().Trim() != "СВОДНАЯ" && page.Name.ToUpper().Trim() != "СТАТИСТИКИ")
{
const int numColPoint = 4;
IXLCell CellDate = page.Cell(1, numColPoint + 1);
DateTime curDate;
DateTime.TryParse(CellDate.GetValue <string>(), out curDate);
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());
}
List <Call> calls = new List <Call>();
while (!(CellDate.CellBelow().IsEmpty() && CellDate.CellBelow().CellRight().IsEmpty() && CellDate.CellBelow().CellBelow().IsEmpty() && CellDate.CellBelow().CellBelow().CellRight().IsEmpty()))
{
if (CellDate.GetValue <string>() != "")
{
DateTime.TryParse(CellDate.GetValue <string>(), out curDate);
}
string phoneNumber = CellDate.CellBelow().GetValue <string>();
var phoneCell = CellDate.CellBelow();
if (phoneNumber == "")
{
phoneNumber = CellDate.CellBelow().CellBelow().GetValue <string>();
phoneCell = CellDate.CellBelow().CellBelow();
}
if (phoneNumber != "")
{
string link = "";
if (phoneCell.HasHyperlink)
{
link = phoneCell.Hyperlink.ExternalAddress.AbsoluteUri;
}
TimeSpan duration;
TimeSpan wrongtime1 = new TimeSpan(1, 0, 0, 0);
TimeSpan wrongtime2 = new TimeSpan();
IXLCell CellPoint = CellDate.CellBelow().CellBelow().CellBelow();
if (CellPoint.DataType == XLDataType.DateTime)
{
CellPoint.DataType = XLDataType.TimeSpan;
}
TimeSpan.TryParse(CellPoint.GetString(), out duration);
IXLCell CellNamePoint;
List <Point> points = new List <Point>();
Point curPoint;
int markOfPoint;
if (wrongtime1 <= duration || duration == wrongtime2)
{
duration = wrongtime2;
if (CellPoint.TryGetValue <int>(out markOfPoint))
{
CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint);
bool error = CellPoint.Style.Fill.BackgroundColor == XLColor.Red;
curPoint = new Point(CellNamePoint.GetString(), markOfPoint, error);
points.Add(curPoint);
}
}
CellPoint = CellDate.CellBelow().CellBelow().CellBelow().CellBelow();
string DealName = "";
string comment = page.Cell(corrRow, CellPoint.Address.ColumnNumber).GetString();
bool redComment = page.Cell(corrRow, CellPoint.Address.ColumnNumber).Style.Fill.BackgroundColor
== XLColor.Red ? true : false;
int maxMark;
page.Cell(corrRow - 3, CellPoint.Address.ColumnNumber).TryGetValue(out maxMark);
if (!CellPoint.TryGetValue <int>(out markOfPoint))
{
if (CellPoint.GetString() != "")
{
DealName = CellPoint.GetString();
}
}
else
{
CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint);
bool error = CellPoint.Style.Fill.BackgroundColor == XLColor.Red;
curPoint = new Point(CellNamePoint.GetString(), markOfPoint, error);
//int i = 0;
//while (page.Cell(CellPoint.Address.RowNumber - i, 1).GetString() == "")
//{
// i++;
//}
//curPoint.stageForBelfan = page.Cell(CellPoint.Address.RowNumber - i, 1).GetString();
curPoint.stageForBelfan = CellPoint.Address.RowNumber.ToString();
points.Add(curPoint);
}
CellPoint = CellPoint.CellBelow();
while (CellPoint.Address.RowNumber < corrRow - 4)
{
if (CellPoint.TryGetValue <int>(out markOfPoint))
{
CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint);
bool error = CellPoint.Style.Fill.BackgroundColor == XLColor.Red;
curPoint = new Point(CellNamePoint.GetString(), markOfPoint, error);
//int i = 0;
//while (page.Cell(CellPoint.Address.RowNumber - i,1).GetString() == "")
//{
// i++;
//}
//curPoint.stageForBelfan = page.Cell(CellPoint.Address.RowNumber - i, 1).GetString();
curPoint.stageForBelfan = CellPoint.Address.RowNumber.ToString();
points.Add(curPoint);
}
else
{
string answer = CellPoint.GetString().ToLower();
if (answer == "нет" || answer == "да")
{
CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint);
bool error = CellPoint.Style.Fill.BackgroundColor == XLColor.Red;
curPoint = new Point(CellNamePoint.GetString(), answer == "нет" ? 0 : 1, error, true);
curPoint.stageForBelfan = CellPoint.Address.RowNumber.ToString();
points.Add(curPoint);
}
}
CellPoint = CellPoint.CellBelow();
}
bool outgoing = true;
string Objections = "";
string howProcessObj = "";
string DealState = "";
string DateOfNext = "";
string doneObj = "";
if (curDate > new DateTime(2020, 5, 6))
{
Objections = page.Cell(corrRow + 2, CellPoint.Address.ColumnNumber).GetString();
howProcessObj = page.Cell(corrRow + 4, CellPoint.Address.ColumnNumber).GetString();
DealState = page.Cell(corrRow + 5, CellPoint.Address.ColumnNumber).GetString();
DateOfNext = page.Cell(corrRow + 6, CellPoint.Address.ColumnNumber).GetString();
doneObj = page.Cell(corrRow + 3, CellPoint.Address.ColumnNumber).GetString();
}
DateTime ddateNext;
if (DateOfNext != "")
{
if (DateTime.TryParse(DateOfNext, out ddateNext))
{
DateOfNext = ddateNext.ToString("dd.MM.yyyy");
}
}
if (Regex.Match(phoneNumber.ToUpper(), "ВХОДЯЩ").Success)
{
outgoing = false;
}
bool greenComment = page.Cell(corrRow, CellPoint.Address.ColumnNumber).Style.Fill.BackgroundColor
== XLColor.Lime ? true : false;
if (points.Count > 0)
{
calls.Add(new Call(phoneNumber, maxMark, duration, comment, DealName, points, redComment, curDate, outgoing, greenComment, Objections, howProcessObj, DealState, link, DateOfNext, doneObj));
}
}
CellDate = CellDate.CellRight();
}
stages.Add(new Stage(page.Name.Trim(), calls));
}
}
}
public void ParserCheckLists(IEnumerable <IFormFile> files)
{
using (var stream = files.First().OpenReadStream())
{
XLWorkbook wb = new XLWorkbook(stream);
FillStageDictionary(wb);
}
foreach (var file in files)
{
string Manager = Regex.Match(file.FileName, @"(\w+)").Groups[1].Value;
using (var stream = file.OpenReadStream())
{
XLWorkbook wb = new XLWorkbook(stream);
IXLWorksheet page = wb.Worksheets.First();
IXLCell cell = page.Cell(1, 5);
DateTime curDate;
if (cell.DataType == XLDataType.DateTime)
{
curDate = cell.GetDateTime();
}
else
{
if (!DateTime.TryParse(cell.GetString(), new CultureInfo("ru-RU"), DateTimeStyles.None, out curDate))
{
DateTime.TryParse(cell.GetString(), new CultureInfo("en-US"), DateTimeStyles.None, out curDate);
}
}
string phoneNumber;
IXLCell phoneCell;
while (!(cell.IsEmpty() && cell.CellRight().IsEmpty() && !cell.IsMerged()))
{
if (cell.GetString() != "")
{
if (cell.DataType == XLDataType.DateTime)
{
curDate = cell.GetDateTime();
}
else
{
if (!DateTime.TryParse(cell.GetString(), new CultureInfo("ru-RU"), DateTimeStyles.None, out curDate))
{
DateTime.TryParse(cell.GetString(), new CultureInfo("en-US"), DateTimeStyles.None, out curDate);
}
}
}
phoneCell = cell.CellBelow();
if (phoneCell.GetString() == "")
{
phoneCell = phoneCell.CellBelow();
}
if (phoneCell.GetString() != "")
{
string link;
if (phoneCell.HasHyperlink)
{
link = phoneCell.GetHyperlink().ExternalAddress.AbsoluteUri;
}
else
{
link = "";
}
Match outgoing = Regex.Match(phoneCell.GetString().ToUpper(), @"ИСХОДЯЩИЙ");
phoneNumber = Regex.Replace(phoneCell.GetString().ToUpper(), @"[^\d]", String.Empty);
string oldphonenum = phoneNumber;
oldphonenum = "8 (" + oldphonenum.Substring(1, 3) + ") " + oldphonenum.Substring(4, 3) + "-" + oldphonenum.Substring(7, 2) + "-" + oldphonenum.Substring(9);
while (phoneNumber[0] == '0')
{
phoneNumber = phoneNumber.Substring(1);
}
if (phoneNumber[0] == '9')
{
phoneNumber = '8' + phoneNumber;
}
if (phoneNumber[0] == '7' || phoneNumber[0] == '8')
{
phoneNumber = "8 (" + phoneNumber.Substring(1, 3) + ") " + phoneNumber.Substring(4, 3) + "-" + phoneNumber.Substring(7, 2) + "-" + phoneNumber.Substring(9);
}
if (processedCalls.Exists(c => c.Client == oldphonenum) && oldphonenum != phoneNumber)
{
var testCall = processedCalls.Where(c => c.Client == oldphonenum).First();
processedCalls.Remove(testCall);
testCall.Client = phoneNumber;
processedCalls.Add(testCall);
}
if (processedCalls.Exists(c => c.Client == phoneNumber && c.Link == ""))
{
var testCall = processedCalls.Where(c => c.Client == phoneNumber).First();
testCall.Link = link;
}
var CellStage = page.Cell("A5");
Regex rx = new Regex("ИТОГ");
int corrRow = 5;
Match Mcomment = Regex.Match(page.Cell(corrRow, 1).GetString().ToUpper(), @"КОРРЕКЦИИ");
while (!Mcomment.Success)
{
corrRow++;
Mcomment = Regex.Match(page.Cell(corrRow, 1).GetString().ToUpper(), @"КОРРЕКЦИИ");
}
while (!rx.Match(CellStage.GetString().ToUpper()).Success&& !rx.Match(CellStage.CellRight().CellRight().CellRight().GetString().ToUpper()).Success)
{
if (CellStage.GetString() != "" && page.Cell(CellStage.Address.RowNumber, cell.Address.ColumnNumber).GetString() != "")
{
var exCallSeq = processedCalls.Where(c => (c.Client == phoneNumber));
var exCall = new ProcessedCall();
if (exCallSeq.Count() > 0)
{
exCall = exCallSeq.First();
//exCall.StartDateAnalyze = curDate.AddDays(-1);
}
else
{
exCall.ClientState = "";
exCall.StartDateAnalyze = DateTime.MinValue;
}
if (curDate >= exCall.StartDateAnalyze ||
(
exCall.ClientState.ToUpper() == "В РАБОТЕ") &&
exCall.StartDateAnalyze < DateTime.Today.AddDays(1)
)
{
DateTime DateNext = new DateTime();
var NextContactCell = page.Cell(corrRow + 6, cell.Address.ColumnNumber);
if (NextContactCell.GetString() != "")
{
if (NextContactCell.DataType == XLDataType.DateTime)
{
DateNext = NextContactCell.GetDateTime();
}
else
{
if (!DateTime.TryParse(NextContactCell.GetString(), new CultureInfo("ru-RU"), DateTimeStyles.None, out DateNext))
{
DateTime.TryParse(NextContactCell.GetString(), new CultureInfo("en-US"), DateTimeStyles.None, out DateNext);
}
}
}
if (curDate > DateTime.Now.AddMonths(-1) && Regex.Match(file.Name, "Гакова|Малькова|Лукина|Кожевникова|Рыбачук", RegexOptions.IgnoreCase).Success)
{
phones.AddCall(new FullCall(phoneNumber, link, Regex.Replace(CellStage.GetString(), @"[\d()]", String.Empty).Trim(), curDate, outgoing.Success, page.Cell(corrRow, cell.Address.ColumnNumber).GetString(), Manager, page.Cell(corrRow + 5, cell.Address.ColumnNumber).GetString(), DateNext));
}
phonesForFirst.AddCall(new FullCall(phoneNumber, link, Regex.Replace(CellStage.GetString(), @"[\d()]", String.Empty).Trim(), curDate, outgoing.Success, page.Cell(corrRow, cell.Address.ColumnNumber).GetString(), Manager, page.Cell(corrRow + 5, cell.Address.ColumnNumber).GetString(), DateNext));
}
}
CellStage = CellStage.CellBelow();
}
}
cell = cell.CellRight();
}
phones.CleanSuccess(ref processedCalls);
}
}
}
public new void Processing()
{
XLWorkbook wb = new XLWorkbook(FilePath);
foreach (var page in wb.Worksheets)
{
if (page.Name.ToUpper().Trim() != "СТАТИСТИКА" && page.Name.ToUpper().Trim() != "СВОДНАЯ")
{
const int numColPoint = 4;
IXLCell CellDate = page.Cell(1, numColPoint + 1);
while (CellDate.GetString() == "" && CellDate.Address.ColumnNumber <= page.LastColumnUsed().ColumnNumber())
{
CellDate = CellDate.CellRight();
}
DateTime curDate;
//if (!DateTime.TryParse(CellDate.GetValue<string>(), out curDate))
//{
// CellDate = CellDate.CellAbove();
DateTime.TryParse(CellDate.GetValue <string>(), out curDate);
//}
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());
}
List <Call> calls = new List <Call>();
while (!(CellDate.CellBelow().IsEmpty() && CellDate.CellBelow().CellRight().IsEmpty()))
{
if (CellDate.GetValue <string>() != "")
{
DateTime.TryParse(CellDate.GetValue <string>(), out curDate);
}
string phoneNumber = CellDate.CellBelow().GetValue <string>();
var phoneCell = CellDate.CellBelow();
if (phoneNumber != "")
{
TimeSpan duration;
string link = "";
if (phoneCell.HasHyperlink)
{
link = phoneCell.Hyperlink.ExternalAddress.AbsoluteUri;
}
IXLCell CellPoint = CellDate.CellBelow().CellBelow().CellBelow();
if (CellPoint.DataType == XLDataType.DateTime)
{
CellPoint.DataType = XLDataType.TimeSpan;
}
TimeSpan.TryParse(CellPoint.GetString(), out duration);
IXLCell CellNamePoint;
List <Point> points = new List <Point>();
Point curPoint;
int markOfPoint;
CellPoint = CellPoint.CellBelow();
string DealName = "";
string comment = page.Cell(corrRow, CellPoint.Address.ColumnNumber).GetString();
bool redComment = page.Cell(corrRow, CellPoint.Address.ColumnNumber).Style.Fill.BackgroundColor
== XLColor.Red ? true : false;
var Color = page.Cell(corrRow, CellPoint.Address.ColumnNumber).Style.Fill.BackgroundColor;
bool greenComment = page.Cell(corrRow, CellPoint.Address.ColumnNumber).Style.Fill.BackgroundColor
== XLColor.Lime ? true : false;
int maxMark;
page.Cell(corrRow - 3, CellPoint.Address.ColumnNumber).TryGetValue(out maxMark);
if (!CellPoint.TryGetValue <int>(out markOfPoint))
{
if (CellPoint.GetString() != "")
{
DealName = CellPoint.GetString();
}
}
else
{
CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint);
bool error = CellPoint.Style.Fill.BackgroundColor == XLColor.Red;
curPoint = new Point(CellNamePoint.GetString(), markOfPoint, error);
curPoint.ColorForRNR = CellNamePoint.Style.Fill.BackgroundColor;
points.Add(curPoint);
}
CellPoint = CellPoint.CellBelow();
int weightPoint;
int numchl;
while (page.Cell(CellPoint.Address.RowNumber, 3).TryGetValue <int>(out numchl) || page.Cell(CellPoint.Address.RowNumber, 3).GetString() == "б\\н")
{
page.Cell(CellPoint.Address.RowNumber, 2).TryGetValue <int>(out weightPoint);
if (CellPoint.TryGetValue <int>(out markOfPoint))
{
CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint);
bool error = CellPoint.Style.Fill.BackgroundColor == XLColor.Red;
if (error)
{
}
curPoint = new Point(CellNamePoint.GetString(), markOfPoint, error);
curPoint.ColorForRNR = CellNamePoint.Style.Fill.BackgroundColor;
points.Add(curPoint);
}
CellPoint = CellPoint.CellBelow();
}
bool outgoing = true;
if (Regex.Match(page.Name.ToUpper(), "ВХОДЯЩ").Success)
{
outgoing = false;
}
string Objections = "";
string howProcessObj = "";
string DealState = "";
string DateOfNext = "";
string doneObj = "";
if (curDate > new DateTime(2020, 5, 6))
{
Objections = page.Cell(corrRow + 2, CellPoint.Address.ColumnNumber).GetString();
howProcessObj = page.Cell(corrRow + 4, CellPoint.Address.ColumnNumber).GetString();
DealState = page.Cell(corrRow + 5, CellPoint.Address.ColumnNumber).GetString();
DateOfNext = page.Cell(corrRow + 6, CellPoint.Address.ColumnNumber).GetString();
DateTime ddateNext;
if (DateOfNext != "")
{
if (DateTime.TryParse(DateOfNext, out ddateNext))
{
DateOfNext = ddateNext.ToString("dd.MM.yyyy");
}
}
doneObj = page.Cell(corrRow + 3, CellPoint.Address.ColumnNumber).GetString();
}
if (points.Count > 0)
{
var curCall = new Call(phoneNumber, maxMark, duration, comment, DealName, points, redComment, curDate, outgoing, greenComment, Objections, howProcessObj, DealState, link, DateOfNext, doneObj);
calls.Add(curCall);
var testt = curCall.getAVGPersent();
if (testt > 1)
{
}
}
}
CellDate = CellDate.CellRight();
}
stages.Add(new Stage(page.Name, calls));
}
}
}
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);
}
public new void Processing()
{
XLWorkbook wb = new XLWorkbook(FilePath);
foreach (var page in wb.Worksheets)
{
if (page.Name.ToUpper().Trim() != "СТАТИСТИКА" && page.Name.ToUpper().Trim() != "СВОДНАЯ")
{
const int numColPoint = 4;
IXLCell CellDate = page.Cell(2, numColPoint + 1);
DateTime curDate;
DateTime.TryParse(CellDate.GetValue <string>(), out curDate);
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());
}
List <Call> calls = new List <Call>();
while (!(CellDate.CellBelow().IsEmpty() && CellDate.CellBelow().CellRight().IsEmpty()))
{
if (CellDate.GetValue <string>() != "")
{
DateTime.TryParse(CellDate.GetValue <string>(), out curDate);
}
string phoneNumber = CellDate.CellBelow().GetValue <string>();
if (phoneNumber != "")
{
TimeSpan duration;
IXLCell CellPoint = CellDate.CellBelow().CellBelow();
if (CellPoint.DataType == XLDataType.DateTime)
{
CellPoint.DataType = XLDataType.TimeSpan;
}
TimeSpan.TryParse(CellPoint.GetString(), out duration);
IXLCell CellNamePoint;
List <Point> points = new List <Point>();
Point curPoint;
int markOfPoint;
CellPoint = CellDate.CellBelow().CellBelow().CellBelow();
string DealName = "";
string comment = page.Cell(corrRow, CellPoint.Address.ColumnNumber).GetString();
bool redComment = page.Cell(corrRow, CellPoint.Address.ColumnNumber).Style.Fill.BackgroundColor
== XLColor.Red ? true : false;
int maxMark;
page.Cell(corrRow - 3, CellPoint.Address.ColumnNumber).TryGetValue(out maxMark);
if (!CellPoint.TryGetValue <int>(out markOfPoint))
{
if (CellPoint.GetString() != "")
{
DealName = CellPoint.GetString();
}
}
else
{
CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint);
bool error = CellPoint.Style.Fill.BackgroundColor == XLColor.Red;
curPoint = new Point(CellNamePoint.GetString(), markOfPoint, error);
points.Add(curPoint);
}
CellPoint = CellPoint.CellBelow();
while (CellPoint.Address.RowNumber < corrRow - 4)
{
if (CellPoint.TryGetValue <int>(out markOfPoint))
{
CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint);
bool error = CellPoint.Style.Fill.BackgroundColor == XLColor.Red;
curPoint = new Point(CellNamePoint.GetString(), markOfPoint, error);
points.Add(curPoint);
}
CellPoint = CellPoint.CellBelow();
}
bool outgoing = true;
if (Regex.Match(page.Name.ToUpper(), "ВХОДЯЩ").Success)
{
outgoing = false;
}
if (points.Count > 0)
{
calls.Add(new Call(phoneNumber, maxMark, duration, comment, DealName, points, redComment, curDate, outgoing));
}
}
CellDate = CellDate.CellRight();
}
stages.Add(new Stage(page.Name, calls));
}
}
}