由于此份文章比较久远并且发现点击率也蛮高的，为了方便大伙的测试与研究我另外创建
了一份代码仓库在GitHub中，请大伙直接从这个地址拉取：
https://github.com/caryyu/excel2pdf
注意！注意！注意！分隔线以下的代码比较久远了，最新的代码以 Github 仓库为主，评论请转移至仓库的 Issue 中进行处理，谢谢！
---------------------------------------分割线----------------------------------------------
说明：
1.最近业务需求涉及到了关于这方面的知识,在网上寻找了很多次都是一些零零碎碎的代码,现在归档记录下来以免以后忘记再回来翻阅一下;同时让有需求的朋友也可以借鉴一下,如果有兴趣的朋友可以自己写一套新方案出来,顺便发我一个链接我,这样共同学习哈。
2.此种方式可以实现多个Excel转PDF多页情况,对边框和一些精细的部分实现的可能不太好,所以有能力的朋友自己可以参考以下代码进行适量的修改.
3.原理:使用PDF的Table与Excel表格进行对应,并解析Excel的行、列、单元格样式与Table的进行匹配.(样式有些部分实现的不是太好,所有大家有问题多自己解决一下啊)
进入正题,首先下载所需的Jar包:
commons-codec.jar
commons-io-1.1.jar
dom4j-1.6.1.jar
poi-3.9-20121203.jar
poi-ooxml-3.9-20121203.jar
poi-ooxml-schemas-3.9-20121203.jar
stax-api-1.0.1.jar
xmlbeans-2.3.0.jar
itext-asian.jar (亚洲语言支持包)
itextpdf-5.4.4.jar
xmlworker-5.4.4.jar (支持Html转PDF,可选..)
(POI官方网站) http://poi.apache.org/
(IText官方网站) http://www.itextpdf.com/
注:我采用的是 itext5.4.4 与 poi3.9.
(*)效果图如下:
(*)关联代码如下:
class Excel {
protected Workbook wb;
protected Sheet sheet;
public Excel(InputStream is) {
try {
this.wb = WorkbookFactory.create(is);
this.sheet = wb.getSheetAt(wb.getActiveSheetIndex());
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}
}
public Sheet getSheet() {
return sheet;
}
public Workbook getWorkbook(){
return wb;
}
}
public class ExcelObject {
/**
* 锚名称
*/
private String anchorName;
/**
* Excel Stream
*/
private InputStream inputStream;
/**
* POI Excel
*/
private Excel excel;
public ExcelObject(InputStream inputStream){
this.inputStream = inputStream;
this.excel = new Excel(this.inputStream);
}
public ExcelObject(String anchorName , InputStream inputStream){
this.anchorName = anchorName;
this.inputStream = inputStream;
this.excel = new Excel(this.inputStream);
}
public String getAnchorName() {
return anchorName;
}
public void setAnchorName(String anchorName) {
this.anchorName = anchorName;
}
public InputStream getInputStream() {
return this.inputStream;
}
public void setInputStream(InputStream inputStream) {
this.inputStream = inputStream;
}
Excel getExcel() {
return excel;
}
}
public class PdfTool {
//
protected Document document;
//
protected OutputStream os;
public Document getDocument() {
if (document == null) {
document = new Document();
}
return document;
}
}
public class Excel2Pdf extends PdfTool{
//
protected List<ExcelObject> objects = new ArrayList<ExcelObject>();
/**
* <p>Description: 导出单项PDF，不包含目录</p>
* @param object
*/
public Excel2Pdf(ExcelObject object , OutputStream os) {
this.objects.add(object);
this.os = os;
}
/**
* <p>Description: 导出多项PDF，包含目录</p>
* @param objects
*/
public Excel2Pdf(List<ExcelObject> objects , OutputStream os) {
this.objects = objects;
this.os = os;
}
/**
* <p>Description: 转换调用</p>
* @throws DocumentException
* @throws MalformedURLException
* @throws IOException
*/
public void convert() throws DocumentException, MalformedURLException, IOException {
getDocument().setPageSize(PageSize.A4.rotate());
PdfWriter writer = PdfWriter.getInstance(getDocument(), os);
writer.setPageEvent(new PDFPageEvent());
//Open document
getDocument().open();
//Single one
if(this.objects.size() <= 1){
PdfPTable table = this.toCreatePdfTable(this.objects.get(0) , getDocument() , writer);
getDocument().add(table);
}
//Multiple ones
if(this.objects.size() > 1){
toCreateContentIndexes(writer , this.getDocument() , this.objects);
//
for (int i = 0; i < this.objects.size(); i++) {
PdfPTable table = this.toCreatePdfTable(this.objects.get(i) , getDocument() , writer);
getDocument().add(table);
}
}
//
getDocument().close();
}
protected PdfPTable toCreatePdfTable(ExcelObject object , Document document , PdfWriter writer) throws MalformedURLException, IOException, DocumentException{
PdfPTable table = new PdfTableExcel(object).getTable();
table.setKeepTogether(true);
// table.setWidthPercentage(new float[]{100} , writer.getPageSize());
table.getDefaultCell().setBorder(PdfPCell.NO_BORDER);
return table;
}
/**
* <p>Description: 内容索引创建</p>
* @throws DocumentException
*/
protected void toCreateContentIndexes(PdfWriter writer , Document document , List<ExcelObject> objects) throws DocumentException{
PdfPTable table = new PdfPTable(1);
table.setKeepTogether(true);
table.getDefaultCell().setBorder(PdfPCell.NO_BORDER);
//
Font font = new Font(Resource.BASE_FONT_CHINESE , 12 , Font.NORMAL);
font.setColor(new BaseColor(0,0,255));
//
for (int i = 0; i < objects.size(); i++) {
ExcelObject o = objects.get(i);
String text = o.getAnchorName();
Anchor anchor = new Anchor(text , font);
anchor.setReference("#" + o.getAnchorName());
//
PdfPCell cell = new PdfPCell(anchor);
cell.setBorder(0);
//
table.addCell(cell);
}
//
document.add(table);
}
/**
* <p>ClassName: PDFPageEvent</p>
* <p>Description: 事件 -> 页码控制</p>
* <p>Author: Cary</p>
* <p>Date: Oct 25, 2013</p>
*/
private static class PDFPageEvent extends PdfPageEventHelper{
protected PdfTemplate template;
public BaseFont baseFont;
@Override
public void onStartPage(PdfWriter writer, Document document) {
try{
this.template = writer.getDirectContent().createTemplate(100, 100);
this.baseFont = new Font(Resource.BASE_FONT_CHINESE , 8, Font.NORMAL).getBaseFont();
} catch(Exception e) {
throw new ExceptionConverter(e);
}
}
@Override
public void onEndPage(PdfWriter writer, Document document) {
//在每页结束的时候把“第x页”信息写道模版指定位置
PdfContentByte byteContent = writer.getDirectContent();
String text = "第" + writer.getPageNumber() + "页";
float textWidth = this.baseFont.getWidthPoint(text, 8);
float realWidth = document.right() - textWidth;
//
byteContent.beginText();
byteContent.setFontAndSize(this.baseFont , 10);
byteContent.setTextMatrix(realWidth , document.bottom());
byteContent.showText(text);
byteContent.endText();
byteContent.addTemplate(this.template , realWidth , document.bottom());
}
}
}
public class PdfTableExcel {
//ExcelObject
protected ExcelObject excelObject;
//excel
protected Excel excel;
//
protected boolean setting = false;
/**
* <p>Description: Constructor</p>
* @param excel
*/
public PdfTableExcel(ExcelObject excelObject){
this.excelObject = excelObject;
this.excel = excelObject.getExcel();
}
/**
* <p>Description: 获取转换过的Excel内容Table</p>
* @return PdfPTable
* @throws BadElementException
* @throws MalformedURLException
* @throws IOException
*/
public PdfPTable getTable() throws BadElementException, MalformedURLException, IOException{
Sheet sheet = this.excel.getSheet();
return toParseContent(sheet);
}
protected PdfPTable toParseContent(Sheet sheet) throws BadElementException, MalformedURLException, IOException{
int rowlength = sheet.getLastRowNum();
List<PdfPCell> cells = new ArrayList<PdfPCell>();
float[] widths = null;
float mw = 0;
for (int i = 0; i < rowlength; i++) {
Row row = sheet.getRow(i);
float[] cws = new float[row.getLastCellNum()];
for (int j = 0; j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
float cw = getPOIColumnWidth(cell);
cws[cell.getColumnIndex()] = cw;
if(isUsed(cell.getColumnIndex(), row.getRowNum())){
continue;
}
cell.setCellType(Cell.CELL_TYPE_STRING);
CellRangeAddress range = getColspanRowspanByExcel(row.getRowNum(), cell.getColumnIndex());
//
int rowspan = 1;
int colspan = 1;
if (range != null) {
rowspan = range.getLastRow() - range.getFirstRow() + 1;
colspan = range.getLastColumn() - range.getFirstColumn() + 1;
}
//PDF单元格
PdfPCell pdfpCell = new PdfPCell();
pdfpCell.setBackgroundColor(new BaseColor(getBackgroundColorByExcel(cell.getCellStyle())));
pdfpCell.setColspan(colspan);
pdfpCell.setRowspan(rowspan);
pdfpCell.setVerticalAlignment(getVAlignByExcel(cell.getCellStyle().getVerticalAlignment()));
pdfpCell.setHorizontalAlignment(getHAlignByExcel(cell.getCellStyle().getAlignment()));
pdfpCell.setPhrase(getPhrase(cell));
pdfpCell.setFixedHeight(this.getPixelHeight(row.getHeightInPoints()));
addBorderByExcel(pdfpCell, cell.getCellStyle());
addImageByPOICell(pdfpCell , cell , cw);
//
cells.add(pdfpCell);
j += colspan - 1;
}
float rw = 0;
for (int j = 0; j < cws.length; j++) {
rw += cws[j];
}
if (rw > mw || mw == 0) {
widths = cws;
mw = rw;
}
}
//
PdfPTable table = new PdfPTable(widths);
table.setWidthPercentage(100);
// table.setLockedWidth(true);
for (PdfPCell pdfpCell : cells) {
table.addCell(pdfpCell);
}
return table;
}
protected Phrase getPhrase(Cell cell){
if(this.setting || this.excelObject.getAnchorName() == null){
return new Phrase(cell.getStringCellValue(), getFontByExcel(cell.getCellStyle()));
}
Anchor anchor = new Anchor(cell.getStringCellValue() , getFontByExcel(cell.getCellStyle()));
anchor.setName(this.excelObject.getAnchorName());
this.setting = true;
return anchor;
}
protected void addImageByPOICell(PdfPCell pdfpCell , Cell cell , float cellWidth) throws BadElementException, MalformedURLException, IOException{
POIImage poiImage = new POIImage().getCellImage(cell);
byte[] bytes = poiImage.getBytes();
if(bytes != null){
// double cw = cellWidth;
// double ch = pdfpCell.getFixedHeight();
//
// double iw = poiImage.getDimension().getWidth();
// double ih = poiImage.getDimension().getHeight();
//
// double scale = cw / ch;
//
// double nw = iw * scale;
// double nh = ih - (iw - nw);
//
// POIUtil.scale(bytes , nw , nh);
pdfpCell.setVerticalAlignment(Element.ALIGN_MIDDLE);
pdfpCell.setHorizontalAlignment(Element.ALIGN_CENTER);
Image image = Image.getInstance(bytes);
pdfpCell.setImage(image);
}
}
protected float getPixelHeight(float poiHeight){
float pixel = poiHeight / 28.6f * 26f;
return pixel;
}
/**
* <p>Description: 此处获取Excel的列宽像素(无法精确实现,期待有能力的朋友进行改善此处)</p>
* @param cell
* @return 像素宽
*/
protected int getPOIColumnWidth(Cell cell) {
int poiCWidth = excel.getSheet().getColumnWidth(cell.getColumnIndex());
int colWidthpoi = poiCWidth;
int widthPixel = 0;
if (colWidthpoi >= 416) {
widthPixel = (int) (((colWidthpoi - 416.0) / 256.0) * 8.0 + 13.0 + 0.5);
} else {
widthPixel = (int) (colWidthpoi / 416.0 * 13.0 + 0.5);
}
return widthPixel;
}
protected CellRangeAddress getColspanRowspanByExcel(int rowIndex, int colIndex) {
CellRangeAddress result = null;
Sheet sheet = excel.getSheet();
int num = sheet.getNumMergedRegions();
for (int i = 0; i < num; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
if (range.getFirstColumn() == colIndex && range.getFirstRow() == rowIndex) {
result = range;
}
}
return result;
}
protected boolean isUsed(int colIndex , int rowIndex){
boolean result = false;
Sheet sheet = excel.getSheet();
int num = sheet.getNumMergedRegions();
for (int i = 0; i < num; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
if (firstRow < rowIndex && lastRow >= rowIndex) {
if(firstColumn <= colIndex && lastColumn >= colIndex){
result = true;
}
}
}
return result;
}
protected Font getFontByExcel(CellStyle style) {
Font result = new Font(Resource.BASE_FONT_CHINESE , 8 , Font.NORMAL);
Workbook wb = excel.getWorkbook();
//字体样式索引
short index = style.getFontIndex();
org.apache.poi.ss.usermodel.Font font = wb.getFontAt(index);
//字体颜色
int colorIndex = font.getColor();
if(font.getBoldweight() == org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD){
result.setStyle(Font.BOLD);
}
HSSFColor color = HSSFColor.getIndexHash().get(colorIndex);
if(color != null){
int rbg = POIUtil.getRGB(color);
result.setColor(new BaseColor(rbg));
}
//下划线
FontUnderline underline = FontUnderline.valueOf(font.getUnderline());
if(underline == FontUnderline.SINGLE){
String ulString = FontStyle.UNDERLINE.getValue();
result.setStyle(ulString);
}
return result;
}
protected int getBackgroundColorByExcel(CellStyle style) {
Color color = style.getFillForegroundColorColor();
return POIUtil.getRGB(color);
}
protected void addBorderByExcel(PdfPCell cell , CellStyle style) {
Workbook wb = excel.getWorkbook();
cell.setBorderColorLeft(new BaseColor(POIUtil.getBorderRBG(wb,style.getLeftBorderColor())));
cell.setBorderColorRight(new BaseColor(POIUtil.getBorderRBG(wb,style.getRightBorderColor())));
cell.setBorderColorTop(new BaseColor(POIUtil.getBorderRBG(wb,style.getTopBorderColor())));
cell.setBorderColorBottom(new BaseColor(POIUtil.getBorderRBG(wb,style.getBottomBorderColor())));
}
protected int getVAlignByExcel(short align) {
int result = 0;
if (align == CellStyle.VERTICAL_BOTTOM) {
result = Element.ALIGN_BOTTOM;
}
if (align == CellStyle.VERTICAL_CENTER) {
result = Element.ALIGN_MIDDLE;
}
if (align == CellStyle.VERTICAL_JUSTIFY) {
result = Element.ALIGN_JUSTIFIED;
}
if (align == CellStyle.VERTICAL_TOP) {
result = Element.ALIGN_TOP;
}
return result;
}
protected int getHAlignByExcel(short align) {
int result = 0;
if (align == CellStyle.ALIGN_LEFT) {
result = Element.ALIGN_LEFT;
}
if (align == CellStyle.ALIGN_RIGHT) {
result = Element.ALIGN_RIGHT;
}
if (align == CellStyle.ALIGN_JUSTIFY) {
result = Element.ALIGN_JUSTIFIED;
}
if (align == CellStyle.ALIGN_CENTER) {
result = Element.ALIGN_CENTER;
}
return result;
}
}
public class Resource {
/**
* 中文字体支持
*/
protected static BaseFont BASE_FONT_CHINESE;
static {
try {
BASE_FONT_CHINESE = BaseFont.createFont("STSongStd-Light", "UniGB-UCS2-H", BaseFont.NOT_EMBEDDED);
} catch (Exception e) {
e.printStackTrace();
}
}
}
public class POIImage {
protected Dimension dimension;
protected byte[] bytes;
protected ClientAnchor anchor;
public POIImage getCellImage(Cell cell) {
byte[] result = null;
Sheet sheet = cell.getSheet();
// Workbook wb = sheet.getWorkbook();
// List<PictureData> pictures = (List<PictureData>) wb.getAllPictures();
if (sheet instanceof HSSFSheet) {
HSSFSheet hssfSheet = (HSSFSheet) sheet;
List<HSSFShape> shapes = hssfSheet.getDrawingPatriarch().getChildren();
for (HSSFShape shape : shapes) {
HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
if (shape instanceof HSSFPicture) {
HSSFPicture pic = (HSSFPicture) shape;
PictureData data = pic.getPictureData();
String extension = data.suggestFileExtension();
int row1 = anchor.getRow1();
int row2 = anchor.getRow2();
int col1 = anchor.getCol1();
int col2 = anchor.getCol2();
if(row1 == cell.getRowIndex() && col1 == cell.getColumnIndex()){
dimension = pic.getImageDimension();
this.anchor = anchor;
this.bytes = data.getData();
}
}
}
}
return this;
}
public Dimension getDimension() {
return dimension;
}
public void setDimension(Dimension dimension) {
this.dimension = dimension;
}
public byte[] getBytes() {
return bytes;
}
public void setBytes(byte[] bytes) {
this.bytes = bytes;
}
public ClientAnchor getAnchor() {
return anchor;
}
public void setAnchor(ClientAnchor anchor) {
this.anchor = anchor;
}
}
public class POIUtil {
public static int getRGB(Color color){
int result = 0x00FFFFFF;
int red = 0;
int green = 0;
int blue = 0;
if (color instanceof HSSFColor) {
HSSFColor hssfColor = (HSSFColor) color;
short[] rgb = hssfColor.getTriplet();
red = rgb[0];
green = rgb[1];
blue = rgb[2];
}
if (color instanceof XSSFColor) {
XSSFColor xssfColor = (XSSFColor) color;
byte[] rgb = xssfColor.getRgb();
red = (rgb[0] < 0) ? (rgb[0] + 256) : rgb[0];
green = (rgb[1] < 0) ? (rgb[1] + 256) : rgb[1];
blue = (rgb[2] < 0) ? (rgb[2] + 256) : rgb[2];
}
if(red != 0 || green != 0 || blue != 0){
result = new java.awt.Color(red, green, blue).getRGB();
}
return result;
}
public static int getBorderRBG(Workbook wb , short index){
int result = 0;
if(wb instanceof HSSFWorkbook){
HSSFWorkbook hwb = (HSSFWorkbook)wb;
HSSFColor color = hwb.getCustomPalette().getColor(index);
if(color != null){
result = getRGB(color);
}
}
if(wb instanceof XSSFWorkbook){
XSSFColor color = new XSSFColor();
color.setIndexed(index);
result = getRGB(color);
}
return result;
}
@SuppressWarnings("finally")
public static byte[] scale(byte[] bytes , double width, double height) {
BufferedImage bufferedImage = null;
BufferedImage bufTarget = null;
try {
ByteArrayInputStream bais = new ByteArrayInputStream(bytes);
bufferedImage = ImageIO.read(bais);
double sx = width / bufferedImage.getWidth();
double sy = height / bufferedImage.getHeight();
int type = bufferedImage.getType();
if (type == BufferedImage.TYPE_CUSTOM) {
ColorModel cm = bufferedImage.getColorModel();
WritableRaster raster = cm.createCompatibleWritableRaster((int)width, (int)height);
boolean alphaPremultiplied = cm.isAlphaPremultiplied();
bufTarget = new BufferedImage(cm, raster, alphaPremultiplied, null);
} else {
bufTarget = new BufferedImage((int)width, (int)height, type);
}
Graphics2D g = bufTarget.createGraphics();
g.setRenderingHint(RenderingHints.KEY_RENDERING, RenderingHints.VALUE_RENDER_QUALITY);
g.drawRenderedImage(bufferedImage, AffineTransform.getScaleInstance(sx, sy));
g.dispose();
if(bufTarget != null){
ByteArrayOutputStream baos = new ByteArrayOutputStream();
ImageIO.write(bufTarget, "png", baos);
byte[] result = baos.toByteArray();
return result;
}
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
}
public static void main(String[] args) throws Exception {
// FileInputStream fis = new FileInputStream(new File(directory+"副本Common-ReportList-AssistPDF-Temp.xls"));
// FileInputStream fis = new FileInputStream(new File("D:\\tmp.xls"));
FileInputStream fis1 = new FileInputStream(new File("D:\\pdfexport\\MAD 5-3-05-Octavia NF-20131025.xls"));
FileInputStream fis2 = new FileInputStream(new File("D:\\pdfexport\\MAD 6-1-47-Octavia NF-20131025.xls"));
FileInputStream fis3 = new FileInputStream(new File("D:\\pdfexport\\MAD 038-Superb FL DS-20131025.xls"));
//
FileOutputStream fos = new FileOutputStream(new File("D:\\test.pdf"));
//
List<ExcelObject> objects = new ArrayList<ExcelObject>();
objects.add(new ExcelObject("1.MAD 5-3-05-Octavia NF-20131025.xls",fis1));
objects.add(new ExcelObject("2.MAD 6-1-47-Octavia NF-20131025.xls",fis2));
objects.add(new ExcelObject("3.MAD 038-Superb FL DS-20131025.xls",fis3));
//
Excel2Pdf pdf = new Excel2Pdf(objects , fos);
pdf.convert();
}
代码完毕,如果有什么问题的可以给我留言,大家可以共同探讨一下!
7930
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
