Controller
@PostMapping("/export")
public void export(HttpServletResponse response, User user){
List<Map<String, Object>> resMap = userMapper.list(user);
ExcelUtilT utilT = new ExcelUtilT();
utilT.createExcel(response, resMap,"", "");
}
Utils工具类
public class ExcelUtilT {
private static ExcelUtilT instance = new ExcelUtilT();
public ExcelUtilT() {
}
public static ExcelUtilT getInstance() {
return instance;
}
public void createExcel(HttpServletResponse response, List<Map<String, Object>> mapList, String filename, String title) {
Map<String, Object> map = mapList.get(0);
Set<String> stringSet = map.keySet();
List<String> headList = new ArrayList<>(stringSet);
Collections.sort(headList);
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet();
XSSFCellStyle headWriteCellStyle = getTitleStyle(wb);
setTitle(sheet, headList, headWriteCellStyle);
XSSFCellStyle contentWriteCellStyle = getContentStyle(wb);
setContent(sheet, headList, mapList, contentWriteCellStyle);
export(response, wb, filename);
}
public void setTitle(XSSFSheet sheet, List<String> headList, XSSFCellStyle headWriteCellStyle) {
XSSFRow row = sheet.createRow(0);
sheet.autoSizeColumn(0);
for (int i = 0; i < headList.size(); i++) {
XSSFCell cell = row.createCell(i);
cell.setCellValue(headList.get(i));
cell.setCellStyle(headWriteCellStyle);
}
}
public XSSFCellStyle getTitleStyle(XSSFWorkbook wb) {
XSSFCellStyle headWriteCellStyle = wb.createCellStyle();
headWriteCellStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(128, 128, 128)));
headWriteCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
headWriteCellStyle.setAlignment(HorizontalAlignment.CENTER);
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
XSSFFont headWriteFont = wb.createFont();
headWriteFont.setFontName("Arial");
headWriteFont.setFontHeightInPoints((short) 10);
headWriteFont.setBold(true);
headWriteFont.setColor(IndexedColors.WHITE.getIndex());
headWriteCellStyle.setFont(headWriteFont);
return headWriteCellStyle;
}
public void setContent(XSSFSheet sheet, List<String> headList, List<Map<String, Object>> mapList, XSSFCellStyle contentWriteCellStyle) {
XSSFRow rows;
XSSFCell cells;
for (int i = 0; i < mapList.size(); i++) {
rows = sheet.createRow(i + 1);
sheet.autoSizeColumn(i + 1);
for (int j = 0; j < headList.size(); j++) {
String value = mapList.get(i).get(headList.get(j)).toString();
cells = rows.createCell(j);
cells.setCellValue(value);
cells.setCellStyle(contentWriteCellStyle);
}
}
}
public XSSFCellStyle getContentStyle(XSSFWorkbook wb) {
XSSFCellStyle contentWriteCellStyle = wb.createCellStyle();
contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
contentWriteCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
contentWriteCellStyle.setAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
XSSFFont headWriteFont = wb.createFont();
headWriteFont.setFontName("Arial");
headWriteFont.setFontHeightInPoints((short) 10);
contentWriteCellStyle.setFont(headWriteFont);
return contentWriteCellStyle;
}
public void export(HttpServletResponse response, XSSFWorkbook wb, String filename) {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
try {
wb.write(response.getOutputStream());
} catch (Exception e) {
System.out.printf("导出Excel异常{}", e.getMessage());
} finally {
IOUtils.closeQuietly(wb);
}
}
}
前端调用
handleExport(){
this.download('/xxx/export', {
...this.queryParams
}, `user_${new Date().getTime()}.xlsx`)
},