这段代码中使用了((Sheet) cell).getDrawingPatriarch()
来获取单元格中图片的绘图区域对象,但是这里的cell
实际上应该是一个Picture
类型的单元格。所以需要修改成(HSSFPicture)cell
来获取绘图区域对象。
另外,在处理图片时,可以使用POI提供的API来获取图片数据和MIME类型,而不是直接从单元格中读取。因为可能会存在一些特殊情况下无法正确读取图片数据的问题。
优化后的代码如下:
import java.awt.image.BufferedImage;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import javax.imageio.ImageIO;
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 com.itextpdf.text.BadElementException;
import com.itextpdf.text.Document;
import com.itextpdf.text.Element;
import com.itextpdf.text.Font;
import com.itextpdf.text.Image;
import com.itextpdf.text.Paragraph;
import com.itextpdf.text.Rectangle;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfWriter;
public class ExcelToPdfConverter {
public static void main(String[] args) throws Exception {
// Read the Excel file
FileInputStream inputStream = new FileInputStream(new File("C:\\Users\\hspcadmin\\Desktop\\测试\\秦皇岛银行资管系统对接ECIF测试案例.xls"));
Workbook workbook = new HSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
// Create the PDF document
Document document = new Document();
PdfWriter writer = PdfWriter.getInstance(document, new FileOutputStream(new File("C:\\Users\\hspcadmin\\Desktop\\测试\\output.pdf")));
document.open();
// Set up fonts for the PDF document
Font titleFont = new Font(Font.FontFamily.TIMES_ROMAN, 16, Font.BOLD);
Font headerFont = new Font(Font.FontFamily.HELVETICA, 12, Font.BOLD);
Font cellFont = new Font(Font.FontFamily.COURIER, 10);
// Process the Excel data
for (int i=0; i<sheet.getLastRowNum()+1; i++) {
Row row = sheet.getRow(i);
if (row == null) {
continue; // skip blank rows
}
PdfPTable table = new PdfPTable(row.getLastCellNum());
table.setWidthPercentage(100);
for (int j=0; j<row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
if (cell == null) {
PdfPCell pdfCell = new PdfPCell();
pdfCell.addElement(new Paragraph("", cellFont));
table.addCell(pdfCell);
continue; // skip blank cells
}
PdfPCell pdfCell = new PdfPCell();
switch (cell.getCellTypeEnum()) {
case STRING:
String value = cell.getStringCellValue();
Paragraph paragraph = new Paragraph(value, cellFont);
pdfCell.addElement(paragraph);
break;
case NUMERIC:
double numericValue = cell.getNumericCellValue();
Paragraph numericParagraph = new Paragraph(Double.toString(numericValue), cellFont);
pdfCell.addElement(numericParagraph);
break;
case BOOLEAN:
boolean boolValue = cell.getBooleanCellValue();
Paragraph boolParagraph = new Paragraph(Boolean.toString(boolValue), cellFont);
pdfCell.addElement(boolParagraph);
break;
case FORMULA:
if (cell.getCachedFormulaResultTypeEnum() == CellType.STRING) {
String formulaValue = cell.getStringCellValue();
Paragraph formulaParagraph = new Paragraph(formulaValue, cellFont);
pdfCell.addElement(formulaParagraph);
} else if (cell.getCachedFormulaResultTypeEnum() == CellType.NUMERIC) {
double formulaNumericValue = cell.getNumericCellValue();
Paragraph formulaNumericParagraph = new Paragraph(Double.toString(formulaNumericValue),
cellFont);
pdfCell.addElement(formulaNumericParagraph);
}
break;
case BLANK:
// Do nothing
break;
default:
// Handle all other cases using the same approach as above
String otherValue=cell.getStringCellValue();
Paragraph otherPara=new Paragraph(otherValue,cellFont);
pdfCell.addElement(otherPara);
}
table.addCell(pdfCell);
// Handle images separately using ImageIO library
if (cell.getCellTypeEnum() == CellType.PICTURE && ((HSSFPicture)cell).getPictureData().getData() != null) {
byte[] pictureData = workbook.getAllPictures().get(((HSSFPicture)cell).getPictureIndex()-1).getData();
InputStream inputStream = new ByteArrayInputStream(pictureData);
BufferedImage image = ImageIO.read(inputStream);
Image img = getImageFromBufferedImage(image);
PdfPCell imageCell = getPdfPCellFromImage(img);
table.addCell(imageCell);
}
}
// Add the table to the PDF document
document.add(table);
}
// Save the PDF document
document.close();
}
// 从BufferedImage获取iText中的Image
private static Image getImageFromBufferedImage(BufferedImage image) throws BadElementException, IOException {
ByteArrayOutputStream baos = new ByteArrayOutputStream();
ImageIO.write(image, "png", baos);
byte[] imageData = baos.toByteArray();
return Image.getInstance(imageData);
}
// 从Image获取PdfPCell,设置单元格属性和样式
private static PdfPCell getPdfPCellFromImage(Image img) {
PdfPCell cell = new PdfPCell(img, true);
cell.setHorizontalAlignment(Element.ALIGN_CENTER); // 设置水平居中
cell.setVerticalAlignment(Element.ALIGN_MIDDLE); // 设置垂直居中
cell.setBorder(Rectangle.NO_BORDER); // 去除边框线条
return cell;
}
}