证书打印信息导出修改

Administrator

2021-11-17 7293f04d18fb2e882e152f962c3aa1d8fcb1f93e

 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;
//      }
//   }


}