java使用POI工具类导出excel

POI导出excel

1.导入maven依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.1</version>
        </dependency>

2.工具类

ExcelEntity.java

/**
 * 代表要打印的Excel表格,用于存放要导出为Excel的相关数据
 *
 * @param <T> 代表要打印的数据实体，如User等
 * @author zhrb@cec.jmu
 */
public class ExcelEntity<T> {
    private String sheetName;//默认生成的sheet名称
    private String header;//题头
    private String footer;//脚注
    //底下是必须具备的属性
    private String fileName;
    private String[] columnNames;//列名
    private String[] methodNames;//与列名对应的方法名
    private List<T> entities;//数据实体
​
​
    public ExcelEntity(String fileName, String[] columnNames, String[] methodNames, List<T> entities) {
        this("sheet1", "", "", fileName, columnNames, methodNames, entities);
    }
​
    public ExcelEntity(String sheetName, String header, String footer, String fileName, String[] columnNames,
                       String[] methodNames, List<T> entities) {
​
        this.sheetName = sheetName;
        this.header = header;
        this.footer = footer;
        this.fileName = fileName;
        this.columnNames = columnNames;
        this.methodNames = methodNames;
        this.entities = entities;
    }
​
    public String getHeader() {
        return header;
    }
​
    public void setHeader(String header) {
        this.header = header;
    }
​
    public String getSheetName() {
        return sheetName;
    }
​
    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }
​
    public List<T> getEntities() {
        return entities;
    }
​
    /**
     * @param entities 用于导出Excel的实体集合
     */
    public void setEntities(List<T> entities) {
        this.entities = entities;
    }
​
    public String getFooter() {
        return footer;
    }
​
    public void setFooter(String footer) {
        this.footer = footer;
    }
​
    public String[] getColumnNames() {
        return columnNames;
    }
​
    public void setColumnNames(String[] columnNames) {
        this.columnNames = columnNames;
    }
​
    public String getFileName() {
        return fileName;
    }
​
    public void setFileName(String fileName) {
        this.fileName = fileName;
    }
​
    public String[] getMethodNames() {
        return methodNames;
    }
​
    public void setMethodNames(String[] methodNames) {
        this.methodNames = methodNames;
    }
}
​

ExcelExporter.java

public class ExcelExporter {
    /**
     * 根据ExcelEntity等参数生成Workbook
     *
     * @param entity
     * @return
     * @throws Exception
     */
    public static <T> Workbook export2Excel(ExcelEntity<T> entity) throws Exception {
        Workbook workbook = export2Excel(entity.getHeader(), entity.getFooter(), entity.getSheetName(), entity.getColumnNames(), entity.getMethodNames(),
                entity.getEntities());
        return workbook;
    }
​
​
    /**
     * 根据给定参数导出Excel文档
     *
     * @param headerTitle 题头
     * @param sheetName
     * @param columnNames 表头名称
     * @param methodNames
     * @param entities
     * @return
     * @throws Exception
     */
    public static <T> Workbook export2Excel(String headerTitle, String footerTitle, String sheetName, String[] columnNames,
                                            String[] methodNames, List<T> entities) throws Exception {
        if (methodNames.length != columnNames.length) {
            throw new IllegalArgumentException("methodNames.length should be equal to columnNames.length:"
                    + columnNames.length + " " + methodNames.length);
        }
        Workbook newWorkBook2007 = new XSSFWorkbook();
        Sheet sheet = newWorkBook2007.createSheet(sheetName);
        //设置题头
        Header header = sheet.getHeader();
        header.setCenter(headerTitle);
        //设置脚注
        Footer footer = sheet.getFooter();
        footer.setCenter(footerTitle);
​
        int[] columnWidths = new int[columnNames.length];
        // 创建表头
        createTableHeader(sheet, 0, headerTitle, columnNames, columnWidths);
        // 填充表内容
        createTableContent(sheet, 1, methodNames, columnWidths, entities);
​
        return newWorkBook2007;
​
    }
​
    /**
     * 创建表头
     *
     * @param sheet
     * @param index        表头开始的行数
     * @param headerTitle  题头
     * @param columnNames
     * @param columnWidths
     */
    private static void createTableHeader(Sheet sheet, int index, String headerTitle, String[] columnNames,
                                          int[] columnWidths) {
​
​
        Row headerRow = sheet.createRow(index);
​
        /* 格式设置 */
        // 设置字体
        Font font = sheet.getWorkbook().createFont();
        font.setBold(true);// 粗体显示
        // 设置背景色
        CellStyle style = sheet.getWorkbook().createCellStyle();
        style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFont(font);
​
        for (int i = 0; i < columnNames.length; i++) {
            Cell headerCell = headerRow.createCell(i);
            headerCell.setCellStyle(style);
            headerCell.setCellValue(columnNames[i]);
        }
​
        for (int i = 0; i < columnNames.length; i++) {
            columnWidths[i] = (columnNames[i].getBytes().length + 2) * 256;
            sheet.setColumnWidth(i, columnWidths[i]);
        }
​
    }
​
    /**
     * 创建表格内容
     *
     * @param sheet
     * @param rowIndexBegin 表内容开始的行数
     * @param methodNames   T对象的方法名
     * @param columnWidths
     * @param entities
     * @throws Exception
     */
    private static <T> void createTableContent(Sheet sheet, int rowIndexBegin, String[] methodNames, int[] columnWidths,
                                               List<T> entities) throws Exception {
        Class<? extends Object> clazz = null;
        if (entities.size() > 0) {
            clazz = entities.get(0).getClass();
        }
        String content;
        for (T t : entities) {
            Row row = sheet.createRow(rowIndexBegin++);
            for (int i = 0; i < methodNames.length; i++) {
                Cell cell = row.createCell(i);
                Method method = clazz.getMethod(methodNames[i], null);
                Object object = method.invoke(t, null);
                object = object == null ? "" : object;
                if (object.getClass().equals(Date.class)) {// 对日期格式进行特殊处理
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    content = sdf.format((Date) object);
                    cell.setCellValue(content);
                } /*else if (object.getClass().equals(Timestamp.class)){//对时间戳进行特殊处理
                    Date d = new Date();
                } */ else {
                    content = object.toString();
                    cell.setCellValue(content);
                }
                int columnWidth = (content.getBytes().length + 2) * 256;
                if (columnWidth > columnWidths[i]) {// 如果实际内容宽度大于对应的表头宽度，则设置为实际内容宽度
                    columnWidths[i] = columnWidth;
                    sheet.setColumnWidth(i, columnWidths[i]);
                }
​
            }
        }
    }
​
​
    /**
     * 将workbook2007存为文件
     *
     * @param workbook2007
     * @param dstFile
     */
    public static void saveWorkBook2007(Workbook workbook2007, String dstFile) {
        File file = new File(dstFile);
        OutputStream os = null;
        try {
            os = new FileOutputStream(file);
            workbook2007.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (os != null) {
                try {
                    os.close();
                } catch (IOException e) {
                    System.out.println(e);
                }
            }
        }
    }
​
}

3.测试

创建测试实体类

Hero.java

public class Hero {
    private String name;
    private Integer age;
​
    public String getName() {
        return name;
    }
​
    public void setName(String name) {
        this.name = name;
    }
​
    public Integer getAge() {
        return age;
    }
​
    public void setAge(Integer age) {
        this.age = age;
    }
}

Wind.java

public class Wind {
    private String location;
    private Integer speed;
    private Date Timestamp;
​
    public String getLocation() {
        return location;
    }
​
    public void setLocation(String location) {
        this.location = location;
    }
​
    public Integer getSpeed() {
        return speed;
    }
​
    public void setSpeed(Integer speed) {
        this.speed = speed;
    }
​
    public Date getTimestamp() {
        return Timestamp;
    }
​
    public void setTimestamp(Date timestamp) {
        Timestamp = timestamp;
    }
}
​

测试导出excel文件

    public static void main(String[] args) throws Exception {
​
        // 准备数据
        List<Wind> winds = new ArrayList<>();// Wind有三个方法:getLocation、getSpeed、getTimestamp
        for (int i = 0; i < 10; i++) {
            Wind wind = new Wind();
            wind.setLocation(i + "");
            wind.setSpeed(i * 10);
            wind.setTimestamp(new Date());
            winds.add(wind);
        }
        String[] columnNames = {"地点", "速度", "时间"};
        String[] methodNames = {"getLocation", "getSpeed", "getTimestamp"};
//      String fileName = "d:/temp/excel1.xlsx";
        String fileName = "/Users/apple/Documents/test.xlsx";
        // 生成ExcelEntity实体，包含4个必备参数
        ExcelEntity<Wind> excelEntity = new ExcelEntity<>(fileName, columnNames, methodNames, winds);
        //excelEntity.setHeader("题头");
        //excelEntity.setFooter("脚注");
        Workbook excel = ExcelExporter.export2Excel(excelEntity);
        //ExcelExporter.export2Excel("题头","脚注", "sheet1", columnNames, methodNames, winds);//也可以这样调用,无需新建ExcelEntity对象
        //将Workbook存为文件
        ExcelExporter.saveWorkBook2007(excel, excelEntity.getFileName());
​
        System.out.println("导出完成！");
    }

生成如下excel文件