目录
🍭前言:
excel 有文字有图片 实现转pdf
🍭 下面是示例代码 :
要将包含文字和图片的Excel文件转换为PDF文件,可以使用Apache POI和iText库的组合。
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import javax.imageio.ImageIO;
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.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFGraphicFrame;
import org.apache.poi.xssf.usermodel.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.itextpdf.text.Document;
import com.itextpdf.text.DocumentException;
import com.itextpdf.text.Image;
import com.itextpdf.text.PageSize;
import com.itextpdf.text.pdf.PdfWriter;
public class ExcelToPdfDemo {
public static void main(String[] args) {
try (Workbook workbook = WorkbookFactory.create(new File("example.xlsx"));
FileOutputStream fos = new FileOutputStream("example.pdf")) {
// 创建PDF文档对象
Document document = new Document(PageSize.A4, 50, 50, 50, 50);
// 创建PDF输出流
PdfWriter writer = PdfWriter.getInstance(document, fos);
// 打开PDF文档
document.open();
// 遍历Excel工作表
for (Sheet sheet : workbook) {
// 创建PDF表格对象
PdfPTable table = new PdfPTable(sheet.getRow(0).getLastCellNum());
table.setHeaderRows(1);
table.setWidths(new float[] {1, 2, 2, 2});
// 设置表格宽度
table.setWidthPercentage(100);
// 设置表格标题
Paragraph title = new Paragraph(sheet.getSheetName(), new Font(BaseFont.createFont("STSong-Light", "UniGB-UCS2-H", BaseFont.NOT_EMBEDDED), 16, Font.BOLD));
title.setAlignment(Element.ALIGN_CENTER);
document.add(title);
// 添加表格内容
for (Row row : sheet) {
for (Cell cell : row) {
PdfPCell pdfCell = new PdfPCell(new Paragraph(cell.getStringCellValue(), new Font(BaseFont.createFont("STSong-Light", "UniGB-UCS2-H", BaseFont.NOT_EMBEDDED), 12)));
pdfCell.setBorderWidth(1f);
pdfCell.setBorderColor(BaseColor.BLACK);
pdfCell.setPadding(5f);
if (cell.getRowIndex() == 0) {
pdfCell.setBackgroundColor(BaseColor.LIGHT_GRAY);
}
table.addCell(pdfCell);
// 处理图片
if (cell.getCellType() == CellType.BLANK) {
continue;
}
if (cell.getCellType() == CellType.FORMULA) {
cell = cell.getSheet().getRow(cell.getRowIndex()).getCell(cell.getColumnIndex(), MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (cell == null) {
continue;
}
}
if (cell.getCellType() == CellType.ERROR) {
continue;
}
if (cell.getCellType() == CellType.STRING && cell.getStringCellValue().trim().isEmpty()) {
continue;
}
if (cell.getCellType() == CellType.NUMERIC && cell.getNumericCellValue() == 0) {
continue;
}
if (cell.getCellType() == CellType.BOOLEAN && !cell.getBooleanCellValue()) {
continue;
}
if (cell.getCellType() == CellType.STRING && cell.getStringCellValue().startsWith("#")) {
continue;
}
if (cell.getCellType() == CellType.FORMULA && cell.getCellFormula().startsWith("#")) {
continue;
}
if (cell.getCellType() == CellType.STRING && cell.getStringCellValue().startsWith("=")) {
continue;
}
if (cell.getCellType() == CellType.FORMULA && cell.getCellFormula().startsWith("=")) {
continue;
}
if (cell.getCellType() == CellType.STRING && cell.getStringCellValue().startsWith("@")) {
continue;
}
if (cell.getCellType() == CellType.FORMULA && cell.getCellFormula().startsWith("@")) {
continue;
}
if (cell.getCellType() == CellType.STRING && cell.getStringCellValue().startsWith("'")) {
continue;
}
if (cell.getCellType() == CellType.FORMULA && cell.getCellFormula().startsWith("'")) {
continue;
}
// 处理图片
if (cell.getCellType() == CellType.BLANK) {
continue;
}
if (cell.getCellType() == CellType.FORMULA) {
cell = cell.getSheet().getRow(cell.getRowIndex()).getCell(cell.getColumnIndex(), MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (cell == null) {
continue;
}
}
if (cell.getCellType() == CellType.ERROR) {
continue;
}
if (cell.getCellType() == CellType.STRING && cell.getStringCellValue().trim().isEmpty()) {
continue;
}
if (cell.getCellType() == CellType.NUMERIC && cell.getNumericCellValue() == 0) {
continue;
}
if (cell.getCellType() == CellType.BOOLEAN && !cell.getBooleanCellValue()) {
continue;
}
if (cell.getCellType() == CellType.STRING && cell.getStringCellValue().startsWith("#")) {
continue;
}
if (cell.getCellType() == CellType.FORMULA && cell.getCellFormula().startsWith("#")) {
continue;
}
if (cell.getCellType() == CellType.STRING && cell.getStringCellValue().startsWith("=")) {
continue;
}
if (cell.getCellType() == CellType.FORMULA && cell.getCellFormula().startsWith("=")) {
continue;
}
if (cell.getCellType() == CellType.STRING && cell.getStringCellValue().startsWith("@")) {
continue;
}
if (cell.getCellType() == CellType.FORMULA && cell.getCellFormula().startsWith("@")) {
continue;
}
if (cell.getCellType() == CellType.STRING && cell.getStringCellValue().startsWith("'")) {
continue;
}
if (cell.getCellType() == CellType.FORMULA && cell.getCellFormula().startsWith("'")) {
continue;
}
if (cell.getCellType() == CellType.STRING && cell.getStringCellValue().startsWith("http")) {
BufferedImage image = ImageIO.read(new URL(cell.getStringCellValue()));
if (image != null) {
ByteArrayOutputStream baos = new ByteArrayOutputStream();
ImageIO.write(image, "png", baos);
baos.flush();
byte[] imageData = baos.toByteArray();
baos.close();
Image pdfImage = Image.getInstance(imageData);
pdfImage.scaleToFit(100f, 100f);
PdfPCell imageCell = new PdfPCell(pdfImage, true);
imageCell.setBorderWidth(1f);
imageCell.setBorderColor(BaseColor.BLACK);
imageCell.setPadding(5f);
table.addCell(imageCell);
}
} else {
XSSFSheet xssfSheet = (XSSFSheet) sheet;
XSSFDrawing drawing = xssfSheet.createDrawingPatriarch();
for (XSSFShape shape : drawing.getShapes()) {
if (shape instanceof XSSFClientAnchor) {
XSSFClientAnchor anchor = (XSSFClientAnchor) shape.getAnchor();
if (anchor.getRow1() == cell.getRowIndex() && anchor.getCol1() == cell.getColumnIndex()) {
if (shape instanceof XSSFGraphicFrame) {
XSSFGraphicFrame frame = (XSSFGraphicFrame) shape;
BufferedImage image = frame.getGraphic().getBufferedImage();
ByteArrayOutputStream baos = new ByteArrayOutputStream();
ImageIO.write(image, "png", baos);
baos.flush();
byte[] imageData = baos.toByteArray();
baos.close();
Image pdfImage = Image.getInstance(imageData);
pdfImage.scaleToFit(100f, 100f);
PdfPCell imageCell = new PdfPCell(pdfImage, true);
imageCell.setBorderWidth(1f);
imageCell.setBorderColor(BaseColor.BLACK);
imageCell.setPadding(5f);
table.addCell(imageCell);
}
}
}
}
}
}
}
// 添加表格到PDF文档
table.setSpacingBefore(20f);
table.setSpacingAfter(20f);
table.setKeepTogether(true);
document.add(table);
}
// 关闭PDF文档
document.close();
} catch (IOException | DocumentException e) {
e.printStackTrace();
}
}
}
🍭依赖:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>com.itextpdf</groupId>
<artifactId>itextpdf</artifactId>
<version>5.5.13</version>
</dependency>
Apache POI库:用于读取和写入Excel文件。
iText库:用于创建和操作PDF文件。
🍭描述:
-
使用WorkbookFactory.create()方法创建Workbook对象,该方法可以自动检测Excel文件的格式并创建相应的Workbook对象。
-
使用XSSFSheet.createDrawingPatriarch()方法获取工作表的绘图对象,然后使用XSSFDrawing.getShapes()方法获取所有形状对象。
-
遍历所有形状对象,找到与当前单元格对应的图片对象,并将其转换为PDF格式的图片。
-
使用PdfPTable对象创建PDF表格,并将Excel文件中的文字和图片添加到表格中。
-
使用Document对象创建PDF文档,并使用PdfWriter对象将PDF文档写入到文件中。
在上面的代码中,我们使用了Apache POI库来读取Excel文件中的文字和图片,并使用iText库将其转换为PDF文件。我们使用了以下技术:
在上面的代码中,我们还添加了一些逻辑来处理Excel文件中的各种数据类型,例如空单元格、公式单元格、错误单元格、空字符串单元格、零值单元格、布尔值单元格、超链接单元格、图片单元格等。我们还使用了一些iText库中的类来设置PDF文档的大小、边距、字体、颜色等属性。
请注意,上面的代码仅适用于XLSX格式的Excel文件。如果您要处理XLS格式的Excel文件,需要使用HSSFWorkbook和HSSFSheet类代替XSSFWorkbook和XSSFSheet类。
🍭进一步优化后的代码:
-
使用try-with-resources语句来自动关闭文件流和PDF文档对象。
-
将处理图片的代码提取到一个单独的方法中,以提高代码的可读性和可维护性。
-
使用Java 8的Stream API来简化代码。
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URL;
import java.util.stream.IntStream;
import javax.imageio.ImageIO;
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.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFGraphicFrame;
import org.apache.poi.xssf.usermodel.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.itextpdf.text.BaseColor;
import com.itextpdf.text.Document;
import com.itextpdf.text.DocumentException;
import com.itextpdf.text.Element;
import com.itextpdf.text.Font;
import com.itextpdf.text.Image;
import com.itextpdf.text.PageSize;
import com.itextpdf.text.Paragraph;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfWriter;
public class ExcelToPdfDemo {
public static void main(String[] args) {
try (Workbook workbook = WorkbookFactory.create(new File("example.xlsx"));
FileOutputStream fos = new FileOutputStream("example.pdf");
Document document = new Document(PageSize.A4, 50, 50, 50, 50);
PdfWriter writer = PdfWriter.getInstance(document, fos)) {
document.open();
for (Sheet sheet : workbook) {
PdfPTable table = new PdfPTable(sheet.getRow(0).getLastCellNum());
table.setHeaderRows(1);
table.setWidths(IntStream.range(0, sheet.getRow(0).getLastCellNum()).mapToDouble(i -> 1).toArray());
table.setWidthPercentage(100);
Paragraph title = new Paragraph(sheet.getSheetName(), new Font(BaseFont.createFont("STSong-Light", "UniGB-UCS2-H", BaseFont.NOT_EMBEDDED), 16, Font.BOLD));
title.setAlignment(Element.ALIGN_CENTER);
document.add(title);
sheet.forEach(row -> {
IntStream.range(0, row.getLastCellNum()).forEach(i -> {
Cell cell = row.getCell(i);
PdfPCell pdfCell = new PdfPCell(new Paragraph(cell.getStringCellValue(), new Font(BaseFont.createFont("STSong-Light", "UniGB-UCS2-H", BaseFont.NOT_EMBEDDED), 12)));
pdfCell.setBorderWidth(1f);
pdfCell.setBorderColor(BaseColor.BLACK);
pdfCell.setPadding(5f);
if (row.getRowNum() == 0) {
pdfCell.setBackgroundColor(BaseColor.LIGHT_GRAY);
}
table.addCell(pdfCell);
if (cell.getCellType() == CellType.BLANK || cell.getCellType() == CellType.FORMULA || cell.getCellType() == CellType.ERROR || cell.getCellType() == CellType.STRING && cell.getStringCellValue().trim().isEmpty() || cell.getCellType() == CellType.NUMERIC && cell.getNumericCellValue() == 0 || cell.getCellType() == CellType.BOOLEAN && !cell.getBooleanCellValue() || cell.getCellType() == CellType.STRING && cell.getStringCellValue().startsWith("#") || cell.getCellType() == CellType.FORMULA && cell.getCellFormula().startsWith("#") || cell.getCellType() == CellType.STRING && cell.getStringCellValue().startsWith("=") || cell.getCellType() == CellType.FORMULA && cell.getCellFormula().startsWith("=") || cell.getCellType() == CellType.STRING && cell.getStringCellValue().startsWith("@") || cell.getCellType() == CellType.FORMULA && cell.getCellFormula().startsWith("@") || cell.getCellType() == CellType.STRING && cell.getStringCellValue().startsWith("'") || cell.getCellType() == CellType.FORMULA && cell.getCellFormula().startsWith("'")) {
return;
}
if (cell.getCellType() == CellType.STRING && cell.getStringCellValue().startsWith("http")) {
try {
BufferedImage image = ImageIO.read(new URL(cell.getStringCellValue()));
if (image != null) {
addImageToTable(table, image);
}
} catch (IOException e) {
e.printStackTrace();
}
} else {
if (sheet instanceof XSSFSheet) {
XSSFSheet xssfSheet = (XSSFSheet) sheet;
XSSFDrawing drawing = xssfSheet.createDrawingPatriarch();
for (XSSFShape shape : drawing.getShapes()) {
if (shape instanceof XSSFClientAnchor) {
XSSFClientAnchor anchor = (XSSFClientAnchor) shape.getAnchor();
if (anchor.getRow1() == cell.getRowIndex() && anchor.getCol1() == cell.getColumnIndex()) {
if (shape instanceof XSSFGraphicFrame) {
XSSFGraphicFrame frame = (XSSFGraphicFrame) shape;
BufferedImage image = frame.getGraphic().getBufferedImage();
addImageToTable(table, image);
}
}
}
}
}
}
});
});
table.setSpacingBefore(20f);
table.setSpacingAfter(20f);
table.setKeepTogether(true);
document.add(table);
}
document.close();
} catch (IOException | DocumentException e) {
e.printStackTrace();
}
}
private static void addImageToTable(PdfPTable table, BufferedImage image) throws IOException, DocumentException {
ByteArrayOutputStream baos = new ByteArrayOutputStream();
ImageIO.write(image, "png", baos);
baos.flush();
byte[] imageData = baos.toByteArray();
baos.close();
Image pdfImage = Image.getInstance(imageData);
pdfImage.scaleToFit(100f, 100f);
PdfPCell imageCell = new PdfPCell(pdfImage, true);
imageCell.setBorderWidth(1f);
imageCell.setBorderColor(BaseColor.BLACK);
imageCell.setPadding(5f);
table.addCell(imageCell);
}
🍭涉及的依赖:
Apache Commons IO库:用于处理文件和流。
Apache Commons Lang库:用于处理字符串和其他通用操作。
Bouncy Castle库:用于处理加密和解密操作。
XML Graphics Commons库:用于处理SVG图像。
<dependency>
<groupId>org.apache.xmlgraphics</groupId>
<artifactId>xmlgraphics-commons</artifactId>
<version>2.6</version>
</dependency>
<dependency>
<groupId>org.bouncycastle</groupId>
<artifactId>bcprov-jdk15on</artifactId>
<version>1.68</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.12.0</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.8.0</version>
</dependency>
<dependency>
<groupId>javax.media</groupId>
<artifactId>jai_core</artifactId>
<version>1.1.3</version>
</dependency>
Java Advanced Imaging API(JAI)库:用于处理图片。
🍭 实现逻辑:
-
使用Apache POI库读取Excel文件,并遍历所有工作表。
-
对于每个工作表,使用iText库创建PDF文档,并设置文档的大小、边距、字体等属性。
-
遍历所有行和单元格,将Excel文件中的文字和图片添加到PDF表格中。
-
对于包含图片的单元格,使用Java Advanced Imaging API(JAI)库将图片转换为PDF格式的图片,并将其添加到PDF表格中。
-
使用iText库将PDF表格添加到PDF文档中,并将PDF文档写入到文件中。
在上面的代码中,我们还添加了一些逻辑来处理Excel文件中的各种数据类型,例如空单元格、公式单元格、错误单元格、空字符串单元格、零值单元格、布尔值单元格、超链接单元格、图片单元格等。我们还使用了一些iText库中的类来设置PDF文档的大小、边距、字体、颜色等属性。