龙空技术网

Java超级好用的Excel动态生成工具

诸葛文武 823

前言:

如今兄弟们对“java在excel动态生成图表”大体比较珍视,小伙伴们都想要学习一些“java在excel动态生成图表”的相关知识。那么小编在网上汇集了一些关于“java在excel动态生成图表””的相关内容,希望姐妹们能喜欢,朋友们一起来学习一下吧!

POI简介

Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

开发工具及环境

开发工具:Idea

JAVA版本:jdk1.8.0_241

IDE插件:lombok

Maven 依赖

<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>
准备工作1.创建ExcelHeader类,该类的作用是可以自定义excel列头名称
import lombok.AllArgsConstructor;import lombok.Builder;import lombok.Data;import lombok.NoArgsConstructor;@Data@AllArgsConstructor@NoArgsConstructor@Builderpublic class ExcelHeader {    private String fieldName;    private String columnName;}
2.创建Excel工具类
import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import javax.servlet.http.HttpServletResponse;import java.beans.PropertyDescriptor;import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import java.lang.reflect.Method;import java.util.Arrays;import java.util.Iterator;import java.util.List;import java.util.stream.Collectors;public class ReflectExcelWriter<T> {    private List<ExcelHeader> headers;    private Class<T> clazz;    public ReflectExcelWriter(List<ExcelHeader> headers, Class clazz) {        this.headers = headers;        this.clazz = clazz;    }    public ReflectExcelWriter(Class clazz) {        this.clazz = clazz;        this.initHeaders();    }    private ReflectExcelWriter() {    }    private void initHeaders() {        this.headers = Arrays.asList(this.clazz.getDeclaredFields()).stream().map(field -> ExcelHeader.builder().fieldName(field.getName()).columnName(field.getName()).build()).collect(Collectors.toList());    }    public void writeFile(String fileName, List<T> dataList) {        FileOutputStream fileOut = null;        Workbook workbook = exportData(dataList);        try {            File file = new File(fileName);            if (!file.exists()) {                file.createNewFile();            }            fileOut = new FileOutputStream(file);            workbook.write(fileOut);        } catch (Exception e) {            e.printStackTrace();        } finally {            try {                if (null != fileOut) fileOut.close();                if (null != workbook) workbook.close();            } catch (Exception e) {                e.printStackTrace();            }        }    }    public void downFile(List<T> dataList, HttpServletResponse response) throws IOException {        response.setContentType("application/vnd.ms-excel;charset=UTF-8");        response.addHeader("Content-Disposition", "attachment;fileName=response.xlsx");// 设置文件名        OutputStream out = response.getOutputStream();        Workbook workbook = exportData(dataList);        try {            workbook.write(out);            out.flush();        } catch (Exception e) {            e.printStackTrace();        } finally {            try {                if (null != workbook) workbook.close();            } catch (Exception e) {                e.printStackTrace();            }        }    }    /**     * 生成Excel并写入数据信息     *     * @param dataList 数据列表     * @return 写入数据后的工作簿对象     */    public Workbook exportData(List<T> dataList) {        // 生成xlsx的Excel        Workbook workbook = new SXSSFWorkbook();        // 生成Sheet表,写入第一行的列头        Sheet sheet = buildDataSheet(workbook);        //构建每行的数据内容        int rowNum = 1;        for (Iterator<T> it = dataList.iterator(); it.hasNext(); ) {            T data = it.next();            if (data == null) {                continue;            }            //输出行数据            Row row = sheet.createRow(rowNum++);            try {                convertDataToRow(data, row);            } catch (IllegalAccessException e) {                e.printStackTrace();            } catch (InstantiationException e) {                e.printStackTrace();            }        }        return workbook;    }    /**     * 生成sheet表,并写入第一行数据(列头)     *     * @param workbook 工作簿对象     * @return 已经写入列头的Sheet     */    private Sheet buildDataSheet(Workbook workbook) {        Sheet sheet = workbook.createSheet();        // 设置列头宽度        for (int i = 0; i < headers.size(); i++) {            sheet.setColumnWidth(i, 4000);        }        // 设置默认行高        sheet.setDefaultRowHeight((short) 400);        // 写入第一行各列的数据        Row head = sheet.createRow(0);        for (int i = 0; i < headers.size(); i++) {            Cell cell = head.createCell(i);            cell.setCellValue(headers.get(i).getColumnName());        }        return sheet;    }    /**     * 将数据转换成行     *     * @param t   源数据     * @param row 行对象     * @return     */    private void convertDataToRow(T t, Row row) throws IllegalAccessException, InstantiationException {        int index = 0;        try {            for (ExcelHeader header : headers) {                Cell cell = row.createCell(index++);                PropertyDescriptor descriptor = new PropertyDescriptor(header.getFieldName(), clazz);                Method getMethod = descriptor.getReadMethod();                if (getMethod != null) {                    Object result = getMethod.invoke(t);                    cell.setCellValue(null == result ? "" : result.toString());                }            }        } catch (Exception e) {            e.printStackTrace();        }    }}
3.创建实体类
@Data    @AllArgsConstructor    @NoArgsConstructor    public static class User{        private String name;        private String phone;        private String birthdate;        private String email;    }
4.测试类默认列头
public class Test {    public static void main(String[] args) {        List<User> users = Arrays.asList(                new User("Eric", "13900000000", "1999-02-10", "Eric@163.com"),                new User("Alice", "18600000000", "2002-03-21", "Alice@163.com"),                new User("Jack", "13800000000", "1972-12-11", "Jack@163.com"),                new User("Bob", "15500000000", "1986-06-15", "Bob@163.com"));        String output = "learning/data/toutiao/user.xlsx";        ReflectExcelWriter<User> writer = new ReflectExcelWriter<>(User.class);        writer.writeFile(output, users);    }}

效果图

使用默认列头

自定义列头

public class Test {    public static void main(String[] args) {        List<User> users = Arrays.asList(                new User("Eric", "13900000000", "1999-02-10", "Eric@163.com"),                new User("Alice", "18600000000", "2002-03-21", "Alice@163.com"),                new User("Jack", "13800000000", "1972-12-11", "Jack@163.com"),                new User("Bob", "15500000000", "1986-06-15", "Bob@163.com"));        List<ExcelHeader> headers = Arrays.asList(                ExcelHeader.builder().fieldName("name").columnName("名字").build(),                ExcelHeader.builder().fieldName("phone").columnName("联系电话").build(),                ExcelHeader.builder().fieldName("birthdate").columnName("生日").build(),                ExcelHeader.builder().fieldName("email").columnName("邮箱").build()        );        String output = "learning/data/toutiao/user.xlsx"        ReflectExcelWriter<User> writer = new ReflectExcelWriter<>(headers, User.class);        writer.writeFile(output, users);    }}

效果图

自定义列头

标签: #java在excel动态生成图表