/// <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(); }
private void AddHeaderMonth(IXLCell startCell, string monthName) { startCell.DataType = XLDataType.Text; startCell.Value = monthName; startCell.CellRight().DataType = XLDataType.Text; startCell.CellRight().Value = INVALUE_HEADER; startCell.CellRight().CellRight().DataType = XLDataType.Text; startCell.CellRight().CellRight().Value = INVALUE_HEADER; }
public IList <House> GetHouses(string path) { XLWorkbook wb = new XLWorkbook(path); IXLWorksheet sheet = wb.Worksheets.First(); IXLRange RangeSheet = sheet.RangeUsed(); int rightBorder = RangeSheet.LastColumn().ColumnNumber(); //установим правую границу данных int downBorder = RangeSheet.LastRow().RowNumber(); //установим нижнюю границу данных List <IXLCell> Xlhouses = sheet.Cells().Where(c => c.GetValue <string>().Contains("Дом")).ToList(); //получим ячейки, в которых номера домов List <House> houses = new List <House>(); foreach (var Xlhouse in Xlhouses) { House house = new House(); house.Name = Xlhouse.GetValue <string>(); house.Flats = new List <Flat>(); IXLCell cell = Xlhouse.CellBelow(); while (!(cell.GetValue <string>() == "" && cell.CellBelow().GetValue <string>() == "" && cell.CellRight().GetValue <string>() == "" && cell.CellRight().CellBelow().GetValue <string>() == "" ) && cell.WorksheetColumn().ColumnNumber() <= rightBorder ) //рассматриваем, есть ли значение в ячейке под названием дома, а также в ближайших от нее соседей { IXLCell cellBellowHouse = cell; while (!(cell.GetValue <string>() == "" && cell.CellBelow().GetValue <string>() == "" ) && cell.WorksheetRow().RowNumber() <= downBorder ) //смотрим есть ли в текущей ячейке значение или той, которая находится снизу { if (cell.GetValue <string>().Contains("№")) { Flat flat = new Flat(); flat.Number = cell.GetValue <string>().Substring(1); flat.Price = cell.CellBelow().GetValue <string>(); house.Flats.Add(flat); } cell = cell.CellBelow(); } cell = cellBellowHouse.CellRight(); //после того, как закончили с колонкой, переходим в следующую колонку //самую верхнюю под номером дома } houses.Add(house); } return(houses); //throw new NotImplementedException(); }
/// <summary> /// 書込み /// </summary> /// <param name="workCell"></param> /// <param name="dataList"></param> /// <param name="color"></param> private static IXLCell writeData(IXLCell workCell, List <object> dataList, bool color) { try { dataList.ForEach(x => { // 書込み workCell.Value = x; // 見た目 if (color) { workCell.Style.Fill.BackgroundColor = XLColor.LightGreen; } workCell.Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); // 作業セルを右に workCell = workCell.CellRight(); }); return(workCell); } catch (Exception ex) { throw ex; } }
/// <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 IXLCell WriteGroupCell(IXLCell beginGroup, MAINDATASETGROUP[] groups, int indexGroup, Dictionary <string, int> fieldIndexes, IXLCell cell) { var indexGroupCell = fieldIndexes[groups[indexGroup].GroupField]; var groupCell = cell.CellRight(indexGroupCell); groupCell.Style.Font.Bold = true; foreach (var field in groups[indexGroup].FIELD) { var indexFieldCell = fieldIndexes[field.index]; var fieldCell = cell.CellRight(indexFieldCell); var subTotalOperation = this.SubTotalOperation(field.function); fieldCell.FormulaA1 = $"SUBTOTAL({subTotalOperation},{beginGroup.CellRight(indexFieldCell).Address}:{fieldCell.CellAbove().Address})"; fieldCell.Style.Font.Bold = true; } return(groupCell); }
private void WriteRow(IXLCell startCell, int row, double inValue, double outValue) { startCell.DataType = XLDataType.Text; startCell.Value = row; startCell.CellRight().DataType = XLDataType.Text; if (inValue > 0) { startCell.CellRight().Value = inValue.ToString("0"); } startCell.CellRight().CellRight().DataType = XLDataType.Text; if (outValue > 0) { startCell.CellRight().CellRight().Value = outValue.ToString("0"); } }
public IXLCell PreencherRiscosAmbiente(IXLWorksheet planilha, RISCO_INVENTARIO_AMBIENTE riscosAmbiente, IXLCell celulaAPreencher) { var coluna = celulaAPreencher.Address.ColumnLetter; var risco = riscoPersistencia.ListarRiscoPorId(riscosAmbiente.CodRiscoAmbiente); planilha.Cell($"{celulaAPreencher.Address.ColumnLetter}{celulaAPreencher.Address.RowNumber}").Value = risco.Nome; celulaAPreencher = celulaAPreencher.CellRight(); coluna = celulaAPreencher.Address.ColumnLetter; var severidade = severidadePersistencia.ListarSeveridadePorId(riscosAmbiente.CodSeveridade); planilha.Cell($"{celulaAPreencher.Address.ColumnLetter}{celulaAPreencher.Address.RowNumber}").Value = severidade.Nome; celulaAPreencher = celulaAPreencher.CellRight(); coluna = celulaAPreencher.Address.ColumnLetter; var probabilidade = probabilidadePersistencia.ListarProbabilidadePorId(riscosAmbiente.CodProbabilidade); planilha.Cell($"{celulaAPreencher.Address.ColumnLetter}{celulaAPreencher.Address.RowNumber}").Value = probabilidade.Nome; celulaAPreencher = celulaAPreencher.CellRight(); coluna = celulaAPreencher.Address.ColumnLetter; planilha.Cell($"{celulaAPreencher.Address.ColumnLetter}{celulaAPreencher.Address.RowNumber}").Value = riscosAmbiente.FonteGeradora; celulaAPreencher = celulaAPreencher.CellRight(); coluna = celulaAPreencher.Address.ColumnLetter; planilha.Cell($"{celulaAPreencher.Address.ColumnLetter}{celulaAPreencher.Address.RowNumber}").Value = riscosAmbiente.ProcedimentosAplicaveis; celulaAPreencher = celulaAPreencher.CellRight(); // preencher EPI if (riscosAmbiente.EPI_RISCO_INVENTARIO_AMBIENTE.Count > 0) { string nomeEpi = ""; coluna = celulaAPreencher.Address.ColumnLetter; foreach (var item in riscosAmbiente.EPI_RISCO_INVENTARIO_AMBIENTE) { var epi = epiPersistencia.ListarEPIPorId(item.CodEPI); nomeEpi += $"{epi.Nome};"; } var tamanhoEpi = nomeEpi.Length; var nomeEpiCorrigido = nomeEpi.Substring(0, tamanhoEpi - 1); planilha.Cell($"{celulaAPreencher.Address.ColumnLetter}{celulaAPreencher.Address.RowNumber}").Value = nomeEpiCorrigido; } celulaAPreencher = celulaAPreencher.CellRight(); coluna = celulaAPreencher.Address.ColumnLetter; planilha.Cell($"{celulaAPreencher.Address.ColumnLetter}{celulaAPreencher.Address.RowNumber}").Value = riscosAmbiente.ContraMedidas; celulaAPreencher = celulaAPreencher.CellRight(); celulaAPreencher = celulaAPreencher.CellRight(); return(celulaAPreencher); }
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(); } }
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); }
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); }
private IXLCell AddValues(int level, TrackedTimeNode row, IXLCell currentCell, string teamMember, double?wiTotalDurationInMin, double?wiDirectDurationInMin, DateTime?recordDate) { var firstCell = currentCell; currentCell.Value = row.WorkItemId; currentCell = currentCell.CellRight().SetValue(row.Project); currentCell = currentCell.CellRight().SetValue(row.WorkItemType); currentCell = currentCell.CellRight().SetValue(level >= 2 ? row.ParentId : null); currentCell = currentCell.CellRight().SetValue(level >= 3 ? row.ParentId : null); currentCell = currentCell.CellRight().SetValue(level >= 4 ? row.ParentId : null); currentCell = currentCell.CellRight().SetValue(level > 4 ? row.ParentId : null); string indent = ""; if (level > 1) { indent = string.Concat(Enumerable.Repeat("║ ", Math.Max(0, level - 2))) + "╠ "; // for a "tree" visualization } currentCell = currentCell.CellRight().SetValue(indent + row.Title); currentCell = currentCell.CellRight().SetValue(level == 1 ? wiTotalDurationInMin : null); currentCell = currentCell.CellRight().SetValue(level == 2 ? wiTotalDurationInMin : null); currentCell = currentCell.CellRight().SetValue(level == 3 ? wiTotalDurationInMin : null); currentCell = currentCell.CellRight().SetValue(level >= 4 ? wiTotalDurationInMin : null); currentCell = currentCell.CellRight().SetValue(wiDirectDurationInMin / 60d); currentCell = currentCell.CellRight().SetValue(recordDate); currentCell = currentCell.CellRight().SetValue(teamMember); if (showReportsByMonth) { var currentMonth = FirstMonth; while (currentMonth <= Lastmonth) { currentCell = currentCell.CellRight(); double?durationInMin = null; if (wiDirectDurationInMin == null) { // global WorkItem (not a time tracked record) durationInMin = row.GetTotalDurationWithChildrenInMinForMonth(currentMonth); } else if (recordDate.HasValue && recordDate.Value.Year == currentMonth.Year && recordDate.Value.Month == currentMonth.Month) { // time tracked record which is in the current month durationInMin = wiDirectDurationInMin; } if (durationInMin != null && durationInMin.Value > 0) { currentCell.SetValue(durationInMin / 60d); } currentMonth = currentMonth.AddMonths(1); } } var range = worksheet.Range(firstCell, currentCell); if (level == 1 && wiDirectDurationInMin == null) // 1stl level WorkItem (not a time tracked record) { range.Style.Fill.BackgroundColor = XLColor.FromArgb(216, 228, 188); } else if (level > 1 && wiDirectDurationInMin == null) // next level WorkItem (not a time tracked record) { range.Style.Fill.BackgroundColor = XLColor.FromArgb(235, 241, 222); } else { // wiDirectDurationInMin != null (time tracked record) range.Style.Fill.BackgroundColor = XLColor.White; } return(currentCell); }
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 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 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 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 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(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)); } } }
public void ExportStockInfo(List <DetailedStockReportInfo> items, DateTime startDate, DateTime endDate, string path) { items.Sort((a, b) => (a.Item.Name + a.Item.Description).ToLower().CompareTo((b.Item.Name + b.Item.Description).ToLower())); var startDateString = startDate.ToString(Utilities.DateTimeToFriendlyFullDateTimeStringFormat()); var endDateString = endDate.ToString(Utilities.DateTimeToFriendlyFullDateTimeStringFormat()); using (var workbook = new XLWorkbook()) { var worksheet = workbook.Worksheets.Add("Stock Info"); worksheet.Cell("A1").Value = "SimpleInventory -- Stock Info Report for Sold Items"; worksheet.Cell("A1").Style.Font.Bold = true; worksheet.Cell("A2").Value = startDateString + " - " + endDateString; // table headers worksheet.Cell("A4").SetValue("Name").Style.Font.SetBold(true); worksheet.Cell("B4").SetValue("Description").Style.Font.SetBold(true); worksheet.Cell("C4").SetValue("Beginning Stock (Computer)").Style.Font.SetBold(true); worksheet.Cell("D4").SetValue("Ending Stock (Computer)").Style.Font.SetBold(true); worksheet.Cell("E4").SetValue("Ending Stock (Manual Entry)").Style.Font.SetBold(true); worksheet.Cell("F4").SetValue("Computer Difference").Style.Font.SetBold(true); worksheet.Cell("G4").SetValue("Manual Difference").Style.Font.SetBold(true); worksheet.Cell("H4").SetValue("Stock Difference").Style.Font.SetBold(true); worksheet.Cell("I4").SetValue("Item Cost").Style.Font.SetBold(true); worksheet.Cell("J4").SetValue("Cost Difference (Missing Items)").Style.Font.SetBold(true); worksheet.Cell("K4").SetValue("Cost Difference (Extra Items)").Style.Font.SetBold(true); // start exporting data var currentCell = worksheet.Cell("A5"); var lastRow = currentCell.WorksheetRow(); IXLCell firstCellWithData = null; // TODO: adjust formulas with string.Format() rather than string concat foreach (DetailedStockReportInfo item in items) { lastRow = currentCell.WorksheetRow(); if (firstCellWithData == null) { firstCellWithData = currentCell; } currentCell.Value = item.Item.Name; currentCell.CellRight(1).Value = item.Item.Description; currentCell.CellRight(2).Value = item.StartStockWithPurchaseStockIncrease; currentCell.CellRight(3).Value = item.EndStock; // computer currentCell.CellRight(4).Value = ""; // manual entry currentCell.CellRight(4).AddConditionalFormat() .WhenEquals("\"\"") .Fill.SetBackgroundColor(XLColor.Yellow); // if data not entered, highlight that work needs to happen!! currentCell.CellRight(5).FormulaA1 = "=SUM(-" + currentCell.CellRight(2).Address.ToStringFixed() + "," + currentCell.CellRight(3).Address.ToStringFixed() + ")"; // computer diff currentCell.CellRight(6).FormulaA1 = "=IF(" + currentCell.CellRight(3).Address.ToStringFixed() + "=\"\", \"-\", " + "SUM(-" + currentCell.CellRight(2).Address.ToStringFixed() + "," + currentCell.CellRight(4).Address.ToStringFixed() + "))"; // manual diff currentCell.CellRight(5).AddConditionalFormat() .WhenNotEquals("=" + currentCell.CellRight(6).Address.ToStringFixed()) .Fill.SetBackgroundColor(XLColor.LightPink); currentCell.CellRight(6).AddConditionalFormat() .WhenNotEquals("=" + currentCell.CellRight(5).Address.ToStringFixed()) .Fill.SetBackgroundColor(XLColor.LightPink); currentCell.CellRight(7).SetFormulaA1("=ABS(SUM(" + currentCell.CellRight(5).Address.ToStringFixed() + ", -" + currentCell.CellRight(6).Address.ToStringFixed() + "))").AddConditionalFormat() .WhenNotEquals("0") .Fill.SetBackgroundColor(XLColor.LightPink); // stock difference currentCell.CellRight(8).Value = item.Item.Cost; // item cost // first sum column is items that have less in real life than in the computer string formula = string.Format("=IF({0}<>\"\", IF({1}>{2},{3}*{4},\"\"), \"\")", currentCell.CellRight(4).Address.ToStringFixed(), // ending stock manual entry currentCell.CellRight(3).Address.ToStringFixed(), // ending stock computer currentCell.CellRight(4).Address.ToStringFixed(), // ending stock manual entry currentCell.CellRight(7).Address.ToStringFixed(), // stock diff currentCell.CellRight(8).Address.ToStringFixed() // item cost ); currentCell.CellRight(9).SetFormulaA1(formula); // cost difference for missing items // second sum column is items that have more in real life than in the computer formula = string.Format("=IF({0}<>\"\", IF({1}<{2},{3}*{4},\"\"), \"\")", currentCell.CellRight(4).Address.ToStringFixed(), // ending stock manual entry currentCell.CellRight(3).Address.ToStringFixed(), // ending stock computer currentCell.CellRight(4).Address.ToStringFixed(), // ending stock manual entry currentCell.CellRight(7).Address.ToStringFixed(), // stock diff currentCell.CellRight(8).Address.ToStringFixed() // item cost ); currentCell.CellRight(10).SetFormulaA1(formula); // cost difference for extra items // if item count is equal, doesn't add to either column if (currentCell.WorksheetRow().RowNumber() % 2 == 0) { currentCell.WorksheetRow().Style.Fill.BackgroundColor = XLColor.LightGray; } // if you add more data columns make sure to adjust print area!!! // go to next row currentCell = currentCell.CellBelow(); } // add cost discrepency if (items.Count > 0) { currentCell.CellRight(8).SetValue("Cost Discrepency").Style.Font.SetBold(true); currentCell.CellRight(9).SetFormulaA1("=SUM(" + firstCellWithData.CellRight(9).Address.ToStringFixed() + ":" + currentCell.CellAbove(1).CellRight(9).Address.ToStringFixed() + ")").Style.Font.SetBold(true); currentCell.CellRight(10).SetFormulaA1("=SUM(" + firstCellWithData.CellRight(10).Address.ToStringFixed() + ":" + currentCell.CellAbove(1).CellRight(10).Address.ToStringFixed() + ")").Style.Font.SetBold(true); } //// auto fit width worksheet.Columns().AdjustToContents(4, 4, 10, 25); // set print area worksheet.PageSetup.PrintAreas.Clear(); var firstCellForPrinting = worksheet.Cell("A1"); var lastCellForPrinting = items.Count > 0 ? currentCell.CellRight(10) : worksheet.Cell("J4"); worksheet.PageSetup.PrintAreas.Add(firstCellForPrinting.Address.ToStringRelative() + ":" + lastCellForPrinting.Address.ToStringRelative()); worksheet.PageSetup.SetRowsToRepeatAtTop("4:4"); worksheet.PageSetup.PagesWide = 1; worksheet.PageSetup.PageOrientation = XLPageOrientation.Landscape; workbook.SaveAs(path); Process.Start(path); } }