MES_Client/TGMES/Core.StlMes.Client.SalePrice/BaseForm/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;
using com.steering.pss.sale.price.entity;


namespace Core.StlMes.Client.SalePrice.BaseForm
{
    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 (count != 5)
                //{
                //    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 = "";
           
                path = System.Windows.Forms.Application.StartupPath + "\\PriceTemplate\\价格导出.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;

            ArrayList parm = new ArrayList();
             ArrayList parm_price = new ArrayList();
          
            SlmPriceBasepriceLineEntity sop = new SlmPriceBasepriceLineEntity();

            foreach (UltraGridRow row in ultraGrid1.Rows)
            {
                if (row.Cells["选择"].Value.ToString().ToUpper() == "TRUE")
                {
                    flag += 1;

                   

                    
                   // sop.PriceLineId = row.Cells[1].Value.ToString();
                    sop.PriceHeadId = row.Cells[1].Value.ToString();
                    sop.FrameId = "A000001";

                    
                    sop.WaijingBegin =Decimal.Parse(row.Cells[2].Value.ToString());
                    sop.WaijingEnd= Decimal.Parse(row.Cells[3].Value.ToString());
                    sop.BihouBegin  = Decimal.Parse(row.Cells[4].Value.ToString());
                    sop.BihouEnd = Decimal.Parse(row.Cells[5].Value.ToString());
                   // sop.Specificion = row.Cells[6].Value.ToString();
                    if (row.Cells[7].Value.ToString().Trim()=="吨")
                    {
                        sop.PriceUnit="121503";
                    }
                    if (row.Cells[7].Value.ToString().Trim() == "支")
                    {
                        sop.PriceUnit = "121501";
                    }
                    if (row.Cells[7].Value.ToString().Trim() == "米")
                    {
                        sop.PriceUnit = "121502";
                    }
                    if (row.Cells[7].Value.ToString().Trim() == "英镑")
                    {
                        sop.PriceUnit = "121504";
                    }
                    if (row.Cells[7].Value.ToString().Trim() == "英尺")
                    {
                        sop.PriceUnit = "121505";
                    } 

                    
                    sop.PriceNum = Decimal.Parse(row.Cells[8].Value.ToString());
                   // sop.Validflag ="1";
                    sop.CreateName = UserInfo.GetUserName();




                    if (row.Cells[2].Value.ToString() == "" || row.Cells[3].Value.ToString() == "" || row.Cells[4].Value.ToString() == "" || row.Cells[5].Value.ToString() == "")
                    {
                        MessageUtil.ShowWarning("外径壁厚范围不能为空！");
                        return;
                    }
                    else
                    {
                        if (!StringUtil.IsNumber(row.Cells[2].Value.ToString()) || !StringUtil.IsNumber(row.Cells[3].Value.ToString()) || !StringUtil.IsNumber(row.Cells[4].Value.ToString()) || !StringUtil.IsNumber(row.Cells[5].Value.ToString()))
                        {
                            MessageUtil.ShowWarning("外径壁厚范围必须为数字！");
                            return;
                        }
                    }

                    parm_price.Add(JSONFormat.Format(sop));
                 


                  

                }
            }

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

            if (MessageUtil.ShowYesNoAndQuestion("是否确认导入数据！") == DialogResult.No) return;


            int i = ServerHelper.SetData("com.steering.pss.sale.price.server.CorePriceFramePrice.save",
                  new object[] { parm, parm_price }, this.ob);




            if (i > 0)
            {
                MessageUtil.ShowTips("数据保存成功！");
              
            }
            else
            {
                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;
                }
            }

        }

    }
}