Springboot 之 使用POI导出Excel文件

本文章来自【知识林】

前面讲述了使用POI导出Word文件和读取Excel文件，这两个例子都相对简单，接下来要讲述的使用POI导出Excel文件要复杂得多，内容也会比较长。

• 创建表头信息

表头信息用于自动生成表头结构及排序

public class ExcelHeader implements Comparable<ExcelHeader>{    /**     * excel的标题名称     */    private String title;    /**     * 每一个标题的顺序     */    private int order;    /**     * 说对应方法名称     */    private String methodName;    public String getTitle() {        return title;    }    public void setTitle(String title) {        this.title = title;    }    public int getOrder() {        return order;    }    public void setOrder(int order) {        this.order = order;    }    public String getMethodName() {        return methodName;    }    public void setMethodName(String methodName) {        this.methodName = methodName;    }    public int compareTo(ExcelHeader o) {        return order>o.order?1:(order<o.order?-1:0);    }    public ExcelHeader(String title, int order, String methodName) {        super();        this.title = title;        this.order = order;        this.methodName = methodName;    }}

• 表头信息的Annotation

/** * 用来在对象的get方法上加入的annotation，通过该annotation说明某个属性所对应的标题 * Created by 钟述林 [e-mail] on 2016/10/29 0:14. */@Retention(RetentionPolicy.RUNTIME)public @interface ExcelResources {    /**     * 属性的标题名称     * @return     */    String title();    /**     * 在excel的顺序     * @return     */    int order() default 9999;}

• 创建数据实体

public class WebDto {    //网站名称    private String name;    //网址    private String url;    //用户名    private String username;    //密码    private String password;    //日均访问量    private Integer readCount;    public WebDto(String name, String url, String username, String password, Integer readCount) {        this.name = name;        this.url = url;        this.username = username;        this.password = password;        this.readCount = readCount;    }    public WebDto() {}    @Override    public String toString() {        return "WebDto{" +                "name='" + name + '\'' +                ", url='" + url + '\'' +                ", username='" + username + '\'' +                ", password='" + password + '\'' +                ", readCount=" + readCount +                '}';    }    @ExcelResources(title="网站名称",order=1)    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    @ExcelResources(title="网址",order=2)    public String getUrl() {        return url;    }    public void setUrl(String url) {        this.url = url;    }    @ExcelResources(title="用户名",order=3)    public String getUsername() {        return username;    }    public void setUsername(String username) {        this.username = username;    }    @ExcelResources(title="密码",order=4)    public String getPassword() {        return password;    }    public void setPassword(String password) {        this.password = password;    }    @ExcelResources(title="日均访问量",order=5)    public Integer getReadCount() {        return readCount;    }    public void setReadCount(Integer readCount) {        this.readCount = readCount;    }}

注意：这里使用到了@ExcelResources来自动识别表头信息及序号

• 获取模板文件的工具类

public class TemplateFileUtil {    public static FileInputStream getTemplates(String tempName) throws FileNotFoundException {        return new FileInputStream(ResourceUtils.getFile("classpath:excel-templates/"+tempName));    }}

注意：从这里可以看出，所有的Excel模板文件都放在resources/excel-templates/目录下。

• 模板工具类

通过此类可以自动复制表样式等功能

/** * 该类实现了基于模板的导出 * 如果要导出序号，需要在excel中定义一个标识为sernums * 如果要替换信息，需要传入一个Map，这个map中存储着要替换信息的值，在excel中通过#来开头 * 要从哪一行那一列开始替换需要定义一个标识为datas * 如果要设定相应的样式，可以在该行使用styles完成设定，此时所有此行都使用该样式 * 如果使用defaultStyls作为表示，表示默认样式，如果没有defaultStyles使用datas行作为默认样式 * Created by 钟述林 [e-mail] on 2016/10/28 23:38. */public class ExcelTemplate {    /**     * 数据行标识     */    public final static String DATA_LINE = "datas";    /**     * 默认样式标识     */    public final static String DEFAULT_STYLE = "defaultStyles";    /**     * 行样式标识     */    public final static String STYLE = "styles";    /**     * 插入序号样式标识     */    public final static String SER_NUM = "sernums";    private static ExcelTemplate et = new ExcelTemplate();    private Workbook wb;    private Sheet sheet;    /**     * 数据的初始化列数     */    private int initColIndex;    /**     * 数据的初始化行数     */    private int initRowIndex;    /**     * 当前列数     */    private int curColIndex;    /**     * 当前行数     */    private int curRowIndex;    /**     * 当前行对象     */    private Row curRow;    /**     * 最后一行的数据     */    private int lastRowIndex;    /**     * 默认样式     */    private CellStyle defaultStyle;    /**     * 默认行高     */    private float rowHeight;    /**     * 存储某一方所对于的样式     */    private Map<Integer,CellStyle> styles;    /**     * 序号的列     */    private int serColIndex;    private ExcelTemplate(){    }    public static ExcelTemplate getInstance() {        return et;    }    /**     * 从classpath路径下读取相应的模板文件     * @param path     * @return     */    public ExcelTemplate readTemplateByClasspath(String path) {        try {            wb = new HSSFWorkbook(TemplateFileUtil.getTemplates(path));            initTemplate();        } catch (IOException e) {            e.printStackTrace();            throw new RuntimeException("读取模板不存在！请检查");        }        return this;    }    /**     * 将文件写到相应的路径下     * @param filepath     */    public void writeToFile(String filepath) {        FileOutputStream fos = null;        try {            fos = new FileOutputStream(filepath);            wb.write(fos);        } catch (FileNotFoundException e) {            e.printStackTrace();            throw new RuntimeException("写入的文件不存在");        } catch (IOException e) {            e.printStackTrace();            throw new RuntimeException("写入数据失败:"+e.getMessage());        } finally {            try {                if(fos!=null) fos.close();            } catch (IOException e) {                e.printStackTrace();            }        }    }    /**     * 将文件写到某个输出流中     * @param os     */    public void wirteToStream(OutputStream os) {        try {            wb.write(os);        } catch (IOException e) {            e.printStackTrace();            throw new RuntimeException("写入流失败:"+e.getMessage());        }    }    /**     * 从某个路径来读取模板     * @param path     * @return     */    public ExcelTemplate readTemplateByPath(String path) {        try {            wb = new HSSFWorkbook(TemplateFileUtil.getTemplates(path));            initTemplate();        } catch (IOException e) {            e.printStackTrace();            throw new RuntimeException("读取模板不存在！请检查");        }        return this;    }    /**     * 创建相应的元素，基于String类型     * @param value     */    public void createCell(String value) {        Cell c = curRow.createCell(curColIndex);        setCellStyle(c);        c.setCellValue(value);        curColIndex++;    }    public void createCell(int value) {        Cell c = curRow.createCell(curColIndex);        setCellStyle(c);        c.setCellValue((int)value);        curColIndex++;    }    public void createCell(Date value) {        Cell c = curRow.createCell(curColIndex);        setCellStyle(c);        c.setCellValue(value);        curColIndex++;    }    public void createCell(double value) {        Cell c = curRow.createCell(curColIndex);        setCellStyle(c);        c.setCellValue(value);        curColIndex++;    }    public void createCell(boolean value) {        Cell c = curRow.createCell(curColIndex);        setCellStyle(c);        c.setCellValue(value);        curColIndex++;    }    public void createCell(Calendar value) {        Cell c = curRow.createCell(curColIndex);        setCellStyle(c);        c.setCellValue(value);        curColIndex++;    }    public void createCell(BigInteger value) {        Cell c = curRow.createCell(curColIndex);        setCellStyle(c);        c.setCellValue(value==null?0:value.intValue());        curColIndex++;    }    /**     * 设置某个元素的样式     * @param c     */    private void setCellStyle(Cell c) {        if(styles.containsKey(curColIndex)) {            c.setCellStyle(styles.get(curColIndex));        } else {            c.setCellStyle(defaultStyle);        }    }    /**     * 创建新行，在使用时只要添加完一行，需要调用该方法创建     */    public void createNewRow() {        if(lastRowIndex>curRowIndex&&curRowIndex!=initRowIndex) {            sheet.shiftRows(curRowIndex, lastRowIndex, 1,true,true);            lastRowIndex++;        }        curRow = sheet.createRow(curRowIndex);        curRow.setHeightInPoints(rowHeight);        curRowIndex++;        curColIndex = initColIndex;    }    /**     * 插入序号，会自动找相应的序号标示的位置完成插入     */    public void insertSer() {        int index = 1;        Row row = null;        Cell c = null;        for(int i=initRowIndex;i<curRowIndex;i++) {            row = sheet.getRow(i);            c = row.createCell(serColIndex);            setCellStyle(c);            c.setCellValue(index++);        }    }    /**     * 根据map替换相应的常量，通过Map中的值来替换#开头的值     * @param datas     */    public void replaceFinalData(Map<String,String> datas) {        if(datas==null) return;        for(Row row:sheet) {            for(Cell c:row) {//                if(c.getCellType()!=Cell.CELL_TYPE_STRING) continue;                String str = c.getStringCellValue().trim();                if(str.startsWith("#")) {                    if(datas.containsKey(str.substring(1))) {                        c.setCellValue(datas.get(str.substring(1)));                    }                }            }        }    }    /**     * 基于Properties的替换，依然也是替换#开始的     * @param prop     */    public void replaceFinalData(Properties prop) {        if(prop==null) return;        for(Row row:sheet) {            for(Cell c:row) {//                if(c.getCellType()!=Cell.CELL_TYPE_STRING) continue;                String str = c.getStringCellValue().trim();                if(str.startsWith("#")) {                    if(prop.containsKey(str.substring(1))) {                        c.setCellValue(prop.getProperty(str.substring(1)));                    }                }            }        }    }    private void initTemplate() {        sheet = wb.getSheetAt(0);        initConfigData();        lastRowIndex = sheet.getLastRowNum();        curRow = sheet.createRow(curRowIndex);    }    /**     * 初始化数据信息     */    private void initConfigData() {        boolean findData = false;        boolean findSer = false;        for(Row row:sheet) {            if(findData) break;            for(Cell c:row) {//                if(c.getCellType()!=Cell.CELL_TYPE_STRING) continue;                String str = c.getStringCellValue().trim();                if(str.equals(SER_NUM)) {                    serColIndex = c.getColumnIndex();                    findSer = true;                }                if(str.equals(DATA_LINE)) {                    initColIndex = c.getColumnIndex();                    initRowIndex = row.getRowNum();                    curColIndex = initColIndex;                    curRowIndex = initRowIndex;                    findData = true;                    defaultStyle = c.getCellStyle();                    rowHeight = row.getHeightInPoints();                    initStyles();                    break;                }            }        }        if(!findSer) {            initSer();        }    }    /**     * 初始化序号位置     */    private void initSer() {        for(Row row:sheet) {            for(Cell c:row) {//                if(c.getCellType()!=Cell.CELL_TYPE_STRING) continue;                String str = c.getStringCellValue().trim();                if(str.equals(SER_NUM)) {                    serColIndex = c.getColumnIndex();                }            }        }    }    /**     * 初始化样式信息     */    private void initStyles() {        styles = new HashMap<Integer, CellStyle>();        for(Row row:sheet) {            for(Cell c:row) {//                if(c.getCellType()!=Cell.CELL_TYPE_STRING) continue;                String str = c.getStringCellValue().trim();                if(str.equals(DEFAULT_STYLE)) {                    defaultStyle = c.getCellStyle();                }                if(str.equals(STYLE)) {                    styles.put(c.getColumnIndex(), c.getCellStyle());                }            }        }    }}

• 操作工具类

/** * 该类实现了将一组对象转换为Excel表格，并且可以从Excel表格中读取到一组List对象中 * 该类利用了BeanUtils框架中的反射完成 * 使用该类的前提，在相应的实体对象上通过ExcelReources来完成相应的注解 * Created by 钟述林 [e-mail] on 2016/10/29 0:15. */public class ExcelUtil {    private static ExcelUtil eu = new ExcelUtil();    private ExcelUtil(){}    public static ExcelUtil getInstance() {        return eu;    }    /**     * 处理对象转换为Excel     * @param template     * @param objs     * @param clz     * @param isClasspath     * @return     */    private ExcelTemplate handlerObj2Excel (String template, List objs, Class clz, boolean isClasspath)  {        ExcelTemplate et = ExcelTemplate.getInstance();        try {            if(isClasspath) {                et.readTemplateByClasspath(template);            } else {                et.readTemplateByPath(template);            }            List<ExcelHeader> headers = getHeaderList(clz);            Collections.sort(headers);            //输出标题            et.createNewRow();            for(ExcelHeader eh:headers) {                et.createCell(eh.getTitle());            }            //输出值            for(Object obj:objs) {                et.createNewRow();                for(ExcelHeader eh:headers) {                    //              Method m = clz.getDeclaredMethod(mn);                    //              Object rel = m.invoke(obj);                    et.createCell(BeanUtils.getProperty(obj,getMethodName(eh)));                }            }        } catch (IllegalAccessException e) {            e.printStackTrace();        } catch (InvocationTargetException e) {            e.printStackTrace();        } catch (NoSuchMethodException e) {            e.printStackTrace();        }        return et;    }    /**     * 根据标题获取相应的方法名称     * @param eh     * @return     */    private String getMethodName(ExcelHeader eh) {        String mn = eh.getMethodName().substring(3);        mn = mn.substring(0,1).toLowerCase()+mn.substring(1);        return mn;    }    /**     * 将对象转换为Excel并且导出，该方法是基于模板的导出，导出到流     * @param datas 模板中的替换的常量数据     * @param template 模板路径     * @param os 输出流     * @param objs 对象列表     * @param clz 对象的类型     * @param isClasspath 模板是否在classPath路径下     */    public void exportObj2ExcelByTemplate(Map<String,String> datas, String template, OutputStream os, List objs, Class clz, boolean isClasspath) {        try {            ExcelTemplate et = handlerObj2Excel(template, objs, clz, isClasspath);            et.replaceFinalData(datas);            et.wirteToStream(os);            os.flush();            os.close();        } catch (IOException e) {            e.printStackTrace();        }    }    /**     * 将对象转换为Excel并且导出，该方法是基于模板的导出，导出到一个具体的路径中     * @param datas 模板中的替换的常量数据     * @param template 模板路径     * @param outPath 输出路径     * @param objs 对象列表     * @param clz 对象的类型     * @param isClasspath 模板是否在classPath路径下     */    public void exportObj2ExcelByTemplate(Map<String,String> datas,String template,String outPath,List objs,Class clz,boolean isClasspath) {        ExcelTemplate et = handlerObj2Excel(template, objs, clz, isClasspath);        et.replaceFinalData(datas);        et.writeToFile(outPath);    }    /**     * 将对象转换为Excel并且导出，该方法是基于模板的导出，导出到流,基于Properties作为常量数据     * @param prop 基于Properties的常量数据模型     * @param template 模板路径     * @param os 输出流     * @param objs 对象列表     * @param clz 对象的类型     * @param isClasspath 模板是否在classPath路径下     */    public void exportObj2ExcelByTemplate(Properties prop, String template, OutputStream os, List objs, Class clz, boolean isClasspath) {        ExcelTemplate et = handlerObj2Excel(template, objs, clz, isClasspath);        et.replaceFinalData(prop);        et.wirteToStream(os);    }    /**     * 将对象转换为Excel并且导出，该方法是基于模板的导出，导出到一个具体的路径中,基于Properties作为常量数据     * @param prop 基于Properties的常量数据模型     * @param template 模板路径     * @param outPath 输出路径     * @param objs 对象列表     * @param clz 对象的类型     * @param isClasspath 模板是否在classPath路径下     */    public void exportObj2ExcelByTemplate(Properties prop,String template,String outPath,List objs,Class clz,boolean isClasspath) {        ExcelTemplate et = handlerObj2Excel(template, objs, clz, isClasspath);        et.replaceFinalData(prop);        et.writeToFile(outPath);    }    private Workbook handleObj2Excel(List objs, Class clz) {        Workbook wb = new HSSFWorkbook();        try {            Sheet sheet = wb.createSheet();            Row r = sheet.createRow(0);            List<ExcelHeader> headers = getHeaderList(clz);            Collections.sort(headers);            //写标题            for(int i=0;i<headers.size();i++) {                r.createCell(i).setCellValue(headers.get(i).getTitle());            }            //写数据            Object obj = null;            for(int i=0;i<objs.size();i++) {                r = sheet.createRow(i+1);                obj = objs.get(i);                for(int j=0;j<headers.size();j++) {                    r.createCell(j).setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));                }            }        } catch (IllegalAccessException e) {            e.printStackTrace();        } catch (InvocationTargetException e) {            e.printStackTrace();        } catch (NoSuchMethodException e) {            e.printStackTrace();        }        return wb;    }    /**     * 导出对象到Excel，不是基于模板的，直接新建一个Excel完成导出，基于路径的导出     * @param outPath 导出路径     * @param objs 对象列表     * @param clz 对象类型     */    public void exportObj2Excel(String outPath,List objs,Class clz) {        Workbook wb = handleObj2Excel(objs, clz);        FileOutputStream fos = null;        try {            fos = new FileOutputStream(outPath);            wb.write(fos);        } catch (FileNotFoundException e) {            e.printStackTrace();        } catch (IOException e) {            e.printStackTrace();        } finally {            try {                if(fos!=null) fos.close();            } catch (IOException e) {                e.printStackTrace();            }        }    }    /**     * 导出对象到Excel，不是基于模板的，直接新建一个Excel完成导出，基于流     * @param os 输出流     * @param objs 对象列表     * @param clz 对象类型     */    public void exportObj2Excel(OutputStream os,List objs,Class clz) {        try {            Workbook wb = handleObj2Excel(objs, clz);            wb.write(os);        } catch (IOException e) {            e.printStackTrace();        }    }    /**     * 从类路径读取相应的Excel文件到对象列表     * @param path 类路径下的path     * @param clz 对象类型     * @param readLine 开始行，注意是标题所在行     * @param tailLine 底部有多少行，在读入对象时，会减去这些行     * @return     */    public List<Object> readExcel2ObjsByClasspath(String path,Class clz,int readLine,int tailLine) {        Workbook wb = null;        try {            wb = new HSSFWorkbook(TemplateFileUtil.getTemplates(path));            return handlerExcel2Objs(wb, clz, readLine,tailLine);        } catch (IOException e) {            e.printStackTrace();        }        return null;    }    /**     * 从文件路径读取相应的Excel文件到对象列表     * @param path 文件路径下的path     * @param clz 对象类型     * @param readLine 开始行，注意是标题所在行     * @param tailLine 底部有多少行，在读入对象时，会减去这些行     * @return     */    public List<Object> readExcel2ObjsByPath(String path,Class clz,int readLine,int tailLine) {        Workbook wb = null;        try {            wb = new HSSFWorkbook(TemplateFileUtil.getTemplates(path));            return handlerExcel2Objs(wb, clz, readLine,tailLine);        } catch (IOException e) {            e.printStackTrace();        }        return null;    }    /**     * 从类路径读取相应的Excel文件到对象列表，标题行为0，没有尾行     * @param path 路径     * @param clz 类型     * @return 对象列表     */    public List<Object> readExcel2ObjsByClasspath(String path,Class clz) {        return this.readExcel2ObjsByClasspath(path, clz, 0,0);    }    /**     * 从文件路径读取相应的Excel文件到对象列表，标题行为0，没有尾行     * @param path 路径     * @param clz 类型     * @return 对象列表     */    public List<Object> readExcel2ObjsByPath(String path,Class clz) {        return this.readExcel2ObjsByPath(path, clz,0,0);    }    private String getCellValue(Cell c) {        String o = null;        switch (c.getCellType()) {            case Cell.CELL_TYPE_BLANK:                o = ""; break;            case Cell.CELL_TYPE_BOOLEAN:                o = String.valueOf(c.getBooleanCellValue()); break;            case Cell.CELL_TYPE_FORMULA:                o = String.valueOf(c.getCellFormula()); break;            case Cell.CELL_TYPE_NUMERIC:                o = String.valueOf(c.getNumericCellValue()); break;            case Cell.CELL_TYPE_STRING:                o = c.getStringCellValue(); break;            default:                o = null;                break;        }        return o;    }    private List<Object> handlerExcel2Objs(Workbook wb,Class clz,int readLine,int tailLine) {        Sheet sheet = wb.getSheetAt(0);        List<Object> objs = null;        try {            Row row = sheet.getRow(readLine);            objs = new ArrayList<Object>();            Map<Integer,String> maps = getHeaderMap(row, clz);            if(maps==null||maps.size()<=0) throw new RuntimeException("要读取的Excel的格式不正确，检查是否设定了合适的行");            for(int i=readLine+1;i<=sheet.getLastRowNum()-tailLine;i++) {                row = sheet.getRow(i);                Object obj = clz.newInstance();                for(Cell c:row) {                    int ci = c.getColumnIndex();                    String mn = maps.get(ci).substring(3);                    mn = mn.substring(0,1).toLowerCase()+mn.substring(1);                    BeanUtils.copyProperty(obj,mn, this.getCellValue(c));                }                objs.add(obj);            }        } catch (InstantiationException e) {            e.printStackTrace();        } catch (IllegalAccessException e) {            e.printStackTrace();        } catch (InvocationTargetException e) {            e.printStackTrace();        }        return objs;    }    private List<ExcelHeader> getHeaderList(Class clz) {        List<ExcelHeader> headers = new ArrayList<ExcelHeader>();        Method[] ms = clz.getDeclaredMethods();        for(Method m:ms) {            String mn = m.getName();            if(mn.startsWith("get")) {                if(m.isAnnotationPresent(ExcelResources.class)) {                    ExcelResources er = m.getAnnotation(ExcelResources.class);                    headers.add(new ExcelHeader(er.title(),er.order(),mn));                }            }        }        return headers;    }    private Map<Integer,String> getHeaderMap(Row titleRow,Class clz) {        List<ExcelHeader> headers = getHeaderList(clz);        Map<Integer,String> maps = new HashMap<Integer, String>();        for(Cell c:titleRow) {            String title = c.getStringCellValue();            for(ExcelHeader eh:headers) {                if(eh.getTitle().equals(title.trim())) {                    maps.put(c.getColumnIndex(), eh.getMethodName().replace("get","set"));                    break;                }            }        }        return maps;    }}

• Excel模板文件

创建一个模板文件，如下图：

• 测试类

@SpringBootTest@RunWith(SpringRunner.class)public class ExportExcelTest {    @Test    public void test() throws Exception {        List<WebDto> list = new ArrayList<WebDto>();        list.add(new WebDto("知识林", "http://www.zslin.com", "admin", "111111", 555));        list.add(new WebDto("权限系统", "http://basic.zslin.com", "admin", "111111", 111));        list.add(new WebDto("校园网", "http://school.zslin.com", "admin", "222222", 333));        Map<String, String> map = new HashMap<String, String>();        map.put("title", "网站信息表");        map.put("total", list.size()+" 条");        map.put("date", getDate());        ExcelUtil.getInstance().exportObj2ExcelByTemplate(map, "web-info-template.xls", new FileOutputStream("D:/temp/out.xls"),                list, WebDto.class, true);    }    private String getDate() {        SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");        return sdf.format(new Date());    }}

执行测试方法后，查看D:/temp/out.xls文件后可以看到如下图的内容：

示例代码：https://github.com/zsl131/spring-boot-test/tree/master/study16

本文章来自【知识林】