首先导包:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
在service层的implment层调用方法:
数据格式为从数据库里得到的已经封装好的数据:
public void exportWithResponse(String sheetName, String fileName, int columnNumber, Object[] newColumnName,
List<HashMap<String, Object>> dataList, HttpServletResponse response) throws IOException {
try (OutputStream out = response.getOutputStream(); XSSFWorkbook wb = new XSSFWorkbook();) {
XSSFSheet sheet = wb.createSheet(sheetName);
XSSFRow row = sheet.createRow(0);
row.setHeightInPoints(37);
// 第四步,创建表头单元格样式 以及表头的字体样式
XSSFCellStyle style = wb.createCellStyle();
setTableHeader(wb, style);
// 第四,一步,创建表头的列
for (int i = 0; i < columnNumber; i++) {
XSSFCell cell = row.createCell(i);
cell.setCellValue((String) newColumnName[i]);
cell.setCellStyle(style);
}
// 第五步,创建单元格,并设置值
for (int i = 0; i < dataList.size(); i++) {
row = sheet.createRow(i + 1);
// 为数据内容设置特点新单元格样式2 自动换行 上下居中左右也居中
XSSFCellStyle zidonghuanhang2 = wb.createCellStyle();
setAutoWrapStyle(zidonghuanhang2);
XSSFCell datacell = null;
for (int j = 0; j < newColumnName.length; j++) {
datacell = row.createCell(j);
if (dataList.get(i).get(newColumnName[j]) == null) {
datacell.setCellValue("");
} else {
datacell.setCellValue(dataList.get(i).get(newColumnName[j]).toString());
}
datacell.setCellStyle(zidonghuanhang2);
}
}
// 第六步,将文件存到浏览器设置的下载位置
String filename = fileName + ".xlsx";
response.setContentType("application/ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition",
"attachment;filename=".concat(String.valueOf(URLEncoder.encode(filename, "UTF-8"))));
wb.write(out);
out.flush();
String str = "导出" + fileName + "成功!";
logger.info(str);
} catch (Exception e) {
logger.error(e.toString());
}
}
private void setTableHeader(Workbook wb, CellStyle style) {
style.setWrapText(true); // 设置自动换行
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER); // 创建一个居中格式
style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
Font headerFont = wb.createFont(); // 创建字体样式
headerFont.setBold(true); // 字体加粗
headerFont.setFontName("黑体"); // 设置字体类型
headerFont.setFontHeightInPoints((short) 10); // 设置字体大小
style.setFont(headerFont); // 为标题样式设置字体样式
}
private void setAutoWrapStyle(CellStyle zidonghuanhang2) {
zidonghuanhang2.setWrapText(true);// 设置自动换行
zidonghuanhang2.setVerticalAlignment(VerticalAlignment.CENTER); // 创建一个上下居中格式
zidonghuanhang2.setAlignment(HorizontalAlignment.CENTER);// 左右居中
// 设置边框
zidonghuanhang2.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
zidonghuanhang2.setBorderBottom(BorderStyle.THIN);
zidonghuanhang2.setBorderLeft(BorderStyle.THIN);
zidonghuanhang2.setBorderRight(BorderStyle.THIN);
zidonghuanhang2.setBorderTop(BorderStyle.THIN);
}
使用如下:
//
SqlDaResult data = sqlHelperService.queryListBySql(customClickHouseJdbcTemplate, sql, params);
//数据库得到的数据
List<HashMap<String, Object>> tableData = (List<HashMap<String, Object>>) data.getTables().get(0);
// 列名
Object[] colName = null;
for (Map<String, Object> item : tableData) {
//把数据库的字段名拿出来
Set<String> strings = item.keySet();
colName = strings.toArray();
break;
}
exportWithResponse("问题区域列表", "问题区域列表_"+ new Date().getTime(), colName.length, colName, tableData, response);
缺点就是这个是自用的数据库数据封装,如有mybatis的查询结果可以依照改着使用