©2020-2024 ioDraw All rights reserved
<>controller
@SneakyThrows @GetMapping("/exportExcel") public void export(
HttpServletResponse response) { List<Student> list = new ArrayList<>();
ExportParams exportParams= new ExportParams(null, null, ExcelType.XSSF);
exportParams.setStyle(SubExcelExportStylerDefaultImpl.class); Workbook workbook
= ExcelExportUtil.exportExcel(exportParams, Student.class, list); CellStyle
textStyle= workbook.createCellStyle(); textStyle.setDataFormat((short)
BuiltinFormats.getBuiltinFormat("TEXT")); int numberOfSheets = workbook.
getNumberOfSheets(); for (int i = 0; i < numberOfSheets; i++) { Sheet sheet =
workbook.getSheetAt(i); short lastCellNum = sheet.getRow(0).getLastCellNum();
for (short j = 0; j < lastCellNum; j++) { sheet.setDefaultColumnStyle(j,
textStyle); } } ExcelUtil.createXssfSelected(workbook, new String[]{"男", "女"}, 2
); ExcelUtil.downloadExcel(response, workbook, "学生表.xlsx"); }
<>utils
SubExcelExportStylerDefaultImpl 类。
public class SubExcelExportStylerDefaultImpl extends AbstractExcelExportStyler
implements IExcelExportStyler { private Workbook workbook; public
SubExcelExportStylerDefaultImpl() { } public SubExcelExportStylerDefaultImpl(
Workbook workbook) { this.workbook = workbook; super.createStyles(workbook); }
@Override public CellStyle getTitleStyle(short color) { Font font = workbook.
createFont(); //设置粗体 font.setBold(true); CellStyle titleStyle = workbook.
createCellStyle(); titleStyle.setFont(font); //下边框 titleStyle.setBorderBottom(
BorderStyle.THIN); //左边框 titleStyle.setBorderLeft(BorderStyle.THIN); //上边框
titleStyle.setBorderTop(BorderStyle.THIN); //右边框 titleStyle.setBorderRight(
BorderStyle.THIN); //水平居中 titleStyle.setAlignment(HorizontalAlignment.CENTER);
//上下居中 titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); //设置自动换行
titleStyle.setWrapText(true); return titleStyle; } @Override public CellStyle
stringSeptailStyle(Workbook workbook, boolean isWarp) { CellStyle style =
workbook.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER); style.setDataFormat(
STRING_FORMAT); if (isWarp) { style.setWrapText(true); } return style; }
@Override public CellStyle getHeaderStyle(short color) { CellStyle titleStyle =
workbook.createCellStyle(); Font font = workbook.createFont(); font.
setFontHeightInPoints((short) 12); titleStyle.setFont(font); titleStyle.
setAlignment(HorizontalAlignment.CENTER); titleStyle.setVerticalAlignment(
VerticalAlignment.CENTER); return titleStyle; } @Override public CellStyle
stringNoneStyle(Workbook workbook, boolean isWarp) { CellStyle style = workbook.
createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.
setVerticalAlignment(VerticalAlignment.CENTER); style.setDataFormat(
STRING_FORMAT); if (isWarp) { style.setWrapText(true); } return style; } }
ExcelUtil 类。
public class ExcelUtil { /** * 下载通用配置 * * @param response HttpServletResponse
* @param workbook Workbook * @param excelName excelName */ public static void
downloadExcel(HttpServletResponse response, Workbook workbook, String excelName)
{ try (OutputStream os = response.getOutputStream()) { response.reset(); if (
excelName== null) { excelName = UUID.randomUUID().toString(); } response.
setHeader("Access-Control-Expose-Headers", "Content-Disposition"); response.
setHeader("Content-Disposition","inline;filename=" + URLEncoder.encode(excelName
, "UTF-8")); workbook.write(os); os.flush(); } catch (IOException e) { e.
printStackTrace(); } } /** * XSSF * excel添加下拉数据校验 * * @param workbook 哪个 sheet
页添加校验 * @param dataSource 数据源数组 * @param col 第几列校验（0开始） * @return */ public
static void createXssfSelected(Workbook workbook, String[] dataSource, int col)
{ Sheet sheet = workbook.getSheetAt(0); CellRangeAddressList
cellRangeAddressList= new CellRangeAddressList(1, 65535, col, col);
DataValidationHelper helper= sheet.getDataValidationHelper();
DataValidationConstraint constraint= helper.createExplicitListConstraint(
dataSource); DataValidation dataValidation = helper.createValidation(constraint,
cellRangeAddressList); //处理Excel兼容性问题 if (dataValidation instanceof
XSSFDataValidation) { dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true); } else { dataValidation.
setSuppressDropDownArrow(false); } dataValidation.setEmptyCellAllowed(true);
dataValidation.setShowPromptBox(true); dataValidation.createPromptBox("提示",
"只能选择下拉框里面的数据"); sheet.addValidationData(dataValidation); } }
<>VO
public class Student { @Excel(name = "名字(必填)") private String clientName;
@Excel(name = "年龄(必填)") private String mobile; @Excel(name = "性别",replace = {
"男_0","女_1"}) private Integer gender; @Excel(name = "证件类型",replace = {"身份证_0",
"护照_1","港澳通行证_2","台胞证_3"}) private Integer clientCertificateType; @Excel(name =
"证件编码") private String clientCertificateCode; }