/// <summary>
/// the static constructor is used to initialize the static readonly arrays.
/// </summary>
static UltimatePrimesSoE()
{
WHLPOS = new byte[WHLPTRN.Length + 1]; //to look up wheel position index from wheel index
for (byte i = 0, acc = 0; i < WHLPTRN.Length; ++i)
{
acc += WHLPTRN[i]; WHLPOS[i + 1] = acc;
}
WHLNDX = new byte[WCRC + 1]; for (byte i = 1; i < WHLPOS.Length; ++i)
{
for (byte j = (byte)(WHLPOS[i - 1] + 1); j <= WHLPOS[i]; ++j)
{
WHLNDX[j] = i;
}
}
WHLRNDUP = new byte[WCRC * 2]; for (byte i = 1; i < WHLRNDUP.Length; ++i)
{
if (i > WCRC)
{
WHLRNDUP[i] = (byte)(WCRC + WHLPOS[WHLNDX[i - WCRC]]);
}
else
{
WHLRNDUP[i] = WHLPOS[WHLNDX[i]];
}
}
Func <ushort, int> nmbts = (v) => { var acc = 0; while (v != 0)
{
acc += (int)v & 1; v >>= 1;
}
return(acc); };
CLUT = new byte[1 << 16]; for (var i = 0; i < CLUT.Length; ++i)
{
CLUT[i] = (byte)nmbts((ushort)(i ^ -1));
}
PRLUT = new byte[WHTS]; for (var i = 0; i < PRLUT.Length; ++i)
{
var t = (uint)(WHLPOS[i] * 2) + FSTBP; if (t >= WCRC)
{
t -= WCRC;
}
if (t >= WCRC)
{
t -= WCRC;
}
PRLUT[i] = (byte)t;
}
WSLUT = new Wst[WHTS * WHTS]; for (var x = 0u; x < WHTS; ++x)
{
var p = FSTBP + 2u * WHLPOS[x]; var pr = p % WCRC;
for (uint y = 0, pos = (p * p - FSTBP) / 2; y < WHTS; ++y)
{
var m = WHLPTRN[(x + y) % WHTS];
pos %= WCRC; var posn = WHLNDX[pos]; pos += m * pr; var nposd = pos / WCRC; var nposn = WHLNDX[pos - nposd * WCRC];
WSLUT[x * WHTS + posn] = new Wst {
msk = (ushort)(1 << (int)(posn & 0xF)),
mlt = (byte)(m * WPC),
xtr = (byte)(WPC * nposd + (nposn >> 4) - (posn >> 4)),
nxt = (ushort)(WHTS * x + nposn)
};
}
}
MCPY = new ushort[PGSZ]; foreach (var lp in BWHLPRMS.SkipWhile(p => p < FSTCP))
{
var p = (uint)lp;
var k = (p * p - FSTBP) >> 1; var pd = p / WCRC; var kd = k / WCRC; var kn = WHLNDX[k - kd * WCRC];
for (uint w = kd * WPC + (uint)(kn >> 4), ndx = WHLNDX[(2 * WCRC + p - FSTBP) / 2] * WHTS + kn; w < MCPY.Length;)
{
var st = WSLUT[ndx]; MCPY[w] |= st.msk; w += st.mlt * pd + st.xtr; ndx = st.nxt;
}
}
}
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;
}
