java后端：实现导出excel，按其中一个列的数据生成二维码图片，显示在列表中

2021-04-23 03:27

阅读：429

标签：word position osi 输出流 new lis rtp null bytearray

新手记录自己的用过的工具，要是能帮到码友就更好了。借鉴：（借鉴的文章找不到了）

需求分析：

实现导出excel，二维码根据编号生成，二维码图片不存储在数据库(刚看到需求，还以为要把二维码图片存在数据库，前辈说完全没必要存)

　　技术图片

运行环境及框架： idea、springboot、

实现：

第一步：导入依赖


        com.google.zxing
            javase
            3.2.1com.google.zxing
            core
            3.2.1org.jeecg
            easypoi-base
            2.3.1

第二步：放在工具类

import com.google.zxing.BarcodeFormat;
import com.google.zxing.WriterException;
import com.google.zxing.common.BitMatrix;
import com.google.zxing.qrcode.QRCodeWriter;
import com.zkhb.boot.ponder.common.utils.common.QRCodeUtil;
import com.zkhb.boot.ponder.common.web.exception.PonderException;
import com.zkhb.boot.ponder.services.pound.entity.PoundCasualUser;
import com.zkhb.boot.ponder.services.pound.vo.report.PoundCasualUserReport;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Workbook;
import org.jeecgframework.poi.excel.ExcelExportUtil;
import org.jeecgframework.poi.excel.entity.ExportParams;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.BufferedOutputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Chen
 * @Description:
 * @ClassName:ExeclUtil
 * @date 2020/7/7 12:46
 * @Version: 1.0
 * @RETURN
 */
@Slf4j
public class ExcelUtil {
    /**
     * 导出excel
     *
     * @param response HttpServletResponse
     * @param fileName 文件名字
     * @param workbook 通过exportPicture()方法获取
     * @author zhy
     */
    public static void writeExcel(HttpServletResponse response, String fileName, Workbook workbook) {
        // 判断数据
        if (workbook == null) {
            throw new PonderException("错误");
        }
        // 重置响应对象
        response.reset();
        try {
            OutputStream outputStream = getOutputStream(fileName, response);
            BufferedOutputStream bufferedOutPut = new BufferedOutputStream(outputStream);
            workbook.write(bufferedOutPut);
            bufferedOutPut.flush();
            bufferedOutPut.close();
            outputStream.close();
        } catch (IOException e) {
            log.error(e.getMessage());
        }
    }

    /**
     * 导出文件时为Writer生成OutputStream
     *
     * @param fileName 文件名字
     * @param response response
     * @return 输出流
     * @author zhy
     */
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) {
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf8");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls");
            response.setHeader("Pragma", "public");
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            return response.getOutputStream();
        } catch (IOException e) {
            throw new PonderException("导出excel表格失败!");
        }
    }

    /**
     * 获取Workbook对象
     *
     * @param pigList     查询黑猪对象列表
     * @param aesPassword aes加密密码
     * @param qrCodeUrl   溯源网址前缀
     * @return 文件下载
     * @author zhy
     */
    public static Workbook exportPicture(List pigList, String aesPassword, String qrCodeUrl) {
        List list = new ArrayList();

        pigList.forEach(item -> {
            PoundCasualUserReport e = new PoundCasualUserReport();
            e.setTransportType(item.getTransportType()==0 ? "一车一货":"一车多货");
            e.setCardNo(item.getCardNo());
            String text=item.getCardNo();
            QRCodeWriter qrCodeWriter = new QRCodeWriter();
            BitMatrix bitMatrix = null;
            try {
                bitMatrix = qrCodeWriter.encode(text, BarcodeFormat.QR_CODE, 150, 150);
            } catch (WriterException e1) {
                e1.printStackTrace();
            }
            BufferedImage image = QRCodeUtil.toBufferedImage(bitMatrix);
            byte[] pngs = imageToBytes(image);
            e.setQrCode(pngs);
            list.add(e);
        });
        return ExcelExportUtil.exportExcel(new ExportParams(), PoundCasualUserReport.class, list);
    }

    /**
     * BufferedImage转byte[]
     *
     * @param bImage BufferedImage对象
     * @return byte[]
     * @auth zhy
     */
    private static byte[] imageToBytes(BufferedImage bImage) {
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        try {
            ImageIO.write(bImage, "png", out);
        } catch (IOException e) {
            log.error(e.getMessage());
        }
        return out.toByteArray();
    }

}

第三步：controller层 @ApiOperation("导出") @RequestMapping(value = "/export")

public Result export(
            @RequestParam(value = "ids") @ApiParam(value = "要导出的id，多个用逗号隔开", required = true) String ids,
            HttpServletRequest request,HttpServletResponse response)throws Exception{
　　　　//获取List集合（替换上你要导出的集合）
      List poundCasualUserList=poundCasualUserService.getCasualUserList(ids);
　　　　//导出的excel名
        String fileName = "临时用户列表" + DateUtils.formatDate(new Date(), "yyyyMMddHHmmss");
　　　　//调用导出的方法
        ExcelUtil.writeExcel(response,fileName,ExcelUtil.exportPicture(poundCasualUserList,null,null));
        return null;
    }