public TempDrawingsStorage ConvertExcelProjectToDomainModel(ExcelProject excelProject)
{
var drawSotrage = new TempDrawingsStorage();
var project = new Drawing { Count = 1, CountAll = 1 };
LoadFromProject(excelProject, project, drawSotrage);
return drawSotrage;
}
public string GetDesignation(ExcelProject position)
{
var des = position.GetField(3) == null ? string.Empty : position.GetField(3).ToString().Trim();
if (des.Contains("\\") || des.Contains("/"))
return String.Empty;
return des;
}
private double RecalculateWeigthsChildrens(ExcelProject excelProject)
{
var weigth = 0d;
var count = 0;
var countAll = 0;
if (excelProject.Positions.Count > 0)
{
foreach (var position in excelProject.Positions)
{
weigth += RecalculateWeigthsChildrens(position);
}
}
else
{
if (CheckFillObject(excelProject.Row[15]))
{
weigth = TryConvertToDouble(excelProject.Row[15].ToString());
}
}
if (CheckFillObject(excelProject.Row[13]))
{
count = (int)TryConvertToDouble(excelProject.Row[13].ToString());
}
if (CheckFillObject(excelProject.Row[14]))
{
countAll = (int)TryConvertToDouble(excelProject.Row[14].ToString());
}
excelProject.Row[15] = weigth.ToString("n3");
excelProject.Row[16] = (countAll * weigth).ToString("n3");
return weigth * count;
}
public string GetNumberBySpec(ExcelProject position)
{
var spec = position.GetField(2) == null ? string.Empty : position.GetField(2).ToString().Trim();
return spec;
}
public void ClearUnusableInfoAll(ExcelProject excelProject)
{
ClearUnusableInfo(excelProject);
foreach (var position in excelProject.Positions)
{
ClearUnusableInfoAll(position);
}
}
public void ConvertInfoAbooutZagToAll(ExcelProject excelProject)
{
ConvertInfoAbooutZag(excelProject);
foreach (var position in excelProject.Positions)
{
ConvertInfoAbooutZagToAll(position);
}
}
public int? GetLength(ExcelProject position)
{
var sep = position.GetField(11) == null ? string.Empty : position.GetField(11).ToString().Trim();
if (!string.IsNullOrWhiteSpace(sep))
{
return Convert.ToInt32(sep);
}
return null;
}
public TempDrawingsStorage ConvertExcelToDrawings(ExcelProject project, ILogging logger)
{
var converter = new ConverterExcelProject(logger);
var parser = _toolsFactory.GetExcelMaterialParser();
var excluder = _toolsFactory.GetProjectExcluder();
var searcher = _toolsFactory.GetNameMaterialSearch();
var normalizer = _toolsFactory.GetExcelFieldsNormalizer();
var standartizer = _toolsFactory.GetStandartizez();
converter.DrawingExcluder = excluder;
converter.Log = logger;
converter.ExcelMaterialParser = parser;
converter.NameMaterialSearch = searcher;
converter.Normalizer = normalizer;
converter.DrawingStandartezer = standartizer;
return converter.ConvertExcelProjectToDomainModel(project);
}
private void ExportToExcel(ExcelProject excel, string folder, string fileName, string fileType)
{
var file = Path.GetFileName(fileName);
ExcelPackage excelPack;
var count = 0;
while (true)
{
var fullPath = Path.Combine(folder, fileType + (count == 0 ? "" : count.ToString()) + file);
count++;
if (!File.Exists(fullPath))
{
excelPack = new ExcelPackage(new FileInfo(fullPath));
break;
}
}
_row = 1;
excelPack.Workbook.Worksheets.Add("AAA");
var ws = excelPack.Workbook.Worksheets[1];
ExportToExcel(excel, ws, "", 1);
excelPack.Save();
}
public void ClearUnusableInfo(ExcelProject excelProject)
{
if (CheckFillObject(excelProject.Row[9]))
{
if (Normalizer != null)
{
var str = excelProject.Row[9].ToString();
excelProject.Row[9] = Normalizer.RemoveUnUseInfoAboutMarkSteal(str);
}
}
if (CheckFillObject(excelProject.Row[8]))
{
if (Normalizer != null)
{
var str = excelProject.Row[8].ToString();
excelProject.Row[8] = Normalizer.SetEmptySpacesGost(str);
}
}
}
public string GetGOSTS(ExcelProject position)
{
return position.GetField(8) == null ? string.Empty : position.GetField(8).ToString().Trim();
}
public void MergeFiles(List<string> files, string folder)
{
var excelProjects = files.Select(file => new ExcelProjectLoaderWithoutHeader(new FileGetter() {FileName = file})).Select(excelLoader => excelLoader.LoadStructureProject()).ToList();
var mergedExcelProject = new ExcelProject
{
Row = new object[18],
Positions = excelProjects
};
mergedExcelProject.Row[13] = 1;
mergedExcelProject.Row[14] = 1;
ExportToExcel(mergedExcelProject, folder, "Объединённый файл.xlsx", string.Empty);
var sss = new RollUpElectroExcelProject();
sss.RecalculateWeigtsWithAllChildrens(mergedExcelProject);
ExportToExcel(mergedExcelProject, folder, "Пересчитанный объединённый файл.xlsx", string.Empty);
}
private void ExportToExcel(ExcelProject excel, ExcelWorksheet ws, string num, int levelCount)
{
var listRowNumber = new List<int>();
var count = 1;
var currRow = _row;
foreach (var o in excel.Row)
{
ws.Cells[_row, count].Value = ConvertToNumber(o);
if (UseFormulaToParentsValues)
{
if (count == 17)
{
ws.Cells[_row, count].Value = null;
ws.Cells[_row, count].Formula = ws.Cells[_row, 15].Address + "*" + ws.Cells[_row, 16].Address;
}
}
count++;
}
ws.Cells[_row, 2].Value = num + levelCount + ".";
_row++;
var chCount = 1;
foreach (var excelProject in excel.Positions)
{
listRowNumber.Add(_row);
ExportToExcel(excelProject, ws, num + levelCount + ".", chCount++);
}
if (excel.Positions.Count > 0)
{
ws.Cells[currRow, 1, currRow, count].Style.Font.Bold = true;
ws.Cells[currRow, 1, currRow, count].Style.Font.Size += 1;
var formula = "=";
if (UseFormulaToParentsValues)
{
ws.Cells[currRow, 16].Formula = "";
var delim = "";
foreach (var child in listRowNumber)
{
ws.Cells[currRow, 16].Formula += delim + ws.Cells[child, 14].Address + "*" + ws.Cells[child, 16].Address;
delim = "+";
}
ws.Cells[currRow, 16].Formula += "";
}
Debug.WriteLine(formula);
}
}
protected override List<ExcelProject> GetListRows(string fileName)
{
var stream = File.OpenRead(fileName);
var excelPack = new ExcelPackage();
excelPack.Load(stream);
stream.Close();
var listRows = new List<ExcelProject>();
for (int i = 1; i <= excelPack.Workbook.Worksheets[1].Dimension.Rows; i++)
{
var structure = new ExcelProject();
var dataRow = excelPack.Workbook.Worksheets[1].Cells[i, 1, i, 18];
structure.Row = new object[18];
for (int j = 0; j < 18; j++)
{
structure.Row[j] = ((object[,])dataRow.Value)[0, j];
}
listRows.Add(structure);
}
return listRows;
}
private async void wizardControl1_NextClick(object sender, WizardCommandButtonClickEventArgs e)
{
if (e.Page == welcomeWizardPage)
{
wpPreviewPackSiemens.Enabled = false;
rtbPreviewLoad.Text = string.Empty;
var logger = new Logger();
ExcelProject project = null;
var task = new Task(() => { project = Controller.LoadStructureExcel(btnPath.Text, logger); });
waitPanel.Visible = true;
wpPreviewPackSiemens.AllowNext = false;
wpPreviewPackSiemens.AllowBack = false;
wpPreviewPackSiemens.AllowCancel = false;
task.Start();
await task;
waitPanel.Visible = false;
wpPreviewPackSiemens.AllowBack = true;
wpPreviewPackSiemens.AllowCancel = true;
wpPreviewPackSiemens.Enabled = true;
foreach (var mess in logger.Messages)
{
rtbPreviewLoad.AppendText(string.Format("{0}\n\r", mess.Message));
}
if (!logger.ErrorHappens)
{
_project = project;
wpPreviewPackSiemens.AllowNext = true;
rtbPreviewLoad.Text += rtbPreviewLoad.Text.Length == 0
? "Загрузка прошла успешно, ошибок нет!\nМожно переходить к следующему этапу."
: "Есть предупреждения. Переходите к следующему этапу, если вы игнорируете их.";
}
}
if (e.Page == wpPreviewPackSiemens)
{
wpConvertedDataView.AllowNext = false;
wpConvertedDataView.AllowBack = false;
wpConvertedDataView.AllowCancel = false;
waitPanelConversion.Visible = true;
var logger = new Logger();
var task = new Task(() => { _storage = Controller.ConvertExcelToDrawings(_project, logger); });
task.Start();
await task;
waitPanelConversion.Visible = false;
if (_storage != null)
{
tlDarwings.DataSource = _storage.Drawings;
}
lbMessages.DataSource =
logger.Messages.Select(mess => new MessageListBox {Tag = mess.Tag, Message = mess.Message}).ToList();
waitPanelConversion.Visible = false;
wpConvertedDataView.AllowNext = true;
wpConvertedDataView.AllowBack = true;
wpConvertedDataView.AllowCancel = true;
SetVisibleLevelsLine();
}
}
public void ConvertSize(ExcelProject excelProject)
{
if (CheckFillObject(excelProject.Row[11]))
{
if (IncludeDebugInfo)
{
if (excelProject.Row.Length == CountColumnRowExcelDk)
{
var arr = new object[CountColumnRowExcelDk + 2];
excelProject.Row.CopyTo(arr, 0);
excelProject.Row = arr;
}
excelProject.Row[18] = excelProject.Row[11];
}
var str = excelProject.Row[11].ToString().ToLowerInvariant().Trim().Replace("ф", string.Empty).Replace("х", "x");
var regFlance = new Regex(@"[0-9]+(\/|\\)[0-9]+");
var regSort = new Regex(@".*\)");
var regPiece = new Regex(@"\/.*");
if (regSort.IsMatch(str))
{
var split = regSort.Replace(str, "").Split('x');
excelProject.Row[11] = split[1];
}
else
{
if (str.Count(x => x == 'x') == 1 && regFlance.IsMatch(str))
{
var match = regFlance.Match(str);
var split = match.Value.Split('/', '\\');
excelProject.Row[11] = split[0];
excelProject.Row[12] = split[0];
return;
}
else
{
if (str.Count(x => x == 'x') == 1)
{
var split = str.Split('x');
excelProject.Row[11] = split[1];
}
if (str.Count(x => x == 'x') == 2)
{
var split = str.Split('x');
excelProject.Row[11] = regPiece.Replace(split[1], "");
excelProject.Row[12] = regPiece.Replace(split[2], "");
}
}
}
}
foreach (var position in excelProject.Positions)
{
ConvertSize(position);
}
}
private void RecalculateWeigths(ExcelProject excelProject)
{
if (CheckFillObject(excelProject.Row[14]) && CheckFillObject(excelProject.Row[15]))
{
var valueWeigth = TryConvertToDouble(excelProject.Row[15].ToString());
var valueCount = TryConvertToDouble(excelProject.Row[14].ToString());
if (valueWeigth > 0 && valueCount > 0)
{
excelProject.Row[16] = (valueCount * valueWeigth).ToString("n3");
excelProject.Row[15] = valueWeigth.ToString("n3");
}
}
}
public string GetStandartSize(ExcelProject position)
{
return position.GetField(6) == null ? string.Empty : position.GetField(6).ToString().Trim();
}
public void RecalculateWeigthsAll(ExcelProject excelProject)
{
RecalculateWeigths(excelProject);
foreach (var position in excelProject.Positions)
{
RecalculateWeigthsAll(position);
}
}
public void ConvertInfoAbooutZag(ExcelProject excelProject)
{
if (CheckFillObject(excelProject.Row[5]))
{
if (IncludeDebugInfo)
{
if (excelProject.Row.Length == CountColumnRowExcelDk)
{
var arr = new object[CountColumnRowExcelDk + 2];
excelProject.Row.CopyTo(arr, 0);
excelProject.Row = arr;
}
excelProject.Row[19] = excelProject.Row[5];
}
var profile = string.Empty;
var standartSize = string.Empty;
var gostSort = string.Empty;
var markSteal = string.Empty;
var regGetProfile = new Regex("^[А-Я]*");
var regGetStSize = new Regex(@"^[0-9,\.А-ЯA-Z-]*");
var regGetGostSort = new Regex(@"^[А-Я]*([ ]|)[0-9,\.А-ЯA-Z-]*");
var regGetMarkSteal = new Regex(@"^[0-9,\.А-ЯA-Z-]*");
var str = excelProject.Row[5].ToString().ToUpperInvariant().Trim().Replace("/", string.Empty).Replace("\\", string.Empty);
if (regGetProfile.IsMatch(str))
{
var match = regGetProfile.Match(str);
profile = match.Value;
str = str.Substring(profile.Length).Trim();
}
if (regGetStSize.IsMatch(str))
{
var match = regGetStSize.Match(str);
standartSize = match.Value;
str = str.Substring(standartSize.Length).Trim();
}
if (regGetGostSort.IsMatch(str))
{
var match = regGetGostSort.Match(str);
gostSort = match.Value;
str = str.Substring(gostSort.Length).Trim();
}
if (regGetMarkSteal.IsMatch(str))
{
var match = regGetMarkSteal.Match(str);
markSteal = match.Value;
str = str.Substring(markSteal.Length).Trim();
}
excelProject.Row[5] = profile;
excelProject.Row[6] = standartSize;
excelProject.Row[8] = gostSort;
excelProject.Row[9] = markSteal;
}
}
public void RecalculateWeigtsWithAllChildrens(ExcelProject excelProject)
{
excelProject.Row[15] = RecalculateWeigthsChildrens(excelProject).ToString("n3");
}
private ExcelProject ConvertElectroExcelToDkExcel(ElectroExcelProject electroExcelProject)
{
var dkExcelRow = new ExcelProject();
dkExcelRow.Row = new object[CountColumnRowExcelDk];
dkExcelRow.Row[2] = electroExcelProject.GetField(Field.NumberBySpec); // pos po spec
dkExcelRow.Row[3] = electroExcelProject.GetField(Field.Designation); // pos po spec
dkExcelRow.Row[4] = electroExcelProject.GetField(Field.Name); // pos po spec
dkExcelRow.Row[11] = electroExcelProject.GetField(Field.Size); // размеры пока в длину
dkExcelRow.Row[13] = electroExcelProject.GetField(Field.CountByOne); // кол-во на одну
dkExcelRow.Row[14] = electroExcelProject.GetField(Field.CountByAll); // кол-во на все
if (electroExcelProject.Positions.Count == 1 && electroExcelProject.Positions[0].Positions.Count == 0)
{
dkExcelRow.Row[5] = electroExcelProject.Positions[0].GetField(Field.InfoAboutZag); // profile (тащим из дитяти если есть)
dkExcelRow.Row[15] = electroExcelProject.Positions[0].GetField(Field.WeigthByOne); // вес на одну (тащим из дитяти если есть)
dkExcelRow.Row[16] = electroExcelProject.Positions[0].GetField(Field.WeigthByAll); // вес на все (тащим из дитяти если есть)
dkExcelRow.Row[11] = electroExcelProject.Positions[0].GetField(Field.Size);
if (electroExcelProject.Positions[0].GetIndexField(Field.ClearWeigthDetail) != -1)
{
dkExcelRow.Row[15] = electroExcelProject.Positions[0].GetField(Field.ClearWeigthDetail);
}
}
else
{
foreach (var excelProject in electroExcelProject.Positions)
{
dkExcelRow.Positions.Add(ConvertElectroExcelToDkExcel(excelProject));
}
}
var swert = dkExcelRow.Positions.FirstOrDefault(x => x.Row[3].ToString().ToLowerInvariant().EndsWith("д"));
if (swert != null)
{
if (dkExcelRow.Row[3].ToString().ToLowerInvariant() ==
swert.Row[3].ToString()
.ToLowerInvariant()
.Substring(0, swert.Row[3].ToString().ToLowerInvariant().Length - 1))
{
foreach (var excelProject in swert.Positions)
{
excelProject.Parent = dkExcelRow;
}
dkExcelRow.Positions.InsertRange(0, swert.Positions);
dkExcelRow.Positions.Remove(swert);
}
}
return dkExcelRow;
}
public string GetMarkSteal(ExcelProject position)
{
return position.GetField(9) == null ? string.Empty : position.GetField(9).ToString().Trim();
}
private void LoadFromProject(ExcelProject excelProject, Drawing drawing, TempDrawingsStorage drawStorage)
{
var childrenProject = new List<Drawing>();
drawing.Name = excelProject.GetField(4)?.ToString().Trim();
drawing.Designation = excelProject.GetField(3)?.ToString().Trim();
drawing.Designation = drawing.Designation?.Replace("СБ", String.Empty).Trim() ?? String.Empty;
drawing.GostOnMaterial = string.Empty;
drawing.GostOnSort = string.Empty;
drawing.MarkSteal = string.Empty;
drawing.StandartSize = string.Empty;
drawing.Profile = string.Empty;
drawing.Files = excelProject.FileNames.Select(x => new UppyFileInfo() { FileName = x }).ToList();
drawing.OP = excelProject.GetField(17) == null ? String.Empty : excelProject.GetField(17).ToString();
DrawingStandartezer?.WriteToDrawingsStandartsField(drawing);
if (DrawingExcluder?.IsNeedExclude(drawing) == true)
return;
drawStorage.Add(drawing);
foreach (ExcelProject position in excelProject.Positions)
{
var subProject = new Drawing
{
ParentId = drawing.Id,
Count = Convert.ToInt32(position.GetField(13)),
Note = string.Empty,
NumberOnSpec = Normalizer.NormalizePositionNumber(position.GetField(3)?.ToString()),
Profile = string.Empty
};
subProject.CountAll = subProject.Count * drawing.CountAll;
if (position.Positions == null || position.Positions.Count == 0)
{
//дальше лезть не надо
subProject.Name = position.GetField(4)?.ToString().Trim();
subProject.Designation = ExcelMaterialParser.GetDesignation(position) == string.Empty ? subProject.Designation : ExcelMaterialParser.GetDesignation(position);
subProject.NumberOnSpec = ExcelMaterialParser.GetNumberBySpec(position);
if (subProject.Designation == string.Empty)
{
subProject.Designation = $"{drawing.Designation} поз. {subProject.NumberOnSpec}".Trim();
}
try
{
subProject.Length = ExcelMaterialParser.GetLength(position);
subProject.Width = ExcelMaterialParser.GetWidth(position);
}
catch (Exception ex)
{
AppendMessageToLog(new LogMessage("Ошибка парсиинга длина и/или ширины.", drawing, TypeMessage.Error));
}
subProject.GostOnSort = Normalizer.SetEmptySpacesGost(ExcelMaterialParser.GetGOSTS(position));
subProject.StandartSize = ExcelMaterialParser.GetStandartSize(position);
subProject.MarkSteal = ExcelMaterialParser.GetMarkSteal(position);
subProject.Profile = NameMaterialSearch.GetNameMaterialByGost(subProject.GostOnSort);
//subProject.Dimension = string.Empty;
subProject.GostOnMaterial = string.Empty;
subProject.Weight = Convert.ToDecimal(position.GetField(15));
subProject.WeightAll = subProject.CountAll * subProject.Weight;
subProject.MarkSteal = Normalizer.RemoveUnUseInfoAboutMarkSteal(subProject.MarkSteal);
DrawingStandartezer?.WriteToDrawingsStandartsField(subProject);
//DrawingStandartezer?.WriteToDrawingsStandartsRates(drawing);
subProject.OP = position.GetField(17) == null ? String.Empty : position.GetField(17).ToString();
subProject.Files = position.FileNames.Select(x => new UppyFileInfo() { FileName = x }).ToList();
if (DrawingExcluder?.IsNeedExclude(subProject) == true)
continue;
childrenProject.Add(subProject);
drawStorage.Add(subProject);
}
else
{
LoadFromProject(position, subProject, drawStorage);
childrenProject.Add(subProject);
}
}
drawing.WeightAll = childrenProject.Sum(x => x.WeightAll);
if (Math.Round(drawing.WeightAll) == 0)
{
drawing.WeightAll = Convert.ToDecimal(excelProject.GetField(15)) * drawing.CountAll;
}
drawing.Weight = drawing.WeightAll / drawing.CountAll;
}
protected virtual List<ExcelProject> GetListRows(string fileName)
{
var stream = File.OpenRead(fileName);
var excelPack = new ExcelPackage();
excelPack.Load(stream);
stream.Close();
var header = excelPack.Workbook.Worksheets[1].Cells[2, 1, 2, 18];
var listRows = new List<ExcelProject>();
var listCaption = new List<string>();
for (int i = 0; i < 18; i++)
{
listCaption.Add(((object[,])header.Value)[0, i].ToString());
}
for (int i = 3; i <= excelPack.Workbook.Worksheets[1].Dimension.Rows; i++)
{
var structure = new ExcelProject();
var dataRow = excelPack.Workbook.Worksheets[1].Cells[i, 1, i, 18];
structure.Row = new object[18];
for (int j = 0; j < 18; j++)
{
structure.Row[j] = ((object[,])dataRow.Value)[0, j];
}
structure.ColumnsCaptions = listCaption;
structure.FileNames = _docsFileName.GetFilesByNameDrawing(structure.GetField(3)?.ToString());
listRows.Add(structure);
}
return listRows;
}
public bool IsNeedExclude(ExcelProject project)
{
return false;
}