Controller层
@GetMapping("/export") public void selectElderFeeDetailExport(HttpServletResponse response, ViewRepoQuery viewRepoQuery){ // 获取登录者所选择的门店信息 if (viewRepoQuery == null) { viewRepoQuery = new ViewRepoQuery(); viewRepoQuery = new ViewRepoQuery(); viewRepoQuery = new ViewRepoQuery(); } if (viewRepoQuery.getPageNum() == null) { viewRepoQuery.setPageNum(1); } if (viewRepoQuery.getPageSize() == null) { viewRepoQuery.setPageSize(10); } // 调用业务逻辑接口处理查询业务 PageInfo<ViewRepoElderFee> res = repoService.selectElderFeeDetail(viewRepoQuery); List<ViewRepoElderFee> resList = res.getList(); ExcelData data = new ExcelData(); data.setName("费用明细数据"); List<String> titles = new ArrayList(); titles.add("区域"); titles.add("门店"); titles.add("老人"); data.setTitles(titles); List<List<Object>> rows = new ArrayList(); for (ViewRepoElderFee o : resList) { List<Object> row = new ArrayList(); row.add(o.getRegionName()); row.add(o.getSiteName()); row.add(o.getElderName()); rows.add(row); } data.setRows(rows); try { ExportExcelUtils.exportExcel(response,data.getName() + ".xlsx",data); } catch (Exception e) { e.printStackTrace(); } // 返回查询结果 }
工具类
package com.hxf.application.util.excel; import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.util.AreaReference; import org.apache.poi.ss.util.CellRangeAddressList; import org.apache.poi.xssf.usermodel.*; import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder; import javax.servlet.http.HttpServletResponse; import java.awt.*; import java.awt.Color; import java.io.OutputStream; import java.net.URLEncoder; import java.util.List; public class ExportExcelUtils { public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data) throws Exception { // 告诉浏览器用什么软件可以打开此文件 response.setHeader("content-Type", "application/vnd.ms-excel"); // 下载文件的默认名称 response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(fileName, "utf-8")); exportExcel(data, response.getOutputStream()); } public static void exportExcel(ExcelData data, OutputStream out) throws Exception { XSSFWorkbook wb = new XSSFWorkbook(); try { String sheetName = data.getName(); if (null == sheetName) { sheetName = "Sheet1"; } XSSFSheet sheet = wb.createSheet(sheetName); // sheet.addValidationData(setBoxs(sheet)); writeExcel(wb, sheet, data); wb.write(out); } finally { wb.close(); } } private static void writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) { int rowIndex = 0; rowIndex = writeTitlesToExcel(wb, sheet, data.getTitles()); writeRowsToExcel(wb, sheet, data.getRows(), rowIndex); autoSizeColumns(sheet, data.getTitles().size() + 1); } private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles) { int rowIndex = 0; int colIndex = 0; Font titleFont = wb.createFont(); titleFont.setFontName("simsun"); titleFont.setBold(true); // titleFont.setFontHeightInPoints((short) 14); titleFont.setColor(IndexedColors.BLACK.index); XSSFCellStyle titleStyle = wb.createCellStyle(); titleStyle.setAlignment(HorizontalAlignment.CENTER); titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192))); titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); titleStyle.setFont(titleFont); setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0))); Row titleRow = sheet.createRow(rowIndex); // titleRow.setHeightInPoints(25); colIndex = 0; for (String field : titles) { Cell cell = titleRow.createCell(colIndex); cell.setCellValue(field); cell.setCellStyle(titleStyle); colIndex++; } rowIndex++; return rowIndex; } private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex) { int colIndex = 0; Font dataFont = wb.createFont(); dataFont.setFontName("simsun"); // dataFont.setFontHeightInPoints((short) 14); dataFont.setColor(IndexedColors.BLACK.index); XSSFCellStyle dataStyle = wb.createCellStyle(); dataStyle.setAlignment(HorizontalAlignment.CENTER); dataStyle.setVerticalAlignment(VerticalAlignment.CENTER); dataStyle.setFont(dataFont); setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0))); for (List<Object> rowData : rows) { Row dataRow = sheet.createRow(rowIndex); // dataRow.setHeightInPoints(25); colIndex = 0; for (Object cellData : rowData) { Cell cell = dataRow.createCell(colIndex); if (cellData != null) { cell.setCellValue(cellData.toString()); } else { cell.setCellValue(""); } cell.setCellStyle(dataStyle); colIndex++; } rowIndex++; } return rowIndex; } private static void autoSizeColumns(Sheet sheet, int columnNumber) { for (int i = 0; i < columnNumber; i++) { int orgWidth = sheet.getColumnWidth(i); sheet.autoSizeColumn(i, true); int newWidth = (int) (sheet.getColumnWidth(i) + 100); if (newWidth > orgWidth) { sheet.setColumnWidth(i, newWidth); } else { sheet.setColumnWidth(i, orgWidth); } } } private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) { style.setBorderTop(border); style.setBorderLeft(border); style.setBorderRight(border); style.setBorderBottom(border); style.setBorderColor(XSSFCellBorder.BorderSide.TOP, color); style.setBorderColor(XSSFCellBorder.BorderSide.LEFT, color); style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, color); style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, color); } public static DataValidation setBoxs(Sheet sheet) { DataValidationHelper helper = sheet.getDataValidationHelper(); System.out.println(sheet.getPhysicalNumberOfRows()); CellRangeAddressList addressList = new CellRangeAddressList(0, 10, 1, 1); final String[] DATA_LIST = new String[] { "男", "女", }; DataValidationConstraint constraint = helper.createExplicitListConstraint(DATA_LIST); DataValidation dataValidation = helper.createValidation(constraint, addressList); //处理Excel兼容性问题 if(dataValidation instanceof XSSFDataValidation) { dataValidation.setSuppressDropDownArrow(true); dataValidation.setShowErrorBox(true); }else { dataValidation.setSuppressDropDownArrow(false); } return dataValidation; } }
实体类
package com.hxf.application.util.excel; import lombok.Data; import java.io.Serializable; import java.util.List; /** * Excel 数据内容 * @author Alex-2713 */ @Data public class ExcelData implements Serializable { // 表头 private List<String> titles; // 数据 private List<List<Object>> rows; // 页签名称 private String name; }