java导出excel，炒鸡详细。踩坑千万不要用HSSFWorkbook工具类，不要生成xls，用XSSFWorkbook生成xlsx |
您所在的位置：网站首页 › xls文件打开报错 › java导出excel，炒鸡详细。踩坑千万不要用HSSFWorkbook工具类，不要生成xls，用XSSFWorkbook生成xlsx |
java导出excel，炒鸡详细。踩坑千万不要用HSSFWorkbook工具类，不要生成xls，用XSSFWorkbook生成xlsx
|
问题： 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 list = deviceRepairService.getRepairFormDetailsList(areaLevel, areaUuid, startDate, endDate); //查询设备类型 List 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 |
【本文地址】
今日新闻 |
推荐新闻 |