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();
}
}
}
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();
}
}
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();
}
}
}
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();
}
}
}