jxl.jar 自己写的excelUtils帮助类 JXL导出Excel 表格

男娘i 2022-02-01 12:55 285阅读 0赞

由于公司有很多需要excel导出的操作，类似下面，有很多重复代码![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDQ4MzQ5_size_16_color_FFFFFF_t_70][]

于是我用建造者 的模式进行简单的优化一下，使用简单的单元格设置，比如单元格的居中方式，我是统一设置，可以改造代码实现建造的效果，如果涉及复杂的单元格样式，这个不适用

下面为代码

import jxl.CellView;
    import jxl.Workbook;
    import jxl.format.Alignment;
    import jxl.format.Colour;
    import jxl.format.UnderlineStyle;
    import jxl.format.VerticalAlignment;
    import jxl.write.*;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.OutputStream;
    import java.lang.reflect.Field;
    import java.util.List;
    
    /**
     * @Author: renbaojia
     * @CreateDate: 2019-05-14 10:01:04
     * @Description: Excel工具类
     */
    public class ExcelBuilder {
        private HttpServletResponse response;
        private WritableSheet sheet;
        private WritableCellFormat headerCellFormat;
        private WritableCellFormat bodyCellFormat;
        private WritableWorkbook book;
        private OutputStream outputStream;
        private final int DEFAULT_FONT_SIZE = 10;
        private final String DEFAULT_FONT_NAME = "宋体";
        private final int DEFAULT_CELL_WIDTH = 5000;
        /**
         * 头部字体
         */
        private WritableFont headerFont = new
                WritableFont(WritableFont.createFont(DEFAULT_FONT_NAME), DEFAULT_FONT_SIZE,
                WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.WHITE);
        /**
         * 内容字体
         */
        private WritableFont bodyFont = new
                WritableFont(WritableFont.createFont(DEFAULT_FONT_NAME), DEFAULT_FONT_SIZE,
                WritableFont.NO_BOLD);
    
        /**
         * 创建对象
         *
         * @return 实例化对象
         */
        public static ExcelBuilder create() {
            return new ExcelBuilder();
        }
    
        /**
         * @param response 请求
         * @return 返回this
         */
        public ExcelBuilder setResponse(HttpServletResponse response) {
            this.response = response;
            return this;
        }
    
        /**
         * @param fontName       字体名称
         * @param ps             大小
         * @param colour         颜色
         * @param underlineStyle 下划线
         * @param italic         是否加粗
         * @return this
         */
        public ExcelBuilder setBoldBodyFont(String fontName, int ps, Colour colour,
                                            UnderlineStyle underlineStyle, boolean italic) {
            bodyFont = new
                    WritableFont(WritableFont.createFont(fontName), ps,
                    WritableFont.BOLD, italic, underlineStyle, colour);
            return this;
        }
    
        /**
         * @param fontName       字体名称
         * @param ps             大小
         * @param colour         颜色
         * @param underlineStyle 下划线
         * @param italic         是否加粗
         * @return this
         */
        public ExcelBuilder setNoBoldBodyFont(String fontName, int ps, Colour colour,
                                              UnderlineStyle underlineStyle, boolean italic) {
            bodyFont = new
                    WritableFont(WritableFont.createFont(fontName), ps,
                    WritableFont.NO_BOLD, italic, underlineStyle, colour);
            return this;
        }
    
        /**
         * @param fontName       字体名称
         * @param ps             大小
         * @param colour         颜色
         * @param underlineStyle 下划线
         * @param italic         是否加粗
         * @return this
         */
        public ExcelBuilder setBoldHeaderFont(String fontName, int ps, Colour colour,
                                              UnderlineStyle underlineStyle, boolean italic) {
            bodyFont = new
                    WritableFont(WritableFont.createFont(fontName), ps,
                    WritableFont.BOLD, italic, underlineStyle, colour);
            return this;
        }
    
        /**
         * @param fontName       字体名称
         * @param ps             大小
         * @param colour         颜色
         * @param underlineStyle 下划线
         * @param italic         是否加粗
         * @return this
         */
        public ExcelBuilder setNoBoldHeaderFont(String fontName, int ps, Colour colour,
                                                UnderlineStyle underlineStyle, boolean italic) {
            bodyFont = new
                    WritableFont(WritableFont.createFont(fontName), ps,
                    WritableFont.NO_BOLD, italic, underlineStyle, colour);
            return this;
        }
    
        /**
         * @param title 设置文件名和标题
         * @return this
         * @throws Exception 异常
         */
        public ExcelBuilder setTitle(String title) throws Exception {
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + new String(title.getBytes("gbk"),
                            "iso8859-1") + ".xls");
            // 获取输出流
            outputStream = response.getOutputStream();
            book = Workbook.createWorkbook(outputStream);
            sheet = book.createSheet(title, 0);
    
    
            //头部单元格样式
            headerCellFormat = new WritableCellFormat(headerFont);
            //内容单元格样式
            bodyCellFormat = new WritableCellFormat(bodyFont);
            // 背景色
            headerCellFormat.setBackground(Colour.PALE_BLUE);
            //水平居中对齐
            headerCellFormat.setAlignment(Alignment.CENTRE);
            //竖直方向居中对齐
            headerCellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
            bodyCellFormat.setAlignment(Alignment.CENTRE);
            bodyCellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
    
    
            return this;
        }
    
        /**
         * 设置列
         *
         * @param header 第一列的字符串数组
         * @return this
         */
        public ExcelBuilder setHeader(String... header) {
            //单元格样式
            CellView cellView = new CellView();
            // 设置宽度
            cellView.setSize(DEFAULT_CELL_WIDTH);
            for (int i = 0; i < header.length; i++) {
                sheet.setColumnView(i, cellView);
                try {
                    sheet.addCell(new Label(i, 0, header[i], headerCellFormat));
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
            return this;
        }
    
        /**
         * 对传入的list进行反射获取属性存入sheet
         *
         * @param list 数据内容
         * @return this
         */
        public ExcelBuilder setList(Object list) {
            List a = (List) list;
            for (int i = 0; i < a.size(); i++) {
                Object obj = a.get(i);
                Class<?> clazz = obj.getClass();
                Field[] declaredFields = clazz.getDeclaredFields();
                for (int j = 0; j < declaredFields.length; j++) {
                    declaredFields[j].setAccessible(true);
                    try {
                        String value = (String) declaredFields[j].get(obj);
                        if (value != null) {
                            sheet.addCell(new Label(j, i + 1, value, bodyCellFormat));
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
            return this;
        }
    
        /**
         * 输出流
         */
        public void build() {
            try {
                book.write();
                book.close();
                outputStream.flush();
                outputStream.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
    
        }
    }

使用方法

//JdbcTemplate查询返回的list
    List<TouristExportDto> list = this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(TouristExportDto.class));
    
    
    ExcelBuilder.create().setResponse(response)
          .setTitle("标题")
          .setBoldHeaderFont("宋体", 20, Colour.WHITE, UnderlineStyle.NO_UNDERLINE, true)
          .setBoldBodyFont("宋体", 20, Colour.BLACK, UnderlineStyle.NO_UNDERLINE, true)
          .setHeader("英文名称", "中文名称", "性别", "国籍", "职业", "出生日期", "证件号码")
          .setList(Collections.singletonList(list).get(0)).build();

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDQ4MzQ5_size_16_color_FFFFFF_t_70]: /images/20220201/2003cb3ecde340478de786ac428cd5f8.png