public void offerPriceItemAddExport(){
condition.clear();
condition.put("id",enquireId);
enquirePrice = publicManager.getFirst(EnquirePrice.class, condition);
condition.clear();
condition.put("enquirePrice",enquirePrice);
condition.put("orderby", "id");
enquirePriceDetailList=publicManager.getList(EnquirePriceDetail.class,condition);
//获取价格表头
condition.clear();
condition.put("objId",enquireId);
condition.put("relaType",Constants.OFFER_PRICE_SUM_ORDER_TYPE_ENQUIRE);
condition.put("orderby","sort");
List<PriceTitleRela> priceTitleRelaList=publicManager.getList(PriceTitleRela.class, condition);
String fileName="导入模板.xls";
String sheetName="导入模板";
OutputStream out=null;
WritableWorkbook wwb=null;
WritableSheet ws=null;
Label label=null;
HttpServletResponse response = ServletActionContext.getResponse();
try {
out=response.getOutputStream();
wwb=Workbook.createWorkbook(out);
ws=wwb.createSheet(sheetName, 0);
//设置列宽度
for(int i=0;i<7+priceTitleRelaList.size();i++){
ws.setColumnView(i+1, 15);
}
//设置字体,大小,颜色等;
WritableFont font1 = new WritableFont(WritableFont.ARIAL,8,WritableFont.BOLD,false, UnderlineStyle.NO_UNDERLINE,Colour.RED);
WritableCellFormat cellFormat1 = new WritableCellFormat(font1);
ws.mergeCells(0, 0, 6+priceTitleRelaList.size(), 0);//第一行合并
// 要插入到的Excel表格的行号,默认从0开始,表头
label=new Label(0,0,"注意:品牌件价格与具体品牌同时为空或不为空",cellFormat1);
ws.addCell(label);
label=new Label(0,1,"序号");
ws.addCell(label);
label=new Label(1,1,"代码");
ws.addCell(label);
label=new Label(2,1,"名称");
ws.addCell(label);
label=new Label(3,1,"数量");
ws.addCell(label);
List<Map<String ,String>> priceList=new ArrayList<Map<String ,String>>();
if(priceTitleRelaList!=null&&priceTitleRelaList.size()>0){
for (int i = 0; i < priceTitleRelaList.size(); i++) {
label=new Label(4+i,1,priceTitleRelaList.get(i).getColumnChnName());
ws.addCell(label);
if(priceTitleRelaList.get(i).getColumnEngName().contains("_NAME")){
Map<String ,String> map=new HashMap<String ,String>();
map.put("ColumnEngName", priceTitleRelaList.get(i).getColumnEngName().substring(0, priceTitleRelaList.get(i).getColumnEngName().indexOf("_NAME")));
map.put("col", String.valueOf(6+i));
priceList.add(map);
}
}
label=new Label(6+priceTitleRelaList.size(),1,"预计物流天数");
ws.addCell(label);
}else{
label=new Label(6,1,"预计物流天数");
ws.addCell(label);
}
for(Integer i=0;i<enquirePriceDetailList.size();i++){
label=new Label(0,i+2,i+1+"");
ws.addCell(label);
label=new Label(1,i+2,enquirePriceDetailList.get(i).getPartNo());
ws.addCell(label);
label=new Label(2,i+2,enquirePriceDetailList.get(i).getPartName());
ws.addCell(label);
label=new Label(3,i+2,enquirePriceDetailList.get(i).getQuantity().setScale(0, BigDecimal.ROUND_HALF_UP).toString());
ws.addCell(label);
label=new Label(4,i+2,enquirePriceDetailList.get(i).getModelName());
ws.addCell(label);
label=new Label(5,i+2,enquirePriceDetailList.get(i).getRemark());
ws.addCell(label);
//配置一个可选下拉框
for (int j = 0; j < priceList.size(); j++) {
condition.clear();
condition.put("subQualityCode",priceList.get(j).get("ColumnEngName"));
condition.put("mainQualityCode",Constants.EP_QUALITY_PPJ);
List<PartQualityBrand> PartQualityBrandList=publicManager.getList(PartQualityBrand.class,condition);
List angerlist = new ArrayList();
Label lblColumn = new Label(Integer.valueOf(priceList.get(j).get("col")), i+2, "");//生成一个待选择的标签
WritableCellFeatures wcf2 = new WritableCellFeatures();//待选择集合对象,这是jxl的对象
if(PartQualityBrandList!=null&&PartQualityBrandList.size()>0){
for (PartQualityBrand partQualityBrand : PartQualityBrandList) {
angerlist.add(partQualityBrand.getPartBrandName());
}
}else angerlist.add(" ");
wcf2.setDataValidationList(angerlist);//设置jxl对象要选择的集合
lblColumn.setCellFeatures(wcf2);//设置到单元格里面去
ws.addCell(lblColumn);//加入sheet表格中
}
}
//插入查到的数据
Label cell=null;
response.setContentType("application/msexcel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "iso8859-1") );
wwb.write();
out.flush();
wwb.close();
} catch (IOException e) {
e.printStackTrace();
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}finally{
IOUtils.closeQuietly(out);
}
}
java新建并下载excel,添加数据
最新推荐文章于 2024-07-07 03:38:48 发布