Пример #1
0
        public xmlFileHandler(mainFrame mainFrame1, excelSection excelSection1, string filePath)
        {
            try
            {
                //instantiate all the necessary lists and objects for handling the XML
                componentAttributes = new List <List <string> >();
                titleBlockInfo      = new List <string>();
                TBindex             = new int[excelSection1.TBtext.Count];
                Hindex = new int[excelSection1.Htext.Count];
                xmlFileParser xmlFileParser1 = new xmlFileParser();
                XmlDocument   xmlRead        = new XmlDocument();


                openXML(xmlRead, filePath); // load the xml file into the xmlRead object
                XmlNodeList partAttributesNodeList = xmlRead.SelectNodes("GRID")[0].SelectNodes("COLUMNS")[0].SelectNodes("COLUMN");
                XmlNodeList componentNodeList      = xmlRead.SelectNodes("GRID")[0].SelectNodes("ROWS")[0].SelectNodes("ROW");
                readHeaderAttributes(partAttributesNodeList, xmlFileParser1, excelSection1.TBtext, ref TBindex);
                readHeaderAttributes(partAttributesNodeList, xmlFileParser1, excelSection1.Htext, ref Hindex);
                readComponents(componentNodeList, xmlFileParser1, TBindex, Hindex, ref titleBlockInfo, ref componentAttributes, ref totalPartCount);
                setupExcel(filePath);
                mainFrame1.writeToConsole("Finished reading xml file.");
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
                throw e;
            }
        }
Пример #2
0
 public void start() // main order of the program.
 {
     runParser = new Thread(delegate()
     {
         try
         {
             try
             {
                 excelSection template  = new excelSection(); //initiating necessary objects
                 sort sort1             = new sort();
                 countParts countParts1 = new countParts();
                 excelFileHandler1      = new excelFileHandler(this);
                 excelFileHandler1.run(ref template, ref sort1, ref countParts1);                                                                                                                                   // read template excel file
                 xmlFileHandler xmlFileHandler1 = new xmlFileHandler(this, template, filename);                                                                                                                     // read source xml file
                 sort1.start(this, sort1, xmlFileHandler1.componentAttributes, template.Htext, template.HcolumnIndex, countParts1);                                                                                 // sort xml file info
                 EBOMexcelFile eBOMexcelFile1 = new EBOMexcelFile(excelFileHandler1, sort1.sorted, template, xmlFileHandler1.exportFileName, this, xmlFileHandler1.totalPartCount, xmlFileHandler1.titleBlockInfo); // create new EBOM from xml file using template file info
                 writeToConsole("Complete");
                 mainFrameScreen1.enableStartButton(true);
                 mainFrameScreen1.enableSourceButton(true);
             }
             catch (Exception e)
             {
                 MessageBox.Show(e.ToString());
             }
             finally
             {
                 try
                 {
                     Marshal.FinalReleaseComObject(excelFileHandler1.xlWorkSheet);
                     //template.xlWorkBook.Close(false, misValue, misValue);
                     excelFileHandler1.xlWorkBook.Close(SaveChanges: false);
                     //template.xlWorkBook.Close();
                     Marshal.FinalReleaseComObject(excelFileHandler1.xlWorkBook);
                     excelFileHandler1.xlWorkBooks.Close();
                     Marshal.FinalReleaseComObject(excelFileHandler1.xlWorkBooks);
                     excelFileHandler1.xlApp.Quit();
                     Marshal.FinalReleaseComObject(excelFileHandler1.xlApp); // excel objects don't releast comObjects to excel so you have to force it
                 }
                 catch {}
             }
         }
         finally
         {
             GC.Collect();
             GC.WaitForPendingFinalizers();
         }
     });
     runParser.Name = "CreateEBOM";
     runParser.Start();
 }
Пример #3
0
        public void populateBodySectionCell(Range cell, ref excelSection excel)
        {
            int index = excel.rowIndex.Count - 1;

            if (excel.rowIndex[index].Count > 0)
            {
                if (excel.rowIndex[index][0] != cell[1, 1].Row)
                {
                    excel.addNewList(excel);
                    index = excel.rowIndex.Count - 1;
                }
            }

            excel.rowIndex[index].Add(cell[1, 1].Row);
            excel.columnIndex[index].Add(cell[1, 1].Column);
            excel.backGroundColor[index].Add(cell[1, 1].Interior.Color);

            excel.textSize[index].Add((int)cell[1, 1].Font.Size);
            excel.textFont[index].Add((string)cell[1, 1].Font.Name);
            excel.textColor[index].Add((double)cell[1, 1].Font.Color);

            //excel.rightLineStyle[index].Add((XlLineStyle)cell[1, 1].Borders(XlBordersIndex.xlEdgeRight).LineStyle); // ignoring top border because it would overwrite header
            excel.rightLineStyle[index].Add(new XlLineStyle()); // ignoring top border because it would overwrite header

            excel.rightWeight[index].Add((XlBorderWeight)cell[1, 1].Borders(XlBordersIndex.xlEdgeRight).Weight);
            excel.bottomLineStyle[index].Add((XlLineStyle)cell[1, 1].Borders(XlBordersIndex.xlEdgeBottom).LineStyle);
            excel.bottomWeight[index].Add((XlBorderWeight)cell[1, 1].Borders(XlBordersIndex.xlEdgeBottom).Weight);
            excel.leftLineStyle[index].Add((XlLineStyle)cell[1, 1].Borders(XlBordersIndex.xlEdgeLeft).LineStyle);
            excel.leftWeight[index].Add((XlBorderWeight)cell[1, 1].Borders(XlBordersIndex.xlEdgeLeft).Weight);



            //if ((XlLineStyle)cell[1, 1].Borders(XlBordersIndex.xlEdgeRight).LineStyle != XlLineStyle.xlLineStyleNone) // only add border style to cell class if it is other than the expected default.
            //{
            //    excel.rightLineStyle[index].Add((XlLineStyle)cell[1, 1].Borders(XlBordersIndex.xlEdgeRight).LineStyle); // ignoring top border because it would overwrite header
            //    excel.rightWeight[index].Add((XlBorderWeight)cell[1, 1].Borders(XlBordersIndex.xlEdgeRight).Weight);
            //    excel.bottomLineStyle[index].Add((XlLineStyle)cell[1, 1].Borders(XlBordersIndex.xlEdgeBottom).LineStyle);
            //    excel.bottomWeight[index].Add((XlBorderWeight)cell[1, 1].Borders(XlBordersIndex.xlEdgeBottom).Weight);
            //    excel.leftLineStyle[index].Add((XlLineStyle)cell[1, 1].Borders(XlBordersIndex.xlEdgeLeft).LineStyle);
            //    excel.leftWeight[index].Add((XlBorderWeight)cell[1, 1].Borders(XlBordersIndex.xlEdgeLeft).Weight);
            //}
            //else
            //{

            //}
        }
Пример #4
0
 public void insertRow(excelSection source, int sourceIndex, excelSection sorted, int sortedIndex)
 {//  insert row from specified index of source list to specified index of received list.
     sorted.text.Insert(sortedIndex, source.text[sourceIndex]);
     sorted.textColor.Insert(sortedIndex, source.textColor[sourceIndex]);
     sorted.textFont.Insert(sortedIndex, source.textFont[sourceIndex]);
     sorted.textSize.Insert(sortedIndex, source.textSize[sourceIndex]);
     sorted.backGroundColor.Insert(sortedIndex, source.backGroundColor[sourceIndex]);
     sorted.rowIndex.Insert(sortedIndex, source.rowIndex[sourceIndex]);
     sorted.columnIndex.Insert(sortedIndex, source.columnIndex[sourceIndex]);
     sorted.topLineStyle.Insert(sortedIndex, source.topLineStyle[sourceIndex]);
     sorted.topWeight.Insert(sortedIndex, source.topWeight[sourceIndex]);
     sorted.rightLineStyle.Insert(sortedIndex, source.rightLineStyle[sourceIndex]);
     sorted.rightWeight.Insert(sortedIndex, source.rightWeight[sourceIndex]);
     sorted.bottomLineStyle.Insert(sortedIndex, source.bottomLineStyle[sourceIndex]);
     sorted.bottomWeight.Insert(sortedIndex, source.bottomWeight[sourceIndex]);
     sorted.leftLineStyle.Insert(sortedIndex, source.leftLineStyle[sourceIndex]);
     sorted.leftWeight.Insert(sortedIndex, source.leftWeight[sourceIndex]);
 }
Пример #5
0
 public void addNewList(excelSection section)
 {
     section.text.Add(new List <string>());
     section.textColor.Add(new List <double>());
     section.textFont.Add(new List <string>());
     section.textSize.Add(new List <int>());
     section.backGroundColor.Add(new List <double>());
     section.rowIndex.Add(new List <int>());
     section.columnIndex.Add(new List <int>());
     section.topLineStyle.Add(new List <XlLineStyle>());
     section.topWeight.Add(new List <XlBorderWeight>());
     section.rightLineStyle.Add(new List <XlLineStyle>());
     section.rightWeight.Add(new List <XlBorderWeight>());
     section.bottomLineStyle.Add(new List <XlLineStyle>());
     section.bottomWeight.Add(new List <XlBorderWeight>());
     section.leftLineStyle.Add(new List <XlLineStyle>());
     section.leftWeight.Add(new List <XlBorderWeight>());
 }
Пример #6
0
 public void addRow(excelSection source, int insertIndex, excelSection sorted)
 { // add row from source list to the end of new received list
     sorted.text.Add(source.text[insertIndex]);
     sorted.textColor.Add(source.textColor[insertIndex]);
     sorted.textFont.Add(source.textFont[insertIndex]);
     sorted.textSize.Add(source.textSize[insertIndex]);
     sorted.backGroundColor.Add(source.backGroundColor[insertIndex]);
     sorted.rowIndex.Add(source.rowIndex[insertIndex]);
     sorted.columnIndex.Add(source.columnIndex[insertIndex]);
     sorted.topLineStyle.Add(source.topLineStyle[insertIndex]);
     sorted.topWeight.Add(source.topWeight[insertIndex]);
     sorted.rightLineStyle.Add(source.rightLineStyle[insertIndex]);
     sorted.rightWeight.Add(source.rightWeight[insertIndex]);
     sorted.bottomLineStyle.Add(source.bottomLineStyle[insertIndex]);
     sorted.bottomWeight.Add(source.bottomWeight[insertIndex]);
     sorted.leftLineStyle.Add(source.leftLineStyle[insertIndex]);
     sorted.leftWeight.Add(source.leftWeight[insertIndex]);
 }
Пример #7
0
        private int populateBody(Worksheet excel, List <List <string> > componentList, excelSection template1, mainFrame mainFrame2)
        {
            int numberOfTemplateRows = template1.backGroundColor.Count;
            int startRow             = template1.rowIndex[0][0];
            int row        = 0;
            int finalCount = 0;
            int match      = 0;

            for (int a = 0; a < componentList.Count; a++)// for each row
            {
                if (mainFrame.end)
                {
                    throw new Exception("User is closing the program");
                }
                row = a % template1.backGroundColor.Count;

                for (int b = 0; b < componentList[a].Count; b++)                                              // for each column
                {
                    writeData(excel.Cells[startRow + a, template1.columnIndex[row][b]], componentList[a][b]); // write cell text
                }

                for (int b = 0; b < componentList[a].Count; b += match + 1)// for each column
                {
                    match = 0;
                    for (int c = b + 1; c < componentList[a].Count; c++) // for each column of the same row after the 'b' column
                    {
                        if (template1.backGroundColor[row][b] == template1.backGroundColor[row][c])
                        {
                            match++;
                        }
                        else
                        {
                            break;
                        }
                    }
                    if (template1.backGroundColor[row][b] == 16777215)
                    {
                        continue;                                                                     // skip matching segment if already default setting.
                    }
                    Range range = getRange(excel, b, b + match, a + startRow, template1.columnIndex); // get like cells
                    range.Interior.Color = template1.backGroundColor[row][b];
                }

                for (int b = 0; b < componentList[a].Count; b += match + 1)// for each column
                {
                    match = 0;
                    for (int c = b + 1; c < componentList[a].Count; c++) // for each column of the same row after the 'b' column
                    {
                        if (template1.rightLineStyle[row][b] == template1.rightLineStyle[row][c])
                        {
                            match++;
                        }
                        else
                        {
                            break;
                        }
                    }
                    if (template1.rightLineStyle[row][b] == XlLineStyle.xlLineStyleNone)
                    {
                        continue;                                                                     // skip matching segment if already default setting.
                    }
                    Range range = getRange(excel, b, b + match, a + startRow, template1.columnIndex); // get like cells
                    range.Borders.LineStyle = template1.rightLineStyle[row][b];
                }
                for (int b = 0; b < componentList[a].Count; b += match + 1)// for each column
                {
                    match = 0;
                    for (int c = b + 1; c < componentList[a].Count; c++) // for each column of the same row after the 'b' column
                    {
                        if (template1.rightWeight[row][b] == template1.rightWeight[row][c])
                        {
                            match++;
                        }
                        else
                        {
                            break;
                        }
                    }
                    if (template1.rightWeight[row][b] == XlBorderWeight.xlThin)
                    {
                        continue;                                                                     // skip matching segment if already default setting.
                    }
                    Range range = getRange(excel, b, b + match, a + startRow, template1.columnIndex); // get like cells
                    range.Borders.Weight = template1.rightWeight[row][b];
                }
                for (int b = 0; b < componentList[a].Count; b += match + 1)// for each column
                {
                    match = 0;
                    for (int c = b + 1; c < componentList[a].Count; c++) // for each column of the same row after the 'b' column
                    {
                        if (template1.bottomLineStyle[row][b] == template1.bottomLineStyle[row][c])
                        {
                            match++;
                        }
                        else
                        {
                            break;
                        }
                    }
                    if (template1.bottomLineStyle[row][b] == XlLineStyle.xlLineStyleNone)
                    {
                        continue;                                                                     // skip matching segment if already default setting.
                    }
                    Range range = getRange(excel, b, b + match, a + startRow, template1.columnIndex); // get like cells
                    range.Borders.LineStyle = template1.bottomLineStyle[row][b];
                }
                for (int b = 0; b < componentList[a].Count; b += match + 1)// for each column
                {
                    match = 0;
                    for (int c = b + 1; c < componentList[a].Count; c++) // for each column of the same row after the 'b' column
                    {
                        if (template1.bottomWeight[row][b] == template1.bottomWeight[row][c])
                        {
                            match++;
                        }
                        else
                        {
                            break;
                        }
                    }
                    if (template1.bottomWeight[row][b] == XlBorderWeight.xlThin)
                    {
                        continue;                                                                     // skip matching segment if already default setting.
                    }
                    Range range = getRange(excel, b, b + match, a + startRow, template1.columnIndex); // get like cells
                    range.Borders.LineStyle = template1.bottomWeight[row][b];
                }
                for (int b = 0; b < componentList[a].Count; b += match + 1)// for each column
                {
                    match = 0;
                    for (int c = b + 1; c < componentList[a].Count; c++) // for each column of the same row after the 'b' column
                    {
                        if (template1.leftLineStyle[row][b] == template1.leftLineStyle[row][c])
                        {
                            match++;
                        }
                        else
                        {
                            break;
                        }
                    }
                    if (template1.leftLineStyle[row][b] == XlLineStyle.xlLineStyleNone)
                    {
                        continue;                                                                     // skip matching segment if already default setting.
                    }
                    Range range = getRange(excel, b, b + match, a + startRow, template1.columnIndex); // get like cells
                    range.Borders.LineStyle = template1.leftLineStyle[row][b];
                }
                for (int b = 0; b < componentList[a].Count; b += match + 1)// for each column
                {
                    match = 0;
                    for (int c = b + 1; c < componentList[a].Count; c++) // for each column of the same row after the 'b' column
                    {
                        if (template1.leftWeight[row][b] == template1.leftWeight[row][c])
                        {
                            match++;
                        }
                        else
                        {
                            break;
                        }
                    }
                    if (template1.leftWeight[row][b] == XlBorderWeight.xlThin)
                    {
                        continue;                                                                     // skip matching segment if already default setting.
                    }
                    Range range = getRange(excel, b, b + match, a + startRow, template1.columnIndex); // get like cells
                    range.Borders.LineStyle = template1.leftWeight[row][b];
                }
                for (int b = 0; b < componentList[a].Count; b += match + 1)// for each column
                {
                    match = 0;
                    for (int c = b + 1; c < componentList[a].Count; c++) // for each column of the same row after the 'b' column
                    {
                        if (template1.textFont[row][b] == template1.textFont[row][c])
                        {
                            match++;
                        }
                        else
                        {
                            break;
                        }
                    }
                    if (template1.textFont[row][b] == "Calibri")
                    {
                        continue;                                                                     // skip matching segment if already default setting.
                    }
                    Range range = getRange(excel, b, b + match, a + startRow, template1.columnIndex); // get like cells
                    range.Text.Name = template1.textFont[row][b];
                }
                for (int b = 0; b < componentList[a].Count; b += match + 1)// for each column
                {
                    match = 0;
                    for (int c = b + 1; c < componentList[a].Count; c++) // for each column of the same row after the 'b' column
                    {
                        if (template1.textSize[row][b] == template1.textSize[row][c])
                        {
                            match++;
                        }
                        else
                        {
                            break;
                        }
                    }
                    if (template1.textSize[row][b] == 11)
                    {
                        continue;                                                                     // skip matching segment if already default setting.
                    }
                    Range range = getRange(excel, b, b + match, a + startRow, template1.columnIndex); // get like cells
                    range.Text.Size = template1.textSize[row][b];
                }
                for (int b = 0; b < componentList[a].Count; b += match + 1)// for each column
                {
                    match = 0;
                    for (int c = b + 1; c < componentList[a].Count; c++) // for each column of the same row after the 'b' column
                    {
                        if (template1.textColor[row][b] == template1.textColor[row][c])
                        {
                            match++;
                        }
                        else
                        {
                            break;
                        }
                    }
                    if (template1.textColor[row][b] == 0)
                    {
                        continue;                                                                     // skip matching segment if already default setting.
                    }
                    Range range = getRange(excel, b, b + match, a + startRow, template1.columnIndex); // get like cells
                    range.Text.Color = template1.textColor[row][b];
                }


                mainFrame2.writeToConsole("Row " + a + " finished.");
                finalCount++;
            }
            return(finalCount);
        }
Пример #8
0
        public EBOMexcelFile(excelFileHandler excelFileHandler2, List <List <string> > componentList, excelSection template, string fileName, mainFrame mainFrame1, int xmlPartCount, List <string> titleBlockInfo)
        {
            populateTitleBlock(excelFileHandler2.xlWorkSheet, titleBlockInfo, template.TBrowIndex, template.TBcolumnIndex); mainFrame1.writeToConsole("Finished writing title block.");
            populateHeaderRow(excelFileHandler2.xlWorkSheet, template.Htext, template.HrowIndex, template.HcolumnIndex); mainFrame1.writeToConsole("Finished writing header row.");
            int finalCount = populateBody(excelFileHandler2.xlWorkSheet, componentList, template, mainFrame1); mainFrame1.writeToConsole("Finished writing body.");

            mainFrame1.missingParts(compareComponentCounts(xmlPartCount, finalCount));
            createFooter(excelFileHandler2.xlWorkSheet, componentList.Count + template.rowIndex[0][0], template.footerColor, template.footerList); mainFrame1.writeToConsole("Finished writing footer.");
            if (!saveExcelFile(excelFileHandler2.xlWorkSheet, fileName))
            {
                excelFileHandler2.xlWorkBook.Close(SaveChanges: false);
            }
            mainFrame1.writeToConsole("Saved new EBOM file.");
        }
Пример #9
0
 public void populateTitleBlockCell(Range cell, ref excelSection excel)
 {
     excel.TBtext.Add(cell[1, 1].Text.Split(':')[0]);
     excel.TBrowIndex.Add(cell[1, 1].Row);
     excel.TBcolumnIndex.Add(cell[1, 1].Column);
 }
Пример #10
0
        public void parseCell(Range cell, int row, int column, ref int totalRows2, ref int totalColumns2, ref excelSection template3, ref sort sort3, ref countParts countParts2)
        {
            /////////////////////// find and parse tag //////////////////////////
            string tag = parseTag(cell[1, 1]);

            switch (tag)
            {
            case "TextHere":
            {
                populateTitleBlockCell(cell[1, 1], ref template3); cell[1, 1].Value = "";
                break;
            }

            case "HeaderHere":
            {
                populateHeaderList(cell[1, 1], ref template3); cell[1, 1].Value = "";
                break;
            }

            case "BodyHere":
            {
                populateBodySectionCell(cell[1, 1], ref template3);  cell[1, 1].Value = "";
                break;
            }

            case "Sort":
            {
                parseSort(cell[1, 1], ref sort3); cell[1, 1].Value = "";
                break;
            }

            case "Footer":
            {
                getFooterInfo(cell[1, 1], ref template3.footerColor, ref template3.footerList); cell[1, 1].Interior.Color = 16777215; cell[1, 1].Value = "";
                break;
            }

            case "Group":
            {
                getQuantityGrouping(column, ref countParts2.groupedColumns); cell[1, 1].Value = "";
                break;
            }

            case "Quantity":
            {
                getQuantityColumn(column, ref countParts2.quantityColumn); cell[1, 1].Value = "";
                break;
            }

            case "EndRow":
            {
                totalRows2 = row; cell[1, 1].Value = "";         // change loop iterator limit variable for rows if EndRow tag is found
                break;
            }

            case "EndColumn":
            {
                totalColumns2 = column; cell[1, 1] = "";         // change loop iterator limit variable for columns if EndColumn tag is found
                break;
            }
            }
        }
Пример #11
0
 public void populateHeaderList(Range cell, ref excelSection excel)
 {
     excel.Htext.Add(cell[1, 1].Text.Split('[')[0]);
     excel.HrowIndex.Add(cell[1, 1].Row);
     excel.HcolumnIndex.Add(cell[1, 1].Column);
 }
Пример #12
0
        private void readTemplateFile(ref Worksheet xlWorkSheet1, int totalRows1, int totalColumns1, ref excelSection template2, ref sort sort2, ref countParts countParts2, mainFrame mainFrame2)
        {
            excelFileParser excelFileParser1 = new excelFileParser();
            excelFileParser bodyRows         = new excelFileParser();


            for (int row = 1; row < totalRows1; row++)
            {
                if (mainFrame.end)
                {
                    throw new Exception("User is closing the program");
                }
                for (int column = 1; column < totalColumns1; column++)
                {
                    excelFileParser1.parseCell(xlWorkSheet.Cells[row, column], row, column, ref totalRows1, ref totalColumns1, ref template2, ref sort2, ref countParts2);
                }
                mainFrame2.writeToConsole("Row " + row + " finished.");
            }
            mainFrame2.writeToConsole("Finished reading template file.");
        }
Пример #13
0
 public void run(ref excelSection template1, ref sort sort1, ref countParts countparts1)
 {
     openTemplateFile(ref xlApp, ref xlWorkBooks, ref xlWorkBook, ref xlWorkSheet);
     readTemplateFile(ref xlWorkSheet, totalRows, totalColumns, ref template1, ref sort1, ref countparts1, mainFrame1);
 }