3579 lines
143 KiB
Plaintext
3579 lines
143 KiB
Plaintext
<%@ 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;//<a href="uploadInvoiceScan.ashx">uploadInvoiceScan.ashx</a>
|
||
//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<jsonobj>(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<SqlCallParameter> 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<SqlCallParameter> 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<SqlCallParameter> sqlCallParameterList)
|
||
{
|
||
SqlCallParameter thisParms = null;
|
||
JsonData jsonData;
|
||
JsonData jsonDataName;//存储过程名称 Sql
|
||
JsonData jsonDataParam;//存储过程参数
|
||
sqlCallParameterList = new List<SqlCallParameter>();
|
||
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)
|
||
{
|
||
}
|
||
}
|
||
/// <summary>
|
||
/// 返回输出参数类型
|
||
/// </summary>
|
||
/// <param name="type"></param>
|
||
/// <param name="output"></param>
|
||
/// <returns></returns>
|
||
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;
|
||
|
||
}
|
||
/// <summary>
|
||
/// Unicode解码
|
||
/// </summary>
|
||
/// <param name="str"></param>
|
||
/// <returns></returns>
|
||
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(); });
|
||
}
|
||
/// <summary>
|
||
/// "UserID": $("#UserID").val(),
|
||
/// "ModularID": $("#ModularID").val(), 子组件
|
||
/// "Type": $("#TYPE").val(),
|
||
/// "Name": $("#NAME").val(),
|
||
/// "Param": $("#PARAM1value").val(),
|
||
/// "Pagination": $("#Pagination").val(),
|
||
/// HasReturn: $("#HASRETURN").get(0).checked
|
||
/// </summary>
|
||
/// <param name="json"></param>
|
||
/// <param name="type"></param>
|
||
/// <param name="name"></param>
|
||
/// <param name="param"></param>
|
||
/// <param name="userID"></param>
|
||
/// <param name="pagination"></param>
|
||
/// <param name="hasReturn"></param>
|
||
/// <param name="modularID">子组件</param>
|
||
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
|
||
}
|
||
|
||
/// <summary>
|
||
/// 查询
|
||
/// </summary>
|
||
/// <returns></returns>
|
||
private void btnSearch_Click(HttpContext context,List<SqlCallParameter> 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);
|
||
|
||
}
|
||
/// <summary>
|
||
/// 查询 导出
|
||
/// </summary>
|
||
/// <returns></returns>
|
||
private void btnSearch_DaoChu_Click(HttpContext context,List<SqlCallParameter> 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);
|
||
|
||
}
|
||
/// <summary>
|
||
/// 异常查询
|
||
/// </summary>
|
||
/// <returns></returns>
|
||
private void btnYiChang_Click(HttpContext context,List<SqlCallParameter> 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);
|
||
}
|
||
/// <summary>
|
||
/// 导出全部
|
||
/// </summary>
|
||
private void btnDaoChuAll_Click(HttpContext context,List<SqlCallParameter> 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)
|
||
|
||
|
||
|
||
}
|
||
|
||
/// <summary>
|
||
/// 发动机详细信息
|
||
/// </summary>
|
||
private void CreatHtml_Click(HttpContext context,List<SqlCallParameter> 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);
|
||
}
|
||
/// <summary>
|
||
/// 导出单台
|
||
/// </summary>
|
||
private void btnDaoChuOne_Click(HttpContext context,List<SqlCallParameter> 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);
|
||
|
||
|
||
}
|
||
/// <summary>
|
||
/// 导出OP
|
||
/// </summary>
|
||
private void btnDaoChuOP_Click(HttpContext context,List<SqlCallParameter> 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);
|
||
|
||
}
|
||
///// <summary>
|
||
///// 导出OP
|
||
///// </summary>
|
||
///// <param name="strOP">工位号 请输入OP!</param>
|
||
///// <param name="szMsgBegin">开始时间</param>
|
||
///// <param name="szMsgEnd">结束时间</param>
|
||
///// <param name="strSortNo">发动机类型号</param>
|
||
//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();
|
||
|
||
|
||
|
||
///// <summary>
|
||
///// 开始日期到结束日期的月份
|
||
///// </summary>
|
||
//List<string> List_DateMonth = new List<string>();
|
||
|
||
///// <summary>
|
||
///// 发动机所在的月份
|
||
///// </summary>
|
||
//List<string> List_DateMonthOne = new List<string>();
|
||
|
||
string strExcelPath;
|
||
|
||
|
||
private ExcelCells ec;
|
||
|
||
/// <summary>
|
||
/// "0006"对应的列"二维码 索引"
|
||
/// </summary>
|
||
//DataTable dt_GroupDetail = new DataTable();
|
||
|
||
string strTable2;
|
||
|
||
//DataTable dt_code = new DataTable();
|
||
|
||
/// <summary>
|
||
///
|
||
/// </summary>
|
||
/// <param name="dateBegin_Value">开始时间</param>
|
||
/// <param name="dateEnd_Value">结束时间</param>
|
||
/// <param name="m_sSortno">生产类型码</param>
|
||
/// <param name="m_sEngineNo"> 发动机号</param>
|
||
/// <param name="m_sEngineNo1">号段查询</param>
|
||
/// <param name="m_sEngineNo2">到</param>
|
||
/// <param name="rbOK_Checked">合格</param>
|
||
/// <param name="rbFanXiu_Checked">返修合格</param>
|
||
/// <param name="rbNoOK_Checked">不合格</param>
|
||
/// <param name="szNote">配件</param>
|
||
/// <param name="szComponent">配件名称</param>
|
||
/// <returns></returns>
|
||
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)
|
||
|
||
{
|
||
|
||
/// <summary>
|
||
/// 查询数据
|
||
/// </summary>
|
||
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<string> List_DateMonth = new List<string>();
|
||
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)
|
||
|
||
{
|
||
|
||
/// <summary>
|
||
/// 查询数据
|
||
/// </summary>
|
||
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<string> List_DateMonth = new List<string>();
|
||
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<string> GetList_DateMonth(DateTime dateBegin_Value ,DateTime dateEnd_Value)
|
||
{
|
||
// 开始日期到结束日期的月份
|
||
List<string> List_DateMonth = new List<string>();
|
||
//初始化
|
||
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;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 导出全部
|
||
/// </summary>
|
||
/// <param name="dateBegin_Value"></param>
|
||
/// <param name="dateEnd_Value"></param>
|
||
/// <param name="m_sSortno"></param>
|
||
/// <param name="m_sEngineNo"></param>
|
||
/// <param name="m_sEngineNo1"></param>
|
||
/// <param name="m_sEngineNo2"></param>
|
||
/// <param name="rbOK_Checked"></param>
|
||
/// <param name="rbFanXiu_Checked"></param>
|
||
/// <param name="rbNoOK_Checked"></param>
|
||
/// <param name="szNote"></param>
|
||
/// <param name="szComponent"></param>
|
||
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;
|
||
}
|
||
/// <summary>
|
||
/// 导出单台
|
||
/// <param name="strBeginTime">生产时间</param>
|
||
/// <param name="strEndTime">完成时间</param>
|
||
/// <param name="strEngNo">发动机编号</param>
|
||
/// <param name="strEngine_Index">engine_index</param>
|
||
/// <param name="strSortNo">发动机类型号</param>
|
||
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("<table class='component'>\n");
|
||
//strHtml.Append("类型:" + ht_Code[strCode].ToString() + "<br>\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("</table><br>\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;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 导出OP
|
||
/// </summary>
|
||
/// <param name="strOP">工位号 请输入OP!</param>
|
||
/// <param name="szMsgBegin">开始时间</param>
|
||
/// <param name="szMsgEnd">结束时间</param>
|
||
/// <param name="strSortNo">发动机类型号</param>
|
||
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;
|
||
}
|
||
|
||
|
||
|
||
/// <summary>
|
||
/// 异常
|
||
/// </summary>
|
||
/// <param name="dateBegin_Value"></param>
|
||
/// <param name="dateEnd_Value"></param>
|
||
/// <param name="strEndTime"></param>
|
||
/// <param name="m_sSortno"></param>
|
||
/// <param name="m_sEngineNo"></param>
|
||
/// <param name="m_sEngineNo1"></param>
|
||
/// <param name="m_sEngineNo2"></param>
|
||
/// <returns></returns>
|
||
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<string> List_DateMonth = new List<string>();
|
||
//初始化
|
||
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<strListOpNumber.Length;i++)
|
||
{
|
||
if (i == strListOpNumber.Length - 1)
|
||
{
|
||
strSQL.Append(" opnumber=" + strListOpNumber[i] + " ");
|
||
}
|
||
else
|
||
{
|
||
strSQL.Append(" opnumber=" + strListOpNumber[i] + " or ");
|
||
}
|
||
}
|
||
|
||
string stDataLen = LineNum == "一线" ? ConfigurationManager.AppSettings.Get("DataLen1") : ConfigurationManager.AppSettings.Get("DataLen2");
|
||
|
||
strSQL.Append(" ) group by engine_index having count(engine_index)=" + stDataLen + " ) ");
|
||
//strSQL += " ) group by engine_index having count(engine_index)=46 ) and closedate between convert(datetime, '" + dateBegin.Value.ToString() + "') and convert(datetime, '" + dateEnd.Value.ToString() + "') ";
|
||
|
||
strSQL.Append(sCondiction);
|
||
|
||
//dt = DataHelper.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
|
||
dt = ExecuteDataTable(strSQL.ToString());
|
||
|
||
//dataGridView1.DataSource = dt;
|
||
|
||
//dataGridView1.Cols["engine_index"].Visible = false;//隐藏engine_index列
|
||
|
||
////Common.BindGrid(dataGridView1);
|
||
|
||
//if (dt != null && dt.Rows.Count > 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 =
|
||
"<style>* {font-size: 12pt;padding: 0;font-family:\"tahoma\";background-color:whitesmoke;}h3 {font:\"tahoma\";font-size:15pt;}table{border:3px solid lightgray;border-collapse:collapse;padding:20;}.component{}.componenthead, .componentdesc{font-weight:700;word-break:break-all;text-align: center;}.componentdesc td{background-color: lightgray;}.componenthead td{padding: 5;background-color: lightgray;color: gray;}.componenthead ~ tr td{border: 1px solid lightgray;padding: 4;padding-left: 10;}.engine{margin-top:30px;width:100%;}.enginehead td{background-color:skyblue;padding:10;border:1px solid lightgray;}.enginehead ~ tr td{border:1px solid lightgray;padding:5;}.row2 td{background-color: #e3e3e3;}.componenthead td:after{content: \"[\" attr(cname) \"]\";}.componenthead ~ tr:hover td {background-color: gold;}</style>";
|
||
/// <summary>
|
||
/// 输出发动机详细信息
|
||
/// </summary>
|
||
/// <param name="strBeginTime">生产时间</param>
|
||
/// <param name="strEndTime">完成时间</param>
|
||
/// <param name="strEngNo">发动机编号</param>
|
||
/// <param name="strEngine_Index">engine_index</param>
|
||
/// <returns>文件</returns>
|
||
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("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" \"http://www.w3.org/TR/html4/loose.dtd\">\n");
|
||
strHtml.Append("<html>\n");
|
||
strHtml.Append("<head>" + css + " <meta http-equiv=Content-Type content=\"text/html;charset=utf-8\"> <link rel=\"stylesheet\" href=\"../report2.css\"></head>\n");
|
||
strHtml.Append("<body>\n");
|
||
strHtml.Append("<h3>发动机编号:" + m_sEngineNo + " 启动时间:" + szMsgBegin + " 下线时间:" + szMsgEnd + "</h3>\n");
|
||
|
||
//第一部分:质量数据
|
||
strHtml.Append("<center><h3>质量数据</h3></center>\n");
|
||
strHtml.Append("<p></p>\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));
|
||
|
||
/// <summary>
|
||
/// 发动机所在的月份
|
||
/// </summary>
|
||
List<string> List_DateMonthOne = new List<string>();
|
||
//初始化
|
||
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("<table class='component'>\n");
|
||
|
||
string strCode = htOpType[dtTableName.Rows[i]["op"].ToString()].ToString();//0004,0006
|
||
strHtml.Append("类型:" + ht_Code[strCode].ToString() + "<br>\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("<tr class='componenthead'>\n");
|
||
for (int j = 0; j < dtsheet1.Columns.Count; j++)
|
||
{
|
||
strHtml.Append("<td cname='" + dtsheet1.Columns[j].ColumnName + "'></td>\n");
|
||
}
|
||
strHtml.Append("</tr>\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("<tr class='row1'>\n");
|
||
}
|
||
else
|
||
{
|
||
strHtml.Append("<tr class='row2'>\n");
|
||
}
|
||
|
||
for (int k = 0; k < dtsheet1.Columns.Count; k++)
|
||
{
|
||
strHtml.Append("<td>" + dtsheet1.Rows[j][k].ToString() + "</td>\n");
|
||
}
|
||
|
||
strHtml.Append("</tr>\n");
|
||
}
|
||
}
|
||
|
||
strHtml.Append("</table><br>\n");
|
||
}
|
||
|
||
//第二部分:操作
|
||
strHtml.Append("<center><h3>操作</h3></center>\n<table class='engine'>\n<tr class='enginehead'>\n<td>操作号</td>\n<td>操作号名称</td>\n<td>操作类型</td>\n<td>描述</td>\n<td>工作</td>\n<td>合格</td>\n<td>返修</td>\n<td>综合</td>\n</tr>\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("<tr class='row1'>\n");
|
||
}
|
||
else
|
||
{
|
||
strHtml.Append("<tr class='row2'>\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("<td " + strbgColor + ">" + dtsheet2.Rows[i][j].ToString() + "</td>\n");
|
||
}
|
||
else
|
||
{
|
||
strHtml.Append("<td>" + dtsheet2.Rows[i][j].ToString() + "</td>\n");
|
||
}
|
||
}
|
||
|
||
strHtml.Append("</tr>\n");
|
||
}
|
||
}
|
||
|
||
strHtml.Append("</table>\n");
|
||
strHtml.Append("</body>\n");
|
||
strHtml.Append("</html>\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<string> List_DateMonth = new List<string>();
|
||
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;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 检查[Group]表中的[code]月份,只建发动机所在月份的表，如：[0007-2015-10],[0008-2015-10],[0009-2015-10],[0010-2015-10]
|
||
/// </summary>
|
||
private Hashtable CheckTableMonth(List<string> 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;
|
||
}
|
||
/// <summary>
|
||
/// 将DataTable数据转换成Excel数据流
|
||
/// </summary>
|
||
/// <param name="data"></param>
|
||
/// <returns></returns>
|
||
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;
|
||
|
||
/// <summary>
|
||
/// 初始化操作类
|
||
/// </summary>
|
||
public void CreateStaticReport()
|
||
{
|
||
//Instantiate a new workbook
|
||
workbook = new Workbook();
|
||
//workbook.Worksheets[0].Cells.SetRowHeightPixel(1, 50);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 打开报表模板
|
||
/// </summary>
|
||
/// <param name="path">模板路径</param>
|
||
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;
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 打开报表模板
|
||
/// </summary>
|
||
/// <param name="fileName">文件全路径名</param>
|
||
public void OpenReport(string fileName)
|
||
{
|
||
try
|
||
{
|
||
//workbook.Open(fileName);
|
||
workbook = new Workbook(fileName);//此处为新方法,Open是旧方法
|
||
}
|
||
catch (System.Exception ex)
|
||
{
|
||
throw ex;
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 获取工作簿中的工作表
|
||
/// </summary>
|
||
/// <param name="sheetNumber">工作簿的位置</param>
|
||
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;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 用于获取Excel文件中SQL语句.有SQL语句和起始位置(即所在的行和列)
|
||
/// </summary>
|
||
/// <param name="inputString">以前缀字符查询,如'$'</param>
|
||
/// <returns></returns>
|
||
public List<string> GetExcelSQLListString(string inputString)
|
||
{
|
||
List<string> SQLListString = new List<string>();
|
||
|
||
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;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 用于获取Excel文件中SQL语句.有SQL语句和起始位置(即所在的行和列)
|
||
/// </summary>
|
||
/// <param name="inputString">以前缀字符查询,如'$'</param>
|
||
/// <param name="SheetName">Excel工作单名称</param>
|
||
/// <returns></returns>
|
||
public List<string> GetExcelSQLListString(string inputString,string SheetName)
|
||
{
|
||
|
||
List<string> SQLListString = new List<string>();
|
||
|
||
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;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 消除单元格内容
|
||
/// </summary>
|
||
private void ClearCells(int row, int column)
|
||
{
|
||
//删除起始单元格至结束单元格数据
|
||
cells.ClearContents(row, column, row, column);
|
||
|
||
cells.InsertRows(1, 2);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 返回内容数据流.
|
||
/// </summary>
|
||
/// <returns></returns>
|
||
public Stream GetExcelStream()
|
||
{
|
||
Stream stream = workbook.SaveToStream();
|
||
return stream;
|
||
}
|
||
|
||
/// 用于获取Excel文件中SQL语句.有SQL语句和起始位置(即所在的行和列)
|
||
/// </summary>
|
||
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;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 将DataTable写到指定的位置中.(Excel中不显示表头)
|
||
/// </summary>
|
||
/// <param name="dt"></param>
|
||
/// <param name="firstRow"></param>
|
||
/// <param name="firstColumn"></param>
|
||
public void ImportDataColumn(DataTable dt, int firstRow, int firstColumn)
|
||
{
|
||
int j = cells.ImportDataTable(dt, false, firstRow, firstColumn, dt.Rows.Count, dt.Columns.Count, false);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 将DataTable写到指定的位置中.(Excel中不显示表头)-是否插入行(insertRows)
|
||
/// </summary>
|
||
/// <param name="dt"></param>
|
||
/// <param name="firstRow"></param>
|
||
/// <param name="firstColumn"></param>
|
||
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);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 将DataTable写到指定的位置中.(Excel中是否显示表头)
|
||
/// </summary>
|
||
/// <param name="dt"></param>
|
||
/// <param name="firstRow"></param>
|
||
/// <param name="firstColumn"></param>
|
||
/// <param name="blHaveCol">Excel中是否显示表头</param>
|
||
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);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 向单元格中写入数据
|
||
/// </summary>
|
||
/// <param name="cellsName">单元格名称,如:B1</param>
|
||
/// <param name="stringValue">数值</param>
|
||
public void PutCellValue(string cellsName, string stringValue)
|
||
{
|
||
cells[cellsName].PutValue(stringValue);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 向单元格中写入数据:x-行,y-列
|
||
/// </summary>
|
||
/// <param name="x">行</param>
|
||
/// <param name="y">列</param>
|
||
/// <param name="stringValue">数值</param>
|
||
public void PutCellValue(int x, int y, string stringValue)
|
||
{
|
||
cells[x, y].PutValue(stringValue);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 单元格公式计算:x-行,y-列
|
||
/// </summary>
|
||
/// <param name="x">行</param>
|
||
/// <param name="y">列</param>
|
||
/// <param name="formulaValue">公式</param>
|
||
public void FormulaValue(int x, int y, string formulaValue)
|
||
{
|
||
cells[x, y].Formula = formulaValue;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 重新计算表格中的公式
|
||
/// </summary>
|
||
public void ReCalcOnOpen()
|
||
{
|
||
workbook.CalculateFormula();
|
||
}
|
||
|
||
/// <summary>
|
||
/// 设置单元格背景颜色
|
||
/// </summary>
|
||
/// <param name="x"></param>
|
||
/// <param name="y"></param>
|
||
/// <param name="colorValue"></param>
|
||
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());
|
||
}
|
||
|
||
}
|
||
|
||
|
||
/// <summary>
|
||
/// 保存文件
|
||
/// </summary>
|
||
/// <param name="path">保存路径</param>
|
||
/// <param name="saveFormat">文件格式</param>
|
||
/// <param name="saveSheetIndex">保存文件的序号,从1开始</param>
|
||
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("");
|
||
}
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 导出Excel
|
||
/// </summary>
|
||
/// <param name="dt">要导出的dataTable数据</param>
|
||
/// <returns>创建时间：130915 wzp</returns>
|
||
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 |