Java Excel to PDF itextpdf, ready to use
- Tool method
-
- 1. How to use
-
- 1. Local conversion
- 2. Internet download
- 2. Introduction of pom dependencies
- 3. Tool methods
- 3. Citations
This article mainly organizes the tools and methods. It is organized by referring to other bloggers’ articles for easy use.
Tool methods
1. How to use
1. Local conversion
- Import dependencies
- Create tool method
- Just pass in the input and output stream or document address.
2. Internet download
Generate or fill through POI or easyExcel, and then the backend converts PDF to respond to the frontend
Idea: Split the network download into local conversion, and then respond to the front end.
- Now the server creates a temporary file directory (the temporary directory can be cleared at the beginning of each download request);
- Write the generated Excel to a local temporary file;
- Get the Excel file input stream and get the response output stream (response.getOutputStream(););
- Just call the public method and pass in the input and output streams.
2. Introduction of pom dependencies
<!-- pom related dependencies -->
<poi.version>4.1.1</poi.version>
<itextpdf.version>5.5.13.2</itextpdf.version>
<!-- POI Excel-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${<!-- -->poi.version}</version>
</dependency>
<!-- iText PDF -->
<dependency>
<groupId>com.itextpdf</groupId>
<artifactId>itextpdf</artifactId>
<version>${<!-- -->itextpdf.version}</version>
</dependency>
3. Tools and methods
package com.xxx.tool.util;
import cn.hutool.core.collection.CollUtil;
import com.itextpdf.text.*;
import com.itextpdf.text.pdf.BaseFont;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfWriter;
import lombok.experimental.UtilityClass;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Objects;
@UtilityClass
public class ExcelToPdfUtil {<!-- -->
public static void excelToPdf(String excelPath, String pdfPath, String excelSuffix) {<!-- -->
try (InputStream in = Files.newInputStream(Paths.get(excelPath));
OutputStream out = Files.newOutputStream(Paths.get(pdfPath))) {<!-- -->
ExcelToPdfUtil.excelToPdf(in, out, excelSuffix);
} catch (Exception e) {<!-- -->
e.printStackTrace();
}
}
/**
* Convert Excel to PDF and write to output stream
*
* @param inStream Excel input stream
* @param outStream PDF output stream
* @param excelSuffix Excel type .xls and .xlsx
* @throws Exception exception information
*/
public static void excelToPdf(InputStream inStream, OutputStream outStream, String excelSuffix) throws Exception {<!-- -->
// Enter the circulation workbook and obtain the sheet
Sheet sheet = getPoiSheetByFileStream(inStream, 0, excelSuffix);
// Get the column width ratio
float[] widths = getColWidth(sheet);
PdfPTable table = new PdfPTable(widths);
table.setWidthPercentage(100);
int colCount = widths.length;
//Set basic font
BaseFont baseFont = BaseFont.createFont("C:\Windows\Fonts\simsun.ttc,0", BaseFont.IDENTITY_H, BaseFont.EMBEDDED);
// iterate through rows
for (int rowIndex = sheet.getFirstRowNum(); rowIndex <= sheet.getLastRowNum(); rowIndex + + ) {<!-- -->
Row row = sheet.getRow(rowIndex);
if (Objects.isNull(row)) {<!-- -->
//Insert empty object
for (int i = 0; i < colCount; i + + ) {<!-- -->
table.addCell(createPdfPCell(null, 0, 13f, null));
}
} else {<!-- -->
// iterate through cells
for (int columnIndex = 0; (columnIndex < row.getLastCellNum() || columnIndex < colCount) & amp; & amp; columnIndex > -1; columnIndex + + ) {<!-- -->
PdfPCell pCell = excelCellToPdfCell(sheet, row.getCell(columnIndex), baseFont);
// Whether to merge cells
if (isMergedRegion(sheet, rowIndex, columnIndex)) {<!-- -->
int[] span = getMergedSpan(sheet, rowIndex, columnIndex);
//Ignore merged cells
boolean mergedCell = span[0] == 1 & amp; & amp; span[1] == 1;
if (mergedCell) {<!-- -->
continue;
}
pCell.setRowspan(span[0]);
pCell.setColspan(span[1]);
}
table.addCell(pCell);
}
}
}
//Initialize PDF document object
createPdfTableAndWriteDocument(outStream, table);
}
/**
* Cell conversion, poi cell converted to itext cell
*
* @param sheet poi sheet page
* @param excelCell poi cell
* @param baseFont base font
* @return com.itextpdf.text.pdf.PdfPCell
*/
private static PdfPCell excelCellToPdfCell(Sheet sheet, Cell excelCell, BaseFont baseFont) throws Exception {<!-- -->
if (Objects.isNull(excelCell)) {<!-- -->
return createPdfPCell(null, 0, 13f, null);
}
int rowIndex = excelCell.getRowIndex();
int columnIndex = excelCell.getColumnIndex();
// image information
List<PicturesInfo> infos = getAllPictureInfos(sheet, rowIndex, rowIndex, columnIndex, columnIndex, false);
PdfPCell pCell;
if (CollUtil.isNotEmpty(infos)) {<!-- -->
pCell = new PdfPCell(Image.getInstance(infos.get(0).getPictureData()));
} else {<!-- -->
Font excelFont = getExcelFont(sheet, excelCell);
//Set cell font
com.itextpdf.text.Font pdFont = new com.itextpdf.text.Font(baseFont, excelFont.getFontHeightInPoints(), excelFont.getBold() ? 1 : 0, BaseColor.BLACK);
Integer border = hasBorder(excelCell) ? null : 0;
String excelCellValue = getExcelCellValue(excelCell);
pCell = createPdfPCell(excelCellValue, border, excelCell.getRow().getHeightInPoints(), pdFont);
}
// Center horizontally
pCell.setHorizontalAlignment(getHorAlign(excelCell.getCellStyle().getAlignment().getCode()));
// vertical alignment
pCell.setVerticalAlignment(getVerAlign(excelCell.getCellStyle().getVerticalAlignment().getCode()));
return pCell;
}
/**
* Create pdf documents and add tables
*
* @param outStream output stream, target document
* @param table table
* @throws DocumentException exception information
*/
private static void createPdfTableAndWriteDocument(OutputStream outStream, PdfPTable table) throws DocumentException {<!-- -->
//Set pdf paper size PageSize.A4 A4 landscape
Document document = new Document(new RectangleReadOnly(842.0F, 595.0F));
PdfWriter.getInstance(document, outStream);
//Set page margin width
document.setMargins(10, 10, 10, 10);
document.open();
document.add(table);
document.close();
}
/**
* Convert the Excel document input stream to the corresponding workbook and obtain the corresponding sheet
*
* @param inputStream Excel document input stream
* @param sheetNo sheet number, default 0 first sheet
* @param excelSuffix file type .xls and .xlsx
* @return poi sheet
* @throws IOException exception
*/
public static Sheet getPoiSheetByFileStream(InputStream inputStream, int sheetNo, String excelSuffix) throws IOException {<!-- -->
Workbook workbook;
if (excelSuffix.endsWith(".xlsx")) {<!-- -->
workbook = new XSSFWorkbook(inputStream);
} else {<!-- -->
workbook = new HSSFWorkbook(inputStream);
}
return workbook.getSheetAt(sheetNo);
}
/**
* Create itext pdf cells
*
* @param content cell content
* @param border border
* @param minimumHeight height
* @param pdFont font
* @return pdf cell
*/
private static PdfPCell createPdfPCell(String content, Integer border, Float minimumHeight, com.itextpdf.text.Font pdFont) {<!-- -->
String contentValue = content == null ? "" : content;
com.itextpdf.text.Font pdFontNew = pdFont == null ? new com.itextpdf.text.Font() : pdFont;
PdfPCell pCell = new PdfPCell(new Phrase(contentValue, pdFontNew));
if (Objects.nonNull(border)) {<!-- -->
pCell.setBorder(border);
}
if (Objects.nonNull(minimumHeight)) {<!-- -->
pCell.setMinimumHeight(minimumHeight);
}
return pCell;
}
/**
*Excel vertical alignment maps to pdf alignment
*/
private static int getVerAlign(int align) {<!-- -->
switch (align) {<!-- -->
case 2:
return com.itextpdf.text.Element.ALIGN_BOTTOM;
case 3:
return com.itextpdf.text.Element.ALIGN_TOP;
default:
return com.itextpdf.text.Element.ALIGN_MIDDLE;
}
}
/**
* Excel horizontal alignment maps to pdf horizontal alignment
*/
private static int getHorAlign(int align) {<!-- -->
switch (align) {<!-- -->
case 1:
return com.itextpdf.text.Element.ALIGN_LEFT;
case 3:
return com.itextpdf.text.Element.ALIGN_RIGHT;
default:
return com.itextpdf.text.Element.ALIGN_CENTER;
}
}
/*============================================== POI acquisition Image and text content tool methods ============================================== ==*/
/**
* Get font
*
* @param sheet excel converted sheet page
* @param cell cell
* @return font
*/
private static Font getExcelFont(Sheet sheet, Cell cell) {<!-- -->
//xls
if (sheet instanceof HSSFSheet) {<!-- -->
Workbook workbook = sheet.getWorkbook();
return ((HSSFCell) cell).getCellStyle().getFont(workbook);
}
// xlsx
return ((XSSFCell) cell).getCellStyle().getFont();
}
/**
* Determine whether the excel cell has a border
*/
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;
}
/**
* Determine whether the cell is a merged cell
*/
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 & amp; & amp; row <= lastRow) {<!-- -->
if (column >= firstColumn & amp; & amp; column <= lastColumn) {<!-- -->
return true;
}
}
}
return false;
}
/**
* Calculate the number of crossed rows and columns of merged cells
*/
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 & amp; & amp; firstRow == row) {<!-- -->
span[0] = lastRow - firstRow + 1;
span[1] = lastColumn - firstColumn + 1;
break;
}
}
return span;
}
/**
* Get the proportion of the width of each column in excel
*/
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 = row.getFirstCellNum(); 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 = row.getFirstCellNum(); i < cellCount; i + + ) {<!-- -->
colWidthPer[i] = (float) colWidths[i] / sum * 100;
}
return colWidthPer;
}
/**
* Get the row number with the largest number of columns in excel
*/
private static int getMaxColRowNum(Sheet sheet) {<!-- -->
int rowNum = 0;
int maxCol = 0;
for (int r = sheet.getFirstRowNum(); r < sheet.getPhysicalNumberOfRows(); r + + ) {<!-- -->
Row row = sheet.getRow(r);
if (row != null & amp; & amp; maxCol < row.getPhysicalNumberOfCells()) {<!-- -->
maxCol = row.getPhysicalNumberOfCells();
rowNum = r;
}
}
return rowNum;
}
/**
* poi gets the cell content based on the cell type
*
* @param excelCell poi cell
* @return cell content text
*/
public static String getExcelCellValue(Cell excelCell) {<!-- -->
if (excelCell == null) {<!-- -->
return "";
}
// Determine the type of data
CellType cellType = excelCell.getCellType();
if (cellType == CellType.STRING) {<!-- -->
return excelCell.getStringCellValue();
}
if (cellType == CellType.BOOLEAN) {<!-- -->
return String.valueOf(excelCell.getBooleanCellValue());
}
if (cellType == CellType.FORMULA) {<!-- -->
return excelCell.getCellFormula();
}
if (cellType == CellType.NUMERIC) {<!-- -->
//short s = excelCell.getCellStyle().getDataFormat();
if (DateUtil.isCellDateFormatted(excelCell)) {<!-- -->// Process date format and time format
SimpleDateFormat sdf;
//Verify short value
if (excelCell.getCellStyle().getDataFormat() == 14) {<!-- -->
sdf = new SimpleDateFormat("yyyy/MM/dd");
} else if (excelCell.getCellStyle().getDataFormat() == 21) {<!-- -->
sdf = new SimpleDateFormat("HH:mm:ss");
} else if (excelCell.getCellStyle().getDataFormat() == 22) {<!-- -->
sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
} else {<!-- -->
throw new RuntimeException("Wrong date format!!!");
}
Date date = excelCell.getDateCellValue();
return sdf.format(date);
} else if (excelCell.getCellStyle().getDataFormat() == 0) {<!-- -->
//process numeric format
DataFormatter formatter = new DataFormatter();
return formatter.formatCellValue(excelCell);
}
}
if (cellType == CellType.ERROR) {<!-- -->
return "Illegal character";
}
return "";
}
/**
* Get all picture information in the sheet
*
* @param sheet sheet table
* @param onlyInternal inside the cell
* @return photo collection
* @throws Exception exception
*/
public static List<PicturesInfo> getAllPictureInfos(Sheet sheet, boolean onlyInternal) throws Exception {<!-- -->
return getAllPictureInfos(sheet, null, null, null, null, onlyInternal);
}
/**
* Get pictures based on sheet and cell information
*
* @param sheet sheet table
* @param minRow minimum row
* @param maxRow maximum row
* @param minCol minimum column
* @param maxCol maximum column
* @param onlyInternal whether it is internal
* @return picture collection
* @throws Exception exception
*/
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("Unhandled type, no extension method added for this type: 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 (shapeContainer == null) {<!-- -->
return picturesInfoList;
}
List<HSSFShape> shapeList = shapeContainer.getChildren();
for (HSSFShape shape : shapeList) {<!-- -->
if (shape instanceof HSSFPicture & amp; & amp; 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)) {<!-- -->
HSSFPictureData pictureData = picture.getPictureData();
picturesInfoList.add(new PicturesInfo()
.setMinRow(anchor.getRow1())
.setMaxRow(anchor.getRow2())
.setMinCol(anchor.getCol1())
.setMaxCol(anchor.getCol2())
.setPictureData(pictureData.getData())
.setExt(pictureData.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();
if (isInternalOrIntersect(minRow, maxRow, minCol, maxCol, anchor.getRow1(), anchor.getRow2(),
anchor.getCol1(), anchor.getCol2(), onlyInternal)) {<!-- -->
XSSFPictureData pictureData = picture.getPictureData();
picturesInfoList.add(new PicturesInfo()
.setMinRow(anchor.getRow1())
.setMaxRow(anchor.getRow2())
.setMinCol(anchor.getCol1())
.setMaxCol(anchor.getCol2())
.setPictureData(pictureData.getData())
.setExt(pictureData.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 & amp; & amp; _rangeMaxRow >= pictureMaxRow & amp; & amp; _rangeMinCol <= pictureMinCol
& amp; & amp; _rangeMaxCol >= pictureMaxCol);
} else {<!-- -->
return ((Math.abs(_rangeMaxRow - _rangeMinRow) + Math.abs(pictureMaxRow - pictureMinRow) >= Math
.abs(_rangeMaxRow + _rangeMinRow - pictureMaxRow - pictureMinRow))
& amp; & amp; (Math.abs(_rangeMaxCol - _rangeMinCol) + Math.abs(pictureMaxCol - pictureMinCol) >= Math
.abs(_rangeMaxCol + _rangeMinCol - pictureMaxCol - pictureMinCol)));
}
}
/**
*Basic information about pictures
*/
private class PicturesInfo {<!-- -->
private int minRow;
private int maxRow;
private int minCol;
private int maxCol;
private String ext;
private byte[] pictureData;
public PicturesInfo() {<!-- -->
}
public byte[] getPictureData() {<!-- -->
return pictureData;
}
public PicturesInfo setPictureData(byte[] pictureData) {<!-- -->
this.pictureData = pictureData;
return this;
}
public int getMinRow() {<!-- -->
return minRow;
}
public PicturesInfo setMinRow(int minRow) {<!-- -->
this.minRow = minRow;
return this;
}
public int getMaxRow() {<!-- -->
return maxRow;
}
public PicturesInfo setMaxRow(int maxRow) {<!-- -->
this.maxRow = maxRow;
return this;
}
public int getMinCol() {<!-- -->
return minCol;
}
public PicturesInfo setMinCol(int minCol) {<!-- -->
this.minCol = minCol;
return this;
}
public int getMaxCol() {<!-- -->
return maxCol;
}
public PicturesInfo setMaxCol(int maxCol) {<!-- -->
this.maxCol = maxCol;
return this;
}
public String getExt() {<!-- -->
return ext;
}
public PicturesInfo setExt(String ext) {<!-- -->
this.ext = ext;
return this;
}
}
}
3. Citation
- Java efficiently converts excel to pdf and supports the conversion of images in excel (supports both .xls and .xlsx formats)
- Java Poi reads excel and processes all types