Beispiel #1
0
        public bool odi_ValidateBOMSheets(Excel.Sheets wkshts, int type = ODI.odiMatrixBOM)
        {
            List <string> sheetlist = ODI.get_SheetList(type);

            foreach (Excel.Worksheet w in wkshts)
            {
                sheetlist.Remove(w.Name);
            }

            return((sheetlist.Count > 0) ? false : true);
        }
Beispiel #2
0
        private void btn_Go_Click(object sender, EventArgs e)
        {
            btn_Go.Enabled = false;

            OpenFileDialog ofd = new OpenFileDialog();

            ofd.InitialDirectory = @"C:\";
            ofd.Filter           = "Excel files (*.xls;*.xlsx)|*.xls;*.xlsx";
            ofd.ValidateNames    = true;
            ofd.Multiselect      = false;
            if (ofd.ShowDialog(this) == DialogResult.Cancel)
            {
                btn_Go.Enabled = true;
                return;
            }

            Excel.Application oXL = initailExcel();

            Excel.Workbook wkb;                                          //要開啟的BOM 檔案
            Excel.Workbook wkbout;                                       //要輸出的BOM 檔案
            List <String>  shList = ODI.get_SheetList(ODI.odiMatrixBOM); // 取得要處理的sheet name

            shList.Reverse();
            //Dictionary<string, Group> GBOM = new Dictionary<string, Group>(); // 總BOM表 Great BOM

            int fileidx = 1; //檔案計數器

            this.Status1.Text = "Opening BOM " + Convert.ToString(fileidx) + " ...";
            try
            {
                //Globals.ThisAddIn.odiDbgOut("Opening Workbook: " + f);
                wkb         = oXL.Workbooks.Open(ofd.FileName, ReadOnly: true);
                oXL.Visible = false;
            }
            catch (Exception ex)
            {
                //Globals.ThisAddIn.odiMsgOut("[Error] Fail Opening Workbook: " + f);
                //Globals.ThisAddIn.odiMsgOut(ex.Message);
                MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
                btn_Go.Enabled = true;
                return;
            }

            try
            {
                this.Status1.Text = "Reading BOM " + Convert.ToString(fileidx) + " ...";
                //Globals.ThisAddIn.odiDbgOut("Validating BOM format... ");
                if (!this.odi_ValidateBOMSheets(wkb.Sheets, ODI.odiMfgBOM))
                {
                    //Globals.ThisAddIn.odiMsgOut("[Warning] Base BOM Format mismatch [sheets missing] : " + f);
                    MessageBox.Show("BOM Format mismatch [sheets missing] : " + ofd.FileName + "\n Check FAQ page in Help menu.");
                    btn_Go.Enabled = true;
                    return;
                }

                wkbout      = oXL.Workbooks.Add(); //產生輸出workbook
                oXL.Visible = false;

                wkbout.Worksheets[1].Name = "WAIT_ForDELEte";

                foreach (string sh in shList)
                {
                    List <Group> GBOM = new List <Group>();

                    /***讀取BOM*/
                    object[,] arr = null;
                    try
                    {
                        wkb.Worksheets[sh].Unprotect(string.Empty);
                        //Unprotect suceeded, 沒有密碼保護,採用陣列讀取
                        arr = wkb.Worksheets[sh].Range["A1"].CurrentRegion.Value2;
                    }
                    catch
                    {
                        //SMT/PTH/BOTTOM因為有密碼保護,無法用陣列讀取,所以只好一格一格來
                        int         iRowCount = wkb.Worksheets[sh].UsedRange.Rows.Count;
                        int         iColCount = wkb.Worksheets[sh].UsedRange.Columns.Count;
                        Excel.Range range;
                        arr = (object[, ])Array.CreateInstance(typeof(object), new int[] { iRowCount, iColCount }, new int[] { 1, 1 });

                        for (int iRow = 1; iRow <= iRowCount; iRow++)
                        {
                            this.Status1.Text = "Reading " + sh + " " + iRow + " / " + iRowCount;
                            for (int iCol = 1; iCol <= iColCount; iCol++)
                            {
                                range = (Excel.Range)wkb.Worksheets[sh].Cells[iRow, iCol];
                                //arr[iRow, iCol] = (range.Value2 == null) ? null : range.Text.ToString();
                                arr[iRow, iCol] = range.Value2;
                            }
                        }
                    }


                    string bomkey = this.Create_BOM(GBOM, arr, ODI.odiMfgBOM);
                    if (String.IsNullOrEmpty(bomkey))
                    {
                        MessageBox.Show("[Error:] BOM Create fail (null bomkey) \n Check FAQ page in Help menu.");
                        btn_Go.Enabled = true;
                        return;
                    }
                    //Globals.ThisAddIn.odiDbgOut("BOM Created: " + bomkey);

                    this.Status1.Text = "Reading BOM " + Convert.ToString(fileidx) + " " + bomkey;


                    /***準備資料 ***/
                    List <string[]> outlist           = new List <string[]>(); //每個row
                    List <int>      GroupRowCountList = new List <int>();      //紀錄每個group第一行的位置

                    /*** 篩選內容,寫入outlist***/
                    var query = from data in GBOM
                                where data.CCL == "Y"
                                select data;


                    this.Status1.Text = "Picking up CCLs for you :)";
                    GroupRowCountList.Add(0);
                    int outColCount = ODI.ColRemark[ODI.odiMatrixBOM];
                    foreach (var q in query)
                    {
                        //每個q為一個group
                        //Globals.ThisAddIn.odiDbgOut(q.Value.MFGPN + " !data.Value.Qty.Contains(0): " + !q.Value.Qty.Contains(0));
                        string[] row = new string[outColCount]; //單行row
                        row[ODI.ColNum[ODI.odiMatrixBOM] - 1]         = Convert.ToString(q.Num);
                        row[ODI.ColHHPN[ODI.odiMatrixBOM] - 1]        = Convert.ToString(q.HHPN);
                        row[ODI.ColDescription[ODI.odiMatrixBOM] - 1] = Convert.ToString(q.Description);
                        row[ODI.ColMFG[ODI.odiMatrixBOM] - 1]         = Convert.ToString(q.MFG);
                        row[ODI.ColMFGPN[ODI.odiMatrixBOM] - 1]       = Convert.ToString(q.MFGPN);
                        row[ODI.ColQty[ODI.odiMatrixBOM] - 1]         = Convert.ToString(q.Qty[0]);
                        row[ODI.ColLocation[ODI.odiMatrixBOM] - 1]    = Convert.ToString(q.Location);
                        row[ODI.ColRemark[ODI.odiMatrixBOM] - 1]      = Convert.ToString(q.Remark);

                        outlist.Add(row);

                        if (q.AVL.Count() > 0) //若有AVL,寫入下一個row
                        {
                            foreach (var a in q.AVL)
                            {
                                string[] rowa = new string[outColCount]; //單行row
                                rowa[ODI.ColNum[ODI.odiMatrixBOM] - 1]         = null;
                                rowa[ODI.ColHHPN[ODI.odiMatrixBOM] - 1]        = Convert.ToString(a.Value.HHPN);
                                rowa[ODI.ColDescription[ODI.odiMatrixBOM] - 1] = Convert.ToString(a.Value.Description);
                                rowa[ODI.ColMFG[ODI.odiMatrixBOM] - 1]         = Convert.ToString(a.Value.MFG);
                                rowa[ODI.ColMFGPN[ODI.odiMatrixBOM] - 1]       = Convert.ToString(a.Value.MFGPN);
                                rowa[ODI.ColQty[ODI.odiMatrixBOM] - 1]         = null;
                                rowa[ODI.ColLocation[ODI.odiMatrixBOM] - 1]    = null;
                                rowa[ODI.ColRemark[ODI.odiMatrixBOM] - 1]      = Convert.ToString(a.Value.Remark);
                                outlist.Add(rowa);
                            }
                        }
                        GroupRowCountList.Add(outlist.Count);
                    }

                    //建立outarr(實際寫入excel array), size: 表頭(5rows)+資料
                    object[,] outarr = (object[, ])Array.CreateInstance(typeof(object), new int[] { outlist.Count() + 5, outColCount }, new int[] { 1, 1 });

                    //寫入結果到outarr
                    for (int ar = 0; ar < outlist.Count(); ar++)
                    {
                        for (int ac = 0; ac < outColCount; ac++)
                        {
                            outarr[ar + 6, ac + 1] = outlist[ar][ac];
                        }
                    }


                    //寫入表頭到outlist
                    outarr[1, 1] = "FUJIN PRECISION INDUSTRY(SHENZHEN) CO.,LTD";
                    outarr[2, 1] = "BILL OF MATERIAL";
                    outarr[3, 2] = arr[3, 2];
                    outarr[3, 6] = arr[3, 6];
                    outarr[3, 8] = arr[3, 8];
                    outarr[4, 2] = arr[4, 2];
                    outarr[4, 6] = arr[4, 6];
                    outarr[4, 8] = arr[4, 8];

                    outarr[5, 1]  = "Item";
                    outarr[5, 2]  = "HF PN";
                    outarr[5, 3]  = "STD PN";
                    outarr[5, 4]  = "Description";
                    outarr[5, 5]  = "Supplier";
                    outarr[5, 6]  = "Supplier PN";
                    outarr[5, 7]  = "Qty";
                    outarr[5, 8]  = "Location";
                    outarr[4, 9]  = "Total Qty";
                    outarr[5, 9]  = "Total Q'ty";
                    outarr[4, 10] = "Total Set";
                    outarr[5, 10] = "Total Set";
                    outarr[4, 11] = "Model A";
                    outarr[4, 12] = "Model B";
                    outarr[4, 13] = "Model C";
                    outarr[4, 14] = "Model D";
                    outarr[4, 15] = "Model E";
                    outarr[4, 16] = "Model F";
                    outarr[5, 17] = "Remark";


                    this.Status1.Text = "Creating Matrix BOM - " + sh;

                    /*** 寫入Excel ***/
                    Excel.Worksheet Wst;
                    Excel.Range     rng;
                    Excel.Range     rng2;
                    int             lastrow = outarr.GetUpperBound(0);
                    int             lastcol = outarr.GetUpperBound(1);

                    Wst      = wkbout.Worksheets.Add();
                    Wst.Name = sh;

                    rng        = Wst.Range["A1"].Resize[lastrow, lastcol];
                    rng.Value2 = outarr;

                    /*** 設定格式 ***/
                    oXL.ActiveWindow.DisplayGridlines = false;

                    //基本字體
                    rng            = Wst.Range["A1"].EntireColumn.EntireRow;
                    rng.Font.Name  = "Calibri";
                    rng.Font.Size  = 10;
                    rng.Font.Color = System.Drawing.Color.FromArgb(0, 0, 0);

                    //欄寬, 對齊
                    ((Excel.Range)Wst.Columns["A:A", System.Type.Missing]).ColumnWidth = 3.75;
                    ((Excel.Range)Wst.Columns["B:B", System.Type.Missing]).ColumnWidth = 14;
                    ((Excel.Range)Wst.Columns["C:C", System.Type.Missing]).ColumnWidth = 0;
                    ((Excel.Range)Wst.Columns["D:D", System.Type.Missing]).ColumnWidth = 35;
                    ((Excel.Range)Wst.Columns["E:E", System.Type.Missing]).ColumnWidth = 10;
                    ((Excel.Range)Wst.Columns["F:F", System.Type.Missing]).ColumnWidth = 18;
                    ((Excel.Range)Wst.Columns["G:G", System.Type.Missing]).ColumnWidth = 3.75;
                    ((Excel.Range)Wst.Columns["H:H", System.Type.Missing]).ColumnWidth = 20;
                    ((Excel.Range)Wst.Columns["I:I", System.Type.Missing]).ColumnWidth = 4;
                    ((Excel.Range)Wst.Columns["J:J", System.Type.Missing]).ColumnWidth = 4;
                    ((Excel.Range)Wst.Columns["K:K", System.Type.Missing]).ColumnWidth = 3.43;
                    ((Excel.Range)Wst.Columns["L:L", System.Type.Missing]).ColumnWidth = 3.43;
                    ((Excel.Range)Wst.Columns["M:M", System.Type.Missing]).ColumnWidth = 3.43;
                    ((Excel.Range)Wst.Columns["N:N", System.Type.Missing]).ColumnWidth = 3.43;
                    ((Excel.Range)Wst.Columns["O:O", System.Type.Missing]).ColumnWidth = 3.43;
                    ((Excel.Range)Wst.Columns["P:P", System.Type.Missing]).ColumnWidth = 3.43;
                    ((Excel.Range)Wst.Columns["Q:Q", System.Type.Missing]).ColumnWidth = 50;

                    rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //基本水平置中

                    ((Excel.Range)Wst.Columns["B:F", System.Type.Missing]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                    ((Excel.Range)Wst.Columns["H:H", System.Type.Missing]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                    ((Excel.Range)Wst.Columns["Q:Q", System.Type.Missing]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                    ((Excel.Range)Wst.Columns["Q:Q", System.Type.Missing]).WrapText            = false;

                    //標題
                    rng = Wst.get_Range("A1", "Q1");
                    rng.Merge(false);
                    rng.Font.Color          = System.Drawing.Color.FromArgb(245, 245, 245);
                    rng.Interior.Color      = System.Drawing.Color.FromArgb(50, 50, 50);
                    rng.Font.Name           = "Arial";
                    rng.Font.FontStyle      = "Bold";
                    rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    rng.Font.Size           = 12;

                    rng = Wst.get_Range("A2", "Q2");
                    rng.Merge(false);
                    rng.Font.Color          = System.Drawing.Color.FromArgb(245, 245, 245);
                    rng.Interior.Color      = System.Drawing.Color.FromArgb(50, 50, 50);
                    rng.Font.Name           = "Arial";
                    rng.Font.FontStyle      = "Bold";
                    rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    rng.Font.Size           = 12;


                    /*
                     * rng = Wst.Range["G1"].Resize[1, lastcol - 6];
                     * rng.Font.FontStyle = "Bold";
                     * rng = Wst.Range["G2"].Resize[2, lastcol - 6];
                     * rng.Font.Color = System.Drawing.Color.FromArgb(115, 117, 118);
                     *
                     * rng = Wst.Range["F1"].Resize[3, 1];
                     * rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
                     * rng.Font.Color = System.Drawing.Color.FromArgb(150, 150, 150);
                     */

                    //Product code highlight
                    Wst.Range["B3"].Characters[13].Font.FontStyle = "Bold";
                    Wst.Range["F3"].Characters[13].Font.FontStyle = "Bold";
                    Wst.Range["H3"].Characters[13].Font.FontStyle = "Bold";

                    //表頭
                    rng = Wst.Range["A5"].Resize[1, ODI.ColTotalSet[ODI.odiMatrixBOM]];
                    rng.Interior.Color = System.Drawing.Color.FromArgb(52, 58, 64);
                    rng.Font.Color     = System.Drawing.Color.FromArgb(245, 248, 251);
                    rng.Font.FontStyle = "Bold";
                    //rng.Font.Name = "Arial";
                    rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    //rng.Font.Size = 10;

                    //Model 欄位特殊處理
                    rng = Wst.Range["K4"].Resize[1, ODI.NumOfMatrix];
                    rng.Interior.Color      = System.Drawing.Color.FromArgb(52, 58, 64);
                    rng.Font.Color          = System.Drawing.Color.FromArgb(245, 248, 251);
                    rng.Font.FontStyle      = "Bold";
                    rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
                    rng.InsertIndent(1);
                    rng.Characters[1, 6].Font.Color = System.Drawing.Color.FromArgb(52, 58, 64);

                    //Remark欄位
                    rng = Wst.Range["Q5"];
                    rng.Interior.Color      = System.Drawing.Color.FromArgb(52, 58, 64);
                    rng.Font.Color          = System.Drawing.Color.FromArgb(245, 248, 251);
                    rng.Font.FontStyle      = "Bold";
                    rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                    //Total Qty
                    rng2 = Wst.get_Range("A3", "A4");
                    rng2.Merge(false);
                    rng2.Copy();
                    rng = Wst.get_Range("I4", "I5");
                    rng.PasteSpecial(Excel.XlPasteType.xlPasteFormats, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone);
                    rng.Interior.Color      = System.Drawing.Color.FromArgb(52, 58, 64);
                    rng.Font.Color          = System.Drawing.Color.FromArgb(245, 248, 251);
                    rng.Font.FontStyle      = "Bold";
                    rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    rng.WrapText            = true;


                    //Total Set
                    rng = Wst.get_Range("J4", "J5");
                    rng2.Copy();
                    rng.PasteSpecial(Excel.XlPasteType.xlPasteFormats, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone);
                    rng.Interior.Color      = System.Drawing.Color.FromArgb(52, 58, 64);
                    rng.Font.Color          = System.Drawing.Color.FromArgb(245, 248, 251);
                    rng.Font.FontStyle      = "Bold";
                    rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    rng.WrapText            = true;


                    //基本框線
                    rng = Wst.Range["A5"].Resize[lastrow - 4, ODI.ColRemark[ODI.odiMatrixBOM]];
                    rng.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
                    rng.Borders.Weight    = Excel.XlBorderWeight.xlThin;
                    rng.Borders.Color     = System.Drawing.Color.FromArgb(101, 101, 101);

                    rng = Wst.Range["K4"].Resize[1, ODI.NumOfMatrix];
                    rng.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
                    rng.Borders.Weight    = Excel.XlBorderWeight.xlThin;
                    rng.Borders.Color     = System.Drawing.Color.FromArgb(101, 101, 101);

                    //每個group加框上色
                    //加上公式
                    bool oddgroup = false;
                    for (int i = 0; i < GroupRowCountList.Count - 1; i++)
                    {
                        this.Status1.Text = "Creating Matrix BOM - " + sh + " Group " + Convert.ToString(i);
                        //Main source 粗體
                        rng           = (Excel.Range)Wst.Cells[GroupRowCountList[i] + 6, 1].Resize[1, lastcol];
                        rng.Font.Bold = true;

                        if (oddgroup)
                        {
                            //整個group上色
                            rng = (Excel.Range)Wst.Cells[GroupRowCountList[i] + 6, 1].Resize[GroupRowCountList[i + 1] - GroupRowCountList[i], lastcol];
                            rng.Interior.Color = System.Drawing.Color.FromArgb(235, 235, 235);
                        }
                        oddgroup = !oddgroup;

                        //合併ID欄位
                        //v1.0: 為了符合原MatrixBOM格式,改為不合併,僅隱藏格線
                        rng = (Excel.Range)Wst.Cells[GroupRowCountList[i] + 6, 1].Resize[GroupRowCountList[i + 1] - GroupRowCountList[i], 1];
                        //rng.Merge(false);
                        rng.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
                        rng.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle   = Excel.XlLineStyle.xlLineStyleNone;


                        //公式
                        rng             = (Excel.Range)Wst.Cells[GroupRowCountList[i] + 6, 9].Resize[GroupRowCountList[i + 1] - GroupRowCountList[i], 1];
                        rng.FormulaR1C1 = "=R" + Convert.ToString(GroupRowCountList[i] + 6) + "C[-2] * R[0]C[1]";

                        rng             = (Excel.Range)Wst.Cells[GroupRowCountList[i] + 6, 10].Resize[GroupRowCountList[i + 1] - GroupRowCountList[i], 1];
                        rng.FormulaR1C1 = @"=IF(OR(RC[1]=""V"",RC[1]=""v""),R5C[1],0)+IF(OR(RC[2]=""V"",RC[2]=""v""),R5C[2],0)+IF(OR(RC[3]=""V"",RC[3]=""v""),R5C[3],0)+IF(OR(RC[4]=""V"",RC[4]=""v""),R5C[4],0)+IF(OR(RC[5]=""V"",RC[5]=""v""),R5C[5],0)+IF(OR(RC[6]=""V"",RC[6]=""v""),R5C[6],0)";
                    }

                    /*** FileList ***/

                    /*
                     * rng = (Excel.Range)Wst.Cells[lastrow + 2, 1];
                     * rng.Value2 = "Result generated from the following files:";
                     * rng.Font.Bold = true;
                     * rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                     *
                     * fileidx = 1;
                     * foreach (string f in listBox_BOMList.Items)
                     * {
                     *  rng = rng.Offset[1, 1]; //Offset to Next row, next column, for file name
                     *  rng.Value2 = f;
                     *  rng = rng.Offset[0, -1];// offset 1 column left, for index
                     *  rng.Value2 = Convert.ToString(fileidx++) + ".";
                     * }
                     * rng = (Excel.Range)Wst.Cells[lastrow + 3, 1].Resize[listBox_BOMList.Items.Count, 2];
                     * rng.Font.Color = System.Drawing.Color.FromArgb(150, 150, 150);
                     */

                    /*** Footer ***/
                }

                wkbout.Worksheets["WAIT_ForDELEte"].Delete();

                wkb.Close(false);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Something wrong with EXCEL operation, please close all EXCEL process in memory and try again. \n If still has issue, please check Matriz Page (Help-->About) for solution or support. " + ex.Message);

                /*
                 * wkb.Close(false);
                 * System.Runtime.InteropServices.Marshal.ReleaseComObject(wkb);
                 * wkb = null;
                 */
                oXL.Workbooks.Close();
                oXL.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
                oXL            = null;
                DialogResult   = DialogResult.OK;
                btn_Go.Enabled = true;
                return;
            }

            wkb = null;

            this.Status1.Text = "Matrix BOM Done. ^o^";
            oXL.DisplayAlerts = true;
            oXL.Visible       = true;
            oXL.UserControl   = true;

            DialogResult   = DialogResult.OK;
            btn_Go.Enabled = true;

            return;
        }