java 根据任意List<Object>输出excel表格
原来阿里的easyexcel有这个功能，我这个代码还是留在这里吧，就当是练习注解和反射了
-------------------------------------------------------
使用方法：将自定义的pojo类，添加到list中，直接调用输出类的out方法就可以
（注意：这个pojo类的属性上必须使用自定义注解，index属性用来标识该属性放在哪一列，value则标示输出的中文列名）目前支持的属性：String、int、double、Date
准备两个类：1.自定义注解类、2.输出类
用到的依赖，这些依赖也可以用其他类似的工具类替换
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.8.1</version>
</dependency>
自定义注解类
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelCell {
//输出列的中文列名
String value();
//该属性输出列的位置
int index();
}
输出类：
/** * * @author: hrw * time: 2022/9/17 12:15 * description:根据list<Object>直接输出excel文件 */ public class ExcelOutUtilByList { static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); /** * @param fileName 文件名，不包含后缀 * @param path excel保存路径,不包含文件名 * @param data 数据list，里面的pojo属性需要有注解@ExcelVaue，才能识别 * @param sdf2 自定义时间格式化类，传null则使用默认格式 “yyyy-MM-dd HH:mm:ss” * @param comment 备注，会显示在标题旁边 * @return 文件位置 */ public static String out(String fileName, String path, List data,SimpleDateFormat sdf2,String comment) { if (fileName.contains(".")) { fileName = fileName.substring(0, fileName.indexOf(".")); } if (data == null || data.size() < 1) { return "没有数据"; } Map indexMap = new HashMap(); Map titleMap = new HashMap(); Class<?> aClass = data.get(1).getClass(); boolean isOk = getValueIndex(new ArrayList(), titleMap, indexMap, aClass);//获取列名和位置 if (!isOk) { return "所有参数都没有@ExcelCell注解，读取列名失败"; } Method[] methods = aClass.getMethods();//获取方法列表 //创建表格 HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(fileName + 1); setHerderRow(fileName,titleMap,wb,comment);//设置标题栏 和样式 第1行 setCellName(titleMap,indexMap,wb,sheet);//设置列名 第2行 HSSFCellStyle style = getContent(wb);//获取单元格样式 for (int i = 0; i < data.size(); i++) {//循环插入数据 从第三行开始插入 HSSFRow row = sheet.createRow(i + 2); Set indexSet = indexMap.keySet(); Iterator iterator = indexSet.iterator(); while (iterator.hasNext()) { String keyParam = (String) iterator.next(); int index = (int) indexMap.get(keyParam); Object o = data.get(i); for (Method method : methods) { if (method.getName().equals(keyParam)) { HSSFCell cell = row.createCell(index - 1); String type = method.getGenericReturnType().toString(); try { if (type.equals("int")) { //取出来的值类型跟excel支持的类型匹配 int value = (int) method.invoke(o,null); cell.setCellValue(value); } else if (type.equals("class java.util.Date")) { Date value = (Date) method.invoke(o,null); String format = ""; if (sdf2 != null) { format = sdf2.format(value); } else { format = sdf.format(value);//Date类先格式化成String再存到cell的value中 } cell.setCellValue(format); } else if (type.equals("double")) { double value = (double) method.invoke(o,null); cell.setCellValue(value); } else { String value = (String) method.invoke(o,null); cell.setCellValue(value); } cell.setCellStyle(style); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } } } } } //输出文件 String fileName2 = path + fileName + ".xls"; File file = new File(fileName2); //创建文件输出流 try { FileOutputStream fileOutputStream = new FileOutputStream(file); //用最开始创建的工作簿.write进行文件写出 wb.write(fileOutputStream); } catch (IOException e) { e.printStackTrace(); System.out.println("导出excel异常：" + e.getMessage()); return "导出excel异常：" + e.getMessage(); } System.out.println("导出成功：" + fileName2); return fileName2; } /** * 根据标题栏创建 各个列名 * @param titleMap 位置-中文标题 * @param indexMap 属性名-位置 * @param wb * @param sheet */ private static void setCellName(Map titleMap, Map indexMap, HSSFWorkbook wb,HSSFSheet sheet) { HSSFRow row = sheet.createRow(1); HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); // 居中 style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());// 设置背景色 style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderBottom(BorderStyle.THIN); //下边框 style.setBorderLeft(BorderStyle.THIN);//左边框 style.setBorderTop(BorderStyle.THIN);//上边框 style.setBorderRight(BorderStyle.THIN);//右边框 Set set = indexMap.keySet(); Iterator iterator = set.iterator(); while (iterator.hasNext()) { String cellName = (String) iterator.next();//英文属性名 int index = (int) indexMap.get(cellName);//对应位置 sheet.autoSizeColumn(index-1);//自动列宽 HSSFCell cell = row.createCell(index - 1); cell.setCellValue((String) titleMap.get(index));//将中文名设置进去 cell.setCellStyle(style); } } /** * 设置sheet页的标题栏和样式 * @param fileName * @param titleMap * @param wb * @param comment */ private static void setHerderRow(String fileName,Map titleMap,HSSFWorkbook wb,String comment) { HSSFSheet sheet = wb.getSheetAt(0); HSSFCell cell = sheet.createRow(0).createCell(0); if (comment != null && comment.length() > 0) {//设置备注 fileName += "（" + comment +"）"; } cell.setCellValue(fileName);//第一行第一列设置为标题栏 HSSFCellStyle style = wb.createCellStyle(); style.setBorderBottom(BorderStyle.THIN); //下边框 style.setBorderLeft(BorderStyle.THIN);//左边框 style.setBorderTop(BorderStyle.THIN);//上边框 style.setBorderRight(BorderStyle.THIN);//右边框 // style.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置加粗 HSSFFont font1 = wb.createFont(); font1.setColor((short)16);; font1.setFontHeightInPoints((short)18); style.setFont(font1); style.setAlignment(HorizontalAlignment.CENTER); sheet.addMergedRegion(new CellRangeAddress(0,0,0,titleMap.size()-1));//合并标题栏 cell.setCellStyle(style); } /** * 根据要输出的类属性上的注解，获取标题栏和列位置 * @param count 固定传空list * @param titleMap * @param indexMap * @param classzz * @return */ public static boolean getValueIndex(List count,Map titleMap,Map indexMap, Class<?> classzz) { boolean flag = true; Field[] fields = classzz.getDeclaredFields(); for (int i = 0; i < fields.length; i++) { Field field = fields[i]; ExcelCell annotation = field.getAnnotation(ExcelCell.class); if (annotation == null) { continue; } String name = fields[i].getName(); name = "get" + name.substring(0, 1).toUpperCase() + name.substring(1); indexMap.put(name, annotation.index()); titleMap.put(annotation.index(),annotation.value()); count.add(1); } if (classzz.getSuperclass() != null) { getValueIndex(count,titleMap,indexMap,classzz.getSuperclass()); } if (count.size() == 0) { flag = false; System.out.println("所有参数都没有@ExcelCell注解，读取列名失败"); } return flag; } /** * 设置数据的单元格格式 * @param wb * @return */ public static HSSFCellStyle getContent(HSSFWorkbook wb){ HSSFCellStyle style = wb.createCellStyle(); style.setBorderBottom(BorderStyle.THIN); //下边框 style.setBorderLeft(BorderStyle.THIN);//左边框 style.setBorderTop(BorderStyle.THIN);//上边框 style.setBorderRight(BorderStyle.THIN);//右边框 return style; } }
测试用的pojo
public class Suanshu { @ExcelCell(value = "参数1",index = 1) private int oneCell; @ExcelCell(value = "参数2",index = 2) private int twoCell; @ExcelCell(value = "加法结果",index = 3) private String jiafaResult; @ExcelCell(value = "减法结果",index = 4) private String jianfaResult; @ExcelCell(value = "乘法结果",index = 5) private String chengfaResult; get set等等方法。。。 ｝
输出效果：
