XinYu_LZMES_Client/Core.LZMes.Client.UIK/YARD_REPORT/StorageStandbyWGJ.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 System.Collections;
using System.Reflection;
using Excel1 = Microsoft.Office.Interop.Excel;
using Infragistics.Win.UltraWinGrid;
using System.Runtime.InteropServices;

namespace Core.LZMes.Client.UIK
{
    public partial class StorageStandbyWGJ : FrmBase
    {
        //记录前一次入库垛位
        internal string before_yard = "1-01A-01";

        public StorageStandbyWGJ()
        {
            InitializeComponent();
        }

        public override void ToolBar_Click(object sender, string ToolbarKey)
        {
            switch (ToolbarKey)
            {
                case "Query":
                    this.DoQuery();
                    break;
                case "Export":
                    this.DoExport();
                    break;
            }
        }

        private void DoQuery()
        {
            try
            {
      
                string starttiem = this.ultraDateTimeEditor5.Value != null ? this.ultraDateTimeEditor5.DateTime.ToString("yyyyMMdd") : "";//出库时间
                string endtime = this.ultraDateTimeEditor4.Value != null ? this.ultraDateTimeEditor4.DateTime.ToString("yyyyMMdd") : "";//出库时间


                bool flag = false;
                this.dataSet1.Tables[0].Clear();
                CoreClientParam ccp = new CoreClientParam();
                ccp.ServerName = "UIB.COM.ComDBQuery";
                ccp.MethodName = "doSimpleQuery";
                ArrayList paramArray = new ArrayList();
                paramArray.Add("UIK050010_06.SELECT");
                paramArray.Add(starttiem);
                paramArray.Add(endtime);
                ccp.ServerParams = new object[] { paramArray };
                ccp.SourceDataTable = this.dataSet1.Tables[0];
                this.ExecuteQueryToDataTable(ccp, CoreInvokeType.Internal);
                System.Diagnostics.Debug.WriteLine(this.dataSet1.Tables[0].ToString());
                DataRowCollection drs1 = dataSet1.Tables[0].Rows;
                if (drs1.Count <= 0)
                {
                    MessageBox.Show("该时间内没有入库待机钢卷");
                }
                
            }
            catch (Exception EX)
            {
                MessageBox.Show(EX.ToString());
            }
        }
        
        /// <summary>
        /// 导出
        /// </summary>

        
        private void DoExport()
        {
            try
            {
                //if (this.saveFileDialog1.ShowDialog(this) == DialogResult.OK)
                //{
                //    string fileName = this.saveFileDialog1.FileName;
                //    ultraGridExcelExporter1.Export(ultraGrid1, fileName);
                //    System.Diagnostics.Process.Start(fileName);
                //}
              this.DataTabletoExcelULTRA(ultraGrid1);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private void StorageStandbyWGJ_Load(object sender, EventArgs e)
        {
            this.ultraCheckEditor1.Checked = true;
            this.ultraCheckEditor1.Checked = false ;
        }

        private void ultraCheckEditor1_CheckedChanged(object sender, EventArgs e)
        {
            /*if (this.ultraCheckEditor1.Checked == true)
            {
                this.ultraCalendarSTRATTIME.Enabled = false;
                this.ultraCalendarENDTIME.Enabled = false;
            }*/

            /*if (this.ultraCheckEditor1.Checked == false)
            {
                this.ultraCalendarENDTIME.Enabled = true;
                this.ultraCalendarSTRATTIME.Enabled = true;
            }*/
        }

        private void ultraGrid1_DoubleClickRow(object sender, Infragistics.Win.UltraWinGrid.DoubleClickRowEventArgs e)
        {
            Infragistics.Win.UltraWinGrid.UltraGridRow uRow = ultraGrid1.ActiveRow;
            String coilNo = uRow.Cells["OLD_SAMPL_NO"].Value.ToString();
            String curProgCd = "PRC";

            this.textBox1.Text = coilNo;

            string entryShift = this.UserInfo.GetUserOrder();//入库班次
            string entryGroup = this.UserInfo.GetUserGroup();//入库班组
            string entryDtime = this.ultraDateTimeEditor1.DateTime.ToString("yyyyMMddHHmmss");//入库时间
            string reg_id = this.UserInfo.GetUserID();//操作人

            StorageStandby_Sub dlg = new StorageStandby_Sub(this, 1);
            if (DialogResult.OK == dlg.ShowDialog(this))
            {
                //string yardAddr = this.textBox3.Text.ToString().Trim();//垛位
                //if (yardAddr == "" && yardAddr.Length != 13)
                //{
                //    MessageBox.Show("垛位输入有误！");
                //    return;
                //}
                //string[] tmpArray = yardAddr.Split('-');

                string yardAddr = dlg.YARD_ADDR.ToUpper();//垛位
                string[] tmpArray = yardAddr.Split('-');

                try
                {
                    if (tmpArray[1].Length < 3)
                    {
                        tmpArray[1] = "0" + tmpArray[1];
                    }
                    if (tmpArray[2].Length < 2)
                    {
                        tmpArray[2] = "0" + tmpArray[2];
                    }
                    if (Convert.ToInt32(tmpArray[0]) != 1)
                    {
                        MessageBox.Show("垛位输入有误，请重新输入！");
                        return;
                    }
                    //if (tmpArray[1] > "17A")
                    //{
                    //    MessageBox.Show("垛位输入有误，请重新输入！");
                    //    return;
                    //}
                    if (Convert.ToInt32(tmpArray[2]) > 69)
                    {
                        MessageBox.Show("垛位行数不能超过69，请重新输入！");
                        return;
                    }
                    if (Convert.ToInt32(tmpArray[1].Substring(0, 2)) > 17)
                    {
                        MessageBox.Show("垛位列数不能超过17，请重新输入！");
                        return;
                    }
                    if ((tmpArray[1].Substring(2, 1)) != "A")
                    {
                        MessageBox.Show("垛位格式不对，请重新输入！");
                        return;
                    }
                    yardAddr = tmpArray[0] + "-" + tmpArray[1] + "-" + tmpArray[2];
                }
                catch (Exception ex)
                {
                    MessageBox.Show("垛位信息错误！");
                    return;
                }
                
                DataTable dt = new DataTable();
                CoreClientParam ccpx = new CoreClientParam();
                ccpx.ServerName = "UIM.UIM010040";
                ccpx.MethodName = "SelectCoilYard";
                ccpx.ServerParams = new object[] { yardAddr };
                ccpx.SourceDataTable = dt;
                this.ExecuteQueryToDataTable(ccpx, CoreInvokeType.Internal);

                if (dt.Rows.Count <= 0)
                {

                    CoreClientParam ccp = new CoreClientParam();
                    ccp.ServerName = "UIM.UIM010040";
                    ccp.MethodName = "saveCoilYard";
                    ccp.ServerParams = new object[] { coilNo, yardAddr, entryShift, entryGroup, entryDtime, reg_id, curProgCd };

                    ccp = this.ExecuteNonQuery(ccp, CoreInvokeType.Internal);
                    if (ccp.ReturnCode != 0)
                    {
                        return;
                    }
                    else
                    {
                        MessageBox.Show("入库成功！");
                    }
                    
                    this.DoQuery();
                }
                else 
                {
                    MessageBox.Show("该位置上已经有了钢卷，请重新输入!");
                    return;
                }
                
            }
            else
            {
                return;
            }
        }


        /// <summary>
        /// 查询区域中一行的垛位信息
        /// </summary>
        /// <param name="areayType"></param>
        /// <param name="areayNo"></param>
        /// <param name="yardCol"></param>
        internal void queryYardRows(int areaType, int areaNo, string yardCol)
        {
            this.dataSet3.Tables[0].Clear();
            CoreClientParam ccp = new CoreClientParam();
            ccp.ServerName = "UIM.UIM010070";
            ccp.MethodName = "queryYardRows";
            ccp.ServerParams = new object[] { areaType, areaNo, yardCol };
            ccp.SourceDataTable = this.dataSet3.Tables[0];

            this.ExecuteQueryToDataTable(ccp, CoreInvokeType.Internal);
        }

        /// <summary>
        /// 查询区域中一行的垛位中钢卷信息
        /// </summary>
        /// <param name="areayType"></param>
        /// <param name="areayNo"></param>
        /// <param name="yardCol"></param>
        internal void queryYardRowCoils(int areaType, int areaNo, string yardCol)
        {
            this.dataSet2.Tables[0].Clear();
            CoreClientParam ccp = new CoreClientParam();
            ccp.ServerName = "UIM.UIM010070";
            ccp.MethodName = "queryYardRowCoils";
            ccp.ServerParams = new object[] { areaType, areaNo, yardCol };
            ccp.SourceDataTable = this.dataSet2.Tables[0];

            this.ExecuteQueryToDataTable(ccp, CoreInvokeType.Internal);
        }

        internal int queryMinYardRow(int areaType, int areaNo, string yardCol)
        {
            int minRow = 1;
            CoreClientParam ccp = new CoreClientParam();
            ccp.ServerName = "UIM.UIM010070";
            ccp.MethodName = "queryMinYardRow";
            ccp.ServerParams = new object[] { areaType, areaNo, yardCol };
            ccp = this.ExecuteQuery(ccp, CoreInvokeType.Internal);

            if (ccp.ReturnObject != null)
            {
                minRow = int.Parse(ccp.ReturnObject.ToString());
            }

            return minRow;
        }

        /// <summary>
        /// 查询库存区域
        /// </summary>
        /// <param name="areaType"></param>
        /// <returns></returns>
        internal ArrayList queryYardAreas(int areaType)
        {
            CoreClientParam ccp = new CoreClientParam();
            ccp.ServerName = "UIM.UIM010040";
            ccp.MethodName = "queryYardAreas";
            ccp.ServerParams = new object[] { areaType };
            ccp = this.ExecuteQuery(ccp, CoreInvokeType.Internal);
            ArrayList retList = (ArrayList)ccp.ReturnObject;
            return retList;
        }

        /// <summary>
        /// 查询库存区分
        /// </summary>
        /// <param name="areaNo"></param>
        /// <returns></returns>
        internal ArrayList queryYardColsByArea(int areaNo)
        {
            CoreClientParam ccp = new CoreClientParam();
            ccp.ServerName = "UIM.UIM010040";
            ccp.MethodName = "queryYardColsByArea";
            ccp.ServerParams = new object[] { areaNo };
            ccp = this.ExecuteQuery(ccp, CoreInvokeType.Internal);
            ArrayList retList = (ArrayList)ccp.ReturnObject;
            return retList;
        }
        
        public void DataTabletoExcelULTRA(UltraGrid tmpDataTable)
        {
            string fileName = saveFileDialog1.FileName;
            string FileName;
            saveFileDialog1.FileName = "入库待机钢卷";
            saveFileDialog1.DefaultExt = ".xls";
            saveFileDialog1.Filter = "excel文件(*.xls)|*.xls|所有文件(*.*)|*.*";
            if (this.saveFileDialog1.ShowDialog(this) == DialogResult.OK)
            {
                fileName = saveFileDialog1.FileName;
                FileName = fileName;
            }
            else
            {
                return;
            }
            string FilePath = saveFileDialog1.FileName;
            int rowNum = tmpDataTable.Rows.Count;
            //页面显示的列数
            int columnNum = tmpDataTable.DisplayLayout.Bands[0].Columns.BoundColumnsCount;
            int rowIndex = 1;
            int columnIndex = 0;

            Excel1.Application xlApp = new Excel1.ApplicationClass();
            //Microsoft.Office.Interop.Excel.Application xlApp;
            //xlApp = new Microsoft.Office.Interop.Excel.Application();
            xlApp.DefaultFilePath = "";
            xlApp.DisplayAlerts = false;
            xlApp.SheetsInNewWorkbook = 1;

            Excel1._Workbook xlBook = (Excel1._Workbook)(xlApp.Workbooks.Add(Missing.Value));//添加新工作簿             
            Excel1.Worksheet ws = (Excel1.Worksheet)xlBook.Worksheets[1];
            //int colnum = tmpDataTable.DisplayLayout.Bands[0].Columns.Count;
            Excel1.Range r = ws.get_Range("A1", NumToExeclRowStr(columnNum) + "1");
            // Excel1.Range r = ws.get_Range("A1", "AA1");
            object[] objHeader = new object[columnNum];

            //将DataTable的列名导入Excel表第一行
            foreach (UltraGridColumn dc in tmpDataTable.DisplayLayout.Bands[0].Columns)
            {
                objHeader[columnIndex] = dc.Header.Caption;
                columnIndex++;
                if (columnIndex == columnNum) break;
            }
            r.Value2 = objHeader;
            //设置第一行表头格式
            //r = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, columnNum]);
            //设置  域 Range  的颜色  
            r.Interior.ColorIndex = 17;
            r.Font.Size = 9;
            // r.get_Characters(0, s1.Length).Font.Size = 20;
            r.RowHeight = 30;
            r.HorizontalAlignment = Excel1.XlHAlign.xlHAlignCenter;
            //r.Font.Name = "隶书"; 
            //r.Font.Color = clBlue; 
            //r.Font.Bold = true;
            r.Borders.LineStyle = Excel1.XlLineStyle.xlContinuous;
            //r.Font.UnderLine = True; 
            //将DataTable中的数据导入Excel中
            for (int i = 0; i < rowNum; i++)
            {
                rowIndex++;
                columnIndex = 0;
                for (int j = 0; j < columnNum; j++)
                {
                    //若为订单号 加个单引号转换数据为文本格式
                    if (columnIndex == 3 || columnIndex == 4)
                    {
                        objHeader[columnIndex] = "'" + tmpDataTable.Rows[i].Cells[j].Text.ToString();
                    }
                    else
                    {
                        objHeader[columnIndex] = tmpDataTable.Rows[i].Cells[j].Text.ToString();
                    }
                    columnIndex++;
                }
                r = ws.get_Range("A" + (i + 2), NumToExeclRowStr(columnNum) + (i + 2));
                r.Value2 = objHeader;
                r.Borders.LineStyle = Excel1.XlLineStyle.xlContinuous;
                r.Font.Size = 9;
            }
            
            //将合同号的数据转换为excel中的数字格式，否则为科学计数法格式
            //Excel1.Range RMXNEW;
            //string Inf0 = "";
            //Inf0 = "Z1:" + "Z2";
            //RMXNEW = ws.get_Range(Inf0, Type.Missing);
            //RMXNEW.EntireColumn.HorizontalAlignment = Excel1.XlHAlign.xlHAlignCenter;
            ////格式化表格数据0为纯数字，@为文本 "yyyy-mm-dd" General
            //RMXNEW.EntireColumn.NumberFormat = "@";
            //Excel1.Range RMXNEW1;
            //string Inf1 = "";
            //Inf1 = "Y1:" + "Y2";
            //RMXNEW1 = ws.get_Range(Inf1, Type.Missing);
            //RMXNEW1.EntireColumn.HorizontalAlignment = Excel1.XlHAlign.xlHAlignCenter;
            //RMXNEW1.EntireColumn.NumberFormat = "yyyy-mm-dd";
            ////导出到EXCEL中 只需要某些列，下面删除多余的列（已经在datatable中按字段排好序）
            //Excel1.Range RMX1;
            //string Inf1 = "";
            //Inf1 = "A1:" + "W1";
            //RMX1 = ws.get_Range(Inf1, Type.Missing);
            //RMX1.EntireColumn.Delete(0);
            r.EntireColumn.AutoFit();  //自适应宽度
            //Excel1.Borders pborders = r.Borders;

            //设置边框
            //r.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
            //r.Borders.get_Item(XlBordersIndex.xlEdgeBottom).Weight = XlBorderWeight.xlMedium;
            //r.WrapText = true;

            xlBook.SaveCopyAs(FilePath);
            this.KillSpecialExcel(xlApp);
            MessageBox.Show("数据已经成功导出到：" + saveFileDialog1.FileName.ToString(),
                "导出完成", MessageBoxButtons.OK, MessageBoxIcon.Information);
            System.Diagnostics.Process.Start(fileName);
        }
        private string NumTochr(int Num)
        {
            int n = 64 + Num;
            return "" + (Char)n;

        }
        private string NumToExeclRowStr(int Num)
        {
            int X, Y;
            if (Num < 27)
            {
                return NumTochr(Num);
            }
            X = Num / 26;
            Y = Num - X * 26;
            return NumTochr(X) + NumTochr(Y);
        }
        #region 结束EXCEL.EXE进程的方法
        /// <summary>
        /// 结束EXCEL.EXE进程的方法
        /// </summary>
        /// <param name="m_objExcel">EXCEL对象</param>
        [DllImport("user32.dll", SetLastError = true)]
        static extern int GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId);

        public void KillSpecialExcel(Microsoft.Office.Interop.Excel.Application m_objExcel)
        {
            try
            {
                if (m_objExcel != null)
                {
                    int lpdwProcessId;
                    GetWindowThreadProcessId(new IntPtr(m_objExcel.Hwnd), out lpdwProcessId); 
                    System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        #endregion
    }
}