导出excel文件设置单元格不是文本字符串格式

最新推荐文章于 2024-06-17 11:32:14 发布

qiaozhangchi

最新推荐文章于 2024-06-17 11:32:14 发布

阅读量1.3k

点赞数

分类专栏：编程日志java 文章标签： excel java poi

本文链接：https://blog.youkuaiyun.com/qiaozhangchi/article/details/114528642

版权

编程日志java 专栏收录该内容

52 篇文章

订阅专栏

导出excel文件单元格默认文本字符串格式，有些字段是数字想计算需要时数字格式才行，下面就是从网上学来的方法

首先类里字段注解加类型

 @Excel(name = "计量价格", orderNum = "10",type=10)
    private Integer totalPrice;

导出过程简单封装

public static <T> void exportFile(String title, String fileName, List<T> list, Class<T> cla, HttpServletResponse response,
        HttpServletRequest request, boolean titleFlg) {
        try {
            String agent = request.getHeader("USER-AGENT");
            String excelName = null;
            if (agent != null && agent.toLowerCase().indexOf("firefox") > 0) {
                excelName = "attachment;filename=" + new String(fileName.getBytes("GB2312"), "ISO-8859-1") + ".xls";
            } else {
                excelName = "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls";
            }
            // 告诉浏览器用什么软件可以打开此文件
            response.setHeader("content-Type", "application/vnd.ms-excel");
            // 下载文件的默认名称
            response.setHeader("Content-Disposition", excelName);
            ExportParams exportParams = new ExportParams(title, fileName);
            exportParams.setStyle(ExcelExportStatisticStyler.class);
            if (!titleFlg) {
                exportParams.setTitle(null);
            }
            log.info("exportParams {}",exportParams.getSheetName());
            log.info("导出Excel数据数量{}", list.size());
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, cla, list);
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            log.error("信息导出失败。异常={}", e.getMessage());
        }
    }

导出格式类的简单实现

public class ExcelExportStatisticStyler   extends ExcelExportStylerDefaultImpl {

    private CellStyle numberCellStyle;

    public ExcelExportStatisticStyler(Workbook workbook) {
        super(workbook);
        createNumberCellStyler();
    }

    private void createNumberCellStyler() {
        numberCellStyle = workbook.createCellStyle();
        numberCellStyle.setAlignment(HorizontalAlignment.CENTER);
        numberCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        numberCellStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("0.00"));
        numberCellStyle.setWrapText(true);
    }

    @Override
    public CellStyle getStyles(boolean noneStyler, ExcelExportEntity entity) {
        if (entity != null
                && 10==entity.getType()) {
            return numberCellStyle;
        }
        return super.getStyles(noneStyler, entity);
    }
}

在需要用到地方调用

public  void exportBill(HttpServletResponse response, HttpServletRequest request, Integer billId, String cost){
        StringBuffer title = new StringBuffer("导出计费明细 合计金额").append(cost).append("  生成时间：").append(DateUtil.formatDateTime(new Date()));
               List<InfoVo> reportVos = baseMapper.selectById(billId);       
        DownloadAndUploadUtil.exportFile(title.toString(), "计费明细报表", reportVos, InfoVo.class, response, request, true);     
    }

参考文章 https://blog.youkuaiyun.com/enthan809882/article/details/106529620