Excel(1) : xls 转 pdf
程序员文章站
2024-01-21 18:44:34
...
参考 : https://blog.csdn.net/weixin_42470840/article/details/80712556
maven 坐标
<!-- https://mvnrepository.com/artifact/com.itextpdf/itextpdf -->
<dependency>
<groupId>com.itextpdf</groupId>
<artifactId>itextpdf</artifactId>
<version>5.5.13</version>
</dependency>
<!-- https://mvnrepository.com/artifact/net.sourceforge.jexcelapi/jxl -->
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
中文字体下载 : https://pan.baidu.com/s/12rsH3IfkNRWDtt3WQe--Vw
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 jxl.Cell;
import jxl.Range;
import jxl.Sheet;
import jxl.Workbook;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
public class ExcelToPdfUtil {
private static String SIMKAI = "/Users/leyili/Desktop/poi/simkai.ttf";
public static void main(String[] args) throws DocumentException, IOException {
xlsToPdf("/Users/leyili/Desktop/poi/test修.pdf","/Users/leyili/Desktop/poi/test修.xls",0);
}
/**
*
* @param pdfPath 生成 pdf 路径
* @param xlsPath xls 路径
* @param sheetNum 第 n 个工作表(从0开始)
* @return
*/
public static String xlsToPdf(String pdfPath,String xlsPath,int sheetNum) {
try {
Document document = new Document(PageSize.A4, 0, 0, 50, 0);
PdfWriter writer = PdfWriter.getInstance(document, new FileOutputStream(pdfPath));
//字体设置
/*
* 由于itext不支持中文,所以需要进行字体的设置,我这里让itext调用windows系统的中文字体,
* 找到文件后,打开属性,将文件名及所在路径作为字体名即可。
*/
//创建BaseFont对象,指明字体,编码方式,是否嵌入
BaseFont bf = BaseFont.createFont(SIMKAI, BaseFont.IDENTITY_H, false);
//创建Font对象,将基础字体对象,字体大小,字体风格
Font font = new Font(bf, 13, Font.NORMAL);
int rowNum = 0;
int colNum = 0;
Workbook workbook = Workbook.getWorkbook(new File(xlsPath));
Sheet sheet = workbook.getSheet(sheetNum);
int column = sheet.getColumns();
//下面是找出表格中的空行和空列
List<Integer> nullCol = new ArrayList<>();
List<Integer> nullRow = new ArrayList<>();
for (int j = 0; j < sheet.getColumns(); j++) {
int nullColNum = 0;
for (int i = 0; i < sheet.getRows(); i++) {
Cell cell = sheet.getCell(j, i);
String str = cell.getContents();
if (str == null || "".equals(str)) {
nullColNum++;
}
}
if (nullColNum == sheet.getRows()) {
nullCol.add(j);
column--;
}
}
for (int i = 0; i < sheet.getRows(); i++) {
int nullRowNum = 0;
for (int j = 0; j < sheet.getColumns(); j++) {
Cell cell = sheet.getCell(j, i);
String str = cell.getContents();
if (str == null || "".equals(str)) {
nullRowNum++;
}
}
if (nullRowNum == sheet.getColumns()) {
nullRow.add(i);
}
}
PdfPTable table = new PdfPTable(column);
Range[] ranges = sheet.getMergedCells();
PdfPCell cell1 = new PdfPCell();
for (int i = 0; i < sheet.getRows(); i++) {
//如果这一行是空行,这跳过这一行
if (nullRow.contains(i)) {
continue;
}
for (int j = 0; j < sheet.getColumns(); j++) {
//如果这一列是空列,则跳过这一列
if (nullCol.contains(j)) {
continue;
}
boolean flag = true;
Cell cell = sheet.getCell(j, i);
String str = cell.getContents();
//合并的单元格判断和处理
for (Range range : ranges) {
if (j >= range.getTopLeft().getColumn() && j <= range.getBottomRight().getColumn()
&& i >= range.getTopLeft().getRow() && i <= range.getBottomRight().getRow()) {
if (str == null || "".equals(str)) {
flag = false;
break;
}
rowNum = range.getBottomRight().getRow() - range.getTopLeft().getRow() + 1;
colNum = range.getBottomRight().getColumn() - range.getTopLeft().getColumn() + 1;
if (rowNum > colNum) {
cell1 = mergeRow(str, font, rowNum);
cell1.setColspan(colNum);
table.addCell(cell1);
} else {
cell1 = mergeCol(str, font, colNum);
cell1.setRowspan(rowNum);
table.addCell(cell1);
}
//System.out.println(num1 + " " + num2);
flag = false;
break;
}
}
if (flag) {
table.addCell(getPDFCell(str, font));
}
}
}
workbook.close();
document.open();
document.add(table);
document.close();
writer.close();
return null;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
/**
* 合并行的静态函数
* @param str
* @param font
* @param i
* @return
*/
public static PdfPCell mergeRow(String str, Font font, int i) {
//创建单元格对象,将内容及字体传入
PdfPCell cell = new PdfPCell(new Paragraph(str, font));
//设置单元格内容居中
cell.setHorizontalAlignment(Element.ALIGN_CENTER);
cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
//将该单元格所在列包括该单元格在内的i行单元格合并为一个单元格
cell.setRowspan(i);
return cell;
}
/**
* 合并列的静态函数
* @param str
* @param font
* @param i
* @return
*/
public static PdfPCell mergeCol(String str, Font font, int i) {
PdfPCell cell = new PdfPCell(new Paragraph(str, font));
cell.setMinimumHeight(25);
cell.setHorizontalAlignment(Element.ALIGN_CENTER);
cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
//将该单元格所在行包括该单元格在内的i列单元格合并为一个单元格
cell.setColspan(i);
return cell;
}
/**
*获取指定内容与字体的单元格
*/
public static PdfPCell getPDFCell(String string, Font font) {
//创建单元格对象,将内容与字体放入段落中作为单元格内容
PdfPCell cell = new PdfPCell(new Paragraph(string, font));
cell.setHorizontalAlignment(Element.ALIGN_CENTER);
cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
//设置最小单元格高度
cell.setMinimumHeight(25);
return cell;
}
注 : 有些格式会有问题!
END。