java导出excel，炒鸡详细。踩坑千万不要用HSSFWorkbook工具类，不要生成xls，用XSSFWorkbook生成xlsx

最新推荐文章于 2024-04-21 12:15:03 发布

赵利伟@

最新推荐文章于 2024-04-21 12:15:03 发布

阅读量5.5k

点赞数 6

文章标签： excel java poi

本文链接：https://blog.csdn.net/weixin_43952697/article/details/107249935

版权

问题：

java导出excel是每个后端开发人员的必备能力，excel由两种后缀，前期的xls及后来的xlsx，大部分人可能不知道他们的区别，

HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls，导出的行数至多为65535行

XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx，最多导出1048576行，

SXSSFWorkbook 从POI 3.8版本开始，提供了一种基于XSSF的低内存占用的SXSSF方式。对于大型excel文件的创建，一个关键问题就是，要确保不会内存溢出。其实，就算生成很小的excel（比如几Mb），它用掉的内存是远大于excel文件实际的size的。如果单元格还有各种格式（比如，加粗，背景标红之类的），那它占用的内存就更多了。对于大型excel的创建且不会内存溢出的，就只有SXSSFWorkbook了。它的原理很简单，用硬盘空间换内存（就像hash map用空间换时间一样）。

我来给大家讲解一下最近的踩坑过程

首先这是我想要的结果，设置单元格后内容过多后可以自动换行。用HSSFWorkbook工具类导出后，用excel2003 2007 wps打开都没问题

但是用Excel2016和2019打开后如下图，what the fuck****。边框显示不全，并且也没有自动换行。思索前后果断放弃HSSF对应的xls，改为使用

XSSFWorkbook 对应的xlsx

直接上代码吧可以直接复制拿来用，改改里边的查询及表格的各个字段即可

public void downloadDeviceRepairExcel(HttpServletRequest request, HttpServletResponse response) {
        logMsg.setOperation("downLoadExcel");

        //解析request中的请求参数
        String areaLevel = request.getParameter("areaLevel");
        String areaName = request.getParameter("areaName");
        String areaUuid = request.getParameter("areaUuid");
        String startDate = request.getParameter("beginDate");
        String endDate = request.getParameter("endDate");

        synchronized (this) {
            //定义xls文件路径和名称
            String fileDir = System.getProperty("user.dir") + "/downLoad/" + areaName +
                    "_xxx报表_"+startDate+"~"+endDate+".xlsx";
            //定义第一个sheet表名
            String sheetName = "报修记录";
            //创建Excel工作簿对象
           /* HSSFWorkbook workbook = new HSSFWorkbook();*/
            XSSFWorkbook workbook = new XSSFWorkbook();

            //创建Excel工作表对象  不添加sheet时生成的xls文件打开时会报错)
//            HSSFSheet sheet = workbook.createSheet(sheetName);
            XSSFSheet sheet = workbook.createSheet(sheetName);

            //自适应宽度
            sheet.autoSizeColumn(0, true);
            //默认列宽
            sheet.setDefaultColumnWidth(20);
            //默认行高
            sheet.setDefaultRowHeightInPoints(18);

            //创建单元格样式
            /*HSSFCellStyle cellStyle = workbook.createCellStyle();*/
            XSSFCellStyle cellStyle = workbook.createCellStyle();

            //水平向左
            cellStyle.setAlignment(HorizontalAlignment.LEFT);
            //垂直居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //垂直
            //边框设置
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);

            //新建文件
            FileOutputStream fileOutputStream = null;
            File xlsFile = null;
            try {
                //返回行数
                this.deviceRepairDetailsExcel(sheet, workbook, areaLevel, areaUuid, areaName, startDate, endDate);
                //创建xls文件
                xlsFile = new File(fileDir);
                if (!xlsFile.getParentFile().exists()) {
                    boolean flag = xlsFile.getParentFile().mkdirs();
                    if (!flag) {
                        logMsg.setMsg("数据报表创建失败");
                        LogUtil.error(logMsg);
                    }
                }

                if (!xlsFile.exists()) {
                    try {
                        xlsFile.createNewFile();
                    } catch (IOException e) {
                        logMsg.setMsg("创建文件失败！");
                        logMsg.setE(e);
                        LogUtil.error(logMsg);
                    }
                }

                fileOutputStream = new FileOutputStream(fileDir);
                workbook.write(fileOutputStream);
            } catch (Exception e) {
                logMsg.setMsg("创建数据报表文件失败！");
                logMsg.setE(e);
                LogUtil.error(logMsg);
            } finally {
                try {
                    if (null != fileOutputStream) {
                        fileOutputStream.close();
                    }
                } catch (IOException e) {
                    logMsg.setMsg("FileOutputStream close error！");
                    logMsg.setE(e);
                    LogUtil.error(logMsg);
                }
            }

            try {
                //导出excel，文件名称乱码，所以用此方法
                DownLoadUtil.getFileForMessyCode(fileDir, request, response);
                //下载完成后删除文件
                if (null != xlsFile) {
                    xlsFile.delete();
                }
            } catch (IOException e) {
                logMsg.setMsg("下载完成报表之后删除文件异常");
                logMsg.setE(e);
                LogUtil.error(logMsg);
            }
        }
    }

具体导出方法

private int deviceRepairDetailsExcel(XSSFSheet  sheet, XSSFWorkbook  workbook, String areaLevel, String areaUuid, String areaName, String startDate, String endDate) {
        //获得总行数
        //定义开始的行号从开始
        int rowCount = sheet.getLastRowNum();
        //调用 
        List<Map<String, Object>> list = deviceRepairService.getRepairFormDetailsList(areaLevel, areaUuid, startDate, endDate);
        //查询设备类型
        List<Map<String, Object>> deviceTypeInfo = hDeviceTypeDao.getDeviceType();
        //定义标题的表格样式
       /* HSSFCellStyle titleCellStyle = workbook.createCellStyle();*/
        XSSFCellStyle titleCellStyle = workbook.createCellStyle();
        //水平
        titleCellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直
        titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleCellStyle.setBorderBottom(BorderStyle.THIN);
        titleCellStyle.setBorderLeft(BorderStyle.THIN);
        titleCellStyle.setBorderRight(BorderStyle.THIN);
        titleCellStyle.setBorderTop(BorderStyle.THIN);
        //字体
        /*HSSFFont titleFontText = workbook.createFont();*/
        XSSFFont titleFontText = workbook.createFont();
        titleFontText.setFontName("宋体");
        //设置字体大小
        titleFontText.setFontHeightInPoints((short) 20);
        //选择需要用到的字体格式
        titleCellStyle.setFont(titleFontText);

        //创建总标题行
        String title = "设备报修报表";
        if (!StringUtil.isEmpty(startDate) || !StringUtil.isEmpty(endDate)) {
            title = title + "_" + areaName + "_" + startDate + "~" + endDate;
        }
     /*   HSSFRow titleRow = sheet.createRow((short) rowCount);*/
        XSSFRow titleRow = sheet.createRow((short) rowCount);
      /*  HSSFCell cell8 = titleRow.createCell(0);*/
        XSSFCell cell8 = titleRow.createCell(0);
        cell8.setCellValue(title);
        //设置行高40
        titleRow.setHeightInPoints(25);
        cell8.setCellStyle(titleCellStyle);

        //定义小标题表格样式
       /* HSSFCellStyle smallTitleCellStyle = workbook.createCellStyle();*/
        XSSFCellStyle smallTitleCellStyle = workbook.createCellStyle();
        //水平
        smallTitleCellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直
        smallTitleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        smallTitleCellStyle.setBorderBottom(BorderStyle.THIN);
        smallTitleCellStyle.setBorderLeft(BorderStyle.THIN);
        smallTitleCellStyle.setBorderRight(BorderStyle.THIN);
        smallTitleCellStyle.setBorderTop(BorderStyle.THIN);
        //字体
        XSSFFont smallTitleFontText = workbook.createFont();
        /*HSSFFont smallTitleFontText = workbook.createFont();*/
        smallTitleFontText.setFontName("宋体");
        //设置字体大小
        smallTitleFontText.setFontHeightInPoints((short) 16);
        //选择需要用到的字体格式
        smallTitleCellStyle.setFont(smallTitleFontText);

        // 定义标题内容
        String[] smallTitle = {"省", "市", "区", "学校名称", "地址", "联系人", "联系人电话", "产品序列号", "产品类型", "产品名称", "故障现象", "故障描述", "报修时间"};
        /*HSSFRow smallTitleRow = sheet.createRow(rowCount + 1);*/
        XSSFRow smallTitleRow = sheet.createRow(rowCount + 1);
        //设置行高20
        smallTitleRow.setHeightInPoints(20);
        //合并单元格
        CellRangeAddress titleCra = new CellRangeAddress(rowCount, rowCount, 0, smallTitle.length - 1);
        sheet.addMergedRegion(titleCra);
        //给合并的单元格添加边框
        // 下边框
        RegionUtil.setBorderBottom(1, titleCra, sheet);
        // 左边框
        RegionUtil.setBorderLeft(1, titleCra, sheet);
        // 右边框
        RegionUtil.setBorderRight(1, titleCra, sheet);
        // 上边框
        RegionUtil.setBorderTop(1, titleCra, sheet);
        for (short j = 0; j < smallTitle.length; j++) {
           /* HSSFCell smallTitleCell = smallTitleRow.createCell(j);*/
            XSSFCell smallTitleCell = smallTitleRow.createCell(j);
            smallTitleCell.setCellStyle(smallTitleCellStyle);
            smallTitleCell.setCellValue(smallTitle[j]);
        }


        //定义详情的表格样式
        /*HSSFCellStyle detailsCellStyle = workbook.createCellStyle();*/
        XSSFCellStyle detailsCellStyle = workbook.createCellStyle();
        //水平
        detailsCellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直
        detailsCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        detailsCellStyle.setBorderBottom(BorderStyle.THIN);
        detailsCellStyle.setBorderLeft(BorderStyle.THIN);
        detailsCellStyle.setBorderRight(BorderStyle.THIN);
        detailsCellStyle.setBorderTop(BorderStyle.THIN);
        //字体
        XSSFFont detailsFontText = workbook.createFont();
       /* HSSFFont detailsFontText = workbook.createFont();*/
        detailsFontText.setFontName("宋体");
        //设置字体大小
        detailsFontText.setFontHeightInPoints((short) 15);
        //选择需要用到的字体格式
        detailsCellStyle.setFont(detailsFontText);
        //写入详情数据
        String[] key = {"provinceName", "cityName", "countyName", "schoolName", "address", "contactName", "contactMobile", "deviceSerialNo",
                "deviceType", "deviceName", "faultType", "faultContent", "createTime"};
        if (list.size() > 0) {
            for (int i = 0; i < list.size(); i++) {
                String deviceType = list.get(i).get("deviceType").toString();
                Map infoMap = list.get(i);
                for (int k=0;k<deviceTypeInfo.size();k++){
                    String deviceTypeInfos = deviceTypeInfo.get(k).get("deviceType").toString();
                    String deviceTypeName = deviceTypeInfo.get(k).get("deviceTypeName").toString();
                    if (deviceType.equals(deviceTypeInfos)){
                        infoMap.put("deviceType",deviceTypeName);
                    }
                }
                //创建第一行
                XSSFRow contentRow = sheet.createRow(i + rowCount + 2);
               /* HSSFRow contentRow = sheet.createRow(i + rowCount + 2);*/
                //设置行高
//                contentRow.setHeightInPoints(16);
                for (int j = 0; j < key.length; j++) {
                    //写入数据
                    /*HSSFCell contentCell = contentRow.createCell(j);*/
                    XSSFCell contentCell = contentRow.createCell(j);
                    contentCell.setCellStyle(detailsCellStyle);
                    contentCell.setCellValue(MapUtils.getString(list.get(i), key[j]));


                    //设置单元格自动换行
                    /*HSSFCellStyle cellStyle=workbook.createCellStyle();*/
                    XSSFCellStyle cellStyle=workbook.createCellStyle();
                    //边框设置
                    cellStyle.setBorderBottom(BorderStyle.THIN);
                    cellStyle.setBorderLeft(BorderStyle.THIN);
                    cellStyle.setBorderRight(BorderStyle.THIN);
                    cellStyle.setBorderTop(BorderStyle.THIN);
                    cellStyle.setWrapText(true);
                    contentCell.setCellStyle(cellStyle);
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                }
            }
        } else {
            
        }
        return list.size() + rowCount + 3;
    }

最后，不要在使用xls导出了，xlsx向下兼容，xls会有一些excel版本不支持的问题，另外导出里的代码都有注释，设置边框或者自动换行等，都有注释，各位可以自取

如果用XSSFWorkbook导致了内存溢出可以用相应的SXSSFWorkbook，核心代码如下

XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
SXSSFWorkbook workbook = new SXSSFWorkbook(xssfWorkbook , 1000);

//创建Excel工作表对象不添加sheet时生成的xls文件打开时会报错)
Sheet sheet = workbook.createSheet(sheetName);

//自适应宽度
// sheet.autoSizeColumn(0, true);
//默认列宽
sheet.setDefaultColumnWidth(20);
//默认行高
sheet.setDefaultRowHeightInPoints(18);

//创建单元格样式
/*HSSFCellStyle cellStyle = workbook.createCellStyle();*/
CellStyle cellStyle = workbook.createCellStyle();

合并单元格后再拆分

效果

CellRangeAddress titleCra1 = new CellRangeAddress(rowCount, rowCount, 0, 11 );
sheet.addMergedRegion(titleCra1);

CellRangeAddress titleCra = new CellRangeAddress(rowCount, rowCount, 12, smallTitle.length-1 );
sheet.addMergedRegion(titleCra);

rowCount：起始行，从0开始

XSSFCell cell2 = titleRow1.createCell(0);
cell2.setCellStyle(titleCellStyle);
cell2.setCellValue(title1);

XSSFCell cell = titleRow1.createCell(12);
cell.setCellStyle(titleCellStyle);
cell.setCellValue(title2);

将合并的单元格拆分并赋值

赵利伟@

关注

6
点赞
踩
31

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫