导出Excel功能

工作中需要做一个导出Excel功能，这里记录一下：

controller：

/**
* 导出EXCEL
* @param page
* @param request
* @param response
* @throws Exception
*/
@RequestMapping(value = {"/ledger_fdi_reinvest_rpt_load"}, method = RequestMethod.POST)
@ResponseBody
public void downLoadExcel(Page<LedgerFdiReinvestRptTab> page, HttpServletRequest request, HttpServletResponse response) throws Exception {

    page.setPageSize(Integer.MAX_VALUE);
page.setPageNo(1);

QueryFilter queryFilter = new QueryFilter();//级联查询目前只支持EQFilter 一级级联

String innerCorpName=request.getParameter("innerCorpName");
if(StringUtils.isNotEmpty(innerCorpName)) {
queryFilter.setLikeFilter("innerCorpName", innerCorpName);
}
String outCorpName=request.getParameter("outCorpName");
if(StringUtils.isNotEmpty(outCorpName)) {
queryFilter.setLikeFilter("outCorpName", outCorpName);
}
String outCnyCde=request.getParameter("outCnyCde");
if(StringUtils.isNotEmpty(outCnyCde)) {
queryFilter.setEQFilter("cnyCde.cnyCde", outCnyCde);
}
String vocCatCde=request.getParameter("vocCatCde");
if(StringUtils.isNotEmpty(vocCatCde)) {
queryFilter.setEQFilter("vocCde.vocCatCde.vocCde", vocCatCde);
}
String outVocCde=request.getParameter("outVocCde");
if(StringUtils.isNotEmpty(outVocCde)) {
queryFilter.setEQFilter("vocCde.vocCde", outVocCde);
}
String rptYear=request.getParameter("rptYear");
if(StringUtils.isNotEmpty(rptYear)) {
queryFilter.setEQFilter("rptYear", rptYear);
}

page.setOrder("desc");
page.setOrderBy("rptYear");
Page<LedgerFdiReinvestRptTab> pages = this.commonService.findSupportCascade(page, queryFilter.getPropertyFilters(),LedgerFdiReinvestRptTab.class);
getdownLoadDatas(pages.getResult(), request, response);
}

private void getdownLoadDatas(List<LedgerFdiReinvestRptTab> fdiYear, HttpServletRequest request, HttpServletResponse response) {
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
for (int i = 0; i < fdiYear.size(); i++) {
Map<String, Object> map = new HashMap<String, Object>();
map.put("rptYear", fdiYear.get(i).getRptYear());
map.put("innerCorpName", fdiYear.get(i).getInnerCorpName());
map.put("finalCorpName", fdiYear.get(i).getFinalCorpName());
map.put("finalCde", fdiYear.get(i).getFinalCde()==null?"":fdiYear.get(i).getFinalCde().getCnyCNa());
map.put("finalVocCdeCat", fdiYear.get(i).getFinalVocCde()==null?"":fdiYear.get(i).getFinalVocCde().getVocCatCde().getVocCNa());
map.put("zfcgbl", fdiYear.get(i).getZfcgbl());
map.put("dqztzje", fdiYear.get(i).getDqztzje());
map.put("qmljztzje", fdiYear.get(i).getQmljztzje());
map.put("outCorpName", fdiYear.get(i).getOutCorpName());
map.put("outCnyCde", fdiYear.get(i).getOutCnyCde()==null?"":fdiYear.get(i).getOutCnyCde().getCnyCNa());
map.put("outVocCdeCat", fdiYear.get(i).getOutVocCde()==null?"":fdiYear.get(i).getOutVocCde().getVocCatCde().getVocCNa());
// map.put("vocCde",                     fdiYear.get(i).getVocCde()==null?"":fdiYear.get(i).getVocCde().getVocCNa());

 list.add(map);
}

String fileName = "年度再投资统计.xls";
String[] headName =new String[]{"序号", "报告年度", "境内投资主体名称", "最终投资项目或企业名称", "最终投资项目或企业国别", "最终投资行业", "中方持股比例", "当期再投资净额", "期末累计再投资净额","直接投资境外企业名称","境外企业所属国家","境外企业所属行业"};
String[] headCode=new String[]{"rptYear","finalCorpName", "finalCorpName", "finalCde", "finalVocCdeCat", "zfcgbl", "dqztzje","qmljztzje","outCorpName","outCnyCde","outVocCdeCat"};
ExcelUtil.exportExcel("sheet1", "", 12, headName , list, list.size(),headCode, fileName, null, "", false, response, request);

}

这里有一个工具类ExcelUtil.java 类：

//1、创建一个webbook，对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
//2、在webbook中添加一个sheet,对应Excel文件中的sheet
Sheet sheet = wb.createSheet(sheetName);
//ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); //纸张
if (landscape) {
//打印设置
PrintSetup ps = sheet.getPrintSetup();
//打印方向，true:横向，false:纵向
ps.setLandscape(landscape);
//如果是纵向打印，则不必设置页边距（设置了很难看）
//页边距（下）
sheet.setMargin(Sheet.BottomMargin, (double) 0.3);
//页边距（左）
sheet.setMargin(Sheet.LeftMargin, (double) 0.3);
//页边距（右）
sheet.setMargin(Sheet.RightMargin, (double) 0.3);
//页边距（上）
sheet.setMargin(Sheet.TopMargin, (double) 0.5);
}
//sheet.setHorizontallyCenter(true); //设置打印页面为水平居中
//3、在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
//设置表格默认列宽度为15个字节
//sheet.setDefaultColumnWidth(20);
//sheet.setDefaultRowHeightInPoints((short)(2*256));
//设置列宽
if ("lxmx".equals(columnType)) {
setColumnWidthForLxmx(sheet);
} else if ("dbtj".equals(columnType)) {
setColumnWidthForDbtj(sheet);
} else if ("lfmx".equals(columnType)) {
setColumnWidthForLfmx(sheet);
}

//title样式
HSSFCellStyle styleForTitle = wb.createCellStyle();
//创建一个居中格式
styleForTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 设置这些样式
/*style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);*/
styleForTitle.setFillForegroundColor(HSSFColor.WHITE.index);
styleForTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
styleForTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体 
Font fontForTitle = wb.createFont(); 
/* font.setColor(HSSFColor.VIOLET.index); */
fontForTitle.setColor(HSSFColor.BLACK.index);
fontForTitle.setFontHeightInPoints((short) 16);
fontForTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式 
styleForTitle.setFont(fontForTitle);

//Head样式
HSSFCellStyle styleForHead = wb.createCellStyle();
//创建一个居中格式
styleForHead.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 设置这些样式
/*style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);*/
styleForHead.setFillForegroundColor(HSSFColor.WHITE.index);
styleForHead.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
styleForHead.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleForHead.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleForHead.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleForHead.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleForHead.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styleForHead.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成一个字体 
Font fontForHead = wb.createFont(); 
/* font.setColor(HSSFColor.VIOLET.index); */
fontForHead.setColor(HSSFColor.BLACK.index);
fontForHead.setFontHeightInPoints((short) 12);
fontForHead.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式 
styleForHead.setFont(fontForHead);

//Content样式
// 生成并设置另一个样式 
HSSFCellStyle styleForContent = wb.createCellStyle(); 
/*style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);*/
styleForContent.setFillForegroundColor(HSSFColor.WHITE.index);
styleForContent.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
styleForContent.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleForContent.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleForContent.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleForContent.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleForContent.setAlignment(HSSFCellStyle.ALIGN_LEFT);
styleForContent.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
styleForContent.setWrapText(true);
// 生成另一个字体 
Font fontForContent = wb.createFont();
fontForContent.setFontHeightInPoints((short) 11);
fontForContent.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
// 把字体应用到当前的样式 
styleForContent.setFont(fontForContent);

Row row = sheet.createRow(0);
for (int i = 0; i < headNum; i++) {
row.createCell(i);
}
//合并第一行
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headNum - 1));
//设置合并单元格边框
RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, new CellRangeAddress(0, 0, 0, headNum - 1), sheet, wb);
Cell cellMerged = row.getCell(0);
cellMerged.setCellStyle(styleForTitle);
cellMerged.setCellType(HSSFCell.CELL_TYPE_STRING);
//设置总标题
cellMerged.setCellValue(title);

//4、创建单元格，并设置值表头 设置表头居中

row = sheet.createRow(1);
//单元格
Cell cell;
for (int k = 0; k < headNum && k < head.length; k++) {
cell = row.createCell(k);
cell.setCellValue(head[k]);
cell.setCellStyle(styleForHead);
}
//5、遍历集合数据，产生数据行 ，实际应用中这些数据从数据库得到，
for (int i = 0; i < listSize; i++) {
if (list.get(i) != null) {
Cell ce;
row = sheet.createRow(i + 2);
Map<String, Object> hmTemp = list.get(i);
String data = "";
//第四步，创建单元格，并设置值
//第一个单元格设置序号
ce = row.createCell(0);
ce.setCellValue(i + 1);
ce.setCellStyle(styleForContent);

for (int j = 0; j < headNum - 1; j++) {
data = hmTemp.get(dataName[j]) == null ? "" : hmTemp.get(dataName[j]).toString();
ce = row.createCell(j + 1);
ce.setCellValue(data);
ce.setCellStyle(styleForContent);
}
}
}

//第六步，导出文件
try {
String agent = request.getHeader("USER-AGENT");
if (null != agent && -1 != agent.indexOf("MSIE")) {
String encodeName = URLEncoder.encode(fileName, "UTF-8");
encodeName = encodeName.replace("+", "%20");//处理空格，空格会被编码成+，转化为%20

if (-1 != agent.indexOf("MSIE 6.0") && encodeName.length() > 150) {//IE6下最长支持150个字符左右，一个汉字占9位编码，最多17个汉字
//这个方式在IE8下 字符集在gb2312内的是可以的，IE6又不支持，那IE6下面超过15个汉字就只能被自动截断了...
//fileName=new String(fileName.getBytes("gb2312"),"ISO8859-1");//如果文件名里面包含了非gb2312编码的字，比如繁体字，就不行了呀
fileName = encodeName;//如有解决办法，更改此行
} else {
fileName = encodeName;
}
} else if (null != agent && -1 != agent.toLowerCase().indexOf("rv:11")) {
fileName = URLEncoder.encode(fileName, "UTF-8");
} else {
fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
}
// 清空response 
response.reset();
// 设置response的Header 
//response.addHeader("Content-Disposition", "attachment;filename="+ new String(fileName.getBytes())); 
response.addHeader("Content-Disposition", "attachment;filename=\"" + fileName + "\"");//火狐读取文件名时，遇到空格就只取空格前面的，把文件名用双引引起来就好了
//response.addHeader("Content-Length", "" + file.length()); 
OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
wb.write(toClient);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
toClient.flush();
toClient.close();
//wb.close();
} catch (IOException ex) {
ex.printStackTrace();
}

}