菜鸟笔记(三) - Java Excel报表导入导出

作者: keepcleargas | 来源:发表于2016-09-08 11:50 被阅读2947次

    本文将介绍Java Poi包的使用,并实现Excel报表的导入导出。

    前提摘要:在系统的管理后台当中中,Excel报表的导入导出已经是不不可避免的场景。值得一提的是支付宝和微信支付的批量转账也需要相应格式的Excel文档支持,这个文档就需要从我们的业务订单系统中相应的导出的[手动滑稽]。

    Apache POI[^1] 基本介绍

    Apache POI 是创建和维护操作各种符合Office Open XML(OOXML)标准和微软的OLE 2复合文档格式(OLE2)的Java API。用它可以使用Java读取和创建,修改MS Excel文件.而且,还可以使用Java读取和创建MS Word和MSPowerPoint文件。Apache POI 提供Java操作Excel解决方案(适用于Excel97-2008)。

    Excel导出

    • 定义数据模型

    参考财付通的批量提现Excel的格式,定义模型如下

        private int recordId; //提现id
        private String cname; //提现人名称
        private String cbank; //提现银行的code
        private String cnum;  //提现卡号
        private int money;    //提现金额
        private int type = 1; //类别
        private String comment = "现金提现"; //备注
    
    • 定义接口IExcelExport
    package cn.daimaniu.blog.poi;
    
    import java.util.List;
    
    public interface IExcelExport<T> {
        /**
         * 获取Excel的Header
         *
         * @return
         */
        String[] getHeader();
    
        /**
         * 返回Excel的header大小
         *
         * @return
         */
        int getHeaderSize();
    
        /**
         * 导出的标题
         *
         * @return
         */
        String getTitle();
    
        /**
         * 是否包含 特殊的Field的处理
         *
         * @param filedName
         * @return
         */
        boolean containSpecialField(String filedName);
    
        /**
         * 获取 特殊的Field的处理后的值
         *
         * @param filedName
         * @return
         */
        String getSpecialFieldValue(String filedName);
    
        /**
         * 获取数据源
         *
         * @return
         */
        List<T> getPoiList();
    
        /**
         * 设置数据源
         */
        void setPoiList(List<T> data);
    }
    
    

    接口主要定义了model的set和get,生成文件名称,excel第一行的标题定义

    • 实现接口 RecordExport
    package cn.daimaniu.blog.poi.impl;
    
    import cn.daimaniu.blog.poi.IExcelExport;
    import cn.daimaniu.blog.poi.model.RecordPoi;
    import org.apache.commons.lang.StringUtils;
    
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.List;
    
    public class RecordExport implements IExcelExport<RecordPoi> {
        List<RecordPoi> poiList = new ArrayList<>();
        private String title;
        String[] headers = {"序号", "收款人姓名", "银行名称", "银行卡号", "付款金额", "到账方式", "备注"};
    
        public String[] getHeader() {
            return headers;
        }
    
        public int getHeaderSize() {
            return headers.length;
        }
    
        public String getTitle() {
            if (StringUtils.isEmpty(title)) {
                return formatDate(new Date(), "yyyy-MM-dd_HH-mm-ss") + "-提现记录.xls";
            } else {
                return title;
            }
        }
    
        private String formatDate(Date date, String format) {
            SimpleDateFormat dateFormat = new SimpleDateFormat(format);
            return dateFormat.format(date);
        }
    
        public boolean containSpecialField(String filedName) {
            return false;
        }
    
        public String getSpecialFieldValue(String filedName) {
            return null;
        }
    
        public List<RecordPoi> getPoiList() {
            return this.poiList;
        }
    
        public void setPoiList(List<RecordPoi> data) {
            this.poiList = data;
        }
    }
    
    • ExcelUtil工具类
        public static <T> void export(IExcelExport<T> excelExport, OutputStream out, String pattern) {
            //读取配置
            String[] headers = excelExport.getHeader();
            Collection<T> dataset = excelExport.getPoiList();
            if (dataset == null || dataset.isEmpty()) {
                //空数据 直接退出
                return;
            }
            // 声明一个工作薄
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 生成一个表格
            HSSFSheet sheet = workbook.createSheet(excelExport.getTitle());
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(15);
            // 生成一个样式
            HSSFCellStyle style = workbook.createCellStyle();
            // 设置这些样式
            style.setFillForegroundColor(HSSFColor.WHITE.index);
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            // 生成一个字体
            HSSFFont font = workbook.createFont();
            font.setColor(HSSFColor.BLACK.index);
            font.setFontHeightInPoints((short) 12);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            // 把字体应用到当前的样式
            style.setFont(font);
            // 生成并设置另一个样式
            HSSFCellStyle style2 = workbook.createCellStyle();
            style2.setFillForegroundColor(HSSFColor.WHITE.index);
            style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            // 生成另一个字体
            HSSFFont font2 = workbook.createFont();
            font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
            // 把字体应用到当前的样式
            style2.setFont(font2);
            // 声明一个画图的顶级管理器
            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
            // 产生表格标题行
            HSSFRow row = sheet.createRow(0);
            for (int i = 0; i < headers.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(style);
                HSSFRichTextString text = new HSSFRichTextString(headers[i]);
                cell.setCellValue(text);
            }
    
            // 遍历集合数据,产生数据行
            Iterator<T> it = dataset.iterator();
            int index = 0;
            while (it.hasNext()) {
                index++;
                row = sheet.createRow(index);
                T t = it.next();
                // 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值
                Field[] fields = t.getClass().getDeclaredFields();
                for (int i = 0; i < fields.length; i++) {
                    HSSFCell cell = row.createCell(i);
                    cell.setCellStyle(style2);
                    Field field = fields[i];
                    String fieldName = field.getName();
                    String getMethodName = "get"
                            + fieldName.substring(0, 1).toUpperCase()
                            + fieldName.substring(1);
                    try {
                        Class tCls = t.getClass();
                        Method getMethod = tCls.getMethod(getMethodName,
                                new Class[]{});
                        Object value = getMethod.invoke(t, new Object[]{});
                        // 判断值的类型后进行强制类型转换
                        String textValue = null;
                        if (excelExport.containSpecialField(fieldName)) {
                            textValue = excelExport.getSpecialFieldValue(fieldName);
                        } else if (value instanceof Date) {
                            Date date = (Date) value;
                            SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                            textValue = sdf.format(date);
                        } else if (value instanceof Long) {
                            Date date = new Date((Long) value);
                            SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                            textValue = sdf.format(date);
                        } else if (value instanceof byte[]) {
                            // 有图片时,设置行高为60px;
                            row.setHeightInPoints(60);
                            // 设置图片所在列宽度为80px,注意这里单位的一个换算
                            sheet.setColumnWidth(i, (short) (35.7 * 80));
                            // sheet.autoSizeColumn(i);
                            byte[] bsValue = (byte[]) value;
                            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,
                                    1023, 255, (short) 6, index, (short) 6, index);
                            anchor.setAnchorType(2);
                            patriarch.createPicture(anchor, workbook.addPicture(
                                    bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
                        } else {
                            // 其它数据类型都当作字符串简单处理
                            if (value == null) {
                                textValue = "";
                            } else {
                                textValue = value.toString();
                            }
                        }
                        // 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成
                        if (textValue != null) {
                            Pattern p = Pattern.compile("^//d+(//.//d+)?$");
                            Matcher matcher = p.matcher(textValue);
                            if (matcher.matches()) {
                                // 是数字当作double处理
                                cell.setCellValue(Double.parseDouble(textValue));
                            } else {
                                HSSFRichTextString richString = new HSSFRichTextString(
                                        textValue);
                                HSSFFont font3 = workbook.createFont();
                                font3.setColor(HSSFColor.BLACK.index);
                                richString.applyFont(font3);
                                cell.setCellValue(richString);
                            }
                        }
                    } catch (SecurityException e) {
                        e.printStackTrace();
                    } catch (NoSuchMethodException e) {
                        e.printStackTrace();
                    } catch (IllegalArgumentException e) {
                        e.printStackTrace();
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    } catch (InvocationTargetException e) {
                        e.printStackTrace();
                    } finally {
                        // 清理资源
                    }
                }
            }
            try {
                workbook.write(out);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    

    导出类,传入IExcelExport接口(泛型),允许使用不同Model的Export实现,实现不同数据源的导出。

    export方法 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值,通过excelExport.containSpecialField 判断是否需要 自定义字段的特殊处理。其它的Style Font的设置可参考官方文档。

    • 执行数据测试

    mvn test -Dtest=ExcelTest#TestExcel

    测试将执行一个模拟数据的导出功能,并将导出的Excel重新解析 并打印出来。

    导出效果:

    cnbj-excel-export@2x.png

    Excel导入

    • 定义接口IExcelConsumer
    public interface IExcelConsumer {
        /**
         * 消费excel sheet
         *
         * @param sheet
         */
        void consume(Sheet sheet);
    }
    
    • 实现消费接口RecordComsumer
    package cn.daimaniu.blog.poi.impl.consumer;
    
    import cn.daimaniu.blog.poi.IExcelConsumer;
    import cn.daimaniu.blog.poi.model.RecordPoi;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    
    import java.util.ArrayList;
    import java.util.List;
    
    public class RecordComsumer implements IExcelConsumer {
        @Override
        public void consume(Sheet sheet) {
            List<RecordPoi> recordPois = new ArrayList<>();
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                if (i % 500 == 0) {
                    //在这里 处理 提取出来的数据
    //                recordPoiMapper.batchInsert(recordPois);
                    System.out.println("Excel解析出 size:" + recordPois.size() + "recordPois:" + recordPois.toString());
                    recordPois = null;
                    recordPois = new ArrayList<>();
                }
                Row row = sheet.getRow(i);
                RecordPoi recordPoi = new RecordPoi();
    
                recordPoi.setRecordId(Integer.parseInt(row.getCell(0).getStringCellValue()));
                recordPoi.setCname(row.getCell(1).getStringCellValue());
                recordPoi.setCbank(row.getCell(2).getStringCellValue());
                recordPoi.setCnum(row.getCell(3).getStringCellValue());
                recordPoi.setMoney(Integer.parseInt(row.getCell(4).getStringCellValue()));
                recordPoi.setType(Integer.parseInt(row.getCell(5).getStringCellValue()));
                recordPoi.setComment(row.getCell(6).getStringCellValue());
    
                recordPois.add(recordPoi);
            }
            if (recordPois != null && recordPois.size() > 0) {
                //在这里 处理 500除余,提取出来的数据
    //            recordPoiMapper.batchInsert(recordPois);
                System.out.println("Excel解析出 size:" + recordPois.size() + "recordPois:" + recordPois.toString());
                recordPois = null;
            }
        }
    }
    
    

    消费类只要依次读取Excel Row的Column数据,重写到Model对象中即可。

    Notice:在Excel导入过程中,一定要注意数据的有效性判断,比如Null,数据格式判断(Text,String,numeric,Date的区别...)

    • 执行数据测试

    mvn test -Dtest=ExcelTest#TestExcel

    测试将执行一个模拟数据的导出功能,并将导出的Excel重新解析 并打印出来。

    打印效果:


    cnbj-excel-import@2x.png

    参考列表

    本文源代码地址:https://github.com/daimaniu/cainiaobiji ,欢迎star 和 fork。

    下期预告

    下期预告(9月12号):Java日期处理实践。

    欢迎大家关注我,每周两篇原创技术文章。菜鸟笔记 下周一不见不散

    相关文章

      网友评论

      • FORGET_静哥哥:您好,我想向您请教一下,如果一个sheet存储的数量超过限制数量,怎么分页?(继续写入第二个sheet),还有一个问题,就是我的数据包含了多个类别,怎么样根据类别输出到不同的sheet中,谢谢!
        keepcleargas:这些都是 已经业务范畴的东西了,你既然懂得写入一个sheet,写入多个sheet方法是一致的。

      本文标题:菜鸟笔记(三) - Java Excel报表导入导出

      本文链接:https://www.haomeiwen.com/subject/blusettx.html