POI导出EXCEL附带二维码以及条形码

跌入凡尘的张公子

已于 2022-11-30 16:22:18 修改

阅读量800

点赞数 1

文章标签： apache java 服务器

于 2022-07-04 17:38:53 首次发布

本文链接：https://blog.csdn.net/zh555888/article/details/125605064

版权

本文介绍如何利用Apache POI库在Java中创建Excel文件，并且在Excel中添加二维码和条形码。详细步骤包括添加相关依赖、实现代码以及定义单元格样式。

摘要由CSDN通过智能技术生成

效果图
在这里插入图片描述

实现代码如下：

添加依赖


        <!--条形码/二维码-->
        <dependency>
            <groupId>com.google.zxing</groupId>
            <artifactId>core</artifactId>
            <version>3.3.0</version>
        </dependency>
        <dependency>
            <groupId>com.google.zxing</groupId>
            <artifactId>javase</artifactId>
            <version>3.3.0</version>
        </dependency>

实现代码


import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ByteArrayResource;
import org.springframework.core.io.InputStreamSource;
import org.springframework.http.*;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.util.List;

@Service
public class CaseMarkServiceImpl implements CaseMarkService {
   
    @Autowired
    private CaseMarkRepository repository;

@Override
    public HttpEntity<InputStreamSource> exportCaseMark(List<String> list) throws IOException, WriterException {
   
        // 查询数据库获取生成caseMark数据
        List<CaseMarkOutDto> resultList = repository.findVehicleInfo(list);
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        if (!CollectionUtils.isEmpty(resultList)) {
   
            for (CaseMarkOutDto dto : resultList) {
   
                SXSSFSheet sheet = workbook.createSheet(dto.getVin());
                // 设置列宽
                sheet.setColumnWidth(0, 20 * 50);
                sheet.setColumnWidth(1, 20 * 270);
                sheet.setColumnWidth(2, 20 * 100);
                sheet.setColumnWidth(3, 20 * 230);
                sheet.setColumnWidth(4, 20 * 200);
                sheet.setColumnWidth(5, 20 * 70);
                sheet.setColumnWidth(6, 20 * 150);
                int index = 0;
                // 首行添加个空白行
                SXSSFRow row = sheet.createRow(index);
                row.setHeight((short) 700);
                for (int i = 0; i < 7; i++) {
   
                    SXSSFCell cell = row.createCell(i);
                    cell.setCellStyle(ExcelStyleUtils.getStyle21(workbook));
                }
                CellRangeAddress cellAddresses = new CellRangeAddress(index, index, 0, 6);
                sheet.addMergedRegion(cellAddresses);
                index++;
                // 获取pcNo
                String pcNoStr = dto.getPcNo();
                String pc1 = pcNoStr.substring(0, 3);
                String pc2 = pcNoStr.substring(3);
                String pcNo = pc1 + "-" + pc2;
                String pcNos = "32F-" + pcNo;
                // 第2行
                SXSSFRow row2 = sheet.createRow(index);
                row2.setHeight((short) 1000);
                for (int i = 0; i < 7; i++) {
   
                    SXSSFCell cell = row2.createCell(i);
                    cell.setCellStyle(ExcelStyleUtils.getStyle5(workbook));
                    if (i == 1) {
   
                        cell.setCellValue(pcNos);
                        cell.setCellStyle(ExcelStyleUtils.getStyle21(workbook));
                    } else if (i == 4) {
   
                        cell.setCellValue(dto.getVesselCode());
                        cell.setCellStyle(ExcelStyleUtils.getStyle21(workbook));
                    }
                }
                CellRangeAddress cal1 = new CellRangeAddress(index, index, 1, 3);
                sheet.addMergedRegion(cal1);
                CellRangeAddress cal2 = new CellRangeAddress(index, index, 4, 6);
                sheet.addMergedRegion(cal2);
                index++;
                // 第3行
                SXSSFRow row3 = sheet.createRow(index);
                row3.setHeight((short) 700);
                for (int i = 0; i < 7; i++) {
   
                    SXSSFCell cell = row3.createCell(i);
                    if (i == 1) {
   
                        cell.setCellValue("HDM");
                        cell.setCellStyle(ExcelStyleUtils.getStyle22(workbook));
                    } else if (i == 3) {
   
                        cell.setCellValue("* " + dto.getVin() + " *");
                        cell.setCellStyle(ExcelStyleUtils.getStyle23(workbook));
                    } else {
   
                        cell.setCellStyle(ExcelStyleUtils.getStyle21(workbook));
                    }
                }
                CellRangeAddress cal3 = new CellRangeAddress(index, index, 3, 5);
                sheet.addMergedRegion(cal3);
                index++;
                // 第4行  300
                SXSSFRow row4 = sheet.createRow(index);
                row4.setHeight((short) 300);
                for (int i = 0; i < 7; i++) {
   
                    SXSSFCell cell = row4.createCell(i);
                    cell.setCellStyle(ExcelStyleUtils.getStyle21(workbook));
                }
                CellRangeAddress cal4 = new CellRangeAddress(index, index, 0, 5);
                sheet.addMergedRegion(cal4);
                index++;
                // 第5行
                SXSSFRow row5 = sheet.createRow(index);
                row5.setHeight((short) 600);
                for (int i = 0; i < 7; i++) {
   
                    SXSSFCell cell = row5.createCell(i);
                    if (i == 1) {
   
                        cell.setCellValue(pcNo);
                        cell.setCellStyle(ExcelStyleUtils.getStyle22(workbook));
                    }
                }
                index++;
                // 第6行
                SXSSFRow row6 = sheet.createRow(index);
                row6.setHeight((short) 300);
                for (int i = 0; i < 7; i++) {
   
                    SXSSFCell cell = row6.createCell(i);
                    cell.setCellStyle(ExcelStyleUtils.getStyle21(workbook));
                }
                index++;
                // 第7行
                SXSSFRow row7 = sheet.createRow(index);
                row7.setHeight((short) 400);
                for (int i = 0; i < 7; i++) {
   
                    SXSSFCell cell = row7.createCell(i);
                    cell.setCellStyle(ExcelStyleUtils.getStyle21(workbook));
                }
                CellRangeAddress cal7 = new CellRangeAddress(index - 2, index, 1, 2);
                sheet.addMergedRegion(cal7);
                index++;
                // 第8行
                SXSSFRow row8 = sheet.createRow(index);
                row8.setHeight((short) 700);
                for (int i = 0; i < 7; i++) {
   
                    SXSSFCell cell = row8.createCell(i);
                    cell.setCellStyle(ExcelStyleUtils.getStyle23(workbook));
                    if (i == 1) {
   
                        cell.setCellValue(dto.getDistOfficialName());
                    }
                }
                CellRangeAddress cal8 = new CellRangeAddress(index, index, 1, 2);
                sheet.addMergedRegion(cal8);
                index++;

                String pc_case = dto.getPcNo() + this.appendCaseNo(dto);
                // 第9行
                SXSSFRow row9 = sheet.createRow(index);
                row9.setHeight((short) 500);
                for (int i = 0; i < 7; i++) {
   
                    SXSSFCell cell = row9.createCell(i);
                    cell