mes-client/UAS-出货标签管理(贸易版)/ExportExcel.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 UAS_LabelMachine.Entity;
using UAS_LabelMachine.PublicMethod;

namespace UAS_LabelMachine
{
    public partial class ExportExcel : Form
    {
        ExcelHandler eh = new ExcelHandler();

        DataHelper dh = SystemInf.dh;
        string inoutno;

        public ExportExcel(string pi_inoutno)
        {
            InitializeComponent();

            inoutno = pi_inoutno;
        }

        private void Export_Click(object sender, EventArgs e)
        {
            ExportFileDialog.Description = "选择导出的路径";
            DialogResult result = ExportFileDialog.ShowDialog();
            string custcode = dh.getFieldDataByCondition("prodinout", "pi_cardcode", "pi_inoutno='" + inoutno + "'").ToString();
            if (result == DialogResult.OK)
            {
                StringBuilder sql = new StringBuilder();
                List<CheckBox> conditionbox = new List<CheckBox>();
                if (!CH_PBCODE.Checked && BoxCode.Checked)
                {
                    MessageBox.Show("勾选盒号分页，打印内容请勾选盒号");
                    return;
                }
                if (!CH_SPLITBATCH.Checked && BatchCode.Checked)
                {
                    MessageBox.Show("勾选扩撒批分页，打印内容请勾选扩撒批号");
                    return;
                }
                conditionbox.Add(CH_BLUEFILM);
                conditionbox.Add(CH_PBCODE);
                conditionbox.Add(CH_REMARK);
                conditionbox.Add(CH_SPLITBATCH);
                conditionbox.Add(CH_WATERID);
                conditionbox.Add(ch_code);
                conditionbox.Add(M_Param1);
                conditionbox.Add(H_Param1);
                conditionbox.Add(H_Param2);
                conditionbox.Add(FirstPage_WID);
                conditionbox.Add(FirstPage_YIELD);
                conditionbox.Add(FirstPage_REMARK);
                string SplitType = "";
                if (FixRow.Checked)
                {
                    SplitType = FixRow.Name;
                }
                else if (BatchCode.Checked)
                {
                    SplitType = BatchCode.Name;
                }
                else if (BoxCode.Checked)
                {
                    SplitType = BoxCode.Name;
                }
                DataTable SQL1_ = (DataTable)dh.ExecuteSql("select es_filed,es_datatype,es_caption,es_prefix,es_suffix,es_filed1 from CS_EXPORTSETTING where es_type='DataPage' and es_enable=-1 and es_custcode='" + custcode + "' order by es_detno", "select");
                //没有置顶客户的取空的
                if (SQL1_.Rows.Count == 0)
                {
                    SQL1_ = (DataTable)dh.ExecuteSql("select es_filed,es_datatype,es_caption,es_prefix,es_suffix,es_filed1 from CS_EXPORTSETTING where es_type='DataPage' and es_enable=-1 and es_custcode is null order by es_detno", "select");
                }
                string es_field1 = "";
                for (int i = 0; i < SQL1_.Rows.Count; i++)
                {
                    //不是参数的直接拼接
                    if (SQL1_.Rows[i]["es_datatype"].ToString() != "Param")
                        es_field1 += SQL1_.Rows[i]["es_prefix"].ToString() + "||" + SQL1_.Rows[i]["es_filed"].ToString() + "||" + SQL1_.Rows[i]["es_suffix"].ToString() + ",";
                    //是参数的则先判断是否含有内容
                    else if (
                        (SQL1_.Rows[i]["es_filed"].ToString().Contains(M_Param1.Text) && M_Param1.Visible == true && M_Param1.Checked) ||
                        (SQL1_.Rows[i]["es_filed"].ToString().Contains(M_Param2.Text) && M_Param2.Visible == true && M_Param2.Checked) ||
                        (SQL1_.Rows[i]["es_filed"].ToString().Contains(M_Param3.Text) && M_Param3.Visible == true && M_Param3.Checked) ||
                        (SQL1_.Rows[i]["es_filed"].ToString().Contains(M_Param4.Text) && M_Param4.Visible == true && M_Param4.Checked) ||
                        (SQL1_.Rows[i]["es_filed"].ToString().Contains(H_Param1.Text) && H_Param1.Visible == true && H_Param1.Checked) ||
                        (SQL1_.Rows[i]["es_filed"].ToString().Contains(H_Param2.Text) && H_Param2.Visible == true && H_Param2.Checked) ||
                        (SQL1_.Rows[i]["es_filed"].ToString().Contains(H_Param3.Text) && H_Param3.Visible == true && H_Param3.Checked) ||
                        (SQL1_.Rows[i]["es_filed"].ToString().Contains(H_Param4.Text) && H_Param4.Visible == true && H_Param4.Checked))
                    {
                        es_field1 += SQL1_.Rows[i]["es_prefix"].ToString() + "||" + SQL1_.Rows[i]["es_filed"].ToString() + "||" + SQL1_.Rows[i]["es_suffix"].ToString() + ",";
                    }
                }
                //分页信息的DataTable
                string datasql = "";
                LogicHandler.GetDataSQL(inoutno, es_field1.Substring(0, es_field1.Length - 1), out datasql);
                DataTable dt = (DataTable)dh.ExecuteSql(datasql, "select");
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    for (int j = 0; j < SQL1_.Rows.Count; j++)
                    {
                        if (dt.Columns[i].ColumnName == (SQL1_.Rows[j]["es_prefix"].ToString() + "||" + SQL1_.Rows[j]["es_filed"].ToString() + "||" + SQL1_.Rows[j]["es_suffix"].ToString()).ToUpper())
                        {
                            dt.Columns[i].ColumnName = SQL1_.Rows[j]["es_filed1"].ToString();
                            dt.Columns[i].Caption = SQL1_.Rows[j]["es_caption"].ToString();
                        }
                    }
                }
                //获取首页需要展示的字段
                DataTable SQL_ = (DataTable)dh.ExecuteSql("select es_filed,es_caption,es_prefix,es_suffix,es_filed1 from CS_EXPORTSETTING where es_type='First' and es_enable=-1 and es_custcode='" + custcode + "' order by es_detno", "select");
                if (SQL_.Rows.Count == 0)
                {
                    SQL_ = (DataTable)dh.ExecuteSql("select es_filed,es_caption,es_prefix,es_suffix,es_filed1 from CS_EXPORTSETTING where es_type='First' and es_enable=-1 and es_custcode is null order by es_detno", "select");
                }
                string es_field = "";
                for (int i = 0; i < SQL_.Rows.Count; i++)
                {
                    es_field += SQL_.Rows[i]["es_prefix"].ToString() + "||" + SQL_.Rows[i]["es_filed"].ToString() + "||" + SQL_.Rows[i]["es_suffix"].ToString() + ",";
                }
                //首页信息的DataTable
                sql.Clear();
                sql.Append("select " + es_field.Substring(0, es_field.Length - 1) + "  from FIRSTPAGE_BATCH_VIEW where pi_inoutno='" + inoutno + "'");
                DataTable dt1 = (DataTable)dh.ExecuteSql(sql.ToString(), "select");
                for (int i = 0; i < dt1.Columns.Count; i++)
                {
                    if (dt1.Columns[i].ColumnName == (SQL_.Rows[i]["es_prefix"].ToString() + "||" + SQL_.Rows[i]["es_filed"].ToString() + "||" + SQL_.Rows[i]["es_suffix"].ToString()).ToUpper())
                    {
                        dt1.Columns[i].ColumnName = SQL_.Rows[i]["es_filed1"].ToString();
                        dt1.Columns[i].Caption = SQL_.Rows[i]["es_caption"].ToString();
                    }
                }
                eh.ExportExcel(dt1, dt, ExportFileDialog.SelectedPath, inoutno, SplitType, int.Parse(RowNum.Text), conditionbox);
                string close = MessageBox.Show(this.ParentForm, "导出成功，是否打开文件", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question).ToString();
                if (close.ToString() == "Yes")
                    System.Diagnostics.Process.Start(ExportFileDialog.SelectedPath + "\\" + inoutno + ".xls");
                Close();
            }
        }

        private void ExportExcel_Load(object sender, EventArgs e)
        {
            Activate();
            LogicHandler.CustInit("0");
            StringBuilder sql = new StringBuilder();
            sql.Clear();
            sql.Append("select distinct cep_name,CEP_COLLECTIONTYPE from ProdIODetail left join Product on pd_prodcode = pr_code ");
            sql.Append("left join CHIP_EDCPARAMS_SPEC on pr_orispeccode=ced_model left join  CHIP_EDCPARAMS_SPECDETAIL  on ced_id=cesd_cedid  ");
            sql.Append("left join  CHIP_EDCPARAMS on CESD_CEPID = CEP_ID where pd_inoutno='" + inoutno + "' and ced_status='-1' and CEP_COLLECTIONTYPE='机测'");
            DataTable dt = (DataTable)dh.ExecuteSql(sql.ToString(), "select");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (i <= 4)
                {
                    Controls["M_Param" + (i + 1)].Visible = true;
                    Controls["M_Param" + (i + 1)].Text = dt.Rows[i]["cep_name"].ToString();
                }
            }
            sql.Clear();
            sql.Append("select distinct cep_name,CEP_COLLECTIONTYPE from ProdIODetail left join Product on pd_prodcode = pr_code ");
            sql.Append("left join CHIP_EDCPARAMS_SPEC on pr_orispeccode=ced_model left join  CHIP_EDCPARAMS_SPECDETAIL  on ced_id=cesd_cedid  ");
            sql.Append("left join  CHIP_EDCPARAMS on CESD_CEPID = CEP_ID where pd_inoutno='" + inoutno + "' and ced_status='-1' and CEP_COLLECTIONTYPE='手测'");
            dt = (DataTable)dh.ExecuteSql(sql.ToString(), "select");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (i <= 4)
                {
                    Controls["H_Param" + (i + 1)].Visible = true;
                    Controls["H_Param" + (i + 1)].Text = dt.Rows[i]["cep_name"].ToString();
                }
            }
        }

        private void ExportSetting_Click(object sender, EventArgs e)
        {
            ExportColumns ex = new ExportColumns(inoutno);
            ex.WindowState = FormWindowState.Maximized;
            ex.ShowDialog();
        }
    }
}