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