效果图
目录
效果图
一、工具类ExcelUtil2【ExcelUtil2不会重名若依自己的ExcelUtil】
二、使用示例
controller
service
serviceImpl
一、工具类ExcelUtil2【ExcelUtil2不会重名若依自己的ExcelUtil】
import com.ruoyi.common.annotation.Excel;
import com.ruoyi.common.core.text.Convert;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.*;public class ExcelUtil2<T> {private static final Logger log = LoggerFactory.getLogger(ExcelUtil2.class);public Class<T> clazz;private String sheetName;private Workbook workbook;private Sheet sheet;private Map<String, CellStyle> styles;private List<T> list;private List<Object[]> fields;public ExcelUtil2(Class<T> clazz) {this.clazz = clazz;}/*** 导出 Excel 并返回 Workbook 对象** @param list 数据列表* @param sheetName 工作表名称* @return Workbook 对象*/public Workbook exportExcel(List<T> list, String sheetName) {this.sheetName = sheetName;this.list = list;init();writeSheet();return workbook;}/*** 导出 Excel 并写入响应流** @param response 响应对象* @param list 数据列表* @param sheetName 工作表名称*/public void exportExcel(HttpServletResponse response, List<T> list, String sheetName) {Workbook workbook = exportExcel(list, sheetName);try {response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");String fileName = StringUtils.format("{}_{}.xlsx", sheetName, DateUtils.dateTimeNow());response.setHeader("Content-Disposition", "attachment;filename=" + fileName);OutputStream os = response.getOutputStream();workbook.write(os);os.flush();os.close();} catch (IOException e) {log.error("导出Excel异常{}", e.getMessage());}}private void init() {this.fields = getFields();this.workbook = new XSSFWorkbook();this.styles = createStyles(workbook);this.sheet = workbook.createSheet(sheetName);}private void writeSheet() {writeHeader();writeData();}private void writeHeader() {Row row = sheet.createRow(0);int col = 0;for (Object[] field : fields) {String title = (String) field[1];Cell cell = row.createCell(col++);cell.setCellValue(title);cell.setCellStyle(styles.get("header"));}}private void writeData() {int rowNum = 1;for (T item : list) {Row row = sheet.createRow(rowNum++);int col = 0;for (Object[] field : fields) {String fieldName = (String) field[0];try {String getterMethodName = "get" + StringUtils.capitalize(fieldName);Method method = clazz.getMethod(getterMethodName);Object value = method.invoke(item);Cell cell = row.createCell(col++);setCellValue(cell, value);} catch (Exception e) {log.error("反射调用方法失败: {}", e.getMessage());}}}}private void setCellValue(Cell cell, Object value) {if (value instanceof Integer) {cell.setCellValue((Integer) value);} else if (value instanceof Long) {cell.setCellValue((Long) value);} else if (value instanceof Double) {cell.setCellValue((Double) value);} else if (value instanceof BigDecimal) {cell.setCellValue(((BigDecimal) value).doubleValue());} else if (value instanceof Date) {cell.setCellValue(DateUtils.parseDateToStr("yyyy-MM-dd", (Date) value));} else {cell.setCellValue(Convert.toStr(value));}}private List<Object[]> getFields() {List<Object[]> fields = new ArrayList<>();for (Field field : clazz.getDeclaredFields()) {Excel excel = field.getAnnotation(Excel.class);if (excel != null) {fields.add(new Object[]{field.getName(), excel.name()});}}return fields;}private Map<String, CellStyle> createStyles(Workbook wb) {Map<String, CellStyle> styles = new HashMap<>();CellStyle headerStyle = wb.createCellStyle();headerStyle.setAlignment(HorizontalAlignment.CENTER);headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);Font headerFont = wb.createFont();headerFont.setBold(true);headerStyle.setFont(headerFont);styles.put("header", headerStyle);return styles;}/*** 设置下拉框数据验证** @param sheet 工作表* @param validationHelper 数据验证助手* @param dataSource 下拉框数据源* @param columnIndex 列索引* @param firstRow 起始行（从0开始）* @param lastRow 结束行*/public static void setDropdownValidation(Sheet sheet, DataValidationHelper validationHelper, List<String> dataSource, int columnIndex, int firstRow, int lastRow) {// 创建下拉框的数据源DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(dataSource.toArray(new String[0]));// 设置下拉框的范围CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, columnIndex, columnIndex);// 创建数据验证DataValidation validation = validationHelper.createValidation(constraint, addressList);// 添加数据验证到工作表sheet.addValidationData(validation);}}二、使用示例
controller
/*** 导出数据模版*/@PreAuthorize("@ss.hasPermi('project:test:query')")@Log(title = "测试数据", businessType = BusinessType.EXPORT)@PostMapping("/exportTemplate")public void exportTemplate(HttpServletResponse response) throws Exception {自己的Service.exportTemplate(response);}service
/*** 导出数据模版*/void exportTemplate(HttpServletResponse response) throws IOException;serviceImpl
public void exportTemplate(HttpServletResponse response) throws Exception {// 1. 获取所有下拉列表List<String> list1 = 获取列表1方法();List<String> list2 = 获取列表2方法();// 2. 创建一个空的列表，用于导出模板List<SysRencaiVo3> list = new ArrayList<>();// 3. 创建 ExcelUtil2 实例ExcelUtil2<SysRencaiVo3> util = new ExcelUtil2<>(SysRencaiVo3.class);// 4. 导出 Excel 并获取工作簿对象Workbook workbook = util.exportExcel(list, "数据模版");// 5. 获取第一个工作表Sheet sheet = workbook.getSheetAt(0);// 6. 设置标题列宽为 15// 假设有 2列for (int i = 0; i < 2; i++) { // 列宽单位为 1/256 字符宽度sheet.setColumnWidth(i, 12 * 256); }// 7. 设置标题样式（黑体、背景色、字体颜色、加粗、水平垂直居中、自动换行）// 获取标题行Row headerRow = sheet.getRow(0); CellStyle headerStyle = workbook.createCellStyle();// 水平居中headerStyle.setAlignment(HorizontalAlignment.CENTER); // 垂直居中headerStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 自动换行headerStyle.setWrapText(true);// 设置背景色为 #7E7E7E（灰色）headerStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());// 填充模式为纯色headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 设置字体（黑体、白色、加粗）Font headerFont = workbook.createFont();// 字体为黑体headerFont.setFontName("黑体"); // 字体颜色为白色headerFont.setColor(IndexedColors.WHITE.getIndex());// 字体加粗headerFont.setBold(true); headerStyle.setFont(headerFont);// 设置上下左右边外边框headerStyle.setBorderTop(BorderStyle.THIN);headerStyle.setBorderBottom(BorderStyle.THIN); headerStyle.setBorderLeft(BorderStyle.THIN); headerStyle.setBorderRight(BorderStyle.THIN); // 上下左右外边框颜色headerStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); headerStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); headerStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());headerStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());// 应用样式到标题行for (Cell cell : headerRow) {cell.setCellStyle(headerStyle);}// 8. 设置数据验证（下拉框）DataValidationHelper validationHelper = sheet.getDataValidationHelper();// 8.1 设置“姓名”下拉框，第1列（索引0），从第2行到第3000行ExcelUtil2.setDropdownValidation(sheet, validationHelper, list1, 0, 1, 3000); // 8.2 设置“学校”下拉框，第2列（索引1），从第2行到第3000行ExcelUtil2.setDropdownValidation(sheet, validationHelper, list2, 1, 1, 3000); // 9. 输出 Excel 文件response.setContentType("application/vnd.ms-excel");response.setHeader("Content-Disposition", "attachment;filename=template.xlsx");workbook.write(response.getOutputStream());workbook.close();}