java使用openOffice将excel转换为pdf前，对excel进行预处理，将所有列显示在一页，将所有已经折叠的行全部展开_openoffice excel

 
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFPrintSetup;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
 
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
 
/**
 * When converting to excel, change the page size first.
 * 2023-08-04
 *
 * @author LiZhongxin
 */
@Component
public class ExcelConverterRefreshFilter {
 
    /**
     * 修改excel页面大小
     */
    public void refresh(String oldFilePath, String oldType, String newFilePath) throws IOException {
        Workbook workbook = null;
        if (oldType.equals("xls")) {
            workbook = new HSSFWorkbook(new FileInputStream(oldFilePath));
        } else if (oldType.equals("xlsx")) {
            workbook = new XSSFWorkbook(new FileInputStream(oldFilePath));
        }
        assert workbook != null;
        int sheetCount = workbook.getNumberOfSheets();
        for (int i = 0; i < sheetCount; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            //setp1: 将所有列显示在一页上.
            setPageSize(sheet, oldType);
            //step 2: 将所有行全部展开。
            setRowHeight(sheet);
            //这是官方写法，自动行高，好像碰到单元格合并的就不好使了，所有下边算法是我自己写的
//            XSSFRow xssfRow = (XSSFRow) sheet.getRow(j);
//            xssfRow.getCTRow().setCustomHeight(false);
        }
        workbook.write(new FileOutputStream(newFilePath));
    }
 
    private void setPageSize(Sheet sheet, String oldType) {
        if (oldType.equals("xls")) {
            //set all columns to appear on one page.
            HSSFPrintSetup printSetup = (HSSFPrintSetup) sheet.getPrintSetup();
            sheet.setAutobreaks(true);
            printSetup.setFitWidth((short) 1);
            printSetup.setFitHeight((short) 0);
        } else if (oldType.equals("xlsx")) {
            //set all columns to appear on one page.
            XSSFPrintSetup printSetup = (XSSFPrintSetup) sheet.getPrintSetup();
            printSetup.setFitHeight((short) 0);
            sheet.setFitToPage(true);
        }
    }
 
 
    private void setRowHeight(Sheet sheet) {
        //1.获取出现次数最多的行高作为一个基准.
        int mostRowHeight = getMostRowHeight(sheet);
        //2.计算出上下冗余.
        int redundancy = mostRowHeight > 0 ? mostRowHeight / 2 : 0;
        for (int j = 0; j < sheet.getLastRowNum(); j++) {
            Row row = sheet.getRow(j);
            //3.获取每行行高.
            int rowHeight = row.getHeight();
            int newRowHeight = 0;
            int cellNum = row.getLastCellNum();
            for (int k = 0; k < cellNum; k++) {
                Cell cell = row.getCell(k);
                //4.判断每行是否在大多数行的上限范围内.
                if ((rowHeight - mostRowHeight >= 0 && rowHeight - mostRowHeight <= redundancy) || (rowHeight - mostRowHeight <= 0 && rowHeight - mostRowHeight >= redundancy)) {
                    System.out.println(cell.toString());
                    //5.判断每个单元格中是否存在换行.
                    String[] state = cell.toString().split("\n");
                    //6.如果有换行，则行高乘以换行数.
                    if (state.length > 1) {
                        newRowHeight = state.length * rowHeight;
                    }
                }
            }
            //7.设置新的行高.
            if (newRowHeight != 0) {
                row.setHeight((short) newRowHeight);
            }
        }
    }
    
 
    private int getMostRowHeight(Sheet sheet) {
        //1.索取所有行高.
        Map<Integer, Integer> rowHeightMap = new HashMap<>();
        for (int j = 0; j < sheet.getLastRowNum(); j++) {
            Row row = sheet.getRow(j);
            int rowHeight = row.getHeight();
            //2.判断map中是否存在此行高。如果存在，则将行高的值设置为+1。如果不存在，请将行高的值设置为1
            if (rowHeightMap.containsKey(rowHeight)) {
                rowHeightMap.put(rowHeight, rowHeightMap.get(rowHeight) + 1);
            } else {
                rowHeightMap.put(rowHeight, 1);
            }
        }
        //3.从map中获取最大值，即出现次数最多的行的高度。
        int mostRowHeight = 0;
        int mostRowHeightCount = 0;
        for (Map.Entry<Integer, Integer> entry : rowHeightMap.entrySet()) {
            if (entry.getValue() > mostRowHeightCount) {
                mostRowHeightCount = entry.getValue();
                mostRowHeight = entry.getKey();
            }
        }
        return mostRowHeight;
    }
}