admin管理员组文章数量:1648924
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;
}
}
本文标签: 转换为openofficeJavaPDFExcel
版权声明:本文标题:java使用openOffice将excel转换为pdf前,对excel进行预处理,将所有列显示在一页,将所有已经折叠的行全部展开 内容由热心网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:https://www.elefans.com/dianzi/1729504476a1203468.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论