コード例 #1
1
    protected void btnExport_Click(object sender, EventArgs e)
    {
        MSYS.Common.ExcelExport openXMLExcel = null;

        MSYS.DAL.DbOperator opt = new MSYS.DAL.DbOperator();

        string basedir       = System.AppDomain.CurrentDomain.BaseDirectory.ToString();
        string strFolderPath = basedir + @"\TEMP";

        if (!System.IO.Directory.Exists(strFolderPath))
        {
            // 目录不存在,建立目录
            System.IO.Directory.CreateDirectory(strFolderPath);
        }

        DirectoryInfo dyInfo = new DirectoryInfo(strFolderPath);

        //获取文件夹下所有的文件
        foreach (FileInfo feInfo in dyInfo.GetFiles())
        {
            //判断文件日期是否小于今天,是则删除
            if (feInfo.CreationTime < DateTime.Now.AddMinutes(-2))
            {
                feInfo.Delete();
            }
        }
        foreach (DirectoryInfo dir in dyInfo.GetDirectories())
        {
            if (dir.CreationTime < DateTime.Now.AddMinutes(-2))
            {
                dir.Delete(true);
            }
        }
        //导出文件模板所在位置
        string filename   = "原始数据" + System.DateTime.Now.ToString("HHmmss") + ".xls";
        String sourcePath = basedir + @"templates\原始数据.xls";
        String filepath   = basedir + @"TEMP\" + filename;

        try
        {
            //申明一个ExcelSaveAs对象,该对象完成将数据写入Excel的操作
            openXMLExcel = new MSYS.Common.ExcelExport(sourcePath, false);
            MSYS.IHAction ihopt = new MSYS.IHAction();
            //   List<MSYS.IHAction.ParaInfo> paralist = ihopt.GetData(txtBtime.Text, txtEtime.Text, listpara.SelectedValue);
            string prodcode = opt.GetSegValue("select prod_code,starttime from ht_prod_report t where t.starttime <= '" + txtBtime.Text + "' and t.endtime >='" + txtEtime.Text + "' union select prod_code,starttime from ht_prod_report t where t.endtime > '" + txtBtime.Text + "' and t.endtime <'" + txtEtime.Text + "' union select prod_code,starttime from  ht_prod_report t where t.starttime >'" + txtBtime.Text + "' and t.starttime <'" + txtEtime.Text + "' order by starttime", "Prod_code");
            MSYS.IHAction.TimeSeg        seg  = ihopt.GetTimeSegP(txtBtime.Text, txtEtime.Text, listpara.SelectedValue, prodcode);
            List <MSYS.IHAction.ParaRes> Rows = ihopt.GetIHRealDataSet(seg);
            DataTable dt = new DataTable();
            dt.Columns.Add("时间");
            dt.Columns.Add("值");
            dt.Columns.Add("状态");


            foreach (MSYS.IHAction.ParaRes info in Rows)
            {
                var values = new object[3];
                values[0] = info.timestamp;
                values[1] = info.value;
                values[2] = info.status;
                dt.Rows.Add(values);
            }
            openXMLExcel.SetCurrentSheet(0);

            openXMLExcel.WriteData(2, 2, listpara.SelectedItem.Text);
            openXMLExcel.WriteData(3, 2, txtBtime.Text + "~" + txtEtime.Text);
            openXMLExcel.WriteDataIntoWorksheet(5, 1, dt);


            ///客户端再下载该文件,在客户端进行浏览
            FileInfo fi = new FileInfo(filepath);
            if (fi.Exists)     //判断文件是否已经存在,如果存在就删除!
            {
                fi.Delete();
            }
            openXMLExcel.SaveAs(filepath);

            openXMLExcel.Dispose();
            openXMLExcel = null;
            KillProcess("EXCEL.EXE");

            Response.Clear();
            Response.Buffer = true;

            Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8));
            Response.ContentType = "application/vnd.ms-excel";
            Response.TransmitFile(filepath);

            //  Response.End();
            HttpContext.Current.ApplicationInstance.CompleteRequest();
        }
        catch (Exception ee)
        {
            if (openXMLExcel != null)
            {
                openXMLExcel.Dispose();
            }
        }
    }
コード例 #2
0
    protected void bindGrid3()
    {
        MSYS.IHAction                ihopt = new MSYS.IHAction();
        MSYS.IHAction.TimeSeg        seg   = ihopt.GetTimeSegP(lbsTime.Text, lbeTime.Text, lbpara_code.Text, lbprod_code.Text);
        List <MSYS.IHAction.ParaRes> Rows  = ihopt.GetIHRealDataSet(seg);

        if (Rows == null)
        {
            ScriptManager.RegisterStartupScript(UpdatePanel1, this.Page.GetType(), "msg", "alert('无数据,请查看数据库状态是否正常!’)", true);
        }
        else
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("时间");
            dt.Columns.Add("值");
            dt.Columns.Add("状态");
            if (rdUnQualified.Checked == true)
            {
                string query            = "select r.upper_limit,r.lower_limit from ht_tech_stdd_code_detail r left join ht_pub_prod_design t on t.tech_stdd_code = r.tech_code where r.para_code = '" + lbpara_code.Text + "' and t.prod_code = '" + lbprod_code.Text + "'";
                MSYS.DAL.DbOperator opt = new MSYS.DAL.DbOperator();
                double upper            = Convert.ToDouble(opt.GetSegValue(query, "upper_limit"));
                double lower            = Convert.ToDouble(opt.GetSegValue(query, "lower_limit"));
                Rows = Rows.FindAll(s => s.status == "过程值" && (s.value <lower || s.value> upper));
            }

            foreach (MSYS.IHAction.ParaRes info in Rows)
            {
                var values = new object[3];
                values[0] = info.timestamp;
                values[1] = info.value;
                values[2] = info.status;
                dt.Rows.Add(values);
            }
            GridView3.DataSource = dt;
            GridView3.DataBind();
        }
    }
コード例 #3
0
    protected void btnExport_Click(object sender, EventArgs e)
    {
        MSYS.IHAction                ihopt = new MSYS.IHAction();
        MSYS.IHAction.TimeSeg        seg   = ihopt.GetTimeSegP(lbsTime.Text, lbeTime.Text, lbpara_code.Text, lbprod_code.Text);
        List <MSYS.IHAction.ParaRes> Rows  = ihopt.GetIHRealDataSet(seg);

        if (Rows == null)
        {
            return;
        }
        DataTable dt = new DataTable();

        dt.Columns.Add("时间");
        dt.Columns.Add("值");
        dt.Columns.Add("状态");
        if (rdUnQualified.Checked == true)
        {
            string query            = "select r.upper_limit,r.lower_limit from ht_tech_stdd_code_detail r left join ht_pub_prod_design t on t.tech_stdd_code = r.tech_code where r.para_code = '" + lbpara_code.Text + "' and t.prod_code = '" + lbprod_code.Text + "'";
            MSYS.DAL.DbOperator opt = new MSYS.DAL.DbOperator();
            double upper            = Convert.ToDouble(opt.GetSegValue(query, "upper_limit"));
            double lower            = Convert.ToDouble(opt.GetSegValue(query, "lower_limit"));
            Rows = Rows.FindAll(s => s.status == "过程值" && (s.value <lower || s.value> upper));
        }

        foreach (MSYS.IHAction.ParaRes info in Rows)
        {
            var values = new object[3];
            values[0] = info.timestamp;
            values[1] = info.value;
            values[2] = info.status;
            dt.Rows.Add(values);
        }
        MSYS.Common.ExcelExport openXMLExcel = null;
        try
        {
            string basedir       = System.AppDomain.CurrentDomain.BaseDirectory.ToString();
            string strFolderPath = basedir + @"\TEMP";
            if (!System.IO.Directory.Exists(strFolderPath))
            {
                // 目录不存在,建立目录
                System.IO.Directory.CreateDirectory(strFolderPath);
            }

            DirectoryInfo dyInfo = new DirectoryInfo(strFolderPath);
            //获取文件夹下所有的文件
            foreach (FileInfo feInfo in dyInfo.GetFiles())
            {
                //判断文件日期是否小于今天,是则删除
                if (feInfo.CreationTime < DateTime.Now.AddMinutes(-2))
                {
                    feInfo.Delete();
                }
            }
            foreach (DirectoryInfo dir in dyInfo.GetDirectories())
            {
                if (dir.CreationTime < DateTime.Now.AddMinutes(-2))
                {
                    dir.Delete(true);
                }
            }
            //导出文件模板所在位置
            string filename   = "原始数据" + System.DateTime.Now.ToString("HHmmss") + ".xls";
            String sourcePath = basedir + @"templates\原始数据.xls";
            String filepath   = basedir + @"TEMP\" + filename;
            openXMLExcel = new MSYS.Common.ExcelExport(sourcePath, false);
            openXMLExcel.SetCurrentSheet(0);

            openXMLExcel.WriteData(2, 2, lbPoint.Text);
            openXMLExcel.WriteData(3, 2, lbsTime.Text + "~" + lbeTime.Text);
            openXMLExcel.WriteDataIntoWorksheet(5, 1, dt);


            ///客户端再下载该文件,在客户端进行浏览
            FileInfo fi = new FileInfo(filepath);
            if (fi.Exists)     //判断文件是否已经存在,如果存在就删除!
            {
                fi.Delete();
            }
            openXMLExcel.SaveAs(filepath);

            openXMLExcel.Dispose();
            openXMLExcel = null;
            KillProcess("EXCEL.EXE");

            Response.Clear();
            Response.Buffer = true;

            Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8));
            Response.ContentType = "application/vnd.ms-excel";
            Response.TransmitFile(filepath);

            //  Response.End();
            HttpContext.Current.ApplicationInstance.CompleteRequest();
        }
        catch (Exception ee)
        {
            if (openXMLExcel != null)
            {
                openXMLExcel.Dispose();
            }
        }
    }
コード例 #4
0
    protected void btngrid1View_Click(object sender, EventArgs e)
    {
        Button      btn   = (Button)sender;
        GridViewRow row   = (GridViewRow)btn.NamingContainer;
        int         index = row.RowIndex;

        string prodcode  = GridView1.DataKeys[index].Values[0].ToString();
        string para_code = GridView1.DataKeys[index].Values[1].ToString();
        string starttime = GridView1.DataKeys[index].Values[2].ToString();
        string stoptime  = GridView1.DataKeys[index].Values[3].ToString();

        MSYS.IHAction       ihopt = new MSYS.IHAction();
        MSYS.DAL.DbOperator opt   = new MSYS.DAL.DbOperator();
        //   List<MSYS.IHAction.ParaInfo> paralist = ihopt.GetData(txtBtime.Text, txtEtime.Text, listpara.SelectedValue);
        DataTable dt = new DataTable();

        dt.Columns.Add("时间");
        dt.Columns.Add("值");
        dt.Columns.Add("状态");
        if (rdAll.Checked && listTeam.SelectedValue != "")
        {
            string  query = "select distinct g.prod_code,g.para_code, h.prod_name as 产品,j.para_name as 工艺点,k.team_name as 班组,g.b_time as 开始时间,g.e_time as 结束时间,round(g.avg,2) as 均值,g.count as 采样点数,round(g.min,2) as 最小值,round(g.max,2) as 最大值,round(g.quarate,2) as 合格率,round(g.uprate,2) as 超上限率,round(g.downrate,2) as 超下限率,round(g.stddev,2) as 标准差,round(g.absdev,2) as 绝对差,round(g.range,2) as 范围,round(g.cpk,2) as cpk  from (select distinct t.prod_code, t.para_code, t.team, t.avg, t.count, t.min, t.max, t.quarate, t.uprate, t.downrate, t.stddev, t.absdev, t.range, t.cpk, t.b_time, t.e_time from ht_qlt_data_record t where t.b_time between '" + starttime + "' and '" + stoptime + "'  and t.team = '" + listTeam.SelectedValue + "'  and t.prod_code = '" + prodcode + "'  and t.para_code = '" + para_code + "') g left join ht_pub_prod_design h on h.prod_code = g.prod_code left join ht_pub_tech_para j on j.para_code = g.para_code left join ht_sys_team k on k.team_code = g.team order by h.prod_name,j.para_name,g.e_time,k.team_name,g.count";
            DataSet data  = opt.CreateDataSetOra(query);
            if (data != null && data.Tables[0].Rows.Count > 1)
            {
                foreach (DataRow srow in data.Tables[0].Rows)
                {
                    starttime = srow["开始时间"].ToString();
                    stoptime  = srow["结束时间"].ToString();
                    MSYS.IHAction.TimeSeg        seg  = ihopt.GetTimeSegP(starttime, stoptime, para_code, prodcode);
                    List <MSYS.IHAction.ParaRes> Rows = ihopt.GetIHRealDataSet(seg);

                    foreach (MSYS.IHAction.ParaRes info in Rows)
                    {
                        var values = new object[3];
                        values[0] = info.timestamp;
                        values[1] = info.value;
                        values[2] = info.status;
                        dt.Rows.Add(values);
                    }
                }
            }
        }
        else
        {
            MSYS.IHAction.TimeSeg        seg  = ihopt.GetTimeSegP(starttime, stoptime, para_code, prodcode);
            List <MSYS.IHAction.ParaRes> Rows = ihopt.GetIHRealDataSet(seg);

            foreach (MSYS.IHAction.ParaRes info in Rows)
            {
                var values = new object[3];
                values[0] = info.timestamp;
                values[1] = info.value;
                values[2] = info.status;
                dt.Rows.Add(values);
            }
        }
        MSYS.Common.ExcelExport openXMLExcel = null;



        string basedir       = System.AppDomain.CurrentDomain.BaseDirectory.ToString();
        string strFolderPath = basedir + @"\TEMP";

        if (!System.IO.Directory.Exists(strFolderPath))
        {
            // 目录不存在,建立目录
            System.IO.Directory.CreateDirectory(strFolderPath);
        }

        DirectoryInfo dyInfo = new DirectoryInfo(strFolderPath);

        //获取文件夹下所有的文件
        foreach (FileInfo feInfo in dyInfo.GetFiles())
        {
            //判断文件日期是否小于今天,是则删除
            if (feInfo.CreationTime < DateTime.Now.AddMinutes(-2))
            {
                feInfo.Delete();
            }
        }
        foreach (DirectoryInfo dir in dyInfo.GetDirectories())
        {
            if (dir.CreationTime < DateTime.Now.AddMinutes(-2))
            {
                dir.Delete(true);
            }
        }
        //导出文件模板所在位置
        string filename   = "在线质量统计" + System.DateTime.Now.ToString("HHmmss") + ".xls";
        String sourcePath = basedir + @"templates\在线质量统计.xls";
        String filepath   = basedir + @"TEMP\" + filename;

        try
        {
            //申明一个ExcelSaveAs对象,该对象完成将数据写入Excel的操作
            openXMLExcel = new MSYS.Common.ExcelExport(sourcePath, false);



            openXMLExcel.SetCurrentSheet(0);

            openXMLExcel.WriteData(2, 2, row.Cells[1].Text);
            openXMLExcel.WriteData(2, 6, row.Cells[0].Text);
            openXMLExcel.WriteData(3, 2, row.Cells[3].Text);
            openXMLExcel.WriteData(3, 6, row.Cells[4].Text);
            openXMLExcel.WriteData(4, 2, row.Cells[2].Text);
            openXMLExcel.WriteData(4, 6, row.Cells[6].Text);
            openXMLExcel.WriteData(6, 1, row.Cells[5].Text);
            openXMLExcel.WriteData(6, 2, row.Cells[7].Text);
            openXMLExcel.WriteData(6, 3, row.Cells[8].Text);
            openXMLExcel.WriteData(6, 4, row.Cells[9].Text);
            openXMLExcel.WriteData(6, 5, row.Cells[10].Text);
            openXMLExcel.WriteData(6, 6, row.Cells[11].Text);
            openXMLExcel.WriteData(6, 7, row.Cells[12].Text);
            openXMLExcel.WriteData(6, 8, row.Cells[13].Text);
            openXMLExcel.WriteData(6, 9, row.Cells[14].Text);
            openXMLExcel.WriteData(6, 10, row.Cells[15].Text);
            openXMLExcel.WriteDataIntoWorksheet(9, 1, dt);


            ///客户端再下载该文件,在客户端进行浏览
            FileInfo fi = new FileInfo(filepath);
            if (fi.Exists)     //判断文件是否已经存在,如果存在就删除!
            {
                fi.Delete();
            }
            openXMLExcel.SaveAs(filepath);

            openXMLExcel.Dispose();
            openXMLExcel = null;
            KillProcess("EXCEL.EXE");

            Response.Clear();
            Response.Buffer = true;

            Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8));
            Response.ContentType = "application/vnd.ms-excel";
            Response.TransmitFile(filepath);

            //  Response.End();
            HttpContext.Current.ApplicationInstance.CompleteRequest();
        }
        catch (Exception ee)
        {
            if (openXMLExcel != null)
            {
                openXMLExcel.Dispose();
            }
        }
    }