private void button5_Click(object sender, EventArgs e)
{
CreateExcelFile c;
LoadXML l;
LoadTemplate t;
t = new LoadTemplate();
l = new LoadXML(t);
c = new CreateExcelFile(l, t);
}
public CreateExcelFile(LoadXML x, LoadTemplate t)
{
XML = x;
template = t;
try
{
//Start the Microsoft Excel Application
string excelTemplate = System.AppDomain.CurrentDomain.BaseDirectory + "New EBOM " + template.time + ".xlsx";
xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
xlWorkBooks = xlApp.Workbooks;
try
{
xlWorkBook = xlWorkBooks.Open(excelTemplate, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false); //open the template file!
}
catch (Exception e) { return; }
xlWorkSheets = xlWorkBook.Worksheets;
xlWorkSheet = xlWorkSheets.get_Item(1);
//int totalRows = template.allCells.GetLength(0);
//int totalColumns = template.allCells.GetLength(1);
//for (int row = 0; row < totalRows ; row++)
//{
// for (int column = 0; column < totalColumns; column++) // the plus one is because the excel columns and rows start at 1
// {
// if (template.allCells[row, column].text.Contains("[TextHere]")) writeData(template.allCells[row, column]);
// }
// Console.WriteLine("Finished writing row " + (row + 1));
//}
foreach (LoadTemplate.myCell cell in template.titleBlock)
{
writeData(cell);
}
foreach (LoadTemplate.myCell cell in template.headerRow)
{
writeData(cell);
}
Console.WriteLine("Finished writing Title Block");
for (int a = 0; a < template.bodyRows.Count; a++)
{
Range range = xlWorkSheet.Range[xlWorkSheet.Cells[template.bodyRows[a][0].rowIndex, template.bodyRows[a][0].columnIndex], xlWorkSheet.Cells[template.bodyRows[a][template.bodyRows[a].Count - 1].rowIndex, template.bodyRows[a][template.bodyRows[a].Count - 1].columnIndex]]; // get whole row
range.Interior.Color = template.bodyColors[a % template.bodyColors.Count]; // change color of whole row
if (template.bodyRows[a][template.bodyRows[a].Count - 1].rightLineStyle != XlLineStyle.xlLineStyleNone) // check if we need to set borders
{
for (int b = 0; b < template.bodyRows[a].Count; b++) //set borders
{
xlWorkSheet.Cells[template.bodyRows[a][b].rowIndex, template.bodyRows[a][b].columnIndex].Borders(XlBordersIndex.xlEdgeRight).LineStyle = template.bodyRows[a][b].rightLineStyle;
xlWorkSheet.Cells[template.bodyRows[a][b].rowIndex, template.bodyRows[a][b].columnIndex].Borders(XlBordersIndex.xlEdgeRight).Weight = template.bodyRows[a][b].rightWeight;
xlWorkSheet.Cells[template.bodyRows[a][b].rowIndex, template.bodyRows[a][b].columnIndex].Borders(XlBordersIndex.xlEdgeBottom).LineStyle = template.bodyRows[a][b].bottomLineStyle;
xlWorkSheet.Cells[template.bodyRows[a][b].rowIndex, template.bodyRows[a][b].columnIndex].Borders(XlBordersIndex.xlEdgeBottom).Weight = template.bodyRows[a][b].bottomWeight;
xlWorkSheet.Cells[template.bodyRows[a][b].rowIndex, template.bodyRows[a][b].columnIndex].Borders(XlBordersIndex.xlEdgeLeft).LineStyle = template.bodyRows[a][b].leftLineStyle;
xlWorkSheet.Cells[template.bodyRows[a][b].rowIndex, template.bodyRows[a][b].columnIndex].Borders(XlBordersIndex.xlEdgeLeft).Weight = template.bodyRows[a][b].leftWeight;
}
}
}
int c = 0;
foreach (List <List <LoadTemplate.myCell> > partGroups in XML.sorted)
{
foreach (List <LoadTemplate.myCell> cellRows in partGroups)
{
foreach (LoadTemplate.myCell cell in cellRows)
{
writeData(cell);
}
}
Console.WriteLine("Finished writing row: " + ++c);
}
Console.WriteLine("Total Part Count: " + XML.totalPartCount);
xlWorkBook.SaveAs(System.AppDomain.CurrentDomain.BaseDirectory + "Ebom_testing" + getTime() + ".xlsx");
}
finally
{
Console.WriteLine("Finished Saving Excel File");
Marshal.FinalReleaseComObject(xlWorkSheet);
Marshal.FinalReleaseComObject(xlWorkSheets);
xlWorkBook.Close();
Marshal.FinalReleaseComObject(xlWorkBook);
xlWorkBooks.Close();
Marshal.FinalReleaseComObject(xlWorkBooks);
xlApp.Quit();
Marshal.FinalReleaseComObject(xlApp); // excel objects don't releast comObjects to excel so you have to force it
}
}
