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