java poi 一列里面有多行 java按列输出

使用方法：将自定义的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等等方法。。。
｝

输出效果：