public TempDrawingsStorage ConvertExcelProjectToDomainModel(ExcelProject excelProject)
 {
     var drawSotrage = new TempDrawingsStorage();
     var project = new Drawing { Count = 1, CountAll = 1 };
     LoadFromProject(excelProject, project, drawSotrage);
     return drawSotrage;
 }
Example #2
0
 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;
        }
Example #4
0
        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);
     }
 }
Example #7
0
        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);
                }
            }
        }
Example #11
0
 public string GetGOSTS(ExcelProject position)
 {
     return position.GetField(8) == null ? string.Empty : position.GetField(8).ToString().Trim();
 }
Example #12
0
        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);

        }
Example #13
0
        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);
            }
        }
Example #14
0
        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");
                }
            }
        }
Example #18
0
 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;
        }
Example #23
0
 public string GetMarkSteal(ExcelProject position)
 {
     return position.GetField(9) == null ? string.Empty : position.GetField(9).ToString().Trim();
 }
Example #24
0
        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;
        }
Example #25
0
        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;
        }
Example #26
0
 public bool IsNeedExclude(ExcelProject project)
 {
     return false;
 }