Exemplo n.º 1
0
        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();*/
        }
Exemplo n.º 2
0
        //统计单输出
        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();
            }
        }
Exemplo n.º 3
0
        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();
            }
        }
Exemplo n.º 4
0
        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();
            }
        }
Exemplo n.º 5
0
        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");
            }
        }