1:jar包
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
<dependency>
<groupId>com.unicom</groupId>
<artifactId>groupmall-common</artifactId>
<version>1.0-SNAPSHOT</version>
</dependency>
2:工具
package com.orange;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
import com.itextpdf.text.Document;
import com.itextpdf.text.DocumentException;
import com.itextpdf.text.Element;
import com.itextpdf.text.Font;
import com.itextpdf.text.PageSize;
import com.itextpdf.text.Paragraph;
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 jxl.read.biff.BiffException;
public class ExcelToPdfUtil {
private static final int BUFFER_SIZE = 2 * 1024;
public static boolean getPdf(File localFile, String filePath, String fileName, String path) {
try {
int shettPage = 0;
Workbook workbook = Workbook.getWorkbook(localFile);
String[] sheetNames = workbook.getSheetNames();
for (String sheetName : sheetNames) {
String PdfFoleUrl = filePath + fileName + "/" + fileName + "_" + sheetName + ".pdf";
createFile(PdfFoleUrl);
FileOutputStream fileOutputStream = new FileOutputStream(PdfFoleUrl);
getShettPagePDF(workbook, sheetName, shettPage, fileOutputStream, path);
shettPage++;
}
workbook.close();
return true;
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (DocumentException e) {
e.printStackTrace();
}
return false;
}
public static boolean getShettPagePDF(Workbook workbook, String sheetName, int shettPage,
FileOutputStream fileOutputStream, String path) throws DocumentException, IOException {
Document document = new Document(PageSize.A4, 0, 0, 50, 0);
PdfWriter writer = PdfWriter.getInstance(document, fileOutputStream);
BaseFont bf = BaseFont.createFont(path, BaseFont.IDENTITY_H, false);
// 创建Font对象,将基础字体对象,字体大小,字体风格
Font font = new Font(bf, 10, Font.NORMAL);
int rowNum = 0;
int colNum = 0;
Sheet sheet = workbook.getSheet(shettPage);
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 {
if (shettPage == 0 && i == 4) {
cell1 = mergeRowRight(str, font, colNum);
} else {
cell1 = mergeCol(str, font, colNum);
}
// cell1 = mergeCol(str, font, colNum);
cell1.setRowspan(rowNum);
table.addCell(cell1);
}
flag = false;
break;
}
}
if (flag) {
table.addCell(getPDFCell(str, font));
}
}
}
document.open();
document.add(table);
document.close();
writer.close();
return true;
}
// 合并行的静态函数
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;
}
// 合并列的静态函数 右对齐
public static PdfPCell mergeRowRight(String str, Font font, int i) {
PdfPCell cell = new PdfPCell(new Paragraph(str, font));
cell.setMinimumHeight(25);
cell.setHorizontalAlignment(Element.ALIGN_RIGHT);
cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
// 将该单元格所在行包括该单元格在内的i列单元格合并为一个单元格
cell.setColspan(i);
return cell;
}
// 合并列的静态函数
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;
}
public static boolean createFile(String fileName) {
Boolean bool = false;
// 文件路径+名称+文件类型
File file = new File(fileName);
try {
// 如果路径不存在,则创建
File parentFile = file.getParentFile();
if (!parentFile.exists()) {
parentFile.mkdirs();
}
// 如果文件存在,则删除旧文件重新创建新的文件
if (file.exists()) {
delFile(fileName);
file = new File(fileName);
}
// 如果文件不存在,则创建新的文件
if (!file.exists()) {
file.createNewFile();
bool = true;
}
} catch (Exception e) {
e.printStackTrace();
}
return bool;
}
public static boolean delFile(String fileName) {
Boolean bool = false;
File file = new File(fileName);
try {
if (file.exists()) {
file.delete();
bool = true;
}
} catch (Exception e) {
// TODO: handle exception
}
return bool;
}
public static void toZip(String srcDir, OutputStream out, boolean KeepDirStructure) throws RuntimeException {
long start = System.currentTimeMillis();
ZipOutputStream zos = null;
try {
zos = new ZipOutputStream(out);
File sourceFile = new File(srcDir);
compress(sourceFile, zos, sourceFile.getName(), KeepDirStructure);
long end = System.currentTimeMillis();
System.out.println("压缩完成,耗时:" + (end - start) + " ms");
} catch (Exception e) {
throw new RuntimeException("zip error from ZipUtils", e);
} finally {
if (zos != null) {
try {
zos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
private static void compress(File sourceFile, ZipOutputStream zos, String name,
boolean KeepDirStructure) throws Exception {
byte[] buf = new byte[BUFFER_SIZE];
if (sourceFile.isFile()) {
// 向zip输出流中添加一个zip实体,构造器中name为zip实体的文件的名字
zos.putNextEntry(new ZipEntry(name));
// copy文件到zip输出流中
int len;
FileInputStream in = new FileInputStream(sourceFile);
while ((len = in.read(buf)) != -1) {
zos.write(buf, 0, len);
}
// Complete the entry
zos.closeEntry();
in.close();
} else {
File[] listFiles = sourceFile.listFiles();
if (listFiles == null || listFiles.length == 0) {
// 需要保留原来的文件结构时,需要对空文件夹进行处理
if (KeepDirStructure) {
// 空文件夹的处理
zos.putNextEntry(new ZipEntry(name + "/"));
// 没有文件,不需要文件的copy
zos.closeEntry();
}
} else {
for (File file : listFiles) {
// 判断是否需要保留原来的文件结构
if (KeepDirStructure) {
// 注意:file.getName()前面需要带上父文件夹的名字加一斜杠,
// 不然最后压缩包中就不能保留原来的文件结构,即:所有文件都跑到压缩包根目录下了
compress(file, zos, name + "/" + file.getName(), KeepDirStructure);
} else {
compress(file, zos, file.getName(), KeepDirStructure);
}
}
}
}
}
}
原文没找到。