在工作中经常会遇到列表数据的导出，每次需要的时候都要去开发一次，且数据不断在变化，于是就有了下述的工具类，可传入各种实体对象的List，最终以指定格式导出excel，废话不多说，上代码~
控制层代码
@PostMapping("/test")public void test(HttpServletResponse response) throws Exception {//查询任意数据列表List<ErrorLog> list = errorLogService.selectListByInfo();//导出操作CommonExcelUtils.exportDynamicsData(response, list, "日志", "日志数据");}此处的list可以是任意数据，也可以是任意SQL组装的list数据，标题会以sql的别名为准.
CommonExcelUtils工具类
/*** 动态列表导出* @param response* @param list 数据list* @param sheetName 页签名称，也是总标题的名称* @param fileName 导出文件名*/@SuppressWarnings("all")public static String exportDynamicsData(HttpServletResponse response,List list, String sheetName, String fileName) throws IOException {//将list数据转成指定类型List<LinkedHashMap<String, Object>> data = CommonBeanUtils.convertListToMap(list);List<List> rows = new ArrayList<>();//excel导出整体数据List<String> titles = new ArrayList<>();//excel导出标题（首行）List<String> title = new ArrayList<>();title.add(sheetName);rows.add(title);//组装标题LinkedHashMap<String,Object> m = (LinkedHashMap<String,Object>) data.get(0);Set<String> keySet = m.keySet();for (String t : keySet) {titles.add(t);}rows.add(titles);//组装数据for (LinkedHashMap<String,Object> info : data) {List d = new ArrayList<>();Set<Entry<String, Object>> entrySet = info.entrySet();for (Entry<String, Object> da : entrySet) {d.add(da.getValue());}rows.add(d);}fileName = fileName +"-"+ DateUtils.parseDateToStr("yyMMdd", new Date()) +".xlsx";//导出文件名称//声明一个工作簿XSSFWorkbook workbook = new XSSFWorkbook();XSSFSheet sheet = workbook.createSheet(sheetName);sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titles.size()-1));//合并第一行的单元格，作标题sheet.setDefaultColumnWidth(14); //设置表格列宽度//导出操作ExcelUtil.exportExcel(response, rows, workbook, sheet, fileName);return fileName;}CommonBeanUtils工具类
/*** List转Map* @param <T>* @param list* @return*/public static <T> List<LinkedHashMap<String, Object>> convertListToMap(List<T> list) {return list.stream().map(CommonBeanUtils::objectToMap).collect(Collectors.toList());}/*** object 转 Map* @param <T>* @param object* @return*/private static <T> LinkedHashMap<String, Object> objectToMap(T object) {LinkedHashMap<String, Object> map = new LinkedHashMap<>();for (Field field : object.getClass().getDeclaredFields()) {field.setAccessible(true);try {map.put(field.getName(), field.get(object));} catch (IllegalAccessException e) {e.printStackTrace();}}return map;}ExcelUtil工具类
import java.io.IOException;
import java.math.BigDecimal;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;public class ExcelUtil<T>
{/*** 自定义导出* @param response* @param excelData* @param sheetName* @param fileName* @param columnWidth* @throws IOException*/public static void exportExcel(HttpServletResponse response,List<List> excelData,XSSFWorkbook workbook, XSSFSheet sheet,String fileName) throws IOException { //设置单元格字体XSSFFont fontName = workbook.createFont();fontName.setFontName("宋体");//写入List<List<String>>中的数据int rowIndex = 0;int rowFlag = -2;XSSFCellStyle createTitleCellStyle = createTitleCellStyle(workbook);XSSFCellStyle createTableCellStyle = createTableCellStyle(workbook);XSSFCellStyle rightRowCellStyle = createRightRowCellStyle(workbook);XSSFCellStyle leftRowCellStyle = createLeftRowCellStyle(workbook);for(List data : excelData){if(rowFlag == 8) rowFlag = 0;rowFlag++;//创建一个row行，然后自增1XSSFRow row = sheet.createRow(rowIndex++);if(rowIndex==1) row.setHeight((short)(20*30));//遍历添加本行数据for (int i = 0; i < data.size(); i++) {//创建一个单元格XSSFCell cell = row.createCell(i);//value单元格值Object value = data.get(i);//设置第一个行标题的样式if(i==0 && rowIndex==1) {cell.setCellStyle(createTitleCellStyle);}//设置表头样式if(rowIndex==2) {cell.setCellStyle(createTableCellStyle);}if(rowIndex>2) {//如果是数字类型，则字体向右对齐if(value instanceof BigDecimal || value instanceof Integer) {row.getCell(i).setCellStyle(rightRowCellStyle);}else {row.getCell(i).setCellStyle(leftRowCellStyle);}}//将内容对象的文字内容写入到单元格中（单独处理数值类型）if(value instanceof BigDecimal) {BigDecimal v = (BigDecimal)value;cell.setCellValue(v.doubleValue());}else {cell.setCellValue(String.valueOf(value));}}}//准备将Excel的输出流通过response输出到页面下载//八进制输出流response.setContentType("application/octet-stream");//设置导出Excel的名称response.setHeader("Content-disposition", "attachment;filename=" + fileName);//刷新缓冲response.flushBuffer();//workbook将Excel写入到response的输出流中，供页面下载该Excel文件workbook.write(response.getOutputStream());//关闭workbookworkbook.close();}/*** 设置标题单元样式** @param workbook* @return*/public static XSSFCellStyle createTitleCellStyle(XSSFWorkbook workbook) {XSSFCellStyle cellStyle = workbook.createCellStyle();XSSFFont font = workbook.createFont();font.setBold(true);font.setFontHeightInPoints((short) 20);font.setFontName(HSSFFont.FONT_ARIAL);// 设置标题字体cellStyle.setFont(font);cellStyle.setWrapText(true);cellStyle = workbook.createCellStyle();cellStyle.setFont(font);// 设置列标题样式XSSFColor color = new XSSFColor();//根据你需要的rgb值获取byte数组color.setRGB(intToByteArray(getIntFromColor(255,231,228)));//设置自定义背景颜色cellStyle.setFillForegroundColor(color);cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置字体水平居中cellStyle.setAlignment(HorizontalAlignment.CENTER);//设置字体垂直居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置边框cellStyle.setBorderBottom(BorderStyle.THIN); //下边框cellStyle.setBorderLeft(BorderStyle.THIN);//左边框cellStyle.setBorderTop(BorderStyle.THIN);//上边框cellStyle.setBorderRight(BorderStyle.THIN);//右边框return cellStyle;}/*** 设置表头单元样式** @param workbook* @return*/public static XSSFCellStyle createTableCellStyle(XSSFWorkbook workbook) {XSSFCellStyle cellStyle = workbook.createCellStyle();XSSFFont font = workbook.createFont();font.setFontHeightInPoints((short) 11);font.setFontName(HSSFFont.FONT_ARIAL);// 设置标题字体cellStyle.setFont(font);cellStyle.setWrapText(true);cellStyle = workbook.createCellStyle();cellStyle.setFont(font);// 设置列标题样式XSSFColor color = new XSSFColor();//根据你需要的rgb值获取byte数组color.setRGB(intToByteArray(getIntFromColor(251,241,227)));//设置自定义背景颜色cellStyle.setFillForegroundColor(color);cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置字体水平居中cellStyle.setAlignment(HorizontalAlignment.CENTER);//设置边框cellStyle.setBorderBottom(BorderStyle.THIN); //下边框cellStyle.setBorderLeft(BorderStyle.THIN);//左边框cellStyle.setBorderTop(BorderStyle.THIN);//上边框cellStyle.setBorderRight(BorderStyle.THIN);//右边框return cellStyle;}/*** 设置内容单元样式** @param workbook* @return*/public static XSSFCellStyle createRowCellStyle(XSSFWorkbook workbook) {XSSFCellStyle cellStyle = workbook.createCellStyle();XSSFColor color = new XSSFColor();//根据你需要的rgb值获取byte数组color.setRGB(intToByteArray(getIntFromColor(220,220,220)));//设置自定义背景颜色cellStyle.setFillForegroundColor(color);cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置边框cellStyle.setBorderBottom(BorderStyle.THIN); //下边框cellStyle.setBorderLeft(BorderStyle.THIN);//左边框cellStyle.setBorderTop(BorderStyle.THIN);//上边框cellStyle.setBorderRight(BorderStyle.THIN);//右边框XSSFColor borderColor = new XSSFColor();//设置字体水平居中cellStyle.setAlignment(HorizontalAlignment.CENTER);//设置字体垂直居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//根据你需要的rgb值获取byte数组borderColor.setRGB(intToByteArray(getIntFromColor(181,181,181)));cellStyle.setLeftBorderColor(borderColor);cellStyle.setRightBorderColor(borderColor);cellStyle.setBottomBorderColor(borderColor);cellStyle.setTopBorderColor(borderColor);return cellStyle;}/*** 设置内容单元样式(字体靠右对齐)* 数字类型* @param workbook* @return*/public static XSSFCellStyle createRightRowCellStyle(XSSFWorkbook workbook) {XSSFCellStyle cellStyle = workbook.createCellStyle();cellStyle.setWrapText(true);cellStyle = workbook.createCellStyle();XSSFFont font = workbook.createFont();font.setFontHeightInPoints((short) 11);cellStyle.setFont(font);//设置字体水平居中cellStyle.setAlignment(HorizontalAlignment.RIGHT);//设置字体垂直居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);return cellStyle;}/*** 设置内容单元样式(字体靠左对齐)* 文本类型* @param workbook* @return*/public static XSSFCellStyle createLeftRowCellStyle(XSSFWorkbook workbook) {XSSFCellStyle cellStyle = workbook.createCellStyle();//设置字体位置cellStyle.setAlignment(HorizontalAlignment.LEFT);//设置字体垂直居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);return cellStyle;}/*** rgb转int*/private static int getIntFromColor(int Red, int Green, int Blue){Red = (Red << 16) & 0x00FF0000;Green = (Green << 8) & 0x0000FF00;Blue = Blue & 0x000000FF;return 0xFF000000 | Red | Green | Blue;}/*** int转byte[]*/public static byte[] intToByteArray(int i) {byte[] result = new byte[4];result[0] = (byte)((i >> 24) & 0xFF);result[1] = (byte)((i >> 16) & 0xFF);result[2] = (byte)((i >> 8) & 0xFF);result[3] = (byte)(i & 0xFF);return result;}
}
最终导出效果：
