4dkankan-after-sale/src/main/java/com/fdkankan/sale/util/MyExcelUtil.java

package com.fdkankan.sale.util;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.img.ImgUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.ObjectUtil;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.fdkankan.fyun.face.FYunFileServiceInterface;
import com.fdkankan.sale.common.CacheUtil;
import com.fdkankan.sale.common.FilePath;
import com.fdkankan.sale.entity.Customer;
import com.fdkankan.sale.entity.PriceList;
import com.fdkankan.sale.service.IPriceListService;
import com.fdkankan.sale.util.pdf.ExportImg;
import com.fdkankan.sale.util.pdf.MyHandler;
import com.fdkankan.sale.util.pdf.TestForExcel2PDF;
import com.fdkankan.sale.vo.response.PriceListExcelVo;
import javassist.expr.FieldAccess;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import org.springframework.stereotype.Component;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.net.URLEncoder;
import java.util.*;


@Component
public class MyExcelUtil {

    @Autowired
    FYunFileServiceInterface fYunFileServiceInterface;

    //根据模板导出excel
    public void listFill(HttpServletResponse response, String excelName, Object obj, Object obj2, ExportImg exportImg,Integer type) {
        String fileName = excelName  + ".xlsx";

        // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
        // 填充list 的时候还要注意 模板中{.} 多了个点 表示list
        //获取文件路径
        String templateName = null;
        if(type == 0){
            templateName = "orderTemplate.xlsx";
        }
        if(type == 1){
            templateName = "repairTemplate.xlsx";
        }
        String ossPath =   String.format(FilePath.oss_file_path,FilePath.common,templateName);
        String localPath = FilePath.file_path + FilePath.common +"/"  + templateName;
        File file = new File(localPath);
        if(!file.exists()){
            fYunFileServiceInterface.downloadFile(ossPath,localPath );
        }
        String ossPathImage =   String.format(FilePath.oss_file_path,FilePath.common,"logo.png");
        String localPathImage = FilePath.file_path + FilePath.common +"/"  + "logo.png";
        File fileImage = new File(localPathImage);
        if(!fileImage.exists()){
            fYunFileServiceInterface.downloadFile(ossPathImage,localPathImage );
        }
        String templateFileName = file.getPath();

        String localFilePath = FilePath.file_path + FilePath.common + "/" + fileName;
        File localFile = new File(localFilePath);
        ExcelWriter excelWriter = null;

        //需要合并的行
        List<Integer> mergeRowsIndex  =new ArrayList<>();
        //需要合并的列
        List<Integer> mergeColumnIndex = new ArrayList<>();
        Integer index = 0;
        if(type == 0){
             index = 13;
            mergeRowsIndex  = Arrays.asList(1);
        }
        if(type == 1){
             index = 47;
            mergeRowsIndex  = Arrays.asList(2);
        }
        if(obj2 != null){
            List<PriceListExcelVo> list = (List<PriceListExcelVo>)obj2;
            Integer i = 0;
            for (PriceListExcelVo excelVo : list) {
                mergeColumnIndex.add(index + i);
                i++;
            }
        }

        excelWriter = EasyExcel.write(localFile)
                .withTemplate(templateFileName)
                //调用合并策略
                .registerWriteHandler(new ExcelFillCellMergeStrategy(1, mergeRowsIndex, mergeColumnIndex))
                .autoCloseStream(true)
                .build();
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        // 这里注意 入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行，然后下面的数据往后移动。默认 是false，会直接使用下一行，如果没有则创建。
        // forceNewRow 如果设置了true,有个缺点 就是他会把所有的数据都放到内存了，所以慎用
        // 简单的说 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
        // 如果数据量大 list不是最后一行 参照下一个
        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
        if(excelWriter != null && obj != null){
            excelWriter.fill(obj, fillConfig, writeSheet);
        }
        if(excelWriter != null &&obj2 != null){
            excelWriter.fill(obj2, fillConfig, writeSheet);
        }
        if(excelWriter != null &&exportImg != null){
            Map<String, Object> map = BeanUtil.beanToMap(exportImg);
            imageHandleByParam(map);
            excelWriter.fill(map, fillConfig, writeSheet);
        }

        assert excelWriter != null;
        excelWriter.finish();
        this.addImage(localFilePath,localPathImage);
        TestForExcel2PDF.excelToPdf(localFilePath,localFilePath.replace(".xlsx",".pdf"));

        response.setContentType("application/force-download");
        response.setCharacterEncoding("utf-8");
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setHeader("Content-disposition", "attachment;filename=" + fileName.replace(".xlsx",".pdf"));

        // 以流的形式下载文件。
        try {
            InputStream fis = new BufferedInputStream(new FileInputStream(localFilePath.replace(".xlsx",".pdf")));
            byte[] buffer = new byte[fis.available()];
            fis.read(buffer);
            fis.close();
            OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
            toClient.write(buffer);
            toClient.flush();
            toClient.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

        /**
         * 图片处理
         *
         * @param param
         */
    private void imageHandleByParam(Map<String, Object> param) {
        for (Map.Entry<String, Object> entry : param.entrySet()) {
            String mapKey = entry.getKey();
            if(ObjectUtil.isNull(entry.getValue())){
                continue;
            }
            String ossPath = entry.getValue().toString();
            String downloadPath = FilePath.file_path + ossPath.replaceAll(CacheUtil.host,"");
            try {
                File file = new File(downloadPath);
                if(!file.exists()){
                    fYunFileServiceInterface.downloadFile(ossPath.replaceAll(CacheUtil.host,""),downloadPath );
                }
                // 填充图片
                ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                BufferedImage bufferImg = ImageIO.read(new File(downloadPath));
                // 图片后缀格式
                ImageIO.write(bufferImg, "png", byteArrayOut);

                bufferImg.flush();
                // 注意：这里需要put回原来的key里
                param.put(mapKey, byteArrayOut.toByteArray());
            } catch (IOException e) {
                e.printStackTrace();
            }finally {
                FileUtil.del(downloadPath);
            }
        }
    }

    public  void  addImage(String filePath,String imagePath)  {
        FileOutputStream fileOut  =   null ;
        BufferedImage bufferImg  = null ;
        try {
            // 先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
            ByteArrayOutputStream byteArrayOut  =   new  ByteArrayOutputStream();
            bufferImg  =  ImageIO.read( new  File( imagePath ));
            ImageIO.write(bufferImg, "png" ,byteArrayOut);

            // 创建一个工作薄
            XSSFWorkbook wb  =   new  XSSFWorkbook(new FileInputStream(new File(filePath)));
            XSSFSheet sheet1  =  wb.getSheetAt(0);
            // XSSFRow row = sheet1.createRow(2);
            int lastRowNum = sheet1.getLastRowNum();

            XSSFDrawing patriarch  =  sheet1.createDrawingPatriarch();
            XSSFClientAnchor anchor  =   new  XSSFClientAnchor( 0 , 0 , 512 , 255 ,( short )  6 , lastRowNum-6 ,( short ) 7 , lastRowNum -4 );
            // 插入图片
            patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),XSSFWorkbook.PICTURE_TYPE_PNG));

            fileOut =  new FileOutputStream( filePath );
            // 写入excel文件
            wb.write(fileOut);
            fileOut.close();

        } catch (IOException io) {
            io.printStackTrace();
            System.out.println( " io erorr :   " +  io.getMessage());
        }   finally {
            if  (fileOut  !=   null ) {
                try   {
                    fileOut.close();
                }
                catch  (IOException e) {
                    //  TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }

}