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

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

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

下面为代码

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();