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, "画面关闭失败。");
}
}
}
}
浙公网安备 33010602011771号