Java操作Excel转Pdf(包含Excel里面的图表)
使用JxCell生成图表,用Aspose转PDF
导入jxcell.jar(下载地址:Edit/View Excel Spreadsheet with Jxcell in Java)
导入aspose-cells-8.5.2.jar
一.生成excel图表(竖柱状图,具体细节自己设置)
View m_view = new View();
RangeRef newRange = null;
try {
m_view.getLock();
//标题 setTextAsValue(行，列，值)；
m_view.setTextAsValue(0,1,"Jan");
m_view.setTextAsValue(0,2,"Feb");
m_view.setTextAsValue(0,3,"Mar");
m_view.setTextAsValue(0,4,"Apr");
//分项
m_view.setTextAsValue(1,0,"香蕉");
m_view.setTextAsValue(2,0,"大鸭梨");
m_view.setTextAsValue(3,0,"芒果");
m_view.setTextAsValue(4,0,"水果1");
m_view.setTextAsValue(5,0,"水果2");
m_view.setTextAsValue(6,0,"共计");
//
m_view.setTextAsValue(0,5,"time");
m_view.setNumber(1,5,1);
m_view.setNumber(2,5,2);
m_view.setNumber(3,5,3);
m_view.setNumber(4,5,4);
m_view.setNumber(5,5,5);
m_view.setNumber(6,5,6);
//数据区域随机赋值
for(int col = 1; col <= 4; col++) {
for (int row = 1; row <= 6; row++) {
m_view.setFormula(row, col, "rand()");//rand()为excle随机函数
}
}
//设置公式
m_view.setFormula(6, 1, "SUM(B1:B5)");
//选中单元格区域
m_view.setSelection("B7:E7");
//编辑复制 向右复制
m_view.editCopyRight();
//绘图区坐标addChart（左上列x，左上行y，右下列x，右下行y）
ChartShape chart = m_view.addChart(0, 9.1, 7, 20.4);
//图标形式
chart.setChartType(ChartShape.TypeColumn);
/*
TypeBar:横向柱状图
TypePie:饼状图
TypeLine:线状图
TypeArea:面积图
TypeDoughnut:圈图
TypeScatter:线点图
*/
//设置连接区域
chart.setLinkRange("Sheet1!$C$2", false);
//添加一个系列
chart.addSeries();
//系列名字
chart.setSeriesName(0, "Sheet1!$C$2");
//系列值
chart.setSeriesYValueFormula(0, "Sheet1!$C$3");
//系列分类
chart.setCategoryFormula("Sheet1!$B$3:$B$7");
chart.addSeries();
chart.setSeriesName(1, "Sheet1!$D$2");
chart.setSeriesYValueFormula(1, "Sheet1!$D$3:$D$7");
chart.addSeries();
chart.setSeriesName(2, "Sheet1!$E$2");
chart.setSeriesYValueFormula(2, "Sheet1!$E$3:$E$7");
chart.addSeries();
chart.setSeriesName(3, "Sheet1!$F$2");
chart.setSeriesYValueFormula(3, "Sheet1!$F$3:$F$7");
//设置横坐标标题
chart.setAxisTitle(ChartShape.XAxis, 0, "横坐标标题");
//设置纵坐标标题
chart.setAxisTitle(ChartShape.YAxis, 0, "纵坐标标题");
//设置图表样式
ChartFormat cf = chart.getChartFormat();
//设置背景色
cf.setPattern((short)1);
cf.setPatternFG(Color.LIGHT_GRAY.getRGB());
chart.setChartFormat(cf);
//设置绘图区颜色
cf = chart.getPlotFormat();
cf.setPattern((short)1);
cf.setPatternFG(new Color(204, 255, 255).getRGB());
chart.setPlotFormat(cf);
//设置横坐标文字大小
cf = chart.getAxisFormat(ChartShape.XAxis, 0);
cf.setFontSizeInPoints(8.5);
chart.setAxisFormat(ChartShape.XAxis, 0, cf);
//设置纵坐标文字大小
cf = chart.getAxisFormat(ChartShape.YAxis, 0);
cf.setFontSizeInPoints(8.5);
chart.setAxisFormat(ChartShape.YAxis, 0, cf);
//设置图标内标线样式
cf = chart.getSeriesFormat(0);//地0个
cf.setLineStyle((short)1);
cf.setLineWeight(3*20);
cf.setLineColor((new Color(0, 0, 128)).getRGB());
cf.setMarkerAuto(false);
cf.setMarkerStyle((short)0);
chart.setSeriesFormat(0, cf);
cf = chart.getSeriesFormat(1);
cf.setLineStyle((short)1);
cf.setLineWeight(3*20);
cf.setLineColor((new Color(255, 0, 255)).getRGB());
cf.setMarkerAuto(false);
cf.setMarkerStyle((short)0);
chart.setSeriesFormat(1, cf);
cf = chart.getSeriesFormat(2);
cf.setLineStyle((short)1);
cf.setLineWeight(3*20);
cf.setLineColor((new Color(255, 255, 0)).getRGB());
cf.setMarkerAuto(false);
cf.setMarkerStyle((short)0);
chart.setSeriesFormat(2, cf);
cf = chart.getSeriesFormat(3);
cf.setLineStyle((short)1);
cf.setLineWeight(3*20);
cf.setLineColor((new Color(0, 255, 255)).getRGB());
cf.setMarkerAuto(false);
cf.setMarkerStyle((short)0);
chart.setSeriesFormat(3, cf);
//主格网
cf = chart.getMajorGridFormat(ChartShape.YAxis, 0);
cf.setLineStyle((short)2);
cf.setLineColor((new Color(255, 0, 0)).getRGB());
cf.setLineAuto();
chart.setMajorGridFormat(ChartShape.YAxis, 0, cf);
//图利位置
chart.setLegendPosition(ChartFormat.LegendPlacementRight);
//图利样式
cf = chart.getLegendFormat();
cf.setFontBold(true);
cf.setFontSizeInPoints(8);
chart.setLegendFormat(cf);
//excel写出路径
m_view.write("D://out.xls");
System.out.println("end");
}
catch (Exception e) {
System.out.println(e.getMessage());
}
finally
{
m_view.releaseLock();
}
二.转PDF
try {
File pdfFile = new File("D://123.pdf");// 输出路径
Workbook wb = new Workbook("D://out.xls");// 原始excel路径
FileOutputStream fileOS = new FileOutputStream(pdfFile);
wb.save(fileOS, com.aspose.cells.SaveFormat.PDF);
fileOS.close();
} catch (Exception e) {
e.printStackTrace();
}
三.去水印
添加excel-license.xml
<License>
<Data>
<Products>
<Product>Aspose.Total for Java</Product>
<Product>Aspose.Words for Java</Product>
</Products>
<EditionType>Enterprise</EditionType>
<SubscriptionExpiry>20991231</SubscriptionExpiry>
<LicenseExpiry>20991231</LicenseExpiry>
<SerialNumber>8bfe198c-7f0c-4ef8-8ff0-acc3237bf0d7</SerialNumber>
</Data>
<Signature>sNLLKGMUdF0r8O1kKilWAGdgfs2BvJb/2Xp8p5iuDVfZXmhppo+d0Ran1P9TKdjV4ABwAgKXxJ3jcQTqE/2IRfqwnPf8itN8aFZlV3TJPYeD3yWE7IT55Gz6EijUpC7aKeoohTb4w2fpox58wWoF3SNp6sK6jDfiAUGEHYJ9pjU=</Signature>
</License>
去水印代码
public static void main(String[] args) throws GeneralException {
/*
src:excel路径
dest:处理后的pdf
*/
convert("D://out.xls","D://123.pdf");
}
public static void convert(String src,String dest) throws GeneralException {
// 验证License 若不验证则转化出的pdf文档会有水印产生
if (!verifyLicense()) {
throw new GeneralException("License验证失败！");
}
try (FileOutputStream fileOS = new FileOutputStream(new File(dest));) {
Workbook wb = new Workbook(src);
wb.save(fileOS, com.aspose.cells.SaveFormat.PDF);
} catch (Exception e) {
e.printStackTrace();
}
}
private static boolean verifyLicense() {
boolean result = false;
try {
InputStream is = ExcelGenerateTable.class.getResourceAsStream("/license.xml");
License aposeLic = new License();
aposeLic.setLicense(is);
result = true;
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
