输出Bom
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using Microsoft.Office.Interop.Excel; using Microsoft.Office.Core; using System.Data.OleDb; using CommonLibrary; using MECMOD; namespace BOM { public partial class frmBomvsBom : BaseForm { public frmBomvsBom() { InitializeComponent(); } List<string> m_lstColName = new List<string>(); List<int> m_lstSign = new List<int>(); private string m_strbanben = "最新版本"; private string m_strPartNumber = "PART NUMBER\n编号\n(零件号)"; private int all = 0; private System.Data.DataTable fnoReadExcel(string path) { DataSet ds; string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Extended Properties=Excel 8.0;" + "data source=" + path; OleDbConnection myConn = new OleDbConnection(strCon); string strCom = "SELECT * FROM [导出BOM$]"; myConn.Open(); OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn); ds = new DataSet(); myCommand.Fill(ds); myConn.Close(); System.Data.DataTable dt = new System.Data.DataTable(); Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application(); object mis = System.Reflection.Missing.Value; Workbook wb = Excel.Workbooks.Open(path, mis, mis, mis, mis, mis, mis, mis, mis, mis, mis, mis, mis, mis, mis) as Workbook; Worksheet ws = wb.Sheets[1] as Worksheet; string strSheetName = ws.Name; Range user = ws.UsedRange; string str1 = ""; string str2 = ""; for (int ii = 1; ii <= user.Columns.Count; ii++) { string strColName = ""; object cell = fnsChangeA1(ii) + "2"; Range r = user.get_Range(cell, mis); int count = r.MergeArea.Columns.Count; if (r.MergeArea.Columns.Count == 1) { try { dt.Columns.Add(r.Value2.ToString()); } catch { } } else { str1 = r.Value2.ToString(); if (r.MergeArea.Rows.Count == 2) { for (int i = 0; i < r.MergeArea.Columns.Count; i++) { cell = fnsChangeA1(ii + i) + "4"; strColName = str1 + "." + user.get_Range(cell, mis).Value2.ToString(); dt.Columns.Add(strColName); } } else if (r.MergeArea.Rows.Count == 1) { cell = fnsChangeA1(ii) + "3"; int thiscount = r.MergeArea.Columns.Count; if (user.get_Range(cell, mis).MergeArea.Rows.Count == 2) { for (int i = 0; i < thiscount; i++) { cell = fnsChangeA1(ii + i) + "3"; strColName = str1 + "." + user.get_Range(cell, mis).Value2.ToString(); dt.Columns.Add(strColName); } } else if (user.get_Range(cell, mis).MergeArea.Rows.Count == 1) { for (int i = 0; i < r.MergeArea.Columns.Count; i++) { cell = fnsChangeA1(ii + i) + "3"; int forcount = user.get_Range(cell, mis).MergeArea.Columns.Count; str2 = user.get_Range(cell, mis).Value2.ToString(); if (user.get_Range(cell, mis).MergeArea.Rows.Count == 1) { for (int jj = 0; jj < forcount; jj++) { cell = fnsChangeA1(ii + i + jj) + "4"; strColName = str1 + "." + str2 + "." + user.get_Range(cell, mis).Value2.ToString(); dt.Columns.Add(strColName); } } else if (user.get_Range(cell, mis).MergeArea.Rows.Count == 2) { strColName = str1 + "." + str2; dt.Columns.Add(strColName); } i += forcount - 1; } } } } ii += count - 1; } Excel.Quit(); //数据写入dt for (int j = 4; j < ds.Tables[0].Rows.Count; j++) { DataRow dr = dt.NewRow(); for (int m = 0; m < dt.Columns.Count; m++) { dr[m] = ds.Tables[0].Rows[j][m].ToString(); } dt.Rows.Add(dr); } for (int n = 0; n < dt.Columns.Count; n++) { m_lstColName.Add(dt.Columns[n].ColumnName); } return dt; } /// <summary> /// 根据数字序号返回英文序号 /// </summary> /// <param name="strNumber">数字序号</param> /// <returns>英文序号</returns> private string fnsChangeA1(int num) { if (num <= 0) { return ""; } System.Text.ASCIIEncoding ascEn = new ASCIIEncoding(); byte[] bytchar = new byte[] { (byte)(num + 64) }; if (num <= 26)//如果小于直接返回值,不是的话取余后返回 { return ascEn.GetString(bytchar); } else { int first = Convert.ToInt32(num / 26); if (first > num / 26) first--; int mod = num % 26; if (mod == 0) { first--; mod = 26; } bytchar = new byte[] { (byte)(mod + 64) }; return fnsChangeA1(first) + ascEn.GetString(bytchar); } } private List<List<int>> fnoReadTable(System.Data.DataTable dt) { List<List<int>> lstreturn= new List<List<int>>(); List<int> th = new List<int>(); List<int> com = new List<int>(); for (int ii = 0; ii < dt.Rows.Count; ii++) { if (dt.Rows[ii][m_lstColName[2]].ToString() == "") break; if (com.Contains(ii)) continue; th.Add(ii); com.Add(ii); for (int i = 0; i < dt.Rows.Count - ii; i++) { if (dt.Rows[ii + i + 1][m_lstColName[2]].ToString() == "") { lstreturn.Add(th); th = new List<int>(); break; } if (Convert.ToInt32(dt.Rows[ii + i + 1][m_lstColName[2]]) < Convert.ToInt32(dt.Rows[ii][m_lstColName[2]])) { lstreturn.Add(th); th = new List<int>(); break; } if (Convert.ToInt32(dt.Rows[ii + i + 1][m_lstColName[2]]) == Convert.ToInt32(dt.Rows[ii][m_lstColName[2]])) { com.Add(ii + i + 1); th.Add(ii + i + 1); continue; } } } return lstreturn; } private void frmBomvsBom_Load(object sender, EventArgs e) { //调用 } private void btnNew_Click(object sender, EventArgs e) { OpenFileDialog ofd = new OpenFileDialog(); ofd.ShowDialog(); txtNew.Text = ofd.FileName; } private void btnOld_Click(object sender, EventArgs e) { OpenFileDialog ofd = new OpenFileDialog(); ofd.ShowDialog(); txtOld.Text = ofd.FileName; } private void btnOK_Click(object sender, EventArgs e) { if(txtNew.Text == "" || txtOld.Text == "") { LogPrint.MessagBox_Error_OK(this, "请选择新BOM和旧BOM。"); //MessageBox.Show("请选择新BOM和旧BOM","错误"); return; } System.Data.DataTable dtNew = fnoReadExcel(txtNew.Text); System.Data.DataTable dtOld = fnoReadExcel(txtOld.Text); List<List<int>> lstNew = fnoReadTable(dtNew); List<List<int>> lstOld = fnoReadTable(dtOld); List<List<int>> lstBack = fnoCompare(lstNew, lstOld, dtNew, dtOld); } private List<List<int>> fnoCompare(List<List<int>> lstNew, List<List<int>> lstOld, System.Data.DataTable dtNew, System.Data.DataTable dtOld) { List<List<int>> lstReturn = new List<List<int>>(); List<int> lstChange = new List<int>(); List<int> lstDel = new List<int>(); List<int> lstAdd = new List<int>(); List<int> newprodlist = lstNew[0]; List<int> oldprodlist = lstOld[0]; if(dtNew.Rows[newprodlist[0]][m_strPartNumber].ToString()!=dtOld.Rows[oldprodlist[0]][m_strPartNumber].ToString()) return null; if (dtNew.Rows[newprodlist[0]][m_strbanben].ToString() != dtOld.Rows[oldprodlist[0]][m_strbanben].ToString()) lstChange.Add(0); for (int ii = 1; ii < lstNew.Count; ii++) { List<int> lstinew = lstNew[ii]; int oldIndex = fniOldIndex(lstNew, lstOld, dtNew, dtOld, ii); List<int> lstiold = lstOld[oldIndex]; } return lstReturn; } private int fniOldIndex(List<List<int>> lstNew, List<List<int>> lstOld, System.Data.DataTable dtNew, System.Data.DataTable dtOld, int newIndex) { int NewPareIndex = lstNew[newIndex][0]-1; string strNewPartNumber = dtNew.Rows[NewPareIndex][m_strPartNumber].ToString(); List<string> lstStrPare = new List<string>(); lstStrPare.Add(strNewPartNumber); while (NewPareIndex > 0) { NewPareIndex = lstNew[dniPareIndex(lstNew, NewPareIndex)][0] - 1; strNewPartNumber = dtNew.Rows[NewPareIndex][m_strPartNumber].ToString(); lstStrPare.Add(strNewPartNumber); } return NewPareIndex; } private int dniPareIndex(List<List<int>> lstNew, int index) { for (int ii = 0; ii < lstNew.Count; ii++) { if (lstNew[ii].Contains(index)) return ii; } return 0; } private void btnCancel_Click(object sender, EventArgs e) { try { this.Close(); } catch (Exception exc) { LogPrint.PrintLine("frmBomvsBom btnCancel_Click Error:" + exc.Message); LogPrint.MessagBox_Error_OK(this, "画面关闭失败。"); } } } }