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 LoadXML(LoadTemplate l) { template = l; properties = new List<string>(); propertyValues = new List<string>(); headers = new List<string>(); unsorted = new List<List<string>>(); sorted = new List<List<LoadTemplate.myCell>>(); openXML(); getHeaderTitles(); getPartInfo(); sort(); }
private void button4_Click(object sender, EventArgs e) { LoadTemplate t; t = new LoadTemplate(); }
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 } }