MES_Client/TGMES/Core.StlMes.Client.SaleOrder/ReviewForm/FrmExcelToGrid.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using CoreFS.CA06;
using Core.Mes.Client.Comm.Tool;
using System.Data.OleDb;
using System.IO;
using Infragistics.Win.UltraWinGrid;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Threading;
using System.Collections;
using Core.Mes.Client.Comm.Format;
using Core.Mes.Client.Comm.Server;


namespace Core.StlMes.Client.SaleOrder.ReviewForm
{
    public partial class FrmExcelToGrid : FrmBase
    {
        public FrmExcelToGrid()
        {
            InitializeComponent();
        }

        public FrmExcelToGrid(OpeBase ob)
        {
            InitializeComponent();
            this.ob = ob;
        }

        HSSFWorkbook hssfworkbook;    //Office 2003
        XSSFWorkbook xssfworkbook;     //Office 2007 2010
        ISheet sheet = null;

        private string saleOrg;

        /// <summary>
        /// 销售组织
        /// </summary>
        public string SaleOrg
        {
            get { return saleOrg; }
            set { saleOrg = value; }
        }

        /// <summary>
        /// 打开选择的Excel
        /// </summary>
        /// <param name="path"></param>
        private void InitializeWorkbook(string path)
        {
            try
            {
                using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                    sheet = hssfworkbook.GetSheetAt(0);
                }
            }
            catch
            {
                using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
                {
                    xssfworkbook = new XSSFWorkbook(file);
                    sheet = xssfworkbook.GetSheetAt(0);
                }
            }
        }

        delegate void SetGridCallBack(DataTable dt);

        private void SetGrid(DataTable dt)
        {
            if (ultraGrid1.InvokeRequired)
            {
                SetGridCallBack sg = new SetGridCallBack(SetGrid);
                this.Invoke(sg, new object[] { dt });
            }
            else
            {
                ultraGrid1.DataSource = dt;
                ultraGrid1.DisplayLayout.Bands[0].Columns["选择"].Header.CheckBoxVisibility = Infragistics.Win.UltraWinGrid.HeaderCheckBoxVisibility.WhenUsingCheckEditor;
                ultraGrid1.DisplayLayout.Bands[0].Columns[1].Width = 135;
                this.Cursor = Cursors.Default;
            }

        }
        /// <summary>
        /// 将Excel内容转化为DataTable
        /// </summary>
        private void ConvertToDataTable()
        {
            //ISheet sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            IRow iRow = sheet.GetRow(0);
            short count = iRow.LastCellNum;
            if (saleOrg == "100102")
            {
                if (count != 5)
                {
                    MessageUtil.ShowWarning("导入的Excel列数与模板列数不符！");
                    return;
                }
            }
            else
            {
                if (count != 6)
                {
                    MessageUtil.ShowWarning("导入的Excel列数与模板列数不符！");
                    return;
                }
            }
            DataTable dt = new DataTable();
            for (int j = 0; j < count + 1; j++)
            {
                if (j == 0)  //加一列选择框
                {
                    dt.Columns.Add("选择");
                }
                else
                {
                    dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
                }
            }
            dt.Columns[0].DefaultValue = "False";
            dt.Columns[0].DataType = typeof(Boolean);
            while (rows.MoveNext())
            {
                IRow row = null;
                try
                {
                    row = (HSSFRow)rows.Current;
                }
                catch
                {
                    row = (XSSFRow)rows.Current;
                }
                DataRow dr = dt.NewRow();

                for (int i = 1; i < row.LastCellNum + 1; i++)
                {
                    ICell cell = row.GetCell(i - 1);

                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                }
                dt.Rows.Add(dr);
            }
            SetGrid(dt);

        }

        /// <summary>
        /// 使用系统软件打开模板
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            string path = "";
            if (saleOrg == "100102")
            {
                path = System.Windows.Forms.Application.StartupPath + "\\PriceTemplate\\PriceTemplateGM.xls";
            }
            else
            {
                path = System.Windows.Forms.Application.StartupPath + "\\PriceTemplate\\PriceTemplateXS.xls";
            }

            try
            {
                System.Diagnostics.Process.Start(path);
            }
            catch (Exception ex)
            {
                MessageUtil.ShowWarning("没有可用的模板！");
            }
        }

        private void ultraToolbarsManager1_ToolClick(object sender, Infragistics.Win.UltraWinToolbars.ToolClickEventArgs e)
        {
            switch (e.Tool.Key)
            {
                case "Import":
                    ImportExcel();
                    break;
                case "Confirm":
                    ConfirmData();
                    break;
                case "Close":
                    this.Close();
                    break;
                default:
                    break;
            }
        }

        private void ImportExcel()
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "所有文件|*.xls;*.xlsx|xls文件(*.xls)|*.xls|xlsx文件(*.xlsx)|*.xlsx";
            ofd.Multiselect = false;
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                this.Cursor = Cursors.WaitCursor;
                string filePath = ofd.FileName;
                InitializeWorkbook(filePath);
                //ConvertToDataTable();
                Thread t = new Thread(new ThreadStart(ConvertToDataTable));
                t.Start();
            }
        }

        Dictionary<string, string> dic = new Dictionary<string, string>();
        Dictionary<string, ArrayList> price = new Dictionary<string, ArrayList>();
        /// <summary>
        /// 确认数据
        /// </summary>
        private void ConfirmData()
        {
            ultraGrid1.UpdateData();
            int flag = 0;

            foreach (UltraGridRow row in ultraGrid1.Rows)
            {
                if (row.Cells["选择"].Value.ToString().ToUpper() == "TRUE")
                {
                    flag += 1;
                    string orderNoSeq = row.Cells[1].Value.ToString();
                    string balPrice = row.Cells[2].Value.ToString();
                    string outPrice = row.Cells[3].Value.ToString();
                    string traPrice = row.Cells[4].Value.ToString();
                    string pakPrice = row.Cells[5].Value.ToString();
                    string priceBase = row.Cells[6].Value.ToString();

                    if (orderNoSeq == "")
                    {
                        MessageUtil.ShowWarning("合同号(合同号/合同行号）不能为空！");
                        return;
                    }
                    string orderNo = "";
                    string seq = "";
                    try
                    {
                        orderNo = orderNoSeq.Substring(0, orderNoSeq.LastIndexOf("/"));
                        seq = orderNoSeq.Substring(orderNoSeq.LastIndexOf("/") + 1, orderNoSeq.Length - 1 - orderNoSeq.LastIndexOf("/"));
                    }
                    catch (Exception)
                    {
                        MessageUtil.ShowWarning("合同号(合同号/合同行号）跟模板数据规则不符！");
                        return;
                    }

                    if (balPrice == "")
                    {
                        MessageUtil.ShowWarning("结算价不能为空！");
                        return;
                    }
                    else
                    {
                        if (!StringUtil.IsNumber(balPrice))
                        {
                            MessageUtil.ShowWarning("结算价不是数字！");
                            return;
                        }
                    }

                    if (outPrice == "")
                    {
                        if (saleOrg != "100102")
                        {
                            MessageUtil.ShowWarning("出厂价不能为空！");
                            return;
                        }
                    }
                    else
                    {
                        if (!StringUtil.IsNumber(outPrice))
                        {
                            MessageUtil.ShowWarning("结算价不是数字！");
                            return;
                        }
                    }

                    if (traPrice != "")
                    {
                        if (!StringUtil.IsNumber(traPrice))
                        {
                            MessageUtil.ShowWarning("运费不是数字！");
                            return;
                        }
                    }

                    if (pakPrice != "")
                    {
                        if (!StringUtil.IsNumber(pakPrice))
                        {
                            MessageUtil.ShowWarning("吊装费不是数字！");
                            return;
                        }
                    }

                    if (priceBase == "")
                    {
                        MessageUtil.ShowWarning("定价说明不能为空！");
                        return;
                    }

                    if (!dic.ContainsKey(orderNo))
                    {
                        dic.Add(orderNo, priceBase);
                    }

                    ArrayList list = new ArrayList();
                    list.Add(orderNo);
                    list.Add(seq);
                    list.Add(balPrice);
                    list.Add(outPrice);
                    list.Add(traPrice);
                    list.Add(pakPrice);
                    if (price.ContainsKey(orderNo))
                    {
                        ArrayList temp = price[orderNo];
                        temp.Add(list);
                        price[orderNo] = temp;
                    }
                    else
                    {
                        ArrayList parm = new ArrayList();
                        parm.Add(list);
                        price[orderNo] = parm;
                    }

                }
            }

            if (flag == 0)
            {
                MessageUtil.ShowWarning("请勾选或者拖选您要确认的数据！");
                return;
            }

            if (MessageUtil.ShowYesNoAndQuestion("是否确认勾选的数据？") == DialogResult.No) return;

            SlmOrderPriceEntity sop = new SlmOrderPriceEntity();
            //提报信息
            sop.ReportName = UserInfo.GetUserName();
            sop.ReportUnitcode = UserInfo.GetDeptid();
            sop.ReportUnitdesc = UserInfo.GetDepartment();
            sop.ReportDeptcode = ClsBaseInfo.GetDepartIdBySectionId(UserInfo.GetDeptid(), this.ob);
            sop.ReportDeptdesc = ClsBaseInfo.GetDepartBySectionId(UserInfo.GetDeptid(), this.ob);

            //评审通过信息
            sop.DealName = UserInfo.GetUserName();
            sop.DealUnitcode = UserInfo.GetDeptid();
            sop.DealUnitdesc = UserInfo.GetDepartment();
            sop.DealDeptcode = ClsBaseInfo.GetDepartIdBySectionId(UserInfo.GetDeptid(), this.ob);
            sop.DealDeptdesc = ClsBaseInfo.GetDepartBySectionId(UserInfo.GetDeptid(), this.ob);

            CoreClientParam ccp = new CoreClientParam();
            ccp.IfShowErrMsg = false;
            ccp.ServerName = "com.steering.pss.sale.order.CoreExcelToGrid";
            ccp.MethodName = "confirmDataToDB";
            ccp.ServerParams = new object[] { dic, JSONFormat.Format(sop), price };
            ccp = this.ExecuteNonQuery(ccp, CoreInvokeType.Internal);
            if (ccp != null)
            {
                if (ccp.ReturnCode == -1)
                {
                    MessageUtil.ShowWarning(ccp.ReturnInfo);
                    return;
                }
                MessageUtil.ShowTips("数据确认成功！");
            }

        }

        private void ultraGrid1_AfterSelectChange(object sender, Infragistics.Win.UltraWinGrid.AfterSelectChangeEventArgs e)
        {
            foreach (UltraGridRow uRow in ultraGrid1.Selected.Rows)
            {
                if (uRow.GetType() != typeof(Infragistics.Win.UltraWinGrid.UltraGridGroupByRow))
                {
                    uRow.Cells["选择"].Value = true;
                }
            }

        }

    }
}