背景
业务中,客户需要导出各种数据统计报表,如果客户要求,我希望导出的报表中能插入这些数据的统计图表该如何操作,如图:
传统POI
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.2</version></dependency>
package com.test.other.demo.report;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.*;import org.apache.poi.util.IOUtils;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.junit.platform.commons.util.StringUtils;import java.io.*;/** * POI 往excel插入图片 */public class POIExport { private Workbook workbook; public static void main(String[] args) throws Exception { Workbook workbook = getWorkBook("/Users/hui.yang/Desktop/excel演示/poi/演示1.xlsx"); Sheet sheet = workbook.getSheet("sheet1"); InputStream inputStream = new FileInputStream("/Users/hui.yang/Desktop/excel演示/poi/演示图表1.png"); byte[] bytes = IOUtils.toByteArray(inputStream); // 这里根据实际需求选择图片类型 int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG); CreationHelper helper = workbook.getCreationHelper(); ClientAnchor anchor = helper.createClientAnchor(); Drawing drawing = sheet.createDrawingPatriarch(); anchor.setRow1(10); //插入行 anchor.setCol1(0); // 插入列 // 插入图片 Picture pict = drawing.createPicture(anchor, pictureIdx); // 调整图片占单元格百分比的大小,1.0就是100% pict.resize(8, 5); //临时缓冲区 ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); //创建临时文件 try { workbook.write(byteArrayOut); } catch (IOException e) { e.printStackTrace(); } finally { workbook.close(); } byte[] bookByteAry = byteArrayOut.toByteArray(); File file = new File("/Users/hui.yang/Desktop/excel演示/poi/演示插入结果.xlsx"); if (!file.getParentFile().exists()) { file.getParentFile().mkdirs(); } if (!file.exists()) { file.createNewFile(); } FileOutputStream fos = new FileOutputStream(file); fos.write(bookByteAry, 0, bookByteAry.length); fos.flush(); fos.close(); } /** * 获取workBoot 兼容xlsx xls */ public static Workbook getWorkBook(String filePath) throws Exception { Workbook workbook = null; if (StringUtils.isBlank(filePath)) { // throw new RuntimeException("路径错误!"); } else if (filePath.toLowerCase().endsWith("xls")) { workbook = new HSSFWorkbook(new FileInputStream(filePath)); } else if (filePath.toLowerCase().endsWith("xlsx")) { workbook = new XSSFWorkbook(new FileInputStream(filePath)); } else { // throw new RuntimeException("路径错误!"); } return workbook; }}
e-iceblue(推荐)
<dependency> <groupId>e-iceblue</groupId> <artifactId>spire.xls.free</artifactId> <version>5.1.0</version></dependency>
package com.test.other.demo.report;import com.spire.xls.*;/** * e-iceblue 往excel插入图片 */public class EiceBlueExport { public static void main(String[] args) { //创建Workbook实例 Workbook workbook = new Workbook(); //加载Excel文档 workbook.loadFromFile("/Users/hui.yang/Desktop/excel演示/e-iceblue/演示1.xlsx"); //获取第一张工作表 Worksheet sheet = workbook.getWorksheets().get(0); //设置图表插入的位置 ExcelPicture pic = sheet.getPictures().add(10, 1, "/Users/hui.yang/Desktop/excel演示/e-iceblue/演示图表1.png"); //设置图片的宽度和高度 pic.setWidth(600); pic.setHeight(120); //保存文档 workbook.saveToFile("/Users/hui.yang/Desktop/excel演示/e-iceblue/演示插入结果.xlsx", ExcelVersion.Version2013); //文档转pdf transFileToPdf("/Users/hui.yang/Desktop/excel演示/e-iceblue/庞源在线-安全-安全周报-20230303130801.xlsx"); } /** * 转pdf */ public static void transFileToPdf(String fillPath) { Workbook wb = new Workbook(); wb.loadFromFile(fillPath); wb.getWorksheets().get(0); ConverterSetting converterSetting = new ConverterSetting(); converterSetting.setSheetFitToPage(true); wb.setConverterSetting(converterSetting); //调用方法保存为PDF格式 wb.saveToFile("/Users/hui.yang/Desktop/excel演示/e-iceblue/庞源在线-安全-安全周报-20230303130801.pdf", FileFormat.PDF); }}
方案对比
1 poi对xlxs xls不同版本有不同的语法,需要单独写个方法去获取WorkBook,而e-iceblue直接封装好方法,傻瓜式操作2 poi需设置图片类型,文件流操作,而e-iceblue直接封装好方法,傻瓜式操作3 e-iceblue有丰富的文档转换工具,如转为PDF,如以上代码,我这边转换的pdf,看了下再也不用在***网站转pdf了, 骚的一批,当然也封装了其他文档转换,这里不赘述
版权声明:内容来源于互联网和用户投稿 如有侵权请联系删除