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");
}
}