xls 转为 Xlsx的工具类
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.usermodel.*;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Slf4j
public class XlsToXlsxUtil {
public static ByteArrayOutputStream xlsToXlsx(InputStream inputStream) throws IOException {
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
Workbook workbook = WorkbookFactory.create(inputStream);
HSSFWorkbook xlsWorkbook = null;
XSSFWorkbook xlsxWorkbook = null;
if (workbook instanceof HSSFWorkbook){
xlsWorkbook = (HSSFWorkbook) workbook;
xlsxWorkbook = new XSSFWorkbook();
}else if (workbook instanceof XSSFWorkbook){
//直接返回流本身
xlsxWorkbook = (XSSFWorkbook) workbook;
xlsxWorkbook.write(outputStream);
return outputStream;
}else {
log.error("xls 文件转换 xlsx 文件异常,文件类型错误:{}",workbook.getClass());
throw new BusinessException("xls 文件转换 xlsx 文件异常,文件类型错误!");
}
try {
int sheetNum = xlsWorkbook.getNumberOfSheets();
for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) {
HSSFSheet hssfSheet = xlsWorkbook.getSheetAt(sheetIndex);
XSSFSheet xssfSheet = xlsxWorkbook.createSheet(hssfSheet.getSheetName());
copySheets(hssfSheet, xssfSheet);
}
xlsxWorkbook.write(outputStream);
} catch (Exception e) {
log.error("文件转换异常", e);
throw new IOException(e);
}
return outputStream;
}
public static void copySheets(HSSFSheet source, XSSFSheet destination) {
int maxColumnNum = 0;
// 获取全部的合并单元格
List<CellRangeAddress> cellRangeAddressList = source.getMergedRegions();
for (int i = source.getFirstRowNum(); i <= source.getLastRowNum(); i++) {
if (i < 0 || i > 1048576){
break;
}
HSSFRow srcRow = source.getRow(i);
XSSFRow destRow = destination.createRow(i);
if (srcRow != null) {
// 拷贝行
copyRow(destination, srcRow, destRow, cellRangeAddressList);
if (srcRow.getLastCellNum() > maxColumnNum) {
maxColumnNum = srcRow.getLastCellNum();
}
}
}
for (int i = 0; i <= maxColumnNum; i++) {
destination.setColumnWidth(i, source.getColumnWidth(i));
}
// 拷贝图片
copyPicture(source, destination);
// 拷贝下拉选择
try {
copySelect(source, destination);
} catch (Exception e) {
log.error("拷贝下拉异常", e);
}
}
private static void copySelect(HSSFSheet source, XSSFSheet destination) {
List<HSSFDataValidation> dataValidations = source.getDataValidations();
for(HSSFDataValidation validation: dataValidations) {
CellRangeAddressList addressList = validation.getRegions();
if (addressList == null || addressList.countRanges() == 0) {
continue;
}
for (int itemIndex = 0; itemIndex < addressList.countRanges(); itemIndex ++) {
int row = addressList.getCellRangeAddress(itemIndex).getFirstRow();
int column = addressList.getCellRangeAddress(itemIndex).getFirstColumn();
CellRangeAddressList regions = new CellRangeAddressList(addressList.getCellRangeAddress(itemIndex).getFirstRow(), addressList.getCellRangeAddress(itemIndex).getLastRow(),
addressList.getCellRangeAddress(itemIndex).getFirstColumn(), addressList.getCellRangeAddress(itemIndex).getLastColumn());
if (row > -1 && column > -1) {
DataValidationConstraint constraint = validation.getValidationConstraint();
if (constraint.getExplicitListValues() != null) {
DataValidationConstraint constraintnew =
destination
.getDataValidationHelper()
.createExplicitListConstraint(constraint.getExplicitListValues());
DataValidation dataValidation =
destination.getDataValidationHelper().createValidation(constraintnew, regions);
destination.addValidationData(dataValidation);
}else{
DataValidation dataValidation =
destination.getDataValidationHelper().createValidation(constraint, regions);
destination.addValidationData(dataValidation);
}
}
}
}
}
// 拷贝行
public static void copyRow(XSSFSheet destSheet, HSSFRow srcRow, XSSFRow destRow,
List<CellRangeAddress> cellRangeAddressList) {
// 拷贝行高
destRow.setHeight(srcRow.getHeight());
for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
if (j < 0) {
continue;
}
HSSFCell oldCell = srcRow.getCell(j);
XSSFCell newCell = destRow.getCell(j);
if (oldCell != null) {
if (newCell == null) {
newCell = destRow.createCell(j);
}
// 拷贝单元格
copyCell(oldCell, newCell, destSheet);
// 获取原先的合并单元格
CellRangeAddress mergedRegion = getMergedRegion(cellRangeAddressList, srcRow.getRowNum(),
(short) oldCell.getColumnIndex());
if (mergedRegion != null) {
// 参照创建合并单元格
CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(),
mergedRegion.getLastRow(), mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
destSheet.addMergedRegion(newMergedRegion);
}
}
}
}
// 拷贝单元格
public static void copyCell(HSSFCell oldCell, XSSFCell newCell, XSSFSheet destSheet) {
HSSFCellStyle sourceCellStyle = oldCell.getCellStyle();
XSSFCellStyle targetCellStyle = destSheet.getWorkbook().createCellStyle();
if (sourceCellStyle == null) {
sourceCellStyle = oldCell.getSheet().getWorkbook().createCellStyle();
}
targetCellStyle.setFillForegroundColor(sourceCellStyle.getFillForegroundColor());
// 设置对齐方式
targetCellStyle.setAlignment(sourceCellStyle.getAlignment());
targetCellStyle.setVerticalAlignment(sourceCellStyle.getVerticalAlignment());
// 设置字体
XSSFFont xssfFont = destSheet.getWorkbook().createFont();
HSSFFont hssfFont = sourceCellStyle.getFont(oldCell.getSheet().getWorkbook());
copyFont(xssfFont, hssfFont);
targetCellStyle.setFont(xssfFont);
// 文本换行
targetCellStyle.setWrapText(sourceCellStyle.getWrapText());
targetCellStyle.setBorderBottom(sourceCellStyle.getBorderBottom());
targetCellStyle.setBorderLeft(sourceCellStyle.getBorderLeft());
targetCellStyle.setBorderRight(sourceCellStyle.getBorderRight());
targetCellStyle.setBorderTop(sourceCellStyle.getBorderTop());
targetCellStyle.setBottomBorderColor(sourceCellStyle.getBottomBorderColor());
targetCellStyle.setFillBackgroundColor(sourceCellStyle.getFillBackgroundColor());
targetCellStyle.setFillPattern(sourceCellStyle.getFillPattern());
targetCellStyle.setHidden(sourceCellStyle.getHidden());
targetCellStyle.setIndention(sourceCellStyle.getIndention());
targetCellStyle.setLeftBorderColor(sourceCellStyle.getLeftBorderColor());
targetCellStyle.setLocked(sourceCellStyle.getLocked());
targetCellStyle.setQuotePrefixed(sourceCellStyle.getQuotePrefixed());
targetCellStyle.setReadingOrder(ReadingOrder.forLong(sourceCellStyle.getReadingOrder()));
targetCellStyle.setRightBorderColor(sourceCellStyle.getRightBorderColor());
targetCellStyle.setRotation(sourceCellStyle.getRotation());
//设置单元格格式 映射需要从XSSFDataFormat中获取
String dataFormatString = oldCell.getCellStyle().getDataFormatString();
XSSFDataFormat dataFormat = destSheet.getWorkbook().createDataFormat();
short format = dataFormat.getFormat(dataFormatString);
targetCellStyle.setDataFormat(format);
newCell.setCellStyle(targetCellStyle);
switch (oldCell.getCellType().getCode()) {
case 1:
newCell.setCellValue(oldCell.getStringCellValue());
break;
case 0:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
case 3:
newCell.setCellType(CellType.BLANK);
break;
case 4:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
case 5:
newCell.setCellErrorValue(oldCell.getErrorCellValue());
break;
case 2:
newCell.setCellFormula(oldCell.getCellFormula());
break;
default:
break;
}
}
// 拷贝字体设置
public static void copyFont(XSSFFont xssfFont, HSSFFont hssfFont) {
xssfFont.setFontName(hssfFont.getFontName());
xssfFont.setBold(hssfFont.getBold());
xssfFont.setFontHeight(hssfFont.getFontHeight());
xssfFont.setCharSet(hssfFont.getCharSet());
xssfFont.setColor(hssfFont.getColor());
xssfFont.setItalic(hssfFont.getItalic());
xssfFont.setUnderline(hssfFont.getUnderline());
xssfFont.setTypeOffset(hssfFont.getTypeOffset());
xssfFont.setStrikeout(hssfFont.getStrikeout());
}
// 根据行列获取合并单元格
public static CellRangeAddress getMergedRegion(List<CellRangeAddress> cellRangeAddressList, int rowNum, short cellNum) {
for (int i = 0; i < cellRangeAddressList.size(); i++) {
CellRangeAddress merged = cellRangeAddressList.get(i);
if (merged.isInRange(rowNum, cellNum)) {
// 已经获取过不再获取
cellRangeAddressList.remove(i);
return merged;
}
}
return null;
}
// 拷贝图片
public static void copyPicture(HSSFSheet source, XSSFSheet destination) {
// 获取sheet中的图片信息
List<Map<String, Object>> mapList = getPicturesFromHSSFSheet(source);
XSSFDrawing drawing = destination.createDrawingPatriarch();
for (Map<String, Object> pictureMap: mapList) {
HSSFClientAnchor hssfClientAnchor = (HSSFClientAnchor) pictureMap.get("pictureAnchor");
HSSFRow startRow = source.getRow(hssfClientAnchor.getRow1());
float startRowHeight = startRow == null ? source.getDefaultRowHeightInPoints() : startRow.getHeightInPoints();
HSSFRow endRow = source.getRow(hssfClientAnchor.getRow1());
float endRowHeight = endRow == null ? source.getDefaultRowHeightInPoints() : endRow.getHeightInPoints();
// hssf的单元格,每个单元格无论宽高,都被分为 宽 1024个单位 高 256个单位。
// 32.00f 为默认的单元格单位宽度 单元格宽度 / 默认宽度 为像素宽度
XSSFClientAnchor xssfClientAnchor = drawing.createAnchor(
(int) (source.getColumnWidth(hssfClientAnchor.getCol1()) / 32.00f
/ 1024 * hssfClientAnchor.getDx1() * Units.EMU_PER_PIXEL),
(int) (startRowHeight / 256 * hssfClientAnchor.getDy1() * Units.EMU_PER_POINT),
(int) (source.getColumnWidth(hssfClientAnchor.getCol2()) / 32.00f
/ 1024 * hssfClientAnchor.getDx2() * Units.EMU_PER_PIXEL),
(int) (endRowHeight / 256 * hssfClientAnchor.getDy2() * Units.EMU_PER_POINT),
hssfClientAnchor.getCol1(),
hssfClientAnchor.getRow1(),
hssfClientAnchor.getCol2(),
hssfClientAnchor.getRow2());
xssfClientAnchor.setAnchorType(hssfClientAnchor.getAnchorType());
drawing.createPicture(xssfClientAnchor,
destination.getWorkbook().addPicture((byte[])pictureMap.get("pictureByteArray"),
Integer.parseInt(pictureMap.get("pictureType").toString())));
System.out.println("imageInsert");
}
}
/**
* 获取图片和位置 (xls)
*/
public static List<Map<String, Object>> getPicturesFromHSSFSheet (HSSFSheet sheet) {
List<Map<String, Object>> mapList = new ArrayList<>();
if (sheet == null || sheet.getDrawingPatriarch() == null) {
return mapList;
}
List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
for (HSSFShape shape : list) {
if (shape instanceof HSSFPicture) {
Map<String, Object> map = new HashMap<>();
HSSFPicture picture = (HSSFPicture) shape;
HSSFClientAnchor cAnchor = picture.getClientAnchor();
HSSFPictureData pdata = picture.getPictureData();
map.put("pictureAnchor", cAnchor);
map.put("pictureByteArray", pdata.getData());
map.put("pictureType", pdata.getPictureType());
map.put("pictureSize", picture.getImageDimension());
mapList.add(map);
}
}
return mapList;
}
}