@GetMapping("exportExcel")
public void export(HttpServletResponse response) {
Workbook workbook = null;
OutputStream out = null;
try {
List<ExcelDataVo> dataVOList = new ArrayList<>(2);
ExcelDataVo dataVO = new ExcelDataVo();
dataVO.setName("毛毛");
dataVO.setAge(12);
dataVO.setLocation("河南");
dataVO.setJob("小学生");
ExcelDataVo dataVO2 = new ExcelDataVo();
dataVO2.setName("皮皮");
dataVO2.setAge(11);
dataVO2.setLocation("北京");
dataVO2.setJob("小学生");
dataVOList.add(dataVO);
dataVOList.add(dataVO2);
workbook = ExcelWriter.exportData(dataVOList);
Date date = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddhhmmss");
if (null != workbook) {
String excelName = "事例Excel导出";
String fileName = excelName + sdf.format(date) + ".xlsx";
fileName = new String(fileName.getBytes("UTF-8"), "iso8859-1");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.setContentType("application/x-download");
response.setCharacterEncoding("UTF-8");
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
response.flushBuffer();
out = response.getOutputStream();
workbook.write(out);
out.flush();
out.close();
}
} catch (Exception e) {
logger.info("写入Excel过程出错!错误原因:" + e.getMessage());
} finally {
try {
if (null != workbook) {
workbook.close();
}
if (null != out) {
out.close();
}
} catch (IOException e) {
logger.info("关闭workbook或outputStream出错!");
}
}
}
public class ExcelWriter {
private static List<String> CELL_HEADS;
static {
CELL_HEADS = new ArrayList<>();
CELL_HEADS.add("姓名");
CELL_HEADS.add("年龄");
CELL_HEADS.add("城市");
CELL_HEADS.add("职业");
}
public static Workbook exportData(List<ExcelDataVo> dataList) {
Workbook workbook = new SXSSFWorkbook();
Sheet sheet = buildDataSheet(workbook);
int rowNum = 1;
for (Iterator<ExcelDataVo> it = dataList.iterator(); it.hasNext(); ) {
ExcelDataVo data = it.next();
if (data == null) {
continue;
}
Row row = sheet.createRow(rowNum++);
convertDataToRow(data, row);
}
return workbook;
}
private static Sheet buildDataSheet(Workbook workbook) {
Sheet sheet = workbook.createSheet();
for (int i = 0; i < CELL_HEADS.size(); i++) {
sheet.setColumnWidth(i, 4000);
}
sheet.setDefaultRowHeight((short) 400);
CellStyle cellStyle = buildHeadCellStyle(sheet.getWorkbook());
Row head = sheet.createRow(0);
for (int i = 0; i < CELL_HEADS.size(); i++) {
Cell cell = head.createCell(i);
cell.setCellValue(CELL_HEADS.get(i));
cell.setCellStyle(cellStyle);
}
return sheet;
}
private static CellStyle buildHeadCellStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(BorderStyle.THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Font font = workbook.createFont();
font.setBold(true);
style.setFont(font);
return style;
}
private static void convertDataToRow(ExcelDataVo data, Row row) {
int cellNum = 0;
Cell cell;
cell = row.createCell(cellNum++);
cell.setCellValue(null == data.getName() ? "" : data.getName());
cell = row.createCell(cellNum++);
if (null != data.getAge()) {
cell.setCellValue(data.getAge());
} else {
cell.setCellValue("");
}
cell = row.createCell(cellNum++);
cell.setCellValue(null == data.getLocation() ? "" : data.getLocation());
cell = row.createCell(cellNum++);
cell.setCellValue(null == data.getJob() ? "" : data.getJob());
}
}