需求描述

原生的 apache poi 提供的 api 生成 excel 比较繁琐。我们今天要实现的是利用注解来快速封装 poi 生成 excel。

我们封装好的方法非常方便，而且兼容常见的数据类型。目标效果如下：

@ExcelSheet 提供生成的单页excel的名字

@ExcelCell 提供了每行的标题和位置

@ExcelSheet("My Report")
public class ReportData {
    @ExcelCell(headerName = "User Name", columnIndex = 0)
    private String username;
    @ExcelCell(headerName = "Age", columnIndex = 1)
    private int age;
    @ExcelCell(headerName = "Birthday", columnIndex = 2)
    private Date birthday;
    @ExcelCell(headerName = "Identity Id", columnIndex = 3)
    private long identityId;
}

@ExcelSheet("My Report")

public class ReportData {

@ExcelCell(headerName = "User Name", columnIndex = 0)

private String username;

@ExcelCell(headerName = "Age", columnIndex = 1)

private int age;

@ExcelCell(headerName = "Birthday", columnIndex = 2)

private Date birthday;

@ExcelCell(headerName = "Identity Id", columnIndex = 3)

private long identityId;

}

这是封装好的生成方法：

生成一个单页excel，传入数据结构的class，传入list数组的reportData，最后输出到一个输出流就好了

ExcelUtil.newSingleSheetExcel(ReportData.class)
                .fillData(reportData)
                .flush(outputStream);

ExcelUtil.newSingleSheetExcel(ReportData.class)

.fillData(reportData)

.flush(outputStream);

详细代码实现

首先定义2个注解

@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelSheet {
    String value() default "";
}

@Target({ElementType.TYPE})

@Retention(RetentionPolicy.RUNTIME)

@Documented

public @interface ExcelSheet {

String value() default "";

}

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelCell {
    String headerName() default "";

    int columnIndex();

    String nullDescription() default "";

    String emptyDescription() default "";

    short alignment() default HSSFCellStyle.ALIGN_CENTER;
}

@Target({ElementType.FIELD})

@Retention(RetentionPolicy.RUNTIME)

@Documented

public @interface ExcelCell {

String headerName() default "";

int columnIndex();

String nullDescription() default "";

String emptyDescription() default "";

short alignment() default HSSFCellStyle.ALIGN_CENTER;

}

然后最核心的就是ExcelUtil类了：

package com.anz.moolah.common.util;

import com.anz.moolah.common.annotation.ExcelCell;
import com.anz.moolah.common.annotation.ExcelSheet;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.*;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public final class ExcelUtil {
    private ExcelUtil() {
    }

    /**
     * usage:
     * ExcelUtil.newSingleSheetExcel(SomeClass.class)
     * .fillDate(List<SomeClass> data)
     * .flush(outputStream);
     *
     * @param clazz this class must contains @ExcelSheet and @ExcelCell annotation
     * @return inner class
     */
    public static InnerExcelClass newSingleSheetExcel(Class<?> clazz) {
        XSSFWorkbook workbook = new XSSFWorkbook();

        XSSFSheet sheet = workbook.createSheet(clazz.getAnnotation(ExcelSheet.class).value());
        initHeader(sheet.createRow(0), clazz, getHeaderStyle(workbook));

        return new InnerExcelClass(workbook, clazz);
    }

    /**
     * usage:
     * ExcelUtil.newMultipleSheetsExcel()
     * .newSheet(SomeClass.class)
     * .fillDate(List<SomeClass> data)
     * .newSheet(SomeClassTwo.class)
     * .fillDate(List<SomeClassTwo> data)
     * .flush(outputStream);
     *
     * @return inner class
     * @see ExcelUtil newSingleSheetExcel()
     */
    public static InnerExcelClass newMultipleSheetsExcel() {
        return new InnerExcelClass(new XSSFWorkbook());
    }

    private static void initHeader(XSSFRow row, Class<?> clazz, XSSFCellStyle headerStyle) {
        Field[] fields = clazz.getDeclaredFields();

        for (Field field : fields) {
            ExcelCell cellAnnotation = field.getAnnotation(ExcelCell.class);
            XSSFCell cell = row.createCell(cellAnnotation.columnIndex());
            cell.setCellStyle(headerStyle);
            cell.setCellValue(cellAnnotation.headerName());
        }
    }

    private static XSSFCellStyle getHeaderStyle(XSSFWorkbook workbook) {
        XSSFCellStyle headerStyle = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        headerStyle.setFont(font);
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        return headerStyle;
    }

    public static class InnerExcelClass {
        private final XSSFWorkbook workbook;
        private Class<?> clazz;

        private XSSFSheet sheet;
        private final XSSFCellStyle defaultCellStyle;
        private final Map<Short, XSSFCellStyle> customCellStyles;
        private final Logger logger = LogManager.getLogger(ExcelUtil.class);

        private InnerExcelClass(XSSFWorkbook xssfWorkbook, Class<?> clazz) {
            this.workbook = xssfWorkbook;
            this.clazz = clazz;

            this.sheet = workbook.getSheetAt(0);
            this.defaultCellStyle = getDefaultCellStyle();
            customCellStyles = new HashMap<>();
        }

        private InnerExcelClass(XSSFWorkbook xssfWorkbook) {
            this.workbook = xssfWorkbook;

            this.defaultCellStyle = getDefaultCellStyle();
            customCellStyles = new HashMap<>();
        }

        public InnerExcelClass newSheet(Class<?> clazz) {
            this.clazz = clazz;
            this.sheet = workbook.createSheet(clazz.getAnnotation(ExcelSheet.class).value());
            initHeader(sheet.createRow(0), clazz, getHeaderStyle(workbook));
            return this;
        }

        public InnerExcelClass fillData(List<?> reportData) {
            for (int index = 0; index < reportData.size(); index++) {
                XSSFRow row = sheet.createRow(index + 1);
                fillRowData(reportData.get(index), row);
            }
            autoSizeColumn();
            return this;
        }

        public void flush(OutputStream outputStream) throws IOException {
            workbook.write(outputStream);
        }

        private void fillRowData(Object reportData, XSSFRow row) {
            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                boolean accessible = field.isAccessible();
                field.setAccessible(true);

                ExcelCell excelCell = field.getAnnotation(ExcelCell.class);
                XSSFCell cell = row.createCell(excelCell.columnIndex());
                getAndFillCellValue(reportData, field, cell, excelCell);

                XSSFCellStyle cellStyle = excelCell.alignment() == HSSFCellStyle.ALIGN_CENTER ?
                        defaultCellStyle : getCustomCellStyle(excelCell.alignment());
                cell.setCellStyle(cellStyle);

                field.setAccessible(accessible);
            }
        }

        private XSSFCellStyle getCustomCellStyle(short alignment) {
            if (!customCellStyles.containsKey(alignment)) {
                XSSFCellStyle cellStyle = workbook.createCellStyle();
                cellStyle.setAlignment(alignment);
                customCellStyles.put(alignment, cellStyle);
            }

            return customCellStyles.get(alignment);
        }

        private void getAndFillCellValue(Object reportData, Field field, XSSFCell cell, ExcelCell excelCell) {
            Class<?> fieldClass = field.getType();
            try {
                if (fieldClass == Integer.class || fieldClass == int.class) {
                    int value = field.getInt(reportData);
                    cell.setCellValue(value);
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                } else if (fieldClass == Short.class || fieldClass == short.class) {
                    short value = field.getShort(reportData);
                    cell.setCellValue(value);
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                } else if (fieldClass == Long.class || fieldClass == long.class) {
                    long value = field.getShort(reportData);
                    cell.setCellValue(value);
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                } else if (fieldClass == String.class) {
                    String value = field.get(reportData).toString();
                    cell.setCellValue(value.equals("") ? excelCell.emptyDescription() : value);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                } else if (fieldClass == Double.class || fieldClass == double.class) {
                    double value = field.getDouble(reportData);
                    cell.setCellValue(value);
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                } else if (fieldClass == Float.class || fieldClass == float.class) {
                    float value = field.getFloat(reportData);
                    cell.setCellValue(value);
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                } else if (fieldClass == Byte.class || fieldClass == byte.class) {
                    byte value = field.getByte(reportData);
                    cell.setCellValue(value);
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                } else if (fieldClass == Character.class || fieldClass == char.class) {
                    char value = field.getChar(reportData);
                    cell.setCellValue(value);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                } else if (fieldClass == Boolean.class || fieldClass == boolean.class) {
                    boolean value = field.getBoolean(reportData);
                    cell.setCellValue(value);
                    cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
                } else if (fieldClass == Date.class) {
                    String value = DateUtil.formatToDay((Date) field.get(reportData));
                    cell.setCellValue(value);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                } else if (fieldClass == BigDecimal.class) {
                    String value = field.get(reportData).toString();
                    cell.setCellValue(value);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                } else {
                    throw new RuntimeException(fieldClass + " is not supported.");
                }
            } catch (NullPointerException npe) {
                cell.setCellValue(excelCell.nullDescription());
                cell.setCellType(Cell.CELL_TYPE_STRING);
            } catch (IllegalAccessException e) {
                logger.error(e.getMessage());
            }
        }

        private void autoSizeColumn() {
            int columnCount = clazz.getDeclaredFields().length;
            while (columnCount > 0) {
                sheet.autoSizeColumn(--columnCount);
            }
        }

        private XSSFCellStyle getDefaultCellStyle() {
            XSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            return cellStyle;
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

package com.anz.moolah.common.util;

import com.anz.moolah.common.annotation.ExcelCell;

import com.anz.moolah.common.annotation.ExcelSheet;

import org.apache.logging.log4j.LogManager;

import org.apache.logging.log4j.Logger;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.xssf.usermodel.*;

import java.io.IOException;

import java.io.OutputStream;

import java.lang.reflect.Field;

import java.math.BigDecimal;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

public final class ExcelUtil {

private ExcelUtil() {

}

/**

* usage:

* ExcelUtil.newSingleSheetExcel(SomeClass.class)

* .fillDate(List<SomeClass> data)

* .flush(outputStream);

* @param clazz this class must contains @ExcelSheet and @ExcelCell annotation

* @return inner class

public static InnerExcelClass newSingleSheetExcel(Class<?> clazz) {

XSSFWorkbook workbook = new XSSFWorkbook();

XSSFSheet sheet = workbook.createSheet(clazz.getAnnotation(ExcelSheet.class).value());

initHeader(sheet.createRow(0), clazz, getHeaderStyle(workbook));

return new InnerExcelClass(workbook, clazz);

}

/**

* usage:

* ExcelUtil.newMultipleSheetsExcel()

* .newSheet(SomeClass.class)

* .fillDate(List<SomeClass> data)

* .newSheet(SomeClassTwo.class)

* .fillDate(List<SomeClassTwo> data)

* .flush(outputStream);

* @return inner class

* @see ExcelUtil newSingleSheetExcel()

public static InnerExcelClass newMultipleSheetsExcel() {

return new InnerExcelClass(new XSSFWorkbook());

}

private static void initHeader(XSSFRow row, Class<?> clazz, XSSFCellStyle headerStyle) {

Field[] fields = clazz.getDeclaredFields();

for (Field field : fields) {

ExcelCell cellAnnotation = field.getAnnotation(ExcelCell.class);

XSSFCell cell = row.createCell(cellAnnotation.columnIndex());

cell.setCellStyle(headerStyle);

cell.setCellValue(cellAnnotation.headerName());

}

private static XSSFCellStyle getHeaderStyle(XSSFWorkbook workbook) {

XSSFCellStyle headerStyle = workbook.createCellStyle();

XSSFFont font = workbook.createFont();

font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);

headerStyle.setFont(font);

headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

return headerStyle;

}

public static class InnerExcelClass {

private final XSSFWorkbook workbook;

private Class<?> clazz;

private XSSFSheet sheet;

private final XSSFCellStyle defaultCellStyle;

private final Map<Short, XSSFCellStyle> customCellStyles;

private final Logger logger = LogManager.getLogger(ExcelUtil.class);

private InnerExcelClass(XSSFWorkbook xssfWorkbook, Class<?> clazz) {

this.workbook = xssfWorkbook;

this.clazz = clazz;

this.sheet = workbook.getSheetAt(0);

this.defaultCellStyle = getDefaultCellStyle();

customCellStyles = new HashMap<>();

}

private InnerExcelClass(XSSFWorkbook xssfWorkbook) {

this.workbook = xssfWorkbook;

this.defaultCellStyle = getDefaultCellStyle();

customCellStyles = new HashMap<>();

}

public InnerExcelClass newSheet(Class<?> clazz) {

this.clazz = clazz;

this.sheet = workbook.createSheet(clazz.getAnnotation(ExcelSheet.class).value());

initHeader(sheet.createRow(0), clazz, getHeaderStyle(workbook));

return this;

}

public InnerExcelClass fillData(List<?> reportData) {

for (int index = 0; index < reportData.size(); index++) {

XSSFRow row = sheet.createRow(index + 1);

fillRowData(reportData.get(index), row);

}

autoSizeColumn();

return this;

}

public void flush(OutputStream outputStream) throws IOException {

workbook.write(outputStream);

}

private void fillRowData(Object reportData, XSSFRow row) {

Field[] fields = clazz.getDeclaredFields();

for (Field field : fields) {

boolean accessible = field.isAccessible();

field.setAccessible(true);

ExcelCell excelCell = field.getAnnotation(ExcelCell.class);

XSSFCell cell = row.createCell(excelCell.columnIndex());

getAndFillCellValue(reportData, field, cell, excelCell);

XSSFCellStyle cellStyle = excelCell.alignment() == HSSFCellStyle.ALIGN_CENTER ?

defaultCellStyle : getCustomCellStyle(excelCell.alignment());

cell.setCellStyle(cellStyle);

field.setAccessible(accessible);

}

private XSSFCellStyle getCustomCellStyle(short alignment) {

if (!customCellStyles.containsKey(alignment)) {

XSSFCellStyle cellStyle = workbook.createCellStyle();

cellStyle.setAlignment(alignment);

customCellStyles.put(alignment, cellStyle);

}

return customCellStyles.get(alignment);

}

private void getAndFillCellValue(Object reportData, Field field, XSSFCell cell, ExcelCell excelCell) {

Class<?> fieldClass = field.getType();

try {

if (fieldClass == Integer.class || fieldClass == int.class) {

int value = field.getInt(reportData);

cell.setCellValue(value);

cell.setCellType(Cell.CELL_TYPE_NUMERIC);

} else if (fieldClass == Short.class || fieldClass == short.class) {

short value = field.getShort(reportData);

cell.setCellValue(value);

cell.setCellType(Cell.CELL_TYPE_NUMERIC);

} else if (fieldClass == Long.class || fieldClass == long.class) {

long value = field.getShort(reportData);

cell.setCellValue(value);

cell.setCellType(Cell.CELL_TYPE_NUMERIC);

} else if (fieldClass == String.class) {

String value = field.get(reportData).toString();

cell.setCellValue(value.equals("") ? excelCell.emptyDescription() : value);

cell.setCellType(Cell.CELL_TYPE_STRING);

} else if (fieldClass == Double.class || fieldClass == double.class) {

double value = field.getDouble(reportData);

cell.setCellValue(value);

cell.setCellType(Cell.CELL_TYPE_NUMERIC);

} else if (fieldClass == Float.class || fieldClass == float.class) {

float value = field.getFloat(reportData);

cell.setCellValue(value);

cell.setCellType(Cell.CELL_TYPE_NUMERIC);

} else if (fieldClass == Byte.class || fieldClass == byte.class) {

byte value = field.getByte(reportData);

cell.setCellValue(value);

cell.setCellType(Cell.CELL_TYPE_NUMERIC);

} else if (fieldClass == Character.class || fieldClass == char.class) {

char value = field.getChar(reportData);

cell.setCellValue(value);

cell.setCellType(Cell.CELL_TYPE_STRING);

} else if (fieldClass == Boolean.class || fieldClass == boolean.class) {

boolean value = field.getBoolean(reportData);

cell.setCellValue(value);

cell.setCellType(Cell.CELL_TYPE_BOOLEAN);

} else if (fieldClass == Date.class) {

String value = DateUtil.formatToDay((Date) field.get(reportData));

cell.setCellValue(value);

cell.setCellType(Cell.CELL_TYPE_STRING);

} else if (fieldClass == BigDecimal.class) {

String value = field.get(reportData).toString();

cell.setCellValue(value);

cell.setCellType(Cell.CELL_TYPE_STRING);

} else {

throw new RuntimeException(fieldClass + " is not supported.");

}

} catch (NullPointerException npe) {

cell.setCellValue(excelCell.nullDescription());

cell.setCellType(Cell.CELL_TYPE_STRING);

} catch (IllegalAccessException e) {

logger.error(e.getMessage());

}

private void autoSizeColumn() {

int columnCount = clazz.getDeclaredFields().length;

while (columnCount > 0) {

sheet.autoSizeColumn(--columnCount);

}

private XSSFCellStyle getDefaultCellStyle() {

XSSFCellStyle cellStyle = workbook.createCellStyle();

cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

return cellStyle;

}

好了，打完手工！

2020-09-21日更新，

加入了null和empty的时候自定义显示内容

加入了多页报表的生成

基于注解的 poi 生成excel文件

需求描述

详细代码实现

发表评论取消回复

需求描述

详细代码实现

发表评论 取消回复

发表评论取消回复