SpringBoot入门之七 文件处理之Excel PDF
1. 整合EasyExcel
easyexcel是阿里开源的解析excel的工具,可以把它看作对poi的优化版。
Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存,poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题,但POI还是有一些缺陷,比如07版Excel解压缩以及解压后存储都是在内存中完成的,内存消耗依然很大。easyexcel重写了poi对07版Excel的解析,能够原本一个3M的excel用POI sax依然需要100M左右内存降低到KB级别,并且再大的excel不会出现内存溢出,03版依赖POI的sax模式。在上层做了模型转换的封装,让使用者更加简单方便。
1.1 引入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.2-beta5</version>
</dependency>
1.2、实体类
@Data
@Builder
public class Book extends BaseRowModel {
@ExcelProperty(value = "书名", index = 0)
private String name;
@ExcelProperty(value = "编号", index = 1)
private String code;
@ExcelProperty(value = "价格", index = 2)
private Integer price;
public Book() {}
public Book(String name, String code, Integer price) {
this.name = name;
this.code = code;
this.price = price;
}
}
1.3 工具方法
EasyExcelListener
@Data
public class EasyExcelListener extends AnalysisEventListener {
/**
* 自定义用于暂时存储data。 可以通过实例获取该值
*/
private List<Object> datas = new ArrayList<>();
/**
* 通过 AnalysisContext 对象还可以获取当前 sheet,当前行等数据
*/
@Override
public void invoke(Object object, AnalysisContext context) {
// 数据存储到list,供批量处理,或后续自己业务逻辑处理。
datas.add(object);
// 根据业务自行 do something
doSomething();
/*
如数据过大,可以进行定量分批处理
if(datas.size()<=100){
datas.add(object);
}else {
doSomething();
datas = new ArrayList<Object>();
}
*/
}
/**
* 根据业务自行实现该方法
*/
private void doSomething() {}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
/*
datas.clear();
解析结束销毁不用的资源
*/
}
}
EasyExcelUtil
public class EasyExcelUtil {
/**
* 读取 Excel(多个 sheet)
*
* @param excel 文件
* @param rowModel 实体类映射,继承 BaseRowModel 类
* @return Excel 数据 list
*/
public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel) {
EasyExcelListener EasyExcelListener = new EasyExcelListener();
ExcelReader reader = getReader(excel, EasyExcelListener);
if (reader == null) {
return null;
}
for (Sheet sheet : reader.getSheets()) {
if (rowModel != null) {
sheet.setClazz(rowModel.getClass());
}
reader.read(sheet);
}
return EasyExcelListener.getDatas();
}
/**
* 读取某个 sheet 的 Excel
*
* @param excel 文件
* @param rowModel 实体类映射,继承 BaseRowModel 类
* @param sheetNo sheet 的序号 从1开始
* @return Excel 数据 list
*/
public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo) {
return readExcel(excel, rowModel, sheetNo, 1);
}
/**
* 读取某个 sheet 的 Excel
*
* @param excel 文件
* @param rowModel 实体类映射,继承 BaseRowModel 类
* @param sheetNo sheet 的序号 从1开始
* @param headLineNum 表头行数,默认为1
* @return Excel 数据 list
*/
public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo, int headLineNum) {
EasyExcelListener EasyExcelListener = new EasyExcelListener();
ExcelReader reader = getReader(excel, EasyExcelListener);
if (reader == null) {
return null;
}
reader.read(new Sheet(sheetNo, headLineNum, rowModel.getClass()));
return EasyExcelListener.getDatas();
}
/**
* 导出 Excel :一个 sheet,带表头
*
* @param response HttpServletResponse
* @param list 数据 list,每个元素为一个 BaseRowModel
* @param fileName 导出的文件名
* @param sheetName 导入文件的 sheet 名
* @param object 映射实体类,Excel 模型
*/
public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list, String fileName,
String sheetName, BaseRowModel object) {
ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
Sheet sheet = new Sheet(1, 0, object.getClass());
sheet.setSheetName(sheetName);
TableStyle tableStyle = new TableStyle();
tableStyle.setTableContentBackGroundColor(IndexedColors.WHITE);
Font font = new Font();
font.setFontHeightInPoints((short)9);
tableStyle.setTableHeadFont(font);
tableStyle.setTableContentFont(font);
sheet.setTableStyle(tableStyle);
writer.write(list, sheet);
writer.finish();
}
/**
* 导出 Excel :多个 sheet,带表头
*
* @param response HttpServletResponse
* @param list 数据 list,每个元素为一个 BaseRowModel
* @param fileName 导出的文件名
* @param sheetName 导入文件的 sheet 名
* @param object 映射实体类,Excel 模型
*/
public static EasyExcelWriterFactory writeExcelWithSheets(HttpServletResponse response,
List<? extends BaseRowModel> list, String fileName, String sheetName, BaseRowModel object) {
EasyExcelWriterFactory writer =
new EasyExcelWriterFactory(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
Sheet sheet = new Sheet(1, 0, object.getClass());
sheet.setSheetName(sheetName);
sheet.setTableStyle(getTableStyle());
writer.write(list, sheet);
return writer;
}
/**
* 导出融资还款情况表
*
* @param response
* @param list
* @param fileName
* @param sheetName
* @param object
*/
public static void writeFinanceRepayment(HttpServletResponse response, List<? extends BaseRowModel> list,
String fileName, String sheetName, BaseRowModel object) {
ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
Sheet sheet = new Sheet(1, 0, object.getClass());
sheet.setSheetName(sheetName);
sheet.setTableStyle(getTableStyle());
writer.write(list, sheet);
for (int i = 1; i <= list.size(); i += 4) {
writer.merge(i, i + 3, 0, 0);
writer.merge(i, i + 3, 1, 1);
}
writer.finish();
}
/**
* 导出文件时为Writer生成OutputStream
*/
private static OutputStream getOutputStream(String fileName, HttpServletResponse response) {
// 创建本地文件
fileName = fileName + ".xls";
try {
fileName = new String(fileName.getBytes(), "ISO-8859-1");
response.addHeader("Content-Disposition", "filename=" + fileName);
return response.getOutputStream();
} catch (Exception e) {
throw new RuntimeException("导出异常!");
}
}
/**
* 返回 ExcelReader
*
* @param excel 需要解析的 Excel 文件
* @param EasyExcelListener new EasyExcelListener()
*/
private static ExcelReader getReader(MultipartFile excel, EasyExcelListener EasyExcelListener) {
String filename = excel.getOriginalFilename();
if (filename == null
|| (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
throw new RuntimeException("文件格式错误!");
}
InputStream inputStream;
try {
inputStream = new BufferedInputStream(excel.getInputStream());
return new ExcelReader(inputStream, null, EasyExcelListener, false);
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
/**
* 资金收支导出 Excel :一个 sheet,带表头
*
* @param response HttpServletResponse
* @param list 数据 list,每个元素为一个 BaseRowModel
* @param fileName 导出的文件名
* @param sheetName 导入文件的 sheet 名
* @param object 映射实体类,Excel 模型
*/
public static void exportFundBudgetExcel(HttpServletResponse response, List<? extends BaseRowModel> list,
String fileName, String sheetName, BaseRowModel object) throws IOException {
ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
Sheet sheet = new Sheet(1, 0, object.getClass());
sheet.setSheetName(sheetName);
sheet.setTableStyle(getTableStyle());
writer.write(list, sheet);
writer.merge(2, 3, 0, 0);
writer.merge(4, 13, 0, 0);
writer.merge(14, 14, 0, 1);
writer.finish();
}
/**
* 读取Excel表格数据,封装成实体
*
* @param inputStream
* @param clazz
* @param sheetNo
* @param headLineMun
* @return
*/
public static Object readExcel(InputStream inputStream, ExcelTypeEnum excelType,
Class<? extends BaseRowModel> clazz, Integer sheetNo, Integer headLineMun) {
if (null == inputStream) {
throw new NullPointerException("the inputStream is null!");
}
EasyExcelListener listener = new EasyExcelListener();
ExcelReader reader = new ExcelReader(inputStream, excelType, null, listener);
reader.read(new Sheet(sheetNo, headLineMun, clazz));
return listener.getDatas();
}
public static Object readExcel(InputStream inputStream, ExcelTypeEnum excelType,
Class<? extends BaseRowModel> clazz) {
if (null == inputStream) {
throw new NullPointerException("the inputStream is null!");
}
EasyExcelListener listener = new EasyExcelListener();
ExcelReader reader = new ExcelReader(inputStream, excelType, null, listener);
// reader.read(new Sheet(sheetNo, headLineMun, clazz));
for (Sheet sheet : reader.getSheets()) {
if (clazz != null) {
sheet.setClazz(clazz);
}
reader.read(sheet);
}
return listener.getDatas();
}
/**
* 根据输入流,判断为xls还是xlsx,该方法原本存在于easyexcel 1.1.0 的ExcelTypeEnum中。
*/
public static ExcelTypeEnum valueOf(InputStream inputStream) {
try {
FileMagic fileMagic = FileMagic.valueOf(inputStream);
if (FileMagic.OLE2.equals(fileMagic)) {
return ExcelTypeEnum.XLS;
}
if (FileMagic.OOXML.equals(fileMagic)) {
return ExcelTypeEnum.XLSX;
}
throw new RuntimeException("excelTypeEnum can not null");
} catch (IOException e) {
throw new RuntimeException(e);
}
}
/**
* 设置全局样式
*
* @return
*/
private static TableStyle getTableStyle() {
TableStyle tableStyle = new TableStyle();
tableStyle.setTableContentBackGroundColor(IndexedColors.WHITE);
Font font = new Font();
font.setBold(true);
font.setFontHeightInPoints((short)9);
tableStyle.setTableHeadFont(font);
Font fontContent = new Font();
fontContent.setFontHeightInPoints((short)9);
tableStyle.setTableContentFont(fontContent);
return tableStyle;
}
}
EasyExcelWriterFactory
public class EasyExcelWriterFactory extends ExcelWriter {
private OutputStream outputStream;
private int sheetNo = 1;
public EasyExcelWriterFactory(OutputStream outputStream, ExcelTypeEnum typeEnum) {
super(outputStream, typeEnum);
this.outputStream = outputStream;
}
public EasyExcelWriterFactory write(List<? extends BaseRowModel> list, String sheetName, BaseRowModel object) {
this.sheetNo++;
try {
Sheet sheet = new Sheet(sheetNo, 0, object.getClass());
sheet.setSheetName(sheetName);
this.write(list, sheet);
} catch (Exception ex) {
ex.printStackTrace();
try {
outputStream.flush();
} catch (IOException e) {
e.printStackTrace();
}
}
return this;
}
@Override
public void finish() {
super.finish();
try {
outputStream.flush();
} catch (IOException e) {
e.printStackTrace();
}
}
}
1.4 业务服务类
@Service
public class BookService {
public List<Book> readBookSheet(InputStream inputStream, ExcelTypeEnum excelType) throws FileNotFoundException {
try {
Object datas = EasyExcelUtil.readExcel(inputStream, excelType, Book.class);
if (datas.getClass().equals(ArrayList.class)) {
List<Book> result = (List<Book>)datas;
return result;
} else {
return null;
}
} catch (Exception e) {
e.printStackTrace();
return null;
} finally {
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
1.5 测试类
@RunWith(SpringRunner.class)
@SpringBootTest
public class SpringbootTeach7ApplicationTests {
@Autowired
private BookService bookService;
@Test
public void readBookByEasyexcel() throws FileNotFoundException {
InputStream inputStream = new FileInputStream(new File("C:\\Users\\indexvc\\Downloads\\20200312\\test1.xlsx"));
// Object book = EasyExcelUtil.readExcel(inputStream, ExcelTypeEnum.XLSX, Book.class, 1, 2);
// System.out.println(book);
//
List<Book> result = bookService.readBookSheet(inputStream, ExcelTypeEnum.XLSX);
result.stream().forEach(System.out::println);
}
@Test
public void writeBookByEasyexcel() {
// 文件输出位置
OutputStream out = null;
try {
out = new FileOutputStream("C:\\Users\\indexvc\\Downloads\\20200312\\test1.xlsx");
ExcelWriter writer = EasyExcelFactory.getWriter(out);
// 写仅有一个 Sheet 的 Excel 文件, 此场景较为通用
Sheet sheet1 = new Sheet(1, 0, Book.class);
// 第一个 sheet 名称
sheet1.setSheetName("sheet1");
// 写数据到 Writer 上下文中
// 入参1: 数据库查询的数据list集合
// 入参2: 要写入的目标 sheet
List<Book> bookList = new ArrayList<>();
for (int i = 0; i < 100; i++) {
Book book = Book.builder().name("天龙八部" + i).code("1").price(i).build();
bookList.add(book);
}
writer.write(bookList, sheet1);
// 将上下文中的最终 outputStream 写入到指定文件中
writer.finish();
} catch (FileNotFoundException e) {
e.printStackTrace();
} finally {
try {
// 关闭流
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
源码地址
https://gitee.com/xiaolaifeng/sample.springboot/tree/master/springboot-teach7
2. 整合itext pdf文件处理
2.1 引入pom
<!-- https://mvnrepository.com/artifact/com.itextpdf/itextpdf -->
<dependency>
<groupId>com.itextpdf</groupId>
<artifactId>itextpdf</artifactId>
<version>5.5.13</version>
</dependency>
<dependency>
<groupId>org.apache.pdfbox</groupId>
<artifactId>pdfbox</artifactId>
<version>1.7.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.itextpdf.tool/xmlworker -->
<dependency>
<groupId>com.itextpdf.tool</groupId>
<artifactId>xmlworker</artifactId>
<version>5.5.13</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.5</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-freemarker</artifactId>
</dependency>
<!-- 加密 -->
<dependency>
<groupId>org.bouncycastle</groupId>
<artifactId>bcprov-jdk15on</artifactId>
<version>1.60</version>
</dependency>
2.2 controller源码
@RestController
public class PdfController {
@Value("${DEST}")
private String dest;
@Value("${HTML}")
private String html;
@Autowired
private Configuration freemarkerCfg;
private void createPdf(String content, String dest) throws IOException, DocumentException {
// step 1
Document document = new Document();
// step 2
PdfWriter writer = PdfWriter.getInstance(document, new FileOutputStream(dest));
// step 3
document.open();
// step 4
XMLWorkerFontProvider fontImp = new XMLWorkerFontProvider(XMLWorkerFontProvider.DONTLOOKFORFONTS);
XMLWorkerHelper.getInstance().parseXHtml(writer, document, new ByteArrayInputStream(content.getBytes("UTF-8")),
null, Charset.forName("UTF-8"), fontImp);
// step 5
document.close();
}
/**
* freemarker渲染html
*/
private String freeMarkerRender(Map<String, Object> data, String htmlTmp) {
Writer out = new StringWriter();
try {
// Template template = freemarkerCfg.getTemplate(htmlTmp);
Template template = freemarkerCfg.getTemplate(htmlTmp, "utf-8");
// 将合并后的数据和模板写入到流中,这里使用的字符流
template.process(data, out);
out.flush();
return out.toString();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
out.close();
} catch (IOException ex) {
ex.printStackTrace();
}
}
return null;
}
@RequestMapping(value = "view-pdf")
public void showPdf(HttpServletResponse response) throws IOException, DocumentException {
// 需要填充的数据
Map<String, Object> data = new HashMap<>(16);
data.put("name", " robin");
String content = freeMarkerRender(data, html);
// 创建pdf
createPdf(content, dest);
// 读取pdf并预览
readPdf(response, dest);
}
/**
* 读取本地pdf,这里设置的是预览
*/
private void readPdf(HttpServletResponse response, String dest) {
response.reset();
response.setContentType("application/pdf");
try {
File file = new File(dest);
FileInputStream fileInputStream = new FileInputStream(file);
OutputStream outputStream = response.getOutputStream();
IOUtils.write(IOUtils.toByteArray(fileInputStream), outputStream);
response.setHeader("Content-Disposition", "inline; filename= file");
outputStream.flush();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
2.3 application.properties
server.port=8081
DEST= target/HelloWorld.pdf
HTML= pdf.ftl
spring.freemarker.charset= UTF-8
spring.freemarker.allow-request-override= false
spring.freemarker.cache= false
spring.freemarker.expose-request-attributes= false
spring.freemarker.expose-session-attributes= false
spring.freemarker.content-type= text/html
spring.freemarker.template-loader-path= classpath:/templates/
spring.freemarker.expose-spring-macro-helpers= false
spring.freemarker.check-template-location= true
spring.freemarker.enabled= true
2.4 用freemark解析的文件模板
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8"/>
<title>Title</title>
<style>
body {
font-family: SimHei;
}
.pos {
position: absolute;
left: 100px;
top: 150px
}
</style>
</head>
<body>
<div class="blue pos">
<h1>你好,${name}</h1>
<h3>hello world</h3>
</div>
</body>
</html>
执行启动文件 ,访问效果如下
## 2.5 pdf文件加水印 加密
public class ItextUtil {
public static BaseFont baseFont() {
BaseFont baseFont = null;
try {
baseFont = BaseFont.createFont("/fonts/simhei.ttf", BaseFont.IDENTITY_H, BaseFont.EMBEDDED);
} catch (Exception e) {
e.printStackTrace();
}
return baseFont;
}
/**
* 插入图片水印
*
* @param InPdfFile
* @param outPdfFile
* @param markImagePath
* @param pageSize
* @throws Exception
*/
public static void addPdfMark(String InPdfFile, String outPdfFile, String markImagePath, int pageSize)
throws Exception {
PdfReader reader = new PdfReader(InPdfFile, "PDF".getBytes());
PdfStamper stamp = new PdfStamper(reader, new FileOutputStream(outPdfFile));
Image img = Image.getInstance(markImagePath);// 插入水印
img.setAbsolutePosition(90, 90);
for (int i = 1; i <= pageSize; i++) {
PdfContentByte under = stamp.getUnderContent(i);
under.addImage(img);
}
stamp.close();// 关闭
File tempfile = new File(InPdfFile);
if (tempfile.exists()) {
tempfile.delete();
}
}
/**
* 插入文字水印
*
* @param inputFile
* @param outputFile
* @param waterMarkName
* @throws IOException
*/
public static void addPdfTextMark(String inputFile, String outputFile, String waterMarkName) throws IOException {
addPdfTextMark(inputFile, outputFile, waterMarkName, "black");
}
/**
* 插入文本水印
*
* @param inputFile
* @param outputFile
* @param waterMarkName
* @param color 文字颜色
* @throws IOException
*/
public static void addPdfTextMark(String inputFile, String outputFile, String waterMarkName, String color)
throws IOException {
PdfReader reader;
try {
reader = new PdfReader(inputFile);
File outputFileTemp = new File(outputFile);
if (outputFileTemp.exists()) {
outputFileTemp.delete();
outputFileTemp.createNewFile();
}
File parentFile = new File(outputFileTemp.getParentFile().getAbsolutePath());
if (!parentFile.exists()) {
parentFile.mkdirs();
}
PdfStamper stamper;
stamper = new PdfStamper(reader, new FileOutputStream(outputFile));
addPdfTextMark(stamper, waterMarkName, color);
} catch (DocumentException e) {
e.printStackTrace();
throw new IOException("文件加水印失败");
} catch (IOException e) {
e.printStackTrace();
throw e;
}
}
/**
* 加密
*
* @param stamper
* @return
* @throws Exception
*/
private static PdfStamper doEncryption(PdfStamper stamper) throws Exception {
String modifyPasswd = "123";
// 读pdf无需密码 , 修改pdf密码为 modifyPasswd=123
stamper.setEncryption(null, modifyPasswd.getBytes(), PdfWriter.ALLOW_FILL_IN | PdfWriter.ALLOW_PRINTING,
PdfWriter.STANDARD_ENCRYPTION_128);
return stamper;
}
private static void addPdfTextMark(PdfStamper stamper, String waterMarkName, String color) {
try {
doEncryption(stamper);
// 设置字体
BaseFont base = ItextUtil.baseFont();
// 获取总页数
int total = stamper.getReader().getNumberOfPages() + 1;
// 获取文件的页面的长和高
Document doc1 = new Document(stamper.getReader().getPageSize(1));
float width = doc1.getPageSize().getWidth();
float height = doc1.getPageSize().getHeight() + 100;
// 设置字体大小
int fontsize = 14;
// 图片位置
PdfContentByte content;
for (int i = 1; i < total; i++) {
content = stamper.getOverContent(i);
content.saveState();
// set Transparency
PdfGState gs = new PdfGState();
gs.setFillOpacity(0.1f);// 设置透明度为0.1
content.setGState(gs);
content.beginText();
if (StringUtils.isEmpty(color) || color.equals("white")) {
content.setColorFill(BaseColor.WHITE);
} else if (color.equals("red")) {
content.setColorFill(BaseColor.RED);
}
content.setFontAndSize(base, fontsize);
int lean = 45;
JLabel label = new JLabel();
FontMetrics metrics;
int textH = 0;
int textW = 0;
label.setText(waterMarkName);
metrics = label.getFontMetrics(label.getFont());
textH = metrics.getHeight() * 16;// 字符串的高, 只和字体有关
textW = metrics.stringWidth(label.getText());// 字符串的宽
for (int j = 1; j < height; j = j + 90) {
for (int k = 1; k < width; k = k + textW) {
content.showTextAligned(Element.ALIGN_LEFT, waterMarkName, k, j, lean);
}
}
content.endText();
content.restoreState();// 注意这里必须调用一次restoreState 否则设置无效
}
stamper.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args) throws IOException {
try {
} catch (Exception e) {
e.printStackTrace();
}
try {
addPdfTextMark("E:\\workspace-train\\springboot-teach8\\target\\HelloWorld.pdf",
"E:\\workspace-train\\springboot-teach8\\target\\test@.pdf", "robin推荐,保密文件仅供测试使用");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
源码地址
https://gitee.com/xiaolaifeng/sample.springboot/tree/master/springboot-teach8