java使用libreoffice将excel转换pdf并解决列过多换行问题

换行问题"/>

java使用libreoffice将excel转换pdf并解决列过多换行问题

程序列表

1. apache poi 版本4.1.2 (可更换版本)

2. jodconverter-local 版本4.4.6 (可更换版本)

3. libreoffice 7.5.3(可更换版本)    安装可参考：中日韩字体 | LibreOffice 简体中文官方网站 - 自由免费的办公套件

4. jdk1.8+

转换原理

1. 使用poi设置源文件打印参数为列不换行，行自动换页,并获取每个sheet最大行宽（大概），并稍微调整每个行的高度，避免单元格文字压边框问题

2. 使用jodconverter配置文档转换尺寸，宽为每个sheet最大行宽，高为A4纸张高度（可自行调整）

pom文件部分代码

  <dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.2</version><scope>compile</scope></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version><scope>compile</scope></dependency><dependency><groupId>org.jodconverter</groupId><artifactId>jodconverter-local</artifactId><version>4.4.6</version></dependency>
​<dependency><groupId>org.libreoffice</groupId><artifactId>juh</artifactId><version>7.5.3</version></dependency><dependency><groupId>org.libreoffice</groupId><artifactId>jurt</artifactId><version>7.5.3</version></dependency><dependency><groupId>org.libreoffice</groupId><artifactId>ridl</artifactId><version>7.5.3</version></dependency><dependency><groupId>org.libreoffice</groupId><artifactId>unoil</artifactId><version>7.5.3</version></dependency>

java代码

导包
...
import com.sun.star.awt.Size;
import com.sun.star.beans.XPropertySet;
import com.sun.star.container.XIndexAccess;
import com.sun.star.container.XNameAccess;
import com.sun.star.container.XNameContainer;
import com.sun.star.document.XDocumentProperties;
import com.sun.star.document.XDocumentPropertiesSupplier;
import com.sun.star.lang.XComponent;
import com.sun.star.sheet.XSpreadsheet;
import com.sun.star.sheet.XSpreadsheetDocument;
import com.sun.star.sheet.XSpreadsheets;
import com.sun.star.style.XStyle;
import com.sun.star.style.XStyleFamiliesSupplier;
import org.apache.poi.ss.usermodel.*;
import org.checkerframework.checker.nullness.qual.NonNull;
import org.jodconverter.core.office.OfficeContext;
import org.jodconverter.local.LocalConverter;
import org.jodconverter.local.filter.Filter;
import org.jodconverter.local.filter.FilterChain;
import org.jodconverter.local.office.LocalOfficeManager;
import org.jodconverter.local.office.utils.Lo;
...
​
​
​
public static void main(String[] args) throws Exception {final File sourceFile = new File("C:\\Users\\yang\\Downloads\\test.xls");//获取文件后缀String fileName = sourceFile.getName();String fileType = fileName.substring(fileName.lastIndexOf("."));final File tempFile = new File(sourceFile.getParent() + File.separator + UUID.randomUUID() + fileType);//设置表格所有列一页打印，并获取每个页大概的最大列宽setExcelPrintParameters(sourceFile, tempFile);final File outputFile = new File("C:\\Users\\yang\\Downloads\\out.pdf");Map<Integer, Integer> parameters = setExcelPrintParameters(sourceFile, tempFile);LocalOfficeManager.Builder builder = LocalOfficeManager.builder()//libreoffice程序目录.officeHome("C:/Program Files/LibreOffice/")//服务监听地址.hostName("127.0.0.1")//本地监听端口，支持多个端口.portNumbers(2001, 2002)//工作目录（临时文件存储路径）.workingDir("D:/")//一个进程的超时时间.processTimeout(86400000L)//任务队列的超时时间.taskQueueTimeout(86400000L)//任务超时时间.taskExecutionTimeout(86400000L)//最大进程数.maxTasksPerProcess(10);LocalOfficeManager manager = builder.build();try {manager.start();LocalConverter.builder().officeManager(manager).filterChain(new MyFilter(parameters)).build().convert(tempFile).to(outputFile).execute();} finally {manager.stop();}FileUtils.deleteQuietly(tempFile);}
​
​public static class MyFilter implements Filter {private final Map<Integer, Integer> sheetWidthMap;
​public MyFilter(Map<Integer, Integer> mySizeMap) {this.sheetWidthMap = mySizeMap;}
​@Overridepublic void doFilter(@NonNull OfficeContext context, @NonNull XComponent document, @NonNull FilterChain chain) throws Exception {final XStyleFamiliesSupplier styleFamiliesSupplier = Lo.qi(XStyleFamiliesSupplier.class, document);final XNameAccess styleFamilies = Lo.qi(XNameAccess.class, styleFamiliesSupplier.getStyleFamilies());final XNameContainer pageStyleFamily = Lo.qi(XNameContainer.class, styleFamilies.getByName("PageStyles"));
​final XSpreadsheetDocument spreadsheetDocument = Lo.qi(XSpreadsheetDocument.class, document);final XSpreadsheets sheets = spreadsheetDocument.getSheets();final XIndexAccess indexedSheets = Lo.qi(XIndexAccess.class, sheets);final int count = indexedSheets.getCount();for (int i = 0; i < count; i++) {final XSpreadsheet sheet = Lo.qi(XSpreadsheet.class, indexedSheets.getByIndex(i));final XPropertySet sheetProperties = Lo.qi(XPropertySet.class, sheet);final String pageStyleName = (String) sheetProperties.getPropertyValue("PageStyle");final XStyle pageStyle = Lo.qi(XStyle.class, pageStyleFamily.getByName(pageStyleName));final XPropertySet pageStyleProps = Lo.qi(XPropertySet.class, pageStyle);pageStyleProps.setPropertyValue("IsLandscape", false);Integer maxWidth = sheetWidthMap.get(i);//设置pdf转换尺寸pageStyleProps.setPropertyValue("Size", new Size((maxWidth * 75) / 100, 29700));XDocumentProperties properties = Lo.qi(XDocumentPropertiesSupplier.class, document).getDocumentProperties();properties.setAuthor("wangy");properties.setGenerator("wangy");
​}}}
​/*** 设置Excel打印参数并获取sheet最大行宽度** @param sourceFile 源文件* @param targetFile 目标文件*/public static Map<Integer, Integer> setExcelPrintParameters(File sourceFile, File targetFile) {Map<Integer, Integer> resultMap = new HashMap<>();try {Workbook workbook = WorkbookFactory.create(sourceFile);
​for (int i = 0, j = workbook.getNumberOfSheets(); i < j; i++) {Sheet sheet = workbook.getSheetAt(i);int maxWidth = 0;int firstRowNum = sheet.getFirstRowNum();int lastRowNum = sheet.getLastRowNum();for (int k = firstRowNum; k <= lastRowNum; k++) {Row row = sheet.getRow(k);if (null == row) {continue;}int height = row.getHeight();row.setHeight((short) (height + 175));int firstCellNum = row.getFirstCellNum();int lastCellNum = row.getLastCellNum();int allWidth = 0;for (int l = firstCellNum; l < lastCellNum; l++) {Cell cell = row.getCell(l);if (null == cell) {continue;}int cellType = cell.getCellType();if (Cell.CELL_TYPE_BLANK == cellType) {continue;}
​int columnWidth = sheet.getColumnWidth(cell.getColumnIndex());allWidth += columnWidth;}if (allWidth > maxWidth) {maxWidth = allWidth;}}resultMap.put(i, maxWidth);sheet.setFitToPage(true);sheet.setAutobreaks(true);//所有列调整为同一页sheet.getPrintSetup().setFitWidth((short) 1);//行自动换页sheet.getPrintSetup().setFitHeight((short) 0);}try (FileOutputStream out = new FileOutputStream(targetFile)) {workbook.write(out);}} catch (Exception e) {e.printStackTrace();}return resultMap;}
​