/// <summary>
/// 生成指定格式数据到Excel
/// </summary>
/// <param name="act">生成数据到excel后执行对生成后的表格的操作</param>
private void GenerateData2Excel(Action<Excel.Worksheet> act)
{
//获取数据并填充到数据表
using (var da = new v_wip_join_order_and_totalTableAdapter())
{
//检测session中保存的查询条件
object obj = Session["wip_where_content"];
string str = string.Empty;
if (obj != null && obj.ToString().Length > 0)
{
str = obj.ToString();
}
//排序条件
obj = Session["wip_order_by_content"];
string strOrderBy =
"order by (CASE WHEN [order_id] IS NULL THEN 0 ELSE 1 END)," +
"[product_num], [order_id], [is_complete_wenzi]";
if (obj != null && obj.ToString().Length > 0)
{
strOrderBy = obj.ToString();
}
//获取前1万行数据
var tab = da.GetAllData(str, strOrderBy);
//删除指定列
tab.Columns.Remove("market_dept");
tab.Columns.Remove("sale_num");
//设置列标题
string[] titleCol =
("订单序号,状态,良率,生产编号,开料,钻孔,沉镀铜,线路,蚀刻,贴合,压合,化金,电测,文字,组装1," +
"打靶,组装2,冲型,FQC,SMT,外发,暂停,WIP总PCS数,出货情况,特急等级,原始生产编号,PNL长,PNL宽,PNL面积," +
"客户名称,客户料号,订单编号,订单PCS数,订单面积," +
"特殊流程,SMT备注,覆盖膜,投料率," +
"开料PNL数,开料PNL面积,PNL含PCS数,开料PCS数,仓存数," +
"落单日期,需求日期,生产日期,已出货数,已出货面积,欠出货数,欠出货面积,订单备注," +
"订单录入员,订单录入时间,订单最后修改时间,已过文字").Split(',');
//设置表名称
string titleTab = "生产WIP " + DateTime.Now.ToString("yyyy-MM-dd HH时mm分");
//临时文件名称
string fileName = "生产WIP " + DateTime.Now.ToString("yyMMddHHmmss") + ".xls";
//将指定列的0值修改为null
for (int i = 0; i < tab.Rows.Count; i++)
{
//当前行
DataRow row = tab.Rows[i];
//指定列才修改0为空白
for (int j = 4; j < 20; j++)
{
//取得当前行当前列的值
object objValue = row[j];
int value;
//检测值
if (int.TryParse(objValue.ToString(), out value) && value == 0)
{
row[j] = DBNull.Value;
}
}
}
//执行导出数据到excel
bool isSuccess = ydPublicMethod.DataTable2Excel(tab, titleCol, ref fileName, titleTab, act);
//如果成功则跳转到指定页面
if (isSuccess)
{
Response.Redirect(fileName);
//停止加载后续内容
Response.End();
}
}
}
/// <summary>
/// 生成指定格式数据到Excel
/// </summary>
/// <param name="act">生成数据到excel后执行对生成后的表格的操作</param>
private void GenerateData2Excel(Action<Excel.Worksheet> act)
{
//获取数据并填充到数据表
using (var da = new v_wip_join_order_and_totalTableAdapter())
{
//检测session中保存的查询条件
object obj = Session["wip_where_content"];
string str = string.Empty;
if (obj != null && obj.ToString().Length > 0)
{
str = obj.ToString();
}
//排序条件
obj = Session["wip_order_by_content"];
string strOrderBy = "order by (CASE WHEN [order_id] IS NULL THEN 0 ELSE 1 END)," +
"[product_num],[order_id]";
if (obj != null && obj.ToString().Length > 0)
{
strOrderBy = obj.ToString();
}
//获取前1万行数据
var tab = da.GetAllData(str, strOrderBy);
//设置列标题
string[] titleCol =
("订单序号,生产编号,pnl长,pnl宽,pnl面积,pnl含pcs数,开料,钻孔," +
"线路,电镀,蚀刻,EQC,绿油,字符,热固油,喷锡,冲型,CNCV坑,测试,FQC,包装,外发," +
"wip总pcs数,首卡卡号,尾卡卡号,状态,良率,订单生产编号,pcs面积,客户名称,客户代码," +
"订单编号,订单pcs总数,订单pcs总面积,仓存pcs数,下单日期,需求日期,生产日期," +
"已交pcs数,已交pcs面积,未交pcs数量,未交pcs面积,备注,订单录入员,录入时间,修改时间").Split(',');
//设置表名称
string titleTab = "WIP " + DateTime.Now.ToString("yyyy-MM-dd HH时mm分");
//临时文件名称
string fileName = "WIP " + DateTime.Now.ToString("yyMMddHHmmss") + ".xls";
//将指定列的0值修改为null
for (int i = 0; i < tab.Rows.Count; i++)
{
//当前行
DataRow row = tab.Rows[i];
//指定列才修改0为空白
for (int j = 4; j < 25; j++)
{
//取得当前行当前列的值
object objValue = row[j];
int value;
//检测值
if (int.TryParse(objValue.ToString(), out value) && value == 0)
{
row[j] = DBNull.Value;
}
}
}
//执行导出数据到excel
bool isSuccess = ydPublicMethod.DataTable2Excel(tab, titleCol, ref fileName, titleTab, act);
//如果成功则跳转到指定页面
if (isSuccess)
{
Response.Redirect(fileName);
//停止加载后续内容
Response.End();
}
}
}
/// <summary>
/// 返回wip数据
/// </summary>
/// <param name="context">封装的http信息对象</param>
/// <returns></returns>
private string GetWipData(HttpContext context)
{
//获取数据并填充到数据表
using (var da = new v_wip_join_order_and_totalTableAdapter())
{
//检测session中保存的查询条件
object obj = context.Session["wip_where_content"];
string str = string.Empty;
if (obj != null && obj.ToString().Length > 0)
{
str = obj.ToString();
}
//检查传入的排序列名
string sort = context.Request.Params["sort"];
if (!string.IsNullOrWhiteSpace(sort))
{
//排序方向
string order = context.Request.Params["order"];
//默认方向为顺序
if (string.IsNullOrWhiteSpace(order) || order.ToLower() != "desc")
{
order = "asc";
}
//设置到session
context.Session["wip_order_by_content"] = "order by [" + sort + "] " + order;
}
//排序条件
obj = context.Session["wip_order_by_content"];
string strOrderBy = string.Empty;
if (obj != null && obj.ToString().Length > 0)
{
strOrderBy = obj.ToString();
}
//获取前1万行数据
var tab = da.GetAllData(str, strOrderBy);
//序列化
var jss = ydJsonConvert.ToJson(tab as System.Data.DataTable);
//返回数据
return jss;
}
}
