自定注解,导出Excel

POI导出

poi的依赖

        <dependencies>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>4.0.1</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>4.0.1</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml-schemas</artifactId>
                <version>4.0.1</version>
            </dependency>
        </dependencies>

poi结构说明:

HSSF提供读写Microsoft Excel XLS格式档案的功能
XSSF提供读写Microsoft Excel OOXML XLSX格式档案的功能
HWPF提供读写Microsoft Word DOC格式档案的功能。
HSLF提供读写Microsoft PowerPoint格式档案的功能。
HDGF提供读Microsoft Visio格式档案的功能。
HPBF提供读Microsoft Publisher格式档案的功能。
HSMF提供读Microsoft Outlook格式档案的功能

API:

API名称 
Workbook   Excel的文档对象,针对不同的Excel类型分为：
                    HSSFWorkbook（2003）和XSSFWorkbool（2007）
Sheet Excel的表单
Row Excel的行
Cell Excel的格子单元
Font Excel字体
CellStyle 格子单元样式

行数,列数 都是从0开始的.

自定义注解:

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

//注解会在class字节码文件中存在，在运行时可以通过反射获
@Retention(RetentionPolicy.RUNTIME)
//字段、枚举的常量
@Target(ElementType.FIELD)
public @interface ExcelAttribute {
    /**
     * 对应的列名称
     *
     * @return
     */
    String name() default "";

    /**
     * 编号
     *
     * @return
     */
    int sort();

    /**
     * 字段类型对应的格式
     *
     * @return
     */
    String format() default "";

}

导出的工具类:

import lombok.Getter;
import lombok.Setter;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;

@Getter
@Setter
/**
 * 导出的工具类
 */
public class ExcelExportUtil<T> {

    /**
     * 写入数据的起始行
     */
    private int rowIndex;
    /**
     * 抽取样式的起始行
     */
    private int styleIndex;
    /**
     * 模版路径
     */
    private String templatePath;
    /**
     * 对象的字节码
     */
    private Class clazz;
    /**
     * 对象中的所有属性
     */
    private Field fields[];

    /**
     * 构造方法,直接new 就可以了.
     *
     * @param clazz
     * @param rowIndex
     * @param styleIndex
     */
    public ExcelExportUtil(Class clazz, int rowIndex, int styleIndex) {
        this.clazz = clazz;
        this.rowIndex = rowIndex;
        this.styleIndex = styleIndex;
        fields = clazz.getDeclaredFields();
    }

    /**
     * 根据模版导入
     *
     * @param response
     * @param is       模版的输入流
     * @param objs     要写入的数据
     * @param fileName 文件名称
     * @throws Exception
     */
    public void export(HttpServletResponse response, InputStream is, List<T> objs, String fileName) throws Exception {

        XSSFWorkbook workbook = new XSSFWorkbook(is);
        Sheet sheet = workbook.getSheetAt(0);

        CellStyle[] styles = getTemplateStyles(sheet.getRow(styleIndex));

        AtomicInteger datasAi = new AtomicInteger(rowIndex);
        for (T t : objs) {
            Row row = sheet.createRow(datasAi.getAndIncrement());
            for (int i = 0; i < styles.length; i++) {
                Cell cell = row.createCell(i);
                cell.setCellStyle(styles[i]);
                for (Field field : fields) {
                    if (field.isAnnotationPresent(ExcelAttribute.class)) {
                        field.setAccessible(true);
                        ExcelAttribute ea = field.getAnnotation(ExcelAttribute.class);
                        /**
                         * i:当前格子,第几个
                         * ea: 属性上面的注解
                         * 如果两个匹配的话,就使用反射调用get方法.
                         */
                        if (i == ea.sort()) {
                            cell.setCellValue(field.get(t).toString());
                        }
                    }
                }
            }
        }
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/octet-stream");
        response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes("ISO8859-1")));
        response.setHeader("filename", fileName);
        workbook.write(response.getOutputStream());
    }

    public CellStyle[] getTemplateStyles(Row row) {
        CellStyle[] styles = new CellStyle[row.getLastCellNum()];
        for (int i = 0; i < row.getLastCellNum(); i++) {
            styles[i] = row.getCell(i).getCellStyle();
        }
        return styles;
    }
}

导入的工具类:

import com.ihrm.domain.poi.ExcelAttribute;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.format.CellFormat;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 导入的工具类
 * @param <T>
 */
public class ExcelImportUtil<T> {
 
    private Class clazz;
    private  Field fields[];
 
    public ExcelImportUtil(Class clazz) {
        this.clazz = clazz;
        fields = clazz.getDeclaredFields();
    }
 
    /**
     * 基于注解读取excel
     */
    public List<T> readExcel(InputStream is, int rowIndex,int cellIndex) {
        List<T> list = new ArrayList<T>();
        T entity = null;
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(is);
            Sheet sheet = workbook.getSheetAt(0);
            // 不准确
            int rowLength = sheet.getLastRowNum();

            System.out.println(sheet.getLastRowNum());
            for (int rowNum = rowIndex; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                entity = (T) clazz.newInstance();
                System.out.println(row.getLastCellNum());
                for (int j = cellIndex; j < row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    for (Field field : fields) {
                        if(field.isAnnotationPresent(ExcelAttribute.class)){
                            field.setAccessible(true);
                            ExcelAttribute ea = field.getAnnotation(ExcelAttribute.class);
                            if(j == ea.sort()) {
                                field.set(entity, covertAttrType(field, cell));
                            }
                        }
                    }
                }
                list.add(entity);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
 

    /**
     * 类型转换 将cell 单元格格式转为 字段类型
     */
    private Object covertAttrType(Field field, Cell cell) throws Exception {
        String fieldType = field.getType().getSimpleName();
        if ("String".equals(fieldType)) {
            return getValue(cell);
        }else if ("Date".equals(fieldType)) {
            return new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse(getValue(cell)) ;
        }else if ("int".equals(fieldType) || "Integer".equals(fieldType)) {
            return Integer.parseInt(getValue(cell));
        }else if ("double".equals(fieldType) || "Double".equals(fieldType)) {
            return Double.parseDouble(getValue(cell));
        }else {
            return null;
        }
    }
 
 
    /**
     * 格式转为String
     * @param cell
     * @return
     */
    public String getValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getRichStringCellValue().getString().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date dt = DateUtil.getJavaDate(cell.getNumericCellValue());
                    return new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(dt);
                } else {
                    // 防止数值变成科学计数法
                    String strCell = "";
                    Double num = cell.getNumericCellValue();
                    BigDecimal bd = new BigDecimal(num.toString());
                    if (bd != null) {
                        strCell = bd.toPlainString();
                    }
                    // 去除 浮点型 自动加的 .0
                    if (strCell.endsWith(".0")) {
                        strCell = strCell.substring(0, strCell.indexOf("."));
                    }
                    return strCell;
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return "";
        }
    }
}

注解解释:

java中元注解有四个： @Retention @Target @Document @Inherited；

//注解的保留位置　　　　　
@Retention：　　　　
    //注解仅存在于源码中，在class字节码文件中不包含
    @Retention(RetentionPolicy.SOURCE)   
    // 默认的保留策略，注解会在class字节码文件中存在，但运行时无法获得，
    @Retention(RetentionPolicy.CLASS) 
    // 注解会在class字节码文件中存在，在运行时可以通过反射获取到
    @Retention(RetentionPolicy.RUNTIME)  
　　
@Target:注解的作用目标　　　　　　　　
    @Target(ElementType.TYPE)   //接口、类、枚举、注解
    @Target(ElementType.FIELD) //字段、枚举的常量
    @Target(ElementType.METHOD) //方法
    @Target(ElementType.PARAMETER) //方法参数
    @Target(ElementType.CONSTRUCTOR)  //构造函数
    @Target(ElementType.LOCAL_VARIABLE)//局部变量
    @Target(ElementType.ANNOTATION_TYPE)//注解
    @Target(ElementType.PACKAGE) ///包   

@Document：说明该注解将被包含在javadoc中 
@Inherited：说明子类可以继承父类中的该注解

-- jdk8写法

package com.fofund.sale.ia.biz.common;

import com.fofund.sale.common.exception.BusinessException;
import com.fofund.sale.ia.biz.enums.ExcleFileTypeEnum;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * @description: 导入Excel工具类
 * @author: zwl
 * @create: 2021-03-02 17:57
 */
public class ImportExcelUtil {

    /**
     * @Description: 读取文件数据
     * @Param: path-服务器文件路径,file-上传文件,c-反射实体类,sheetIndex-sheet页,rowIndex-行号,colIndex-列数]
     * @return: java.util.List<T>
     * @Author: zwl
     * @Date: 2021/3/2 18:28
     */
    public static <T> List<T> readObjFromExcel(String path, MultipartFile file, Class<T> c
            , int sheetIndex, int rowIndex, int colIndex) {
        // 获取 Workbook 对象
        Workbook workbook;
        if (null != file) {
            workbook = multipartFileConvertToWorkbook(file);
        } else {
            workbook = excelFileConvertToWorkbook(path);
        }
        // Sheet 下标从 0 开始
        Sheet sheet = getSheetFromWorkbook(workbook, sheetIndex);
        List<T> list = new ArrayList<>();
        Stream.iterate(0, i -> i + 1)
                // 最大行数
                .limit(sheet.getLastRowNum() + 1)
                // 跳过前几行
                .skip(rowIndex)
                // 获取行列表
                .map(i -> sheet.getRow(i))
                // 获取单个列表
                .map(a -> getCellList(a, colIndex))
                // 每行对应转化为一个目标对象
                .map(a -> textConvertToObj(a, c))
                // 目标对象列表
                .forEach(a -> list.add(a));
        return list;
    }

    private static Workbook multipartFileConvertToWorkbook(MultipartFile file) {
        if (null == file) {
            throw new BusinessException("文件不存在");
        }
        // 文件类型
        String path = file.getOriginalFilename();
        int pointIndex = path.lastIndexOf('.');
        if (pointIndex == -1) {
            throw new BusinessException("文件名不合法");
        }
        // 去掉所有空格
        String type = StringUtils.trimAllWhitespace(path.substring(pointIndex));
        if (StringUtils.isEmpty(type)) {
            throw new BusinessException("文件类型不合法");
        }
        // 文件对象
        Workbook wb;
        try {
            if (ExcleFileTypeEnum.XLS.getValue().equalsIgnoreCase(type)) {
                wb = new HSSFWorkbook(file.getInputStream());
            } else if (ExcleFileTypeEnum.XLSX.getValue().equalsIgnoreCase(type)) {
                wb = new XSSFWorkbook(file.getInputStream());
            } else {
                throw new BusinessException("文件类型不合法");
            }
        } catch (EncryptedDocumentException | IOException e) {
            throw new BusinessException("获取文件失败");
        }
        return wb;
    }

    private static Workbook excelFileConvertToWorkbook(String path) {
        if (StringUtils.isEmpty(path)) {
            throw new BusinessException("文件路径不能为空");
        }
        // 文件类型
        int pointIndex = path.lastIndexOf('.');
        if (pointIndex == -1) {
            throw new BusinessException("文件路径不合法");
        }
        // 去掉所有空格
        String type = StringUtils.trimAllWhitespace(path.substring(pointIndex));
        if (StringUtils.isEmpty(type)) {
            throw new BusinessException("文件类型不合法");
        }
        // 文件对象
        Workbook wb;
        try {
            if (ExcleFileTypeEnum.XLS.getValue().equalsIgnoreCase(type)) {
                // xls 文件
                wb = new HSSFWorkbook(new FileInputStream(path));
            } else if (ExcleFileTypeEnum.XLSX.getValue().equalsIgnoreCase(type)) {
                // xlsx 文件
                wb = new XSSFWorkbook(new FileInputStream(path));
            } else {
                throw new BusinessException("文件类型不合法");
            }
        } catch (EncryptedDocumentException | IOException e) {
            throw new BusinessException("获取文件类型失败");
        }
        return wb;
    }

    private static Sheet getSheetFromWorkbook(Workbook workbook, int sheetIndex) {
        // Sheet 下标从 0 开始
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        if (null == sheet) {
            throw new BusinessException("文件内容不能为空");
        }
        // Row 下标从 0 开始 // 3 行返回 2
        sheet.getLastRowNum();
        // Col 下标从 1 开始 // 3 列返回 3
        sheet.getRow(0).getLastCellNum();
        return sheet;
    }

    private static List<Cell> getCellList(Row row, int colIndex) {
        return Stream.iterate(0, i -> i + 1)
                .limit(row.getLastCellNum())
                .skip(colIndex)
                .map(i -> row.getCell(i))
                .collect(Collectors.toList());
    }

    private static <T> T textConvertToObj(List<Cell> cellList, Class<T> c) {
        T t;
        try {
            // 对象实例化
            t = c.getConstructor().newInstance();
        } catch (InstantiationException | IllegalAccessException | InvocationTargetException | NoSuchMethodException e) {
            throw new BusinessException("反射获取实例化对象失败");
        }
        // 属性赋值
        Arrays.stream(c.getDeclaredFields()).forEach(a -> setFieldValue(a, t, cellList));
        return t;
    }

    private static <T> void setFieldValue(Field field, T t, List<Cell> cellList) {
        if ("serialVersionUID".equalsIgnoreCase(field.getName())) {
            return;
        }
        // 获取属性类型名称
        String typeName = field.getGenericType().getTypeName();
        // 允许赋值私有变量
        field.setAccessible(true);
        // 过滤未加注解属性
        ExcelColField excelColField = field.getAnnotation(ExcelColField.class);
        if (null == excelColField) {
            setDefaultValue(field, t);
            return;
        }
        // 注解列数
        int col = excelColField.col();
        // 空单元格赋值为 null
        if (col > cellList.size() - 1) {
            setDefaultValue(field, t);
            return;
        }
        // 单元格
        Cell cell = cellList.get(col);
        if (null == cell) {
            setDefaultValue(field, t);
            return;
        }
        String value;
        // 单元格值类型
        CellType cellType = cell.getCellTypeEnum();
        switch (cellType) {
            case NUMERIC:
                value = String.valueOf(cell.getNumericCellValue());
                break;
            case BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            case STRING:
                value = cell.getStringCellValue();
                break;
            default:
                setDefaultValue(field, t);
                return;
        }
        if (typeName.indexOf(".") != -1) {
            typeName = typeName.substring(typeName.lastIndexOf(".") + 1);
        }
        // 精度保留类型
        RoundingMode roundingMode = excelColField.roundingMode();
        // 精度
        int roundingNum = excelColField.roundingNum();
        try {
            // 根据类型判断
            switch (typeName) {
                // 布尔
                case "boolean":
                case "Boolean":
                    field.set(t, Boolean.valueOf(value));
                    break;
                // 字符串
                case "char":
                case "Character":
                    field.set(t, value.charAt(0));
                    break;
                case "String":
                    field.set(t, value);
                    break;
                // 整数
                case "byte":
                case "Byte":
                    field.set(t, (byte) Double.parseDouble(value));
                    break;
                case "short":
                case "Short":
                    field.set(t, (short) Double.parseDouble(value));
                    break;
                case "int":
                case "Integer":
                    field.set(t, (int) Double.parseDouble(value));
                    break;
                case "long":
                case "Long":
                    field.set(t, (long) Double.parseDouble(value));
                    break;
                // 浮点数 由 BigDecimal 进行格式化
                case "float":
                case "Float":
                    field.set(t, new BigDecimal(value).setScale(roundingNum, roundingMode).floatValue());
                    break;
                case "double":
                case "Double":
                    field.set(t, new BigDecimal(value).setScale(roundingNum, roundingMode).doubleValue());
                    break;
                // 大数据类型
                case "BigDecimal":
                    field.set(t, new BigDecimal(value).setScale(roundingNum, roundingMode));
                    break;
                case "BigInteger":
                    field.set(t, new BigDecimal(value).setScale(0
                            , RoundingMode.DOWN).unscaledValue());
                    break;
                // 默认赋值
                default:
                    setDefaultValue(field, t);
                    break;
            }
        } catch (IllegalAccessException e) {
            throw new BusinessException("访问权限异常");
        }
    }

    private static <T> void setDefaultValue(Field field, T t) {
        // 基本类型设置为 null
        try {
            field.set(t, null);
        } catch (IllegalAccessException e) {
            throw new BusinessException("访问权限异常");
        }
    }
}

package com.fofund.sale.ia.biz.common;

import java.lang.annotation.*;
import java.math.RoundingMode;

/**
 * @Description: 导入Excel 实体类注解
 * @Author: zwl
 * @Date: 2021/3/2 18:41
 */
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface ExcelColField {
    // 列数，从 0 开始
    int col();

    // 浮点数据类型保留类型
    RoundingMode roundingMode() default RoundingMode.HALF_UP;

    // 浮点数据类型保留位数
    int roundingNum() default 2;

}

package com.fofund.sale.ia.biz.enums;


public enum ExcleFileTypeEnum {
    XLS(".XLS", "2003"),
    XLSX(".XLSX", "2007");

    private String value;
    private String desc;

    ExcleFileTypeEnum(String value, String desc) {
        this.value = value;
        this.desc = desc;
    }

    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }

    public String getDesc() {
        return desc;
    }

    public void setDesc(String desc) {
        this.desc = desc;
    }

}

工具类优化后:

package com.fofund.sale.ia.biz.common;

import com.fofund.sale.common.exception.BusinessException;
import com.fofund.sale.ia.biz.enums.ExcleFileTypeEnum;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.BufferedInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * @description: 导入Excel工具类 拆了两个方法,一个http字符串 一个传文件
 *  纯数字 会返回 .0 比如 110 字符串类型会返回 110.0 需要特殊处理一下. 因为excle格式的问题
 * @author: zwl
 * @create: 2021-03-02 17:57
 */
public class ImportExcelUtil {

    /**
     * @Description: 读取文件数据 file
     * @Param: file-文件,c-实体类,sheetIndex-sheet页,rowIndex-行号,colIndex-列数]
     * @return: java.util.List<T>
     * @Author: zwl
     * @Date: 2021/3/2 18:28
     */
    public static <T> List<T> readObjFromExcelByFile(MultipartFile file, Class<T> c
            , int sheetIndex, int rowIndex, int colIndex) {
        Workbook workbook = multipartFileConvertToWorkbook(file);
        return gettList(c, sheetIndex, rowIndex, colIndex, workbook);
    }

    /**
     * @Description: 读取文件数据
     * @Param: path-http文件路径,c-实体类,sheetIndex-sheet页,rowIndex-行号,colIndex-列数]
     * @return: java.util.List<T>
     * @Author: zwl
     * @Date: 2021/3/2 18:28
     */
    public static <T> List<T> readObjFromExcelByPath(String path, Class<T> c
            , int sheetIndex, int rowIndex, int colIndex) {
        Workbook workbook = excelFileConvertToWorkbook(path);
        return gettList(c, sheetIndex, rowIndex, colIndex, workbook);
    }

    private static <T> List<T> gettList(Class<T> c, int sheetIndex, int rowIndex, int colIndex, Workbook workbook) {
        // Sheet 下标从 0 开始
        Sheet sheet = getSheetFromWorkbook(workbook, sheetIndex);
        List<T> list = new ArrayList<>();
        Stream.iterate(0, i -> i + 1)
                .limit(sheet.getLastRowNum() + 1)
                .skip(rowIndex)
                .map(i -> sheet.getRow(i))
                .map(a -> getCellList(a, colIndex))
                .map(a -> textConvertToObj(a, c))
                .forEach(a -> list.add(a));
        return list;
    }

    private static Workbook multipartFileConvertToWorkbook(MultipartFile file) {
        if (null == file) {
            throw new BusinessException("文件不存在");
        }
        String path = file.getOriginalFilename();
        int pointIndex = path.lastIndexOf('.');
        if (pointIndex == -1) {
            throw new BusinessException("文件名不合法");
        }
        // 去掉所有空格
        String type = StringUtils.trimAllWhitespace(path.substring(pointIndex));
        if (StringUtils.isEmpty(type)) {
            throw new BusinessException("文件类型不合法");
        }
        // 文件对象
        Workbook wb;
        try {
            InputStream inputStream = file.getInputStream();
            if (POIFSFileSystem.hasPOIFSHeader(inputStream)) {
                wb = new HSSFWorkbook(inputStream);
            } else {
                wb = new XSSFWorkbook(inputStream);
            }
        } catch (EncryptedDocumentException | IOException e) {
            throw new BusinessException("获取文件失败");
        }
        return wb;
    }

    private static Workbook excelFileConvertToWorkbook(String path) {
        if (StringUtils.isEmpty(path)) {
            throw new BusinessException("文件路径不能为空");
        }
        int pointIndex = path.lastIndexOf('.');
        if (pointIndex == -1) {
            throw new BusinessException("文件路径不合法");
        }
        // 去掉所有空格
        String type = StringUtils.trimAllWhitespace(path.substring(pointIndex));
        if (StringUtils.isEmpty(type)) {
            throw new BusinessException("文件类型不合法");
        }
        Workbook wb;
        try {
//判断优化 参考地址:https://my.oschina.net/u/4314581/blog/3316664
            BufferedInputStream inputStream = new BufferedInputStream(getExcleFileUrl(path).openStream());
            if (POIFSFileSystem.hasPOIFSHeader(inputStream)) {
                wb = new HSSFWorkbook(inputStream);
            } else {
                wb = new XSSFWorkbook(inputStream);
            }

            /* //如果是path是文件所在路径 比如 /test/file.xls 这种 用文件流
            Workbook wb;
            try {
                if (ExcleFileTypeEnum.XLS.getValue().equalsIgnoreCase(type)) {
                    wb = new HSSFWorkbook(new FileInputStream(path));
                } else if (ExcleFileTypeEnum.XLSX.getValue().equalsIgnoreCase(type)) {
                    wb = new XSSFWorkbook(new FileInputStream(path));
                } else {
                    throw new BusinessException("文件类型不合法");
                }
            } catch (EncryptedDocumentException | IOException e) {
                throw new BusinessException("获取文件类型失败");
            }*/
        } catch (EncryptedDocumentException | IOException e) {
            throw new BusinessException("获取文件失败");
        }
        return wb;
    }

    private static URL getExcleFileUrl(String path) throws IOException {
        URL url = new URL(path);
        HttpURLConnection conn = (HttpURLConnection) url.openConnection();
        conn.setConnectTimeout(3 * 1000);
        //防止屏蔽程序抓取而返回403错误
        conn.setRequestProperty("User-Agent", "Mozilla/4.0 (compatible; MSIE 5.0; Windows NT; DigExt)");
        String newUrl = path;
        //解决重定向的问题. 比如:  http 变 https
        // 参考链接:https://blog.csdn.net/weixin_42687829/article/details/109113117
        Map<String, List<String>> map = conn.getHeaderFields();
        for (String key : map.keySet()) {
            if ("Location".equals(key)) {
                newUrl = map.get(key).get(0);
                break;
            }
        }
        url = new URL(newUrl);
        return url;
    }

    private static Sheet getSheetFromWorkbook(Workbook workbook, int sheetIndex) {
        // Sheet 下标从 0 开始
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        if (null == sheet) {
            throw new BusinessException("文件内容不能为空");
        }
        // Row 下标从 0 开始 // 3 行返回 2
        sheet.getLastRowNum();
        sheet.getRow(0).getLastCellNum();
        return sheet;
    }

    private static List<Cell> getCellList(Row row, int colIndex) {
        return Stream.iterate(0, i -> i + 1)
                .limit(row.getLastCellNum())
                .skip(colIndex)
                .map(i -> row.getCell(i))
                .collect(Collectors.toList());
    }

    private static <T> T textConvertToObj(List<Cell> cellList, Class<T> c) {
        T t;
        try {
            // 对象实例化
            t = c.getConstructor().newInstance();
        } catch (InstantiationException | IllegalAccessException | InvocationTargetException | NoSuchMethodException e) {
            throw new BusinessException("反射获取实例化对象失败");
        }
        // 属性赋值
        Arrays.stream(c.getDeclaredFields()).forEach(a -> setFieldValue(a, t, cellList));
        return t;
    }

    private static <T> void setFieldValue(Field field, T t, List<Cell> cellList) {
        field.setAccessible(true);
        // 过滤未加注解属性
        ExcelColField excelColField = field.getAnnotation(ExcelColField.class);
        if (null == excelColField) {
            return;
        }
        Class<?> className = field.getType();
        // 注解列数
        int col = excelColField.col();
        // 空单元格赋值为 null
        if (col > cellList.size() - 1) {
            setDefaultValue(field, t);
            return;
        }
        // 单元格
        Cell cell = cellList.get(col);
        if (null == cell) {
            setDefaultValue(field, t);
            return;
        }
        String value = String.valueOf(cell);

        // 精度保留类型
        RoundingMode roundingMode = excelColField.roundingMode();
        // 精度
        int roundingNum = excelColField.roundingNum();
        try {
            // 根据类型判断
            if (Boolean.class.equals(className) || boolean.class.equals(className)) {
                // 布尔
                field.set(t, Boolean.valueOf(value));
            } else if (char.class.equals(className) || Character.class.equals(className)) {
                // 字符串
                field.set(t, value.charAt(0));
            } else if (String.class.equals(className)) {
                field.set(t, value);
            } else if (byte.class.equals(className) || Byte.class.equals(className)) {
                field.set(t, (byte) Double.parseDouble(value));
            } else if (short.class.equals(className) || Short.class.equals(className)) {
                field.set(t, (short) Double.parseDouble(value));
            } else if (int.class.equals(className) || Integer.class.equals(className)) {
                field.set(t, (int) Double.parseDouble(value));
            } else if (long.class.equals(className) || Long.class.equals(className)) {
                field.set(t, (long) Double.parseDouble(value));
            } else if (float.class.equals(className) || Float.class.equals(className)) {
                // 浮点数 由 BigDecimal 进行格式化
                field.set(t, new BigDecimal(value).setScale(roundingNum, roundingMode).floatValue());
            } else if (double.class.equals(className) || Double.class.equals(className)) {
                field.set(t, new BigDecimal(value).setScale(roundingNum, roundingMode).doubleValue());
            } else if (BigDecimal.class.equals(className)) {
                // 大数据类型
                field.set(t, new BigDecimal(value).setScale(roundingNum, roundingMode));
            } else {
                // 默认赋值
                setDefaultValue(field, t);
            }
        } catch (IllegalAccessException e) {
            throw new BusinessException("访问权限异常");
        }
    }

    private static <T> void setDefaultValue(Field field, T t) {
        // 基本类型设置为 null
        try {
            field.set(t, null);
        } catch (IllegalAccessException e) {
            throw new BusinessException("访问权限异常");
        }
    }
}

问题:域名重定向

是因为 有重定向的情况存在, 所以要在获取链接的时候, 拿到重定向的地址, 然后重新构建链接. 获取流

easyexcel

pom

        <!-- easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.6</version>
        </dependency>

导出工具类

import static com.alibaba.excel.EasyExcelFactory.write;
import static com.alibaba.excel.EasyExcelFactory.writerSheet;
import static com.google.common.collect.Lists.partition;

import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import javax.servlet.http.HttpServletResponse;

/**
 * @version 1.0
 * @description: 导出excle工具类
 * @author: 单人影
 * @create: 2021-12-09 13:22
 **/
public class ExportExcle {

    private ExportExcle() {
        throw new IllegalStateException("ExportExcle class");
    }

    private static final int MAX_NUMBER = 20000;
    /** 
    * @description 导出方法 
    * @param: [response, list, clazz, name]
    * @return void
    * @author 单人影
    * @date 2021/12/16 14:56
    * @version 1.0
    */ 
    public static <T> void export(HttpServletResponse response, List<T> list, Class clazz, String name) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        //  URLEncoder 防止中文名乱码
        response.setHeader("Content-Disposition", "attachment;filename*= UTF-8''" + URLEncoder.encode(name, "UTF-8"));
        //获取response的输出流
        List<List<T>> splitList = partition(list, MAX_NUMBER);
        int i = 0;
        ExcelWriter writer = write(response.getOutputStream()).registerWriteHandler(new AutoWidth()).build();
        for (List<T> splic : splitList) {
            // .writerSheet里面放入两个参数 1. sheet编号（从0开始）  2. sheet名字
            // .head里面放入 实体类的class
            WriteSheet sheet = writerSheet(i, "sheet_" + i).head(clazz).build();
            //写入
            writer.write(splic, sheet);
            i++;
        }
        writer.finish();
    }
}

自适应样式

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.Cell;

/**
 * 自适应样式
 */
public class AutoWidth extends AbstractColumnWidthStyleStrategy {

    private static final int MAX_COLUMN_WIDTH = 255;
    //因为在自动列宽的过程中，有些设置地方让列宽显得紧凑，所以做出了个判断
    private static final int COLUMN_WIDTH = 10;
    private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(16);

    public AutoWidth() {
    }

    /**
     * @return void
     * @description 设置自适应宽度
     * @param: [writeSheetHolder, cellDataList, cell, head, relativeRowIndex, isHead]
     * @author 单人影
     * @date 2021/12/9 21:28
     * @version 1.0
     */
    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head,
            Integer relativeRowIndex, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = (Map) CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap(16);
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > MAX_COLUMN_WIDTH) {
                    columnWidth = MAX_COLUMN_WIDTH;
                } else {
                    if (columnWidth < COLUMN_WIDTH) {
                        columnWidth = columnWidth * 2;
                    }
                }
                Integer maxColumnWidth = (Integer) ((Map) maxColumnWidthMap).get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    ((Map) maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }
            }
        }
    }

    /**
     * @return java.lang.Integer
     * @description 数据长度
     * @param: [cellDataList, cell, isHead]
     * @author 单人影
     * @date 2021/12/9 21:28
     * @version 1.0
     */
    private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = (CellData) cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
}

controller导出代码:

    @PostMapping(value = "/export/trade-request")
    public void exportTradeRequest(@RequestBody TradeRequestRequest request, HttpServletResponse resource) {
        tradeRequestService.exportTradeRequest(request, resource);
    }

service代码

    @Override
    public void exportTradeRequest(TradeRequestRequest request, HttpServletResponse response) {
        BcmTrustTradeReqFlowBO bcmTrustTradeReqFlowBO = RequestConvert.CONVERT.convertTradeRequestRequest2BO(request);
        List<ExportTradeReqFlowBO> list = bcmTrustTradeReqFlowLogic.exportTradeRequest(bcmTrustTradeReqFlowBO);
        try {
            ExportExcle.export(response, list, ExportTradeReqFlowBO.class, DateUtils.sysdateToyyyyMMddHHmmss() + "交易导出申请");
        } catch (IOException e) {
            log.error("导出交易excle失败:", e);
            throw new BusinessException(ErrorEnums.BCM05020007);
        }
    }

 导出实体类 注意: 对象的所有字段都会展示, 就算没写注解也会在导出的Excel中展示

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;

/**
 * 认申购申请信息实体类
 *
 * @author makejava
 * @since 2021-06-24
 */
@Getter
@Setter
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class ExportTradeReqFlowBO {

    /**
     * 交易申请日
     */
    @ExcelProperty(value = "交易申请日", index = 0)
    private String appDateStr;

    /**
     * 交易申请流水号
     */
    @ExcelProperty(value = "交易申请流水号", index = 1)
    private String appSerialNo;

    /**
     * 托管唯一代码
     */
    @ExcelProperty(value = "托管唯一代码", index = 2)
    private String jhPrdCode;

    /**
     * 交易账号
     */
    @ExcelProperty(value = "交易账号", index = 3)
    private String tradeAcco;

    /**
     * 账户名称
     */
    @ExcelProperty(value = "账户名称", index = 4)
    private String tradeAccoName;

    /**
     * 基金账号 基金账号未确认则默认为：NX9999
     */
    @ExcelProperty(value = "基金账号", index = 5)
    private String taAccountId;

    /**
     * 基金代码
     */
    @ExcelProperty(value = "基金代码", index = 6)
    private String fundCode;

    /**
     * 基金名称
     */
    @ExcelProperty(value = "基金名称", index = 7)
    private String fundName;

    /**
     * 业务类型
     */
    @ExcelProperty(value = "业务类型", index = 8)
    private String businessCodeStr;

    /**
     * 申请金额 单位：分
     */
    @ExcelProperty(value = "申请金额", index = 9)
    private String appMoneyStr;

    /**
     * 保管账号
     */
    @ExcelProperty(value = "保管账号", index = 10)
    private String bgAccNo;

    /**
     * 保管账户名称
     */
    @ExcelProperty(value = "保管账户名称", index = 11)
    private String bgAccName;

    @ExcelProperty(value = "订单状态", index = 12)
    private String orderStatusStr;

    @ExcelProperty(value = "资金状态", index = 13)
    private String capitalStatusStr;

    @ExcelProperty(value = "更新时间", index = 14)
    private String lastModifyTimeStr;

}