一、概述
在文件处理中，经常有文件类型转换的使用场景；在这篇文章中，将使用poi以及itextpdf完成excel转pdf的操作。
二、依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>com.itextpdf</groupId>
<artifactId>itextpdf</artifactId>
<version>5.5.13</version>
</dependency>
<!-- hutool -->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.3.7</version>
</dependency>
三、示例
1.字体的处理
要有一个类，去资源文件中读取字体。
import com.itextpdf.text.pdf.BaseFont;
import java.util.HashMap;
import java.util.Map;
/**
* 读取字体
*/
public class FontGenerater {
// 符号
public final static String EMPTY = "";
public final static String FXG = "\\";
public final static String DIAN = ".";
//for report
public final static String HG = "-";
public final static String REPORT_UNDERLINE = "<r_u>";
public final static String REPORT_UNDERLINE_SUFFIX = "</r_u>";
public final static String NORMAL_CHAR_SET = "UTF-8";
// font
public static final short FONT_SMALL_SIZE = 8;
public static final int FONT_SMALL_VALUELENGTH = 12;
public static String FONT_URL = EMPTY;
public static Map FONTMAP = null;
public static final String FONT_HWXK_NAME = "华文行楷";
public static final String FONT_HWXK = "/STXINGKA.TTF";
public static final String FONT_FS_NAME = "仿宋";
public static final String FONT_FS_NAME_PINYIN = "FangSong";
public static final String FONT_FS = "\\simfang.ttf";
public static final String FONT_CALIBRI_NAME = "Calibri";
public static final String FONT_CALIBRI = "\\calibri.ttf";
public static final String FONT_CONSOLAS_NAME = "Consolas";
public static final String FONT_CONSOLAS = "\\consola.ttf";
public static final String FONT_DENGXIAN_NAME = "等线";
public static final String FONT_DENGXIAN = "\\Deng.ttf";
public static final String FONT_SONTI_NAME = "宋体";
public static final String FONT_SONTI = "\\simsun.ttc,0";
public static BaseFont getFont(String name) {
if (FONTMAP == null) {
FONT_URL = "D:\\website\\tplResource\\font";
FONTMAP = new HashMap<String, BaseFont>() {{
try {
put(FONT_SONTI_NAME, BaseFont.createFont(FONT_URL + FONT_SONTI, BaseFont.IDENTITY_H, BaseFont.EMBEDDED));
put(FONT_HWXK_NAME, BaseFont.createFont(FONT_URL + FONT_HWXK, BaseFont.IDENTITY_H, BaseFont.EMBEDDED));
put(FONT_FS_NAME, BaseFont.createFont(FONT_URL + FONT_FS, BaseFont.IDENTITY_H, BaseFont.EMBEDDED));
put(FONT_FS_NAME_PINYIN, BaseFont.createFont(FONT_URL + FONT_FS, BaseFont.IDENTITY_H, BaseFont.EMBEDDED));
put(FONT_CALIBRI_NAME, BaseFont.createFont(FONT_URL + FONT_CALIBRI, BaseFont.IDENTITY_H, BaseFont.EMBEDDED));
put(FONT_CONSOLAS_NAME, BaseFont.createFont(FONT_URL + FONT_CONSOLAS, BaseFont.IDENTITY_H, BaseFont.EMBEDDED));
put(FONT_DENGXIAN_NAME, BaseFont.createFont(FONT_URL + FONT_DENGXIAN, BaseFont.IDENTITY_H, BaseFont.EMBEDDED));
} catch (Exception e) {
}
}};
}
return (BaseFont) FONTMAP.get(name);
}
}
注意：这里的字体路径自己可以去config配置文件里面拿。
一些字体
2.excel转pdf的工具类(可以直接食用)
import cn.hutool.core.util.StrUtil;
import com.itextpdf.text.Font;
import com.itextpdf.text.*;
import com.itextpdf.text.pdf.BaseFont;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfPTable;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import xyz.xunfan.xbs.constant.FontGenerater;
import java.text.DecimalFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
public class Excel2Pdf {
public static PdfPTable excelToPdf(Workbook workbook, String filename, float pagewidth, int sheetindex) throws Exception {
Sheet sheet = workbook.getSheetAt(sheetindex);
float[] widths = getColWidth(sheet);
PdfPTable table = new PdfPTable(widths);
table.setWidthPercentage(90);
table.setLockedWidth(true);
table.setTotalWidth(pagewidth);
int colCount = widths.length;
for (int r = 0; r < sheet.getPhysicalNumberOfRows(); r++) {
Row row = sheet.getRow(r);
if (row != null) {
for (int c = 0; c < colCount; c++) {
Cell excelCell = row.getCell(c);
if (excelCell == null) {
excelCell = row.createCell(c);
}
String value = "";
if (excelCell != null) {
excelCell.setCellType(CellType.STRING);
value = excelCell.getStringCellValue() == null ? "" : excelCell.getStringCellValue();
}
org.apache.poi.ss.usermodel.Font excelFont = getExcelFont(workbook, excelCell, filename);
short fontsize = excelFont.getFontHeightInPoints();
if (!StrUtil.isEmpty(value) && value.length() > FontGenerater.FONT_SMALL_VALUELENGTH && value.split(FontGenerater.HG).length >= 3 && r <= 1) {
fontsize = FontGenerater.FONT_SMALL_SIZE;
}
Font pdFont = getFont(excelFont.getFontName(), fontsize, excelFont.getBold());
PdfPCell pCell = null;
if (value.indexOf(FontGenerater.REPORT_UNDERLINE) >= 0) {
pCell = new PdfPCell(new Phrase(FontGenerater.EMPTY, pdFont));
Paragraph para = new Paragraph();
String[] values = value.split(FontGenerater.REPORT_UNDERLINE_SUFFIX);
for (String v : values) {
if (v.indexOf(FontGenerater.REPORT_UNDERLINE) >= 0) {
v = v.replace(FontGenerater.REPORT_UNDERLINE, FontGenerater.EMPTY);
Chunk dateUnderline = new Chunk(v);
dateUnderline.setUnderline(0.1f, -2f);
para.add(dateUnderline);
} else {
para.add(new Chunk(v));
}
}
pCell.getPhrase().add(para);
} else {
pCell = new PdfPCell(new Phrase(value, pdFont));
}
List<PicturesInfo> infos = POIExtend.getAllPictureInfos(sheet, r, r, c, c, false);
if (!infos.isEmpty()) {
PicturesInfo info = infos.get(0);
Image img = Image.getInstance(infos.get(0).getPictureData());
img.scaleToFit(120, 120);
pCell = new PdfPCell(img);
pCell.setUseAscender(true);
pCell.setHorizontalAlignment(Element.ALIGN_CENTER);
pCell.setVerticalAlignment(Element.ALIGN_MIDDLE);
}
pCell.setBorder(0);
pCell.setHorizontalAlignment(getHorAglin(excelCell.getCellStyle().getAlignment().getCode()));
pCell.setVerticalAlignment(getVerAglin(excelCell.getCellStyle().getVerticalAlignment().getCode()));
pCell.setMinimumHeight(row.getHeightInPoints());
if (isMergedRegion(sheet, r, c)) {
int[] line = getMerged(sheet, r, c);
if (r == line[0]) {
pCell.setBorderWidthLeft(excelCell.getCellStyle().getBorderLeft().getCode());
pCell.setBorderWidthTop(excelCell.getCellStyle().getBorderTop().getCode());
}
if (line[1] == sheet.getPhysicalNumberOfRows() - 1) {
pCell.setBorderWidthBottom(sheet.getRow(line[1]).getCell(line[2]).getCellStyle().getBorderBottom().getCode());
}
int[] span = getMergedSpan(sheet, r, c);
if (span[0] == 1 && span[1] == 1) {//忽略合并过的单元格
continue;
}
pCell.setRowspan(span[0]);
pCell.setColspan(span[1]);
c = c + span[1] - 1;//合并过的列直接跳过
} else {
pCell.setBorderWidthTop(excelCell.getCellStyle().getBorderTop().getCode());
pCell.setBorderWidthLeft(excelCell.getCellStyle().getBorderLeft().getCode());
}
if (r == sheet.getPhysicalNumberOfRows() - 1) {
pCell.setBorderWidthBottom(excelCell.getCellStyle().getBorderBottom().getCode());
}
if (c == row.getPhysicalNumberOfCells() - 1) {
pCell.setBorderWidthRight(excelCell.getCellStyle().getBorderRight().getCode());
}
table.addCell(pCell);
}
} else {
PdfPCell pCell = new PdfPCell(new Phrase(FontGenerater.EMPTY));
pCell.setBorder(0);
pCell.setMinimumHeight(13);
table.addCell(pCell);
}
}
return table;
}
//获取字体
private static org.apache.poi.ss.usermodel.Font getExcelFont(Workbook workbook, Cell cell, String excelName) {
if (excelName.endsWith(".xls")) {
return ((HSSFCell) cell).getCellStyle().getFont(workbook);
}
return ((XSSFCell) cell).getCellStyle().getFont();
}
/**
* 判断excel单元格是否有边框
*
* @param excelCell
* @return
*/
private static boolean hasBorder(Cell excelCell) {
short top = excelCell.getCellStyle().getBorderTop().getCode();
short bottom = excelCell.getCellStyle().getBorderBottom().getCode();
short left = excelCell.getCellStyle().getBorderLeft().getCode();
short right = excelCell.getCellStyle().getBorderRight().getCode();
return top + bottom + left + right > 2;
}
private static void setBorder(Cell excelCell, PdfPCell pCell) {
pCell.setBorderWidthTop(excelCell.getCellStyle().getBorderTop().getCode());
pCell.setBorderWidthBottom(excelCell.getCellStyle().getBorderBottom().getCode());
pCell.setBorderWidthLeft(excelCell.getCellStyle().getBorderLeft().getCode());
pCell.setBorderWidthRight(excelCell.getCellStyle().getBorderRight().getCode());
}
/**
* 获取excel单元格数据显示格式
*
* @param dataFormat
* @return
* @throws Exception
*/
private static String getNumStyle(String dataFormat) throws Exception {
if (dataFormat == null || dataFormat.length() == 0) {
throw new Exception("");
}
if (dataFormat.indexOf("%") > -1) {
return dataFormat;
} else {
return dataFormat.substring(0, dataFormat.length() - 2);
}
}
/**
* 判断单元格是否是合并单元格
*
* @param sheet
* @param row
* @param column
* @return
*/
private static boolean isMergedRegion(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
return true;
}
}
}
return false;
}
private static int[] getMerged(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
int[] span = {0, 0, 0, 0};
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
span[0] = firstRow;
span[1] = lastRow;
span[2] = firstColumn;
span[3] = lastColumn;
break;
}
}
}
return span;
}
/**
* 计算合并单元格合并的跨行跨列数
*
* @param sheet
* @param row
* @param column
* @return
*/
private static int[] getMergedSpan(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
int[] span = {1, 1};
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if (firstColumn == column && firstRow == row) {
span[0] = lastRow - firstRow + 1;
span[1] = lastColumn - firstColumn + 1;
break;
}
}
return span;
}
/**
* 获取excel中每列宽度的占比
*
* @param sheet
* @return
*/
private static float[] getColWidth(Sheet sheet) {
int rowNum = getMaxColRowNum(sheet);
Row row = sheet.getRow(rowNum);
int cellCount = row.getPhysicalNumberOfCells();
int[] colWidths = new int[cellCount];
int sum = 0;
for (int i = 0; i < cellCount; i++) {
Cell cell = row.getCell(i);
if (cell != null) {
colWidths[i] = sheet.getColumnWidth(i);
sum += sheet.getColumnWidth(i);
}
}
float[] colWidthPer = new float[cellCount];
for (int i = 0; i < cellCount; i++) {
colWidthPer[i] = (float) colWidths[i] / sum * 100;
}
return colWidthPer;
}
/**
* 获取excel中列数最多的行号
*
* @param sheet
* @return
*/
private static int getMaxColRowNum(Sheet sheet) {
int rowNum = 0;
int maxCol = 0;
for (int r = 0; r < sheet.getPhysicalNumberOfRows(); r++) {
Row row = sheet.getRow(r);
if (row != null && maxCol < row.getPhysicalNumberOfCells()) {
maxCol = row.getPhysicalNumberOfCells();
rowNum = r;
}
}
return rowNum;
}
/**
* excel垂直对齐方式映射到pdf对齐方式
*
* @param aglin
* @return
*/
private static int getVerAglin(int aglin) {
switch (aglin) {
case 1:
return Element.ALIGN_MIDDLE;
case 2:
return Element.ALIGN_BOTTOM;
case 0:
return Element.ALIGN_TOP;
default:
return Element.ALIGN_MIDDLE;
}
}
/**
* excel水平对齐方式映射到pdf水平对齐方式
*
* @param aglin
* @return
*/
private static int getHorAglin(int aglin) {
switch (aglin) {
case 2:
return Element.ALIGN_CENTER;
case 3:
return Element.ALIGN_RIGHT;
case 1:
return Element.ALIGN_LEFT;
default:
return Element.ALIGN_CENTER;
}
}
/**
* 格式化数字
*
* @param pattern
* @param num
* @return
*/
private static String numFormat(String pattern, double num) {
DecimalFormat format = new DecimalFormat(pattern);
return format.format(num);
}
private static int[] getImgPostion(String imgKey) {
String[] arr = imgKey.split("_");
int[] position = new int[arr.length];
for (int i = 0; i < arr.length; i++) {
position[i] = Integer.parseInt(arr[i]);
}
return position;
}
public static Map<String, HSSFPictureData> getPictrues(HSSFWorkbook wb) {
Map<String, HSSFPictureData> map = new HashMap<String, HSSFPictureData>();
// getAllPictures方法只能获取不同的图片，如果Excel中存在相同的图片，只能得到一张图片
List<HSSFPictureData> pics = wb.getAllPictures();
if (pics.size() == 0) {
return map;
}
for (Integer sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
HSSFSheet sheet = wb.getSheetAt(sheetIndex);
HSSFPatriarch patriarch = sheet.getDrawingPatriarch();
if (patriarch == null) {
continue;
}
for (HSSFShape shape : patriarch.getChildren()) {
HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
if (shape instanceof HSSFPicture) {
HSSFPicture pic = (HSSFPicture) shape;
int picIndex = pic.getPictureIndex() - 1;
HSSFPictureData picData = pics.get(picIndex);
// 键格式：sheet索引_行号_列号_单元格内的上边距_单元格内的左边距_uuid
String key = sheetIndex + "_" + anchor.getRow1() + "_" + anchor.getCol1() + "_" + anchor.getRow2() + "_" + anchor.getCol2();
key += "_" + anchor.getDx1() + "_" + anchor.getDy1() + "_" + anchor.getDx2() + "_" + anchor.getDy2();
key += "_" + UUID.randomUUID();
map.put(key, picData);
}
}
}
return map;
}
public static Map<String, PictureData> getPictrues(Workbook wb, int type) {
Map<String, PictureData> map = new HashMap<String, PictureData>();
// getAllPictures方法只能获取不同的图片，如果Excel中存在相同的图片，只能得到一张图片
List<? extends PictureData> pics = wb.getAllPictures();
if (pics.size() == 0) {
return map;
}
for (Integer sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
Sheet sheet = wb.getSheetAt(sheetIndex);
Drawing<?> patriarch = sheet.getDrawingPatriarch();
if (patriarch == null) {
continue;
}
if (type == 2) {
HSSFPatriarch hssfpatriarch = (HSSFPatriarch) patriarch;
for (HSSFShape shape : hssfpatriarch.getChildren()) {
HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
if (shape instanceof HSSFPicture) {
HSSFPicture pic = (HSSFPicture) shape;
int picIndex = pic.getPictureIndex() - 1;
HSSFPictureData picData = (HSSFPictureData) pics.get(picIndex);
// 键格式：sheet索引_行号_列号_单元格内的上边距_单元格内的左边距_uuid
String key = sheetIndex + "_" + anchor.getRow1() + "_" + anchor.getCol1() + "_" + anchor.getRow2() + "_" + anchor.getCol2();
key += "_" + anchor.getDx1() + "_" + anchor.getDy1() + "_" + anchor.getDx2() + "_" + anchor.getDy2();
key += "_" + UUID.randomUUID();
map.put(key, picData);
}
}
} else if (type == 1) {
XSSFSheet xssfsheet = (XSSFSheet) sheet;
for (POIXMLDocumentPart dr : xssfsheet.getRelations()) {
if (dr instanceof XSSFDrawing) {
XSSFDrawing drawing = (XSSFDrawing) dr;
List<XSSFShape> shapes = drawing.getShapes();
for (XSSFShape shape : shapes) {
XSSFPicture pic = (XSSFPicture) shape;
XSSFPictureData data = pic.getPictureData();
XSSFClientAnchor aaa = pic.getClientAnchor();
CTMarker ccc = aaa.getFrom();
int ddd = ccc.getRow();
int eee = ccc.getCol();
// XSSFClientAnchor anchor = pic.getPreferredSize();
// CTMarker ctMarker = anchor.getFrom();
// String picIndex = String.valueOf(0) + "_" + ctMarker.getRow() + "_" + ctMarker.getCol();
String picIndex = String.valueOf(0) + "_" + ddd + "_" + eee;
map.put(picIndex, pic.getPictureData());
}
}
}
}
}
return map;
}
/**
* 单元格是否是图片的起始位置
*
* @return 单元格是否是图片的起始位置
*/
public static BaseColor parseBackgroundColor(Color hssfColor) {
if (hssfColor == null) {
// 白色
return new BaseColor(255, 255, 255);
}
short[] rgb = new short[]{255, 255, 255};
if (rgb[0] == 0 && rgb[1] == 0 && rgb[2] == 0) {
rgb = new short[]{255, 255, 255};
}
return new BaseColor(rgb[0], rgb[1], rgb[2]);
}
public static PdfPCell parseImgPCell(Cell cell) {
PdfPCell pdfpCell = new PdfPCell();
CellStyle cellStyle = cell.getCellStyle();
pdfpCell.setUseAscender(true);
// 水平对齐方式
int halign_itext = cellStyle.getAlignment().getCode();
//int halign_itext = parseHorizontalAlignmen(halign);
pdfpCell.setHorizontalAlignment(halign_itext);
// 垂直对齐方式
int valign_itext = cellStyle.getVerticalAlignment().getCode();
// int valign_itext = parseVerticalAlignment(valign);
pdfpCell.setVerticalAlignment(valign_itext);
// 填充色（背景色）
// HSSFColor backgroundColor = cellStyle.getFillBackgroundColorColor();
Color backgroundColor = cellStyle.getFillForegroundColorColor();
BaseColor backgroundColor_itext = parseBackgroundColor(backgroundColor);
pdfpCell.setBackgroundColor(backgroundColor_itext);
// 自动换行
boolean noWrap = !cellStyle.getWrapText();
pdfpCell.setNoWrap(noWrap);
// 边框样式
// 下边框
float borderWidth = cellStyle.getBorderBottom().getCode() / 32.00f;
//float borderWidth = borderStyle ;
pdfpCell.setBorderWidthBottom(borderWidth);
// 上框线
// borderStyle = cellStyle.getBorderTop();
pdfpCell.setBorderWidthTop(cellStyle.getBorderTop().getCode() / 32.00f);
// 左框线
// borderStyle = cellStyle.getBorderLeft();
pdfpCell.setBorderWidthLeft(cellStyle.getBorderLeft().getCode() / 32.00f);
// 右框线
//borderStyle = cellStyle.getBorderRight();
pdfpCell.setBorderWidthRight(cellStyle.getBorderRight().getCode() / 32.00f);
pdfpCell.normalize();
// pdfpCell.disableBorderSide(9);
return pdfpCell;
}
public static BaseColor parseColor(HSSFColor hssfColor) {
if (hssfColor == null) {
return new BaseColor(255, 255, 255);
}
short[] rgb = hssfColor.getTriplet();
return new BaseColor(rgb[0], rgb[1], rgb[2]);
}
public static Font getFont(String fontname, int heigth, boolean bold) throws Exception {
BaseFont font = FontGenerater.getFont(fontname);
if (font == null) {
font = (BaseFont) FontGenerater.FONTMAP.get(FontGenerater.FONT_SONTI_NAME);
}
return new Font(font, heigth, bold ? Font.BOLD : Font.NORMAL, BaseColor.BLACK);
}
}
3.excel生成pdf具体步骤
// 1.获取excel文件
XSSFWorkbook temp = new XSSFWorkbook("D:\\website\\tplResource\\file\\excel" + "/excel_export2.xlsx");
// 2.新建一个stream,用于绑定document
ByteArrayOutputStream stream = new ByteArrayOutputStream();
// 3.A4大小
RectangleReadOnly shuban = new RectangleReadOnly(PageSize.A4);
Document document = new Document(shuban);
// 4.获取一个pdfwriter实例
PdfWriter writer = PdfWriter.getInstance(document, stream);
// 5.打开document
document.open();
// 6.设置页边距
document.setMargins(20, 20, 30, 30);
// 7.新增页
document.newPage();
// 8.excel转pdf，并将处理后的内容添加到document中
document.add(Excel2Pdf.excelToPdf(temp, "excel_export2.xlsx", PageSize.A4.getWidth() - 150, 0));
// 9.关闭workbook document
temp.close();
document.close();
// 10.以byte[]方式获取pdf
byte[] pdfByte = stream.toByteArray();
stream.flush();
stream.reset();
stream.close();
// 11.保存pdf
String fileName = "/pdfdemo.pdf";
String filePath = "D:\\website\\tplResource\\test";
(new File(filePath)).mkdirs();
File file = new File(filePath + fileName);
if (file.exists()) {
file.delete();
}
FileOutputStream outputStream = new FileOutputStream(filePath + fileName);
outputStream.write(pdfByte);
outputStream.close();
excel转pdf示例
注意点：
A. 可控制pdf的长宽来完成横版/竖版；
方法：public RectangleReadOnly(float urx, float ury);
如果是横版：RectangleReadOnly banMian= new RectangleReadOnly(PageSize.A4.getHeight(), PageSize.A4.getWidth());
B.如果是多页，如何处理
document.newPage();加上document.add()就是新的一页；
C.要小心excel的内容在一页上放不下；此时，会自动产生第二页并把多余的内容放在第二页。
有不足之处，望留言指出！萌新上路，请多多指教。
2022.7.21 补充漏贴出的几个类
public class PicturesInfo {
private int minRow;
private int maxRow;
private int minCol;
private int maxCol;
private String ext;
private byte[] pictureData;
public PicturesInfo(int minRow, int maxRow, int minCol, int maxCol, byte[] pictureData, String ext) {
this.minRow = minRow;
this.maxRow = maxRow;
this.minCol = minCol;
this.maxCol = maxCol;
this.ext = ext;
this.pictureData = pictureData;
}
public byte[] getPictureData() {
return pictureData;
}
public void setPictureData(byte[] pictureData) {
this.pictureData = pictureData;
}
public int getMinRow() {
return minRow;
}
public void setMinRow(int minRow) {
this.minRow = minRow;
}
public int getMaxRow() {
return maxRow;
}
public void setMaxRow(int maxRow) {
this.maxRow = maxRow;
}
public int getMinCol() {
return minCol;
}
public void setMinCol(int minCol) {
this.minCol = minCol;
}
public int getMaxCol() {
return maxCol;
}
public void setMaxCol(int maxCol) {
this.maxCol = maxCol;
}
public String getExt() {
return ext;
}
public void setExt(String ext) {
this.ext = ext;
}
}
public class POIExtend {
public static List<PicturesInfo> getAllPictureInfos(Sheet sheet, boolean onlyInternal) throws Exception {
return getAllPictureInfos(sheet, null, null, null, null, onlyInternal);
}
public static List<PicturesInfo> getAllPictureInfos(Sheet sheet, Integer minRow, Integer maxRow, Integer minCol,
Integer maxCol, boolean onlyInternal) throws Exception {
if (sheet instanceof HSSFSheet) {
return getXLSAllPictureInfos((HSSFSheet) sheet, minRow, maxRow, minCol, maxCol, onlyInternal);
} else if (sheet instanceof XSSFSheet) {
return getXLSXAllPictureInfos((XSSFSheet) sheet, minRow, maxRow, minCol, maxCol, onlyInternal);
} else {
throw new Exception("未处理类型，没有为该类型添加：GetAllPicturesInfos()扩展方法！");
}
}
private static List<PicturesInfo> getXLSAllPictureInfos(HSSFSheet sheet, Integer minRow, Integer maxRow,
Integer minCol, Integer maxCol, Boolean onlyInternal) {
List<PicturesInfo> picturesInfoList = new ArrayList<>();
HSSFShapeContainer shapeContainer = sheet.getDrawingPatriarch();
if (null != shapeContainer) {
List<HSSFShape> shapeList = shapeContainer.getChildren();
for (HSSFShape shape : shapeList) {
if (shape instanceof HSSFPicture && shape.getAnchor() instanceof HSSFClientAnchor) {
HSSFPicture picture = (HSSFPicture) shape;
HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
if (isInternalOrIntersect(minRow, maxRow, minCol, maxCol, anchor.getRow1(), anchor.getRow2(),
anchor.getCol1(), anchor.getCol2(), onlyInternal)) {
picturesInfoList.add(
new PicturesInfo(anchor.getRow1(), anchor.getRow2(), anchor.getCol1(), anchor.getCol2(),
picture.getPictureData().getData(), picture.getPictureData().getMimeType()));
}
}
}
}
return picturesInfoList;
}
private static List<PicturesInfo> getXLSXAllPictureInfos(XSSFSheet sheet, Integer minRow, Integer maxRow,
Integer minCol, Integer maxCol, Boolean onlyInternal) {
List<PicturesInfo> picturesInfoList = new ArrayList<>();
List<POIXMLDocumentPart> documentPartList = sheet.getRelations();
for (POIXMLDocumentPart documentPart : documentPartList) {
if (documentPart instanceof XSSFDrawing) {
XSSFDrawing drawing = (XSSFDrawing) documentPart;
List<XSSFShape> shapes = drawing.getShapes();
for (XSSFShape shape : shapes) {
if (shape instanceof XSSFPicture) {
XSSFPicture picture = (XSSFPicture) shape;
// XSSFClientAnchor anchor = picture.getPreferredSize();
XSSFClientAnchor anchor = picture.getClientAnchor();
if (isInternalOrIntersect(minRow, maxRow, minCol, maxCol, anchor.getRow1(), anchor.getRow2(),
anchor.getCol1(), anchor.getCol2(), onlyInternal)) {
picturesInfoList.add(new PicturesInfo(anchor.getRow1(), anchor.getRow2(), anchor.getCol1(),
anchor.getCol2(), picture.getPictureData().getData(),
picture.getPictureData().getMimeType()));
}
}
}
}
}
return picturesInfoList;
}
private static boolean isInternalOrIntersect(Integer rangeMinRow, Integer rangeMaxRow, Integer rangeMinCol,
Integer rangeMaxCol, int pictureMinRow, int pictureMaxRow, int pictureMinCol, int pictureMaxCol,
Boolean onlyInternal) {
int _rangeMinRow = rangeMinRow == null ? pictureMinRow : rangeMinRow;
int _rangeMaxRow = rangeMaxRow == null ? pictureMaxRow : rangeMaxRow;
int _rangeMinCol = rangeMinCol == null ? pictureMinCol : rangeMinCol;
int _rangeMaxCol = rangeMaxCol == null ? pictureMaxCol : rangeMaxCol;
if (onlyInternal) {
return (_rangeMinRow <= pictureMinRow && _rangeMaxRow >= pictureMaxRow && _rangeMinCol <= pictureMinCol
&& _rangeMaxCol >= pictureMaxCol);
} else {
return ((Math.abs(_rangeMaxRow - _rangeMinRow) + Math.abs(pictureMaxRow - pictureMinRow) >= Math
.abs(_rangeMaxRow + _rangeMinRow - pictureMaxRow - pictureMinRow))
&& (Math.abs(_rangeMaxCol - _rangeMinCol) + Math.abs(pictureMaxCol - pictureMinCol) >= Math
.abs(_rangeMaxCol + _rangeMinCol - pictureMaxCol - pictureMinCol)));
}
}
}
网友评论