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;
}
}
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();
}
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
//{
//}
}
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]);
}
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>());
}
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]);
}
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);
}
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.");
}
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);
}
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;
}
}
}
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);
}
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.");
}
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);
}
