导出多级表头的Excel表格

导出样式

代码

前端Vue

handleAcceptExport() {
      this.$modal.confirm('是否确认导出有数据列表？').then(() => {
          this.exportLoading = true;
          return LicensingAcceptExport(this.queryParams).then(res => {
            const file = new Blob([res], { type: "application/vnd.ms-excel" });
            const url = URL.createObjectURL(file);
            const a = document.createElement("a");
            a.href = url;
            a.click();
            this.exportLoading = false;
          });
      });
    },

后端

实体

@Data
public class ExcelExportVo {

    // 这两个就是一级表头，最后一级表头对应的是具体的某个属性，它们都是被包裹在一级表头下的
    @ExcelCollection(name = "许可申请受理情况")
    private List<ApplyInfo> applyInfoList;

    @ExcelCollection(name = "许可续办受理情况")
    private List<RenewInfo> renewInfoList;

    /**
     * 许可申请受理情况
     */
    @Data
    public static class ApplyInfo{
        // 二级表头可以用 groupName 实现
        @Excel(name = "日期",width = 20)
        private String date;

        @Excel(name = "申请数", groupName = "申请情况")
        private Integer applyValue1;
        @Excel(name = "采编", groupName = "申请情况")
        private Integer applyCollection1;
        @Excel(name = "转载", groupName = "申请情况")
        private Integer applyReprint1;
        @Excel(name = "平台",groupName = "申请情况")
        private Integer applyPlatform1;
        @Excel(name = "申请数", groupName = "审核通过情况")
        private Integer applyValue2;
        @Excel(name = "采编", groupName = "审核通过情况")
        private Integer applyCollection2;
        @Excel(name = "转载", groupName = "审核通过情况")
        private Integer applyReprint2;
        @Excel(name = "平台",groupName = "审核通过情况")
        private Integer applyPlatform2;

        public ApplyInfo(String date, Integer applyValue1, Integer applyCollection1, Integer applyReprint1, Integer applyPlatform1, Integer applyValue2, Integer applyCollection2, Integer applyReprint2, Integer applyPlatform2) {
            this.date = date;
            this.applyValue1 = applyValue1;
            this.applyCollection1 = applyCollection1;
            this.applyReprint1 = applyReprint1;
            this.applyPlatform1 = applyPlatform1;
            this.applyValue2 = applyValue2;
            this.applyCollection2 = applyCollection2;
            this.applyReprint2 = applyReprint2;
            this.applyPlatform2 = applyPlatform2;
        }
    }

    /**
     * 许可续办受理情况
     */
    @Data
    public static class RenewInfo{
        @Excel(name = "申请数", groupName = "申请情况")
        private Integer renewValue1;
        @Excel(name = "采编", groupName = "申请情况")
        private Integer renewCollection1;
        @Excel(name = "转载", groupName = "申请情况")
        private Integer renewReprint1;
        @Excel(name = "平台",groupName = "申请情况")
        private Integer renewPlatform1;
        @Excel(name = "申请数", groupName = "审核通过情况")
        private Integer renewValue2;
        @Excel(name = "采编", groupName = "审核通过情况")
        private Integer renewCollection2;
        @Excel(name = "转载", groupName = "审核通过情况")
        private Integer renewReprint2;
        @Excel(name = "平台",groupName = "审核通过情况")
        private Integer renewPlatform2;

        public RenewInfo(Integer renewValue1, Integer renewCollection1, Integer renewReprint1, Integer renewPlatform1, Integer renewValue2, Integer renewCollection2, Integer renewReprint2, Integer renewPlatform2) {
            this.renewValue1 = renewValue1;
            this.renewCollection1 = renewCollection1;
            this.renewReprint1 = renewReprint1;
            this.renewPlatform1 = renewPlatform1;
            this.renewValue2 = renewValue2;
            this.renewCollection2 = renewCollection2;
            this.renewReprint2 = renewReprint2;
            this.renewPlatform2 = renewPlatform2;
        }
    }
}

方法

@GetMapping("/LicensingAcceptExport")
    public void LicensingAcceptExport(HttpServletResponse response, StatisSearchVo statis) {
        List<String> nameList;
        if (null != statis && null != statis.getBeginTime() && null != statis.getEndTime()) {
            // 获取间隔时间。
            nameList = DateUtil.getDateList(statis.getBeginTime(), statis.getEndTime(), 20);
        } else {
            // 为空获取前20天时间。
            nameList = DateUtil.getDateList(new Date(), DateUtil.DIRECTION_NEGATIVE, 20);
        }

        String beginTime = nameList.get(0);
        String endTime = nameList.get(nameList.size() - 1);
        //查询单位注册审核柱图
        String provinceId = statis.getProvinceId();
        String[] provinceIdArray = null;
        if (null != statis.getProvinceId()) {
            provinceIdArray = provinceId.split("，");
        }

        List<ExcelExportVo> dataList = new ArrayList<>();
        List<ExcelExportVo.ApplyInfo> applyInfoList = new ArrayList<>();
        List<ExcelExportVo.RenewInfo> renewInfoList = new ArrayList<>();

        StatisTaskVo taskVo = new StatisTaskVo();
        taskVo.setProvinceIdArray(provinceIdArray);
        taskVo.setBeginTime(beginTime);
        taskVo.setEndTime(endTime);
        //许可情况申请、续办受理数据列表
         List<LicenseAcceptanceVo> acceptanceVo = statisTaskService.getApplyandRenew(taskVo);
        SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd");
        for (LicenseAcceptanceVo lists : acceptanceVo) {
            String transformDate = simpleDateFormat.format(lists.getStatisDate());
            applyInfoList.add(new ExcelExportVo.ApplyInfo(transformDate, lists.getApplySubmitCount(), lists.getApplySubmitCollectionCount(),
                    lists.getApplySubmitReprintCount(), lists.getApplySubmitPlatformCount(), lists.getApplyPassCount(), lists.getApplyPassCollectionCount(),
                    lists.getApplyPassReprintCount(), lists.getApplyPassPlatformCount()));
            renewInfoList.add(new ExcelExportVo.RenewInfo(lists.getRenewSubmitCount(), lists.getRenewSubmitCollectionCount(), lists.getRenewSubmitReprintCount(),
                    lists.getRenewSubmitPlatformCount(), lists.getRenewPassCount(), lists.getRenewPassCollectionCount(), lists.getRenewPassReprintCount(),
                    lists.getRenewPassPlatformCount()));
        }

        ExcelExportVo vo = new ExcelExportVo();
        vo.setApplyInfoList(applyInfoList);
        vo.setRenewInfoList(renewInfoList);
        dataList.add(vo);

        // 第一个sheet
        ExportParams params1 = new ExportParams();
        params1.setStyle(ExcelStyleUtil.class);
        params1.setSheetName("sheet1");
        params1.setTitle("许可受理情况");
        Map<String, Object> params1Map = new HashMap<>();
        params1Map.put("title", params1);
        params1Map.put("entity", ExcelExportVo.class);
        params1Map.put("data", dataList);

        // 将sheet封装
        List<Map<String, Object>> sheetsList = new ArrayList<>();
        sheetsList.add(params1Map);
        Workbook workBook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
        ExcelUtilTwo.downLoadExcel(response, workBook);
    }

//多个sheet
 // 第二个sheet
        ExportParams params2 = new ExportParams();
        params2.setStyle(ExcelStyleUtil.class);
        params2.setSheetName("用户信息2");
        Map<String, Object> params2Map = new HashMap<>();
        params2Map.put("title", params2);
        params2Map.put("entity", ExcelExportVo.class);
        params2Map.put("data", new ArrayList<>());

        // 第三个sheet
        ExportParams params3 = new ExportParams();
        params3.setStyle(ExcelStyleUtil.class);
        params3.setSheetName("用户信息3");
        Map<String, Object> params3Map = new HashMap<>();
        params3Map.put("title", params3);
        params3Map.put("entity", ExcelExportVo.class);
        params3Map.put("data", new ArrayList<>());
		
		// 将3个sheet封装
        List<Map<String, Object>> sheetsList = new ArrayList<>();
        sheetsList.add(params1Map);
        sheetsList.add(params2Map);
        sheetsList.add(params3Map);
        Workbook workBook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
        ExcelUtil.downLoadExcel("用户信息",response,workBook);
    }

工具类ExcelUtil、ExcelStyleUtil

/**
 * excel操作工具类
 */
public class ExcelUtil {

    /**
     * 偶数行设置背景色
     */
    private static void setRowBackground(Workbook workbook){
        Sheet sheet = workbook.getSheetAt(0);
        CellStyle styles = ExcelStyleUtil.getStyles(workbook,false,(short) 12);
        for(int i = 0; i <= sheet.getLastRowNum(); i ++) {
            Row row = sheet.getRow(i);
            if (i%2==0){
                for(int j = 0; j < row.getPhysicalNumberOfCells(); j ++) {
                    Cell cell = row.getCell(j);
                    cell.setCellStyle(styles);
                }
            }
        }
    }

    /**
     * 导出设置隔行背景色
     * @param params 导出参数
     * @param list 数据
     * @param pojoClass pojo类型
     * @param isSetRowBackground 是否设置隔行背景色
     */
    public static void exportExcel(ExportParams params, List<?> list, Class<?> pojoClass, boolean isSetRowBackground, HttpServletResponse response){
        Workbook workbook = ExcelExportUtil.exportExcel(params,pojoClass,list);
        if (workbook != null);
        if (isSetRowBackground) setRowBackground(workbook);
        downLoadExcel(response, workbook);
    }

    /**
     * excel 导出
     * @param list           数据
     * @param title          标题
     * @param sheetName      sheet名称
     * @param pojoClass      pojo类型
     * @param fileName       文件名称
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response){
        ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
        exportParams.setStyle(ExcelStyleUtil.class);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * excel 导出
     * @param list           数据
     * @param pojoClass      pojo类型
     * @param fileName       文件名称
     */
    public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response){
        ExportParams exportParams = new ExportParams();
        exportParams.setStyle(ExcelStyleUtil.class);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * list map 导出
     * @param list     数据
     * @param fileName 文件名称
     */
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
        ExportParams exportParams = new ExportParams();
        exportParams.setStyle(ExcelStyleUtil.class);
        defaultExport(list, fileName, response);
    }

    /**
     * 默认的 excel 导出
     * @param list         数据
     * @param pojoClass    pojo类型
     * @param fileName     文件名称
     * @param exportParams 导出参数
     */
    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams){
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        downLoadExcel(response, workbook);
    }

    /**
     * 默认的 excel 导出
     * @param list     数据
     * @param fileName 文件名称
     */
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.XSSF);
        downLoadExcel(response, workbook);
    }

    /**
     * 下载
     * @param response
     * @param workbook excel数据
     */
    public static void downLoadExcel(HttpServletResponse response, Workbook workbook){
        try {
            // 格式化时间。
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddhhmmssSSS");
            // 获取当前时间。
            Date date = new Date();
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String((sdf.format(date) + ".xls").getBytes(), "ISO8859-1") + "\"");
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * excel 导入
     * @param filePath   excel文件路径
     * @param titleRows  标题行
     * @param headerRows 表头行
     * @param pojoClass  pojo类型
     */
    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setNeedSave(true);
        params.setSaveUrl("/excel/");
        try {
            return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("模板不能为空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 导入
     * @param file       上传的文件
     * @param titleRows  标题行
     * @param headerRows 表头行
     * @param needVerify 是否检验excel内容
     * @param pojoClass  pojo类型
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerify, Class<T> pojoClass) throws IOException {
        if (file == null) {
            return null;
        }
        try {
            return importExcel(file.getInputStream(), titleRows, headerRows, needVerify, pojoClass);
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 导入
     * @param inputStream 文件输入流
     * @param titleRows   标题行
     * @param headerRows  表头行
     * @param needVerify  是否检验excel内容
     * @param pojoClass   pojo类型
     */
    public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, boolean needVerify, Class<T> pojoClass) throws IOException {
        if (inputStream == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setSaveUrl("upload/excel/");
        params.setNeedSave(true);
        params.setNeedVerify(needVerify);
        try {
            return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("excel文件不能为空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * Excel 类型枚举
     */
    enum ExcelTypeEnum {
        XLS("xls"), XLSX("xlsx");
        private String value;

        ExcelTypeEnum(String value) {
            this.value = value;
        }

        public String getValue() {
            return value;
        }

        public void setValue(String value) {
            this.value = value;
        }
    }


    /**
     * 上传文件，返回一个workbook
     * @param file
     */
    public static Workbook importExcel(MultipartFile file) throws IOException {
        File toFile = new File(file.getOriginalFilename());
        Workbook workbook = null;
        if(toFile.getPath().endsWith("xls")){
            workbook = new HSSFWorkbook(file.getInputStream());
        }else if(toFile.getPath().endsWith("xlsx")){
            workbook = new XSSFWorkbook(file.getInputStream());
        }else {
            throw new RuntimeException("请确认你上传的文件类型");
        }
        return workbook;
    }

    /**
     * 读取指定sheet的数据
     * @param file 上传的文件
     * @param sheetName 要读取的sheetName
     * @param titleRows 表头行数
     * @param headRows 标题行数
     * @param startRows 表头之前有多少行不要的数据，从1开始，忽略空行
     * @param readRows 要读取多少行数据，从0开始，比如读取十行，值就是9; 不指定时默认为0
     * @param pojoClass 实体
     */
    public static <T> List<T> importExcel(MultipartFile file,String sheetName,Integer titleRows,Integer headRows, Integer startRows,Integer readRows,Class<T> pojoClass) throws Exception {
        Workbook workbook = importExcel(file);
        int numberOfSheets = workbook.getNumberOfSheets();
        List<T> list = null;
        for (int i = 0; i < numberOfSheets; i++) {
            String name = workbook.getSheetName(i).trim();
            if (name.equals(sheetName) || name.endsWith(sheetName)){
                ImportParams params = new ImportParams();
                params.setTitleRows(titleRows);
                params.setHeadRows(headRows);
                params.setStartRows(startRows);
                params.setReadRows(readRows);
                //第几个sheet页
                params.setStartSheetIndex(i);
                final ExcelImportService excelImportService = new ExcelImportService();
                ExcelImportResult<T> result = excelImportService.importExcelByIs(file.getInputStream(), pojoClass, params, false);
                list = result.getList();
                break;
            }
        }
        return list;
    }

    /**
     * 以map的形式导出表格
     * @param list 数据
     */
    public static <T> List<Map<String, Object>> objectToMap(List<T> list){
        List<Map<String, Object>> result = new ArrayList<>();
        Map<String, Object> map = null;
        try {
            for (T item : list) {
                map = new HashMap<>();
                Class<?> clazz = item.getClass();
                for (Field field : clazz.getDeclaredFields()) {
                    field.setAccessible(true);
                    String fieldName = field.getName();
                    Object value = field.get(item);
                    map.put(fieldName, value);
                }
                result.add(map);
            }
            return result;
        }catch (IllegalAccessException e){
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 动态导出列，根据Excel注解获取列的字段注释（表头名）、宽度
     * @param clazz
     * @param fields 选择要导出的列
     * @param changeHead 要更改表头的列，格式是{"字段1":"更改的表头1","字段2":"更改的表头2"}
     */
    public static List<ExcelExportEntity> dynamicExport(Class<?> clazz,String fields, JSONObject changeHead) {
        List<ExcelExportEntity> beanList = new ArrayList<>();
        String[] split = fields.split(",");
        int length = split.length;
        try {
            for (int i = 0; i < length; i++) {
                Field f = clazz.getDeclaredField(split[i]);
                Excel annotation = f.getAnnotation((Excel.class));
                String comment = annotation.name();
                if (changeHead != null && Objects.nonNull(changeHead.get(f.getName()))){
                    comment = changeHead.get(f.getName()).toString();
                }
                Double width = annotation.width();
                beanList.add(new ExcelExportEntity(comment, f.getName(),width.intValue()));
            }
        }catch (NoSuchFieldException e){
            e.printStackTrace();
        }
        return beanList;
    }

    /**
     * 动态导出列（选择要忽略的列），根据Excel注解获取列的字段注释（表头名）、宽度
     * @param clazz
     * @param fields 选择要忽略的列
     * @param changeHead 要更改表头的列，格式是{"字段名1":"更改的表头1","字段名2":"更改的表头2"}
     */
    public static List<ExcelExportEntity> dynamicIgnoreExport(Class<?> clazz, String fields, JSONObject changeHead) {
        List<ExcelExportEntity> beanList = new ArrayList<>();
        String[] split = fields.split(",");
        int length = split.length;
        Field[] declaredFields = clazz.getDeclaredFields();
        for (int i = 0; i < length; i++) {
            for (Field f : declaredFields) {
                if (f.getName().equals(split[i])) continue;
                Excel annotation = f.getAnnotation((Excel.class));
                if (annotation != null){
                    String comment = annotation.name();
                    if (changeHead != null && Objects.nonNull(changeHead.get(f.getName()))){
                        comment = changeHead.get(f.getName()).toString();
                    }
                    Double width = annotation.width();
                    beanList.add(new ExcelExportEntity(comment, f.getName(),width.intValue()));
                }
            }
        }
        return beanList;
    }

    /**
     * 导出Excel，并在最后追加图片
     * @param sheetName sheet名称
     * @param wb        HSSFWorkbook对象
     */
    public static Workbook getWorkbook(String sheetName,Workbook wb, String imgUrl) throws IOException {
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        Sheet sheet = wb.getSheet(sheetName);
        /*生成图表*/
        if(!StringUtils.isEmpty(imgUrl)) {
            String[] imgUrlArr = imgUrl.split("base64,");//拆分base64编码后部分
            byte[] buffer = new BASE64Decoder().decodeBuffer(imgUrlArr[1]);
            String picPath = System.getProperty("user.dir")+"\\upload\\image\\pic.png";
            File file = new File(picPath);//图片文件
            try {
                //生成图片
                OutputStream out = new FileOutputStream(file);//图片输出流
                out.write(buffer);
                out.flush();//清空流
                out.close();//关闭流
                ByteArrayOutputStream outStream = new ByteArrayOutputStream(); // 将图片写入流中
                BufferedImage bufferImg = ImageIO.read(new File(picPath));
                ImageIO.write(bufferImg, "PNG", outStream);
                // 利用HSSFPatriarch将图片写入EXCEL
                Drawing<?> drawing = sheet.createDrawingPatriarch();
                //位置：第1个单元格中x轴的偏移量、第1个单元格中y轴的偏移量、 第2个单元格中x轴的偏移量、 第2个单元格中y轴的偏移量、第1个单元格的列号、第1个单元格的行号、 第2个单元格的列号、第2个单元格的行号
                //HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 0, 8, (short) 10, 40);
                ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 6, 9, 40);
                drawing.createPicture(anchor, wb.addPicture(outStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
            } catch (Exception ex) {
                ex.printStackTrace();
            }
            if (file.exists()) {
                file.delete();//删除图片
            }
        }
        return wb;
    }
}

/**
 * @Description: excel导出样式设置工具类
 */
public class ExcelStyleUtil implements IExcelExportStyler {

    private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");
    private static final short FONT_SIZE_TEN = 10;
    private static final short FONT_SIZE_ELEVEN = 10;
    private static final short FONT_SIZE_TWELVE = 12;

    /**
     * 大标题样式
     */
    private CellStyle headerStyle;
    /**
     * 每列标题样式
     */
    private CellStyle titleStyle;
    /**
     * 数据行样式
     */
    private CellStyle styles;

    public ExcelStyleUtil(Workbook workbook) {
        this.init(workbook);
    }

    /**
     * 初始化样式
     * @param workbook
     */
    private void init(Workbook workbook) {
        this.headerStyle = initHeaderStyle(workbook);
        this.titleStyle = initTitleStyle(workbook,true,FONT_SIZE_ELEVEN);
        this.styles = initStyles(workbook);
    }

    @Override
    public CellStyle getHeaderStyle(short i) {
        return headerStyle;
    }

    @Override
    public CellStyle getTitleStyle(short i) {
        return titleStyle;
    }

    @Override
    public CellStyle getTemplateStyles(boolean b, ExcelForEachParams excelForEachParams) {
        return null;
    }

    @Override
    public CellStyle getStyles(boolean b, ExcelExportEntity excelExportEntity) {
        return styles;
    }

    @Override
    public CellStyle getStyles(Cell cell, int i, ExcelExportEntity entity, Object o, Object o1) {
        return getStyles(true, entity);
    }

    /**
     * 设置隔行背景色
     */
    public static CellStyle getStyles(Workbook workbook,boolean isBold,short size) {
        CellStyle style = initTitleStyle(workbook,isBold,size);
        style.setDataFormat(STRING_FORMAT);
        return style;
    }

    /**
     * 初始化--大标题样式
     */
    private static CellStyle initHeaderStyle(Workbook workbook) {
        CellStyle style = getBaseCellStyle(workbook);
        style.setFont(getFont(workbook, FONT_SIZE_TWELVE, true));
        return style;
    }

    /**
     * 初始化--每列标题样式
     */
    private static CellStyle initTitleStyle(Workbook workbook,boolean isBold,short size) {
        CellStyle style = getBaseCellStyle(workbook);
        style.setFont(getFont(workbook, size, isBold));
        //背景色
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return style;
    }

    /**
     * 初始化--数据行样式
     */
    private static CellStyle initStyles(Workbook workbook) {
        CellStyle style = getBaseCellStyle(workbook);
        style.setFont(getFont(workbook, FONT_SIZE_TEN, false));
        style.setDataFormat(STRING_FORMAT);
        return style;
    }

    /**
     * 基础样式
     */
    private static CellStyle getBaseCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        //下边框
        style.setBorderBottom(BorderStyle.THIN);
        //左边框
        style.setBorderLeft(BorderStyle.THIN);
        //上边框
        style.setBorderTop(BorderStyle.THIN);
        //右边框
        style.setBorderRight(BorderStyle.THIN);
        //水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        //上下居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置自动换行
        style.setWrapText(true);
        return style;
    }

    /**
     * 字体样式
     * @param size   字体大小
     * @param isBold 是否加粗
     */
    private static Font getFont(Workbook workbook, short size, boolean isBold) {
        Font font = workbook.createFont();
        //字体样式
        font.setFontName("Arial");
        //是否加粗
        font.setBold(isBold);
        //字体大小
        font.setFontHeightInPoints(size);
        return font;
    }
}