用poi把xls格式转换成xlsx格式
java中要实现excel新老格式的转换比较麻烦,开源库也没几个好用的。用ChatGpt查询也是推荐直接用POI,下面是借助ChatGPT写出来的代码,经过小小修改,格式转换良好,基本能用,就是效率比较低下。将就着用吧,哎!
/** * Excel格式从xls转换成xlsx格式 * * @param xlsInputStream xls格式的输入流 * @param xlsxOutputStream xlsx格式的输出流 */ public static void convertXlsToXlsxByStream(InputStream xlsInputStream, OutputStream xlsxOutputStream) { try { HSSFWorkbook oldWorkbook = new HSSFWorkbook(xlsInputStream); XSSFWorkbook newWorkbook = new XSSFWorkbook(); for (int i = 0; i < oldWorkbook.getNumberOfSheets(); i++) { HSSFSheet oldSheet = oldWorkbook.getSheetAt(i); XSSFSheet newSheet = newWorkbook.createSheet(oldSheet.getSheetName()); // 复制单元格值、样式和格式 for (int j = 0; j <= oldSheet.getLastRowNum(); j++) { HSSFRow oldRow = oldSheet.getRow(j); XSSFRow newRow = newSheet.createRow(j); if (oldRow != null) { for (int k = 0; k < oldRow.getLastCellNum(); k++) { HSSFCell oldCell = oldRow.getCell(k); XSSFCell newCell = newRow.createCell(k); if (oldCell != null) { try { setCellValue(newCell, oldCell); copyCellStyle(newWorkbook, newCell, oldWorkbook, oldCell); } catch (Exception ex) { log.warn("单元格拷贝异常:", ex); } } } } } // 复制单元格合并信息 List<CellRangeAddress> mergedRegions = oldSheet.getMergedRegions(); for (CellRangeAddress mergedRegion : mergedRegions) { CellRangeAddress targetMergedRegion = new CellRangeAddress( mergedRegion.getFirstRow(), mergedRegion.getLastRow(), mergedRegion.getFirstColumn(), mergedRegion.getLastColumn() ); newSheet.addMergedRegion(targetMergedRegion); } } newWorkbook.write(xlsxOutputStream); oldWorkbook.close(); newWorkbook.close(); } catch (Exception e) { log.error("excel格式转换(xls->xlsx)异常:", e); } } private static void setCellValue(XSSFCell newCell, HSSFCell oldCell) { if (oldCell == null) { return; } switch (oldCell.getCellType()) { case STRING: newCell.setCellValue(oldCell.getStringCellValue()); break; case NUMERIC: if (DateUtil.isCellDateFormatted(oldCell)) { newCell.setCellValue(oldCell.getDateCellValue()); } else { newCell.setCellValue(oldCell.getNumericCellValue()); } break; case BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case FORMULA: newCell.setCellValue(oldCell.getCellFormula()); break; default: } } private static void copyCellStyle(XSSFWorkbook xssfWorkbook, XSSFCell newCell, HSSFWorkbook hssfWorkbook, HSSFCell oldCell) { HSSFCellStyle oldCellStyle = oldCell.getCellStyle(); // 创建一个XSSFCellStyle(新Excel格式) XSSFCellStyle newCellStyle = xssfWorkbook.createCellStyle(); // 复制HSSFCellStyle的样式属性到XSSFCellStyle newCellStyle.setAlignment(oldCellStyle.getAlignment()); newCellStyle.setVerticalAlignment(oldCellStyle.getVerticalAlignment()); // 复制字体属性 XSSFFont newFont = xssfWorkbook.createFont(); HSSFFont oldFont = oldCellStyle.getFont(hssfWorkbook); newFont.setFontName(oldFont.getFontName()); newFont.setFontHeightInPoints(oldFont.getFontHeightInPoints()); newFont.setColor(oldFont.getColor()); newCellStyle.setFont(newFont); newCellStyle.setFillForegroundColor(oldCellStyle.getFillForegroundColor()); newCellStyle.setFillPattern(oldCellStyle.getFillPattern()); // 设置单元格类型 newCellStyle.setDataFormat(oldCellStyle.getDataFormat()); newCell.setCellStyle(newCellStyle); }
热门相关:藏娇记事 大妆 未来兽世:买来的媳妇,不生崽 酒吧 妻子的情人2