导出表格controller
@RequestMapping(value = "/downloadTemplate")
public String downloadTemplate(HttpServletRequest request, HttpServletResponse response) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException, IntrospectionException, IOException {
//查询需要导出的数据
String[] columnArr = {"*姓名", "*学号", "手机号", "身份证号", "性别"};
//导出题目Excel创建HSSFWorkbook
XSSFWorkbook wb = userBaseInfoFromExcelService.createTemplateHSSFWorkbook(columnArr);
// 将文件保存到指定位置
response.setContentType("APPLICATION/OCTET-STREAM");
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
String titleStr = "模板" + dateFormat.format(new Date());
String title = new String(titleStr.getBytes("gb2312"),"iso-8859-1");
response.setHeader( "Content-Disposition" ,"attachment;filename=\"" + title + ".xlsx" + "\"" );
OutputStream ouputStream = response.getOutputStream();
wb.write(ouputStream);
ouputStream.flush();
ouputStream.close();
return null;
}
导出表格实现类
@Override
public XSSFWorkbook createTemplateHSSFWorkbook(String[] columnArr) throws IntrospectionException,
IllegalArgumentException, IllegalAccessException,
InvocationTargetException {
// 第一步,创建一个webbook,对应一个Excel文件
XSSFWorkbook wb = new XSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
XSSFSheet sheet = wb.createSheet("模板");
//设置cell格式为文本格式
XSSFCellStyle cellStyle = wb.createCellStyle();
XSSFDataFormat dataFormat = wb.createDataFormat();
cellStyle.setDataFormat(dataFormat.getFormat("@"));
// 设置列宽
for (int i = 0,length = columnArr.length; i < length; i++) {
sheet.setColumnWidth(i, 6000);
//设置当前sheet格式为文本格式
sheet.setDefaultColumnStyle(i,cellStyle);
}
// 设置字体大小
XSSFFont font = wb.createFont();
font.setFontHeightInPoints((short) 14);
font.setBold(true);
//font.setFontHeightInPoints((short) 12);
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
XSSFRow row = sheet.createRow((int) 0);
row.setHeight((short) 400);
// 第四步,创建单元格,并设置值表头 设置表头居中
XSSFCellStyle style = wb.createCellStyle();
// 设置字体样式
style.setFont(font);
// 设置表头居中
style.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
XSSFCell cell;
for (int i = 0,length = columnArr.length; i < length; i++) {
cell = row.createCell((short) i);
cell.setCellValue(columnArr[i]);
cell.setCellStyle(style);
}
return wb;
}