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