客户需要从完单物料信息中到处excel
大概思路:
单击某一按钮,触发请求至后台,创建输出流,导出excel ^_^
前台代码:
(此段代码 注释部分存在一个问题,注释部分的请求无效,后台无法响应前台请求,
参数传过去了,后台也接受了,但输出流没有输出,木鸡why)解决方法,用一个重定向代替这个框架sendRequest发送请求。
isc.IButton.create({
ID:"excelBtn",
width:"80",
height:"25",
icon:"demoApp/page_excel.png",
title:"導出excel",
click:function(){
var length = DataList.getSelectedRecords().getLength();
if(length>0){
var projEndId = DataList.getSelectedRecord().projEndId;
window.location='com/el/ProjectEnd/createPeSystemReport.action?projEndId=' + projEndId;
// RPCManager.sendRequest({
// actionURL:"com/el/ProjectEnd/createPeSystemReport.action",
// params:{projEndId:projEndId},
// callback:function() {isc.say("報表導入成功");}
// });
}else{
isc.say("請選擇要導出的項目");
}
}
});
后台代码
在web层(controller)写一个方法,在此方法内获取信息数据源,创建输出流,然后调用生成excel的函数,关闭输出流。
//----------導出excel報----------------------------------
@RequestMapping(value = "com/el/ProjectEnd/createPeSystemReport.action")
public void exportExecl(HttpServletRequest request, HttpServletResponse response) {
int projEndId = Integer.parseInt(request.getParameter("projEndId"));
System.out.println("projEndId--:"+projEndId);
List<ProjItemSumLine> list = projItemSumLineService.findProjLineByProjEndId(projEndId);
ProjItemSumHead projItemSumHead = this.projItemSumHeadService.findProjItemSumHeadByProjEndId(projEndId);
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=PEMaterialSummary.xls; target=_blank");
String ppc_ = "PPC";
String pmc_ = "PMC";
String purchaser_ = "Purchaser";//採購
String engineer_ = "Engineer";
String ppc = this.projItemSumLiabUserService.findProjItemSumLiabUserName(projEndId, ppc_);
String pmc = this.projItemSumLiabUserService.findProjItemSumLiabUserName(projEndId, pmc_);
String purchaser = this.projItemSumLiabUserService.findProjItemSumLiabUserName(projEndId, purchaser_);
String enginer = this.projItemSumLiabUserService.findProjItemSumLiabUserName(projEndId, engineer_);
ExportToExecl ete = new ExportToExecl();
OutputStream os = ete.exportMaterialSummary(list,projItemSumHead,ppc,pmc,purchaser,enginer, response);
try {
if(os != null){
os.flush();
os.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
生成excel的函数:
返回OutputStream类型
package com.el.pe.util.export;
import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.IndexedColors;
import com.el.pe.model.ProjItemSumHead;
import com.el.pe.model.ProjItemSumLine;
public class ExportToExecl {
public OutputStream exportMaterialSummary(List<ProjItemSumLine> list, ProjItemSumHead projItemSumHead,String ppc,String pmc,String purchaser,String enginer,HttpServletResponse response) {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet itemSheet = workbook.createSheet("PEMaterialSummary");
int rownum = 2;// 第二行開始
String cellTitle[] = {"列名"};
Map<String, HSSFCellStyle> styles = ExportToExecl.createStyles(workbook);
HSSFRow row_first_header = itemSheet.createRow(rownum++);
HSSFCell cell_title = null;
//創建列名
for (int i = 0; i < cellTitle.length; i++) {
itemSheet.setColumnWidth(i, 18 * 256);
row_first_header.setHeight((short) (2 * 356));
cell_title = row_first_header.createCell(i);
cell_title.setCellType(HSSFCell.CELL_TYPE_STRING);
cell_title.setCellStyle(styles.get("cell_header_title"));
cell_title.setCellValue(cellTitle[i]);
}
//合併單元格 四个参数分别为:开始行开始列,结束行结束列
// itemSheet.addMergedRegion(new CellRangeAddress(0,(short)0,1,(short)0));
// itemSheet.addMergedRegion(new CellRangeAddress((short)1, (short)1, (short)2, (short)2));
//写入数据
HSSFRow row_data = null;
HSSFCell cell_data = null;
for(int j =0;j<list.size();j++){
for(int i = 0 ;i<cellTitle.length;i++){
//此处省略N个字
}
try {
OutputStream os = response.getOutputStream();
workbook.write(os);
return os;
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
/*----------創建excel styles----------------------------------*/
private static Map<String, HSSFCellStyle> createStyles(HSSFWorkbook wb) {
Map<String, HSSFCellStyle> styles = new HashMap<String, HSSFCellStyle>();
//----------------------標題樣式---------------------------
HSSFCellStyle cell_header_title = wb.createCellStyle();
HSSFFont font_header_title = wb.createFont();
font_header_title.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗體
font_header_title.setFontHeight((short) (9 * 20));
font_header_title.setFontName("Times New Roman");// 字體樣式
cell_header_title.setFont(font_header_title);
cell_header_title.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中
cell_header_title.setWrapText(true);
styles.put("cell_header_title", cell_header_title);
//-----------------------設置字符樣式---------------------------
HSSFCellStyle cell_data_default = wb.createCellStyle();
HSSFFont font_data_default = wb.createFont();
font_data_default.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
font_data_default.setFontHeight((short) (8 * 20));
font_data_default.setFontName("Arial Narrow");// 字體樣式
cell_data_default.setFont(font_data_default);
cell_data_default.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 居中
cell_data_default.setWrapText(true);// 自動換行
styles.put("cell_data_default", cell_data_default);
return styles;
}
}