Excel模板设置属性内容，图片填充，亲测有效！！！

ZY猪猪侠

于 2024-07-18 16:35:23 发布

阅读量68

点赞数 2

分类专栏：笔记文章标签： java excel

本文链接：https://blog.csdn.net/qq_45056878/article/details/140525244

版权

笔记专栏收录该内容

25 篇文章 0 订阅

订阅专栏

void downTemplate {
	// 读取文件资源
	ClassPathResource classPathResource = new ClassPathResource("template/" + templateName + ".xlsx");
	// 转成Excel对象
	XSSFWorkbook wb = new XSSFWorkbook(classPathResource.getInputStream());
	// 获取Excel页数
	XSSFSheet xss = wb.getSheetAt(0);
	// 自动计算模板中的公式
	xss.setForceFormulaRecalculation(true);
	// 设置单元格A1值为"Test"
	xss.getRow(0).getCell(0).setCellValue("Test");
	// 设置单元格样式
	// 给第一行第二列设置样式
	XSSFRow row = createInvRow(xss, 1);
	createCellStype(wb, row, 0)
	// 图片填充
	createPicture(wb, xss);
}

// 创建单元格样式对象
XSSFCell createCellStype(XSSFWorkbook wb, XSSFRow row, Integer cellIndex) {
	XSSFCellStyle cellStyle = wb.createCellStyle();
	cellStyle.setBorderBottom(BorderStyle.THIN);
	cellStyle.setBorderTop(BorderStyle.THIN);
	cellStyle.setBorderLeft(BorderStyle.THIN);
	cellStyle.setBorderRight(BorderStyle.THIN);
	if (cellIndex == 11 || cellIndex == 12) {
    	cellStyle.setBorderRight(BorderStyle.MEDIUM);
    }
	// 水平居中
	cellStyle.setAlignment(HorizontalAlignment.CENTER);
	// 样式应用到该单元格上
    XSSFCell cell = row.createCell(cellIndex);
    cell.setCellStyle(cellStyle);
    return cell;
}

// 找到需要插入的行数，并新建一个POI的row对象
XSSFRow createInvRow(XSSFSheet sheet, Integer rowIndex) {
    XSSFRow row;
    if (sheet.getRow(rowIndex) != null) {
        int lastRowNo = sheet.getLastRowNum();
        sheet.shiftRows(rowIndex, lastRowNo, 1);
    }
    row = sheet.createRow(rowIndex);
    return row;
}

void createPicture(XSSFWorkbook wb, XSSFSheet xss) throws IOException {
	 final ClassPathResource classPathResource = new ClassPathResource("template/图片.png");
	 final InputStream inputStream = classPathResource.getInputStream();
	 final byte[] data = toByteArray(inputStream);
	 XSSFDrawing drawingPatriarch = xss.createDrawingPatriarch();
	 [XSSFClientAnchor参数说明](https://wenku.csdn.net/answer/97627113b30fbec8c7e23a1c02b6f2ec)
	 XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 2, 0, 2, 4);
	 anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
	 final XSSFPicture picture = drawingPatriarch.createPicture(anchor, wb.addPicture(data, XSSFWorkbook.PICTURE_TYPE_PNG));
	 // 设置图片比列大小
	 picture.resize(0.8, 0.8);
}