XSSFWorkbook
pom文件
<!--Excel的导出-->
<!--xls-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!--xlsx-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<!--日期格式化工具-->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.1</version>
</dependency>
Controller
@ApiOperation("导出excel")
@RequestMapping(value = "/excel", method = RequestMethod.POST,produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)
public void excel(HttpServletResponse response, @RequestBody List<CustomersForm> list) {
try {
customersService.exportExcel(response, list);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
注意:1.如果从浏览器下载的文件报错记得加这个
produces = MediaType.APPLICATION_OCTET_STREAM_VALUE
2.List<CustomersForm> list这个是我们需求 需要前端传的数据不用读数据库
要读数据库的话你也可以把集合查出来不用传这个值
*/
service
@Override
public void exportExcel(HttpServletResponse response, List<CustomersForm> list) throws IOException {
//创建一个webbook,对应一个Excel文件夹
XSSFWorkbook workbook = new XSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
XSSFSheet sheet = workbook.createSheet("用户实体");
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
XSSFRow row = sheet.createRow(0);
String fileName = new String(new DateTime().toString("yyyyMMddHHmm").getBytes("gbk"), "iso-8859-1");
// 创建一个居中格式
XSSFCellStyle style = workbook.createCellStyle();
// 创建一个居中格式
style.setAlignment(HorizontalAlignment.CENTER);
int rowNum = 1;
//设置每列标题
String[] headers = {"用户ID", "用户账号", "会员等级", "认证状态", "消费金额", "订单数量", "用户启用状态",
"公司名称", "省", "市", "县", "详细地址", "联系人", "联系号码", "职位", "qq", "微信", "邮箱", "信用代码"};
//在excel表中添加表头
for (int i = 0; i < headers.length; i++) {
XSSFCell cell = row.createCell(i);
XSSFRichTextString text = new XSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
//在表中存放查询到的数据放入对应的列
for (CustomersForm customersForm: list ) {
XSSFRow row1 = sheet.createRow(rowNum);
row1.createCell(0).setCellValue(customersForm.getId()); //设置单元格内容
row1.createCell(1).setCellValue(customersForm.getMobile());
row1.createCell(2).setCellValue(customersForm.getLevel());
row1.createCell(3).setCellValue(customersForm.getState());
row1.createCell(4).setCellValue(customersForm.getPay());
row1.createCell(5).setCellValue(customersForm.getNum());
row1.createCell(6).setCellValue(customersForm.getActive());
row1.createCell(7).setCellValue(customersForm.getNameCompany());
row1.createCell(8).setCellValue(customersForm.getProvince());
row1.createCell(9).setCellValue(customersForm.getCity());
row1.createCell(10).setCellValue(customersForm.getCounty());
row1.createCell(11).setCellValue(customersForm.getDetailsAddress());
row1.createCell(12).setCellValue(customersForm.getTheContact());
row1.createCell(13).setCellValue(customersForm.getTheNumber());
row1.createCell(14).setCellValue(customersForm.getJob());
row1.createCell(15).setCellValue(customersForm.getQq());
row1.createCell(16).setCellValue(customersForm.getWechat());
row1.createCell(17).setCellValue(customersForm.getEmail());
row1.createCell(18).setCellValue(customersForm.getCreditCode());
rowNum++;
}
// //6、写入本地
// FileOutputStream outputStream = new FileOutputStream(fileName+".xlsx");
// workbook.write(outputStream);
// Long end = System.currentTimeMillis();
// //7、关闭流
// outputStream.close();
// 设置文件MIME类型
response.reset(); // 必要地清除response中的缓存信息
response.setContentType(request.getServletContext().getMimeType(fileName));
// response.setContentType("application/octet-stream");
response.setContentType("application/msexcel;charset=utf-8");
response.setHeader("name", fileName);
response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
response.setHeader("Pragma", "public");
response.setDateHeader("Expires", 0);
response.setHeader("Access-Control-Allow-Origin", "*");
response.setHeader("Access-Control-Allow-Methods", "POST, GET, OPTIONS, DELETE");
response.setHeader("Content-disposition",
"attachment; filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\""+".xlsx");
OutputStream osOut = response.getOutputStream();
workbook.write(osOut);
osOut.flush();
osOut.close();
}