<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10-FINAL</version>
</dependency>
/**
* 下载EXCEL模板* @param request
* @param response
*/
@RequestMapping(value = "/exportBlackListExcel")
public void exportXlsPayListExcel(HttpServletRequest request,HttpServletResponse response){
HSSFWorkbook workbook = null;
try {
workbook = this.exportXlsExcelForParm();
createExcelSreamResult(workbook, response);
} catch (Exception e) {
LOGGER.error("黑名单EXCEL模板导出失败", e);
}
}
private void createExcelSreamResult(HSSFWorkbook workbook, HttpServletResponse response) {
try {
bulidExpResponseHead(response);
if(workbook != null){
OutputStream out = response.getOutputStream();
workbook.write(out);//写入输出流
out.flush();
out.close();
}
} catch (Exception e) {
LOGGER.error("将workbook流写入到响应流时发生异常:",e);
}
}
private void bulidExpResponseHead(HttpServletResponse response) {
response.setContentType("application/msexcel");
response.setBufferSize(1024*8);
response.addHeader("Content-Disposition","attachment; filename=黑名单模板.xls");
//确保IE识别本次为下载文件
response.setHeader("Content-Transfer-Encoding","binary");
response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
response.setHeader("Pragma", "public");
}
/**
* 生成EXCEL准备参数
* @return
*/
public HSSFWorkbook exportXlsExcelForParm() {
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
Map<String, String> map = new HashMap<String, String>();
map.put("idNo", "513701198912069823");
map.put("name", "测试");
map.put("enterReason", "测试");
list.add(map);
HSSFWorkbook workbook = new HSSFWorkbook();
try {
workbook = writeDataToExcel("黑名单模板", list,workbook);
} catch (IOException e) {
LOGGER.error("调用构造Excel的SXSSFWorkbook对象发生异常:", e);
}
return workbook;
}
/**
* 生成EXCEL
* @param title
* @param result
* @param workbook
* @return
* @throws IOException
*/
public static HSSFWorkbook writeDataToExcel(String title,List<Map<String, String>> result,HSSFWorkbook workbook) throws IOException {
Sheet s = workbook.createSheet();
CellStyle cs = workbook.createCellStyle();
Font font = workbook.createFont();
font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
cs.setFont(font);
Cell cell = null;
//创建第一行标题
Row row = s.createRow(0);
String arrTitile[] = {"idNo","name","enterReason"};
//设置标题
for(int a = 0 ; a<arrTitile.length;a++){
cell = row.createCell(a);
cell.setCellStyle(cs);
cell.setCellValue(arrTitile[a]);
}
int rowNum = 1;
//设置值
row = s.createRow(rowNum);
for(int i = 0 ;i< result.size();i++){
int cellIndex = 0 ;
//cell = row.createCell(cellIndex++);
//cell.setCellStyle(cs);
//cell.setCellValue(i+1+"");
cell = row.createCell(cellIndex++);
cell.setCellStyle(cs);
if (result.get(i).get("idNo") != null && result.get(i).get("idNo") != "") {
cell.setCellValue(result.get(i).get("idNo") + "");
}
cell = row.createCell(cellIndex++);
cell.setCellStyle(cs);
if (result.get(i).get("name") != null && result.get(i).get("name") != "") {
cell.setCellValue(result.get(i).get("name") + "");
}
cell = row.createCell(cellIndex++);
cell.setCellStyle(cs);
if (result.get(i).get("enterReason") != null && result.get(i).get("enterReason") != "") {
cell.setCellValue(result.get(i).get("enterReason") + "");
}
rowNum++;
row = s.createRow(rowNum);
}
return workbook;
}