电子档案目录PDF预览

最新推荐文章于 2023-06-16 10:38:35 发布

置顶田心王大可

最新推荐文章于 2023-06-16 10:38:35 发布

阅读量886

点赞数

分类专栏： easyexcel 文章标签： java

本文链接：https://blog.csdn.net/zhq505824802/article/details/119738056

版权

easyexcel 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

# 需求
档案电子化著录模块需要PDF预览功能方便打印标准A4纸质文档
模版如下图:

代码流程:

控制层:

	/**
	 * 预览档案目录
	 */
	@GetMapping("preview-category")
	@ApiOperationSupport(order = 12)
	@ApiOperation(value = "预览档案目录", notes = "传入documInfoId")
	public void previewCategory(@ApiIgnore @RequestParam Long documInfoId, HttpServletResponse response) throws Exception {
		documCategoryService.previewCategory(documInfoId, response);

 	}

实现:

	@Override
	@Transactional(rollbackFor = Exception.class)
	public void previewCategory(Long documInfoId, HttpServletResponse response) throws Exception
	{
		//获取通用模版数据
		List<DocumCategoryVO> documCategoryList = baseMapper.exportCategory();

		//获取所有子集
		documCategoryList = this.getChildren(documCategoryList, documInfoId);

		//查询档案 信息
		DocumInfo documInfo = documInfoService.getBaseMapper().selectById(documInfoId);
		//转化为easyExcel数据
		List<CategoryExcel> categoryExcels = this.change(documCategoryList);
		//excel转pdf
		changeToPdf(categoryExcels, documInfo, response);
	}

技术1:

easyExcel

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。

文档:EasyExcel · 语雀

先写model及配置

package org.springblade.modules.document.excel;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.*;
import com.aspose.cells.Color;
import com.aspose.cells.Style;
import lombok.Data;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xwpf.usermodel.Borders;

import java.io.Serializable;

/**
 *
 * @author zhq
 */
@Data
@ColumnWidth(30)
@HeadStyle(borderLeft = BorderStyle.THIN,borderBottom = BorderStyle.THIN,borderRight = BorderStyle.THIN,borderTop = BorderStyle.THIN,fillBackgroundColor = 5)
//@HeadFontStyle(fontHeightInPoints = 12)
@HeadRowHeight(20)
@ContentRowHeight(30)
//@ContentFontStyle(fontName = "宋体")
@ContentStyle(borderLeft = BorderStyle.THIN,borderBottom = BorderStyle.THIN,
	borderRight = BorderStyle.THIN,borderTop = BorderStyle.THIN,horizontalAlignment = HorizontalAlignment.CENTER)
public class CategoryExcel implements Serializable {
	private static final long serialVersionUID = 1L;

	@ColumnWidth(8)
	@ExcelProperty(value = "序号",index = 0)
	private String serialNumber;

	@ColumnWidth(33)
	@ExcelProperty(value = "材料名称",index = 1)
	private String name;

	@ColumnWidth(8)
	@ExcelProperty(value = {"材料形成时间","年"},index = 2)
	private String year;

	@ColumnWidth(8)
	@ExcelProperty(value = {"材料形成时间","月"},index = 3)
	private String month;

	@ColumnWidth(8)
	@ExcelProperty(value = {"材料形成时间","日"},index = 4)
	private String day;

	@ColumnWidth(8)
	@ExcelProperty(value = "页数",index = 5)
	private String pageNum;

	@ColumnWidth(8)
	@ExcelProperty(value = "备注",index = 6)
	private String remark;
}

easyExcel数据转化

@Override
	public void changeToPdf(List<CategoryExcel> categoryExcels, DocumInfo documInfo, HttpServletResponse response) throws Exception
	{
		String execlName = System.getProperty("user.dir") + File.separator + UUID.randomUUID() + ".xlsx";
		String pdfName = System.getProperty("user.dir") + File.separator + UUID.randomUUID() + ".pdf";

		ClassPathResource classPathResource = new ClassPathResource("templates/category.xlsx");

		int index = 1;
		int size = categoryExcels.size();
		int total = (int) Math.ceil((double) size / 20);
		//ExcelWriter excelWriter = EasyExcel.write(execlName).withTemplate(templateFileName).build();

		XSSFWorkbook workbook = new XSSFWorkbook(classPathResource.getInputStream());
		// 复制你数据的N-1条，本来第0个sheet有一条
		for (int i = 0; i < total; i++)
		{
			workbook.cloneSheet(0);
		}
		ByteArrayOutputStream bos = new ByteArrayOutputStream();
		workbook.write(bos);
		byte[] bArray = bos.toByteArray();
		InputStream is = new ByteArrayInputStream(bArray);

		ExcelWriter excelWriter = EasyExcel.write(execlName).withTemplate(is).build();

		for (int i = 1; i < total + 1; i++)
		{
			WriteSheet writeSheet = EasyExcel.writerSheet(i - 1, "Sheet" + (i - 1)).build();
			List<CategoryExcel> data = new ArrayList<>();
            //每页20条数据处理
			if (i == total)
			{
				data = categoryExcels.subList(20 * (i - 1), size);
				//填充空的数据
				for (int j = size; j < 20 * i; j++)
				{
					data.add(new CategoryExcel());
				}
			} else
			{
				data = categoryExcels.subList(20 * (i - 1), 20 * i);
			}
			excelWriter.fill(data, writeSheet);
            //填充模版里的第几页  总页数   材料名称  档案编号
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("document_no", documInfo.getDocumentNo());
			map.put("name", documInfo.getName());
			map.put("index", i);
			map.put("total", total);
			excelWriter.fill(map, writeSheet);
		}
		excelWriter.finish();


		Excel2PDF.excel2pdf(execlName, pdfName, total);
		documPageService.execl(pdfName, response);

	}

转pdf

用到apose-cells 8.5.2jar包

apose官网链接:File Format APIs for Word Excel PDF Email PowerPoint Barcode Images OCR Note & 3D

package org.springblade.modules.document.excel;

import com.aspose.cells.License;
import com.aspose.cells.PdfSaveOptions;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;
import com.lowagie.text.pdf.PdfPage;
import com.lowagie.text.pdf.PdfPageEvent;
import com.lowagie.text.pdf.PdfPages;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;

public class Excel2PDF {

    /**
     * 获取license 去除水印
     * @return
     */
    public static boolean getLicense() {
        boolean result = false;
        try {
            InputStream is = Excel2PDF.class.getClassLoader().getResourceAsStream("\\templates\\license.xml");
            License aposeLic = new License();
            aposeLic.setLicense(is);
            result = true;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * excel 转为pdf 输出。
     *
     * @param sourceFilePath  excel文件
     * @param desFilePathd  pad 输出文件目录
     */
    public static void excel2pdf(String sourceFilePath, String desFilePathd,int total) throws Exception{
        if (!getLicense()) { // 验证License 若不验证则转化出的pdf文档会有水印产生
            return;
        }
        try {
            Workbook wb = new Workbook(sourceFilePath);// 原始excel路径

            FileOutputStream fileOS = new FileOutputStream(desFilePathd);
            PdfSaveOptions pdfSaveOptions = new PdfSaveOptions();
			pdfSaveOptions.setDefaultFont("宋体");
			pdfSaveOptions.setOnePagePerSheet(true);

            //int[] autoDrawSheets={3};
            //当excel中对应的sheet页宽度太大时，在PDF中会拆断并分页。此处等比缩放。
            autoDraw(wb,total);

            //int[] showSheets={total};
            //隐藏workbook中不需要的sheet页。
            printSheetPage(wb,total);
            wb.save(fileOS, pdfSaveOptions);
            fileOS.flush();
            fileOS.close();
        } finally {
			File execlFile = new File(sourceFilePath);
			if (execlFile != null&&execlFile.exists())
			{
				execlFile.delete();
			}
		}
    }


    /**
     * 设置打印的sheet 自动拉伸比例
     * @param wb
     * @param page 自动拉伸的页的sheet数组
     */
    public static void autoDraw(Workbook wb,int total){
        if(total>0){
            for (int i = 0; i < total; i++) {
                wb.getWorksheets().get(i).getHorizontalPageBreaks().clear();
                wb.getWorksheets().get(i).getVerticalPageBreaks().clear();
            }
        }
    }


    /**
     * 隐藏workbook中不需要的sheet页。
     * @param wb
     * @param page 显示页的sheet数组
     */
    public static void printSheetPage(Workbook wb,int total){
        for (int i= 1; i < wb.getWorksheets().getCount(); i++)  {
            wb.getWorksheets().get(i).setVisible(false);
        }
        if(total==0){
            wb.getWorksheets().get(0).setVisible(true);
        }else{
            for (int i = 0; i < total; i++) {
                wb.getWorksheets().get(i).setVisible(true);
            }
        }
    }

}

使用的模版文件:

结果:

难点1:

模版复制多个sheet页 easyexcel没提供在下面评论给出的方案

// 模板地址
String templateFileName = BugReportDocServiceImplTest.class.getResource("/static/excel/sectionBugDetailListTemplate.xlsx").getPath();
// 从数据库获取并封装数据
List<SectionCheckExcelBO> sectionCheckExcelByLineId = bugReportDocService.findSectionCheckExcelByLineId(71L);
LocalDateTime localDateTime = LocalDateTime.now();
DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("HHmmss");
// 复制N个sheet的输出文件
String fileName = "d:/temp/abc"+dateTimeFormatter.format(localDateTime)+".xlsx";

  // 复制模板
File tempFile = new File(templateFileName);
FileInputStream fis = new FileInputStream(tempFile);
XSSFWorkbook workbook = new XSSFWorkbook(fis);
// 复制你数据的N-1条，本来第0个sheet有一条
for (int i = 0; i < sectionCheckExcelByLineId.size() -1 ; i++) {
	workbook.cloneSheet(0);
}
FileOutputStream out = new FileOutputStream(new File(fileName));
out.flush();
workbook.write(out);
out.close();
// 最后要输出的多sheet文件路径
String writeFile = "d:/temp/Write"+dateTimeFormatter.format(localDateTime)+".xlsx";
// easyexcel模板输出
	ExcelWriter excelWriter = EasyExcel.write(writeFile).withTemplate(fileName).build();
	for (int i = 0; i < sectionCheckExcelByLineId.size(); i++) {
		WriteSheet sheet = EasyExcel.writerSheet().build();
		sheet.setSheetNo(i);
		sheet.setSheetName("Sheet" + (i+1));
		SectionCheckExcelBO bo = sectionCheckExcelByLineId.get(i);
		System.out.println(JsonFormatUtil.formatJson(new Gson().toJson(bo)));
		excelWriter.fill(bo, sheet);
		FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
		excelWriter.fill(bo.getBugExcelBOList(), fillConfig, sheet);
	}
excelWriter.finish();
// 中心思想就是：通过poi把模板复制N条数据对应的N个模板sheet, 然后用easyexcel来输出
// 有种曲线救国的意思，poi容易oom，相对于XSSFWorkbook优化后的SXSSFWorkbook又不支持克隆
// 数据量大的话够呛

难点2：

apose不熟悉

PdfSaveOptions pdfSaveOptions = new PdfSaveOptions();
			pdfSaveOptions.setDefaultFont("宋体");
			pdfSaveOptions.setOnePagePerSheet(true);

            //int[] autoDrawSheets={3};
            //当excel中对应的sheet页宽度太大时，在PDF中会拆断并分页。此处等比缩放。
            autoDraw(wb,total);

            //int[] showSheets={total};
            //隐藏workbook中不需要的sheet页。
            printSheetPage(wb,total);

pdf只有单页表头乱跑问题

田心王大可

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
电子档案目录PDF预览

# 需求档案电子化著录模块需要PDF预览功能方便打印标准A4纸质文档模版如下图:代码流程:控制层: /** * 预览档案目录 */ @GetMapping("preview-category") @ApiOperationSupport(order = 12) @ApiOperation(value = "预览档案目录", notes = "传入documInfoId") public void previewCategory(@ApiIgnore ...
复制链接

扫一扫