做项目中需要用到导出excel的功能，记录一下。
1、使用jxl导出。
这个是从网上找的。这个不需要params inputName参数。
- @Action(value = "vsealFileDepts.export1", results = { @Result(name = "success", type = "stream") })
- // , params = {
- // "contentType", "text/html;charset=UTF-8" }
- // ,params={
- // "contentType","application/octet-stream",
- // "inputName","fileInputStream",
- // "contentDisposition","attachment;filename=${fileName}.xls",
- // "bufferSize","1024"
- // }
- public String export1() throws Exception {
- HttpServletResponse response = ServletActionContext.getResponse();
- // 定义request ,response.
- // 查询下载附件.
- // 设置下载头信息.begin
- response.setCharacterEncoding("UTF-8");
- response.setContentType("application/vnd.ms-excel");
- response.setHeader("Content-Disposition", "attachment; filename="
- + new String("用户通讯录.xls".getBytes("GB2312"), "iso8859-1"));
- response.setHeader("Pragma", "No-cache");
- response.setHeader("Cache-Control", "No-cache");
- response.setDateHeader("Expires", 0);
- // 这个地方一定要进行编码的转换要不然中文字符会出现乱码.
- // 设置下载头信息.end,
- OutputStream output = null;
- InputStream fis = null;
- try {
- output = response.getOutputStream();
- jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(output);
- jxl.write.WritableSheet ws = wwb.createSheet("用户通讯录", 0);
- // 设置标题.ws.addCell(new jxl.write.Label(列, 行, 内容.));
- ws.addCell(new Label(0, 0, "编号"));
- ws.addCell(new Label(1, 0, "登录名称"));
- ws.addCell(new Label(2, 0, "联系人"));
- ws.addCell(new Label(3, 0, "电话"));
- ws.addCell(new Label(4, 0, "email"));
- ws.addCell(new Label(5, 0, "单位名称"));
- ws.addCell(new Label(6, 0, "单位邮编"));
- ws.addCell(new Label(7, 0, "单位地址"));
- // 设置显示长度.
- ws.setColumnView(1, 15);
- // 登录名长度
- ws.setColumnView(2, 15);
- ws.setColumnView(3, 15);
- ws.setColumnView(4, 20);
- ws.setColumnView(5, 20);
- ws.setColumnView(6, 20);
- ws.setColumnView(7, 20);
- ws.setColumnView(8, 40);
- int i = 1;
- List list1 = new ArrayList();
- for (int j = 0; j < list1.size(); j++) {
- User user = (User) list1.get(j);
- ws.addCell(new jxl.write.Number(0, i + 1, i));
- // 这里设置是自增的序号而不是ID号.也可以改成ID号.
- // ws.addCell(new jxl.write.Label(1, i + 1, ""
- // + user.getUserId()));
- ws.addCell(new Label(1, i + 1, "" + user.getAddress()));
- // 登录名
- ws.addCell(new Label(2, i + 1, "" + user.getAddress()));
- // 联系人
- ws.addCell(new Label(3, i + 1, "" + user.getPhone()));
- // 联系电话.
- ws.addCell(new Label(4, i + 1, "" + user.getEmail()));
- // email.
- if (null != user.getAddress()) {
- ws.addCell(new Label(5, i + 1, "" + user.getAddress()));
- if (user.getAddress() != null) {
- ws.addCell(new Label(6, i + 1, "" + user.getAddress()));
- } else {
- ws.addCell(new Label(6, i + 1, ""));
- // 增加邮编为""的判断.因为这个是Integer的类型.
- }
- ws.addCell(new Label(7, i + 1, "" + user.getAddress()));
- } else {
- ws.addCell(new Label(5, i + 1, ""));
- ws.addCell(new Label(6, i + 1, ""));
- ws.addCell(new Label(7, i + 1, ""));
- }
- i++;
- }
- wwb.write();
- wwb.close();
- } catch (Exception e) {
- System.out.println("Error!");
- e.printStackTrace();
- } finally {// 正常关闭输入输出流.
- try {
- if (fis != null) {
- fis.close();
- fis = null;
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
- try {
- if (output != null) {
- output.close();
- output = null;
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- return null;
- }
前端jsp页面能调用这个action即可。
2、使用poi导出。
这种方法必须要使用struts2的注解中的params inputName 参数。
首先在action中声明变量，并写明get/set方法
因为是自己项目的代码，所以不便放出这里还是只放出关键代码，poi的生成写的有点乱，因为需要针对特定excel做排版美化的处理，所以就没有单独封装到一个工具类去生成，后面再想想这块。
@Results(value = {
@Result(name = "depositExcel", type = "stream", params = {
"contentType", "application/vnd.ms-excel", "inputName",
"excelFile", "contentDisposition",
"attachment;filename=${filename}.xls", "bufferSize", "1024" }),
@Result(name = "returnExcel", type = "stream", params = { "contentType",
"application/vnd.ms-excel", "contentDisposition",
"attachment;filename=${filename}.xls", "inputName",
"excelFile" }),
@Result(name = "balanceExcel", type = "stream", params = { "contentType",
"application/vnd.ms-excel", "contentDisposition",
"attachment;filename=${filename}.xls", "inputName",
"excelFile" })
})private InputStream excelFile;
public InputStream getExcelFile() {
return excelFile;
}
public void setExcelFile(InputStream excelFile) {
this.excelFile = excelFile;
}
private String filename;
public String getFilename() {
String encodeFileName = null;
try {
encodeFileName = new String(filename.getBytes(),
"ISO8859-1");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
return encodeFileName;
}
public void setFilename(String filename) {
this.filename = filename;
}
/**
* 导出入住明细报表
* @return
* @throws UnsupportedEncodingException
*/
public String exportDepositReport() throws UnsupportedEncodingException{
String inDateStartStr=this.getRequest().getParameter("inDateStart");
String inDateEndStr=this.getRequest().getParameter("inDateEnd");
StayDormQueryVO vo=new StayDormQueryVO();
if(StringUtils.isNotBlank(inDateStartStr)&&StringUtils.isNotBlank(inDateEndStr)){
DateTime inDateStart = new DateTime(inDateStartStr);
DateTime inDateEnd = new DateTime(inDateEndStr);
vo.setInDateStart(inDateStart.getDate());
vo.setInDateEnd(inDateEnd.getDate());
}else{
DateTime inDateEnd = new DateTime();
//减一个月
inDateEnd.plusMonth(-1);
vo.setInDateStart(inDateEnd.getDate());
vo.setInDateEnd(new Date());
}
List<StayDormListVO> dormListVOs=this.stayDormService.findDepositReport(vo);
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("first sheet");
wb.createSheet("second sheet");
sheet.setColumnWidth( (short) 0, (short) ( 4*50*32) );
sheet.setColumnWidth( (short) 1, (short) ( 4*50*32) );
sheet.setColumnWidth( (short) 2, (short) ( 4*50*32) );
sheet.setColumnWidth( (short) 3, (short) ( 4*50*32) );
//字体
HSSFFont fontTitle=wb.createFont();
fontTitle.setFontHeightInPoints((short)14); //字体大小
fontTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //字体粗
HSSFFont fonttext=wb.createFont();
fonttext.setFontHeightInPoints((short)12); //字体大小
// fonttext.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFFont fontTitle2=wb.createFont();
fontTitle2.setFontHeightInPoints((short)13); //教师的字体大小
HSSFFont fontTable=wb.createFont();
fontTable.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //字体粗
fontTable.setFontHeightInPoints((short)14); //字体大小
HSSFFont fontTableClass=wb.createFont();
fontTableClass.setFontHeightInPoints((short)14); //字体大小
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
cellStyle.setWrapText(true);
cellStyle.setFont(fontTitle);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
HSSFCellStyle cellStyle2 = wb.createCellStyle();
cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
cellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
cellStyle2.setWrapText(true);
cellStyle2.setFont(fonttext);
cellStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
HSSFRow rows = sheet.createRow(0);
sheet.addMergedRegion(new CellRangeAddress(
0, //first row (0-based)
0, //last row (0-based)
0, //first column (0-based)
3 //last column (0-based)
));
rows.createCell(0).setCellValue("入住明细统计结果（"+inDateStartStr+" "+inDateEndStr+"）");
rows.getCell(0).setCellStyle(cellStyle);
rows.setHeightInPoints((float) 30);//行高
HSSFRow row = sheet.createRow(1);
row.createCell(0).setCellValue("员工");
row.createCell(1).setCellValue("姓名");
row.createCell(2).setCellValue("押金金额");
row.createCell(3).setCellValue("时间");
row.getCell(0).setCellStyle(cellStyle);
row.getCell(1).setCellStyle(cellStyle);
row.getCell(2).setCellStyle(cellStyle);
row.getCell(3).setCellStyle(cellStyle);
row.setHeightInPoints((float) 30);//行高
double totalDeposit=0.00;
if(dormListVOs!=null){
int j=0;
for(int i=0;i<dormListVOs.size();i++){
dormListVOs.get(i).setInDateStr(dormListVOs.get(i).getInDate());
totalDeposit+=dormListVOs.get(i).getDeposit();
HSSFRow row2 = sheet.createRow(i+2);
row2.createCell(0).setCellValue(dormListVOs.get(i).getUserNo());
row2.createCell(1).setCellValue(dormListVOs.get(i).getName());
row2.createCell(2).setCellValue(dormListVOs.get(i).getDeposit());
row2.createCell(3).setCellValue(dormListVOs.get(i).getInDateStr());
row2.getCell(0).setCellStyle(cellStyle2);
row2.getCell(1).setCellStyle(cellStyle2);
row2.getCell(2).setCellStyle(cellStyle2);
row2.getCell(3).setCellStyle(cellStyle2);
j++;
}
HSSFCellStyle cellStyle3 = wb.createCellStyle();
cellStyle3.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
cellStyle3.setWrapText(true);
cellStyle3.setFont(fontTitle);
cellStyle3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
HSSFRow row3 = sheet.createRow(j+2);
row3.createCell(3).setCellValue("总计："+totalDeposit);
row3.getCell(3).setCellStyle(cellStyle3);
}
ByteArrayOutputStream baos = new ByteArrayOutputStream();
try {
wb.write(baos);
baos.close();
} catch (IOException e) {
e.printStackTrace();
}
this.filename=inDateStartStr+"至"+inDateEndStr+"入住明细统计报表";
excelFile = new ByteArrayInputStream(baos.toByteArray());
return "depositExcel";
}
257
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
