protected override void CreatingSubWriters()
{
//如未指定模板Excel或模板Excel
if (_book == null)
{
_book = _isXSSF ? (IWorkbook) new XSSFWorkbook() : new HSSFWorkbook(); // WorkbookFactory.Create(new MemoryStream());
}
//记录模板的Sheet
List <string> sheetnames = new List <string>();
for (int i = 0; i < _book.NumberOfSheets; i++)
{
sheetnames.Add(_book.GetSheetName(i));
}
_allTempleteSheets = sheetnames.ToArray();
foreach (var sheet in ProductRule.Sheets)
{
ISheet exSheet = _book.GetSheet(sheet.Name) ?? _book.CreateSheet(sheet.Name);
SheetWriter writer = new SheetWriter(exSheet, sheet, this);
if (!sheet.IsDynamic)
{
Components.Add(writer);
}
else
{
exSheet.IsSelected = false;
foreach (var dSheet in writer.GetDynamics())
{
ISheet newSheet = exSheet.CopySheet(dSheet.NameRule);
Components.Add(new SheetWriter(newSheet, dSheet, this));
}
}
}
}
//建立新的Sheet for 寫入
protected ISheet createSheet(int idx)
{
ISheet targetSheet = (ISheet)hssfworkbook.CloneSheet(0);
string strSheetName = "費用表_" + idx;
if (hssfworkbook.GetSheet(strSheetName) != null)
{
targetSheet = hssfworkbook.GetSheet(strSheetName);
}
else
{
log.Info("creare Sheet");
ISheet tempSheet = hssfworkbook.GetSheet("樣本");
string strNewSheetName = "費用表_" + lstSheetName.Count;
targetSheet = tempSheet.CopySheet(strNewSheetName);
}
return(targetSheet);
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
if (string.IsNullOrEmpty(Request["BattchNum"]))
{
throw new Exception("非法数据请求。提示：批次号无效。");
}
try
{
string sourceFileName = Server.MapPath("/template/mes_out.xls");
string targetFileName = "";
using (ProxyBE p = new ProxyBE())
{
SearchOrderDetailArgs args = new SearchOrderDetailArgs();
args.OrderBy = "[OrderID]";
//按批次导出
string BattchNo = Request["BattchNum"].ToString();
args.BattchCode = BattchNo;
args.CabinetStatus = "M";
SearchResult sr = p.Client.SearchOrderDetail(SenderUser, args);
#region 排除库存件及外购件
List <Category> lists = new List <Category>();
List <Category> lists_O = new List <Category>();
Category cate_S = p.Client.GetCategoryByParentID_CategoryCode(SenderUser, Guid.Empty, "StorageMaterials");
Category cate_O = p.Client.GetCategoryByParentID_CategoryCode(SenderUser, Guid.Empty, "OutSourcingMaterials");
if (cate_S != null)
{
lists = p.Client.GetCategoriesByParentID(SenderUser, cate_S.CategoryID);
}
if (cate_O != null)
{
lists_O = p.Client.GetCategoriesByParentID(SenderUser, cate_O.CategoryID);
}
lists.AddRange(lists_O);//两个集合合并
var whereItems = new Dictionary <string, string>();
foreach (Category c in lists)
{
if (c.IsDisabled)
{
continue;
}
string[] CategoryNames = c.CategoryName.Split(',');
string where = "";
foreach (string s in CategoryNames)
{
if (s.Trim() != "")
{
where += string.Format(" ItemName not like '%{0}%' ", s);
}
}
whereItems.Add(c.CategoryName, where);
}
var condition = string.Empty;
foreach (var item in whereItems)
{
condition += item.Value + "And";
}
condition = condition.TrimEnd('A', 'n', 'd');
var srows = sr.DataSet.Tables[0].Select(condition);
var tmpDs = new DataSet();
DataTable dt = sr.DataSet.Tables[0].Clone(); //复制表结构
if (srows.Length > 0)
{
foreach (var row in srows)
{
dt.ImportRow(row);
}
}
tmpDs.Tables.Add(dt);
#endregion
//批次总柜数
int TotalBattchQty = p.Client.GetTotalOrderBattchQty(SenderUser, BattchNo);
targetFileName = Path.Combine(Config.StorageFolder, "temp");
if (!Directory.Exists(targetFileName))
{
Directory.CreateDirectory(targetFileName);
}
targetFileName = Path.Combine(targetFileName, BattchNo + "_排产优化.xls");
if (File.Exists(targetFileName))
{
File.Delete(targetFileName);
}
File.Copy(sourceFileName, targetFileName, true);
//加工文件
SearchOrderProcessFileArgs pfArgs = new SearchOrderProcessFileArgs();
pfArgs.BattchNum = BattchNo;
pfArgs.FileType = new List <string>()
{
"DXF", "CNC", "ProcessFile"
};
SearchResult pfResult = p.Client.SearchOrderProcessFile(SenderUser, pfArgs);
//订单列表
List <Guid> list_OrderIDs = new List <Guid>();
List <Category> category_lists = new List <Category>();
Category category = p.Client.GetCategoryByParentID_CategoryCode(SenderUser, Guid.Empty, "OptimizeType");
if (category != null)
{
category_lists = p.Client.GetCategoriesByParentID(SenderUser, category.CategoryID);
}
Dictionary <string, string> conditions = new Dictionary <string, string>();
if (category_lists.Count == 0)
{
conditions.Add("开料", "IsSpecialShap=0");
}
else
{
conditions.Add("异形板", "IsSpecialShap=1"); //先抽异型
var list = category_lists.OrderByDescending(o => o.Sequence); //排序：先挑Y的数据
foreach (Category c in list)
{
if (c.IsDisabled)
{
continue;
}
string[] CategoryNames = c.CategoryName.Split(',');
string where = " 1=1 and (1>2 ";
if (CategoryNames.Contains("Y"))
{
where += string.Format(" or ItemName like '{0}%')", c.CategoryName);
}
else if (CategoryNames.Length == 1)
{
where += string.Format(" or ItemName like '%{0}%') and MadeWidth>=78 and MadeLength>=78", c.CategoryName);
}
else
{
foreach (string s in CategoryNames)
{
if (s.Trim() != "")
{
where += string.Format(" or ItemName like '%{0}%'", s);
}
}
where += ") and MadeWidth>=78 and MadeLength>=78";
}
conditions.Add(c.CategoryName, where);
}
}
conditions.Add("小板", "IsSpecialShap=0 and (MadeWidth<78 or MadeLength<78)");
//异形
//conditions.Add("异形板", "IsSpecialShap=1");
conditions.Add("其它", "1=1");
using (FileStream fs = new FileStream(targetFileName, FileMode.Open, FileAccess.Read))
{
#region 导出数据
IWorkbook workbook = new HSSFWorkbook(fs);
ISheet worksheet = workbook.GetSheetAt(0);
int sheet_index = 0;
foreach (string key in conditions.Keys)
{
//DataRow[] rows = sr.DataSet.Tables[0].Select(conditions[key]);
DataRow[] rows = tmpDs.Tables[0].Select(conditions[key]);
if (rows.Length == 0)
{
continue;
}
#region 填充数据
if (sheet_index >= 1)
{
worksheet.CopySheet(key + "_优化数据表", true);
}
else
{
workbook.SetSheetName(sheet_index, key + "_优化数据表");
}
worksheet = workbook.GetSheetAt(sheet_index);
sheet_index++;
#region 清空sheet页数据
int TotalRows = worksheet.LastRowNum;
for (int rownum = 1; rownum <= TotalRows; rownum++)
{
IRow datarow = worksheet.GetRow(rownum);
worksheet.RemoveRow(datarow);
}
#endregion
int rows_index = 1;//从第2行开始导入
foreach (DataRow row in rows)
{
Guid OrderID = new Guid(row["OrderID"].ToString());
if (!list_OrderIDs.Contains(OrderID))
{
list_OrderIDs.Add(OrderID);
}
//加工文件
string dxfFile = "";
DataRow[] pfrow = pfResult.DataSet.Tables[0].Select(string.Format("FileName like '%{0}%' and OrderID='{1}'", row["BarcodeNo"].ToString(), OrderID));
if (pfrow.Length > 0)
{
dxfFile = pfrow[0]["FilePath"].ToString();
}
IRow _row = worksheet.CreateRow(rows_index); //表示每循环一次，在Excel中创建一行，并给这一行
_row.Height = 25 * 20;
_row.CreateCell(0).SetCellValue(rows_index); //序号
_row.CreateCell(1).SetCellValue(row["ItemName"].ToString()); //板件名称
_row.CreateCell(2).SetCellValue(row["BarcodeNo"].ToString()); //板件名称
_row.CreateCell(3).SetCellValue(row["MaterialType"].ToString()); //材质颜色
_row.CreateCell(4).SetCellValue(decimal.Parse(row["MadeLength"].ToString()).ToString("#")); //开料长度
_row.CreateCell(5).SetCellValue(decimal.Parse(row["MadeWidth"].ToString()).ToString("#")); //开料宽度
_row.CreateCell(6).SetCellValue(decimal.Parse(row["MadeHeight"].ToString()).ToString("#")); //厚度
_row.CreateCell(7).SetCellValue(decimal.Parse(row["EndLength"].ToString()).ToString("#")); //成品长度
_row.CreateCell(8).SetCellValue(decimal.Parse(row["EndWidth"].ToString()).ToString("#")); //成品宽度
_row.CreateCell(9).SetCellValue(row["IsSpecialShap"].ToString().ToLower() == "true" ? "是" : "否"); //是否异形
_row.CreateCell(10).SetCellValue(decimal.Parse(row["Qty"].ToString()).ToString("#")); //数量
_row.CreateCell(11).SetCellValue(row["TextureDirection"].ToString()); //修改纹理
_row.CreateCell(12).SetCellValue(row["EdgeDesc"].ToString()); //封边描述
_row.CreateCell(13).SetCellValue(row["FrontLabel"].ToString()); //正面条码
_row.CreateCell(14).SetCellValue(row["BackLabel"].ToString()); //反面条码
_row.CreateCell(15).SetCellValue(row["CabinetGroup"].ToString()); //柜体号
_row.CreateCell(16).SetCellValue(row["Remarks"].ToString()); //工艺备注
_row.CreateCell(17).SetCellValue(dxfFile); //保存路径
_row.CreateCell(18).SetCellValue(row["OrderNo"].ToString()); //订单号
_row.CreateCell(19).SetCellValue(row["CustomerName"].ToString()); //客户名称
_row.CreateCell(20).SetCellValue(row["Address"].ToString()); //客户地址
_row.CreateCell(21).SetCellValue(row["ShipDate"].ToString()); //交货日期
_row.CreateCell(22).SetCellValue(row["CabinetName"].ToString()); //产品名称
_row.CreateCell(23).SetCellValue(row["MaterialStyle"].ToString()); //风格
_row.CreateCell(24).SetCellValue(row["MaterialCategory"].ToString()); //材质
_row.CreateCell(25).SetCellValue(row["Color"].ToString()); //颜色
_row.CreateCell(26).SetCellValue(row["BattchCode"].ToString()); //批次号
_row.CreateCell(27).SetCellValue(TotalBattchQty); //批次总柜数
_row.CreateCell(28).SetCellValue(row["BattchIndex"].ToString()); //批次柜号
int TotalOrderQty = p.Client.GetTotalOrderCabinetQty(SenderUser, new Guid(row["OrderID"].ToString()));
_row.CreateCell(29).SetCellValue(TotalOrderQty); //订单总柜数
_row.CreateCell(30).SetCellValue(row["Sequence"].ToString()); //当前柜号
rows_index++;
}
foreach (DataRow r in rows)
{
//sr.DataSet.Tables[0].Rows.Remove(r);
tmpDs.Tables[0].Rows.Remove(r);
}
#endregion
}
#endregion
Response.Clear();
HttpContext.Current.Response.Buffer = true;
Response.ContentType = "application/ms-excel";
Response.Charset = "GB2312";
Response.ContentEncoding = Encoding.UTF8;
Response.AppendHeader("content-disposition", "attachment;filename=\"" + System.Web.HttpUtility.UrlEncode(Path.GetFileName(targetFileName)));
using (MemoryStream ms = new MemoryStream())
{
//将工作簿的内容放到内存流中
workbook.Write(ms);
//将内存流转换成字节数组发送到客户端
Response.BinaryWrite(ms.GetBuffer());
//Response.End();
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
FileStream file = new FileStream(targetFileName, FileMode.Create);
workbook.Write(file);
file.Close();
workbook = null;
ms.Close();
ms.Dispose();
}
p.Client.UpdateOrder2CabinetStatusByBattchCode(BattchNo, "P");
//排产完成
foreach (Guid orderid in list_OrderIDs)
{
List <Order2Cabinet> cabinets = p.Client.GetOrder2CabinetByOrderID(SenderUser, orderid);
if (cabinets.Find(li => li.CabinetStatus == "M") != null)
{
continue;
}
Order order = p.Client.GetOrder(SenderUser, orderid);
if (order != null)
{
order.Status = "P";
order.StepNo++;
SaveOrderArgs orderArgs = new SaveOrderArgs();
orderArgs.Order = order;
//流程步骤
//OrderTask ot = new OrderTask();
//ot.Action = "订单优化完成，待生产";
//ot.CurrentStep = "订单优化";
//ot.ActionRemarksType = "订单系统操作";
//ot.ActionRemarks = "订单优化完成，待生产";
//ot.Resource = "订单排产组";
//ot.NextResources = "";
//ot.NextStep = "待生产";
//orderArgs.OrderTask = ot;
p.Client.SaveOrder(SenderUser, orderArgs);
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
/// <summary>
/// 导出遍历每一行 Modify:杨波 2016-03-26
/// </summary>
/// <param name="dt">table</param>
/// <param name="sheetIndex">模板文件编号</param>
private void SetCellValue(DataTable dt, int sheetIndex)
{
int totlecount = dt.Rows.Count; //获得table总行
int pagecount = 0;
int pagesize = 64000; //分页数大小 在2003中最多支持行数65500
ISheet sheet1 = null;
//获取页面数
if (totlecount == 0)
{
return;
}
if (totlecount % pagesize > 0)
{
pagecount = (totlecount / pagesize) + 1;
}
else
{
pagecount = totlecount / pagesize;
}
//获取第一个sheet
sheet1 = hssfworkbook.GetSheetAt(sheetIndex);
//按照页数复制sheet
for (int s = 1; s < pagecount; s++)
{
sheet1.CopySheet(hssfworkbook.GetSheetName(sheetIndex) + "_" + s);
}
//设置变量
int sheetCount = 0;
int a = 0;
int LastRowNum = 0;
int rowNum = 0;
int i;
int count = 0;
//默认第一次执行
rowNum = list.Max(d => d.RowIndex);
// cellCount = list.Max(d => d.ColumnIndex);//-2014 01 10-
//需要把循环以下的行向下移动dt.Rows.Count;
i = list.Where(d => d.SetValueType == 1).FirstOrDefault().RowIndex + 1;
if (pagecount > 1)
{
count = pagesize; //按页面大小来
}
else
{
count = dt.Rows.Count;
}
LastRowNum = sheet1.LastRowNum;
sheet1.ShiftRows(i, LastRowNum + 1, count - 1, true, true);
//循环DataTable
foreach (DataRow dr in dt.Rows)
{
//如果页面超出设定大小 我们就查找下一个sheet 在循环添加
if (a == pagesize)
{
sheetCount++;//循环行数计数器
a = 0;
if (sheetCount == (pagecount - 1))
{
count = totlecount % pagesize;
}
else
{
count = pagesize;
}
rowNum = list.Max(d => d.RowIndex);
i = list.Where(d => d.SetValueType == 1).FirstOrDefault().RowIndex + 1;
string names = hssfworkbook.GetSheetName(sheetIndex) + "_" + sheetCount;
sheet1 = hssfworkbook.GetSheet(hssfworkbook.GetSheetName(sheetIndex) + "_" + sheetCount);
LastRowNum = sheet1.LastRowNum;
sheet1.ShiftRows(i, LastRowNum + 1, count - 1, true, true);
}
if (rowNum != rowNum + a)
{
sheet1.CreateRow(rowNum + a);
for (int j = 0; j <= cellCount; j++)//创建单元格 //-2014 01 10 j < cellCount;
{
sheet1.GetRow(rowNum + a).CreateCell(j);
}
}
foreach (ExcelModel m in list)
{
if (m.SetValueType == 1)
{
if (dt.Columns.Contains(m.ColumnName))
{
sheet1.GetRow(m.RowIndex + a).GetCell(m.ColumnIndex).SetCellValue(dr[m.ColumnName].ToString());
}
else
{
sheet1.GetRow(m.RowIndex + a).GetCell(m.ColumnIndex).SetCellValue("");
}
sheet1.GetRow(m.RowIndex + a).GetCell(m.ColumnIndex).CellStyle = m.ColumnStyle;
sheet1.GetRow(m.RowIndex + a).GetCell(m.ColumnIndex).SetCellType(m.ColumnType);
}
}
a++;
}
}
public HttpResponseMessage DownloadConsultationTemplate()
{
List <GetApplicationConsultationDTO> appConsultations = _ctx.Applications
.Where(a => a.CurrentYear == SystemConfig.ApplicationStartYear && a.Status == ApplicationStatus.FINISH_REVIEW)
.OrderBy(a => a.TotalScore)
.Select(a => new GetApplicationConsultationDTO
{
ProjectName = a.ProjectName,
ApplicationId = a.ApplicationId,
DelegateType = a.DeleageType == DelegateType.NORMAL ? "非定向":"定向",
ProjectTypeName = a.ProjectType.Name,
InstituteName = a.Institute.Name,
LeaderName = a.Leader.Name,
Period = a.Period.Value,
Budget = a.TotalBudget,
TotalScore = a.TotalScore,
CurrentYearBudget = a.FirstYearBudget
})
.OrderByDescending(ac => ac.TotalScore)
.ToList();
var prjConsultations = _ctx.Projects
.Where(p => p.Status == ProjectStatus.ACTIVE)
.Where(p => p.EndDate.Year >= DateTime.Now.Year)
.Where(p => !p.AnnualTasks.Any(at => at.Year == DateTime.Now.Year))
.Select(p => new GetProjectConsultationDTO()
{
ProjectName = p.Name,
ProjectId = p.ProjectId,
DelegateType = p.DelegateType == DelegateType.NORMAL ? "非定向" : "定向",
ProjectTypeName = p.ProjectType.Name,
InstituteName = p.Institude.Name,
LeaderName = p.Leader.Name,
Period = p.Period.Value,
Budget = p.TotalBudget,
ArrivalBudget = p.AnnualTasks.Sum(at => at.CurrentBudget.Value),
ApplicationId = p.ApplicationId,
Year = p.AnnualTasks.Count() + 1,
CurrentYearBudget = 0.0
})
.ToList();
foreach (var prj in prjConsultations)
{
var budget = _ctx.AnnualBudgets.FirstOrDefault(ab => ab.ApplicationId == prj.ApplicationId && ab.Year == prj.Year);
if (null != budget)
{
prj.CurrentYearBudget = budget.Amount.Value;
}
}
string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, SystemConfig.ExportConsultationExcelTemplate);
IWorkbook workbook = WorkbookFactory.Create(path);
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.General;
style.VerticalAlignment = VerticalAlignment.Center;
ISheet appSheet = workbook.GetSheet("Application");
ISheet prjSheet = workbook.GetSheet("Project");
if (appConsultations.Count > 0 || prjConsultations.Count > 0)
{
workbook.RemoveSheetAt(0);
workbook.RemoveSheetAt(0);
}
int supportAmount = appConsultations.Count * 2 / 10;
int storageAmount = appConsultations.Count * 4 / 10;
var support = appConsultations.Take(supportAmount).Select(a => a.ApplicationId).ToList();
var storage = appConsultations.Skip(supportAmount).Take(storageAmount).Select(a => a.ApplicationId).ToList();
var projectTypes = _ctx.ProjectTypes.Select(pt => pt.Name);
#region 存放非定向的项目
foreach (string type in projectTypes)
{
var typeApp = appConsultations.Where(a => a.ProjectTypeName.Equals(type) && a.DelegateType.Equals("非定向"));//筛选出非定向的项目
if (typeApp.Count() <= 0)
{
continue;
}
ISheet typeSheet = appSheet.CopySheet(type, true);
int insertRow = 1;
foreach (var app in typeApp)
{
if (!app.TotalScore.HasValue)
{
return(ResponseWrapper.ExceptionResponse(new OtherException("请先手动计算总分后再导出数据")));
}
IRow row = typeSheet.CreateRow(insertRow);
row.CreateCell(0).SetCellValue(app.ProjectName);
row.CreateCell(1).SetCellValue(app.ApplicationId);
row.CreateCell(2).SetCellValue(app.DelegateType);
row.CreateCell(3).SetCellValue(app.InstituteName);
row.CreateCell(4).SetCellValue(app.LeaderName);
row.CreateCell(5).SetCellValue(app.Period);
row.CreateCell(6).SetCellValue(app.TotalScore.Value);
row.CreateCell(7).SetCellValue(app.Budget.Value);
row.CreateCell(8).SetCellValue(app.CurrentYearBudget.Value);
if (support.Contains(app.ApplicationId))
{
row.CreateCell(9).SetCellValue("出库");
}
else if (storage.Contains(app.ApplicationId))
{
row.CreateCell(9).SetCellValue("入库");
}
else
{
row.CreateCell(9).SetCellValue("不资助");
}
insertRow++;
}
//typeSheet.SetColumnHidden(2, true);
}
#endregion
#region 存放定向项目
var directionalProject = appConsultations.Where(a => a.DelegateType.Equals("定向"));
if (directionalProject.Count() > 0)
{
ISheet typeSheet = appSheet.CopySheet("定向委托项目", true);
//对于定向项目，把初审评分修改为类别信息
IRow headRow = typeSheet.GetRow(0);
headRow.GetCell(6).SetCellValue("类别");
int insertRow = 1;
foreach (var app in directionalProject)
{
IRow row = typeSheet.CreateRow(insertRow);
row.CreateCell(0).SetCellValue(app.ProjectName);
row.CreateCell(1).SetCellValue(app.ApplicationId);
row.CreateCell(2).SetCellValue(app.DelegateType);
row.CreateCell(3).SetCellValue(app.InstituteName);
row.CreateCell(4).SetCellValue(app.LeaderName);
row.CreateCell(5).SetCellValue(app.Period);
row.CreateCell(6).SetCellValue(app.ProjectTypeName);
row.CreateCell(7).SetCellValue(app.Budget.Value);
row.CreateCell(8).SetCellValue(app.CurrentYearBudget.Value);
row.CreateCell(9).SetCellValue("出库");
insertRow++;
}
}
#endregion
if (prjConsultations.Count() > 0)
{
ISheet projectSheet = prjSheet.CopySheet("在研项目", true);
int insertRow = 1;
foreach (var prj in prjConsultations)
{
IRow row = projectSheet.CreateRow(insertRow);
row.CreateCell(0).SetCellValue(prj.ProjectName);
row.CreateCell(1).SetCellValue(prj.ProjectId);
row.CreateCell(2).SetCellValue(prj.ProjectTypeName);
row.CreateCell(3).SetCellValue(prj.InstituteName);
row.CreateCell(4).SetCellValue(prj.LeaderName);
row.CreateCell(5).SetCellValue(prj.Period);
row.CreateCell(6).SetCellValue(prj.Budget.Value);
row.CreateCell(7).SetCellValue(prj.ArrivalBudget.Value);
row.CreateCell(8).SetCellValue(prj.CurrentYearBudget.Value);
row.CreateCell(9).SetCellValue("持续资助");
insertRow++;
}
}
string desName = string.Format("{0}_{1}.{2}", "Consultation", DateTime.Now.ToFileTime(), "xls");
string desPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, SystemConfig.ConsultationPath, desName);
var stream = File.OpenWrite(desPath);
workbook.Write(stream);
stream.Close();
workbook.Close();
return(FileHelper.Download(HttpContext.Current, "\\" + SystemConfig.ConsultationPath + "\\" + desName, desName));
}
public ISheet CopySheet(string Name)
{
return(_sheet.CopySheet(Name));
}
