Java高效實(shí)現(xiàn)excel轉(zhuǎn)pdf(支持帶圖片的轉(zhuǎn)換)
更新時(shí)間：2024年01月30日 10:13:34 作者：沉默的方三毛
這篇文章主要為大家詳細(xì)介紹了如何用java實(shí)現(xiàn)excel轉(zhuǎn)pdf文件,并且支持excel單元格中帶有圖片的轉(zhuǎn)換,文中的示例代碼講解詳細(xì),需要的可以參考下
本文用java實(shí)現(xiàn)excel轉(zhuǎn)pdf文件，并且支持excel單元格中帶有圖片的轉(zhuǎn)換，使用poi來(lái)讀取excel文件數(shù)據(jù)，用itext來(lái)動(dòng)態(tài)生成pdf文檔，核心代碼如下：
public static byte[] excelToPdf(String excelPath, String pdfPath) throws Exception{
InputStream in = new FileInputStream(excelPath);
Workbook workbook;
if (excelPath.endsWith(".xlsx")){
workbook = new XSSFWorkbook(in);
} else {
workbook = new HSSFWorkbook(in);
}
Sheet sheet = workbook.getSheetAt(0);
ByteArrayOutputStream stream = new ByteArrayOutputStream();
Document document = new Document(PageSize.A4);//此處根據(jù)excel大小設(shè)置pdf紙張大小
PdfWriter writer = PdfWriter.getInstance(document, stream);
document.setMargins(0, 0, 15, 15);//設(shè)置也邊距
document.open();
float[] widths = getColWidth(sheet);
PdfPTable table = new PdfPTable(widths);
table.setWidthPercentage(90);
int colCount = widths.length;
BaseFont baseFont = BaseFont.createFont("C:\\Windows\\Fonts\\simsun.ttc,0", BaseFont.IDENTITY_H,
BaseFont.EMBEDDED);//設(shè)置基本字體
for (int r = sheet.getFirstRowNum(); r < sheet.getPhysicalNumberOfRows(); r++) {
Row row = sheet.getRow(r);
if (row != null) {
for (int c = row.getFirstCellNum(); (c < row.getLastCellNum() || c < colCount) && c > -1; c++) {
if (c >= row.getPhysicalNumberOfCells()) {
PdfPCell pCell = new PdfPCell(new Phrase(""));
pCell.setBorder(0);
table.addCell(pCell);
continue;
}
Cell excelCell = row.getCell(c);
String value = "";
if (excelCell != null) {
value = excelCell.toString().trim();
if (value != null && value.length() != 0){
String dataFormat = excelCell.getCellStyle().getDataFormatString();//獲取excel單元格數(shù)據(jù)顯示樣式
if (dataFormat != "General" && dataFormat != "@"){
try {
String numStyle = getNumStyle(dataFormat);
value = numFormat(numStyle, excelCell.getNumericCellValue());
} catch (Exception e) {
}
}
}
}
org.apache.poi.ss.usermodel.Font excelFont = getExcelFont(workbook, excelCell, excelPath);
//HSSFFont excelFont = excelCell.getCellStyle().getFont(workbook);
Font pdFont = new Font(baseFont, excelFont.getFontHeightInPoints(),
excelFont.getBoldweight() == 700 ? Font.BOLD : Font.NORMAL, BaseColor.BLACK);//設(shè)置單元格字體
PdfPCell pCell = new PdfPCell(new Phrase(value, pdFont));
List<PicturesInfo> infos = POIExtend.getAllPictureInfos(sheet, r, r, c, c, false);
if (!infos.isEmpty()){
pCell = new PdfPCell(Image.getInstance(infos.get(0).getPictureData()));
PicturesInfo info = infos.get(0);
System.out.println("最大行：" + info.getMaxRow() + "最小行：" + info.getMinRow() + "最大列:" + info.getMaxCol() + "最小列：" + info.getMinCol());;
}
boolean hasBorder = hasBorder(excelCell);
if (!hasBorder){
pCell.setBorder(0);
}
pCell.setHorizontalAlignment(getHorAglin(excelCell.getCellStyle().getAlignment()));
pCell.setVerticalAlignment(getVerAglin(excelCell.getCellStyle().getVerticalAlignment()));
pCell.setMinimumHeight(row.getHeightInPoints());
if (isMergedRegion(sheet, r, c)) {
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;//合并過的列直接跳過
}
table.addCell(pCell);
}
} else {
PdfPCell pCell = new PdfPCell(new Phrase(""));
pCell.setBorder(0);
pCell.setMinimumHeight(13);
table.addCell(pCell);
}
}
document.add(table);
document.close();
byte[] pdfByte = stream.toByteArray();
stream.flush();
stream.reset();
stream.close();
FileOutputStream outputStream = new FileOutputStream(pdfPath);
outputStream.write(pdfByte);
outputStream.flush();
outputStream.close();
return pdfByte;
}//獲取字體
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單元格是否有邊框
/**
* 判斷excel單元格是否有邊框
* @param excelCell
* @return
*/
private static boolean hasBorder(Cell excelCell) {
short top = excelCell.getCellStyle().getBorderTop();
short bottom = excelCell.getCellStyle().getBorderBottom();
short left = excelCell.getCellStyle().getBorderLeft();
short right = excelCell.getCellStyle().getBorderRight();
return top + bottom + left + right > 2;
}
獲取excel單元格數(shù)據(jù)顯示格式
/**
* 獲取excel單元格數(shù)據(jù)顯示格式
* @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;
}計(jì)算合并單元格合并的跨行跨列數(shù)
/**
* 計(jì)算合并單元格合并的跨行跨列數(shù)
* @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中每列寬度的占比
/**
* 獲取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 = 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;
}獲取excel中列數(shù)最多的行號(hào)
/**
* 獲取excel中列數(shù)最多的行號(hào)
* @param sheet
* @return
*/
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 && maxCol < row.getPhysicalNumberOfCells()) {
maxCol = row.getPhysicalNumberOfCells();
rowNum = r;
}
}
return rowNum;
}excel垂直對(duì)齊方式映射到pdf對(duì)齊方式
/**
* excel垂直對(duì)齊方式映射到pdf對(duì)齊方式
* @param aglin
* @return
*/
private static int getVerAglin(int aglin) {
switch (aglin) {
case 1:
return com.itextpdf.text.Element.ALIGN_MIDDLE;
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水平對(duì)齊方式映射到pdf水平對(duì)齊方式
/**
* excel水平對(duì)齊方式映射到pdf水平對(duì)齊方式
* @param aglin
* @return
*/
private static int getHorAglin(int aglin) {
switch (aglin) {
case 2:
return com.itextpdf.text.Element.ALIGN_CENTER;
case 3:
return com.itextpdf.text.Element.ALIGN_RIGHT;
case 1:
return com.itextpdf.text.Element.ALIGN_LEFT;
default:
return com.itextpdf.text.Element.ALIGN_CENTER;
}
}格式化數(shù)字
/**
* 格式化數(shù)字
* @param pattern
* @param num
* @return
*/
private static String numFormat(String pattern, double num){
DecimalFormat format = new DecimalFormat(pattern);
return format.format(num);
}
以下代碼是對(duì)excel轉(zhuǎn)pdf時(shí)對(duì)excel中圖片進(jìn)行處理，實(shí)現(xiàn)圖片在pdf中正常顯示
//圖片基本信息
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;
}
}以下是獲取excel中圖片數(shù)據(jù)以及位置信息代碼：
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFShape;
import org.apache.poi.hssf.usermodel.HSSFShapeContainer;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFSheet;
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()擴(kuò)展方法！");
}
}
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();
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)));
}
}
}以上就是Java高效實(shí)現(xiàn)excel轉(zhuǎn)pdf(支持帶圖片的轉(zhuǎn)換)的詳細(xì)內(nèi)容，更多關(guān)于Java excel轉(zhuǎn)pdf的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
這篇文章主要介紹了java使用ftp上傳文件示例,需要的朋友可以參考下2014-02-02
Springboot入門案例及部署項(xiàng)目的詳細(xì)過程
Spring Boot是由Pivotal團(tuán)隊(duì)提供的全新框架，其設(shè)計(jì)目的是用來(lái)簡(jiǎn)化新Spring應(yīng)用的初始搭建以及開發(fā)過程，本文給大家分享一個(gè)入門案例使用Springboot1.5.9搭建，具體配置部署過程跟隨小編一起看看吧2021-07-07
MyBatis中使用分頁(yè)插件PageHelper實(shí)現(xiàn)分頁(yè)功能
分頁(yè)是經(jīng)常使用的功能，本文主要介紹了Mybatis中處理特殊SQL處理邏輯，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-06-06
詳解Java七大阻塞隊(duì)列之SynchronousQueue
SynchronousQueue不需要存儲(chǔ)線程間交換的數(shù)據(jù)，它的作用像是一個(gè)匹配器，使生產(chǎn)者和消費(fèi)者一一匹配。本文詳細(xì)講解了Java七大阻塞隊(duì)列之一SynchronousQueue，需要了解的小伙伴可以參考一下這篇文章2021-09-09
springboot查看和修改內(nèi)置tomcat版本的方法步驟
本文主要介紹了springboot查看和修改內(nèi)置tomcat版本的方法步驟,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2024-07-07
這篇文章主要介紹了Java中的異常處理機(jī)制的相關(guān)資料，幫助大家更好的理解和學(xué)習(xí)Java，感興趣的朋友可以了解下2020-11-11
Java簡(jiǎn)單實(shí)現(xiàn)銀行ATM系統(tǒng)
這篇文章主要為大家詳細(xì)介紹了Java簡(jiǎn)單實(shí)現(xiàn)銀行ATM系統(tǒng)，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2022-05-05
這篇文章主要介紹了mybatis if標(biāo)簽判斷不生效的解決方法，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2021-02-02
實(shí)戰(zhàn)分布式醫(yī)療掛號(hào)系統(tǒng)之整合Swagger2到通用模塊
這篇文章主要為大家介紹了實(shí)戰(zhàn)分布式醫(yī)療掛號(hào)系統(tǒng)之整合Swagger2到通用模塊，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪2022-04-04
這篇文章主要介紹了Java getParameter方法案例詳解,本篇文章通過簡(jiǎn)要的案例,講解了該項(xiàng)技術(shù)的了解與使用,以下就是詳細(xì)內(nèi)容,需要的朋友可以參考下2021-08-08