using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
//using ADODB;
using System.Data.SqlClient;
using System.Data;
using System.Drawing;
using MES_SPC;
//using Microsoft.Office.Core;

public class DirectExcel
{//直接操作Excel
    public DirectExcel()
    {
        //逻辑区域
    }

    /// <summary>
    /// 质量数据查询
    /// 引用页质量数据查询页面QualityData_Query.aspx
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="fileName"></param>
    /// <param name="Count"></param>
    public static void ExcelTable_QualityData_Query_Direct(ref string sql, ref string fileName, ref int Count,ref int ColCount,ref object[,] ColsArray)
    {
        //新建一个excel
        //string connectionString = ApplicationConfiguration.ERP_ConnectionString;

        //Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        //excel.Application.Workbooks.Add(true);
        //Microsoft.Office.Interop.Excel.Workbook wb = (Microsoft.Office.Interop.Excel.Workbook)excel.Application.Workbooks[1];
        //excel.Visible = false;
        //Microsoft.Office.Interop.Excel.Worksheet ws = new Microsoft.Office.Interop.Excel.Worksheet();
        //ws = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;
        //ws.Name = "Sheet1";//新建的Table名称



        //string LastCol = GetLastCellName(ColCount);


        ////设置头
        //Microsoft.Office.Interop.Excel.Range rangeHeader =ws.get_Range("A1", LastCol+"1");
        //rangeHeader.Merge(0);
        //ws.Cells[1, 1] = "合格质量数据";
        ////rangeHeader.Font.Name = "黑体";
        //rangeHeader.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        ////rangeHeader.Borders.LineStyle = 1;     //设置单元格边框的粗细
        ////rangeHeader.EntireColumn.AutoFit();     //自动调整列宽


        ////序号,订单号,发动机号,工位号,测量位置, 测量项目 ,测量值,测量单位,理论值 ,上限值,下限值,合格标志,生产日期

        ////第二部设置列头
        //Microsoft.Office.Interop.Excel.Range rangeCol =ws.get_Range("A2", LastCol+"2");
        ////Excel.Range rangeCol = (Excel.Range)ws.get_Range(ws.Cells[2, 1], ws.Cells[2, ColCount]);
        //object[,] arycol = (object[,])(rangeCol.Value2);
        ////arycol = new object[,] { { "序号", "订单号", "发动机号", "工位号", "测量位置", "测量项目", "测量值", "测量单位", "理论值", "上限值", "下限值", "合格标志", "生产日期" } };
        //arycol = ColsArray;


        ////for (int j = 1; j < SourceTable.Columns.Count + 1; j++)
        ////{
        ////    arycol[1, j] = SourceTable.Columns[j - 1].ColumnName.ToString();
        ////}
        //rangeCol.Value2 = arycol;
        //rangeCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        //rangeCol.Borders.LineStyle = 1;     //设置单元格边框的粗细
        ////rangeCol.EntireColumn.AutoFit();     //自动调整列宽

        //try
        //{
        //    ADODB.Connection conn = new ADODB.Connection();
        //    conn.CommandTimeout = 120;
        //    conn.Open("driver={SQL Server};" + connectionString, "", "", 0);
        //    ADODB.Recordset rs = new ADODB.Recordset();
        //    rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, 0);
        //    Microsoft.Office.Interop.Excel.Range range = ws.get_Range("A3", Missing.Value);
        //    //range.CopyFromRecordset(rs, SourceTable.Rows.Count + 3, SourceTable.Columns.Count);//rs, 65535, 65535
        //    range.CopyFromRecordset(rs, 1000000, 30);//rs, 65535, 65535


        //    //设置序号
        //    Microsoft.Office.Interop.Excel.Range Drange = ws.get_Range("A3","A"+(Count+2).ToString());
        //    Microsoft.Office.Interop.Excel.Range DrangeFirst = ws.get_Range("A3");
        //    DrangeFirst.Value2 = 1;
        //    Drange.DataSeries(Missing.Value, Microsoft.Office.Interop.Excel.XlDataSeriesType.xlDataSeriesLinear, Microsoft.Office.Interop.Excel.XlDataSeriesDate.xlDay, 1, Count, false);


        //    //设置日期格式
        //   //int index = SourceTable.Columns["生产日期"].Ordinal + 1;
        //    Microsoft.Office.Interop.Excel.Range rangeDate = ws.get_Range(LastCol + "3", LastCol + (Count + 2).ToString());
        //    rangeDate.NumberFormatLocal = @"yyyy-mm-dd hh:mm";//日期型格式

        //    Microsoft.Office.Interop.Excel.Range RangeOfAll = ws.get_Range("A3", LastCol + (Count + 2).ToString());
        //    RangeOfAll.Borders.LineStyle = 1;     //设置单元格边框的粗细
        //    RangeOfAll.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        //    RangeOfAll.EntireColumn.AutoFit();     //自动调整列宽

        //    //设置不合格的就为红色
        //    Microsoft.Office.Interop.Excel.FormatCondition cond =
        //        (Microsoft.Office.Interop.Excel.FormatCondition)RangeOfAll.FormatConditions.Add(Microsoft.Office.Interop.Excel.XlFormatConditionType.xlExpression,
        //        Missing.Value, "=$L1=0", Missing.Value);

        //    cond.Interior.PatternColorIndex = Microsoft.Office.Interop.Excel.Constants.xlAutomatic;
        //    cond.Interior.TintAndShade = 0;
        //    cond.Interior.Color = ColorTranslator.ToWin32(Color.Red);
        //    cond.StopIfTrue = false;

        //}
        //catch (Exception ex)
        //{
        //    string str = ex.Message;
        //}
        //finally
        //{
        //    wb.Saved = true;
        //    wb.SaveCopyAs(fileName);//保存
        //    //excel.SaveWorkspace(fileName);
        //    //app.Quit();//关闭进程
        //    wb.Close();
        //    excel.Quit();
        //    GC.Collect();

        //}
    }


    /// <summary>
    /// 工件总成质量数据报表
    /// 引用页QualityData_Query_2.aspx(质量数据导出excel)
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="fileName"></param>
    /// <param name="Count"></param>
    public static void ExcelTable_QualityData_Query_xml_Direct(ref string sql, ref string fileName, ref int Count, ref int ColCount, ref object[,] ColsArray)
    {
        //新建一个excel
        string connectionString = ApplicationConfiguration.ERP_ConnectionString;

        //Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        //excel.Application.Workbooks.Add(true);
        //Microsoft.Office.Interop.Excel.Workbook wb = (Microsoft.Office.Interop.Excel.Workbook)excel.Application.Workbooks[1];
        //excel.Visible = false;
        //Microsoft.Office.Interop.Excel.Worksheet ws = new Microsoft.Office.Interop.Excel.Worksheet();
        //ws = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;
        //ws.Name = "Sheet1";//新建的Table名称

        //string LastCol = GetLastCellName(ColCount);
        ////设置头
        //Microsoft.Office.Interop.Excel.Range rangeHeader = ws.get_Range("A1", LastCol+"1");
        //rangeHeader.Merge(0);
        //ws.Cells[1, 1] = "工件总成质量数据报表";
        ////rangeHeader.Font.Name = "黑体";
        //rangeHeader.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        ////rangeHeader.Borders.LineStyle = 1;     //设置单元格边框的粗细
        ////rangeHeader.EntireColumn.AutoFit();     //自动调整列宽


        ////序号,订单号，总成型号，总成流水号，工序号，工序名称，托盘编号，操作者，测量位置，数据1类型，数据1理论值上限，数据1，数据1理论值下限，数据1单位，数据2类型，数据2理论值上限，数据2，数据2理论值下限，数据2单位，工序状况，总合格标志，生产日期
        ////Orientation = 90以90度进行旋转


        ////第二部设置列头
        //Microsoft.Office.Interop.Excel.Range rangeCol = ws.get_Range("A2", LastCol+"2");
        ////Excel.Range rangeCol = (Excel.Range)ws.get_Range(ws.Cells[2, 1], ws.Cells[2, ColCount]);
        //object[,] arycol = (object[,])(rangeCol.Value2);
        ////arycol = new object[,]  
        ////{{ GetColumnName_Ver("序号"),
        ////    GetColumnName_Ver("订单号"),
        ////    GetColumnName_Ver("总成型号"),
        ////    GetColumnName_Ver("总成流水号"),
        ////    GetColumnName_Ver("工序号"),
        ////    GetColumnName_Ver("工序名称"),
        ////    GetColumnName_Ver("托盘编号"),
        ////    GetColumnName_Ver("操作者"),
        ////    GetColumnName_Ver("测量位置"),
        ////    GetColumnName_Ver("数据1类型"),
        ////    GetColumnName_Ver("数据1理论值上限 "),
        ////    GetColumnName_Ver("数据1"),
        ////    GetColumnName_Ver("数据1理论值下限 "),
        ////    GetColumnName_Ver("数据1单位"),
        ////    GetColumnName_Ver("数据2类型"),
        ////    GetColumnName_Ver("数据2理论值上限 "),
        ////    GetColumnName_Ver("数据2"),
        ////    GetColumnName_Ver("数据2理论值下限 "),
        ////    GetColumnName_Ver("数据2单位"),
        ////    GetColumnName_Ver("工序状况"),
        ////    GetColumnName_Ver("总合格标志"),
        ////    GetColumnName_Ver("生产日期")
        ////} };

        //arycol = ColsArray;

        //rangeCol.Value2 = arycol;
        //rangeCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        //rangeCol.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlTop;
        //rangeCol.Borders.LineStyle = 1;     //设置单元格边框的粗细
        ////rangeCol.EntireColumn.AutoFit();     //自动调整列宽
        ////rangeCol.Orientation = 90;

        //try
        //{
        //    ADODB.Connection conn = new ADODB.Connection();
        //    conn.CommandTimeout = 120;
        //    conn.Open("driver={SQL Server};" + connectionString, "", "", 0);
        //    ADODB.Recordset rs = new ADODB.Recordset();
        //    rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, 0);
        //    Microsoft.Office.Interop.Excel.Range range = ws.get_Range("A3", Missing.Value);
        //    //range.CopyFromRecordset(rs, SourceTable.Rows.Count + 3, SourceTable.Columns.Count);//rs, 65535, 65535
        //    range.CopyFromRecordset(rs, 1000000, 30);//rs, 65535, 65535


        //    //设置序号
        //    Microsoft.Office.Interop.Excel.Range Drange = ws.get_Range("A3", "A" + (Count + 2).ToString());
        //    Microsoft.Office.Interop.Excel.Range DrangeFirst = ws.get_Range("A3");
        //    DrangeFirst.Value2 = 1;
        //    Drange.DataSeries(Missing.Value, Microsoft.Office.Interop.Excel.XlDataSeriesType.xlDataSeriesLinear, Microsoft.Office.Interop.Excel.XlDataSeriesDate.xlDay, 1, Count, false);


        //    //设置日期格式
        //    //int index = SourceTable.Columns["生产日期"].Ordinal + 1;
        //    Microsoft.Office.Interop.Excel.Range rangeDate = ws.get_Range(LastCol+"3", LastCol + (Count + 2).ToString());
        //    rangeDate.NumberFormatLocal = @"yyyy-mm-dd hh:mm";//日期型格式

        //    Microsoft.Office.Interop.Excel.Range RangeOfAll = ws.get_Range("A3", LastCol + (Count + 2).ToString());
        //    RangeOfAll.Borders.LineStyle = 1;     //设置单元格边框的粗细
        //    RangeOfAll.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        //    RangeOfAll.EntireColumn.AutoFit();     //自动调整列宽

        //    //设置工序状况不合格的就为红色
        //    Microsoft.Office.Interop.Excel.Range RangeOfgongxu = ws.get_Range("T3", "T" + (Count + 2).ToString());
        //    Microsoft.Office.Interop.Excel.FormatCondition cond =
        //        (Microsoft.Office.Interop.Excel.FormatCondition)RangeOfgongxu.FormatConditions.Add(Microsoft.Office.Interop.Excel.XlFormatConditionType.xlExpression,
        //        Missing.Value, @"=$T1=""NOK""", Missing.Value);

        //    cond.Interior.PatternColorIndex = Microsoft.Office.Interop.Excel.Constants.xlAutomatic;
        //    cond.Interior.TintAndShade = 0;
        //    cond.Interior.Color = ColorTranslator.ToWin32(Color.Red);
        //    cond.StopIfTrue = false;

        //    //设置工序状况不合格的就为红色
        //    Microsoft.Office.Interop.Excel.Range RangeOfhege = ws.get_Range("U3", "U" + (Count + 2).ToString());
        //    Microsoft.Office.Interop.Excel.FormatCondition cond2 =
        //        (Microsoft.Office.Interop.Excel.FormatCondition)RangeOfhege.FormatConditions.Add(Microsoft.Office.Interop.Excel.XlFormatConditionType.xlExpression,
        //        Missing.Value, @"=$U1=""NOK""", Missing.Value);

        //    cond2.Interior.PatternColorIndex = Microsoft.Office.Interop.Excel.Constants.xlAutomatic;
        //    cond2.Interior.TintAndShade = 0;
        //    cond2.Interior.Color = ColorTranslator.ToWin32(Color.Red);
        //    cond2.StopIfTrue = false;

        //}
        //catch (Exception ex)
        //{
        //    string str = ex.Message;
        //}
        //finally
        //{
        //    wb.Saved = true;
        //    wb.SaveCopyAs(fileName);//保存
        //    //excel.SaveWorkspace(fileName);
        //    //app.Quit();//关闭进程
        //    wb.Close();
        //    excel.Quit();
        //    GC.Collect();

        //}
    }


    /// <summary>
    /// 不合格质量数据报表
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="fileName"></param>
    /// <param name="Count"></param>
    public static void ExcelTable_QualityData_Query2(ref string sql, ref string fileName, ref int Count, ref int ColCount, ref object[,] ColsArray)
    {
        //新建一个excel
        string connectionString = ApplicationConfiguration.ERP_ConnectionString;

        //Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        //excel.Application.Workbooks.Add(true);
        //Microsoft.Office.Interop.Excel.Workbook wb = (Microsoft.Office.Interop.Excel.Workbook)excel.Application.Workbooks[1];
        //excel.Visible = false;
        //Microsoft.Office.Interop.Excel.Worksheet ws = new Microsoft.Office.Interop.Excel.Worksheet();
        //ws = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;
        //ws.Name = "Sheet1";//新建的Table名称

        //string LastCol = GetLastCellName(ColCount);
        ////插入图片
        //float PicLeft,PicTop;
        //string imgPath = System.Web.HttpContext.Current.Server.MapPath("~/images/report_icon.jpg");
        
        ////转换成字节，再由字节转换成图片
        ////byte[] bytesArr = PicToByteArr(imgPath);
        ////System.Drawing.Image bmp = ReturnPhoto(bytesArr);//图片数据
        ////string imgPath=System.Web.HttpContext.Current.Application.


        //Microsoft.Office.Interop.Excel.Range rangePic = ws.get_Range("A1", LastCol+"1");
        //rangePic.Merge(0);

        //PicLeft = Convert.ToSingle(rangePic.Left)+2;
        //PicTop = Convert.ToSingle(rangePic.Top)+ 1;

        //ws.Shapes.AddPicture(imgPath,Microsoft.Office.Core.MsoTriState.msoFalse,
        //Microsoft.Office.Core.MsoTriState.msoTrue,
        //PicLeft,PicTop,(float)305,(float)12);



        ////设置头
        //Microsoft.Office.Interop.Excel.Range rangeHeader = ws.get_Range("A2", LastCol+"2");
        //rangeHeader.Merge(0);
        //ws.Cells[2, 1] = "不合格质量数据报表";
        ////rangeHeader.Font.Name = "黑体";
        //rangeHeader.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        ////rangeHeader.Borders.LineStyle = 1;     //设置单元格边框的粗细
        ////rangeHeader.EntireColumn.AutoFit();     //自动调整列宽


        ////序号,订单号，总成型号，总成流水号，工序号，工序名称，托盘编号，操作者，测量位置，数据1类型，数据1理论值上限，数据1，数据1理论值下限，数据1单位，数据2类型，数据2理论值上限，数据2，数据2理论值下限，数据2单位，工序状况，总合格标志，生产日期
        ////Orientation = 90以90度进行旋转


        ////第二部设置列头
        //Microsoft.Office.Interop.Excel.Range rangeCol = ws.get_Range("A3", LastCol+"3");
        ////Excel.Range rangeCol = (Excel.Range)ws.get_Range(ws.Cells[2, 1], ws.Cells[2, ColCount]);
        //object[,] arycol = (object[,])(rangeCol.Value2);
        ////arycol = new object[,]  
        ////{{ GetColumnName_Ver("序号"),
        ////    GetColumnName_Ver("订单号"),
        ////    GetColumnName_Ver("总成型号"),
        ////    GetColumnName_Ver("总成流水号"),
        ////    GetColumnName_Ver("工序号"),
        ////    GetColumnName_Ver("工序名称"),
        ////    GetColumnName_Ver("测量位置"),
        ////    GetColumnName_Ver("数据类型"),
        ////    GetColumnName_Ver("数据理论值上限"),
        ////    GetColumnName_Ver("数据"),
        ////    GetColumnName_Ver("数据理论值下限 "),
        ////    GetColumnName_Ver("备注"),
        ////} };
        //arycol = ColsArray;

        //rangeCol.Value2 = arycol;
        //rangeCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        //rangeCol.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlTop;
        //rangeCol.Borders.LineStyle = 1;     //设置单元格边框的粗细
        ////rangeCol.EntireColumn.AutoFit();     //自动调整列宽
        ////rangeCol.Orientation = 90;

        //try
        //{
        //    ADODB.Connection conn = new ADODB.Connection();
        //    conn.Open("driver={SQL Server};" + connectionString, "", "", 0);
        //    ADODB.Recordset rs = new ADODB.Recordset();
        //    rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, 0);
        //    Microsoft.Office.Interop.Excel.Range range = ws.get_Range("A4", Missing.Value);
        //    //range.CopyFromRecordset(rs, SourceTable.Rows.Count + 3, SourceTable.Columns.Count);//rs, 65535, 65535
        //    range.CopyFromRecordset(rs, 1000000, 30);//rs, 65535, 65535


        //    //设置序号
        //    Microsoft.Office.Interop.Excel.Range Drange = ws.get_Range("A4", "A" + (Count + 3).ToString());
        //    Microsoft.Office.Interop.Excel.Range DrangeFirst = ws.get_Range("A4");
        //    DrangeFirst.Value2 = 1;
        //    Drange.DataSeries(Missing.Value, Microsoft.Office.Interop.Excel.XlDataSeriesType.xlDataSeriesLinear, Microsoft.Office.Interop.Excel.XlDataSeriesDate.xlDay, 1, Count, false);


        //    Microsoft.Office.Interop.Excel.Range RangeOfAll = ws.get_Range("A4", LastCol + (Count + 3).ToString());
        //    RangeOfAll.Borders.LineStyle = 1;     //设置单元格边框的粗细
        //    RangeOfAll.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        //    RangeOfAll.EntireColumn.AutoFit();     //自动调整列宽

        //    //设置数据列为红色
        //    Microsoft.Office.Interop.Excel.Range RangeOfshuju = ws.get_Range("J4", "J" + (Count + 3).ToString());
        //    RangeOfshuju.Cells.Interior.Color = Color.Red;


        //}
        //catch (Exception ex)
        //{
        //    string str = ex.Message;
        //}
        //finally
        //{
        //    wb.Saved = true;
        //    wb.SaveCopyAs(fileName);//保存
        //    //excel.SaveWorkspace(fileName);
        //    //app.Quit();//关闭进程
        //    wb.Close();
        //    excel.Quit();
        //    GC.Collect();

        //}
    }



    /// <summary>
    /// 合格质量数据
    /// 变速器称重
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="fileName"></param>
    /// <param name="Count"></param>
    public static void ExcelTable_QualityData_Query_kg(ref string sql, ref string fileName, ref int Count, ref int ColCount, ref object[,] ColsArray)
    {
        //新建一个excel
        string connectionString = ApplicationConfiguration.ERP_ConnectionString;

        //Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        //excel.Application.Workbooks.Add(true);
        //Microsoft.Office.Interop.Excel.Workbook wb = (Microsoft.Office.Interop.Excel.Workbook)excel.Application.Workbooks[1];
        //excel.Visible = false;
        //Microsoft.Office.Interop.Excel.Worksheet ws = new Microsoft.Office.Interop.Excel.Worksheet();
        //ws = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;
        //ws.Name = "Sheet1";//新建的Table名称

        //string LastCol = GetLastCellName(ColCount);
        ////设置头
        //Microsoft.Office.Interop.Excel.Range rangeHeader = ws.get_Range("A1", LastCol+"1");
        //rangeHeader.Merge(0);
        //ws.Cells[1, 1] = "合格质量数据";
        ////rangeHeader.Font.Name = "黑体";
        //rangeHeader.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        ////rangeHeader.Borders.LineStyle = 1;     //设置单元格边框的粗细
        ////rangeHeader.EntireColumn.AutoFit();     //自动调整列宽


        ////序号,发动机号，工件重量，抽油后重量，重量差，允许重量差，合格标志，操作时间
        ////Orientation = 90以90度进行旋转


        ////第二部设置列头
        //Microsoft.Office.Interop.Excel.Range rangeCol = ws.get_Range("A2", LastCol+"2");
        ////Excel.Range rangeCol = (Excel.Range)ws.get_Range(ws.Cells[2, 1], ws.Cells[2, ColCount]);
        //object[,] arycol = (object[,])(rangeCol.Value2);
        ////arycol = new object[,] { { "序号", "发动机号", "工件重量(kg)", "抽油后重量(kg)", "重量差(kg)", "允许重量差(kg)", "合格标志", "操作时间" } };
        //arycol = ColsArray;

        //rangeCol.Value2 = arycol;
        //rangeCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        //rangeCol.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlTop;
        //rangeCol.Borders.LineStyle = 1;     //设置单元格边框的粗细
        ////rangeCol.EntireColumn.AutoFit();     //自动调整列宽
        ////rangeCol.Orientation = 90;

        //try
        //{
        //    ADODB.Connection conn = new ADODB.Connection();
        //    conn.Open("driver={SQL Server};" + connectionString, "", "", 0);
        //    ADODB.Recordset rs = new ADODB.Recordset();
        //    rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, 0);
        //    Microsoft.Office.Interop.Excel.Range range = ws.get_Range("A3", Missing.Value);
        //    //range.CopyFromRecordset(rs, SourceTable.Rows.Count + 3, SourceTable.Columns.Count);//rs, 65535, 65535
        //    range.CopyFromRecordset(rs, 1000000, 30);//rs, 65535, 65535


        //    //设置序号
        //    Microsoft.Office.Interop.Excel.Range Drange = ws.get_Range("A3", "A" + (Count + 2).ToString());
        //    Microsoft.Office.Interop.Excel.Range DrangeFirst = ws.get_Range("A3");
        //    DrangeFirst.Value2 = 1;
        //    Drange.DataSeries(Missing.Value, Microsoft.Office.Interop.Excel.XlDataSeriesType.xlDataSeriesLinear, Microsoft.Office.Interop.Excel.XlDataSeriesDate.xlDay, 1, Count, false);


        //    //设置日期格式
        //    //int index = SourceTable.Columns["生产日期"].Ordinal + 1;
        //    Microsoft.Office.Interop.Excel.Range rangeDate = ws.get_Range("J3", "J" + (Count + 2).ToString());
        //    rangeDate.NumberFormatLocal = @"yyyy-mm-dd hh:mm";//日期型格式

        //    Microsoft.Office.Interop.Excel.Range RangeOfAll = ws.get_Range("A3", LastCol + (Count + 2).ToString());
        //    RangeOfAll.Borders.LineStyle = 1;     //设置单元格边框的粗细
        //    RangeOfAll.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        //    RangeOfAll.EntireColumn.AutoFit();     //自动调整列宽

        //    //设置工序状况不合格的就为红色
        //    Microsoft.Office.Interop.Excel.FormatCondition cond =
        //        (Microsoft.Office.Interop.Excel.FormatCondition)RangeOfAll.FormatConditions.Add(Microsoft.Office.Interop.Excel.XlFormatConditionType.xlExpression,
        //        Missing.Value, "=$I1=0", Missing.Value);

        //    cond.Interior.PatternColorIndex = Microsoft.Office.Interop.Excel.Constants.xlAutomatic;
        //    cond.Interior.TintAndShade = 0;
        //    cond.Interior.Color = ColorTranslator.ToWin32(Color.Red);
        //    cond.StopIfTrue = false;
        //}
        //catch (Exception ex)
        //{
        //    string str = ex.Message;
        //}
        //finally
        //{
        //    wb.Saved = true;
        //    wb.SaveCopyAs(fileName);//保存
        //    //excel.SaveWorkspace(fileName);
        //    //app.Quit();//关闭进程
        //    wb.Close();
        //    excel.Quit();
        //    GC.Collect();

        //}
    }

    /// <summary>
    /// 人工合格放行
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="fileName"></param>
    /// <param name="Count"></param>
    public static void ExcelTable_QualityData_Query_okgo(ref string sql, ref string fileName, ref int Count, ref int ColCount, ref object[,] ColsArray)
    {
        //新建一个excel
        //string connectionString = ApplicationConfiguration.ERP_ConnectionString;

        //Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        //excel.Application.Workbooks.Add(true);
        //Microsoft.Office.Interop.Excel.Workbook wb = (Microsoft.Office.Interop.Excel.Workbook)excel.Application.Workbooks[1];
        //excel.Visible = false;
        //Microsoft.Office.Interop.Excel.Worksheet ws = new Microsoft.Office.Interop.Excel.Worksheet();
        //ws = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;
        //ws.Name = "Sheet1";//新建的Table名称

        //string LastCol = GetLastCellName(ColCount);
        ////设置头
        //Microsoft.Office.Interop.Excel.Range rangeHeader = ws.get_Range("A1", LastCol + "1");
        //rangeHeader.Merge(0);
        //ws.Cells[1, 1] = "人工合格放行";
        ////rangeHeader.Font.Name = "黑体";
        //rangeHeader.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        ////rangeHeader.Borders.LineStyle = 1;     //设置单元格边框的粗细
        ////rangeHeader.EntireColumn.AutoFit();     //自动调整列宽


        ////序号,发动机号，工件重量，抽油后重量，重量差，允许重量差，合格标志，操作时间
        ////Orientation = 90以90度进行旋转


        ////第二部设置列头
        //Microsoft.Office.Interop.Excel.Range rangeCol = ws.get_Range("A2", LastCol + "2");
        ////Excel.Range rangeCol = (Excel.Range)ws.get_Range(ws.Cells[2, 1], ws.Cells[2, ColCount]);
        //object[,] arycol = (object[,])(rangeCol.Value2);
        ////arycol = new object[,] { { "序号", "发动机号", "工件重量(kg)", "抽油后重量(kg)", "重量差(kg)", "允许重量差(kg)", "合格标志", "操作时间" } };
        //arycol = ColsArray;

        //rangeCol.Value2 = arycol;
        //rangeCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        //rangeCol.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlTop;
        //rangeCol.Borders.LineStyle = 1;     //设置单元格边框的粗细
        ////rangeCol.EntireColumn.AutoFit();     //自动调整列宽
        ////rangeCol.Orientation = 90;

        //try
        //{
        //    ADODB.Connection conn = new ADODB.Connection();
        //    conn.Open("driver={SQL Server};" + connectionString, "", "", 0);
        //    ADODB.Recordset rs = new ADODB.Recordset();
        //    rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, 0);
        //    Microsoft.Office.Interop.Excel.Range range = ws.get_Range("A3", Missing.Value);
        //    //range.CopyFromRecordset(rs, SourceTable.Rows.Count + 3, SourceTable.Columns.Count);//rs, 65535, 65535
        //    range.CopyFromRecordset(rs, 1000000, 30);//rs, 65535, 65535


        //    //设置序号
        //    Microsoft.Office.Interop.Excel.Range Drange = ws.get_Range("A3", "A" + (Count + 2).ToString());
        //    Microsoft.Office.Interop.Excel.Range DrangeFirst = ws.get_Range("A3");
        //    DrangeFirst.Value2 = 1;
        //    Drange.DataSeries(Missing.Value, Microsoft.Office.Interop.Excel.XlDataSeriesType.xlDataSeriesLinear, Microsoft.Office.Interop.Excel.XlDataSeriesDate.xlDay, 1, Count, false);


        //    //设置日期格式
        //    //int index = SourceTable.Columns["生产日期"].Ordinal + 1;
        //    Microsoft.Office.Interop.Excel.Range rangeDate = ws.get_Range("F3", "F" + (Count + 2).ToString());
        //    rangeDate.NumberFormatLocal = @"yyyy-mm-dd hh:mm";//日期型格式

        //    Microsoft.Office.Interop.Excel.Range RangeOfAll = ws.get_Range("A3", LastCol + (Count + 2).ToString());
        //    RangeOfAll.Borders.LineStyle = 1;     //设置单元格边框的粗细
        //    RangeOfAll.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        //    RangeOfAll.EntireColumn.AutoFit();     //自动调整列宽

        //    //设置工序状况不合格的就为红色
        //    //Microsoft.Office.Interop.Excel.FormatCondition cond =
        //    //    (Microsoft.Office.Interop.Excel.FormatCondition)RangeOfAll.FormatConditions.Add(Microsoft.Office.Interop.Excel.XlFormatConditionType.xlExpression,
        //    //    Missing.Value, "=$I1=0", Missing.Value);

        //    //cond.Interior.PatternColorIndex = Microsoft.Office.Interop.Excel.Constants.xlAutomatic;
        //    //cond.Interior.TintAndShade = 0;
        //    //cond.Interior.Color = ColorTranslator.ToWin32(Color.Red);
        //    //cond.StopIfTrue = false;
        //}
        //catch (Exception ex)
        //{
        //    string str = ex.Message;
        //}
        //finally
        //{
        //    wb.Saved = true;
        //    wb.SaveCopyAs(fileName);//保存
        //    //excel.SaveWorkspace(fileName);
        //    //app.Quit();//关闭进程
        //    wb.Close();
        //    excel.Quit();
        //    GC.Collect();

        //}
    }


    //ExcelTable
    /// <summary>
    /// 物料数据
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="fileName"></param>
    /// <param name="Count"></param>
    public static void ExcelTable_Materials(ref string sql, ref string fileName, ref int Count, ref int ColCount, ref object[,] ColsArray)
    {
        //新建一个excel
        //string connectionString = ApplicationConfiguration.ERP_ConnectionString;

        //Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        //excel.Application.Workbooks.Add(true);
        //Microsoft.Office.Interop.Excel.Workbook wb = (Microsoft.Office.Interop.Excel.Workbook)excel.Application.Workbooks[1];
        //excel.Visible = false;
        //Microsoft.Office.Interop.Excel.Worksheet ws = new Microsoft.Office.Interop.Excel.Worksheet();
        //ws = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;
        //ws.Name = "Sheet1";//新建的Table名称

        //string LastCol = GetLastCellName(ColCount);

        ////设置头
        //Microsoft.Office.Interop.Excel.Range rangeHeader = ws.get_Range("A1", LastCol+"1");
        //rangeHeader.Merge(0);
        //ws.Cells[1, 1] = "物料数据";
        //rangeHeader.Font.Name = "黑体";
        //rangeHeader.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        ////rangeHeader.Borders.LineStyle = 1;     //设置单元格边框的粗细
        ////rangeHeader.EntireColumn.AutoFit();     //自动调整列宽


        ////第二部设置列头
        //Microsoft.Office.Interop.Excel.Range rangeCol = ws.get_Range("A2", LastCol+"2");
        ////Excel.Range rangeCol = (Excel.Range)ws.get_Range(ws.Cells[2, 1], ws.Cells[2, ColCount]);
        //object[,] arycol = (object[,])(rangeCol.Value2);
        ////arycol = new object[,] { { "序号", "订单号", "发动机型号", "总成流水号", "工序号", "工序名称", "零件数量", "零件名称", "零件图号", "零件数量", "物料号", "生产厂家" } };
        //arycol = ColsArray;

        //rangeCol.Value2 = arycol;
        //rangeCol.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        //rangeCol.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlTop;
        //rangeCol.Borders.LineStyle = 1;     //设置单元格边框的粗细
        ////rangeCol.EntireColumn.AutoFit();     //自动调整列宽
        ////rangeCol.Orientation = 90;

        //try
        //{
        //    ADODB.Connection conn = new ADODB.Connection();
        //    conn.Open("driver={SQL Server};" + connectionString, "", "", 0);
        //    ADODB.Recordset rs = new ADODB.Recordset();
        //    rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, 0);
        //    Microsoft.Office.Interop.Excel.Range range = ws.get_Range("A3", Missing.Value);
        //    //range.CopyFromRecordset(rs, SourceTable.Rows.Count + 3, SourceTable.Columns.Count);//rs, 65535, 65535
        //    range.CopyFromRecordset(rs, 1000000, 30);//rs, 65535, 65535


        //    //设置序号
        //    Microsoft.Office.Interop.Excel.Range Drange = ws.get_Range("A3", "A" + (Count + 2).ToString());
        //    Microsoft.Office.Interop.Excel.Range DrangeFirst = ws.get_Range("A3");
        //    DrangeFirst.Value2 = 1;
        //    Drange.DataSeries(Missing.Value, Microsoft.Office.Interop.Excel.XlDataSeriesType.xlDataSeriesLinear, Microsoft.Office.Interop.Excel.XlDataSeriesDate.xlDay, 1, Count, false);


        //    ////设置日期格式
        //    ////int index = SourceTable.Columns["生产日期"].Ordinal + 1;
        //    //Microsoft.Office.Interop.Excel.Range rangeDate = ws.get_Range("H3", "H" + (Count + 2).ToString());
        //    //rangeDate.NumberFormatLocal = @"yyyy-mm-dd hh:mm";//日期型格式

        //    Microsoft.Office.Interop.Excel.Range RangeOfAll = ws.get_Range("A3", LastCol + (Count + 2).ToString());
        //    RangeOfAll.Borders.LineStyle = 1;     //设置单元格边框的粗细
        //    RangeOfAll.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        //    RangeOfAll.EntireColumn.AutoFit();     //自动调整列宽

        //}
        //catch (Exception ex)
        //{
        //    string str = ex.Message;
        //}
        //finally
        //{
        //    wb.Saved = true;
        //    wb.SaveCopyAs(fileName);//保存
        //    //excel.SaveWorkspace(fileName);
        //    //app.Quit();//关闭进程
        //    wb.Close();
        //    excel.Quit();
        //    GC.Collect();

        //}
    }


    /// <summary>
    /// 字段名称各个字符中间增加回车，以保证文本竖着书写。
    /// </summary>
    /// <param name="columnName"></param>
    /// <returns></returns>
    private static string GetColumnName_Ver(string columnName)
    {
        string columnName_tr;
        columnName_tr = "";
        for (int i = 0; i < columnName.Length; i++)
        {
            columnName_tr = columnName_tr + columnName[i] + Environment.NewLine;
        }
        return columnName_tr;
    }

     /// <summary>
    /// 将图片转换为字节数组
    /// </summary>
    /// <param name="path">图片的路径</param>
    /// <returns>字节数组</returns>
    public static byte[] PicToByteArr(string path)
    {
        System.IO.FileStream fs = new System.IO.FileStream(path,System.IO.FileMode.Open);//将图片写入流中。
        int filelength = 0;
        filelength = (int)fs.Length; //获得文件长度
        Byte[] byteArr = new Byte[filelength]; //建树一个字节数组
        fs.Read(byteArr,0,filelength); //按字节俭读取            
        fs.Close();
        return byteArr;
    }

     /// <summary>
    /// 参数是byte返回图片
    /// </summary>
    /// <param name="byteArr">字节数组</param>
    /// <returns>图片</returns>
    public static System.Drawing.Image ReturnPhoto(byte[] byteArr)
    {
        System.IO.MemoryStream ms = new System.IO.MemoryStream(byteArr);
        System.Drawing.Image img = System.Drawing.Image.FromStream(ms);
        ms.Close();
        return img;
    }


    /// <summary>
    /// 根据列和行号得到最终
    /// </summary>
    /// <param name="ColCount"></param>
    /// <param name="RowCount"></param>
    /// <returns></returns>
    public static string GetLastCellName(int ColCount)
    {
        string LastCellName = String.Empty;
        switch (ColCount)
        {
            case 1:
                LastCellName = "A";
                break;
            case 2:
                LastCellName = "B";
                break;
            case 3:
                LastCellName = "C";
                break;
            case 4:
                LastCellName = "D";
                break;
            case 5:
                LastCellName = "E";
                break;
            case 6:
                LastCellName = "F";
                break;
            case 7:
                LastCellName = "G";
                break;
            case 8:
                LastCellName = "H";
                break;
            case 9:
                LastCellName = "I";
                break;
            case 10:
                LastCellName = "J";
                break;
            case 11:
                LastCellName = "K";
                break;
            case 12:
                LastCellName = "L";
                break;
            case 13:
                LastCellName = "M";
                break;
            case 14:
                LastCellName = "N";
                break;
            case 15:
                LastCellName = "O";
                break;
            case 16:
                LastCellName = "P";
                break;
            case 17:
                LastCellName = "Q";
                break;
            case 18:
                LastCellName = "R";
                break;
            case 19:
                LastCellName = "S";
                break;
            case 20:
                LastCellName = "T";
                break;
            case 21:
                LastCellName = "U";
                break;
            case 22:
                LastCellName = "V";
                break;
            case 23:
                LastCellName = "W";
                break;
            case 24:
                LastCellName = "X";
                break;
            case 25:
                LastCellName = "Y";
                break;
            case 26:
                LastCellName = "Z";
                break;
            default: break;
        }
        return LastCellName;
    }


}