Excel操作_25569) * 86400 * 1000-CSDN博客

本文链接：https://blog.csdn.net/johnrui/article/details/141129062

一、fast-excel easy-excel

1. excel 数据读取

1.1 从xlsx格式中解析日期

/**
 * Excel中的日期是以数字形式存储的，需将其转换成日期格式。
 * @param numericDate
 * @return
 */
public static Date convertToDate(double numericDate) {
    long timestamp = (long) ((numericDate - 25569) * 86400 * 1000);
    return new Date(timestamp);
}

1.2 导出excel 单元格包含图片

easyExcel官网：https://easyexcel.opensource.alibaba.com/
https://gitcode.csdn.net/
easyExcel: https://blog.csdn.net/qq_14808529/article/details/142551367
easyExcel2: https://blog.csdn.net/m0_51963973/article/details/131054664
easyExcel3: https://www.iocoder.cn/Fight/Spring-Boot--EasyExcel-import-and-export-useful-to-detonation-POI-can-be-thrown-away/
easyExcel3: https://www.sohu.com/a/801033864_121432305
easyExcel5: https://blog.csdn.net/gitblog_00053/article/details/142159996
easyExcel6: https://blog.csdn.net/qq_42875345/article/details/127868770
easyExcel-转换: https://blog.csdn.net/luck_jinwei/article/details/141311429
easyExcel-合并: https://blog.csdn.net/qq_38828126/article/details/131803671
easyExcel-合并: https://gitcode.csdn.net/65ea8a881a836825ed7947ad.html?dp_token=eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJpZCI6NTU5NDA4LCJleHAiOjE3Mjg5NjI5MDAsImlhdCI6MTcyODM1ODEwMCwidXNlcm5hbWUiOiJqb2hucnVpIn0.rMy0wnxjeSwOdrByrY6HCQqXi8HuFbm9RsUAJEJj7MM&spm=1001.2101.3001.6650.15&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Eactivity-15-132106812-blog-131803671.235%5Ev43%5Epc_blog_bottom_relevance_base3&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Eactivity-15-132106812-blog-131803671.235%5Ev43%5Epc_blog_bottom_relevance_base3&utm_relevant_index=26
easyExcel-合并: https://www.zhihu.com/question/485711258
easyExcel-图片处理:https://blog.csdn.net/weixin_56777219/article/details/134004314
easyExcel-图片处理2:https://zhuanlan.zhihu.com/p/373900541?utm_id=0
easyExcel-图片处理3:https://blog.csdn.net/m0_61662029/article/details/137670845
easyExcel-图片处理4:https://www.jb51.net/program/285252o7r.htm
easyExcel-图片处理4:https://www.cnblogs.com/qq1445496485/p/15273104.html
easyExcel-图片处理4:https://github.com/alibaba/easyexcel/blob/v2.2.11/src/test/java/com/alibaba/easyexcel/test/demo/write/ImageData.java
easyExcel-导入图片处理1:https://blog.csdn.net/qq_38974638/article/details/124411365
easyExcel-导入图片处理2:https://blog.csdn.net/ChengXuYuanH__/article/details/129121085
easyExcel-导入图片处理3:https://www.cnblogs.com/XiaoMingStudy1/p/18001412

https://nginx.org/en/download.html

@ExcelProperty(value = "豁口图片",converter = ImagUrlUtil .class)

package com.common.util;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.alibaba.excel.util.IoUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.ObjectUtils;

import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.net.URLConnection;
@Slf4j
public class ImagUrlUtil implements Converter<String> {
public static int urlConnectTimeout = 2000;
public static int urlReadTimeout = 6000;

@Override
public Class<?> supportJavaTypeKey() {
return String.class;
}

@Override
public WriteCellData<?> convertToExcelData(String url, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) throws IOException {
InputStream inputStream = null;
try {
URL value = new URL(url);
if (ObjectUtils.isEmpty(value)){
return new WriteCellData<>("图片链接为空");
}
URLConnection urlConnection = value.openConnection();
urlConnection.setConnectTimeout(urlConnectTimeout);
urlConnection.setReadTimeout(urlReadTimeout);
inputStream = urlConnection.getInputStream();
byte[] bytes = IoUtils.toByteArray(inputStream);
return new WriteCellData<>(bytes);
}catch (Exception e){
log.info("图片获取异常",e);
return new WriteCellData<>("图片获取异常");
} finally {
if (inputStream != null) {
inputStream.close();
}
}
}
}

二、poi-ooxml

1. excel导出包含图片

参考：POI导出图片（HSSFWorkbook和SXSSFWorkbook两种导出方式）_dzs.qishuta.org-CSDN博客

参考：POI在Excel中导出图片（含多图）_poi导出图片到excel-CSDN博客

1.1 表格数据组装

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.util.StringUtils;

import java.io.IOException;
import java.io.OutputStream;
import java.security.GeneralSecurityException;
import java.util.List;
import java.util.Locale;
import java.util.Map;

/**
 * 批量导出excel
 */
@Slf4j
public class ToLargeExcelDirector {

    protected String sheetName;
    protected String[] heads;
    protected List<?> body;
    protected int size;
    private SXSSFWorkbook wb;
    private ToLargeExcelBuilder builder;
    protected Locale locale;
    protected OutputStream outputStream;
    // 判断是不是第一次调用outBigToZipFile方法
    protected Boolean isFirst = true;
    protected String filePassWord;

    public ToLargeExcelDirector(Locale locale, List<?> list, String sheetName, OutputStream outputStream, String filePassWord) {
        this.locale = locale;
        this.body = list;
        this.sheetName = sheetName;
        this.outputStream = outputStream;
        this.filePassWord = filePassWord;
    }

    /**
     * 设置列表数据
     *
     * @param list 列表数据
     */
    public void setList(List<?> list) {
        this.body = list;
    }

    public String[] getHeads() {
        return this.heads;
    }

    public List<?> getBody() {
        return this.body;
    }

    public int getSize() {
        return size;
    }

    public void setSize(int size) {
        this.size = size;
    }

    public ToLargeExcelBuilder getBuilder(Sheet sheet) {
        return new ToLargeExcelBuilder(sheet);
    }
    public ToLargeExcelBuilder getBuilder(SXSSFWorkbook wb, Sheet sheet) {
        return new ToLargeExcelBuilder(wb,sheet);
    }

    /**
     * 设置工作表宽度
     *
     * @param sheet 工作表对象
     */
    public void setSheetWidth(Sheet sheet) {
    }
    /**
     * 将大表格数据导出到zip文件中
     * outBigToFile:(返回ExceL) KEY: URL: KEY:ROWS: (zip使用)
     * @param excelMap 映射
     * @return 映射
     * @throws IOException 发生异常时说明导出失败，推送失败消息
     * @throws GeneralSecurityException
     */
    public Map<String, String> outBigToZipFile(Map<String, String> excelMap) throws IOException, GeneralSecurityException {
        if (isFirst) {
            if (builder == null) {
                wb = new SXSSFWorkbook(1000);
                Sheet sheet = wb.createSheet(StringUtils.isEmpty(sheetName) ? "new sheet" : sheetName);
//                SXSSFSheet sheet = wb.createSheet(StringUtils.isEmpty(sheetName) ? "new sheet" : sheetName);
                setSheetWidth(sheet);
//                builder = getBuilder(sheet);
                builder = getBuilder(wb,sheet);
            }
            int rows = 0;
            builder.buildHead(getHeads(), rows, getHeadStyle(wb));
            rows++;
            builder.buildBody(getBody(), rows, getBodyStyle(wb));
            rows = rows + body.size();

            excelMap.put("ROWS", String.valueOf(rows));
            //满足条件或无数据时输出文档
            if (rows >= size || body.size() == 0) {
                if (StringUtils.isEmpty(filePassWord)) {
                    wb.write(outputStream);
                } else {
                    // 文件加密
                    ExportUtil.encryptExcelOutputStream(wb, outputStream, filePassWord);
                }
            } else {
                isFirst = false;
            }
        } else {
            if (builder == null) {
//                builder = getBuilder(wb.getSheet(StringUtils.isEmpty(sheetName) ? "new sheet" : sheetName));
                builder = getBuilder(wb,wb.getSheet(StringUtils.isEmpty(sheetName) ? "new sheet" : sheetName));
            }
            String rowsStr = excelMap.get("ROWS");
            int rows = 0;
            if (!StringUtils.isEmpty(rowsStr)) {
                rows = Integer.parseInt(rowsStr);
            }
            builder.buildBody(getBody(), rows, getBodyStyle(wb));
            rows = rows + body.size();
            excelMap.put("ROWS", String.valueOf(rows));
            //满足条件或无数据时输出文档
            if (rows >= size || body.size() == 0) {
                if (StringUtils.isEmpty(filePassWord)) {
                    wb.write(outputStream);
                } else {
                    // 文件加密
                    ExportUtil.encryptExcelOutputStream(wb, outputStream, filePassWord);
                }
            }
        }

        return excelMap;
    }


    /**
     * 表列名设置样式
     *
     * @param wb 工作簿
     * @return 表头样式
     */
    public CellStyle getHeadStyle(SXSSFWorkbook wb) {
        CellStyle cs = wb.createCellStyle();
        Font font = wb.createFont(); // 设置字体
        font.setFontHeightInPoints((short) 10);// 字体大小
        font.setBold(Boolean.TRUE);// 加粗
        cs.setFont(font);
        cs.setAlignment(HorizontalAlignment.CENTER);// 居中
        cs.setBorderBottom(BorderStyle.THIN);// 边框
        cs.setBorderLeft(BorderStyle.THIN);// 边框
        cs.setBorderRight(BorderStyle.THIN);// 边框
        cs.setBorderTop(BorderStyle.THIN);// 边框
        return cs;
    }


    /**
     * 表内容设置样式
     *
     * @param wb 工作簿
     * @return 正文样式
     */
    public CellStyle getBodyStyle(SXSSFWorkbook wb) {
        CellStyle cs = wb.createCellStyle();
        cs.setAlignment(HorizontalAlignment.CENTER);// 居中
        cs.setBorderBottom(BorderStyle.THIN);// 边框
        cs.setBorderLeft(BorderStyle.THIN);// 边框
        cs.setBorderRight(BorderStyle.THIN);// 边框
        cs.setBorderTop(BorderStyle.THIN);// 边框
        return cs;
    }

}

1.2 表格结构构建

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

@Slf4j
public class ToLargeExcelBuilder {

    protected SXSSFWorkbook workbook;
    protected Sheet sheet;

    public ToLargeExcelBuilder(Sheet sheet) {
        this.sheet = sheet;
    }
    public ToLargeExcelBuilder(SXSSFWorkbook workbook,Sheet sheet) {
        this.workbook = workbook;
        this.sheet = sheet;
    }

    public ToLargeExcelBuilder(SXSSFWorkbook workbook,SXSSFSheet sheet) {
        this.workbook = workbook;
        this.sheet = sheet;
    }

    /**
     * 创建单元格
     *
     * @param row 行对象
     * @param i   列索引
     * @param cs  单元格样式
     * @return 新创建的单元格对象
     */
    private Cell createCell(Row row, int i, CellStyle cs) {
        Cell cell = row.createCell((short) i);
        cell.setCellStyle(cs);
        return cell;
    }


    /**
     * 创建表头
     *
     * @param heads 表头字符串数组
     * @param rows  行数
     * @param cs    单元格样式
     */
    public void buildHead(String[] heads, int rows, CellStyle cs) {
        Row row;
        Cell cell;
        if (heads == null) {
            return;
        }
        row = sheet.createRow((short) rows);
        for (int j = 0; j < heads.length; j++) {
            cell = createCell(row, j, cs);
            cell.setCellValue(heads[j]);
        }
    }

    /**
     * 创建内容
     *
     * @param list 数据列表
     * @param rows 起始行数
     * @param cs   单元格样式
     */
    public void buildBody(List<?> list, int rows, CellStyle cs) {
        if (CollectionUtils.isEmpty(list)) {
            return;
        }
        Row row;
        Cell cell;
        String[] rec;
        // 自动换行
        cs.setWrapText(true);
        for (int i = 0; i < list.size(); i++) {
            rec = (String[]) list.get(i);
            row = sheet.createRow(i + rows);
            row.setHeight((short)1000);//设置行高
            for (int j = 0; j < rec.length; j++) {
                cell = createCell(row, j, cs);
                //插入图片
                ImgTypeEnum imgTypeEnum =  ImgTypeEnum.getEnumInfo(rec[j]);
                if(ObjectUtil.isNotNull(imgTypeEnum) && ObjectUtil.isNotNull(imgTypeEnum.getVal())){
                    byte[] picturebytes =  getImage(rec[j]);
                    log.info("picturebytes-url:"+rec[j]);

                    if (picturebytes != null && picturebytes.length>0) {
                        log.info("picturebytes-data-length:"+picturebytes.length);
                        // sheet只能获取一个
                        Drawing patriarch = sheet.createDrawingPatriarch();
                        // 设置图片的属性
                        /* dx1:图片左边界距离单元格左边框像素值,
                         * dy1:图片上边界距离单元格上边框像素值,
                         * dx2:图片右边界距离单元格右边框像素值（负数）,
                         * dy2:图片下边界距离单元格下边框像素值（负数）,
                         * col1:列下标（0开始），
                         * row1:行下标（0开始），
                         * col2:列下标（1开始），
                         * row2:行下标（1开始）。*/
                        log.info("picturebytes-data.row:{} col:{}",row.getRowNum()+1,j+1);
                        XSSFClientAnchor  anchor = new XSSFClientAnchor(0, 0,1023, 255, (short) j, row.getRowNum(), (short) j+1, row.getRowNum()+1);
                        anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
                        // 插入图片
                        patriarch.createPicture(anchor,
                                workbook.addPicture(picturebytes, XSSFWorkbook.PICTURE_TYPE_JPEG));

                    }else{
                        cell.setCellValue(rec[j]);
                    }
                }
                else {
                        cell.setCellValue(rec[j]);
                }
            }
        }
    }



    /**
     * 根据图片id,获取图片byte 数据
     * @param fieldid
     * @return
     */
    private static byte[] getImage(String fieldid) {
        InputStream inputStream = null;
        byte[] bytes = null;
        try {
            String ossUrl = SubscribeProcessor.linkInstance(ApiConst.EVO_OSS);
            HostPortDto hostPortDto = IPUtil.getHostAndPort(ossUrl);
            if (NullEmptyVerify.isNull(ossUrl)) {
                throw new RuntimeException("无法发现OSS服务");
            }
//            String httpUrl = ossUrl.substring(0, ossUrl.indexOf(":"));
//            ossUrl = ossUrl.substring(ossUrl.indexOf(":") + 3);
//            httpUrl = httpUrl + "://" + ossUrl.substring(0, ossUrl.indexOf("/"));
//            httpUrl = httpUrl + "/" + fieldid + "?token=" + OauthClient.getToken();

            String httpUrl = String.format(Constants.IMG_URL_FORMAT, hostPortDto.getHost(), fieldid);;
            log.info("ssourl:{}",httpUrl);
            bytes  =  HttpUtils.doGet(httpUrl);
//            URL url = new URL(httpUrl);
//            inputStream = url.openStream();
//            bytes = IoUtil.readBytes(inputStream);
//            IOUtils.toByteArray()
//            bytes = IOUtils.toByteArray(inputStream);
            return bytes;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return bytes;
    }


public static void main(String[] args) throws IOException {
    String httpUrl = "https://10.31.34.220/evo-apigw/evo-oss/13002/d/1000003$1$0$0/20250617/10/5748-266685-0-Peccancy.jpg";

    byte[] bytes  =  HttpUtils.doGet(httpUrl);;
    System.out.println(bytes.length);

}




}

1.3 表格数据组装实现类

@Slf4j
public class PicRecordLargeExportToExcel extends ToLargeExcelDirector {



    public PicRecordLargeExportToExcel(Locale locale, List<?> list, String title, OutputStream outputStream, String filePassWord) {
        super(locale, list, title, outputStream, filePassWord);
    }

    public String[] getHeads() {
        // 国际化
        Map<String, String> translateMap = ResourceBundleUtil.getTranslateMap(PASS_CAR_HEADER_LIST,locale);
        String devName = translateMap.get("excel.head.devName");
        String devChnname = translateMap.get("excel.head.location");
        String carNum = translateMap.get("excel.head.carNum");
        String carNumColor = translateMap.get("excel.head.carNumColor");
        String carColor = translateMap.get("excel.head.carColor");
        String carType = translateMap.get("excel.head.carType");
        String carSpeed = translateMap.get("excel.head.carSpeed");
        String capDate = translateMap.get("excel.head.capDate");
        //定制新增字段
        String carImgUrl = PicRecordExcelHeadersEnum.EXCEL_HEAD_CAR_IMG.getVal();
        String carNumImgUrl = PicRecordExcelHeadersEnum.EXCEL_HEAD_CAR_NUM_IMG.getVal();
        String personName = PicRecordExcelHeadersEnum.EXCEL_HEAD_CAR_PERSON_NAME.getVal();
        String personImgUrl = PicRecordExcelHeadersEnum.EXCEL_HEAD_CAR_PERSON_IMG.getVal();
        String departmentName = PicRecordExcelHeadersEnum.EXCEL_HEAD_CAR_PERSON_DEPARTMENT.getVal();
        String phone = PicRecordExcelHeadersEnum.EXCEL_HEAD_CAR_PERSON_PHONE.getVal();
        String personIdentityStr = PicRecordExcelHeadersEnum.EXCEL_HEAD_CAR_PERSON_PERSONIDENTITYSTR.getVal();
        String similarity = PicRecordExcelHeadersEnum.EXCEL_HEAD_CAR_PERSON_SIMILARITY.getVal();

        this.heads = new String[]{devName, devChnname, carNum, carNumColor, carColor, carType, carSpeed, capDate,
                carImgUrl,carNumImgUrl,personName,personImgUrl,departmentName,phone,personIdentityStr,similarity};
        return this.heads;
    }

    /**
     * 设置Excel表格的列宽
     *
     * @param sheet 要设置列宽的Excel表格
     */
    public void setSheetWidth(Sheet sheet) { // 指定列宽
        for (int i = 0; i < 8; i++) {
            sheet.setColumnWidth((short) i, (short) 6000);
        }
        for (int i = 8; i < 15; i++) {
            sheet.setColumnWidth((short) i, (short) 15000);
        }
    }

    @SuppressWarnings({"unchecked", "rawtypes"})
    public List getBody() {
        List list = new ArrayList();
        String[] str;
        int i = 0;
        PicRecordVo bean;
        //获取国际化map
        List<String> i18keyList = new ArrayList<>();
        i18keyList.addAll(CarConstantData.getCarNumColorMap().values());
        i18keyList.addAll(CarConstantData.getCarColorMap().values());
        i18keyList.addAll(CarConstantData.getCarTypeMap().values());
        i18keyList.add(ROOT_ORG_NAME_CN);
        Map<String, String> translateMap = ResourceBundleUtil.getTranslateMap(i18keyList,locale);
        //定制内容：过车记录查询和违章查询”中增加车辆的部门属性，如果不是在车辆管理模块里维护的内部车辆，车辆部门显示空

        for (; i < this.body.size(); i++) {
            bean = (PicRecordVo) this.body.get(i);
//            log.info("bean:{}", JSONObject.toJSONString(bean));
            str = new String[16];
            // 卡口名称
            str[0] = bean.getDevName();
            // 抓拍地点
            str[1] = ROOT_ORG_NAME_CN.equals(bean.getDevChnname())?translateMap.get(ROOT_ORG_NAME_CN):bean.getDevChnname();
            // 号牌号码
            str[2] = bean.getCarNum();
            // 车牌颜色
            str[3] = translateMap.get(CarConstantData.getCarNumColorMap().get(bean.getCarNumcolor() + ""));
            // 车身颜色
            str[4] = translateMap.get(CarConstantData.getCarColorMap().get(bean.getCarColor() + ""));
            // 车辆类型
            str[5] = translateMap.get(CarConstantData.getCarTypeMap().get(bean.getCarType() + ""));
            // 车速
            str[6] = "" + bean.getCarSpeed();
            // 抓拍时间
            str[7] = bean.getCapDateStr();
            // 车抓拍
            str[8] = bean.getCarImgUrl();
            // 车牌抓拍
            str[9] = bean.getCarNumPic();
            // 姓名
            str[10] = bean.getPersonName();
            // 驾驶员抓拍
            str[11] = bean.getLibImgUrl();
            // 部门
            str[12] = bean.getDepartmentName();
            // 联系方式
            str[13] = bean.getPhone();
            // 车主类型
            str[14] = bean.getPersonIdentityStr();
            // 相似度
            str[15] = ObjectUtil.isNotNull(bean.getSimilarity()) ? bean.getSimilarity().toString(): StringUtils.SPACE;

            list.add(str);
        }
        this.body = list;
        return this.body;
    }

}

1.4