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.Reflection;
using Excel1 = Microsoft.Office.Interop.Excel;
using Infragistics.Win.UltraWinGrid;
using System.Runtime.InteropServices;
using System.Collections;

namespace Core.LZMes.Client.UIM
{
    public partial class UIM010233 : FrmBase
    {
        public UIM010233()
        {
            InitializeComponent();
        }

        public override void ToolBar_Click(object sender, string ToolbarKey)
        {
            switch (ToolbarKey)
            {
                case "query":
                    this.DoQuery();
                    break;
                case "queryRZ":
                    this.DoQueryRZ();
                    break;
                case "export":
                    //this.DataTabletoExcelkk(this.dataSet2.Tables[0]);
                    this.DataTabletoExcelULTRA(ultraGrid2, "网上销售(" + this.textBox_RZ.Text + ")产品信息");
                    this.DataTabletoExcelULTRA(ultraGrid1, "网上销售(" + this.textBox_RZ.Text + ")质量信息");
                    saveWGCoil();  //导出网购卷同时保存钢卷到网购卷记录表中
                    break;
                case "Exit":
                    this.Close();
                    break;
            }
        }

        /// <summary>
        /// 查询冷轧成品库库存数据
        /// </summary>
        private void DoQuery()
        {
            try
            {
                this.dataSet2.Tables[0].Clear();
                this.dataSet1.Tables[0].Clear();
                this.textBox_RZ.Text = "冷轧";    //导出冷轧数据标识

                string startTime = this.ultraDateTimeEditor1.Value != null ? ultraDateTimeEditor1.DateTime.ToString("yyyyMMdd") : "";//入库开始时间
                string endTime = this.ultraDateTimeEditor2.Value != null ? ultraDateTimeEditor2.DateTime.ToString("yyyyMMdd") : "";//入库结束时间
                string specStlGrd = this.textBox3.Text;//牌号
                string coilNo = this.textBox9.Text;//钢卷号
                string wanggou_yn = this.ultraComboEditor1_YN.Value.ToString();

                string[] queryParams = new string[] { startTime, endTime, specStlGrd, coilNo, wanggou_yn };
                List<string[]> list = new List<String[]>();
                list.Add(queryParams);
                CoreClientParam ccp = new CoreClientParam();
                ccp.ServerName = "UIM.UIM010233";
                ccp.MethodName = "queryerjiYardList";
                ccp.ServerParams = new object[] { list };
                ccp.SourceDataTable = this.dataSet2.Tables[0];
                this.ExecuteQueryToDataTable(ccp, CoreInvokeType.Internal);


                string[] queryParams1 = new string[] { startTime, endTime, specStlGrd, coilNo, wanggou_yn };
                List<string[]> list1 = new List<String[]>();
                list1.Add(queryParams1);
                CoreClientParam ccp1 = new CoreClientParam();
                ccp1.ServerName = "UIM.UIM010233";
                ccp1.MethodName = "queryerjizhiliang";
                ccp1.ServerParams = new object[] { list1 };
                ccp1.SourceDataTable = this.dataSet1.Tables[0];
                this.ExecuteQueryToDataTable(ccp1, CoreInvokeType.Internal);
                //设置已经网购卷的颜色
                Color color = Color.PaleGoldenrod;
                foreach (Infragistics.Win.UltraWinGrid.UltraGridRow ugr in ultraGrid2.Rows)
                {
                    string status = ugr.Cells["WANGGOU_YN"].Text;
                    if ("是".Equals(status))
                    {
                        color = Color.White;
                    }
                    else
                    {
                        color = Color.PaleGoldenrod;
                    }

                    ugr.Appearance.BackColor = color;
                }
                foreach (Infragistics.Win.UltraWinGrid.UltraGridRow ugr in ultraGrid1.Rows)
                {
                    string status = ugr.Cells["WANGGOU_YN"].Text;
                    if ("是".Equals(status))
                    {
                        color = Color.White;
                    }
                    else
                    {
                        color = Color.PaleGoldenrod;
                    }

                    ugr.Appearance.BackColor = color;
                }

                ////统计钢卷数量，钢卷总重量
                //int coilCount = (int)dataSet2.Tables[0].Compute("count(OLD_SAMPL_NO)", "");
                //double coilWgtSum = 0;

                //foreach (Infragistics.Win.UltraWinGrid.UltraGridRow ugr in ultraGrid2.Rows)
                //{
                //    double actWgt = 0;
                //    try
                //    {
                //        Color color = Color.PaleGoldenrod;
                //        string status = ugr.Cells["TOT_DEC_GRD"].Value.ToString().Trim();
                //        if ("合格".Equals(status))
                //        {
                //            color = Color.White;
                //        }
                //        else if ("不合格".Equals(status))
                //        {
                //            color = Color.FromArgb(255, 128, 128);
                //        }

                //        ugr.Appearance.BackColor = color;

                //        actWgt = double.Parse(ugr.Cells["ACT_WGT"].Value.ToString().Trim());
                //    }
                //    catch (Exception e)
                //    {
                //    }
                //    coilWgtSum += actWgt;
                //}
                
            }
            catch (Exception EX)
            {
                MessageBox.Show(EX.ToString());
            }
        }

        /// <summary>
        /// 查询热轧成品库库存数据
        /// </summary>
        private void DoQueryRZ()
        {
            try
            {
                this.dataSet2.Tables[0].Clear();
                this.dataSet1.Tables[0].Clear();
                this.textBox_RZ.Text = "热轧";  //导出热轧数据标识

                string startTime = this.ultraDateTimeEditor1.Value != null ? ultraDateTimeEditor1.DateTime.ToString("yyyyMMdd") : "";//入库开始时间
                string endTime = this.ultraDateTimeEditor2.Value != null ? ultraDateTimeEditor2.DateTime.ToString("yyyyMMdd") : "";//入库结束时间
                string specStlGrd = this.textBox3.Text;//牌号
                string coilNo = this.textBox9.Text;//钢卷号
                string wanggou_yn = this.ultraComboEditor1_YN.Value.ToString();

                string[] queryParams = new string[] { startTime, endTime, specStlGrd, coilNo, wanggou_yn };
                List<string[]> list = new List<String[]>();
                list.Add(queryParams);
                CoreClientParam ccp = new CoreClientParam();
                ccp.ServerName = "UIM.UIM010233";
                ccp.MethodName = "queryerjiYardListRZ";
                ccp.ServerParams = new object[] { list };
                ccp.SourceDataTable = this.dataSet2.Tables[0];
                this.ExecuteQueryToDataTable(ccp, CoreInvokeType.Internal);


                string[] queryParams1 = new string[] { startTime, endTime, specStlGrd, coilNo, wanggou_yn };
                List<string[]> list1 = new List<String[]>();
                list1.Add(queryParams1);
                CoreClientParam ccp1 = new CoreClientParam();
                ccp1.ServerName = "UIM.UIM010233";
                ccp1.MethodName = "queryerjizhiliangRZ";
                ccp1.ServerParams = new object[] { list1 };
                ccp1.SourceDataTable = this.dataSet1.Tables[0];
                this.ExecuteQueryToDataTable(ccp1, CoreInvokeType.Internal);

                //设置已经网购卷的颜色
                Color color = Color.PaleGoldenrod;
                foreach (Infragistics.Win.UltraWinGrid.UltraGridRow ugr in ultraGrid2.Rows)
                {
                    string status = ugr.Cells["WANGGOU_YN"].Text;
                    if ("是".Equals(status))
                    {
                        color = Color.White;
                    }
                    else {
                        color = Color.PaleGoldenrod;
                    }

                    ugr.Appearance.BackColor = color;
                }
                foreach (Infragistics.Win.UltraWinGrid.UltraGridRow ugr in ultraGrid1.Rows)
                {
                    string status = ugr.Cells["WANGGOU_YN"].Text;
                    if ("是".Equals(status))
                    {
                        color = Color.White;
                    }
                    else
                    {
                        color = Color.PaleGoldenrod;
                    }

                    ugr.Appearance.BackColor = color;
                }
                ////统计钢卷数量，钢卷总重量
                //int coilCount = (int)dataSet2.Tables[0].Compute("count(OLD_SAMPL_NO)", "");
                //double coilWgtSum = 0;

                //foreach (Infragistics.Win.UltraWinGrid.UltraGridRow ugr in ultraGrid2.Rows)
                //{
                //    double actWgt = 0;
                //    try
                //    {
                //        Color color = Color.PaleGoldenrod;
                //        string status = ugr.Cells["TOT_DEC_GRD"].Value.ToString().Trim();
                //        if ("合格".Equals(status))
                //        {
                //            color = Color.White;
                //        }
                //        else if ("不合格".Equals(status))
                //        {
                //            color = Color.FromArgb(255, 128, 128);
                //        }

                //        ugr.Appearance.BackColor = color;

                //        actWgt = double.Parse(ugr.Cells["ACT_WGT"].Value.ToString().Trim());
                //    }
                //    catch (Exception e)
                //    {
                //    }
                //    coilWgtSum += actWgt;
                //}

            }
            catch (Exception EX)
            {
                MessageBox.Show(EX.ToString());
            }
        }

        private void UIM010233_Load(object sender, EventArgs e)
        {
            this.ultraDateTimeEditor1.Value = "";
            this.ultraDateTimeEditor2.Value = "";
        }

        private void DoExport()
        {
            try
            {
                if (this.saveFileDialog1.ShowDialog(this) == DialogResult.OK)
                {
                    string fileName = this.saveFileDialog1.FileName;
                   // this.ultraGridExcelExporter1.Export(ultraGrid1, fileName);
                   // string a = this.ultraGrid2.DisplayLayout.Appearance.Key;

                    this.ultraGridExcelExporter1.Export(ultraGrid1, fileName);
                    System.Diagnostics.Process.Start(fileName);

                    //Infragistics.Excel.Workbook work = new Infragistics.Excel.Workbook();
                    //Infragistics.Excel.Worksheet sheet1 = work.Worksheets.Add("网上销售(" + this.textBox_RZ.Text + ")产品信息");
                    //Infragistics.Excel.Worksheet sheet2 = work.Worksheets.Add("网上销售(" + this.textBox_RZ.Text + ")质量信息");
                    //this.ultraGridExcelExporter1.Export(this.ultraGrid1, sheet1,1,0);
                    //this.ultraGridExcelExporter1.Export(this.ultraGrid1, sheet2, 1, 0);
                    //Infragistics.Excel.BIFF8Writer.WriteWorkbookToFile(work, fileName);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
        

        public void DataTabletoExcelkk(System.Data.DataTable tmpDataTable)
        {
            string fileName = saveFileDialog1.FileName;
            string FileName;
            saveFileDialog1.FileName = "网上销售(" + this.textBox_RZ.Text + ")产品信息";
            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;
            if (tmpDataTable == null)
                return;
            int rowNum = tmpDataTable.Rows.Count;
            int columnNum = tmpDataTable.Columns.Count;
            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.Columns.Count;
            Excel1.Range r = ws.get_Range("A1", NumToExeclRowStr(colnum) + "1");
           // Excel1.Range r = ws.get_Range("A1", "AA1");
            object[] objHeader = new object[colnum];

            //将DataTable的列名导入Excel表第一行
            foreach (DataColumn dc in tmpDataTable.Columns)
            {
                objHeader[columnIndex] = dc.Caption;
                columnIndex++;

            }
            r.Value2 = objHeader;
            //将DataTable中的数据导入Excel中
            for (int i = 0; i < rowNum; i++)
            {
                rowIndex++;
                columnIndex = 0;
                for (int j = 0; j < columnNum; j++)
                {
                    objHeader[columnIndex] = tmpDataTable.Rows[i][j].ToString();
                    columnIndex++;
                }
                r = ws.get_Range("A" + (i + 2), NumToExeclRowStr(colnum) + (i + 2));
                r.Value2 = objHeader;
            }
            //将合同号的数据转换为excel中的数字格式，否则为科学计数法格式
            Excel1.Range RMXNEW;
            string Inf0 = "";
            Inf0 = "V1:" + "V2";
            RMXNEW = ws.get_Range(Inf0, Type.Missing);
            RMXNEW.EntireColumn.HorizontalAlignment = Excel1.XlHAlign.xlHAlignCenter;
            RMXNEW.EntireColumn.NumberFormat = "0";
            Excel1.Range RMXNEW1;
            string Inf1 = "";
            Inf1 = "W1:" + "W2";
            RMXNEW1 = ws.get_Range(Inf1, Type.Missing);
            RMXNEW1.EntireColumn.HorizontalAlignment = Excel1.XlHAlign.xlHAlignCenter;
            RMXNEW1.EntireColumn.NumberFormat = "0"; 
            ////导出到EXCEL中 只需要某些列，下面删除多余的列（已经在datatable中按字段排好序）
            //Excel1.Range RMX1;
            //string Inf1 = "";
            //Inf1 = "A1:" + "W1";
            //RMX1 = ws.get_Range(Inf1, Type.Missing);
            //RMX1.EntireColumn.Delete(0);
            r.EntireColumn.AutoFit();
            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;
            //if (Y == 0) Y = 1;
            return NumTochr(X) + NumTochr(Y);

        }
        //单独作为一个excel表导出
        public void DataTabletoExcelULTRA(UltraGrid tmpDataTable,string rFileName)
        {
            string fileName = saveFileDialog1.FileName;
            string FileName;
            saveFileDialog1.FileName = rFileName;
            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;


            if (tmpDataTable == null)
                return;
            int rowNum = tmpDataTable.Rows.Count;
            int columnNum = tmpDataTable.DisplayLayout.Bands[0].Columns.Count - 1;
            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;  //设置导出表格工作簿sheet数量

            Excel1._Workbook xlBook = (Excel1._Workbook)(xlApp.Workbooks.Add(Missing.Value));//添加新工作簿             
            Excel1.Worksheet ws = (Excel1.Worksheet)xlBook.Worksheets[1];
            ws.Name = rFileName;
            //int colnum = tmpDataTable.DisplayLayout.Bands[0].Columns.Count;
            Excel1.Range r = ws.get_Range("A1", NumToExeclRowStr(columnNum - 1) + "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)
            {
                if (columnIndex < columnNum - 1)
                {
                    objHeader[columnIndex] = dc.Header.Caption;
                    columnIndex++;
                }

            }
            r.Value2 = objHeader;
            //将DataTable中的数据导入Excel中
            int h = 0;
            for (int i = 0; i < rowNum; i++)
            {
                if (tmpDataTable.Rows[i].Cells[columnNum].Text.ToString() == "True")
                {
                    rowIndex++;
                    columnIndex = 0;
                    for (int j = 0; j < columnNum - 1; j++)
                    {
                        objHeader[columnIndex] = tmpDataTable.Rows[h].Cells[j].Text.ToString();
                        columnIndex++;
                    }
                    r = ws.get_Range("A" + (h + 2), NumToExeclRowStr(columnNum) + (h + 2));
                    r.Value2 = objHeader;
                    h++;
                }
            }
            //将合同号的数据转换为excel中的数字格式，否则为科学计数法格式
            Excel1.Range RMXNEW;
            string Inf0 = "";
            Inf0 = "V1:" + "V2";
            RMXNEW = ws.get_Range(Inf0, Type.Missing);
            RMXNEW.EntireColumn.HorizontalAlignment = Excel1.XlHAlign.xlHAlignCenter;
            RMXNEW.EntireColumn.NumberFormat = "0";
            Excel1.Range RMXNEW1;
            string Inf1 = "";
            Inf1 = "W1:" + "W2";
            RMXNEW1 = ws.get_Range(Inf1, Type.Missing);
            RMXNEW1.EntireColumn.HorizontalAlignment = Excel1.XlHAlign.xlHAlignCenter;
            RMXNEW1.EntireColumn.NumberFormat = "0";
            ////导出到EXCEL中 只需要某些列，下面删除多余的列（已经在datatable中按字段排好序）
            //Excel1.Range RMX1;
            //string Inf1 = "";
            //Inf1 = "A1:" + "W1";
            //RMX1 = ws.get_Range(Inf1, Type.Missing);
            //RMX1.EntireColumn.Delete(0);
            r.EntireColumn.AutoFit();
            xlBook.SaveCopyAs(FilePath);
            this.KillSpecialExcel(xlApp);
            MessageBox.Show("数据成功导出到：" + saveFileDialog1.FileName.ToString(),
                "导出完成", MessageBoxButtons.OK, MessageBoxIcon.Information);
            //System.Diagnostics.Process.Start(fileName);
        }

        //将两个表的数据作为sheet导出到一个excel中
        public void DataTabletoExcelULTRA2(UltraGrid  tmpDataTable)
        {
            string fileName = saveFileDialog1.FileName;
            string FileName;
            saveFileDialog1.FileName = "网上销售(" + this.textBox_RZ.Text + ")产品信息";
            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;


            if (tmpDataTable == null)
                return;
            int rowNum = tmpDataTable.Rows.Count;
            int columnNum = tmpDataTable.DisplayLayout.Bands[0].Columns.Count-1;
            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 = 2;  //设置导出表格工作簿sheet数量

            Excel1._Workbook xlBook = (Excel1._Workbook)(xlApp.Workbooks.Add(Missing.Value));//添加新工作簿             
            Excel1.Worksheet ws = (Excel1.Worksheet)xlBook.Worksheets[1];
            ws.Name = "网上销售(" + this.textBox_RZ.Text + ")产品信息";
            //int colnum = tmpDataTable.DisplayLayout.Bands[0].Columns.Count;
            Excel1.Range r = ws.get_Range("A1", NumToExeclRowStr(columnNum - 1) + "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)
            {
                if (columnIndex < columnNum - 1)
                {
                    objHeader[columnIndex] = dc.Header.Caption;
                    columnIndex++;
                }

            }
            r.Value2 = objHeader;
            //将DataTable中的数据导入Excel中
            int h = 0;
            for (int i = 0; i < rowNum; i++)
            {
                if (tmpDataTable.Rows[i].Cells[columnNum].Text.ToString() == "True") 
                { 
                    rowIndex++;
                    columnIndex = 0;
                    for (int j = 0; j < columnNum-1; j++)
                    {
                        objHeader[columnIndex] = tmpDataTable.Rows[h].Cells[j].Text.ToString();
                        columnIndex++;
                    }
                    r = ws.get_Range("A" + (h + 2), NumToExeclRowStr(columnNum) + (h + 2));
                    r.Value2 = objHeader;
                    h++;
                }
            }
            //将合同号的数据转换为excel中的数字格式，否则为科学计数法格式
            Excel1.Range RMXNEW;
            string Inf0 = "";
            Inf0 = "V1:" + "V2";
            RMXNEW = ws.get_Range(Inf0, Type.Missing);
            RMXNEW.EntireColumn.HorizontalAlignment = Excel1.XlHAlign.xlHAlignCenter;
            RMXNEW.EntireColumn.NumberFormat = "0";
            Excel1.Range RMXNEW1;
            string Inf1 = "";
            Inf1 = "W1:" + "W2";
            RMXNEW1 = ws.get_Range(Inf1, Type.Missing);
            RMXNEW1.EntireColumn.HorizontalAlignment = Excel1.XlHAlign.xlHAlignCenter;
            RMXNEW1.EntireColumn.NumberFormat = "0";
            ////导出到EXCEL中 只需要某些列，下面删除多余的列（已经在datatable中按字段排好序）
            //Excel1.Range RMX1;
            //string Inf1 = "";
            //Inf1 = "A1:" + "W1";
            //RMX1 = ws.get_Range(Inf1, Type.Missing);
            //RMX1.EntireColumn.Delete(0);
            r.EntireColumn.AutoFit();
            xlBook = xlBookAddSheet2(ultraGrid1, xlBook); //获得第2个sheet的质量数据
            xlBook.SaveCopyAs(FilePath);
            this.KillSpecialExcel(xlApp);
            MessageBox.Show("数据成功导出到：" + saveFileDialog1.FileName.ToString(),
                "导出完成", MessageBoxButtons.OK, MessageBoxIcon.Information);
            saveWGCoil();  //导出网购卷同时保存钢卷到网购卷记录表中
            //System.Diagnostics.Process.Start(fileName);
        }
        //在同一个excel表中添加质量信息工作表sheet
        public Excel1._Workbook xlBookAddSheet2(UltraGrid tmpDataTable, Excel1._Workbook xlBook)
        {
            if (tmpDataTable == null)
                return xlBook;
            int rowNum = tmpDataTable.Rows.Count;
            int columnNum = tmpDataTable.DisplayLayout.Bands[0].Columns.Count-1;
            int rowIndex = 1;
            int columnIndex = 0;

            Excel1.Worksheet ws = (Excel1.Worksheet)xlBook.Worksheets[2];
            ws.Name = "质量信息";
            //int colnum = tmpDataTable.DisplayLayout.Bands[0].Columns.Count;
            Excel1.Range r = ws.get_Range("A1", NumToExeclRowStr(columnNum-1) + "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)
            {
                if (columnIndex < columnNum - 1)
                {
                    objHeader[columnIndex] = dc.Header.Caption;
                    columnIndex++;
                }

            }
            r.Value2 = objHeader;
            //将DataTable中的数据导入Excel中
            int h = 0;
            for (int i = 0; i < rowNum; i++)
            {
                if (tmpDataTable.Rows[i].Cells[columnNum].Text.ToString() == "True")
                {
                    rowIndex++;
                    columnIndex = 0;
                    for (int j = 0; j < columnNum-1; j++)
                    {
                        objHeader[columnIndex] = tmpDataTable.Rows[h].Cells[j].Text.ToString();
                        columnIndex++;
                    }
                    r = ws.get_Range("A" + (h + 2), NumToExeclRowStr(columnNum-1) + (h + 2));
                    r.Value2 = objHeader;
                    h++;
                }
            }
            //将合同号的数据转换为excel中的数字格式，否则为科学计数法格式
            Excel1.Range RMXNEW;
            string Inf0 = "";
            Inf0 = "V1:" + "V2";
            RMXNEW = ws.get_Range(Inf0, Type.Missing);
            RMXNEW.EntireColumn.HorizontalAlignment = Excel1.XlHAlign.xlHAlignCenter;
            RMXNEW.EntireColumn.NumberFormat = "0";
            Excel1.Range RMXNEW1;
            string Inf1 = "";
            Inf1 = "W1:" + "W2";
            RMXNEW1 = ws.get_Range(Inf1, Type.Missing);
            RMXNEW1.EntireColumn.HorizontalAlignment = Excel1.XlHAlign.xlHAlignCenter;
            RMXNEW1.EntireColumn.NumberFormat = "0";
            ////导出到EXCEL中 只需要某些列，下面删除多余的列（已经在datatable中按字段排好序）
            //Excel1.Range RMX1;
            //string Inf1 = "";
            //Inf1 = "A1:" + "W1";
            //RMX1 = ws.get_Range(Inf1, Type.Missing);
            //RMX1.EntireColumn.Delete(0);
            r.EntireColumn.AutoFit();
            return xlBook;
        }

        #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
        private void button1_Click(object sender, EventArgs e)
        {
            this.DoExport();
        }

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            try
            {
                foreach (UltraGridRow ugr in this.ultraGrid1.Rows)
                {
                    ugr.Cells["CHK"].Value = this.checkBox1.Checked;
                    foreach (UltraGridRow ugr2 in this.ultraGrid2.Rows)
                    {
                        ugr2.Cells["CHK"].Value = this.checkBox1.Checked;
                    }
                    this.ultraGrid2.UpdateData();
                }
                this.ultraGrid1.UpdateData();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.ToString());
            }
        }

        //把已经导出到网购的钢卷保存到网购钢卷记录表中TBC01_WSXS_COIL_COM
        private void saveWGCoil()
        {
            try
            {
                ArrayList list = new ArrayList();
                ArrayList al = null;
                string lineName = "C";
                if (this.textBox_RZ.Text == "热轧") {
                    lineName = "H";
                }
                foreach (UltraGridRow ugr in this.ultraGrid2.Rows)
                {
                    if (Convert.ToBoolean(ugr.Cells["CHK"].Text))
                    {
                        al = new ArrayList();
                        string coil_no = ugr.Cells["OLD_SAMPL_NO"].Text.ToString();
                        DataTable dttb = new DataTable();
                        CoreClientParam ccp = new CoreClientParam();
                        ccp.ServerName = "UIB.COM.ComDBQuery";
                        ccp.MethodName = "doSimpleQuery";
                        ArrayList paramArray = new ArrayList();
                        paramArray.Add("UIM010232_WSXS.SELECT");
                        paramArray.Add(coil_no);
                        ccp.ServerParams = new object[] { paramArray };
                        ccp.SourceDataTable = dttb;
                        this.ExecuteQueryToDataTable(ccp, CoreInvokeType.Internal);
                        //判定是否存在该钢卷
                        if (dttb.Rows.Count == 0)
                        {
                            al.Add("UIM010232_WSXS.INSERT");
                            al.Add(coil_no);
                            al.Add("Y");
                            al.Add(lineName);
                            al.Add("");
                            al.Add(ugr.Cells["TESHUSHUOMING"].Text.ToString());
                            al.Add("UIM010233.saveWGCoil");
                            al.Add(this.UserInfo.GetUserName().ToString());
                            list.Add(al);
                        }
                        //else 
                        //{
                        //    al.Add("UIM010232_WSXS.UPDATE");
                        //    al.Add("Y");
                        //    al.Add(ugr.Cells["TESHUSHUOMING"].Text.ToString());
                        //    al.Add("UIM010233.saveWGCoil");
                        //    al.Add(this.UserInfo.GetUserName().ToString());
                        //    al.Add(coil_no);
                        //}
                       
                    }
                }
                if (list.Count > 0) 
                {
                    CoreClientParam ccp = new CoreClientParam();
                    ccp.ServerName = "UIB.COM.ComDBSave";
                    ccp.MethodName = "doXmlSave";
                    ccp.ServerParams = new object[] { list };
                    ccp.SourceDataTable = this.dataSet2.Tables[0];
                    this.ExecuteQueryToDataTable(ccp, CoreInvokeType.Internal);
                }
                if (this.textBox_RZ.Text == "热轧")
                {
                    this.DoQueryRZ();
                }
                else 
                {
                    this.DoQuery();
                }
                this.checkBox1.Checked = false;
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.ToString());
            }
        }

        //private void ultraGrid2_DoubleClickRow(object sender, DoubleClickRowEventArgs e)
        //{
        //    try
        //    {
        //        if (this.ultraGrid2.ActiveRow == null) return;
        //        string clickColumnID = this.ultraGrid2.ActiveCell.Column.Key;
                
        //        string OLD_SAMPL_NO = this.ultraGrid2.ActiveRow.Cells["OLD_SAMPL_NO"].Value.ToString();
        //        string CHK_VAL = this.ultraGrid2.ActiveRow.Cells["CHK"].Value.ToString();
        //        if (CHK_VAL == "True")
        //        {
        //            this.ultraGrid2.ActiveRow.Cells["CHK"].Value = "False";
        //        }
        //        else
        //        {
        //            this.ultraGrid2.ActiveRow.Cells["CHK"].Value = "True";
        //        }
        //        foreach (UltraGridRow ugr in this.ultraGrid1.Rows)
        //        {
        //            if (OLD_SAMPL_NO.Equals(ugr.Cells["OLD_SAMPL_NO"].Text.ToString()))
        //            {
        //                if (CHK_VAL == "True")
        //                {
        //                    ugr.Cells["CHK"].Value = "False";
        //                }
        //                else
        //                {
        //                    ugr.Cells["CHK"].Value = "True";
        //                }
        //            }
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        System.Diagnostics.Debug.WriteLine(ex.ToString());
        //    }
            
        //}
        
        private void ultraGrid2_CellChange(object sender, CellEventArgs e)
        {
            try
            {
                if (this.ultraGrid2.ActiveRow == null) return;
                string clickColumnID = this.ultraGrid2.ActiveCell.Column.Key;
                if (clickColumnID.Equals("CHK"))
                {
                    string OLD_SAMPL_NO = this.ultraGrid2.ActiveRow.Cells["OLD_SAMPL_NO"].Value.ToString();
                    string CHK_VAL = this.ultraGrid2.ActiveRow.Cells["CHK"].Text.ToString();
                    //if (CHK_VAL == "True")
                    //{
                    //    this.ultraGrid2.ActiveRow.Cells["CHK"].Value = "False";
                    //}
                    //else
                    //{
                    //    this.ultraGrid2.ActiveRow.Cells["CHK"].Value = "True";
                    //}
                    foreach (UltraGridRow ugr in this.ultraGrid1.Rows)
                    {
                        if (OLD_SAMPL_NO.Equals(ugr.Cells["OLD_SAMPL_NO"].Text.ToString()))
                        {
                            if (CHK_VAL == "True")
                            {
                                ugr.Cells["CHK"].Value = "True";
                            }
                            else
                            {
                                ugr.Cells["CHK"].Value = "False";
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.ToString());
            }
        }

        private void ultraGrid1_CellChange(object sender, CellEventArgs e)
        {
            try
            {
                if (this.ultraGrid1.ActiveRow == null) return;
                string clickColumnID = this.ultraGrid1.ActiveCell.Column.Key;
                if (clickColumnID.Equals("CHK"))
                {
                    string OLD_SAMPL_NO = this.ultraGrid1.ActiveRow.Cells["OLD_SAMPL_NO"].Value.ToString();
                    string CHK_VAL = this.ultraGrid1.ActiveRow.Cells["CHK"].Text.ToString();
                    //if (CHK_VAL == "True")
                    //{
                    //    this.ultraGrid2.ActiveRow.Cells["CHK"].Value = "False";
                    //}
                    //else
                    //{
                    //    this.ultraGrid2.ActiveRow.Cells["CHK"].Value = "True";
                    //}
                    foreach (UltraGridRow ugr in this.ultraGrid2.Rows)
                    {
                        if (OLD_SAMPL_NO.Equals(ugr.Cells["OLD_SAMPL_NO"].Text.ToString()))
                        {
                            if (CHK_VAL == "True")
                            {
                                ugr.Cells["CHK"].Value = "True";
                            }
                            else
                            {
                                ugr.Cells["CHK"].Value = "False";
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.ToString());
            }
        }
    }
}