From 7293f04d18fb2e882e152f962c3aa1d8fcb1f93e Mon Sep 17 00:00:00 2001
From: Administrator <admin>
Date: Wed, 17 Nov 2021 15:46:35 +0800
Subject: [PATCH] 证书打印信息导出修改
---
src/main/java/org/springblade/modules/accreditation/controller/AccreditationRecordsController.java | 275 ++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 274 insertions(+), 1 deletions(-)
diff --git a/src/main/java/org/springblade/modules/accreditation/controller/AccreditationRecordsController.java b/src/main/java/org/springblade/modules/accreditation/controller/AccreditationRecordsController.java
index 4900cc0..4e72d3a 100644
--- a/src/main/java/org/springblade/modules/accreditation/controller/AccreditationRecordsController.java
+++ b/src/main/java/org/springblade/modules/accreditation/controller/AccreditationRecordsController.java
@@ -1,11 +1,20 @@
package org.springblade.modules.accreditation.controller;
import com.alibaba.excel.EasyExcel;
+import com.alibaba.excel.ExcelWriter;
+import com.alibaba.excel.enums.WriteDirectionEnum;
+import com.alibaba.excel.write.metadata.WriteSheet;
+import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.baomidou.mybatisplus.core.metadata.IPage;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import lombok.AllArgsConstructor;
import org.apache.commons.codec.Charsets;
+import org.apache.poi.hssf.usermodel.*;
+import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.ss.util.CellRangeAddress;
+import org.apache.poi.xssf.usermodel.*;
+import org.springblade.common.utils.ConvertUtil;
import org.springblade.core.mp.support.Condition;
import org.springblade.core.mp.support.Query;
import org.springblade.core.tool.api.R;
@@ -14,15 +23,22 @@
import org.springblade.modules.FTP.FtpUtil;
import org.springblade.modules.accreditation.excel.ExportSecurityBookPaperExcel;
import org.springblade.modules.accreditation.excel.ExportSecurityPaperExcel;
+import org.springblade.modules.accreditation.excel.ExportSecurityPaperExcelVO;
import org.springblade.modules.system.entity.User;
import org.springblade.modules.system.service.IUserService;
import org.springblade.modules.accreditation.entity.AccreditationRecords;
import org.springblade.modules.accreditation.service.AccreditationRecordsService;
import org.springblade.modules.accreditation.vo.AccreditationRecordsVo;
+import org.springframework.core.io.ClassPathResource;
+import org.springframework.core.io.Resource;
import org.springframework.web.bind.annotation.*;
+import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
-import java.io.IOException;
+import java.awt.image.BufferedImage;
+import java.io.*;
+import java.net.MalformedURLException;
+import java.net.URL;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
@@ -274,4 +290,261 @@
}
+ /**
+ * 导出证书制证信息(包含照片)
+ */
+ @GetMapping("export-security-book-papers")
+ public void exportSecurityBookPapers(AccreditationRecordsVo accreditationRecords, HttpServletResponse response) throws Exception {
+ //按条件查询成绩数据
+ List<AccreditationRecordsVo> excels = accreditationRecordsService.exportSecurityBookPapers(accreditationRecords);
+ List<ExportSecurityPaperExcelVO> list = new ArrayList<>();
+ //数据转换封装
+ excels.forEach(accreditationRecordsVo -> {
+ ExportSecurityPaperExcelVO exportSecurityPaperExcelVO = new ExportSecurityPaperExcelVO();
+ //如果头像有
+ if (null != accreditationRecordsVo.getAvatar() && accreditationRecordsVo.getAvatar() != "") {
+ exportSecurityPaperExcelVO.setAvatar(accreditationRecordsVo.getAvatar());
+ }
+ exportSecurityPaperExcelVO.setName(accreditationRecordsVo.getRealName()+accreditationRecordsVo.getIdCardNo());
+ //封装
+ list.add(exportSecurityPaperExcelVO);
+ });
+ // 声明一个工作薄
+ HSSFWorkbook workBook = new HSSFWorkbook();
+ // 生成一个表格
+ HSSFSheet sheet = workBook.createSheet();
+ //首行锁定
+// sheet.createFreezePane(0, 1, 0, 1);
+ workBook.setSheetName(0, "证书打印信息");
+ CellStyle style = workBook.createCellStyle();
+ Font font = workBook.getFontAt((short) 0);
+ font.setCharSet(HSSFFont.DEFAULT_CHARSET);
+ //更改默认字体大小
+ font.setFontHeightInPoints((short) 12);
+ font.setFontName("宋体");
+ style.setFont(font);
+ // 上下居中
+ style.setVerticalAlignment(VerticalAlignment.CENTER);
+ //默认宽高
+ sheet.setDefaultColumnWidth((short)11);
+
+ // 创建表格标题行 第一行
+// HSSFRow titleRow = sheet.createRow(0);
+// titleRow.setHeight((short) 300);
+// titleRow.createCell(0).setCellValue("员工姓名");
+// titleRow.createCell(1).setCellValue("员工照片");
+ HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
+ //如果总数小于等于7
+ if (list.size()<=7){
+ HSSFRow row = sheet.createRow(0);
+ row.setHeight((short) 1980);
+ HSSFRow row1 = sheet.createRow(1);
+ row1.setHeight((short) 300);
+ for (int i = 0; i < list.size(); i++) {
+ ExportSecurityPaperExcelVO checkWorkVo = list.get(i);
+ row1.createCell(i).setCellValue(checkWorkVo.getName());
+ //如果头像有
+ if (null != list.get(i).getAvatar() && list.get(i).getAvatar() != "") {
+ if (list.get(i).getAvatar().contains("http:")) {
+ //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
+ BufferedImage bufferImg = null;
+ //先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
+ ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
+ try {
+ bufferImg = ImageIO.read(new URL(list.get(i).getAvatar()));
+ } catch (IOException e) {
+ e.printStackTrace();
+ }
+ try {
+ ImageIO.write(bufferImg, "jpg", byteArrayOut);
+ } catch (IOException e) {
+ e.printStackTrace();
+ }
+ //anchor主要用于设置图片的属性
+ HSSFClientAnchor anchor =
+ new HSSFClientAnchor
+ ( 0, //x缩放
+ 0, // y缩放
+ 1023, //最大1023
+ 255, //最大255
+ (short) i, //宽度占几格 0开始
+ 0, //在第几行
+ (short) i, //宽度占几格 0开始
+ 0 //第几列
+ );
+ //插入图片
+ patriarch.createPicture(anchor, workBook.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
+ }
+ }
+ }
+ }else {
+ //总数大于7
+ List<ExportSecurityPaperExcelVO> excelList = new ArrayList<>();
+ //标记行,初始为0
+ int rowNum = -2;
+ for (int i = 0; i < list.size(); i++) {
+ //每次从集合中读取7个对象,求余数
+ if (i != 0 && (i + 1) % 7 == 0) {
+ excelList.add(list.get(i));
+ rowNum = rowNum + 2;
+ //写入表格
+ writeBookForEach(excelList,rowNum,workBook,sheet,patriarch);
+ //清空集合
+ excelList.clear();
+ } else {
+ excelList.add(list.get(i));
+ }
+ }
+ //判断余下的
+ if (excelList.size()>0){
+ rowNum++;
+ //写入表格
+ writeBookForEach(excelList,rowNum,workBook,sheet,patriarch);
+ }
+ }
+ //导出数据
+ response.setContentType("application/vnd.ms-excel; charset=UTF-8");
+ response.setHeader("Content-Disposition", "attachment;filename=" + "证书打印信息导出"+DateUtil.time() + ".xlsx");
+ workBook.write(response.getOutputStream());
+ }
+
+ /**
+ * 循环写入表格
+ * @param list 每行需要插入的数据
+ * @param rowNum 行标记
+ * @param workBook book
+ * @param sheet sheet
+ * @param patriarch 画图对象
+ */
+ private void writeBookForEach(List<ExportSecurityPaperExcelVO> list, int rowNum, HSSFWorkbook workBook, HSSFSheet sheet, HSSFPatriarch patriarch) {
+ HSSFRow row = sheet.createRow(rowNum);
+ row.setHeight((short) 1980);
+ HSSFRow row1 = sheet.createRow(rowNum+1);
+ row1.setHeight((short) 300);
+ for (int i = 0; i < list.size(); i++) {
+ ExportSecurityPaperExcelVO checkWorkVo = list.get(i);
+ row1.createCell(i).setCellValue(checkWorkVo.getName());
+ //如果头像有
+ if (null != list.get(i).getAvatar() && list.get(i).getAvatar() != "") {
+ if (list.get(i).getAvatar().contains("http:")) {
+ //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
+ BufferedImage bufferImg = null;
+ //先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
+ ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
+ try {
+ bufferImg = ImageIO.read(new URL(list.get(i).getAvatar()));
+ } catch (IOException e) {
+ e.printStackTrace();
+ }
+ try {
+ ImageIO.write(bufferImg, "jpg", byteArrayOut);
+ } catch (IOException e) {
+ e.printStackTrace();
+ }
+ //anchor主要用于设置图片的属性
+ HSSFClientAnchor anchor =
+ new HSSFClientAnchor
+ ( 0, //x缩放
+ 0, // y缩放
+ 1023, //最大1023
+ 255, //最大255
+ (short) i, //宽度占几格 0开始
+ rowNum, //在第几行
+ (short) i, //宽度占几格 0开始
+ rowNum //第几列
+ );
+ //插入图片
+ patriarch.createPicture(anchor, workBook.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
+ }
+ }
+ }
+ }
+
+
+// /**
+// * 导出证书制证信息(包含照片) easyExcel 填充
+// */
+// @GetMapping("export-security-book-papers")
+// public void exportSecurityBookPapers(AccreditationRecordsVo accreditationRecords, HttpServletResponse response) throws Exception {
+// //按条件查询成绩数据
+// List<AccreditationRecordsVo> excels = accreditationRecordsService.exportSecurityBookPapers(accreditationRecords);
+// List<ExportSecurityPaperExcelVO> list = new ArrayList<>();
+// excels.forEach(accreditationRecordsVo -> {
+// ExportSecurityPaperExcelVO exportSecurityPaperExcelVO = new ExportSecurityPaperExcelVO();
+// //如果头像有
+// if (null != accreditationRecordsVo.getAvatar() && accreditationRecordsVo.getAvatar() != "") {
+// try {
+// if (accreditationRecordsVo.getAvatar().contains("http:")) {
+// exportSecurityPaperExcelVO.setAvatar(new URL(accreditationRecordsVo.getAvatar()));
+// }
+// } catch (MalformedURLException e) {
+// e.printStackTrace();
+// }
+// }
+// exportSecurityPaperExcelVO.setName(accreditationRecordsVo.getRealName()+accreditationRecordsVo.getIdCardNo());
+// //封装
+// list.add(exportSecurityPaperExcelVO);
+// });
+// String fileName = null;
+// try {
+// response.setContentType("application/vnd.ms-excel");
+// response.setCharacterEncoding(org.apache.commons.codec.Charsets.UTF_8.name());
+// fileName = URLEncoder.encode("证书制证数据"+ DateUtil.time(), Charsets.UTF_8.name());
+//// response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
+// //修改单元格格式为文本格式
+//// EasyExcel.write(response.getOutputStream(), AccreditationRecordsVo.class)
+//// .sheet("证书制证数据表")
+//// .doWrite(excels);
+// // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
+// // {} 代表普通变量 {.} 代表是list的变量securityPaper.xlsx
+// String templateFileName = "xls" + File.separator + "securityPaper.xlsx";
+//// Resource resource = new ClassPathResource("xls" + File.separator + "securityPaper.xlsx");
+//// InputStream is = resource.getInputStream();
+//// ByteArrayOutputStream os = ConvertUtil.parse(is);
+////// ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build();
+//// ExcelWriter excelWriter = EasyExcel.write().file(os).withTemplate(templateFileName).build();
+//
+// ExcelWriter excelWriter = null;
+// try {
+// excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate
+// (new ClassPathResource(templateFileName).getInputStream()).build();
+// } catch (IOException e) {
+// }
+//
+// //设置横向填充
+// FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
+// WriteSheet writeSheet = EasyExcel.writerSheet().build();
+// excelWriter.fill(list, fillConfig, writeSheet);
+//// //如果总数小于等于8
+//// if (list.size()<=8){
+//// WriteSheet writeSheet = EasyExcel.writerSheet().build();
+//// excelWriter.fill(list, fillConfig, writeSheet);
+//// }else {
+//// //总数大于8
+//// List<ExportSecurityPaperExcelVO> excelList = new ArrayList<>();
+//// for (int i = 0; i < list.size(); i++) {
+//// //每次从集合中读取8个对象
+//// if (i != 0 && (i + 1) / 8 == 0) {
+//// WriteSheet writeSheet = EasyExcel.writerSheet().build();
+//// excelWriter.fill(excelList, fillConfig, writeSheet);
+//// //清空集合
+//// excelList.clear();
+//// } else {
+//// excelList.add(list.get(i));
+//// }
+//// }
+//// //判断余下的
+//// if (excelList.size()>0){
+//// WriteSheet writeSheet = EasyExcel.writerSheet().build();
+//// excelWriter.fill(excelList, fillConfig, writeSheet);
+//// }
+//// }
+// // 别忘记关闭流
+// excelWriter.finish();
+// } catch (Throwable var6) {
+// throw var6;
+// }
+// }
+
+
}
--
Gitblit v1.9.3