输出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, "画面关闭失败。");
            }
        }
    }
}

 

转载于:https://www.cnblogs.com/matthui/p/3210627.html