public void buildFile(OperationParaVO opVO) { sheet.Copy(Missing.Value, workBook.Sheets[workBook.Sheets.Count]); sheet.Name = "解析结果"; sheet.Columns.Clear(); sheet.Rows.Clear(); for (int i = 0; i < opVO.resList.Count; i++) { sheet.Cells[i + 1, 1] = opVO.resList[i]; } workBook.Close(); workBooks.Close(); /**/ /*object missing=System.Reflection.Missing.Value; * Application app = new Application(); * app.Application.Workbooks.Add(true); * Workbook book =(Workbook)app.ActiveWorkbook; * Worksheet sheet = (Worksheet)book.ActiveSheet; * sheet.Cells[1, 1] = "源数据站点名称记录"; * sheet.Cells[1, 2] = "匹配总数"; * //将DataTable赋值给excel * * //保存excel文件 * book.SaveCopyAs("D:\\source.xls"); * //关闭文件 * book.Close(false, missing, missing); * //退出excel * app.Quit();*/ }
//统计单输出 public bool WritePgmTimeList(string fileName, NCListData exceldata) { Microsoft.Office.Interop.Excel.Application xls = new Microsoft.Office.Interop.Excel.Application(); _Workbook book = null; _Worksheet sheet = null; try { book = xls.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); xls.Visible = false; xls.DisplayAlerts = true; Microsoft.Office.Interop.Excel.Range cellRange; int shtIdx; int rowIdx = 4; for (shtIdx = 1; shtIdx <= book.Worksheets.Count; shtIdx++) { sheet = (_Worksheet)book.Worksheets.get_Item(shtIdx); cellRange = sheet.Cells; bool flg = false; //查找空行 for (rowIdx = 4; rowIdx < 101; rowIdx++) { object obj = ((Range)sheet.Cells[rowIdx, 1]).Text; if (obj == null || string.IsNullOrEmpty(obj.ToString())) { flg = true; break; } } if (flg) { break; } } //最后一行,生成sheet if (shtIdx == book.Worksheets.Count + 1 && rowIdx == 101) { sheet.Copy(Type.Missing, sheet); sheet = (_Worksheet)book.Worksheets.get_Item(shtIdx); //清空数据 cellRange = sheet.Cells; for (int j = 4; j < 101; j++) { for (int k = 1; k <= 6; k++) { cellRange[j, k] = null; } } rowIdx = 4; } cellRange = sheet.Cells; cellRange[rowIdx, 1] = exceldata.ProjectName; cellRange[rowIdx, 2] = exceldata.PartName; cellRange[rowIdx, 3] = exceldata.Procedure; cellRange[rowIdx, 4] = exceldata.ProgTime; cellRange[rowIdx, 5] = DateTime.Now.ToString("yyyy-MM-dd"); cellRange[rowIdx, 6] = exceldata.Memo; book.Save(); //保存 book.Close(false, Missing.Value, Missing.Value); return(true); } catch { return(false); } finally { xls.Quit(); xls = null; sheet = null; book = null; GC.Collect(); } }
private void Copy_invoice_work_sheet(InvoiceModel invoice) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); try { string templatePath = sTemplatePath + @"\Sunzex_INV.xlsx"; string outputPath = sOutputPath + @"\" + invoice.filename + ".xlsx"; app.Visible = false; app.Workbooks.Add(); if (!System.IO.File.Exists(templatePath)) { MessageBox.Show("Không tìm thấy file mẫu"); app.Workbooks.Close(); app.Quit(); return; } app.Workbooks.Add(templatePath); try { app.Workbooks.Add(!System.IO.File.Exists(outputPath) ? "" : outputPath); } catch (Exception ex) { MessageBox.Show(ex.Message); } _Worksheet ws = (_Worksheet)app.Workbooks[2].Worksheets[invoice.type]; _Worksheet sheet = (_Worksheet)app.Workbooks[3].Worksheets[1]; //check exist bool found = false; foreach (_Worksheet aSheet in app.Workbooks[3].Sheets) { if (invoice.sheetname.Equals(aSheet.Name)) { found = true; break; } } if (found) { throw new IOException("Invoice " + invoice.sheetname + " đã tồn tại, không thể tạo mới!"); } if (IsOpenedWB_ByName(invoice.filename + ".xlsx")) { throw new IOException("File " + invoice.filename + " đang được mở nên không thể ghi đè. Hãy đóng file và thử tạo lại invoice! "); } //TODO //Begin copy ws.Copy(sheet); app.Workbooks[3].Sheets[1].Activate(); sheet = app.ActiveSheet; sheet.Range["E7"].Value2 = invoice.number; sheet.Range["I7"].Value2 = invoice.date; sheet.Range["A10"].Value2 = invoice.consignee; sheet.Range["A17"].Value2 = invoice.portload; sheet.Range["C17"].Value2 = invoice.destination; sheet.Range["C19"].Value2 = invoice.sailing; sheet.Name = invoice.sheetname; if (invoice.type <= 3) { int row1 = 19; for (int i = 1; i <= invoice.type; i++) { sheet.Range["A" + (row1 + i * 3)].Value2 = invoice.detail_name[i]; sheet.Range["A" + (row1 + i * 3 + 1)].Value2 = @"ORDER: HSS90" + invoice.detail_order[i].Substring(1, invoice.detail_order[i].Length - 1); sheet.Range["A" + (row1 + i * 3 + 2)].Value2 = @"PO#" + invoice.detail_PO[i]; sheet.Range["E" + (row1 + i * 3)].Value2 = invoice.detail_quantity[i]; sheet.Range["G" + (row1 + i * 3)].Value2 = "0.03"; } int row2 = row1 + 4 + 3 * invoice.type; for (int i = 1; i <= invoice.type; i++) { sheet.Range["A" + (row2 + i * 3)].Value2 = invoice.detail_name[i]; sheet.Range["A" + (row2 + i * 3 + 1)].Value2 = @"ORDER: HSS90" + invoice.detail_order[i].Substring(1, invoice.detail_order[i].Length - 1); sheet.Range["A" + (row2 + i * 3 + 2)].Value2 = @"PO#" + invoice.detail_PO[i]; sheet.Range["E" + (row2 + i * 3)].Value2 = invoice.detail_quantity[i]; sheet.Range["G" + (row2 + i * 3)].Value2 = invoice.detail_price[i]; } } else if (invoice.type <= 5) { int row1 = 20; for (int i = 1; i <= invoice.type; i++) { sheet.Range["A" + (row1 + i * 2)].Value2 = invoice.detail_name[i] + " - " + @"ORDER: HSS90" + invoice.detail_order[i].Substring(1, invoice.detail_order[i].Length - 1); sheet.Range["A" + (row1 + i * 2 + 1)].Value2 = @"PO#" + invoice.detail_PO[i]; sheet.Range["E" + (row1 + i * 2)].Value2 = invoice.detail_quantity[i]; sheet.Range["G" + (row1 + i * 2)].Value2 = "0.03"; } int row2 = row1 + 4 + 2 * invoice.type; for (int i = 1; i <= invoice.type; i++) { sheet.Range["A" + (row2 + i * 2)].Value2 = invoice.detail_name[i] + " - " + @"ORDER: HSS90" + invoice.detail_order[i]; sheet.Range["A" + (row2 + i * 2 + 1)].Value2 = @"PO#" + invoice.detail_PO[i]; sheet.Range["E" + (row2 + i * 2)].Value2 = invoice.detail_quantity[i]; sheet.Range["G" + (row2 + i * 2)].Value2 = invoice.detail_price[i]; } } app.ActiveWorkbook.SaveAs(outputPath); object misValue = System.Reflection.Missing.Value; app.Workbooks[3].Close(false, misValue, misValue); app.Workbooks[2].Close(false, misValue, misValue); app.Workbooks[1].Close(false, misValue, misValue); app.Workbooks.Close(); app.Quit(); if (checkBox_openAfter.Checked) { OpenFile(outputPath); } else { MessageBox.Show("Invoice " + invoice.sheetname + " tạo thành công!"); } } catch (IOException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { object misValue = System.Reflection.Missing.Value; app.Workbooks[1].Close(false, misValue, misValue); app.Workbooks.Close(); app.Quit(); } }
private void Copy_shipping_work_sheet(ShippingModel ship) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); try { string templatePath = sTemplatePath + @"\Sunzex_SHIP.xlsx"; string outputPath = sOutputPath + @"\" + ship.filename + ".xlsx"; app.Visible = false; app.Workbooks.Add(); if (!System.IO.File.Exists(templatePath)) { MessageBox.Show("Không tìm thấy file mẫu"); app.Workbooks.Close(); app.Quit(); return; } app.Workbooks.Add(templatePath); try { app.Workbooks.Add(!System.IO.File.Exists(outputPath) ? "" : outputPath); } catch (Exception ex) { MessageBox.Show(ex.Message); } _Worksheet ws = (_Worksheet)app.Workbooks[2].Worksheets[1]; _Worksheet sheet = (_Worksheet)app.Workbooks[3].Worksheets[1]; //check exist bool found = false; foreach (_Worksheet aSheet in app.Workbooks[3].Sheets) { if (ship.sheetname.Equals(aSheet.Name)) { found = true; break; } } if (found) { throw new IOException("Shipping " + ship.sheetname + " đã tồn tại, không thể tạo mới!"); } if (IsOpenedWB_ByName(ship.filename + ".xlsx")) { throw new IOException("File " + ship.filename + " đang được mở nên không thể ghi đè. Hãy đóng file và thử tạo lại shipping! "); } //Begin copy ws.Copy(sheet); app.Workbooks[3].Sheets[1].Activate(); sheet = app.ActiveSheet; sheet.Range["H7"].Value2 = ship.date; sheet.Range["E14"].Value2 = ship.shipment; sheet.Range["E15"].Value2 = ship.transport; sheet.Range["E16"].Value2 = ship.destination; sheet.Range["E17"].Value2 = ship.portload; sheet.Range["E18"].Value2 = ship.voyage; sheet.Range["E19"].Value2 = ship.comodity; sheet.Range["E20"].Value2 = ship.amount.Replace(".", "").Replace(",", "."); sheet.Range["E22"].Value2 = ship.total.Replace(".", ""); sheet.Range["E23"].Value2 = ship.gross.Replace(".", "").Replace(",", "."); sheet.Name = ship.sheetname; //TODO: Handle saves option "No"/"Cancel" app.ActiveWorkbook.SaveAs(outputPath); object misValue = System.Reflection.Missing.Value; app.Workbooks[3].Close(false, misValue, misValue); app.Workbooks[2].Close(false, misValue, misValue); app.Workbooks[1].Close(false, misValue, misValue); app.Workbooks.Close(); app.Quit(); if (checkBox_openAfter.Checked) { OpenFile(outputPath); } else { MessageBox.Show("Shipping " + ship.sheetname + " tạo thành công!"); } } catch (IOException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show("Lỗi đọc/ghi/mở file: Do " + ex.Message); app.Workbooks.Close(); app.Quit(); } finally { app.Workbooks.Close(); app.Quit(); } }
public void openFile(string[] arr, string openBalanceValue) { Form1 sendMsg = new Form1(); shipments shipmentsReport = new shipments(); Application excelApp = new Microsoft.Office.Interop.Excel.Application(); // New excel workbook into which we will save our worksheets Workbook newWorkBook = excelApp.Workbooks.Add(); /// Open files passed into arr for (int arri = 0; arri < arr.Length; arri++) { Workbook excelBook = excelApp.Workbooks.Open(arr[arri]); _Worksheet excelSheet = excelBook.Sheets[1]; Range excelRange = excelSheet.UsedRange; //sendMsg.sendMessage(arr[arri]); string sheetCellValue = excelSheet.Cells[2, 2].value; if (sheetCellValue == "102" || sheetCellValue == "101") { excelSheet.Name = "Receipt"; ShipmentsReceits(ref excelSheet); } else if (sheetCellValue == "602" || sheetCellValue == "601") { excelSheet.Name = "Shipments"; ShipmentsReceits(ref excelSheet); } else if (sheetCellValue == "0002") { excelSheet.Name = "Current stock Boxes & Cabinets"; Stock(ref excelSheet); } Worksheet newWorkSheet = newWorkBook.Worksheets.get_Item(arri + 1); excelSheet.Copy(newWorkSheet); //sendMsg.sendMessage(excelSheet.ToString()); excelBook.Save(); excelBook.Close(true); } ///////////////// PCCC Invoicing summary sheet /////////////////////////// Worksheet summarykSheet = newWorkBook.Worksheets.get_Item(4); summarykSheet.Name = "PCCC invoicing summary"; summarykSheet.Columns["A:A"].ColumnWidth = 26; summarykSheet.Columns["B:B"].ColumnWidth = 10; summarykSheet.Columns["C:C"].ColumnWidth = 14; DateTime thisDay = DateTime.Today; summarykSheet.Cells[1, 1].Value2 = "Storage Charge " + DateTime.Now.AddMonths(-1).ToString("MMMM") + " " + thisDay.Year; summarykSheet.Cells[1, 3].Value2 = GetNumberOfWeeks(thisDay) + " Week Month"; summarykSheet.Cells[3, 1].Value2 = "Retrieval & Delivery of Files"; summarykSheet.Cells[4, 2].Value2 = "Qty"; summarykSheet.Cells[5, 1].Value2 = "No of Files Picked"; summarykSheet.Cells[5, 2].Value2 = Global.filesPicked; summarykSheet.Cells[6, 1].Value2 = "No. Deliveries"; summarykSheet.Cells[6, 2].Value2 = Global.deliveries; summarykSheet.Cells[7, 1].Value2 = "No. of Pick Ups"; summarykSheet.Cells[7, 2].Value2 = Global.pickUps; int cell = 8; foreach (string key in Global.locations.Keys) { summarykSheet.Cells[cell, 1].Value2 = "Delivery from/to " + key; summarykSheet.Cells[cell, 2].Value2 = Global.numberOfTrips[Global.locations[key], 1]; //sendMsg.sendMessage(key + " "+ Global.locations[key]+ " If working = "+ Global.numberOfTrips[Global.locations[key], 1]); cell++; } summarykSheet.Cells[17, 1].Value2 = "Total Extra Charges for Month"; summarykSheet.Cells[19, 1].Value2 = "Boxes Picked"; summarykSheet.Cells[19, 2].Value2 = 0; summarykSheet.Cells[20, 1].Value2 = "Cabs Picked"; summarykSheet.Cells[20, 2].Value2 = 0; summarykSheet.Cells[22, 1].Value2 = "Flatpacked Boxes delivered"; summarykSheet.Cells[22, 2].Value2 = Global.boxesDelivered; /////////////////////////// PCCC Storage ///////////////////////// string[] textBoxValues = openBalanceValue.Split('/'); Worksheet pcccStorage = newWorkBook.Worksheets.get_Item(5); pcccStorage.Name = "PCCC Storage"; pcccStorage.Columns["A:C"].ColumnWidth = 14; pcccStorage.Columns["D:K"].ColumnWidth = 11; pcccStorage.Columns["J:J"].ColumnWidth = 14; // PCCC Storage Boxes part pcccStorage.Cells[1, 1].Value2 = "Boxes"; pcccStorage.Cells[2, 1].Value2 = "Date"; pcccStorage.Cells[2, 2].Value2 = "Customer"; pcccStorage.Cells[2, 3].Value2 = "Opening Bal"; pcccStorage.Cells[2, 4].Value2 = "IN"; pcccStorage.Cells[2, 5].Value2 = "OUT"; pcccStorage.Cells[2, 6].Value2 = "Closing Bal"; pcccStorage.Cells[2, 8].Value2 = "Storage"; pcccStorage.Cells[2, 10].Value2 = "W/Ending"; // PCCC Storage Cabinets part pcccStorage.Cells[9, 1].Value2 = "Cabinets"; pcccStorage.Cells[10, 1].Value2 = "Date"; pcccStorage.Cells[10, 2].Value2 = "Customer"; pcccStorage.Cells[10, 3].Value2 = "Opening Bal"; pcccStorage.Cells[10, 4].Value2 = "IN"; pcccStorage.Cells[10, 5].Value2 = "OUT"; pcccStorage.Cells[10, 6].Value2 = "Closing Bal"; pcccStorage.Cells[10, 8].Value2 = "Storage"; pcccStorage.Cells[10, 10].Value2 = "W/Ending"; //sendMsg.sendMessage(textBoxValues.Length.ToString() + " text box value length"); if (textBoxValues.Length > 1) { pcccStorage.Cells[3, 3].Value2 = textBoxValues[0]; // passed open balance from form pcccStorage.Cells[11, 3].Value2 = textBoxValues[1]; } // Add week number and week end date to both Boxes and Cabinets for (int i = 0; i < Global.endOfWeek.Length; i++) { // PCCC Boxes pcccStorage.Cells[3 + i, 1].Value2 = "Wk " + (1 + i) + " " + DateTime.Now.AddMonths(-1).ToString("MMMM"); pcccStorage.Cells[3 + i, 2].Value2 = "PCCC Boxes"; pcccStorage.Cells[3 + i, 10].Value2 = Global.endOfWeek[i]; //PCCC Cabinets pcccStorage.Cells[11 + i, 1].Value2 = "Wk " + (1 + i) + " " + DateTime.Now.AddMonths(-1).ToString("MMMM"); pcccStorage.Cells[11 + i, 2].Value2 = "PCCC Cabinets"; pcccStorage.Cells[11 + i, 10].Value2 = Global.endOfWeek[i]; } string[] timePicked; timePicked = Global.pickList.ToArray(); int counter = 0; for (int i = 0; i < timePicked.Length; i++) { if (i % 2 != 0) { for (int y = counter; y < Global.endOfWeek.Length; y++) { if (DateTime.Parse(timePicked[i]) < DateTime.Parse(pcccStorage.Cells[3 + y, 10].Value2)) { pcccStorage.Cells[3 + y, 4].Value2 = timePicked[i - 1]; counter = y; y = Global.endOfWeek.Length; } } } } // Boxes box to fill up int pcccSummaryBoxes = 0; int handlingBoxes = 0; for (int i = 0; i < Global.endOfWeek.Length; i++) { if (pcccStorage.Cells[3 + i, 4].Value2 == null) { pcccStorage.Cells[3 + i, 4].Value2 = 0; // IN pcccStorage.Cells[3 + i, 5].Value2 = 0; // OUT pcccStorage.Cells[3 + i, 6].Value2 = Convert.ToInt32(pcccStorage.Cells[3 + i, 3].Value2) + Convert.ToInt32(pcccStorage.Cells[3 + i, 4].Value2); // Closing Balance pcccStorage.Cells[3 + i, 8].Value2 = pcccStorage.Cells[3 + i, 6].Value2; // Storage if (i < Global.endOfWeek.Length - 1) { pcccStorage.Cells[3 + (i + 1), 3].Value2 = pcccStorage.Cells[3 + i, 8].Value2; } } else { //sendMsg.sendMessage("eto ->" + pcccStorage.Cells[3 + i, 4].Value2.ToString()); pcccStorage.Cells[3 + i, 5].Value2 = 0; // OUT pcccStorage.Cells[3 + i, 6].Value2 = Convert.ToInt32(pcccStorage.Cells[3 + i, 3].Value2) + Convert.ToInt32(pcccStorage.Cells[3 + i, 4].Value2); // Closing Balance pcccStorage.Cells[3 + i, 8].Value2 = pcccStorage.Cells[3 + i, 6].Value2; // Storage if (i < Global.endOfWeek.Length - 1) { pcccStorage.Cells[3 + (i + 1), 3].Value2 = pcccStorage.Cells[3 + i, 8].Value2; } } pcccSummaryBoxes += Convert.ToInt32(pcccStorage.Cells[3 + i, 6].Value2); handlingBoxes += Convert.ToInt32(pcccStorage.Cells[3 + i, 4].Value2); } int pcccSummaryCabinets = 0; // Cabinets to fill up for (int i = 0; i < Global.endOfWeek.Length; i++) { if (pcccStorage.Cells[3 + i, 4].Value2 == null) { pcccStorage.Cells[11 + i, 4].Value2 = 0; // IN pcccStorage.Cells[11 + i, 5].Value2 = 0; // OUT pcccStorage.Cells[11 + i, 6].Value2 = Convert.ToInt32(pcccStorage.Cells[3 + i, 3].Value2) + Convert.ToInt32(pcccStorage.Cells[3 + i, 4].Value2); // Closing Balance pcccStorage.Cells[11 + i, 8].Value2 = pcccStorage.Cells[3 + i, 6].Value2; // Storage if (i < Global.endOfWeek.Length - 1) { pcccStorage.Cells[11 + (i + 1), 3].Value2 = pcccStorage.Cells[3 + i, 8].Value2; } } else { //sendMsg.sendMessage("eto ->" + pcccStorage.Cells[11 + i, 4].Value2.ToString()); pcccStorage.Cells[11 + i, 5].Value2 = 0; // OUT pcccStorage.Cells[11 + i, 6].Value2 = Convert.ToInt32(pcccStorage.Cells[11 + i, 3].Value2) + Convert.ToInt32(pcccStorage.Cells[11 + i, 4].Value2); // Closing Balance pcccStorage.Cells[11 + i, 8].Value2 = pcccStorage.Cells[11 + i, 6].Value2; // Storage if (i < Global.endOfWeek.Length - 1) { pcccStorage.Cells[11 + (i + 1), 3].Value2 = pcccStorage.Cells[11 + i, 8].Value2; } pcccSummaryCabinets += Convert.ToInt32(pcccStorage.Cells[11 + i, 6].Value2); } } pcccStorage.Cells[2 + Global.endOfWeek.Length, 8].Value2 = Global.pcccBoxes; // current pccc box count pcccStorage.Cells[10 + Global.endOfWeek.Length, 8].Value2 = Global.pcccCabinets; // current pccc cabinet count /////////////////// Rates ////////////////////////// Workbook excelRates = excelApp.Workbooks.Open(@"V:\Warehouses\Parkmore Warehouse\Reports\HSE_RATES.xlsx"); _Worksheet excelRatesSheet = excelRates.Sheets[1]; // Get new workBook sheet nr 6 Worksheet pcccSummary = newWorkBook.Worksheets.get_Item(6); // Copy rates sheet into new Nr 6 sheet excelRatesSheet.Copy(pcccSummary); // close Rates workBook excelRates.Save(); excelRates.Close(true); // Rename NewBook Nr 6 sheet pcccSummary = newWorkBook.Worksheets.get_Item("PCCC"); pcccSummary.Name = "PCCC Summary " + DateTime.Now.AddMonths(-1).ToString("MMMM") + " " + thisDay.Year; pcccSummary.Cells[1, 1].Value2 = "PCCC Summary Invoice " + DateTime.Now.AddMonths(-1).ToString("MMMM") + " " + thisDay.Year; pcccSummary.Cells[21, 1].Value2 = "Total Invoice " + DateTime.Now.AddMonths(-1).ToString("MMMM") + " " + thisDay.Year; pcccSummary.Cells[4, 3].Value2 = pcccSummaryBoxes; pcccSummary.Cells[5, 3].Value2 = pcccSummaryCabinets; //locations pcccSummary.Cells[9, 3].Value2 = Global.numberOfTrips[Global.locations["Shantalla"], 1] + Global.numberOfTrips[Global.locations["Doughiska"], 1] + Global.numberOfTrips[Global.locations["Mervue"], 1]; //Global.numberOfTrips[Global.locations["Athenry"], 1] + Global.numberOfTrips[Global.locations["Tuam"], 1] + Global.numberOfTrips[Global.locations["Loughrea"], 1] + Global.numberOfTrips[Global.locations["Mountbellew"], 1] + Global.numberOfTrips[Global.locations["Ballinasloe"], 1]; pcccSummary.Cells[10, 3].Value2 = Global.numberOfTrips[Global.locations["Athenry"], 1] + Global.numberOfTrips[Global.locations["Tuam"], 1] + Global.numberOfTrips[Global.locations["Loughrea"], 1] + Global.numberOfTrips[Global.locations["Mountbellew"], 1] + Global.numberOfTrips[Global.locations["Ballinasloe"], 1];; pcccSummary.Cells[14, 3].Value2 = Global.filesPicked; pcccSummary.Cells[15, 3].Value2 = handlingBoxes; /////////////////// END ////////////////////////// newWorkBook.SaveAs(@"C:\Users\ssladmin\Desktop\Weekly rep\PCCC Monthly Invoice.xlsx"); newWorkBook.Close(true); excelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); GC.Collect(); GC.WaitForPendingFinalizers(); }
private void Generate_Click(object sender, RoutedEventArgs e) { List <string> NCProg_Tools = new List <string>(); Microsoft.Office.Interop.Excel.Application oXL; _Workbook oWB; _Worksheet oSheetProjectSummary = null; _Worksheet oSheetProjectSummaryFull = null; _Worksheet oSheetNCProgSummary = null; _Worksheet oSheetNewNCProgSummary = null; _Worksheet oSheetToolpathDetails = null; _Worksheet oSheetToolList = null; _Worksheet oSheetNewToolList = null; Dictionary <string, string> VarsListProjectSummary = new Dictionary <string, string>(); Dictionary <string, string> VarsListProjectSummaryFull = new Dictionary <string, string>(); Dictionary <string, string> VarsListNCProgSummary = new Dictionary <string, string>(); Dictionary <string, string> VarsListToolpathDetails = new Dictionary <string, string>(); Dictionary <string, string> VarsListToolList = new Dictionary <string, string>(); bool Has_ProjectSummary = false; bool Has_ProjectSummaryFull = false; bool Has_NCProgSummary = false; bool Has_ToolList = false; bool Has_ToolpathDetails = false; string Project_Path = PowerMILLAutomation.ExecuteEx("print $project_pathname(0)"); string NCProgDetails = ""; string MergedModelList = ""; int FileQty = 0; if (listNCProgsSelected.Items.Count == 0) { //No NC Program selected, do nothing... MessageBox.Show("Please select at least one NC Program"); } else { List <ToolInfo> ToolData = new List <ToolInfo>(); List <ToolpathInfo> ToolpathData = new List <ToolpathInfo>(); List <ToolInfo> ProjectToolData = new List <ToolInfo>(); List <ToolpathInfo> ProjectToolpathData = new List <ToolpathInfo>(); ProjectInfo Project = new ProjectInfo(); //Start Excel and get Application object. oXL = new Microsoft.Office.Interop.Excel.Application(); oXL.Visible = true; oXL.DisplayAlerts = false; //Get the template from the option page string Path = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\ExcellSetupSheet\\Template.ini"; string TemplateFile = ""; if (File.Exists(Path)) { const Int32 BufferSize = 128; using (var fileStream = File.OpenRead(Path)) using (var streamReader = new StreamReader(fileStream, Encoding.UTF8, true, BufferSize)) { String line; while ((line = streamReader.ReadLine()) != null) { TemplateFile = line; } } } else { Path = System.Reflection.Assembly.GetExecutingAssembly().CodeBase.Substring(8, System.Reflection.Assembly.GetExecutingAssembly().CodeBase.Length - 8); string directory = System.IO.Path.GetDirectoryName(Path); TemplateFile = directory + "\\Tool_List.xlsx"; } if (!Directory.Exists(Project_Path + "\\Excel_Setupsheet\\")) { Directory.CreateDirectory(Project_Path + "\\Excel_Setupsheet\\"); } string[] fileArrayXLSX = Directory.GetFiles(Project_Path + "\\Excel_Setupsheet\\", "*.xlsx"); string[] fileArrayXLS = Directory.GetFiles(Project_Path + "\\Excel_Setupsheet\\", "*.xls"); foreach (string File in fileArrayXLSX) { if (File.IndexOf("~$") < 0) { FileQty = FileQty + 1; } } foreach (string File in fileArrayXLS) { if (File.IndexOf("~$") < 0) { FileQty = FileQty + 1; } } if (FileQty > 0) { File.Copy(TemplateFile, Project_Path + "\\Excel_Setupsheet\\SetupSheet_" + FileQty + ".xlsx"); TemplateFile = Project_Path + "\\Excel_Setupsheet\\SetupSheet_" + FileQty + ".xlsx"; } else { File.Copy(TemplateFile, Project_Path + "\\Excel_Setupsheet\\SetupSheet.xlsx"); TemplateFile = Project_Path + "\\Excel_Setupsheet\\SetupSheet.xlsx"; } //Get a new workbook. oWB = (_Workbook)(oXL.Workbooks.Open(TemplateFile)); List <string> ModelList = PowerMILLAutomation.GetListOf(PowerMILLAutomation.enumEntity.MachinableModels); PowerMILLAutomation.GetModelsLimits(ModelList, out double ModelsMinX, out double ModelsMinY, out double ModelsMinZ, out double ModelsMaxX, out double ModelsMaxY, out double ModelsMaxZ); Project = new ProjectInfo(); Project.MachModelsMaxX = ModelsMaxX.ToString(); Project.MachModelsMaxY = ModelsMaxY.ToString(); Project.MachModelsMaxZ = ModelsMaxZ.ToString(); Project.MachModelsMinX = ModelsMinX.ToString(); Project.MachModelsMinY = ModelsMinY.ToString(); Project.MachModelsMinZ = ModelsMinZ.ToString(); Project.Name = PowerMILLAutomation.ExecuteEx("print $project_pathname(1)"); Project.Path = PowerMILLAutomation.ExecuteEx("print $project_pathname(0)"); Project.OrderNumber = PowerMILLAutomation.ExecuteEx("print $project.orderNumber"); Project.Programmer = PowerMILLAutomation.ExecuteEx("print $project.programmer"); Project.PartName = PowerMILLAutomation.ExecuteEx("print $project.partname"); Project.Customer = PowerMILLAutomation.ExecuteEx("print $project.customer"); Project.Date = DateTime.Now.ToString(); Project.Notes = PowerMILLAutomation.ExecuteEx("print $project.notes"); Project.ExcelTemplate = TemplateFile; foreach (string Model in ModelList) { MergedModelList = MergedModelList + Environment.NewLine + Model; } Project.ModelsList = MergedModelList; Project.CombinedNCTPList = ""; foreach (Worksheet worksheet in oWB.Worksheets) { if (worksheet.Name == "Project_Summary") { oSheetProjectSummary = worksheet; //Extract template keywords and cell adresses VarsListProjectSummary = WriteFiles.ExtractTemplateData(oSheetProjectSummary); Has_ProjectSummary = true; } else if (worksheet.Name == "Project_Summary_Full") { oSheetProjectSummaryFull = worksheet; //Extract template keywords and cell adresses VarsListProjectSummaryFull = WriteFiles.ExtractTemplateData(oSheetProjectSummaryFull); Has_ProjectSummaryFull = true; } else if (worksheet.Name == "NCProg_Summary") { oSheetNCProgSummary = worksheet; //Extract template keywords and cell adresses VarsListNCProgSummary = WriteFiles.ExtractTemplateData(oSheetNCProgSummary); Has_NCProgSummary = true; } else if (worksheet.Name == "Toolpath_Details") { oSheetToolpathDetails = worksheet; //Extract template keywords and cell adresses VarsListToolpathDetails = WriteFiles.ExtractTemplateData(oSheetToolpathDetails); Has_ToolpathDetails = true; } else if (worksheet.Name == "ToolList") { oSheetToolList = worksheet; //Extract template keywords and cell adresses VarsListToolList = WriteFiles.ExtractTemplateData(oSheetToolList); Has_ToolList = true; } } List <string> NCProg_Toolpaths = new List <string>(); foreach (String NCProg in listNCProgsSelected.Items) { if (NCProgDetails == "") { NCProgDetails = NCProg; } else { NCProgDetails = NCProgDetails + Environment.NewLine + NCProg; } NCProg_Toolpaths = PowerMILLAutomation.GetNCProgToolpathes(NCProg); foreach (string Toolpath in NCProg_Toolpaths) { NCProgDetails = NCProgDetails + Environment.NewLine + " " + Toolpath; } Project.TotalTime = Project.TotalTime + double.Parse(PowerMILLAutomation.ExecuteEx("print $entity('ncprogram';'" + NCProg + "').Statistics.TotalTime")); //Extract the PowerMILL parameters found in the template from the current NCProgram toolapths WriteFiles.ExtractData(NCProg, out ToolData, out ToolpathData); if (Has_ProjectSummaryFull) { foreach (ToolpathInfo Toolpath in ToolpathData) { ProjectToolpathData.Add(new ToolpathInfo { Name = Toolpath.Name, Description = Toolpath.Description, Notes = Toolpath.Notes, ToolName = Toolpath.ToolName, ToolNumber = Toolpath.ToolNumber, ToolDiameter = Toolpath.ToolDiameter, ToolType = Toolpath.ToolType, ToolCutterLength = Toolpath.ToolCutterLength, ToolHolderName = Toolpath.ToolHolderName, ToolOverhang = Toolpath.ToolOverhang, ToolNumberOfFlutes = Toolpath.ToolNumberOfFlutes, ToolLengthOffset = Toolpath.ToolLengthOffset, ToolRadOffset = Toolpath.ToolRadOffset, ToolpathType = Toolpath.ToolpathType, ToolTipRadius = Toolpath.ToolTipRadius, ToolDescription = Toolpath.ToolDescription, Thickness = Toolpath.Thickness, AxialThickness = Toolpath.AxialThickness, CutterComp = Toolpath.CutterComp, Feed = Toolpath.Feed, Speed = Toolpath.Speed, IPT = Toolpath.IPT, SFM = Toolpath.SFM, PlungeFeed = Toolpath.PlungeFeed, SkimFeed = Toolpath.SkimFeed, Coolant = Toolpath.Coolant, Stepover = Toolpath.Stepover, DOC = Toolpath.DOC, GeneralAxisType = Toolpath.GeneralAxisType, Statistic_Time = Toolpath.Statistic_Time, TPWorkplane = Toolpath.TPWorkplane, Tolerance = Toolpath.Tolerance, RapidHeight = Toolpath.RapidHeight, SkimHeight = Toolpath.SkimFeed, ToolpathMinX = Toolpath.ToolpathMinX, ToolpathMinY = Toolpath.ToolpathMinY, ToolpathMinZ = Toolpath.ToolpathMinZ, ToolpathMaxX = Toolpath.ToolpathMaxX, ToolpathMaxY = Toolpath.ToolpathMaxY, ToolpathMaxZ = Toolpath.ToolpathMaxZ, FirstLeadInType = Toolpath.FirstLeadInType, SecondLeadInType = Toolpath.SecondLeadInType, FirstLeadOutType = Toolpath.FirstLeadOutType, SecondLeadOutType = Toolpath.SecondLeadOutType, CuttingDistance = Toolpath.CuttingDistance, NCProgName = NCProg }); } } if (Has_NCProgSummary) { //Write the Excel document int Index = oSheetNCProgSummary.Index; oSheetNCProgSummary.Copy(oSheetNCProgSummary, Type.Missing); if (NCProg.Length > 31) { oWB.Sheets[Index].Name = NCProg.Replace("*", "").Substring(0, 30); } else { oWB.Sheets[Index].Name = NCProg; } oSheetNewNCProgSummary = oWB.Sheets[Index]; if (Has_ToolpathDetails) { WriteFiles.CreateExcelFile(NCProg, ToolpathData, ToolData, Project, oSheetNewNCProgSummary, VarsListNCProgSummary, oWB, true, Project_Path, oSheetNewNCProgSummary, NCProgDetails); } else { WriteFiles.CreateExcelFile(NCProg, ToolpathData, ToolData, Project, oSheetNewNCProgSummary, VarsListNCProgSummary, oWB, false, Project_Path, oSheetNewNCProgSummary, NCProgDetails); } } if (Has_ToolpathDetails) { //Write the Excel document WriteFiles.CreateExcelFile(NCProg, ToolpathData, ToolData, Project, oSheetToolpathDetails, VarsListToolpathDetails, oWB, false, Project_Path, oSheetNewNCProgSummary, NCProgDetails); } if (Has_ToolList) { //Write the Excel document int Index = oSheetToolList.Index; oSheetToolList.Copy(oSheetToolList, Type.Missing); if (NCProg.Length > 25) { oWB.Sheets[Index].Name = NCProg.Replace("*", "").Substring(0, 24) + "-Tools"; } else { oWB.Sheets[Index].Name = NCProg + "-Tools"; } oWB.Sheets[Index].Name = NCProg + "-Tools"; oSheetNewToolList = oWB.Sheets[Index]; WriteFiles.CreateExcelFile(NCProg, ToolpathData, ToolData, Project, oSheetNewToolList, VarsListToolList, oWB, false, Project_Path, oSheetNewNCProgSummary, NCProgDetails); } } Project.CombinedNCTPList = NCProgDetails; if (Has_ProjectSummary) { WriteFiles.CreateExcelFile("None", ToolpathData, ToolData, Project, oSheetProjectSummary, VarsListProjectSummary, oWB, true, Project_Path, oSheetProjectSummary, NCProgDetails); } if (Has_ProjectSummaryFull) { WriteFiles.CreateExcelFile("None", ProjectToolpathData, ToolData, Project, oSheetProjectSummaryFull, VarsListProjectSummaryFull, oWB, true, Project_Path, oSheetProjectSummaryFull, NCProgDetails); } if (Has_NCProgSummary) { oSheetNCProgSummary.Delete(); } if (Has_ToolpathDetails) { oSheetToolpathDetails.Delete(); } if (Has_ToolList) { oSheetToolList.Delete(); } oXL.DisplayAlerts = true; oWB.Sheets[1].Activate(); oWB.Save(); MessageBox.Show("SetupSheet exported successfully"); } }