<%@ WebHandler Language="C#" Class="MESCommonBaseAMS" %> using System; using System.Collections; using Aspose.Cells; using System.Web; using System.Text; using System.Linq; using System.Collections.Generic; using System.IO; using System.Web.Script.Serialization; using System.Data; //using BizDataAccess; using System.Data.SqlClient;//uploadInvoiceScan.ashx //using DbCallData; using BasicData; //using LitJson; using DataLinkMesWork; using System.Configuration; using System.Text.RegularExpressions; #region MESCommonBaseAMS public class MESCommonBaseAMS : IHttpHandler { public void ProcessRequest(HttpContext context) { context.Response.ContentType = "application/json"; var data = context.Request; var stream = new StreamReader(data.InputStream).ReadToEnd(); string InvoiceNum; //JsonData jsonData; string responseText = "NULL"; byte[] bytes = null; string fileName = "test.xsl"; try { JsonData jsonData=null; try { jsonData = JsonMapper.ToObject(stream); } catch(Exception err) { } int type=0; try { if(jsonData!=null) { if(jsonData.ContainsKey("Type")) { type = Int32.Parse( jsonData["Type"].ToString()); }else if(jsonData.ContainsKey("type")) { type = Int32.Parse( jsonData["type"].ToString()); } } } catch { } jsonobj dataobj=null; try { dataobj = new JavaScriptSerializer().Deserialize(stream); if(dataobj!=null) { type = Int32.Parse(dataobj.Type); } } catch(Exception err) { dataobj = null; } if(dataobj==null) { try { InvoiceNum = HttpContext.Current.Request["param"]; if(InvoiceNum!=null) { jsonData = JsonMapper.ToObject(InvoiceNum); if(jsonData.ContainsKey("Type")) { type = Int32.Parse( jsonData["Type"].ToString()); }else if(jsonData.ContainsKey("type")) { type = Int32.Parse( jsonData["type"].ToString()); } } } catch { jsonData = null; } } string fileExtension=""; HttpFileCollection files; string suffix; string name; switch(type) { //大众项目 case 21: string name1; List sqlCallParameterList; GetSqlCallParameter(jsonData, out name1, out sqlCallParameterList); ExePROCEDURE_Type21(context, name1, sqlCallParameterList); break; case 2001: //DataLink.LogJsonData(jsonData); MESDownloadExcel.ExcelWebCall.ExcelFile(jsonData,out bytes,out fileName,ref fileExtension); fileName = fileName + "."+fileExtension; HttpContext.Current.Response.ContentType = "application/octet-stream"; //HttpContext.Current.Response.ContentType = getContentType(extension); //通知浏览器下载文件而不是打开 HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)); HttpContext.Current.Response.AddHeader("Access-Control-Expose-Headers", "Content-Disposition"); HttpContext.Current.Response.BinaryWrite(bytes); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); break; case 2002: //DataLink.LogJsonData(jsonData); MESDownloadExcel.ExcelWebCall.ExcelFilePdf(jsonData,out bytes,out fileName,out fileExtension); fileName = fileName + "."+fileExtension; HttpContext.Current.Response.ContentType = "application/octet-stream"; //HttpContext.Current.Response.ContentType = getContentType(extension); //通知浏览器下载文件而不是打开 HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)); HttpContext.Current.Response.AddHeader("Access-Control-Expose-Headers", "Content-Disposition"); HttpContext.Current.Response.BinaryWrite(bytes); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); break; case 2003:// 合成excel图片，下载 string[] dataimg; if(context.Request.Form.Keys.Count>0) { dataimg = data.Form.GetValues(0); MESDownloadExcel.ExcelWebCall.ExcelFile(jsonData,out bytes,out fileName,ref dataimg[0]); if (bytes == null) return; fileName = fileName + "."+dataimg[0]; HttpContext.Current.Response.ContentType = "application/octet-stream"; //HttpContext.Current.Response.ContentType = getContentType(extension); //通知浏览器下载文件而不是打开 HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)); HttpContext.Current.Response.AddHeader("Access-Control-Expose-Headers", "Content-Disposition"); HttpContext.Current.Response.BinaryWrite(bytes); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); //if(DataLinkMesWork.DataLink.isBakup=="1") //{ // DataLinkMesWork.DataLink.ExePROCEDURE_Type16_Bakup(jsonData, out bytes, out fileName, out suffix); //} } break; case 2004: //DataLink.LogJsonData(jsonData); DataLinkMesWork.DataLink.ExePROCEDURE_Type2004(jsonData, out bytes, out fileName, out fileExtension); //MESDownloadExcel.ExcelWebCall.ExcelFile(jsonData,out bytes,out fileName,ref fileExtension); fileName = fileName + "."+fileExtension; HttpContext.Current.Response.ContentType = "application/octet-stream"; //HttpContext.Current.Response.ContentType = getContentType(extension); //通知浏览器下载文件而不是打开 HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)); HttpContext.Current.Response.AddHeader("Access-Control-Expose-Headers", "Content-Disposition"); HttpContext.Current.Response.BinaryWrite(bytes); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); break; case 15://上传文件 files = context.Request.Files; if (files.Count > 0) { fileName = files[0].FileName; bytes = new byte[files[0].InputStream.Length]; files[0].InputStream.Read(bytes, 0, bytes.Length); suffix = fileName.Substring(fileName.LastIndexOf(".")+1); name = fileName.Substring(0,fileName.LastIndexOf(".")); responseText = DataLinkMesWork.DataLink.ExePROCEDURE_Type15(jsonData, name, suffix, bytes); context.Response.Write(responseText); //if(DataLinkMesWork.DataLink.isBakup=="1") //{ // DataLinkMesWork.DataLink.ExePROCEDURE_Type15_Bakup(jsonData, name, suffix, bytes); //} } else { fileName = ""; bytes = new byte[1]; suffix = ""; name = ""; responseText = DataLinkMesWork.DataLink.ExePROCEDURE_Type15(jsonData, name, suffix, bytes); context.Response.Write(responseText); //if(DataLinkMesWork.DataLink.isBakup=="1") //{ // DataLinkMesWork.DataLink.ExePROCEDURE_Type15_Bakup(jsonData, name, suffix, bytes); //} } break; case 16://上传文件 DataLink.LogJsonData(jsonData); DataLinkMesWork.DataLink.ExePROCEDURE_Type16(jsonData, out bytes, out fileName, out suffix); if (bytes == null) return; fileName = fileName + "."+suffix; HttpContext.Current.Response.ContentType = "application/octet-stream"; //HttpContext.Current.Response.ContentType = getContentType(extension); //通知浏览器下载文件而不是打开 HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)); HttpContext.Current.Response.AddHeader("Access-Control-Expose-Headers", "Content-Disposition"); HttpContext.Current.Response.BinaryWrite(bytes); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); //if(DataLinkMesWork.DataLink.isBakup=="1") //{ // DataLinkMesWork.DataLink.ExePROCEDURE_Type16_Bakup(jsonData, out bytes, out fileName, out suffix); //} break; default: //DataLink.LogJsonData(jsonData); responseText = DataLinkMesWork.DataLink.SqlWebCall(type,jsonData,dataobj);//.SqlWebCall(stream); context.Response.Write(responseText); //if(DataLinkMesWork.DataLink.isBakup=="1") //{ // DataLinkMesWork.DataLink.SqlWebCall_Bakup(type,jsonData,dataobj); //} break; } } catch(Exception err) { context.Response.Write(responseText); } } public bool IsReusable { get { return false; } } private void ExePROCEDURE_Type21(HttpContext context ,string nameFunction,List sqlCallParameterList) { switch (nameFunction) { //综合查询 case "btnSearch": btnSearch_Click(context,sqlCallParameterList); break; //异常查询 case "btnYiChang": btnYiChang_Click(context,sqlCallParameterList); break; //发动机详细信息 case "CreatHtml": CreatHtml_Click(context,sqlCallParameterList); break; //导出OP case "btnDaoChuOP": btnDaoChuOP_Click(context,sqlCallParameterList); break; //导出单台 case "btnDaoChuOne": btnDaoChuOne_Click(context,sqlCallParameterList); break; //导出全部 case "btnDaoChuAll": btnDaoChuAll_Click(context,sqlCallParameterList); break; } } public static void GetSqlCallParameter(JsonData json,out string name1,out List sqlCallParameterList) { SqlCallParameter thisParms = null; JsonData jsonData; JsonData jsonDataName;//存储过程名称 Sql JsonData jsonDataParam;//存储过程参数 sqlCallParameterList = new List(); name1 = ""; try { { string type1; string param1; string userID1; string pagination1; bool hasReturn1; string modularID1; GetString_JsonData(json, out type1, out name1, out param1, out userID1, out pagination1, out hasReturn1, out modularID1); jsonDataName = name1;//存储过程名称 jsonDataParam = param1; //存储过程参数 json格式 [{\"name\":\"name1\",\"value\":\"value1\"},{\"name\":\"name2\",\"value\":\"value1\"}] jsonData = JsonMapper.ToObject(jsonDataParam.ToString()); //转为JSON格式 if (jsonDataParam.ToString() == "" || jsonDataParam.ToString() == null)//执行没有参数的存储过程 { // isOK = SQLCommon.ExecuteInsertMesWork(jsonDataName.ToString(), ref param, connStringBakup, out err); } else//执行有参数的存储过程 { //thisParms = new []SqlParameter(); //thisParms = new SqlParameter[jsonData.Count]; for (int i = 0; i < jsonData.Count; i++) { string name = jsonData[i]["name"].ToString();// 参数名 string value = null; try { //判断是否为数组，若为数组转为字符串格式 if (jsonData[i]["value"].IsArray == true) { //数组转字符串 value = jsonData[i]["value"].ToJson().ToString().Replace("[", "").Replace("]", "").Replace("\"", "");// 数组参数值转字符串 // unicode解码 value = DeUnicode(value); } else//不是数组格式直接转字符串 { value = jsonData[i]["value"].ToString();// 参数值 } } catch (Exception E)//value为null时。Tostring()会报错，需要手动赋值 { value = null; } string output = null;// 参数是否为输出类型 string type = null;// 参数类型 { thisParms = new SqlCallParameter(name, value); if (jsonData[i].ContainsKey("output")) { if(jsonData[i]["output"]!=null) { output = jsonData[i]["output"].ToString();// 参数是否为输出类型 } } if (jsonData[i].ContainsKey("type")) { if(jsonData[i]["type"] != null) { type = jsonData[i]["type"].ToString();// 参数类型 object inputValue = null; ; inputValue = GetInputValue(type, output); thisParms = new SqlCallParameter(name, inputValue); } } sqlCallParameterList.Add(thisParms); } } } } } catch (Exception e) { } } /// /// 返回输出参数类型 /// /// /// /// public static object GetInputValue(object type, object output) { object inputValue = null; switch (type.ToString()) { case "int": inputValue = Convert.ToInt32(output); break; case "string": inputValue = output; break; case "boolean": case "bool": if (output.ToString() == "1" || output.ToString() == "true") { inputValue = true; } else { inputValue = false; } break; case "datetime": inputValue = Convert.ToDateTime(output); break; default: inputValue = output.ToString(); break; } return inputValue; } /// /// Unicode解码 /// /// /// public static string DeUnicode(string str) { //最直接的方法Regex.Unescape(str); Regex reg = new Regex(@"(?i)\\[uU]([0-9a-f]{4})"); return reg.Replace(str, delegate (Match m) { return ((char)Convert.ToInt32(m.Groups[1].Value, 16)).ToString(); }); } /// /// "UserID": $("#UserID").val(), /// "ModularID": $("#ModularID").val(), 子组件 /// "Type": $("#TYPE").val(), /// "Name": $("#NAME").val(), /// "Param": $("#PARAM1value").val(), /// "Pagination": $("#Pagination").val(), /// HasReturn: $("#HASRETURN").get(0).checked /// /// /// /// /// /// /// /// /// 子组件 public static void GetString_JsonData(JsonData json, out string type,out string name,out string param,out string userID,out string pagination,out bool hasReturn,out string modularID) { type = null; name = null; param = ""; userID = null; pagination=null; hasReturn=false; modularID = ""; if (json == null) return; if (json.Keys.Contains("Type")) { if (json["Type"] != null) type = json["Type"].ToString(); } else if (json.Keys.Contains("type")) { if (json["type"] != null) type = json["type"].ToString(); } if (json.Keys.Contains("Name")) { if (json["Name"] != null) name = json["Name"].ToString(); } else if (json.Keys.Contains("name")) { if (json["name"] != null) name = json["name"].ToString(); } if (json.Keys.Contains("Param")) { if (json["Param"] != null) param = json["Param"].ToString(); } else if (json.Keys.Contains("param")) { if (json["param"] != null) param = json["param"].ToString(); } if (json.Keys.Contains("UserID")) { if (json["UserID"] != null) userID = json["UserID"].ToString(); } else if (json.Keys.Contains("userID")) { if (json["userID"] != null) userID = json["userID"].ToString(); } if (json.Keys.Contains("Pagination")) { if (json["Pagination"] != null) pagination = json["Pagination"].ToString(); } else if (json.Keys.Contains("pagination")) { if (json["pagination"] != null) pagination = json["pagination"].ToString(); } if (json.Keys.Contains("HasReturn")) { try { if (json["HasReturn"] != null) hasReturn = Convert.ToBoolean(json["HasReturn"].ToString()); } catch { } } else if (json.Keys.Contains("hasReturn")) { try { if (json["hasReturn"] != null) hasReturn = Convert.ToBoolean(json["hasReturn"].ToString()); } catch { } } if (json.Keys.Contains("ModularID")) { try { if (json["ModularID"] != null) modularID = json["ModularID"].ToString(); } catch { } } else if (json.Keys.Contains("modularID")) { try { if (json["modularID"] != null) modularID = json["modularID"].ToString(); } catch { } } //ModularID } /// /// 查询 /// /// private void btnSearch_Click(HttpContext context,List sqlCallParameterList) { string szMsgBegin = "2021-11-02 00:00:00"; string szMsgEnd = "2021-12-31 11:07:52"; string m_sSortno = ""; string m_sEngineNo = ""; string m_sEngineNo1 = ""; string m_sEngineNo2 = ""; bool rbOK_Checked = false; bool rbFanXiu_Checked = false; bool rbNoOK_Checked = false; string szNote = "ZKG"; string szComponent = ""; int PageCurrent = 1; int PageSize = 10; int PageCount = 1; int ItemCount = 1; for (int i = 0; i < sqlCallParameterList.Count; i++) { switch (i) { case 0: szMsgBegin = sqlCallParameterList[i].value.ToString(); break; case 1: szMsgEnd = sqlCallParameterList[i].value.ToString(); break; case 2: m_sSortno = sqlCallParameterList[i].value.ToString(); break; case 3: m_sEngineNo = sqlCallParameterList[i].value.ToString(); break; case 4: m_sEngineNo1 = sqlCallParameterList[i].value.ToString(); break; case 5: m_sEngineNo2 = sqlCallParameterList[i].value.ToString(); break; case 6: rbOK_Checked = Convert.ToBoolean(sqlCallParameterList[i].value); break; case 7: rbFanXiu_Checked = Convert.ToBoolean(sqlCallParameterList[i].value); break; case 8: rbNoOK_Checked = Convert.ToBoolean(sqlCallParameterList[i].value); break; case 9: szNote = sqlCallParameterList[i].value.ToString(); break; case 10: szComponent = sqlCallParameterList[i].value.ToString(); break; case 11: PageCurrent = Convert.ToInt32(sqlCallParameterList[i].value); break; case 12: PageSize = Convert.ToInt32(sqlCallParameterList[i].value); break; case 13: PageCount = Convert.ToInt32(sqlCallParameterList[i].value); break; case 14: ItemCount = Convert.ToInt32(sqlCallParameterList[i].value); break; } } string dt = new Form1().btnSearch_Click( szMsgBegin, szMsgEnd, m_sSortno,m_sEngineNo,m_sEngineNo1, m_sEngineNo2,rbOK_Checked,rbFanXiu_Checked,rbNoOK_Checked,szNote,szComponent,PageCurrent,PageSize,PageCount,ItemCount); //string result = DataLinkMesWork.DataLink.DataTableToJson(dt); context.Response.Write(dt); } /// /// 查询 导出 /// /// private void btnSearch_DaoChu_Click(HttpContext context,List sqlCallParameterList) { string szMsgBegin = "2021-11-02 00:00:00"; string szMsgEnd = "2021-12-31 11:07:52"; string m_sSortno = ""; string m_sEngineNo = ""; string m_sEngineNo1 = ""; string m_sEngineNo2 = ""; bool rbOK_Checked = false; bool rbFanXiu_Checked = false; bool rbNoOK_Checked = false; string szNote = "ZKG"; string szComponent = ""; int PageCurrent = 1; int PageSize = 10; int PageCount = 1; int ItemCount = 1; for (int i = 0; i < sqlCallParameterList.Count; i++) { switch (i) { case 0: szMsgBegin = sqlCallParameterList[i].value.ToString(); break; case 1: szMsgEnd = sqlCallParameterList[i].value.ToString(); break; case 2: m_sSortno = sqlCallParameterList[i].value.ToString(); break; case 3: m_sEngineNo = sqlCallParameterList[i].value.ToString(); break; case 4: m_sEngineNo1 = sqlCallParameterList[i].value.ToString(); break; case 5: m_sEngineNo2 = sqlCallParameterList[i].value.ToString(); break; case 6: rbOK_Checked = Convert.ToBoolean(sqlCallParameterList[i].value); break; case 7: rbFanXiu_Checked = Convert.ToBoolean(sqlCallParameterList[i].value); break; case 8: rbNoOK_Checked = Convert.ToBoolean(sqlCallParameterList[i].value); break; case 9: szNote = sqlCallParameterList[i].value.ToString(); break; case 10: szComponent = sqlCallParameterList[i].value.ToString(); break; case 11: PageCurrent = Convert.ToInt32(sqlCallParameterList[i].value); break; case 12: PageSize = Convert.ToInt32(sqlCallParameterList[i].value); break; case 13: PageCount = Convert.ToInt32(sqlCallParameterList[i].value); break; case 14: ItemCount = Convert.ToInt32(sqlCallParameterList[i].value); break; } } DataTable dt = new Form1().btnSearchDaoChu_Click( szMsgBegin, szMsgEnd, m_sSortno,m_sEngineNo,m_sEngineNo1, m_sEngineNo2,rbOK_Checked,rbFanXiu_Checked,rbNoOK_Checked,szNote,szComponent); string result = DataLinkMesWork.DataLink.DataTableToJson(dt); context.Response.Write(result); } /// /// 异常查询 /// /// private void btnYiChang_Click(HttpContext context,List sqlCallParameterList) { string szMsgBegin = "2021-11-02 00:00:00"; string szMsgEnd = "2021-12-31 11:07:52"; string m_sSortno = ""; string m_sEngineNo = ""; string m_sEngineNo1 = ""; string m_sEngineNo2 = ""; for(int i = 0; i < sqlCallParameterList.Count; i++) { switch (i) { case 0: szMsgBegin = sqlCallParameterList[i].value.ToString(); break; case 1: szMsgEnd = sqlCallParameterList[i].value.ToString(); break; case 2: m_sSortno = sqlCallParameterList[i].value.ToString(); break; case 3: m_sEngineNo = sqlCallParameterList[i].value.ToString(); break; case 4: m_sEngineNo1 = sqlCallParameterList[i].value.ToString(); break; case 5: m_sEngineNo2 = sqlCallParameterList[i].value.ToString(); break; } } DataTable dt = new Form1().btnYiChang_Click( szMsgBegin, szMsgEnd, m_sSortno,m_sEngineNo,m_sEngineNo1, m_sEngineNo2); string result = DataLinkMesWork.DataLink.DataTableToJson(dt); context.Response.Write(result); } /// /// 导出全部 /// private void btnDaoChuAll_Click(HttpContext context,List sqlCallParameterList) { string szMsgBegin = "2021-11-02 00:00:00"; string szMsgEnd = "2021-12-31 11:07:52"; string m_sSortno = ""; string m_sEngineNo = ""; string m_sEngineNo1 = ""; string m_sEngineNo2 = ""; bool rbOK_Checked = false; bool rbFanXiu_Checked = false; bool rbNoOK_Checked = false; string szNote = "ZKG"; string szComponent = ""; int PageCurrent = 25; int PageSize = 1000; int PageCount = 1; int ItemCount = 1; for(int i = 0; i < sqlCallParameterList.Count; i++) { switch (i) { case 0: szMsgBegin = sqlCallParameterList[i].value.ToString(); break; case 1: szMsgEnd = sqlCallParameterList[i].value.ToString(); break; case 2: m_sSortno = sqlCallParameterList[i].value.ToString(); break; case 3: m_sEngineNo = sqlCallParameterList[i].value.ToString(); break; case 4: m_sEngineNo1 = sqlCallParameterList[i].value.ToString(); break; case 5: m_sEngineNo2 = sqlCallParameterList[i].value.ToString(); break; case 6: rbOK_Checked = Convert.ToBoolean(sqlCallParameterList[i].value); break; case 7: rbFanXiu_Checked = Convert.ToBoolean(sqlCallParameterList[i].value); break; case 8: rbNoOK_Checked = Convert.ToBoolean(sqlCallParameterList[i].value); break; case 9: szNote = sqlCallParameterList[i].value.ToString(); break; case 10: szComponent = sqlCallParameterList[i].value.ToString(); break; } } MemoryStream ms; ms = new Form1().btnDaoChuAll_Click(szMsgBegin, szMsgEnd, m_sSortno,m_sEngineNo,m_sEngineNo1, m_sEngineNo2,rbOK_Checked,rbFanXiu_Checked,rbNoOK_Checked,szNote,szComponent,PageCurrent,PageSize , PageCount,ItemCount); //MESDownloadExcel.ExcelWebCall.ExcelFile(jsonData,out bytes,out fileName,ref fileExtension); //fileName = fileName + "."+fileExtension; string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; DownLoadFile(fileName, ms); // MemoryStream CreatHtml(string strEngNo,string strBeginTime,string strEndTime,string strEngine_Index) } /// /// 发动机详细信息 /// private void CreatHtml_Click(HttpContext context,List sqlCallParameterList) { string szMsgBegin="2021-12-18 22:26:37"; string szMsgEnd="2021-12-19 00:28:58"; string m_sEngineNo = "DMBNK0725"; string strEngine_Index="2160615"; for(int i = 0; i < sqlCallParameterList.Count; i++) { switch (i) { case 0: szMsgBegin = sqlCallParameterList[i].value.ToString(); break; case 1: szMsgEnd = sqlCallParameterList[i].value.ToString(); break; case 2: m_sEngineNo = sqlCallParameterList[i].value.ToString(); break; case 3: strEngine_Index = sqlCallParameterList[i].value.ToString(); break; } } MemoryStream ms; ms = new Form1().CreatHtml( szMsgBegin, szMsgEnd,m_sEngineNo, strEngine_Index); string fileName = m_sEngineNo+"_"+DateTime.Now.ToString("yyyyMMddHHmmss") + ".html"; DownLoadFile(fileName, ms); } /// /// 导出单台 /// private void btnDaoChuOne_Click(HttpContext context,List sqlCallParameterList) { //发动机编号 string m_sEngineNo; //生产时间 string szMsgBegin; //完成时间 string szMsgEnd; string strEngine_Index; //发动机类型号 string strSortNo; m_sEngineNo = "DMBNK0725"; szMsgBegin="2021-12-18 22:26:37"; szMsgEnd="2021-12-19 00:28:58"; strEngine_Index="2160615"; strSortNo="5502"; for(int i = 0; i < sqlCallParameterList.Count; i++) { switch (i) { case 0: szMsgBegin = sqlCallParameterList[i].value.ToString(); break; case 1: szMsgEnd = sqlCallParameterList[i].value.ToString(); break; case 2: m_sEngineNo = sqlCallParameterList[i].value.ToString(); break; case 3: strEngine_Index = sqlCallParameterList[i].value.ToString(); break; } } MemoryStream ms; ms = new Form1().btnDaoChuOne_Click( szMsgBegin, szMsgEnd, m_sEngineNo,strEngine_Index, strSortNo); if (ms == null) return; //MESDownloadExcel.ExcelWebCall.ExcelFile(jsonData,out bytes,out fileName,ref fileExtension); //fileName = fileName + "."+fileExtension; string fileName = m_sEngineNo+"_"+DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; DownLoadFile(fileName, ms); } /// /// 导出OP /// private void btnDaoChuOP_Click(HttpContext context,List sqlCallParameterList) { string strOP; string szMsgBegin; string szMsgEnd; string strSortNo; strOP = "393"; szMsgBegin = "2021-11-01 00:00:00"; szMsgEnd = "2022-01-31 14:16:35"; strSortNo = ""; for(int i = 0; i < sqlCallParameterList.Count; i++) { switch (i) { case 0: szMsgBegin = sqlCallParameterList[i].value.ToString(); break; case 1: szMsgEnd = sqlCallParameterList[i].value.ToString(); break; case 2: strOP = sqlCallParameterList[i].value.ToString(); break; case 3: strSortNo = sqlCallParameterList[i].value.ToString(); break; } } MemoryStream ms; ms = new Form1().btnDaoChuOP_Click(szMsgBegin, szMsgEnd,strOP, strSortNo); if (ms == null) return; //MESDownloadExcel.ExcelWebCall.ExcelFile(jsonData,out bytes,out fileName,ref fileExtension); //fileName = fileName + "."+fileExtension; string fileName = strOP+"_"+DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; DownLoadFile(fileName, ms); } ///// ///// 导出OP ///// ///// 工位号 请输入OP! ///// 开始时间 ///// 结束时间 ///// 发动机类型号 //public void btnDaoChuOP_Click(string strOP,string szMsgBegin ,string szMsgEnd,string strSortNo) private void DownLoadFile(string fileName,MemoryStream ms) { HttpContext.Current.Response.ContentType = "application/octet-stream"; //HttpContext.Current.Response.ContentType = getContentType(extension); //通知浏览器下载文件而不是打开 HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)); HttpContext.Current.Response.AddHeader("Access-Control-Expose-Headers", "Content-Disposition"); HttpContext.Current.Response.BinaryWrite(ms.ToArray()); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); } } #endregion public class SqlCallParameter { public string name { set; get; } public object value { set; get; } public SqlCallParameter(string name, object value) { this.name = name; this.value = value; } } #region Form1 class Form1 { static string LineNum = "一线"; //"0006"---"SELECT * from [0006-2015-10] union SELECT * from [0006-2015-11]" //Hashtable ht_CodeSQL = new Hashtable(); //Hashtable ht_CodeSQLMonth = new Hashtable(); //"0006"---"二维码扫描" // Hashtable ht_Code = new Hashtable(); ///// ///// 开始日期到结束日期的月份 ///// //List List_DateMonth = new List(); ///// ///// 发动机所在的月份 ///// //List List_DateMonthOne = new List(); string strExcelPath; private ExcelCells ec; /// /// "0006"对应的列"二维码 索引" /// //DataTable dt_GroupDetail = new DataTable(); string strTable2; //DataTable dt_code = new DataTable(); /// /// /// /// 开始时间 /// 结束时间 /// 生产类型码 /// 发动机号 /// 号段查询 /// 到 /// 合格 /// 返修合格 /// 不合格 /// 配件 /// 配件名称 /// public string btnSearch_Click(string szMsgBegin,string szMsgEnd, string m_sSortno, string m_sEngineNo, string m_sEngineNo1, string m_sEngineNo2, bool rbOK_Checked, bool rbFanXiu_Checked, bool rbNoOK_Checked, string szNote, string szComponent, int PageCurrent, int PageSize, int PageCount, int ItemCount) { /// /// 查询数据 /// string jsonStr = ""; try { //string szMsgBegin = dateBegin.Text; //string szMsgEnd = dateEnd.Text; //string m_sSortno = txtSortNo.Text.Trim(); //string m_sEngineNo = txtEngNo.Text.Trim(); //string m_sEngineNo1 = txtEngNoBegin.Text.Trim(); //string m_sEngineNo2 = txtEngNoEnd.Text.Trim(); DateTime dateBegin_Value = Convert.ToDateTime(szMsgBegin); DateTime dateEnd_Value = Convert.ToDateTime(szMsgEnd); TimeSpan ts = dateEnd_Value - dateBegin_Value; if (ts.TotalSeconds <= 0) { //Common.MsgInfoShow("结束时间必须大于开始时间!"); return jsonStr; } try { //启动等待窗口 //Thread TD = new Thread(ShowWaitingForm); //TD.Start(); long l1 = DateTime.Now.Ticks/10000; DateTime date = dateBegin_Value; // 开始日期到结束日期的月份 List List_DateMonth = new List(); List_DateMonth = GetList_DateMonth(dateBegin_Value, dateEnd_Value); //初始化 //List_DateMonth.Clear(); //while (true) //{ // if (date.ToString("yyyy-MM") == dateEnd_Value.ToString("yyyy-MM")) // { // string strDateMonth = date.ToString("yyyy-MM"); // List_DateMonth.Add(strDateMonth); // break; // } // else // { // string strDateMonth = date.ToString("yyyy-MM"); // List_DateMonth.Add(strDateMonth); // date = date.AddMonths(1); // } //} Hashtable ht_Code; Hashtable ht_CodeSQL = CheckTable( szMsgBegin, szMsgEnd,out ht_Code); //string szMsgBegin = dateBegin.Text; //string szMsgEnd = dateEnd.Text; //string m_sSortno = txtSortNo.Text.Trim(); //string m_sEngineNo = txtEngNo.Text.Trim(); //string m_sEngineNo1 = txtEngNoBegin.Text.Trim(); //string m_sEngineNo2 = txtEngNoEnd.Text.Trim(); string sCondiction = " and closedate between convert(datetime, '" + szMsgBegin + "') and convert(datetime, '" + szMsgEnd + "') "; if (!string.IsNullOrEmpty(m_sSortno)) { sCondiction += " and sortno='" + m_sSortno + "' "; } if (!string.IsNullOrEmpty(m_sEngineNo)) { sCondiction += " and kb+engineno like '%" + m_sEngineNo + "%' "; } //2015-03-23-zjh-发动机号码段查询 if (!string.IsNullOrEmpty(m_sEngineNo1) && !string.IsNullOrEmpty(m_sEngineNo2)) { sCondiction += " and kb+engineno >= '" + m_sEngineNo1 + "' "; sCondiction += " and kb+engineno <= '" + m_sEngineNo2 + "' "; } if (rbOK_Checked) { //2015-01-26-zjh-改为查询历史数据库 //sCondiction += " and qatable.mark = '合格' "; sCondiction += " and sortnodesc = '合格' "; } else if (rbFanXiu_Checked) { //2015-01-26-zjh-改为查询历史数据库 //sCondiction += " and qatable.mark = '返修合格' "; sCondiction += " and sortnodesc = '返修合格' "; } else if (rbNoOK_Checked) { //2015-01-26-zjh-改为查询历史数据库 //sCondiction += " and qatable.mark = '不合格' "; sCondiction += " and sortnodesc = '不合格' "; } //string szNote = cboPeiJian.Text.Trim(); //string szComponent = txtPeiJian.Text.Trim(); if (!string.IsNullOrEmpty(szComponent)) { //sCondiction += " and flowdata.engine_index in (select engine_index from DMCTable, Component where Component.componentvalue_id = dmctable.value_id and StatusTeil='" + szNote + "' and dmc like '%" + szComponent + "%') "; // and closedate between convert(datetime, '2015-10-29 00:00:00') and convert(datetime, '2015-11-01 01:32:32') and flowdata.engine_index in (select engine_index from ( select * from [0006-2015-10] UNION select * from [0006-2015-11] where 索引='ZKG' and [二维码] like '%bg%')as tb) //sCondiction += " and flowdata.engine_index in (select distinct engine_index from (" + // ht_CodeSQL["0006"].ToString() + " where 索引='" + szNote + "' and [二维码] like '%" + // szComponent + "%')as tb) "; sCondiction += " and flowdata.engine_index in (select distinct engine_index from (" + ht_CodeSQL["0006"].ToString() + ")as tb where 索引='" + szNote + "' and [二维码] like '%" + szComponent + "%' )"; } //string strSQL = "select rank() over(order by closedate desc, sortno) as 编号,qatable.engine_index, qatable.mark as 状态,sortno as 发动机类型号, kb + engineno as 发动机编号,[workload] as 托盘号,convert(char, date,120) as 生产时间 ,convert(char, closedate,120) as 完成时间 from flowdata,qatable where qatable.engine_index = flowdata.engine_index " + sCondiction; string strSqlStart = " SELECT TOP " + PageSize + " * FROM ("; string strSqlEnd = " ) as A WHERE 编号 > "+ PageSize + " * ( "+PageCurrent+"-1) "+ " DECLARE @PageCount int,@ItemCount int " +"select @PageCount ="+PageCount + " ,@ItemCount = count(*) from flowdata where 1=1 " + sCondiction +" SELECT @PageCount as N'PageCount',@ItemCount as N'ItemCount'"; string strSQL = " select rank() over(order by closedate desc, sortno) as 编号,engine_index, sortnodesc as 状态,sortno as 发动机类型号, kb + engineno as 发动机编号,[workload] as 托盘号,convert(char, date,120) as 生产时间 ,convert(char, closedate,120) as 完成时间 from flowdata where 1=1 " + sCondiction; //dt = DataHelper.ExecuteDataSet(CommandType.Text, strSQL).Tables[0]; string sql = strSqlStart + strSQL + strSqlEnd; //dt = ExecuteDataTable(sql); DataSet ds; ExecuteDataset(sql, out ds); string result = JsonHelper.DataTableToJson(ds.Tables[0]); string resultOutput = JsonHelper.DataTableToJson(ds.Tables[1]); jsonStr = "{\"result\":" + result + "," + "\"output\":" + resultOutput + "}"; //dataGridView1.DataSource = dt; //dataGridView1.Cols["engine_index"].Visible = false;//隐藏engine_index列 ////Common.BindGrid(dataGridView1); //if (dt != null && dt.Rows.Count > 0) //{ // //2015-03-17-查询后显示合格率等 // string strHeGeLv; // float iHeGe = 0, iFanXiu = 0, BuHeGe = 0, iHeGeLv = 0, iFanXiuLv = 0, BuHeGeLv = 0; // for (int i = 0; i < dt.Rows.Count; i++) // { // string strMark = dt.Rows[i]["状态"].ToString(); // if (strMark == "合格") // { // iHeGe++; // } // else if (strMark == "返修合格") // { // iFanXiu++; // } // else if (strMark == "不合格") // { // BuHeGe++; // } // } // string tmpStr1; // string tmpStr2; // string tmpStr3; // int nCount = dt.Rows.Count; // iHeGeLv = iHeGe/nCount*100; // tmpStr1 = iHeGeLv.ToString("0.00"); // iFanXiuLv = iFanXiu/nCount*100; // tmpStr2 = iFanXiuLv.ToString("0.00"); // BuHeGeLv = BuHeGe/nCount*100; // tmpStr3 = BuHeGeLv.ToString("0.00"); // strHeGeLv = "合格率:" + tmpStr1 + "% 返修合格率:" + tmpStr2 + "% 不合格率:" + tmpStr3 + "%"; // lblHeGeLv.Text = strHeGeLv; // long l22 = DateTime.Now.Ticks/10000 - l1; // toolStripStatusLabel1.Text = string.Format("查询结果:共 {0} 条记录.耗时:{1}ms", dt.Rows.Count, l22); // ////2018-06-14-zjh-默认选中第一行,防止数据量大时,点击末尾的行时软件会死 // //dataGridView1.Rows[0].Selected = true; //} //else //{ // lblHeGeLv.Text = ""; //空数据时初始化 // long l2 = DateTime.Now.Ticks/10000 - l1; // toolStripStatusLabel1.Text = string.Format("查询结果:共 0 条记录.耗时:{0}ms", l2); // //MessageBox.Show("此时间段无数据.", "结果", MessageBoxButtons.OK, MessageBoxIcon.Information); //} //waitingFrm.Activate(); //这个操作主要是为了防止在数据加载完成后， //焦点不在等待窗口上，造成自动关闭失效。 //waitingFrm.Close(); //TD.Abort(); //终止线程 //TD.Join(); } catch (Exception ex) { //Common.MsgInfoShow(ex.Source + ":" + ex.ToString()); } } catch (Exception ex) { //Common.MsgInfoShow(ex.Source + ":" + ex.ToString()); } return jsonStr; } public DataTable btnSearchDaoChu_Click(string szMsgBegin,string szMsgEnd, string m_sSortno, string m_sEngineNo, string m_sEngineNo1, string m_sEngineNo2, bool rbOK_Checked, bool rbFanXiu_Checked, bool rbNoOK_Checked, string szNote, string szComponent) { /// /// 查询数据 /// DataTable dt = new DataTable(); try { //string szMsgBegin = dateBegin.Text; //string szMsgEnd = dateEnd.Text; //string m_sSortno = txtSortNo.Text.Trim(); //string m_sEngineNo = txtEngNo.Text.Trim(); //string m_sEngineNo1 = txtEngNoBegin.Text.Trim(); //string m_sEngineNo2 = txtEngNoEnd.Text.Trim(); DateTime dateBegin_Value = Convert.ToDateTime(szMsgBegin); DateTime dateEnd_Value = Convert.ToDateTime(szMsgEnd); TimeSpan ts = dateEnd_Value - dateBegin_Value; if (ts.TotalSeconds <= 0) { //Common.MsgInfoShow("结束时间必须大于开始时间!"); return dt; } try { //启动等待窗口 //Thread TD = new Thread(ShowWaitingForm); //TD.Start(); long l1 = DateTime.Now.Ticks/10000; DateTime date = dateBegin_Value; // 开始日期到结束日期的月份 List List_DateMonth = new List(); List_DateMonth = GetList_DateMonth(dateBegin_Value, dateEnd_Value); //初始化 //List_DateMonth.Clear(); //while (true) //{ // if (date.ToString("yyyy-MM") == dateEnd_Value.ToString("yyyy-MM")) // { // string strDateMonth = date.ToString("yyyy-MM"); // List_DateMonth.Add(strDateMonth); // break; // } // else // { // string strDateMonth = date.ToString("yyyy-MM"); // List_DateMonth.Add(strDateMonth); // date = date.AddMonths(1); // } //} Hashtable ht_Code; Hashtable ht_CodeSQL = CheckTable( szMsgBegin, szMsgEnd,out ht_Code); //string szMsgBegin = dateBegin.Text; //string szMsgEnd = dateEnd.Text; //string m_sSortno = txtSortNo.Text.Trim(); //string m_sEngineNo = txtEngNo.Text.Trim(); //string m_sEngineNo1 = txtEngNoBegin.Text.Trim(); //string m_sEngineNo2 = txtEngNoEnd.Text.Trim(); string sCondiction = " and closedate between convert(datetime, '" + szMsgBegin + "') and convert(datetime, '" + szMsgEnd + "') "; if (!string.IsNullOrEmpty(m_sSortno)) { sCondiction += " and sortno='" + m_sSortno + "' "; } if (!string.IsNullOrEmpty(m_sEngineNo)) { sCondiction += " and kb+engineno like '%" + m_sEngineNo + "%' "; } //2015-03-23-zjh-发动机号码段查询 if (!string.IsNullOrEmpty(m_sEngineNo1) && !string.IsNullOrEmpty(m_sEngineNo2)) { sCondiction += " and kb+engineno >= '" + m_sEngineNo1 + "' "; sCondiction += " and kb+engineno <= '" + m_sEngineNo2 + "' "; } if (rbOK_Checked) { //2015-01-26-zjh-改为查询历史数据库 //sCondiction += " and qatable.mark = '合格' "; sCondiction += " and sortnodesc = '合格' "; } else if (rbFanXiu_Checked) { //2015-01-26-zjh-改为查询历史数据库 //sCondiction += " and qatable.mark = '返修合格' "; sCondiction += " and sortnodesc = '返修合格' "; } else if (rbNoOK_Checked) { //2015-01-26-zjh-改为查询历史数据库 //sCondiction += " and qatable.mark = '不合格' "; sCondiction += " and sortnodesc = '不合格' "; } //string szNote = cboPeiJian.Text.Trim(); //string szComponent = txtPeiJian.Text.Trim(); if (!string.IsNullOrEmpty(szComponent)) { //sCondiction += " and flowdata.engine_index in (select engine_index from DMCTable, Component where Component.componentvalue_id = dmctable.value_id and StatusTeil='" + szNote + "' and dmc like '%" + szComponent + "%') "; // and closedate between convert(datetime, '2015-10-29 00:00:00') and convert(datetime, '2015-11-01 01:32:32') and flowdata.engine_index in (select engine_index from ( select * from [0006-2015-10] UNION select * from [0006-2015-11] where 索引='ZKG' and [二维码] like '%bg%')as tb) //sCondiction += " and flowdata.engine_index in (select distinct engine_index from (" + // ht_CodeSQL["0006"].ToString() + " where 索引='" + szNote + "' and [二维码] like '%" + // szComponent + "%')as tb) "; sCondiction += " and flowdata.engine_index in (select distinct engine_index from (" + ht_CodeSQL["0006"].ToString() + ")as tb where 索引='" + szNote + "' and [二维码] like '%" + szComponent + "%' )"; } //string strSQL = "select rank() over(order by closedate desc, sortno) as 编号,qatable.engine_index, qatable.mark as 状态,sortno as 发动机类型号, kb + engineno as 发动机编号,[workload] as 托盘号,convert(char, date,120) as 生产时间 ,convert(char, closedate,120) as 完成时间 from flowdata,qatable where qatable.engine_index = flowdata.engine_index " + sCondiction; string strSQL = " select rank() over(order by closedate desc, sortno) as 编号,engine_index, sortnodesc as 状态,sortno as 发动机类型号, kb + engineno as 发动机编号,[workload] as 托盘号,convert(char, date,120) as 生产时间 ,convert(char, closedate,120) as 完成时间 from flowdata where 1=1 " + sCondiction; //dt = DataHelper.ExecuteDataSet(CommandType.Text, strSQL).Tables[0]; dt = ExecuteDataTable(strSQL); //dataGridView1.DataSource = dt; //dataGridView1.Cols["engine_index"].Visible = false;//隐藏engine_index列 ////Common.BindGrid(dataGridView1); //if (dt != null && dt.Rows.Count > 0) //{ // //2015-03-17-查询后显示合格率等 // string strHeGeLv; // float iHeGe = 0, iFanXiu = 0, BuHeGe = 0, iHeGeLv = 0, iFanXiuLv = 0, BuHeGeLv = 0; // for (int i = 0; i < dt.Rows.Count; i++) // { // string strMark = dt.Rows[i]["状态"].ToString(); // if (strMark == "合格") // { // iHeGe++; // } // else if (strMark == "返修合格") // { // iFanXiu++; // } // else if (strMark == "不合格") // { // BuHeGe++; // } // } // string tmpStr1; // string tmpStr2; // string tmpStr3; // int nCount = dt.Rows.Count; // iHeGeLv = iHeGe/nCount*100; // tmpStr1 = iHeGeLv.ToString("0.00"); // iFanXiuLv = iFanXiu/nCount*100; // tmpStr2 = iFanXiuLv.ToString("0.00"); // BuHeGeLv = BuHeGe/nCount*100; // tmpStr3 = BuHeGeLv.ToString("0.00"); // strHeGeLv = "合格率:" + tmpStr1 + "% 返修合格率:" + tmpStr2 + "% 不合格率:" + tmpStr3 + "%"; // lblHeGeLv.Text = strHeGeLv; // long l22 = DateTime.Now.Ticks/10000 - l1; // toolStripStatusLabel1.Text = string.Format("查询结果:共 {0} 条记录.耗时:{1}ms", dt.Rows.Count, l22); // ////2018-06-14-zjh-默认选中第一行,防止数据量大时,点击末尾的行时软件会死 // //dataGridView1.Rows[0].Selected = true; //} //else //{ // lblHeGeLv.Text = ""; //空数据时初始化 // long l2 = DateTime.Now.Ticks/10000 - l1; // toolStripStatusLabel1.Text = string.Format("查询结果:共 0 条记录.耗时:{0}ms", l2); // //MessageBox.Show("此时间段无数据.", "结果", MessageBoxButtons.OK, MessageBoxIcon.Information); //} //waitingFrm.Activate(); //这个操作主要是为了防止在数据加载完成后， //焦点不在等待窗口上，造成自动关闭失效。 //waitingFrm.Close(); //TD.Abort(); //终止线程 //TD.Join(); } catch (Exception ex) { //Common.MsgInfoShow(ex.Source + ":" + ex.ToString()); } } catch (Exception ex) { //Common.MsgInfoShow(ex.Source + ":" + ex.ToString()); } return dt; } private List GetList_DateMonth(DateTime dateBegin_Value ,DateTime dateEnd_Value) { // 开始日期到结束日期的月份 List List_DateMonth = new List(); //初始化 List_DateMonth.Clear(); DateTime date = dateBegin_Value; while (true) { if (date.ToString("yyyy-MM") == dateEnd_Value.ToString("yyyy-MM")) { string strDateMonth = date.ToString("yyyy-MM"); List_DateMonth.Add(strDateMonth); break; } else { string strDateMonth = date.ToString("yyyy-MM"); List_DateMonth.Add(strDateMonth); date = date.AddMonths(1); } } return List_DateMonth; } /// /// 导出全部 /// /// /// /// /// /// /// /// /// /// /// /// public MemoryStream btnDaoChuAll_Click(string szMsgBegin,string szMsgEnd, string m_sSortno, string m_sEngineNo, string m_sEngineNo1, string m_sEngineNo2, bool rbOK_Checked, bool rbFanXiu_Checked, bool rbNoOK_Checked, string szNote, string szComponent, int PageCurrent, int PageSize, int PageCount, int ItemCount) { MemoryStream ms = null; DataTable dt = btnSearchDaoChu_Click(szMsgBegin, szMsgEnd, m_sSortno, m_sEngineNo, m_sEngineNo1, m_sEngineNo2, rbOK_Checked, rbFanXiu_Checked, rbNoOK_Checked, szNote, szComponent); try { if (dt != null && dt.Rows.Count > 0) { ms = DataTableExport(dt); //Workbook workbook = new Workbook(); ////workbook.Worksheets.Add("<" + strEngNo + ">");//sheet2名称 //Worksheet sheet1 = workbook.Worksheets[0]; ////sheet1.Name = "<" + strEngNo + ">质量数据";//sheet1名称 //// Worksheet sheet2 = workbook.Worksheets[1]; //Cells cells = sheet1.Cells; //cells.ImportDataTable(dt, true, "0"); ////先创建文件夹,否则生成Excel方法:OutputExcel(),workbook.Save()报错 //string strTempPath = ConfigurationManager.AppSettings.Get("TempPath"); //string szTempFile = AppDomain.CurrentDomain.BaseDirectory + strTempPath; //if (!Directory.Exists(szTempFile)) //{ // Directory.CreateDirectory(szTempFile); //} //ec = new ExcelCells(); //string fileName = ec.OutputExcel(dt, "Temp"); //ec = new ExcelCells(); //bool blHaveCol = true;//Excel中显示表头 //ec.ImportDataColumn(dt, 0, 0, blHaveCol); ////gridDesktop1.ImportExcelFile(ec.GetExcelStream());//此方法只支持带批注的Excel 97-2003,但Excel 2007不支持 ////ImportExcelFile()方法对带批注的只支持Excel 97-2003,对Excel 2007直接加载内存流数据会报错,必须保存成相应Excel 97-2003,2007格式的中间文件再用ImportExcelFile方法打开才行 ////如果不存在文件夹则新建 //string strFilePath = AppDomain.CurrentDomain.BaseDirectory + "temp"; //if (!Directory.Exists(strFilePath)) //{ // Directory.CreateDirectory(strFilePath); //} //string fileName = strFilePath + "temp" + ec.workbook.FileName.Substring(ec.workbook.FileName.ToLower().LastIndexOf(".xls"));//根据Excel的版本存为xls或xlsx类型,否则报错 //ec.workbook.Save(fileName); ////Old ////gridDesktop1.ImportExcelFile(fileName); //2013-10-15:直接打开Excel //System.Diagnostics.Process process = new System.Diagnostics.Process(); //process.StartInfo.FileName = fileName; //process.Start(); ////string localFilePath, fileNameExt, newFileName, FilePath; //SaveFileDialog sfd = new SaveFileDialog(); ////设置文件类型 ////sfd.Filter = "Excel 2003文件（*.xls）|*.xls|Excel 2007文件（*.xlsx）|*.xlsx"; //string strHouZhui = ec.workbook.FileFormat.ToString(); //sfd.Filter = "自动选择（*.xls）和（*.xlsx）文件|*." + strHouZhui; ////设置默认文件类型显示顺序 ////sfd.FilterIndex = 2;//Excel 2007文件（*.xlsx） ////保存对话框是否记忆上次打开的目录 //sfd.RestoreDirectory = true; ////点了保存按钮进入 //if (sfd.ShowDialog() == DialogResult.OK) //{ // //string localFilePath = sfd.FileName.ToString(); //获得文件路径 // //string fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1); //获取文件名，不带路径 // //获取文件路径，不带文件名 // //FilePath = localFilePath.Substring(0, localFilePath.LastIndexOf("\\")); // //给文件名前加上时间 // //newFileName = DateTime.Now.ToString("yyyyMMdd") + fileNameExt; // //在文件名里加字符 // //saveFileDialog1.FileName.Insert(1,"dameng"); // //System.IO.FileStream fs = (System.IO.FileStream)sfd.OpenFile();//输出文件 // ////fs输出带文字或图片的文件，就看需求了 // this.Cursor = Cursors.WaitCursor;//鼠标忙 // //网格内容保存到一个的Excel文件 // gridDesktop1.ExportExcelFile(sfd.FileName); // Common.MsgInfoShow("导出成功！"); // ////格内容保存到MS Excel 2007的xlsx文件格式 // //gridDesktop1.ExportExcelFile("D:\\book1.xlsx", FileFormatType.Excel2007Xlsx); //} } else { //MessageBox.Show("记录为空,无法导出.", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } } catch (Exception ex) { // Common.MsgInfoShow(ex.Source + ":" + ex.ToString()); } return ms; } /// /// 导出单台 /// 生产时间 /// 完成时间 /// 发动机编号 /// engine_index /// 发动机类型号 public MemoryStream btnDaoChuOne_Click(string strBeginTime,string strEndTime,string strEngNo,string strEngine_Index,string strSortNo) { MemoryStream ms = null; try { //if (dataGridView1.RowSel < 0) //{ // Common.MsgInfoShow("请选择一行记录!"); // return; //} //int x = dataGridView1.RowSel; //if (dataGridView1.Rows.Count > 0) { //先创建文件夹,否则生成Excel方法:OutputExcel(),workbook.Save()报错 //string strTempPath = ConfigurationManager.AppSettings.Get("TempPath"); //string szTempFile = AppDomain.CurrentDomain.BaseDirectory + strTempPath; //if (!Directory.Exists(szTempFile)) //{ // Directory.CreateDirectory(szTempFile); //} //string strEngNo = dataGridView1.Rows[x]["发动机编号"].ToString(); //string strBeginTime = dataGridView1.Rows[x]["生产时间"].ToString(); //string strEndTime = dataGridView1.Rows[x]["完成时间"].ToString(); //string strEngine_Index = dataGridView1.Rows[x]["engine_index"].ToString(); //string strSortNo = dataGridView1.Rows[x]["发动机类型号"].ToString(); Workbook workbook = new Workbook(); workbook.Worksheets.Add("<" + strEngNo + ">");//sheet2名称 Worksheet sheet1 = workbook.Worksheets[0]; sheet1.Name = "<" + strEngNo + ">质量数据";//sheet1名称 Worksheet sheet2 = workbook.Worksheets[1]; sheet1.Cells[0, 0].PutValue("生产时间"); sheet1.Cells[0, 1].PutValue(strBeginTime); sheet1.Cells[0, 2].PutValue("完成时间"); sheet1.Cells[0, 3].PutValue(strEndTime); sheet1.Cells[0, 4].PutValue("发动机类型号"); sheet1.Cells[0, 5].PutValue(strSortNo); sheet2.Cells[0, 0].PutValue("生产时间"); sheet2.Cells[0, 1].PutValue(strBeginTime); sheet2.Cells[0, 2].PutValue("完成时间"); sheet2.Cells[0, 3].PutValue(strEndTime); int iSheet1Row = 2; string strSQL; //sheet1-new //2019-04-01-zjh-按双击网页的样式导出Excel DataSet ds = new DataSet();//存储相应op号对应的DataTable int iTableCount = 0;//table名称,加1 Hashtable htOpType = new Hashtable();//存储相应op号对应的类型代码 DataTable dtTableName = new DataTable();//op按从小到大排序后,取DataSet中的DataTable数据 dtTableName.Columns.Add("op", typeof(int)); dtTableName.Columns.Add("TableName", typeof(string)); DateTime dateBegin_Value; DateTime dateEnd_Value; //dateBegin_Value = Convert.ToDateTime(strBeginTime); //dateEnd_Value = Convert.ToDateTime(strEndTime); Hashtable ht_Code = new Hashtable(); Hashtable ht_CodeSQL = CheckTable( strBeginTime, strEndTime,out ht_Code); foreach (DictionaryEntry de in ht_CodeSQL) { strSQL = "select engine.station as 工位,engine.opnumber as OP号,tbCode.* from ( " + ht_CodeSQL[de.Key].ToString() + " ) as tbCode inner join engine on engine.engine_index = tbCode.engine_index and Engine.opnumber=tbCode.opnumber where engine.engine_index= '" + strEngine_Index + "' order by Engine.opnumber asc"; //DataTable dtCode = DataHelper.ExecuteDataSet(CommandType.Text, strSQL).Tables[0]; DataTable dtCode = ExecuteDataTable(strSQL); if (dtCode != null && dtCode.Rows.Count > 0) { string strOpRows1 = dtCode.Rows[0]["OP号"].ToString();//首行op号 DataTable dtOp = dtCode.Clone(); for (int i = 0; i < dtCode.Rows.Count; i++) { string strOpRowsN = dtCode.Rows[i]["OP号"].ToString();//第二行后的op号 if (!htOpType.Contains(strOpRowsN)) { htOpType.Add(strOpRowsN, de.Key);//op:2-----Type0006:二维码扫描,op:11-----Type0004:扳手 } if (i == 0)//处理第一行 { iTableCount++; dtOp.TableName = "tb" + iTableCount.ToString(); ds.Tables.Add(dtOp);//第一次直接添加 //添加op和TableName DataRow dr = dtTableName.NewRow(); dr["op"] = Convert.ToInt32(strOpRowsN); dr["TableName"] = dtOp.TableName; dtTableName.Rows.Add(dr); } else { if (strOpRows1 != strOpRowsN)//下一行和上一行的op不相同,数据插入到新建的DataTable中 { ds.Tables.Remove(dtOp.TableName);//把第一次的移除后再添加,否则添加相同名称的DataTable名称报错 ds.Tables.Add(dtOp.Copy()); dtOp.Rows.Clear();//清除数据 strOpRows1 = strOpRowsN;//覆盖后,下一个循环重新判断 iTableCount++; dtOp.TableName = "tb" + iTableCount.ToString(); ds.Tables.Add(dtOp); //添加op和TableName DataRow dr = dtTableName.NewRow(); dr["op"] = Convert.ToInt32(strOpRowsN); dr["TableName"] = dtOp.TableName; dtTableName.Rows.Add(dr); } } dtOp.Rows.Add(dtCode.Rows[i].ItemArray);//复制数据行 } } } DataView dv = dtTableName.DefaultView; dv.Sort = "op asc"; dtTableName = dv.ToTable(); for (int i = 0; i < dtTableName.Rows.Count; i++) { DataTable dtCode = ds.Tables[dtTableName.Rows[i]["TableName"].ToString()]; string strCode = htOpType[dtTableName.Rows[i]["op"].ToString()].ToString();//0004,0006 //strHtml.Append("\n"); //strHtml.Append("类型:" + ht_Code[strCode].ToString() + "
\n"); sheet1.Cells[iSheet1Row, 0].PutValue("类型"); sheet1.Cells[iSheet1Row, 1].PutValue(ht_Code[strCode].ToString()); iSheet1Row++; //dtCode原来列:工位 OP号 engine_index opnumber optype 二维码 索引 //dtsheet1修改列,去掉3，4，5列后:工位 OP号 二维码 索引 dtCode.Columns.Remove("engine_index"); dtCode.Columns.Remove("opnumber"); dtCode.Columns.Remove("optype"); DataTable dtsheet1 = dtCode.Copy(); //2016-03-30-zjh-自定义显示列名 strSQL = "SELECT t1.[groupid],t1.[code],t1.[description],t2.[column_index],t2.[column_name],t2.[description] as GroupDetaildescription,t2.show_name FROM [Group] t1 inner join [GroupDetail] t2 on t1.groupid=t2.groupid"; DataTable dt_GroupDetail = ExecuteDataTable(strSQL); //去掉GroupDetail表description="skip"的列 for (int iCol = 0; iCol < dtCode.Columns.Count; iCol++) { DataRow[] drs = dt_GroupDetail.Select("code='" + strCode + "' and column_name='" + dtCode.Columns[iCol].ColumnName + "'"); if (drs != null && drs.Length > 0) { if (drs[0]["GroupDetaildescription"].ToString().Trim().ToLower() == "skip") { dtsheet1.Columns.Remove(dtCode.Columns[iCol].ColumnName); } } } if (dtsheet1 != null && dtsheet1.Rows.Count > 0) { //2016-03-28-zjh-带通道号的列,按通道号从小到大排序 if (dtsheet1.Columns[2].ColumnName == "轴号") { DataTable dtsheetCopy = new DataTable(); foreach (DataColumn col in dtsheet1.Columns) { if (col.ColumnName == "轴号") { dtsheetCopy.Columns.Add("轴号", typeof(Int32)); } else { dtsheetCopy.Columns.Add(col.ColumnName); } } //填充数据 foreach (DataRow row in dtsheet1.Rows) { //dtsheetCopy.Rows.Add(row.ItemArray); dtsheetCopy.ImportRow(row); } DataView dvTest = dtsheetCopy.DefaultView; dvTest.Sort = "轴号 asc";//按轴号排序 dtsheet1 = dvTest.ToTable(); } } //strHtml.Append("

\n"); bool blHaveCol = true;//是否显示表头 int firstRow = iSheet1Row; int firstColumn = 0; bool blInsertRow = false; Cells cells = sheet1.Cells; cells.ImportDataTable(dtsheet1, blHaveCol, firstRow, firstColumn, dtsheet1.Rows.Count, dtsheet1.Columns.Count, blInsertRow, "yyyy-MM-dd HH:mm:ss"); iSheet1Row += dtsheet1.Rows.Count + 2;//加2:空一行 } //sheet2 //strSQL = "select engine.opnumber as OP号,[opNumber].description as OP号名称,engine.optype as 操作类型,[optypeinfo].name as 描述,qatable.mark as 综合 from engine inner join qatable on engine.engine_index = qatable.engine_index left join [opNumber] on engine.opnumber=[opnumber].id left join [optypeinfo] on engine.optype=[optypeinfo].id where engine.engine_index= '" + strEngine_Index + "'"; //strSQL = "select engine.opnumber as OP号,[opNumber].description as OP号名称,engine.optype as 操作类型,[optypeinfo].name as 描述,flowdata.sortnodesc as 综合 from engine inner join flowdata on engine.engine_index = flowdata.engine_index left join [opNumber] on engine.opnumber=[opnumber].id left join [optypeinfo] on engine.optype=[optypeinfo].id where engine.engine_index= '" + strEngine_Index + "'"; strSQL = "select engine.opnumber as OP号,[opNumber].description as OP号名称,engine.optype as 操作类型,[optypeinfo].name as 描述,case Engine.isworked when 1 then 'OK' when 0 then 'NOK' end as 工作,case Engine.isqaok when 1 then 'OK' when 0 then 'NOK' end as 合格,case Engine.isrepaireok when 1 then 'OK' when 0 then '-' end as 返修,OP_QATable.opmark as 综合 from engine inner join OP_QATable on engine.opnumber=OP_QATable.opnumber and engine.engine_index=OP_QATable.engine_index left join [opNumber] on engine.opnumber=[opnumber].id left join [optypeinfo] on engine.optype=[optypeinfo].id where engine.engine_index= '" + strEngine_Index + "'"; //DataTable dtsheet2 = DataHelper.ExecuteDataSet(CommandType.Text, strSQL).Tables[0]; DataTable dtsheet2 = ExecuteDataTable(strSQL); dv = dtsheet2.DefaultView; dv.Sort = "op号 asc"; dtsheet2 = dv.ToTable(); if (dtsheet2 != null && dtsheet2.Rows.Count > 0) { bool blHaveCol = true;//是否显示表头 int firstRow = 1; int firstColumn = 0; bool blInsertRow = false; Cells cells = sheet2.Cells; cells.ImportDataTable(dtsheet2, blHaveCol, firstRow, firstColumn, dtsheet2.Rows.Count, dtsheet2.Columns.Count, blInsertRow, "yyyy-MM-dd HH:mm:ss"); } //string FileName = "Temp-" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".xlsx";//.xls //string returnStr = AppDomain.CurrentDomain.BaseDirectory + "Temp\\" + FileName; ms = workbook.SaveToStream(); //workbook.Save(returnStr, SaveFormat.Xlsx); ////2013-10-15:直接打开Excel //System.Diagnostics.Process process = new System.Diagnostics.Process(); //process.StartInfo.FileName = returnStr; //process.Start(); } //else //{ // Common.MsgInfoShow("请选择一行记录!"); //} } catch (Exception ex) { //Common.MsgInfoShow(ex.Source + ":" + ex.ToString()); } return ms; } /// /// 导出OP /// /// 工位号 请输入OP! /// 开始时间 /// 结束时间 /// 发动机类型号 public MemoryStream btnDaoChuOP_Click(string szMsgBegin ,string szMsgEnd,string strOP,string strSortNo) { MemoryStream ms = null; try { //string strOP = txtOP.Text.Trim(); //if (string.IsNullOrEmpty(strOP)) //{ // Common.MsgInfoShow("请输入OP!"); // return; //} ////启动等待窗口 //Thread TD = new Thread(ShowWaitingForm); //TD.Start(); //先创建文件夹,否则生成Excel方法:OutputExcel(),workbook.Save()报错 string strTempPath = ConfigurationManager.AppSettings.Get("TempPath"); string szTempFile = AppDomain.CurrentDomain.BaseDirectory + strTempPath; if (!Directory.Exists(szTempFile)) { Directory.CreateDirectory(szTempFile); } Workbook workbook = new Workbook(); Worksheet sheet1 = workbook.Worksheets[0]; sheet1.Name = "质量数据";//sheet1名称 int iSheet1Row = 0; string strSQL; //string szMsgBegin = dateBegin.Text; //string szMsgEnd = dateEnd.Text; //DateTime dateBegin_Value = Convert.ToDateTime(szMsgBegin); //DateTime dateEnd_Value = Convert.ToDateTime(szMsgEnd); Hashtable ht_Code; Hashtable ht_CodeSQL = CheckTable( szMsgBegin, szMsgEnd,out ht_Code); //是否打开Excel文件 bool isOpen = false; foreach (DictionaryEntry de in ht_CodeSQL) { //select flowdata.kb + flowdata.engineno as 发动机号,flowdata.date as 生产时间,flowdata.closedate as 完成时间,Engine.station as 工位,Engine.opnumber as OP号,flowdata.workload as 托盘号,tbCode.* from ( select * from [0006-2015-10] UNION select * from [0006-2015-11] ) as tbCode inner join engine on engine.engine_index = tbCode.engine_index and Engine.opnumber=tbCode.opnumber inner join flowdata on tbCode.engine_index = flowdata.engine_index where FlowData.closedate between convert(datetime, '" + szMsgBegin + "') and convert(datetime, '" + szMsgEnd + "') and tbCode.opnumber=5 strSQL = "select flowdata.kb + flowdata.engineno as 发动机号,flowdata.date as 生产时间,flowdata.closedate as 完成时间,Engine.station as 工位,Engine.opnumber as OP号,flowdata.workload as 托盘号,tbCode.* from ( " + ht_CodeSQL[de.Key].ToString() + " ) as tbCode inner join engine on engine.engine_index = tbCode.engine_index and Engine.opnumber=tbCode.opnumber inner join flowdata on tbCode.engine_index = flowdata.engine_index where FlowData.closedate between convert(datetime, '" + szMsgBegin + "') and convert(datetime, '" + szMsgEnd + "') and tbCode.opnumber='" + strOP + "'"; //string strSortNo = txtSortNo.Text.Trim(); if (!string.IsNullOrEmpty(strSortNo)) { strSQL += " and flowdata.[sortno]='" + strSortNo + "'"; } //DataTable dtCode = DataHelper.ExecuteDataSet(CommandType.Text, strSQL).Tables[0]; DataTable dtCode = ExecuteDataTable(strSQL); if (dtCode != null && dtCode.Rows.Count > 0) { isOpen = true; sheet1.Cells[iSheet1Row, 0].PutValue("类型"); sheet1.Cells[iSheet1Row, 1].PutValue(ht_Code[de.Key].ToString()); iSheet1Row++; //dtCode原来列:工位 OP号 engine_index opnumber optype 二维码 索引 //dtsheet1修改列,去掉3，4，5列后:工位 OP号 二维码 索引 dtCode.Columns.Remove("engine_index"); dtCode.Columns.Remove("opnumber"); dtCode.Columns.Remove("optype"); DataTable dtsheet1 = dtCode.Copy(); //2016-03-30-zjh-自定义显示列名 strSQL = "SELECT t1.[groupid],t1.[code],t1.[description],t2.[column_index],t2.[column_name],t2.[description] as GroupDetaildescription,t2.show_name FROM [Group] t1 inner join [GroupDetail] t2 on t1.groupid=t2.groupid"; DataTable dt_GroupDetail = ExecuteDataTable(strSQL); //去掉GroupDetail表description="skip"的列 for (int iCol = 0; iCol < dtCode.Columns.Count; iCol++) { DataRow[] drs = dt_GroupDetail.Select("code='" + de.Key.ToString() + "' and column_name='" + dtCode.Columns[iCol].ColumnName + "'"); if (drs != null && drs.Length > 0) { if (drs[0]["GroupDetaildescription"].ToString().Trim().ToLower() == "skip") { dtsheet1.Columns.Remove(dtCode.Columns[iCol].ColumnName); } } } bool blHaveCol = true;//是否显示表头 int firstRow = iSheet1Row; int firstColumn = 0; bool blInsertRow = false; Cells cells = sheet1.Cells; cells.ImportDataTable(dtsheet1, blHaveCol, firstRow, firstColumn, dtsheet1.Rows.Count, dtsheet1.Columns.Count, blInsertRow, "yyyy-MM-dd HH:mm:ss"); iSheet1Row += dtsheet1.Rows.Count + 2;//加2:空一行 } } ms = workbook.SaveToStream(); //关闭稍等窗口 //waitingFrm.Close(); //TD.Abort(); //终止线程 //TD.Join(); //if (isOpen) //{ // string FileName = "Temp-" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".xlsx"; //.xls // string returnStr = AppDomain.CurrentDomain.BaseDirectory + "Temp\\" + FileName; // workbook.Save(returnStr, SaveFormat.Xlsx); // //2013-10-15:直接打开Excel // System.Diagnostics.Process process = new System.Diagnostics.Process(); // process.StartInfo.FileName = returnStr; // process.Start(); //} } catch (Exception ex) { //Common.MsgInfoShow(ex.Source + ":" + ex.ToString()); } return ms; } /// /// 异常 /// /// /// /// /// /// /// /// /// public DataTable btnYiChang_Click( string szMsgBegin,string szMsgEnd, string m_sSortno, string m_sEngineNo, string m_sEngineNo1, string m_sEngineNo2) { //string m_sSortno = txtSortNo.Text.Trim(); //string m_sEngineNo = txtEngNo.Text.Trim(); //string m_sEngineNo1 = txtEngNoBegin.Text.Trim(); //string m_sEngineNo2 = txtEngNoEnd.Text.Trim(); DataTable dt = new DataTable(); try { DateTime dateBegin_Value; DateTime dateEnd_Value; dateBegin_Value = Convert.ToDateTime(szMsgBegin); dateEnd_Value = Convert.ToDateTime(szMsgEnd); //string szMsgBegin = dateBegin_Value.ToString(); //string szMsgEnd = dateEnd_Value.ToString(); //TimeSpan ts = dateEnd.Value - dateBegin.Value; //if (ts.TotalSeconds <= 0) //{ // Common.MsgInfoShow("结束时间必须大于开始时间!"); // return; //} try { ////启动等待窗口 //Thread TD = new Thread(ShowWaitingForm); //TD.Start(); long l1 = DateTime.Now.Ticks / 10000; DateTime date = dateBegin_Value; // 开始日期到结束日期的月份 List List_DateMonth = new List(); //初始化 List_DateMonth.Clear(); while (true) { if (date.ToString("yyyy-MM") == dateEnd_Value.ToString("yyyy-MM")) { string strDateMonth = date.ToString("yyyy-MM"); List_DateMonth.Add(strDateMonth); break; } else { string strDateMonth = date.ToString("yyyy-MM"); List_DateMonth.Add(strDateMonth); date = date.AddMonths(1); } } //2018-06-07-zjh strTable2 = ""; Hashtable ht_Code; CheckTable( szMsgBegin, szMsgEnd,out ht_Code); //string szMsgBegin = dateBegin.Text; //string szMsgEnd = dateEnd.Text; //string m_sSortno = txtSortNo.Text.Trim(); //string m_sEngineNo = txtEngNo.Text.Trim(); //string m_sEngineNo1 = txtEngNoBegin.Text.Trim(); //string m_sEngineNo2 = txtEngNoEnd.Text.Trim(); string sCondiction = " and closedate between convert(datetime, '" + szMsgBegin + "') and convert(datetime, '" + szMsgEnd + "') "; if (!string.IsNullOrEmpty(m_sSortno)) { sCondiction += " and sortno='" + m_sSortno + "' "; } if (!string.IsNullOrEmpty(m_sEngineNo)) { sCondiction += " and kb+engineno like '%" + m_sEngineNo + "%' "; } //2015-03-23-zjh-发动机号码段查询 if (!string.IsNullOrEmpty(m_sEngineNo1) && !string.IsNullOrEmpty(m_sEngineNo2)) { sCondiction += " and kb+engineno >= '" + m_sEngineNo1 + "' "; sCondiction += " and kb+engineno <= '" + m_sEngineNo2 + "' "; } //string strSQL = "select rank() over(order by closedate desc, sortno) as 编号,flowdata.engine_index, qatable.mark as 状态,sortno as 发动机类型号, kb + engineno as 发动机编号,[workload] as 托盘号,convert(char, date,120) as 生产时间 ,convert(char, closedate,120) as 完成时间 from flowdata,qatable where qatable.engine_index = flowdata.engine_index and flowdata.engine_index not in(select [engine_index] from( "; //如果速度慢,不加载qatable表 StringBuilder strSQL = new StringBuilder(""); strSQL.Append("select rank() over(order by closedate desc, sortno) as 编号,flowdata.engine_index, sortnodesc as 状态,sortno as 发动机类型号, kb + engineno as 发动机编号,[workload] as 托盘号,convert(char, date,120) as 生产时间 ,convert(char, closedate,120) as 完成时间 from flowdata where flowdata.engine_index not in(select [engine_index] from( "); strSQL.Append(strTable2 + " )t1 where ( "); //opnumber=39 or opnumber=40 or opnumber=41 or opnumber=54 or opnumber=55 or opnumber=135 or opnumber=225 or opnumber=258 or opnumber=382 or opnumber=60 or opnumber=84 string strOpNumber = LineNum == "一线" ? ConfigurationManager.AppSettings.Get("OpNumber1") : ConfigurationManager.AppSettings.Get("OpNumber2"); string[] strListOpNumber = strOpNumber.Split(','); for(int i=0;i 0) //{ // long l22 = DateTime.Now.Ticks / 10000 - l1; // toolStripStatusLabel1.Text = string.Format("查询结果:共 {0} 条记录.耗时:{1}ms", dt.Rows.Count, l22); //} //else //{ // long l2 = DateTime.Now.Ticks / 10000 - l1; // toolStripStatusLabel1.Text = string.Format("查询结果:共 0 条记录.耗时:{0}ms", l2); //} ////waitingFrm.Activate(); //这个操作主要是为了防止在数据加载完成后， ////焦点不在等待窗口上，造成自动关闭失效。 //waitingFrm.Close(); //TD.Abort(); //终止线程 //TD.Join(); } catch (Exception ex) { // Common.MsgInfoShow(ex.Source + ":" + ex.ToString()); } } catch (Exception ex) { //Common.MsgInfoShow(ex.Source + ":" + ex.ToString()); } return dt; } private string css = ""; /// /// 输出发动机详细信息 /// /// 生产时间 /// 完成时间 /// 发动机编号 /// engine_index /// 文件 public MemoryStream CreatHtml(string szMsgBegin,string szMsgEnd,string m_sEngineNo,string strEngine_Index) { //int x = dataGridView1.RowSel; Hashtable ht_Code; CheckTable(szMsgBegin, szMsgEnd, out ht_Code); StringBuilder strHtml = new StringBuilder(""); //string strEngNo = dataGridView1.Rows[x]["发动机编号"].ToString().Trim(); //string strBeginTime = dataGridView1.Rows[x]["生产时间"].ToString().Trim(); //string strEndTime = dataGridView1.Rows[x]["完成时间"].ToString().Trim(); //string strEngine_Index = dataGridView1.Rows[x]["engine_index"].ToString().Trim(); strHtml.Append("\n"); strHtml.Append("\n"); strHtml.Append("" + css + " \n"); strHtml.Append("\n"); strHtml.Append("

发动机编号:" + m_sEngineNo + " 启动时间:" + szMsgBegin + " 下线时间:" + szMsgEnd + "

\n"); //第一部分:质量数据 strHtml.Append("

质量数据

\n"); strHtml.Append("

\n"); string strSQL; DataSet ds = new DataSet();//存储相应op号对应的DataTable int iTableCount = 0;//table名称,加1 Hashtable htOpType = new Hashtable();//存储相应op号对应的类型代码 DataTable dtTableName = new DataTable();//op按从小到大排序后,取DataSet中的DataTable数据 dtTableName.Columns.Add("op", typeof(int)); dtTableName.Columns.Add("TableName", typeof(string)); /// /// 发动机所在的月份 /// List List_DateMonthOne = new List(); //初始化 List_DateMonthOne.Clear(); string strDateMonth = Convert.ToDateTime(szMsgEnd).ToString("yyyy-MM"); //string strDateMonth = Convert.ToDateTime(dataGridView1.Rows[x]["完成时间"].ToString()).ToString("yyyy-MM"); List_DateMonthOne.Add(strDateMonth); Hashtable ht_CodeSQLMonth = CheckTableMonth(List_DateMonthOne); //sheet1 foreach (DictionaryEntry de in ht_CodeSQLMonth) { //select engine.station as 工位,engine.opnumber as OP号,tbCode.* from ( select * from [0006-2015-10] UNION select * from [0006-2015-11] ) as tbCode inner join engine on engine.engine_index = tbCode.engine_index and Engine.opnumber=tbCode.opnumber where engine.engine_index=2376 //select engine.station as 工位,engine.opnumber as OP号,tbCode.* from ( select * from [0007-2015-10] UNION select * from [0007-2015-11] ) as tbCode inner join engine on engine.engine_index = tbCode.engine_index and Engine.opnumber=tbCode.opnumber where engine.engine_index=2376 strSQL = "select engine.station as 工位,engine.opnumber as OP号,tbCode.* from ( " + ht_CodeSQLMonth[de.Key].ToString() + " ) as tbCode inner join engine on engine.engine_index = tbCode.engine_index and Engine.opnumber=tbCode.opnumber where engine.engine_index= '" + strEngine_Index + "' order by Engine.opnumber asc"; //DataTable dtCode = DataHelper.ExecuteDataSet(CommandType.Text, strSQL).Tables[0]; DataTable dtCode = ExecuteDataTable(strSQL); if (dtCode != null && dtCode.Rows.Count > 0) { string strOpRows1 = dtCode.Rows[0]["OP号"].ToString();//首行op号 DataTable dtOp = dtCode.Clone(); for (int i = 0; i < dtCode.Rows.Count; i++) { string strOpRowsN = dtCode.Rows[i]["OP号"].ToString();//第二行后的op号 if (!htOpType.Contains(strOpRowsN)) { htOpType.Add(strOpRowsN, de.Key);//op:2-----Type0006:二维码扫描,op:11-----Type0004:扳手 } if (i == 0)//处理第一行 { iTableCount++; dtOp.TableName = "tb" + iTableCount.ToString(); ds.Tables.Add(dtOp);//第一次直接添加 //添加op和TableName DataRow dr = dtTableName.NewRow(); dr["op"] = Convert.ToInt32(strOpRowsN); dr["TableName"] = dtOp.TableName; dtTableName.Rows.Add(dr); } else { if (strOpRows1 != strOpRowsN)//下一行和上一行的op不相同,数据插入到新建的DataTable中 { ds.Tables.Remove(dtOp.TableName);//把第一次的移除后再添加,否则添加相同名称的DataTable名称报错 ds.Tables.Add(dtOp.Copy()); dtOp.Rows.Clear();//清除数据 strOpRows1 = strOpRowsN;//覆盖后,下一个循环重新判断 iTableCount++; dtOp.TableName = "tb" + iTableCount.ToString(); ds.Tables.Add(dtOp); //添加op和TableName DataRow dr = dtTableName.NewRow(); dr["op"] = Convert.ToInt32(strOpRowsN); dr["TableName"] = dtOp.TableName; dtTableName.Rows.Add(dr); } } dtOp.Rows.Add(dtCode.Rows[i].ItemArray);//复制数据行 } } } DataView dv = dtTableName.DefaultView; dv.Sort = "op asc"; dtTableName = dv.ToTable(); for (int i = 0; i < dtTableName.Rows.Count; i++) { DataTable dtCode = ds.Tables[dtTableName.Rows[i]["TableName"].ToString()]; strHtml.Append("\n"); string strCode = htOpType[dtTableName.Rows[i]["op"].ToString()].ToString();//0004,0006 strHtml.Append("类型:" + ht_Code[strCode].ToString() + "
\n"); //dtCode原来列:工位 OP号 engine_index opnumber optype 二维码 索引 //dtsheet1修改列,去掉3，4，5列后:工位 OP号 二维码 索引 dtCode.Columns.Remove("engine_index"); dtCode.Columns.Remove("opnumber"); dtCode.Columns.Remove("optype"); DataTable dtsheet1 = dtCode.Copy(); //2016-03-30-zjh-自定义显示列名 strSQL = "SELECT t1.[groupid],t1.[code],t1.[description],t2.[column_index],t2.[column_name],t2.[description] as GroupDetaildescription,t2.show_name FROM [Group] t1 inner join [GroupDetail] t2 on t1.groupid=t2.groupid"; DataTable dt_GroupDetail = ExecuteDataTable(strSQL); //去掉GroupDetail表description="skip"的列 for (int iCol = 0; iCol < dtCode.Columns.Count; iCol++) { DataRow[] drs = dt_GroupDetail.Select("code='" + strCode + "' and column_name='" + dtCode.Columns[iCol].ColumnName + "'"); if (drs != null && drs.Length > 0) { if (drs[0]["GroupDetaildescription"].ToString().Trim().ToLower() == "skip") { dtsheet1.Columns.Remove(dtCode.Columns[iCol].ColumnName); } } } strHtml.Append("\n"); for (int j = 0; j < dtsheet1.Columns.Count; j++) { strHtml.Append("\n"); } strHtml.Append("\n"); if (dtsheet1 != null && dtsheet1.Rows.Count > 0) { //2016-03-28-zjh-带通道号的列,按通道号从小到大排序 if (dtsheet1.Columns[2].ColumnName == "轴号") { //DataTable dtsheetCopy = UpdateDataTable(dtsheet1, "轴号", "System.Int32"); //DataTable dtsheetCopy = dtsheet1.Clone(); ////修改列类型后,dtsheetCopy.ImportRow(row);“报错:"无法设置列轴号" "。此值违反了此列的MaxLength限制”,改为直接新建表后新增列 //dtsheetCopy.Columns[2].DataType = typeof(Int32);//Type.GetType("System.Int32");//无数据时才能修改表结构 DataTable dtsheetCopy = new DataTable(); foreach (DataColumn col in dtsheet1.Columns) { if (col.ColumnName == "轴号") { dtsheetCopy.Columns.Add("轴号", typeof(Int32)); //修改列类型后,dtsheetCopy.ImportRow(row);“报错:"无法设置列轴号" "。此值违反了此列的MaxLength限制”,改为直接新建表后新增列 //col.DataType = typeof(Int32); } else { dtsheetCopy.Columns.Add(col.ColumnName); } } //填充数据 foreach (DataRow row in dtsheet1.Rows) { //dtsheetCopy.Rows.Add(row.ItemArray); dtsheetCopy.ImportRow(row); } DataView dvTest = dtsheetCopy.DefaultView; dvTest.Sort = "轴号 asc";//按轴号排序 dtsheet1 = dvTest.ToTable(); } int nCounter = 0; for (int j = 0; j < dtsheet1.Rows.Count; j++) { nCounter++; if (nCounter % 2 == 1) { strHtml.Append("\n"); } else { strHtml.Append("\n"); } for (int k = 0; k < dtsheet1.Columns.Count; k++) { strHtml.Append("\n"); } strHtml.Append("\n"); } } strHtml.Append("
" + dtsheet1.Rows[j][k].ToString() + "

\n"); } //第二部分:操作 strHtml.Append("

操作

\n\n\n\n\n\n\n\n\n\n\n\n"); int nCounter2 = 0; //strSQL = "select engine.opnumber as OP号,[opNumber].description as OP号名称,engine.optype as 操作类型,[optypeinfo].name as 描述,case Engine.isworked when 1 then 'OK' when 0 then 'NOK' end as 工作,case Engine.isqaok when 1 then 'OK' when 0 then 'NOK' end as 合格,case Engine.isrepaireok when 1 then 'OK' when 0 then '-' end as 返修,case Engine.isqaok when 1 then '合格' when 0 then '不合格' end as 综合 from engine inner join qatable on engine.engine_index = qatable.engine_index left join [opNumber] on engine.opnumber=[opnumber].id left join [optypeinfo] on engine.optype=[optypeinfo].id where engine.engine_index= '" + strEngine_Index + "'"; //strSQL = "select engine.opnumber as OP号,[opNumber].description as OP号名称,engine.optype as 操作类型,[optypeinfo].name as 描述,case Engine.isworked when 1 then 'OK' when 0 then 'NOK' end as 工作,case Engine.isqaok when 1 then 'OK' when 0 then 'NOK' end as 合格,case Engine.isrepaireok when 1 then 'OK' when 0 then '-' end as 返修,case Engine.isqaok when 1 then '合格' when 0 then '不合格' end as 综合 from engine left join [opNumber] on engine.opnumber=[opnumber].id left join [optypeinfo] on engine.optype=[optypeinfo].id where engine.engine_index= '" + strEngine_Index + "'"; strSQL = "select engine.opnumber as OP号,[opNumber].description as OP号名称,engine.optype as 操作类型,[optypeinfo].name as 描述,case Engine.isworked when 1 then 'OK' when 0 then 'NOK' end as 工作,case Engine.isqaok when 1 then 'OK' when 0 then 'NOK' end as 合格,case Engine.isrepaireok when 1 then 'OK' when 0 then '-' end as 返修,OP_QATable.opmark as 综合 from engine inner join OP_QATable on engine.opnumber=OP_QATable.opnumber and engine.engine_index=OP_QATable.engine_index left join [opNumber] on engine.opnumber=[opnumber].id left join [optypeinfo] on engine.optype=[optypeinfo].id where engine.engine_index= '" + strEngine_Index + "'"; //DataTable dtsheet2 = DataHelper.ExecuteDataSet(CommandType.Text, strSQL).Tables[0]; DataTable dtsheet2 = ExecuteDataTable(strSQL); dv = dtsheet2.DefaultView; dv.Sort = "op号 asc"; dtsheet2 = dv.ToTable(); if (dtsheet2 != null && dtsheet2.Rows.Count > 0) { for (int i = 0; i < dtsheet2.Rows.Count; i++) { nCounter2++; if (nCounter2 % 2 == 1) { strHtml.Append("\n"); } else { strHtml.Append("\n"); } for (int j = 0; j < dtsheet2.Columns.Count; j++) { if (dtsheet2.Columns[j].ColumnName == "综合") { //zjh-判断综合列的背景色，不合格为红色，合格并返修为黄色，其他为无色 string strbgColor = ""; if (dtsheet2.Rows[i]["合格"].ToString() == "NOK") { strbgColor = "style='background-color:red'"; } else if (dtsheet2.Rows[i]["合格"].ToString() == "OK" && dtsheet2.Rows[i]["返修"].ToString() == "OK") { strbgColor = "style='background-color:yellow'"; } strHtml.Append("\n"); } else { strHtml.Append("\n"); } } strHtml.Append("\n"); } } strHtml.Append("
操作号操作号名称操作类型描述工作合格返修综合
" + dtsheet2.Rows[i][j].ToString() + "" + dtsheet2.Rows[i][j].ToString() + "
\n"); strHtml.Append("\n"); strHtml.Append("\n"); //string str = "Testing 1-2-3"; //convert string 2 stream byte[] array = Encoding.UTF8.GetBytes(strHtml.ToString()); MemoryStream ms = new MemoryStream(array); //convert stream 2 string return ms; // StreamReader reader = new StreamReader(stream); //string strFileName = AppDomain.CurrentDomain.BaseDirectory + "Report\\" + strEngNo + ".html"; ////如果存在,则先删除文件 //if (File.Exists(strFileName)) //{ // File.Delete(strFileName); //} //FileInfo fileinfo = new FileInfo(strFileName); //using (FileStream fs = fileinfo.OpenWrite()) //{ // //StreamWriter sw = new StreamWriter(fs);//乱码 // StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.UTF8);//Encoding.GetEncoding("gb2312") // //sw.BaseStream.Seek(0, SeekOrigin.End); // sw.Write(strHtml); // sw.Flush(); // sw.Close(); //} //return strFileName; } private Hashtable CheckTable(string szMsgBegin,string szMsgEnd,out Hashtable ht_Code) { //IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[0007-2015-10]') AND type in (N'U')) //select '1' //else //CREATE TABLE [0007-2015-10]( // [engine_index] [int] NOT NULL, // [opnumber] [int] NOT NULL, // [optype] [int] NOT NULL, // [设定上限] [nvarchar](50) NULL, // [设定下限] [nvarchar](50) NULL, // [实际] [nvarchar](50) NULL, // [Einzel I.O.] [nvarchar](50) NULL, // [程序] [nvarchar](50) NULL, // [通道] [nvarchar](50) NULL //) ON [PRIMARY] // 开始日期到结束日期的月份 DateTime dateBegin_Value = Convert.ToDateTime(szMsgBegin); DateTime dateEnd_Value = Convert.ToDateTime(szMsgEnd); ht_Code = new Hashtable(); List List_DateMonth = new List(); List_DateMonth = GetList_DateMonth(dateBegin_Value, dateEnd_Value); //IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[0007-2015-10]') AND type in (N'U')) select '1' else CREATE TABLE [0007-2015-10]([engine_index] [int] NOT NULL,[opnumber] [int] NOT NULL,[optype] [int] NOT NULL,[设定上限] [nvarchar](50) NULL,[设定下限] [nvarchar](50) NULL,[实际] [nvarchar](50) NULL,[Einzel I.O.] [nvarchar](50) NULL,[程序] [nvarchar](50) NULL,[通道] [nvarchar](50) NULL) ON [PRIMARY] Hashtable ht_CodeSQL = new Hashtable(); string strSQL = "select [code],[description] from [Group]"; //dt_code = DataHelper.ExecuteDataSet(CommandType.Text, strSQL).Tables[0]; DataTable dt_code = new DataTable(); dt_code = ExecuteDataTable(strSQL); for (int i = 0; i < List_DateMonth.Count; i++) { string strDate = List_DateMonth[i];//DateTime.Now.ToString("yyyy-MM"); string strCheckTable = ""; //2016-03-30-zjh-自定义显示列名 strSQL = "SELECT t1.[groupid],t1.[code],t1.[description],t2.[column_index],t2.[column_name],t2.[description] as GroupDetaildescription,t2.show_name FROM [Group] t1 inner join [GroupDetail] t2 on t1.groupid=t2.groupid"; //dt_GroupDetail = DataHelper.ExecuteDataSet(CommandType.Text, strSQL).Tables[0]; DataTable dt_GroupDetail = ExecuteDataTable(strSQL); if (dt_code != null && dt_code.Rows.Count > 0) { for (int j = 0; j < dt_code.Rows.Count; j++) { string strTable = "[" + dt_code.Rows[j]["code"].ToString() + "-" + strDate + "]";//[0007-2015-10] strCheckTable += "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'" + strTable + "') AND type in (N'U')) select '1' else CREATE TABLE " + strTable + "([engine_index] [int] NOT NULL,[opnumber] [int] NOT NULL,[optype] [int] NOT NULL,"; DataRow[] drs = dt_GroupDetail.Select("code='" + dt_code.Rows[j]["code"].ToString() + "'", "column_index asc"); if (drs != null && drs.Length > 0) { for (int m = 0; m < drs.Length; m++) { strCheckTable += "[" + drs[m]["column_name"] + "] [nvarchar](50) NULL,";//[设定上限] [nvarchar](50) NULL, } strCheckTable += ") ON [PRIMARY] "; } } } if (!string.IsNullOrEmpty(strCheckTable)) { ExecuteNonQuery(strCheckTable); //Utility.ExectueTranNonQuery(strCheckTable);//2016-03-08-zjh-权限用一线的数据库 } } if (dt_code != null && dt_code.Rows.Count > 0) { //初始化 ht_CodeSQL.Clear(); //2016-03-30-zjh-自定义显示列名 strSQL = "SELECT t1.[groupid],t1.[code],t1.[description],t2.[column_index],t2.[column_name],t2.[description] as GroupDetaildescription,t2.show_name FROM [Group] t1 inner join [GroupDetail] t2 on t1.groupid=t2.groupid"; DataTable dt_GroupDetail = ExecuteDataTable(strSQL); for (int i = 0; i < dt_code.Rows.Count; i++) { //2016-03-30-zjh-自定义显示列名 string strColName = ""; DataRow[] drs = dt_GroupDetail.Select("code='" + dt_code.Rows[i]["code"].ToString() + "'", "column_index asc");//column_index asc将来GroupDetail表需要增加排序列 if (drs != null && drs.Length > 0) { for (int m = 0; m < drs.Length; m++) { strColName += ",[" + drs[m]["column_name"].ToString() + "] as '" + drs[m]["show_name"].ToString() + "'"; } } string strValue = ""; string strValue2 = ""; for (int j = 0; j < List_DateMonth.Count; j++) { string strTable = "[" + dt_code.Rows[i]["code"].ToString() + "-" + List_DateMonth[j] + "]";//[0007-2015-10] //strValue += " select * from " + strTable + " "; //2016-03-30-zjh-自定义显示列名 strValue += " select engine_index,opnumber,optype" + strColName + " from " + strTable + " "; strValue2 += " select engine_index,opnumber from " + strTable + " "; if (j != List_DateMonth.Count - 1)//最后一条记录不加UNION,其他的加UNION { strValue += " UNION ";//UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL strValue2 += " UNION ALL "; } } if (!ht_CodeSQL.Contains(dt_code.Rows[i]["code"].ToString())) { //strValue = "(" + strValue + ") as [" + dt_code.Rows[i]["code"].ToString() + "]"; ht_CodeSQL.Add(dt_code.Rows[i]["code"].ToString(), strValue); //2018-06-07-zjh if (i == dt_code.Rows.Count - 1) { strTable2 += strValue2; } else { strTable2 += strValue2 + " UNION ALL "; } } if (!ht_Code.Contains(dt_code.Rows[i]["code"].ToString())) { ht_Code.Add(dt_code.Rows[i]["code"].ToString(), dt_code.Rows[i]["description"].ToString()); } } } return ht_CodeSQL; } /// /// 检查[Group]表中的[code]月份,只建发动机所在月份的表，如：[0007-2015-10],[0008-2015-10],[0009-2015-10],[0010-2015-10] /// private Hashtable CheckTableMonth(List List_DateMonthOne) { Hashtable ht_CodeSQLMonth = new Hashtable(); string strSQL = "select [code],[description] from [Group]"; //dt_code = DataHelper.ExecuteDataSet(CommandType.Text, strSQL).Tables[0]; DataTable dt_code = new DataTable(); dt_code = ExecuteDataTable(strSQL); if (dt_code != null && dt_code.Rows.Count > 0) { //初始化 ht_CodeSQLMonth.Clear(); //2016-03-30-zjh-自定义显示列名 strSQL = "SELECT t1.[groupid],t1.[code],t1.[description],t2.[column_index],t2.[column_name],t2.[description] as GroupDetaildescription,t2.show_name FROM [Group] t1 inner join [GroupDetail] t2 on t1.groupid=t2.groupid"; DataTable dt_GroupDetail = ExecuteDataTable(strSQL); for (int i = 0; i < dt_code.Rows.Count; i++) { //2016-03-30-zjh-自定义显示列名 string strColName = ""; DataRow[] drs = dt_GroupDetail.Select("code='" + dt_code.Rows[i]["code"].ToString() + "'", "column_index asc");//column_index asc将来GroupDetail表需要增加排序列 if (drs != null && drs.Length > 0) { for (int m = 0; m < drs.Length; m++) { strColName += ",[" + drs[m]["column_name"].ToString() + "] as '" + drs[m]["show_name"].ToString() + "'"; } } string strValue = ""; string strValue2 = ""; for (int j = 0; j < List_DateMonthOne.Count; j++) { string strTable = "[" + dt_code.Rows[i]["code"].ToString() + "-" + List_DateMonthOne[j] + "]";//[0007-2015-10] //strValue += " select * from " + strTable + " "; //2016-03-30-zjh-自定义显示列名 strValue += " select engine_index,opnumber,optype" + strColName + " from " + strTable + " "; strValue2 += " select engine_index,opnumber from " + strTable + " "; if (j != List_DateMonthOne.Count - 1)//最后一条记录不加UNION,其他的加UNION { strValue += " UNION ";//UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL strValue2 += " UNION ALL "; } } if (!ht_CodeSQLMonth.Contains(dt_code.Rows[i]["code"].ToString())) { //strValue = "(" + strValue + ") as [" + dt_code.Rows[i]["code"].ToString() + "]"; ht_CodeSQLMonth.Add(dt_code.Rows[i]["code"].ToString(), strValue); } } } return ht_CodeSQLMonth; } /// /// 将DataTable数据转换成Excel数据流 /// /// /// public MemoryStream DataTableExport(DataTable data) { MemoryStream ms = null; try { Workbook book = new Workbook(); Worksheet sheet = book.Worksheets[0]; Cells cells = sheet.Cells; int Colnum = data.Columns.Count;//表格列数 int Rownum = data.Rows.Count;//表格行数 //生成行 列名行 for (int i = 0; i < Colnum; i++) { cells[0, i].PutValue(data.Columns[i].ColumnName); } //生成数据行 for (int i = 0; i < Rownum; i++) { for (int k = 0; k < Colnum; k++) { cells[1 + i, k].PutValue(data.Rows[i][k].ToString()); } } ms = book.SaveToStream(); GC.Collect(); } catch (Exception e) { //logger.Error("生成excel出错：" + e.Message); } return ms; } public static DataTable ExecuteDataTable(string sqlZiDingYi) { string connStr = LineNum == "一线" ? System.Configuration.ConfigurationManager.AppSettings.Get("ConnectionString") : System.Configuration.ConfigurationManager.AppSettings.Get("connectionStringL2"); SqlConnection conn = new SqlConnection(connStr); try { conn.Open(); SqlCommand comm = new SqlCommand(sqlZiDingYi, conn); int iTimeOut = Convert.ToInt32( System.Configuration.ConfigurationManager.AppSettings.Get("TimeOut")); comm.CommandTimeout = iTimeOut;//600秒=10分钟:迁移数据量比较大,超时时间设置为10分钟 DataTable dt = new DataTable(); dt.Load(comm.ExecuteReader()); conn.Close(); return dt; } catch (Exception ex) { conn.Close(); //MessageBox.Show(ex.Source + ":" + ex.Message); return new DataTable(); } } public static bool ExecuteDataset(string sql, out DataSet ds) { string errorMessage; string connectionString = LineNum == "一线" ? System.Configuration.ConfigurationManager.AppSettings.Get("ConnectionString") : System.Configuration.ConfigurationManager.AppSettings.Get("connectionStringL2"); bool result = false; errorMessage = ""; ds = new DataSet(); using (SqlConnection conn = new SqlConnection(connectionString)) { try { conn.Open(); using (SqlDataAdapter dsCommand = new SqlDataAdapter()) { dsCommand.SelectCommand = new SqlCommand(sql, conn); dsCommand.Fill(ds); } result = true; } catch (Exception e) { errorMessage = e.ToString(); } finally { if (conn.State == ConnectionState.Open) conn.Close(); } } return result; } public static void ExecuteNonQuery(string sqlText) { string connectionString = System.Configuration.ConfigurationManager.AppSettings.Get("ConnectionString"); using (SqlConnection conn = new SqlConnection(connectionString)) { try { conn.Open(); using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = conn; cmd.CommandText = sqlText; sqlText = cmd.CommandText; cmd.ExecuteNonQuery(); } } catch (Exception e) { } finally { if (conn.State == ConnectionState.Open) conn.Close(); } } } } #endregion #region ExcelCells public class ExcelCells { public Workbook workbook; public Worksheet worksheet; public Cells cells; /// /// 初始化操作类 /// public void CreateStaticReport() { //Instantiate a new workbook workbook = new Workbook(); //workbook.Worksheets[0].Cells.SetRowHeightPixel(1, 50); } /// /// 打开报表模板 /// /// 模板路径 public void OpenReport(MemoryStream stream) { //Open template //打开模板 //string path = System.Web.HttpContext.Current.Server.MapPath("~"); //path = path.Substring(0, path.LastIndexOf("\\")); //path += @"\designer\Workbooks\HelloWorld.xls"; try { // workbook.Open(path); //workbook.Open(stream); workbook = new Workbook(stream);//此处为新方法,Open是旧方法 } catch (System.Exception ex) { throw ex; } } /// /// 打开报表模板 /// /// 文件全路径名 public void OpenReport(string fileName) { try { //workbook.Open(fileName); workbook = new Workbook(fileName);//此处为新方法,Open是旧方法 } catch (System.Exception ex) { throw ex; } } /// /// 获取工作簿中的工作表 /// /// 工作簿的位置 public void GetWorksheet(int sheetNumber) { //获得工作簿中的第一个工作表 worksheet = workbook.Worksheets[sheetNumber]; ////清空批注信息 //worksheet.ClearComments(); ////设置所有列为自适应列宽 //worksheet.AutoFitColumns(); ////指定行设置为自适应行宽 //worksheet.AutoFitRow(0, 0, 0); //获得工作表中的Sheet cells = worksheet.Cells; } public string GetEecelCellsValue(string cellsName) { return cells[cellsName].StringValue; } /// /// 用于获取Excel文件中SQL语句.有SQL语句和起始位置(即所在的行和列) /// /// 以前缀字符查询,如'$' /// public List GetExcelSQLListString(string inputString) { List SQLListString = new List(); string str = string.Empty; //读取Excel的批注 for (int i = 0; i < worksheet.Comments.Count; i++) { //str = string.Format("{0}${1}${2}", cells[0, 0].Worksheet.Comments[i].Note, cells[0, 0].Worksheet.Comments[i].Row, cells[0, 0].Worksheet.Comments[i].Column); if (!worksheet.Comments[i].Note.Contains(inputString)) { continue; } str = string.Format("{0}${1}${2}", worksheet.Comments[i].Note, worksheet.Comments[i].Row, worksheet.Comments[i].Column); SQLListString.Add(str); } //清空批注信息 worksheet.ClearComments(); //单元格直接填充方式如:connectionString$SELECT top 2 * FROM wl ////Find the cell ending with the input string //Aspose.Cells.Cell cell = cells.FindStringContains(inputString, null); //if (cell != null) //{ // str = string.Format("{0}${1}${2}", cell.StringValue, cell.Row, cell.Column); // //单元格里有公式的不做sql解析,直接调用相应公式 // if (cell.Formula == null) // { // SQLListString.Add(str); // } // // [10/23/2011 Andy] // //删除sql所在的位置内容,以防止填充错位 // //ClearCells(cell.Row, cell.Column); //} //while (cell != null) //{ // cell = cells.FindStringContains(inputString, cell); // if (cell != null) // { // str = string.Format("{0}${1}${2}", cell.StringValue, cell.Row, cell.Column); // //单元格里有公式的不做sql解析,直接调用相应公式 // if (cell.Formula == null) // { // SQLListString.Add(str); // } // // [10/23/2011 Andy] // //删除sql所在的位置内容,以防止填充错位 // //ClearCells(cell.Row, cell.Column); // } //} return SQLListString; } /// /// 用于获取Excel文件中SQL语句.有SQL语句和起始位置(即所在的行和列) /// /// 以前缀字符查询,如'$' /// Excel工作单名称 /// public List GetExcelSQLListString(string inputString,string SheetName) { List SQLListString = new List(); string str = string.Empty; //Find the cell ending with the input string Aspose.Cells.Cell cell = workbook.Worksheets[SheetName].Cells.FindStringContains(inputString, null); if (cell != null) { str = string.Format("{0}${1}${2}", cell.StringValue, cell.Row, cell.Column); SQLListString.Add(str); // [10/23/2011 Andy] //删除sql所在的位置内容,以防止填充错位 //ClearCells(cell.Row, cell.Column); } while (cell != null) { cell = cells.FindStringContains(inputString, cell); if (cell != null) { str = string.Format("{0}${1}${2}", cell.StringValue, cell.Row, cell.Column); SQLListString.Add(str); // [10/23/2011 Andy] //删除sql所在的位置内容,以防止填充错位 //ClearCells(cell.Row, cell.Column); } } return SQLListString; } /// /// 消除单元格内容 /// private void ClearCells(int row, int column) { //删除起始单元格至结束单元格数据 cells.ClearContents(row, column, row, column); cells.InsertRows(1, 2); } /// /// 返回内容数据流. /// /// public Stream GetExcelStream() { Stream stream = workbook.SaveToStream(); return stream; } /// 用于获取Excel文件中SQL语句.有SQL语句和起始位置(即所在的行和列) /// public Cell GetExcelSQLString() { string str = string.Empty; //Find the cell ending with the input string Aspose.Cells.Cell cell = cells.FindStringContains("$", null); if (cell != null) { str = string.Format("{0}${1}${2}", cell.StringValue, cell.Row, cell.Column); } return cell; } public Cell GetExcelSQLNextString(Cell previousCell) { string str = string.Empty; //Find the cell ending with the input string Aspose.Cells.Cell cell = cells.FindStringContains("$", previousCell); if (cell != null) { str = string.Format("{0}${1}${2}", cell.StringValue, cell.Row, cell.Column); } return cell; } /// /// 将DataTable写到指定的位置中.(Excel中不显示表头) /// /// /// /// public void ImportDataColumn(DataTable dt, int firstRow, int firstColumn) { int j = cells.ImportDataTable(dt, false, firstRow, firstColumn, dt.Rows.Count, dt.Columns.Count, false); } /// /// 将DataTable写到指定的位置中.(Excel中不显示表头)-是否插入行(insertRows) /// /// /// /// public void ImportDataColumn(bool blinsertRows, DataTable dt, int firstRow, int firstColumn) { int j = cells.ImportDataTable(dt, false, firstRow, firstColumn, dt.Rows.Count, dt.Columns.Count, blinsertRows); } /// /// 将DataTable写到指定的位置中.(Excel中是否显示表头) /// /// /// /// /// Excel中是否显示表头 public void ImportDataColumn(DataTable dt, int firstRow, int firstColumn,bool blHaveCol) { int j = cells.ImportDataTable(dt, blHaveCol, firstRow, firstColumn, dt.Rows.Count, dt.Columns.Count, false, "yyyy-MM-dd HH:mm:ss"); } public void ImportDataColumn(DataTable dt, string startCell) { cells.ImportDataTable(dt, false, startCell); } public DataTable ExportDataColumn(int firstRow,int firstCol,int totalRows,int totalCols) { return cells.ExportDataTable(firstRow, firstCol, totalRows, totalCols); } /// /// 向单元格中写入数据 /// /// 单元格名称,如:B1 /// 数值 public void PutCellValue(string cellsName, string stringValue) { cells[cellsName].PutValue(stringValue); } /// /// 向单元格中写入数据:x-行,y-列 /// /// 行 /// 列 /// 数值 public void PutCellValue(int x, int y, string stringValue) { cells[x, y].PutValue(stringValue); } /// /// 单元格公式计算:x-行,y-列 /// /// 行 /// 列 /// 公式 public void FormulaValue(int x, int y, string formulaValue) { cells[x, y].Formula = formulaValue; } /// /// 重新计算表格中的公式 /// public void ReCalcOnOpen() { workbook.CalculateFormula(); } /// /// 设置单元格背景颜色 /// /// /// /// public void PutCellColor(int x, int y, string colorValue) { Style stype = cells[x,y].GetStyle(); stype.ForegroundColor = System.Drawing.ColorTranslator.FromHtml(colorValue); stype.Pattern = BackgroundType.Solid; stype.Font.IsBold = true; cells[x, y].SetStyle(stype); } public void PutCellColor(string cellName, string colorValue) { Style stype = cells[cellName].GetStyle(); stype.ForegroundColor = System.Drawing.ColorTranslator.FromHtml(colorValue); stype.Pattern = BackgroundType.Solid; stype.Font.IsBold = true; cells[cellName].SetStyle(stype); } public void other() { //Get the first worksheet in the workbook //worksheet.Name = "222"; //Get the cells collection in the sheet //Put a string value into the cell using its name //写入使用其名称的单元格字符串值 cells["A1"].PutValue("Cell Value"); //put a string value into the cell using its name cells["A2"].PutValue("Hello World"); //Put an boolean value into the cell using its name cells["A3"].PutValue(true); //Put an int value into the cell using its name cells["A4"].PutValue(100); //Put an double value into the cell using its name cells["A5"].PutValue(2856.5); //Put an string value that can be converted to other data type if appropriate cells["A6"].PutValue((123.6).ToString(), true); //Put an object value into the cell using its name object obj = "Aspose"; cells["A7"].PutValue(obj); //Put an datetime value into the cell DateTime dt = DateTime.Now; cells["A8"].PutValue(dt); //cells["A9"].Style.Number = 14; cells[1].PutValue(38843); //Put a string value into the cell using its row and column cells[0, 1].PutValue("Cell Value Type"); for (int i = 1; i < 100; i++) { for (int j = 1; j < 100; j++) { cells[i, j].PutValue("ddlld" + i.ToString()); //ee.SetCellValue(1, i, j, "ddlld" + i.ToString()); } } //for (int i = 1; i < 8; i++) //{ // switch (cells[i, 0].Type) // { // //Cell value is boolean // case CellValueType.IsBool: // cells[i, 1].PutValue("IsBool"); // break; // //Cell value is datetime // case CellValueType.IsDateTime: // cells[i, 1].PutValue("IsDateTime"); // break; // //Blank cell // case CellValueType.IsNull: // cells[i, 1].PutValue("IsNull"); // break; // //Cell value is numeric // case CellValueType.IsNumeric: // cells[i, 1].PutValue("IsNumeric"); // break; // //Cell value is string // case CellValueType.IsString: // cells[i, 1].PutValue("IsString"); // break; // //Cell value type is unknown // case CellValueType.IsUnknown: // cells[i, 1].PutValue("IsUnknown"); // break; // } //} //Save the excel file // End response to avoid unneeded html after xls //Response.End(); } public void SaveAs(string path, SaveFormat saveFormat) { //workbookNew.Worksheets[sheetNumber] = worksheet; //Stream stream =(Stream) worksheet; //workbook.Worksheets.RemoveAt(sheetNumber); try { //worksheet.AutoFitColumns(); //worksheet.AutoFitColumn(5); //将所有字符串型数据转换为数值型数据 //cells.ConvertStringToNumericValue(); //获取或设置是否显示网格 worksheet.IsGridlinesVisible = true; //设置当前光标停留的单元格 worksheet.Move(0); workbook.Save(path,saveFormat); } catch (System.Exception e) { Console.WriteLine(e.Message.ToString()); } } /// /// 保存文件 /// /// 保存路径 /// 文件格式 /// 保存文件的序号,从1开始 public void SaveAs(string path, SaveFormat saveFormat, int saveSheetIndex) { try { //获取或设置是否显示网格 worksheet.IsGridlinesVisible = false; //设置当前光标停留的单元格 worksheet.Move(0); int j = workbook.Worksheets.Count; if (saveSheetIndex<1) { saveSheetIndex = 1; } ArrayList al = new ArrayList(); for (int i = 0; i < j; i++) { al.Add(workbook.Worksheets[i].Name); } al.Remove(al[saveSheetIndex-1]); foreach (String str in al) { workbook.Worksheets.RemoveAt(str); } workbook.Save(path, saveFormat); } catch (System.Exception e) { Console.WriteLine(e.Message.ToString()); } } public void ClearCell(string InputString) { Aspose.Cells.Cell cell = cells.FindStringContains(InputString, null); if (cell != null) { cell.PutValue(""); } while (cell != null) { cell = cells.FindStringContains(InputString, cell); if (cell != null) { cell.PutValue(""); } } } /// /// 导出Excel /// /// 要导出的dataTable数据 /// 创建时间：130915 wzp public string OutputExcel(DataTable dt, string fileName) { string returnStr = string.Empty; Workbook book = new Workbook(); Worksheet sheet = book.Worksheets[0]; int i = 0; foreach (DataColumn dc in dt.Columns) { sheet.Cells[0, i].PutValue(dc.ColumnName); i++; } for (int r = 0; r < dt.Rows.Count; r++) { for (int c = 0; c < dt.Columns.Count; c++) { sheet.Cells[r + 1, c].PutValue(dt.Rows[r][c].ToString()); } } string FileName = fileName + "-" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".xlsx";//.xls //string FilePath = System.Web.HttpContext.Current.Server.MapPath("~/") + "DownLoad\\" + FileName; returnStr = AppDomain.CurrentDomain.BaseDirectory +"Temp\\" + FileName; book.Save(returnStr, SaveFormat.Xlsx); return returnStr; } } #endregion