POI-EXCEL导入导出工具

已于 2023-10-23 10:03:30 修改
阅读量1.1k 收藏 4
点赞数 1
文章标签： excel java 开发语言
于 2022-10-20 14:47:33 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Mr_Hellon/article/details/127426640
版权

目录

目录

1.添加依赖

2.基本框架准备

3.Application代码

4.ExcelClassField类

5.ExcelUtils类

6.ExcelImport注解

7.ExcelExport注解

8.User类

9.UserController类

10.个性化配置

1.添加依赖

        <!--Excel相关的依赖-->
        <!-- 文件上传 -->
        <dependency>
            <groupId>org.apache.httpcomponents</groupId>
            <artifactId>httpmime</artifactId>
            <version>4.5.7</version>
        </dependency>
        <!-- JSON -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.41</version>
        </dependency>
        <!-- POI -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.16</version>
        </dependency>
        <!--Excel相关的依赖-->

2.基本框架准备

3.Application代码

@SpringBootApplication
public class DemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }

}

4.ExcelClassField类

public class ExcelClassField {

    /** 字段名称 */
    private String fieldName;

    /** 表头名称 */
    private String name;

    /** 映射关系 */
    private LinkedHashMap<String, String> kvMap;

    /** 示例值 */
    private Object example;

    /** 排序 */
    private int sort;

    /** 是否为注解字段：0-否，1-是 */
    private int hasAnnotation;

    public String getFieldName() {
        return fieldName;
    }

    public void setFieldName(String fieldName) {
        this.fieldName = fieldName;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public LinkedHashMap<String, String> getKvMap() {
        return kvMap;
    }

    public void setKvMap(LinkedHashMap<String, String> kvMap) {
        this.kvMap = kvMap;
    }

    public Object getExample() {
        return example;
    }

    public void setExample(Object example) {
        this.example = example;
    }

    public int getSort() {
        return sort;
    }

    public void setSort(int sort) {
        this.sort = sort;
    }

    public int getHasAnnotation() {
        return hasAnnotation;
    }

    public void setHasAnnotation(int hasAnnotation) {
        this.hasAnnotation = hasAnnotation;
    }

}

5.ExcelUtils类

package com.midea.lmes.intelligent.logistics.application.mes.util.poi;

import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.midea.lmes.intelligent.logistics.common.util.BeanUtlis.BeanUtils;
import com.midea.lmes.intelligent.logistics.domain.vmi.base.entity.base.ExcelExport;
import com.midea.lmes.intelligent.logistics.domain.vmi.base.entity.base.ExcelImport;
import com.midea.lmes.intelligent.logistics.domain.vmi.base.entity.base.ExportSetterEnum;
import com.mideaframework.core.exception.BusinessException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ooxml.POIXMLDocument;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
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 org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URL;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Map.Entry;
import java.util.regex.Pattern;
import java.util.stream.Collectors;


/**
 * @FileName: ExcelUtils
 * @author: zenghn
 * @CreatedTime: 2022/08/26 09:25
 * @description:
 */

@Slf4j
@SuppressWarnings("unused")
public class ExcelUtils {

    private static final String XLSX = ".xlsx";
    private static final String XLS = ".xls";
    public static final String ROW_MERGE = "row_merge";
    public static final String COLUMN_MERGE = "column_merge";
    private static final String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
    private static final String ROW_NUM = "rowNum";
    private static final String ROW_DATA = "rowData";
    private static final String ROW_TIPS = "rowTips";
    private static final int CELL_OTHER = 0;
    private static final int CELL_ROW_MERGE = 1;
    private static final int CELL_COLUMN_MERGE = 2;
    private static final int IMG_HEIGHT = 30;
    private static final int IMG_WIDTH = 30;
    private static final char LEAN_LINE = '/';
    private static final int BYTES_DEFAULT_LENGTH = 10240;
    private static final NumberFormat NUMBER_FORMAT = NumberFormat.getNumberInstance();


    public static <T> List<T> readFile(File file, Class<T> clazz) throws Exception {
        JSONArray array = readFile(file);
        return getBeanList(array, clazz);
    }

    public static <T> List<T> readMultipartFile(MultipartFile mFile, Class<T> clazz) throws Exception {
        JSONArray array = readMultipartFile(mFile);
        return getBeanList(array, clazz);
    }

    public static JSONArray readFile(File file) throws Exception {
        return readExcel(null, file);
    }

    public static JSONArray readMultipartFile(MultipartFile mFile) throws Exception {
        return readExcel(mFile, null);
    }

    public static Map<String, JSONArray> readFileManySheet(File file) throws Exception {
        return readExcelManySheet(null, file);
    }

    public static Map<String, JSONArray> readFileManySheet(MultipartFile file) throws Exception {
        return readExcelManySheet(file, null);
    }

    private static <T> List<T> getBeanList(JSONArray array, Class<T> clazz) throws Exception {
        List<T> list = new ArrayList<>();
        Map<Integer, String> uniqueMap = new HashMap<>(16);
        for (int i = 0; i < array.size(); i++) {
            list.add(getBean(clazz, array.getJSONObject(i), uniqueMap));
        }
        return list;
    }

    /**
     * 获取每个对象的数据
     */
    private static <T> T getBean(Class<T> c, JSONObject obj, Map<Integer, String> uniqueMap) throws Exception {
        T t = c.newInstance();
        Field[] fields = c.getDeclaredFields();
        List<String> errMsgList = new ArrayList<>();
        boolean hasRowTipsField = false;
        StringBuilder uniqueBuilder = new StringBuilder();
        int rowNum = 0;
        for (Field field : fields) {
            // 行号
            if (field.getName().equals(ROW_NUM)) {
                rowNum = obj.getInteger(ROW_NUM);
                field.setAccessible(true);
                field.set(t, rowNum);
                continue;
            }
            // 是否需要设置异常信息
            if (field.getName().equals(ROW_TIPS)) {
                hasRowTipsField = true;
                continue;
            }
            // 原始数据
            if (field.getName().equals(ROW_DATA)) {
                field.setAccessible(true);
                field.set(t, obj.toString());
                continue;
            }
            // 设置对应属性值
            setFieldValue(t, field, obj, uniqueBuilder, errMsgList);
        }
        // 数据唯一性校验
        if (uniqueBuilder.length() > 0) {
            if (uniqueMap.containsValue(uniqueBuilder.toString())) {
                Set<Integer> rowNumKeys = uniqueMap.keySet();
                for (Integer num : rowNumKeys) {
                    if (uniqueMap.get(num).equals(uniqueBuilder.toString())) {
                        errMsgList.add(String.format("数据唯一性校验失败,(%s)与第%s行重复)", uniqueBuilder, num));
                    }
                }
            } else {
                uniqueMap.put(rowNum, uniqueBuilder.toString());
            }
        }
        // 失败处理
        if (errMsgList.isEmpty() && !hasRowTipsField) {
            return t;
        }
        StringBuilder sb = new StringBuilder();
        int size = errMsgList.size();
        for (int i = 0; i < size; i++) {
            if (i == size - 1) {
                sb.append(errMsgList.get(i));
            } else {
                sb.append(errMsgList.get(i)).append(";");
            }
        }
        // 设置错误信息
        for (Field field : fields) {
            if (field.getName().equals(ROW_TIPS)) {
                field.setAccessible(true);
                field.set(t, errMsgList);
            }
        }
        return t;
    }

    private static <T> void setFieldValue(T t, Field field, JSONObject obj, StringBuilder uniqueBuilder, List<String> errMsgList) {
        // 获取 ExcelImport 注解属性
        ExcelImport annotation = field.getAnnotation(ExcelImport.class);
        if (annotation == null) {
            return;
        }
        String cname = annotation.value();
        if (cname.trim().length() == 0) {
            return;
        }
        // 获取具体值
        String val = null;
        if (obj.containsKey(cname)) {
            val = getString(obj.getString(cname));
        }
        if (val == null) {
            return;
        }
        field.setAccessible(true);
        // 判断是否必填
        boolean require = annotation.required();
        if (require && val.isEmpty()) {
            errMsgList.add(String.format("[%s]不能为空", cname));
            return;
        }
        // 数据唯一性获取
        boolean unique = annotation.unique();
        if (unique) {
            if (uniqueBuilder.length() > 0) {
                uniqueBuilder.append("--").append(val);
            } else {
                uniqueBuilder.append(val);
            }
        }
        // 判断是否超过最大长度
        int maxLength = annotation.maxLength();
        if (maxLength > 0 && val.length() > maxLength) {
            errMsgList.add(String.format("[%s]长度不能超过%s个字符(当前%s个字符)", cname, maxLength, val.length()));
        }
        //判断是否少于最小长度
        int minLength = annotation.minLength();
        if (minLength != 0 && val.length() < minLength) {
            errMsgList.add(String.format("[%s]长度不能小于%s个字符(当前%s个字符)", cname, minLength, val.length()));
        }
        // 判断当前属性是否有映射关系
        LinkedHashMap<String, String> kvMap = getKvMap(annotation.kv());
        if (!kvMap.isEmpty()) {
            boolean isMatch = false;
            for (String key : kvMap.keySet()) {
                if (kvMap.get(key).equals(val)) {
                    val = key;
                    isMatch = true;
                    break;
                }
            }
            if (!isMatch) {
                errMsgList.add(String.format("[%s]的值不正确(当前值为%s)", cname, val));
                return;
            }
        }
        // 其余情况根据类型赋值
        String fieldClassName = field.getType().getSimpleName();
        try {
            if ("String".equalsIgnoreCase(fieldClassName)) {
                field.set(t, val);
            } else if ("boolean".equalsIgnoreCase(fieldClassName)) {
                field.set(t, Boolean.valueOf(val));
            } else if ("int".equalsIgnoreCase(fieldClassName) || "Integer".equals(fieldClassName)) {
                try {
                    field.set(t, Integer.valueOf(val));
                } catch (NumberFormatException e) {
                    errMsgList.add(String.format("[%s]的值格式不正确(当前值为%s)", cname, val));
                }
            } else if ("double".equalsIgnoreCase(fieldClassName)) {
                field.set(t, Double.valueOf(val));
            } else if ("long".equalsIgnoreCase(fieldClassName)) {
                field.set(t, Long.valueOf(val));
            } else if ("BigDecimal".equalsIgnoreCase(fieldClassName)) {
                field.set(t, new BigDecimal(val));
            } else if ("Date".equalsIgnoreCase(fieldClassName)) {
                try {
                    field.set(t, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(val));
                } catch (Exception e) {
                    field.set(t, new SimpleDateFormat("yyyy-MM-dd").parse(val));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static Map<String, JSONArray> readExcelManySheet(MultipartFile mFile, File file) throws IOException {
        Workbook book = getWorkbook(mFile, file);
        if (book == null) {
            return Collections.emptyMap();
        }
        Map<String, JSONArray> map = new LinkedHashMap<>();
        for (int i = 0; i < book.getNumberOfSheets(); i++) {
            Sheet sheet = book.getSheetAt(i);
            JSONArray arr = readSheet(sheet);
            map.put(sheet.getSheetName(), arr);
        }
        book.close();
        return map;
    }

    private static JSONArray readExcel(MultipartFile mFile, File file) throws IOException {
        Workbook book = getWorkbook(mFile, file);
        if (book == null) {
            return new JSONArray();
        }
        JSONArray array = readSheet(book.getSheetAt(0));
        book.close();
        return array;
    }

    private static Workbook getWorkbook(MultipartFile mFile, File file) throws IOException {
        boolean fileNotExist = (file == null || !file.exists());
        if (mFile == null && fileNotExist) {
            return null;
        }
        // 解析表格数据
        InputStream in = null;
        String fileName = null;
        Workbook book = null;
        try {
            if (mFile != null) {
                // 上传文件解析
                in = mFile.getInputStream();
                fileName = getString(mFile.getOriginalFilename()).toLowerCase();
            } else {
                // 本地文件解析
                in = new FileInputStream(file);
                fileName = file.getName().toLowerCase();
            }

            //校验后缀合法性
            if (!(fileName.endsWith(XLSX) || fileName.endsWith(XLS))) {
                throw BusinessException.fail("当前文件后缀不是xls或者xlsx,格式不合法");
            }

            //使用工具类型创建工作薄,内含魔法码校验,已兼容2003以下和2007以上的EXCEL版本
            try {
                book = WorkbookFactory.create(in);
            } catch (Exception e) {
                throw new IOException("当前文件异常,无法读取工作簿内容");
            }
        } catch (Exception e) {
            throw e;
        } finally {
            if (in != null) in.close();
        }
        return book;
    }

    private static JSONArray readSheet(Sheet sheet) {
        // 首行下标
        int rowStart = sheet.getFirstRowNum();
        // 尾行下标
        int rowEnd = sheet.getLastRowNum();
        // 获取表头行
        Row headRow = sheet.getRow(rowStart);
        if (headRow == null) {
            return new JSONArray();
        }
        int cellStart = headRow.getFirstCellNum();
        int cellEnd = headRow.getLastCellNum();
        Map<Integer, String> keyMap = new HashMap<>();
        for (int j = cellStart; j < cellEnd; j++) {
            // 获取表头数据
            String val = getCellValue(headRow.getCell(j));
            if (val != null && val.trim().length() != 0) {
                keyMap.put(j, val);
            }
        }
        // 如果表头没有数据则不进行解析
        if (keyMap.isEmpty()) {
            return (JSONArray) Collections.emptyList();
        }
        // 获取每行JSON对象的值
        JSONArray array = new JSONArray();
        // 如果首行与尾行相同，表明只有一行，返回表头数据
        if (rowStart == rowEnd) {
            JSONObject obj = new JSONObject();
            // 添加行号
            obj.put(ROW_NUM, 1);
            for (int i : keyMap.keySet()) {
                obj.put(keyMap.get(i), "");
            }
            array.add(obj);
            return array;
        }
        for (int i = rowStart + 1; i <= rowEnd; i++) {
            Row eachRow = sheet.getRow(i);
            JSONObject obj = new JSONObject();
            // 添加行号
            obj.put(ROW_NUM, i + 1);
            StringBuilder sb = new StringBuilder();
            for (int k = cellStart; k < cellEnd; k++) {
                if (eachRow != null) {
                    String val = getCellValue(eachRow.getCell(k));
                    // 所有数据添加到里面，用于判断该行是否为空
                    sb.append(val);
                    obj.put(keyMap.get(k), val);
                }
            }
            if (sb.length() > 0) {
                array.add(obj);
            }
        }
        return array;
    }

    private static String getCellValue(Cell cell) {
        // 空白或空
        if (cell == null || cell.getCellTypeEnum() == CellType.BLANK) {
            return "";
        }
        // String类型
        if (cell.getCellTypeEnum() == CellType.STRING) {
            String val = cell.getStringCellValue();
            if (val == null || val.trim().length() == 0) {
                return "";
            }
            return val.trim();
        }
        // 数字类型
        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
            String s = cell.getNumericCellValue() + "";
            // 去掉尾巴上的小数点0
            if (Pattern.matches(".*\\.0*", s)) {
                return s.split("\\.")[0];
            } else {
                return s;
            }
        }
        // 布尔值类型
        if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
            return cell.getBooleanCellValue() + "";
        }
        // 错误类型
        return cell.getCellFormula();
    }

    public static <T> void exportTemplate(HttpServletResponse response, String fileName, Class<T> clazz) {
        exportTemplate(response, fileName, fileName, clazz, false);
    }

    public static <T> void exportTemplate(HttpServletResponse response, String fileName, String sheetName,
                                          Class<T> clazz) {
        exportTemplate(response, fileName, sheetName, clazz, false);
    }

    public static <T> void exportTemplate(HttpServletResponse response, String fileName, Class<T> clazz,
                                          boolean isContainExample) {
        exportTemplate(response, fileName, fileName, clazz, isContainExample);
    }

    public static <T> void exportTemplate(HttpServletResponse response, String fileName, String sheetName,
                                          Class<T> clazz, boolean isContainExample) {
        // 获取表头字段
        List<ExcelClassField> headFieldList = getExcelClassFieldList(clazz);
        // 获取表头数据和示例数据
        List<List<Object>> sheetDataList = new ArrayList<>();
        List<Object> headList = new ArrayList<>();
        List<Object> exampleList = new ArrayList<>();
        Map<Integer, List<String>> selectMap = new LinkedHashMap<>();
        for (int i = 0; i < headFieldList.size(); i++) {
            ExcelClassField each = headFieldList.get(i);
            headList.add(each.getName());
            exampleList.add(each.getExample());
            LinkedHashMap<String, String> kvMap = each.getKvMap();
            if (kvMap != null && kvMap.size() > 0) {
                selectMap.put(i, new ArrayList<>(kvMap.values()));
            }
        }
        sheetDataList.add(headList);
        if (isContainExample) {
            sheetDataList.add(exampleList);
        }
        // 导出数据
        export(response, fileName, sheetName, sheetDataList, selectMap);
    }

    private static <T> List<ExcelClassField> getExcelClassFieldList(Class<T> clazz) {
        // 解析所有字段
        Field[] fields = clazz.getDeclaredFields();
        boolean hasExportAnnotation = false;
        Map<Integer, List<ExcelClassField>> map = new LinkedHashMap<>();
        List<Integer> sortList = new ArrayList<>();
        for (Field field : fields) {
            ExcelClassField cf = getExcelClassField(field);
            if (cf.getHasAnnotation() == 1) {
                hasExportAnnotation = true;
            }
            int sort = cf.getSort();
            if (map.containsKey(sort)) {
                map.get(sort).add(cf);
            } else {
                List<ExcelClassField> list = new ArrayList<>();
                list.add(cf);
                sortList.add(sort);
                map.put(sort, list);
            }
        }
        Collections.sort(sortList);
        // 获取表头
        List<ExcelClassField> headFieldList = new ArrayList<>();
        if (hasExportAnnotation) {
            for (Integer sort : sortList) {
                for (ExcelClassField cf : map.get(sort)) {
                    if (cf.getHasAnnotation() == 1) {
                        headFieldList.add(cf);
                    }
                }
            }
        } else {
            headFieldList.addAll(map.get(0));
        }
        return headFieldList;
    }

    private static ExcelClassField getExcelClassField(Field field) {
        ExcelClassField cf = new ExcelClassField();
        String fieldName = field.getName();
        cf.setFieldName(fieldName);
        ExcelExport annotation = field.getAnnotation(ExcelExport.class);
        // 无 ExcelExport 注解情况
        if (annotation == null) {
            cf.setHasAnnotation(0);
            cf.setName(fieldName);
            cf.setSort(0);
            return cf;
        }
        // 有 ExcelExport 注解情况
        cf.setHasAnnotation(1);
        cf.setName(annotation.value());
        String example = getString(annotation.example());
        if (!example.isEmpty()) {
            if (isNumeric(example) && example.length() < 8) {
                cf.setExample(Double.valueOf(example));
            } else {
                cf.setExample(example);
            }
        } else {
            cf.setExample("");
        }
        cf.setSort(annotation.sort());
        // 解析映射
        String kv = getString(annotation.kv());
        cf.setKvMap(getKvMap(kv));
        return cf;
    }

    private static LinkedHashMap<String, String> getKvMap(String kv) {
        LinkedHashMap<String, String> kvMap = new LinkedHashMap<>();
        if (kv.isEmpty()) {
            return kvMap;
        }
        String[] kvs = kv.split(";");
        if (kvs.length == 0) {
            return kvMap;
        }
        for (String each : kvs) {
            String[] eachKv = getString(each).split("-");
            if (eachKv.length != 2) {
                continue;
            }
            String k = eachKv[0];
            String v = eachKv[1];
            if (k.isEmpty() || v.isEmpty()) {
                continue;
            }
            kvMap.put(k, v);
        }
        return kvMap;
    }

    /**
     * 导出表格到本地
     *
     * @param file      本地文件对象
     * @param sheetData 导出数据
     */
    public static void exportFile(File file, List<List<Object>> sheetData) {
        if (file == null) {
            System.out.println("文件创建失败");
            return;
        }
        if (sheetData == null) {
            sheetData = new ArrayList<>();
        }
        Map<String, List<List<Object>>> map = new HashMap<>();
        map.put(file.getName(), sheetData);
        export(null, file, file.getName(), map, null);
    }

    /**
     * 导出表格到本地
     *
     * @param <T>      导出数据类似，和K类型保持一致
     * @param filePath 文件父路径（如：D:/doc/excel/）
     * @param fileName 文件名称（不带尾缀，如：学生表）
     * @param list     导出数据
     * @throws IOException IO异常
     */
    public static <T> File exportFile(String filePath, String fileName, List<T> list) throws IOException {
        File file = getFile(filePath, fileName);
        List<List<Object>> sheetData = getSheetData(list);
        exportFile(file, sheetData);
        return file;
    }

    /**
     * 获取文件
     *
     * @param filePath filePath 文件父路径（如：D:/doc/excel/）
     * @param fileName 文件名称（不带尾缀，如：用户表）
     * @return 本地File文件对象
     */
    private static File getFile(String filePath, String fileName) throws IOException {
        String dirPath = getString(filePath);
        String fileFullPath;
        if (dirPath.isEmpty()) {
            fileFullPath = fileName;
        } else {
            // 判定文件夹是否存在，如果不存在，则级联创建
            File dirFile = new File(dirPath);
            if (!dirFile.exists()) {
                boolean mkdirs = dirFile.mkdirs();
                if (!mkdirs) {
                    return null;
                }
            }
            // 获取文件夹全名
            if (dirPath.endsWith(String.valueOf(LEAN_LINE))) {
                fileFullPath = dirPath + fileName + XLSX;
            } else {
                fileFullPath = dirPath + LEAN_LINE + fileName + XLSX;
            }
        }
        System.out.println(fileFullPath);
        File file = new File(fileFullPath);
        if (!file.exists()) {
            boolean result = file.createNewFile();
            if (!result) {
                return null;
            }
        }
        return file;
    }

    private static <T> List<List<Object>> getSheetData(List<T> list) {
        // 获取表头字段
        List<ExcelClassField> excelClassFieldList = getExcelClassFieldList(list.get(0).getClass());
        List<String> headFieldList = new ArrayList<>();
        List<Object> headList = new ArrayList<>();
        Map<String, ExcelClassField> headFieldMap = new HashMap<>();
        for (ExcelClassField each : excelClassFieldList) {
            String fieldName = each.getFieldName();
            headFieldList.add(fieldName);
            headFieldMap.put(fieldName, each);
            headList.add(each.getName());
        }
        // 添加表头名称
        List<List<Object>> sheetDataList = new ArrayList<>();
        sheetDataList.add(headList);
        // 获取表数据
        for (T t : list) {
            Map<String, Object> fieldDataMap = getFieldDataMap(t);
            Set<String> fieldDataKeys = fieldDataMap.keySet();
            List<Object> rowList = new ArrayList<>();
            for (String headField : headFieldList) {
                if (!fieldDataKeys.contains(headField)) {
                    continue;
                }
                Object data = fieldDataMap.get(headField);
                if (data == null) {
                    rowList.add("");
                    continue;
                }
                ExcelClassField cf = headFieldMap.get(headField);
                // 判断是否有映射关系
                LinkedHashMap<String, String> kvMap = cf.getKvMap();
                if (kvMap == null || kvMap.isEmpty()) {
                    rowList.add(data);
                    continue;
                }
                String val = kvMap.get(data.toString());
                if (isNumeric(val)) {
                    rowList.add(Double.valueOf(val));
                } else {
                    rowList.add(val);
                }
            }
            sheetDataList.add(rowList);
        }
        return sheetDataList;
    }

    private static <T> Map<String, Object> getFieldDataMap(T t) {
        Map<String, Object> map = new HashMap<>();
        Field[] fields = t.getClass().getDeclaredFields();
        try {
            for (Field field : fields) {
                String fieldName = field.getName();
                field.setAccessible(true);
                Object object = field.get(t);
                map.put(fieldName, object);
            }
        } catch (IllegalArgumentException | IllegalAccessException e) {
            e.printStackTrace();
        }
        return map;
    }

    public static void exportEmpty(HttpServletResponse response, String fileName) {
        List<List<Object>> sheetDataList = new ArrayList<>();
        List<Object> headList = new ArrayList<>();
        headList.add("导出无数据");
        sheetDataList.add(headList);
        export(response, fileName, sheetDataList);
    }

    public static void export(HttpServletResponse response, String fileName, List<List<Object>> sheetDataList) {
        export(response, fileName, fileName, sheetDataList, null);
    }

    public static void exportWidthMap(HttpServletResponse response, String fileName, List<List<Object>> sheetDataList, Map<Integer, Integer> widthMap) {
        exportWidthMap(response, fileName, fileName, sheetDataList, null, widthMap);
    }

    public static void exportForSpecial(HttpServletResponse response, String fileName, List<List<Object>> sheetDataList, Map<Integer, Integer> widthMap, Map<String, String> personalMap) {
        exportForSpecial(response, fileName, fileName, sheetDataList, null, widthMap, personalMap);
    }

    public static void exportManySheet(HttpServletResponse response, String fileName, Map<String, List<List<Object>>> sheetMap) {
        export(response, null, fileName, sheetMap, null);
    }

    public static void export(HttpServletResponse response, String fileName, String sheetName,
                              List<List<Object>> sheetDataList) {
        export(response, fileName, sheetName, sheetDataList, null);
    }

    public static void export(HttpServletResponse response, String fileName, String sheetName,
                              List<List<Object>> sheetDataList, Map<Integer, List<String>> selectMap) {

        Map<String, List<List<Object>>> map = new HashMap<>();
        map.put(sheetName, sheetDataList);
        export(response, null, fileName, map, selectMap);
    }

    public static void exportWidthMap(HttpServletResponse response, String fileName, String sheetName,
                                      List<List<Object>> sheetDataList, Map<Integer, List<String>> selectMap, Map<Integer, Integer> widthMap) {

        Map<String, List<List<Object>>> map = new HashMap<>();
        map.put(sheetName, sheetDataList);
        exportWidthMap(response, null, fileName, map, selectMap, widthMap);
    }

    public static void exportForSpecial(HttpServletResponse response, String fileName, String sheetName,
                                        List<List<Object>> sheetDataList, Map<Integer, List<String>> selectMap, Map<Integer, Integer> widthMap, Map<String, String> personalMap) {

        Map<String, List<List<Object>>> map = new HashMap<>();
        map.put(sheetName, sheetDataList);
        exportForSpecial(response, null, fileName, map, selectMap, widthMap, personalMap);
    }

    public static void exportManySheetToExcel(HttpServletResponse response, String fileName,
                                              Map<String, Map<String, String>> fieldMap,
                                              Map<String, List<Map<String, Object>>> sheetMap) {
        Map<String, List<List<Object>>> map = new HashMap<String, List<List<Object>>>();
        //遍历封装每个key表,封装数据
        for (Entry<String, Map<String, String>> mapEntry : fieldMap.entrySet()) {
            //表名
            String sheetName = mapEntry.getKey();
            //表头
            Map<String, String> eachFiledMap = mapEntry.getValue();
            //表数据
            List<Map<String, Object>> list = sheetMap.get(sheetName);
            //封装整个表格
            List<List<Object>> sheetDataList = new ArrayList<>();
            exchangeExcelDate(sheetDataList, eachFiledMap, list);
            map.put(sheetName, sheetDataList);
        }
        export(response, null, fileName, map, null);
    }

    /**
     * 导出EXCEL(不兼容ExcelExport注解的KV映射)
     *
     * @param response null时本地导出,不为null时前端导出
     * @param fileName sheet名称
     * @param fieldMap 抬头字段映射
     * @param list     导出行数据需要自行转换格式
     */
    public static void exportToExcel(HttpServletResponse response, String fileName,
                                     Map<String, String> fieldMap, List<Map<String, Object>> list) {
        Map<String, List<List<Object>>> map = new HashMap<>();
        List<List<Object>> sheetDataList = new ArrayList<>();
        exchangeExcelDate(sheetDataList, fieldMap, list);
        map.put(fileName, sheetDataList);
        export(response, null, fileName, map, null);
    }

    private static void exchangeExcelDate(List<List<Object>> sheetDataList,
                                          Map<String, String> fieldMap,
                                          List<Map<String, Object>> list) {
        //配置表头
        List<Object> fields = new ArrayList<>();
        for (String field : fieldMap.values()) {
            fields.add(field);
        }
        sheetDataList.add(fields);
        //配置每行数据
        for (Map<String, Object> objectMap : list) {
            List<Object> row = new ArrayList<>();
            for (Entry<String, String> entry : fieldMap.entrySet()) {
                String field = entry.getKey();
                Object o = objectMap.get(field);
                row.add(o);
            }
            sheetDataList.add(row);
        }
    }

    /**
     * 按实体类导出EXCEL(兼容ExcelExport注解的KV映射)
     *
     * @param response null时本地导出,不为null时前端导出
     * @param fileName sheet名称
     * @param list     导出实体类集合,无需转换格式
     * @param template 导出实体类的类型
     * @param <T>      数据不为空时,导出EXCEL对象类型
     * @param <K>      数据为空时,导出EXCEL时的模板对象类型
     */
    public static <T, K> void export(HttpServletResponse response, String fileName, List<T> list, Class<K> template) {
        // list 是否为空
        boolean lisIsEmpty = list == null || list.isEmpty();
        // 如果模板数据为空，且导入的数据为空，则导出空文件
        if (template == null && lisIsEmpty) {
            exportEmpty(response, fileName);
            return;
        }
        // 如果 list 数据，则导出模板数据
        if (lisIsEmpty) {
            exportTemplate(response, fileName, template);
            return;
        }
        // 导出数据
        List<List<Object>> sheetDataList = getSheetData(list);
        export(response, fileName, sheetDataList);
    }

    /**
     * 按指定宽度导出EXCEL
     */
    public static <T, K> void exportByWidthMap(HttpServletResponse response, String fileName, List<T> list, Class<K> template, Map<Integer, Integer> widthMap) {
        // list 是否为空
        boolean lisIsEmpty = list == null || list.isEmpty();
        // 如果模板数据为空，且导入的数据为空，则导出空文件
        if (template == null && lisIsEmpty) {
            exportEmpty(response, fileName);
            return;
        }
        // 如果 list 数据，则导出模板数据
        if (lisIsEmpty) {
            exportTemplate(response, fileName, template);
            return;
        }
        // 导出数据
        List<List<Object>> sheetDataList = getSheetData(list);
        exportWidthMap(response, fileName, sheetDataList, widthMap);
    }

    /**
     * 按指定宽度以及个性化配置导出EXCEL
     */
    public static <T, K> void exportForSpecial(HttpServletResponse response, String fileName, List<T> list, Class<K> template, Map<Integer, Integer> widthMap, Map<String, String> personalMap) {
        // list 是否为空
        boolean lisIsEmpty = list == null || list.isEmpty();
        // 如果模板数据为空，且导入的数据为空，则导出空文件
        if (template == null && lisIsEmpty) {
            exportEmpty(response, fileName);
            return;
        }
        // 如果 list 数据，则导出模板数据
        if (lisIsEmpty) {
            exportTemplate(response, fileName, template);
            return;
        }
        // 导出数据
        List<List<Object>> sheetDataList = getSheetData(list);
        exportForSpecial(response, fileName, sheetDataList, widthMap, personalMap);
    }

    public static void export(HttpServletResponse response, String fileName, List<List<Object>> sheetDataList, Map<Integer, List<String>> selectMap) {
        export(response, fileName, fileName, sheetDataList, selectMap);
    }

    private static void export(HttpServletResponse response, File file, String fileName,
                               Map<String, List<List<Object>>> sheetMap, Map<Integer, List<String>> selectMap) {
        // 整个 Excel 表格 book 对象
        SXSSFWorkbook book = new SXSSFWorkbook();
        // 每个 Sheet 页
        Set<Entry<String, List<List<Object>>>> entries = sheetMap.entrySet();
        for (Entry<String, List<List<Object>>> entry : entries) {
            List<List<Object>> sheetDataList = entry.getValue();
            Sheet sheet = book.createSheet(entry.getKey());
            Drawing<?> patriarch = sheet.createDrawingPatriarch();
            // 设置表头背景色（灰色）
            CellStyle headStyle = book.createCellStyle();
            headStyle.setFillForegroundColor(IndexedColors.GREY_80_PERCENT.index);
            headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            headStyle.setAlignment(HorizontalAlignment.CENTER);
            headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
            // 设置表身背景色（默认色）
            CellStyle rowStyle = book.createCellStyle();
            rowStyle.setAlignment(HorizontalAlignment.CENTER);
            rowStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            // 设置表格列宽度（默认为15个字节）
            sheet.setDefaultColumnWidth(15);
            // 创建合并算法数组
            int rowLength = sheetDataList.size();
            int columnLength = sheetDataList.get(0).size();
            int[][] mergeArray = new int[rowLength][columnLength];
            for (int i = 0; i < sheetDataList.size(); i++) {
                // 每个 Sheet 页中的行数据
                Row row = sheet.createRow(i);
                List<Object> rowList = sheetDataList.get(i);
                for (int j = 0; j < rowList.size(); j++) {
                    // 每个行数据中的单元格数据
                    Object o = rowList.get(j);
                    int v = 0;
                    if (o instanceof URL) {
                        // 如果要导出图片的话, 链接需要传递 URL 对象
                        setCellPicture(book, row, patriarch, i, j, (URL) o);
                    } else {
                        Cell cell = row.createCell(j);
                        if (i == 0) {
                            // 第一行为表头行，采用灰色底背景
                            v = setCellValue(cell, o, headStyle);
                        } else {
                            // 其他行为数据行，默认白底色
                            v = setCellValue(cell, o, rowStyle);
                        }
                    }
                    mergeArray[i][j] = v;
                }
            }
            // 合并单元格
            mergeCells(sheet, mergeArray);
            // 设置下拉列表
            setSelect(sheet, selectMap);
        }
        // 写数据
        if (response != null) {
            // 前端导出
            try {
                write(response, book, fileName);
            } catch (IOException e) {
                e.printStackTrace();
            }
        } else {
            // 本地导出
            FileOutputStream fos = null;
            try {
                fos = new FileOutputStream(file);
                ByteArrayOutputStream ops = new ByteArrayOutputStream();
                book.write(ops);
                fos.write(ops.toByteArray());
            } catch (Exception e) {
                log.error("本地导出异常:" + e.getMessage());
            } finally {
                if (fos != null) {
                    try {
                        fos.close();
                    } catch (Exception e) {
                        log.error("FileOutputStream close is fail , cause by:{}", e.toString());
                    }
                }
            }
        }
    }

    private static void exportWidthMap(HttpServletResponse response, File file, String fileName,
                                       Map<String, List<List<Object>>> sheetMap, Map<Integer, List<String>> selectMap,
                                       Map<Integer, Integer> widthMap) {
        // 整个 Excel 表格 book 对象
        SXSSFWorkbook book = new SXSSFWorkbook();
        // 每个 Sheet 页
        Set<Entry<String, List<List<Object>>>> entries = sheetMap.entrySet();
        for (Entry<String, List<List<Object>>> entry : entries) {
            List<List<Object>> sheetDataList = entry.getValue();
            Sheet sheet = book.createSheet(entry.getKey());
            Drawing<?> patriarch = sheet.createDrawingPatriarch();
            // 设置表头背景色（灰色）
            CellStyle headStyle = book.createCellStyle();
            headStyle.setFillForegroundColor(IndexedColors.GREY_80_PERCENT.index);
            headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            headStyle.setAlignment(HorizontalAlignment.CENTER);
            headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
            // 设置表身背景色（默认色）
            CellStyle rowStyle = book.createCellStyle();
            rowStyle.setAlignment(HorizontalAlignment.CENTER);
            rowStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //默认列宽
            sheet.setDefaultColumnWidth(15);
            if (widthMap != null && widthMap.size() > 0) {
                widthMap.entrySet().forEach(e -> {
                    sheet.setColumnWidth(e.getKey() - 1, e.getValue() * 256);
                });
            }

            // 创建合并算法数组
            int rowLength = sheetDataList.size();
            int columnLength = sheetDataList.get(0).size();
            int[][] mergeArray = new int[rowLength][columnLength];
            for (int i = 0; i < sheetDataList.size(); i++) {
                // 每个 Sheet 页中的行数据
                Row row = sheet.createRow(i);
                List<Object> rowList = sheetDataList.get(i);
                for (int j = 0; j < rowList.size(); j++) {
                    // 每个行数据中的单元格数据
                    Object o = rowList.get(j);
                    int v = 0;
                    if (o instanceof URL) {
                        // 如果要导出图片的话, 链接需要传递 URL 对象
                        setCellPicture(book, row, patriarch, i, j, (URL) o);
                    } else {
                        Cell cell = row.createCell(j);
                        if (i == 0) {
                            // 第一行为表头行，采用灰色底背景
                            v = setCellValue(cell, o, headStyle);
                        } else {
                            // 其他行为数据行，默认白底色
                            v = setCellValue(cell, o, rowStyle);
                        }
                    }
                    mergeArray[i][j] = v;
                }
            }
            // 合并单元格
            mergeCells(sheet, mergeArray);
            // 设置下拉列表
            setSelect(sheet, selectMap);
        }
        // 写数据
        if (response != null) {
            // 前端导出
            try {
                write(response, book, fileName);
            } catch (IOException e) {
                e.printStackTrace();
            }
        } else {
            // 本地导出
            FileOutputStream fos = null;
            try {
                fos = new FileOutputStream(file);
                ByteArrayOutputStream ops = new ByteArrayOutputStream();
                book.write(ops);
                fos.write(ops.toByteArray());
            } catch (Exception e) {
                log.error("本地导出异常:" + e.getMessage());
            } finally {
                if (fos != null) {
                    try {
                        fos.close();
                    } catch (Exception e) {
                        log.error("FileOutputStream close is fail , cause by:{}", e.toString());
                    }
                }
            }
        }
    }

    /**
     * 按自定义列宽与个性配置导出
     */
    private static void exportForSpecial(HttpServletResponse response, File file, String fileName,
                                         Map<String, List<List<Object>>> sheetMap, Map<Integer, List<String>> selectMap,
                                         Map<Integer, Integer> widthMap, Map<String, String> personalMap) {
        //获取个性化配置
        Integer fontSize = StringUtils.isNotEmpty(personalMap.get(ExportSetterEnum.FONT_SIZE.getProperty())) && StringUtils.isNumeric(personalMap.get(ExportSetterEnum.FONT_SIZE.getProperty())) ? Integer.valueOf(personalMap.get(ExportSetterEnum.FONT_SIZE.getProperty())) : null;
        Integer height = StringUtils.isNotEmpty(personalMap.get(ExportSetterEnum.HEIGHT.getProperty())) && StringUtils.isNumeric(personalMap.get(ExportSetterEnum.HEIGHT.getProperty())) ? Integer.valueOf(personalMap.get(ExportSetterEnum.HEIGHT.getProperty())) : null;
        Boolean autoWidth = (Boolean.TRUE.toString().equalsIgnoreCase(personalMap.get(ExportSetterEnum.AUTO_WIDTH.getProperty())) || Boolean.FALSE.toString().equalsIgnoreCase(personalMap.get(ExportSetterEnum.AUTO_WIDTH.getProperty()))) ? Boolean.valueOf(personalMap.get(ExportSetterEnum.AUTO_WIDTH.getProperty())) : Boolean.FALSE;
        Boolean noTopic = (Boolean.TRUE.toString().equalsIgnoreCase(personalMap.get(ExportSetterEnum.NO_TOPIC.getProperty())) || Boolean.FALSE.toString().equalsIgnoreCase(personalMap.get(ExportSetterEnum.NO_TOPIC.getProperty()))) ? Boolean.valueOf(personalMap.get(ExportSetterEnum.NO_TOPIC.getProperty())) : Boolean.FALSE;
        Boolean wrapText = (Boolean.TRUE.toString().equalsIgnoreCase(personalMap.get(ExportSetterEnum.WRAP_TEXT.getProperty())) || Boolean.FALSE.toString().equalsIgnoreCase(personalMap.get(ExportSetterEnum.WRAP_TEXT.getProperty()))) ? Boolean.valueOf(personalMap.get(ExportSetterEnum.WRAP_TEXT.getProperty())) : Boolean.FALSE;
        Boolean greyHead = (Boolean.TRUE.toString().equalsIgnoreCase(personalMap.get(ExportSetterEnum.GREY_HEAD.getProperty())) || Boolean.FALSE.toString().equalsIgnoreCase(personalMap.get(ExportSetterEnum.GREY_HEAD.getProperty()))) ? Boolean.valueOf(personalMap.get(ExportSetterEnum.GREY_HEAD.getProperty())) : Boolean.FALSE;
        Boolean leftCellWriteForAll = false;
        List<Integer> leftCellWriteIndexList = new ArrayList<>();
        if (StringUtils.isNotEmpty(personalMap.get(ExportSetterEnum.LEFT_CELL_WRITE.getProperty()))) {
            String leftCellWriteIndexStr = personalMap.get(ExportSetterEnum.LEFT_CELL_WRITE.getProperty());
            String[] indexStrList = leftCellWriteIndexStr.split(",");
            for (String indexStr : indexStrList) {
                if ("-1".equalsIgnoreCase(indexStr)) {
                    leftCellWriteForAll = true;
                    break;
                }
                if (StringUtils.isNumeric(indexStr) && Integer.valueOf(indexStr).compareTo(Integer.valueOf(0)) > 0) {
                    leftCellWriteIndexList.add(Integer.valueOf(indexStr) - 1);
                }
            }
        }

        // 整个 Excel 表格 book 对象
        SXSSFWorkbook book = new SXSSFWorkbook();
        // 每个 Sheet 页
        Set<Entry<String, List<List<Object>>>> entries = sheetMap.entrySet();
        for (Entry<String, List<List<Object>>> entry : entries) {
            List<List<Object>> sheetDataList = entry.getValue();
            SXSSFSheet sheet = (SXSSFSheet) book.createSheet(entry.getKey());
            Drawing<?> patriarch = sheet.createDrawingPatriarch();
            // 设置表头背景色（灰色）
            CellStyle headStyle = book.createCellStyle();
            headStyle.setFillForegroundColor(IndexedColors.GREY_80_PERCENT.index);
            headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            headStyle.setAlignment(HorizontalAlignment.CENTER);
            headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
            headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            headStyle.setBorderLeft(BorderStyle.THIN);
            headStyle.setBorderRight(BorderStyle.THIN);
            headStyle.setBorderTop(BorderStyle.THIN);
            headStyle.setBorderBottom(BorderStyle.THIN);

            //设置表身背景色（默认色）
            CellStyle rowStyle = book.createCellStyle();
            if (wrapText){
                rowStyle.setWrapText(true);
            }
            if (leftCellWriteForAll) {
                //内容靠左对齐
                rowStyle.setAlignment(HorizontalAlignment.LEFT);
            } else {
                //内容居中
                rowStyle.setAlignment(HorizontalAlignment.CENTER);
            }
            rowStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            rowStyle.setBorderLeft(BorderStyle.THIN);
            rowStyle.setBorderRight(BorderStyle.THIN);
            rowStyle.setBorderTop(BorderStyle.THIN);
            rowStyle.setBorderBottom(BorderStyle.THIN);

            //初始化行格式(靠做对齐列属性与常规一样,区别在单元个对齐)
            CellStyle leftRowStyle = book.createCellStyle();
            if (CollectionUtils.isNotEmpty(leftCellWriteIndexList)) {
                if (wrapText) {
                    rowStyle.setWrapText(true);
                }
                leftRowStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                leftRowStyle.setBorderLeft(BorderStyle.THIN);
                leftRowStyle.setBorderRight(BorderStyle.THIN);
                leftRowStyle.setBorderTop(BorderStyle.THIN);
                leftRowStyle.setBorderBottom(BorderStyle.THIN);
                leftRowStyle.setAlignment(HorizontalAlignment.LEFT);
            }

            //空行格式
            CellStyle emptyCellStyle = book.createCellStyle();
            emptyCellStyle.setBorderLeft(BorderStyle.NONE);
            emptyCellStyle.setBorderLeft(BorderStyle.NONE);

            //自定义字体大小
            if (ObjectUtil.isNotNull(fontSize)) {
                sheet.trackAllColumnsForAutoSizing();
                Font font = book.createFont();
                font.setFontName("宋体");
                font.setFontHeightInPoints(fontSize.shortValue());
                rowStyle.setFont(font);
                headStyle.setFont(font);
                emptyCellStyle.setFont(font);
                leftRowStyle.setFont(font);
            }

            //默认列宽
            if (autoWidth) {
                Map<Integer, Double> autoWithMap = getColumnAutoWithMap(sheetDataList);
                sheet.trackAllColumnsForAutoSizing();
                int column = sheetDataList.get(0).size();
                for (int i = 0; i < column; i++) {
                    //跳过widthMap的自动列宽配置
                    if (widthMap.containsKey(i + 1)) continue;
                    sheet.setColumnWidth(i, Math.min(255 * 256, autoWithMap.get(i).intValue()));
                }
            }else {
                sheet.setDefaultColumnWidth(15);
            }
            //如果存在指定行宽,则以指定行宽为准
            if (widthMap != null && widthMap.size() > 0) {
                widthMap.entrySet().forEach(e -> {
                    sheet.setColumnWidth(e.getKey() - 1, e.getValue() * 256);
                });
            }

            // 创建合并算法数组
            int rowLength = sheetDataList.size();
            int columnLength = sheetDataList.get(0).size();
            int[][] mergeArray = new int[rowLength][columnLength];
            //记录上一行是否为空行
            boolean preLineNull = false;
            for (int i = 0; i < sheetDataList.size(); i++) {
                // 每个 Sheet 页中的行数据
                Row row = sheet.createRow(i);
                List<Object> rowList = sheetDataList.get(i);
                //判断是否为空行
                List<Object> notNullList = rowList.stream().filter(e -> ObjectUtil.isNotEmpty(e)).collect(Collectors.toList());
                for (int j = 0; j < rowList.size(); j++) {
                    // 每个行数据中的单元格数据
                    Object o = rowList.get(j);
                    //是否自定义全局行高
                    if (ObjectUtil.isNotNull(height) && !wrapText) {
                        row.setHeight(Short.valueOf(height * 20 + ""));
                    }
                    int v = 0;
                    //当前为空行是所有的格式按空行格式配置
                    if (o instanceof URL) {
                        // 如果要导出图片的话, 链接需要传递 URL 对象
                        setCellPicture(book, row, patriarch, i, j, (URL) o);
                    } else {
                        Cell cell = row.createCell(j);
                        //抬头行配置
                        if (i == 0) {
                            // 第一行为表头行，采用灰色底背景
                            if (noTopic) {
                                v = setCellValue(cell, o, emptyCellStyle);
                                preLineNull = true;
                            } else {
                                if (greyHead) {
                                    v = setCellValue(cell, o, headStyle);
                                } else {
                                    v = setCellValue(cell, o, rowStyle);
                                }
                            }
                        }
                        //空行记录与配置
                        else if (CollectionUtils.isEmpty(notNullList)) {
                            if (ObjectUtil.isNotNull(height) && wrapText) {
                                row.setHeight(Short.valueOf(height * 20 + ""));
                            }
                            //空行格式统一化
                            v = setCellValue(cell, o, emptyCellStyle);
                            preLineNull = true;
                        }
                        //明细行配置
                        else {
                            if (preLineNull) {
                                //若果前一行是空行定义当前行为头
                                if (ObjectUtil.isNotNull(height) && wrapText) {
                                    row.setHeight(Short.valueOf(height * 20 + ""));
                                }
                                if (greyHead) {
                                    v = setCellValue(cell, o, headStyle);
                                } else {
                                    v = setCellValue(cell, o, rowStyle);
                                }
                                if (j == rowList.size() - 1) {
                                    preLineNull = false;
                                }
                            } else {
                                //当指定列需要靠左对齐时
                                if (CollectionUtils.isNotEmpty(leftCellWriteIndexList) && leftCellWriteIndexList.contains(j)) {
                                    v = setCellValue(cell, o, leftRowStyle);
                                }
                                // 其他行为数据行，默认白底色
                                else {
                                    v = setCellValue(cell, o, rowStyle);
                                }
                            }
                        }
                    }
                    mergeArray[i][j] = v;
                }
            }
            // 合并单元格
            mergeCells(sheet, mergeArray);
            // 设置下拉列表
            setSelect(sheet, selectMap);
        }
        // 写数据
        if (response != null) {
            // 前端导出
            try {
                write(response, book, fileName);
            } catch (IOException e) {
                e.printStackTrace();
            }
        } else {
            // 本地导出
            FileOutputStream fos = null;
            try {
                fos = new FileOutputStream(file);
                ByteArrayOutputStream ops = new ByteArrayOutputStream();
                book.write(ops);
                fos.write(ops.toByteArray());
            } catch (Exception e) {
                log.error("本地导出异常:" + e.getMessage());
            } finally {
                if (fos != null) {
                    try {
                        fos.close();
                    } catch (Exception e) {
                        log.error("FileOutputStream close is fail , cause by:{}", e.toString());
                    }
                }
            }
        }
    }

    /**
     * 自动列宽运算
     */
    private static Map<Integer, Double> getColumnAutoWithMap(List<List<Object>> sheetDataList) {
        Map<Integer, Double> autoMap = new HashMap<>();
        //行数
        int row = sheetDataList.size();
        //列数
        int column = sheetDataList.get(0).size();
        for (int i = 0; i < row; i++) {
            //遍历第i行数据
            List<Object> rowLine = sheetDataList.get(i);
            //遍历第i行第j列数据
            for (int j = 0; j < rowLine.size(); j++) {
                Object o = rowLine.get(j);
                String str = (String) o;
                if (str.length() <= 1) {
                    str = "xx";
                }
                double size = str.getBytes().length;
                double length = size * 1.2 * 256;
                //初始化列宽
                if (autoMap.get(j) == null) {
                    autoMap.put(j, length);
                }
                //比较获取最大字段长度
                else {
                    Double max = autoMap.get(j);
                    max = max >= length ? max : length;
                    autoMap.put(j, max);
                }
            }
        }
        return autoMap;
    }

    /**
     * 合并当前Sheet页的单元格
     *
     * @param sheet      当前 sheet 页
     * @param mergeArray 合并单元格算法
     */
    private static void mergeCells(Sheet sheet, int[][] mergeArray) {
        // 横向合并
        for (int x = 0; x < mergeArray.length; x++) {
            int[] arr = mergeArray[x];
            boolean merge = false;
            int y1 = 0;
            int y2 = 0;
            for (int y = 0; y < arr.length; y++) {
                int value = arr[y];
                if (value == CELL_COLUMN_MERGE) {
                    if (!merge) {
                        y1 = y;
                    }
                    y2 = y;
                    merge = true;
                } else {
                    merge = false;
                    if (y1 > 0) {
                        sheet.addMergedRegion(new CellRangeAddress(x, x, (y1 - 1), y2));
                    }
                    y1 = 0;
                    y2 = 0;
                }
            }
            if (y1 > 0) {
                sheet.addMergedRegion(new CellRangeAddress(x, x, (y1 - 1), y2));
            }
        }
        // 纵向合并
        int xLen = mergeArray.length;
        int yLen = mergeArray[0].length;
        for (int y = 0; y < yLen; y++) {
            boolean merge = false;
            int x1 = 0;
            int x2 = 0;
            for (int x = 0; x < xLen; x++) {
                int value = mergeArray[x][y];
                if (value == CELL_ROW_MERGE) {
                    if (!merge) {
                        x1 = x;
                    }
                    x2 = x;
                    merge = true;
                } else {
                    merge = false;
                    if (x1 > 0) {
                        sheet.addMergedRegion(new CellRangeAddress((x1 - 1), x2, y, y));
                    }
                    x1 = 0;
                    x2 = 0;
                }
            }
            if (x1 > 0) {
                sheet.addMergedRegion(new CellRangeAddress((x1 - 1), x2, y, y));
            }
        }
    }

    private static void write(HttpServletResponse response, SXSSFWorkbook book, String fileName) throws IOException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String name = new String(fileName.getBytes("GBK"), "ISO8859_1") + XLSX;
        response.addHeader("Content-Disposition", "attachment;filename=" + name);
        ServletOutputStream out = response.getOutputStream();
        book.write(out);
        out.flush();
        out.close();
    }

    private static int setCellValue(Cell cell, Object o, CellStyle style) {
        // 设置样式
        cell.setCellStyle(style);
        // 数据为空时
        if (o == null || "null".equalsIgnoreCase(o.toString()) || "".equalsIgnoreCase((o.toString().trim()))) {
            cell.setCellType(CellType.STRING);
            cell.setCellValue("");
            return CELL_OTHER;
        }
        // 是否为字符串
        if (o instanceof String) {
            String s = o.toString();
            // 当数字类型长度超过8位时，改为字符串类型显示（Excel数字超过一定长度会显示为科学计数法）
            if (isNumeric(s) && s.length() < 8) {
                cell.setCellType(CellType.NUMERIC);
                cell.setCellValue(Double.parseDouble(s));
                return CELL_OTHER;
            } else {
                cell.setCellType(CellType.STRING);
                cell.setCellValue(s);
            }
            if (s.equals(ROW_MERGE)) {
                return CELL_ROW_MERGE;
            } else if (s.equals(COLUMN_MERGE)) {
                return CELL_COLUMN_MERGE;
            } else {
                return CELL_OTHER;
            }
        }
        // 是否为字符串
        if (o instanceof Integer || o instanceof Long || o instanceof Double || o instanceof Float) {
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(Double.parseDouble(o.toString()));
            return CELL_OTHER;
        }
        // 是否为Boolean
        if (o instanceof Boolean) {
            cell.setCellType(CellType.BOOLEAN);
            cell.setCellValue((Boolean) o);
            return CELL_OTHER;
        }
        // 如果是BigDecimal，则默认3位小数
        if (o instanceof BigDecimal) {
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(((BigDecimal) o).setScale(3, RoundingMode.HALF_UP).doubleValue());
            return CELL_OTHER;
        }
        // 如果是Date数据，则显示格式化数据
        if (o instanceof Date) {
            cell.setCellType(CellType.STRING);
            cell.setCellValue(formatDate((Date) o));
            return CELL_OTHER;
        }
        //如果是空集合,则显示空串数据
        if (o instanceof Collection) {
            cell.setCellType(CellType.STRING);
            //装换为List
            List list = JSONObject.parseObject(JSON.toJSONString(o), List.class);
            if (list.size() == 0) {
                cell.setCellValue("");
                return CELL_OTHER;
            } else {
                String str = "";
                for (int i = 0; i < list.size(); i++) {
                    if (i == 0) {
                        str += list.get(i);
                    } else {
                        str += "," + list.get(i);
                    }
                }
                cell.setCellValue(str);
                return CELL_OTHER;
            }
        }
        // 如果是其他，则默认字符串类型
        cell.setCellType(CellType.STRING);
        cell.setCellValue(o.toString());
        return CELL_OTHER;
    }

    private static void setCellPicture(SXSSFWorkbook wb, Row sr, Drawing<?> patriarch, int x, int y, URL url) {
        // 设置图片宽高
        sr.setHeight((short) (IMG_WIDTH * IMG_HEIGHT));
        // （jdk1.7版本try中定义流可自动关闭）
        try (InputStream is = url.openStream(); ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            byte[] buff = new byte[BYTES_DEFAULT_LENGTH];
            int rc;
            while ((rc = is.read(buff, 0, BYTES_DEFAULT_LENGTH)) > 0) {
                outputStream.write(buff, 0, rc);
            }
            // 设置图片位置
            XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, y, x, y + 1, x + 1);
            // 设置这个，图片会自动填满单元格的长宽
            anchor.setAnchorType(AnchorType.MOVE_AND_RESIZE);
            patriarch.createPicture(anchor, wb.addPicture(outputStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static String formatDate(Date date) {
        if (date == null) {
            return "";
        }
        SimpleDateFormat format = new SimpleDateFormat(DATE_FORMAT);
        return format.format(date);
    }

    private static void setSelect(Sheet sheet, Map<Integer, List<String>> selectMap) {
        if (selectMap == null || selectMap.isEmpty()) {
            return;
        }
        Set<Entry<Integer, List<String>>> entrySet = selectMap.entrySet();
        for (Entry<Integer, List<String>> entry : entrySet) {
            int y = entry.getKey();
            List<String> list = entry.getValue();
            if (list == null || list.isEmpty()) {
                continue;
            }
            String[] arr = new String[list.size()];
            for (int i = 0; i < list.size(); i++) {
                arr[i] = list.get(i);
            }
            DataValidationHelper helper = sheet.getDataValidationHelper();
            CellRangeAddressList addressList = new CellRangeAddressList(1, 65000, y, y);
            DataValidationConstraint dvc = helper.createExplicitListConstraint(arr);
            DataValidation dv = helper.createValidation(dvc, addressList);
            if (dv instanceof HSSFDataValidation) {
                dv.setSuppressDropDownArrow(false);
            } else {
                dv.setSuppressDropDownArrow(true);
                dv.setShowErrorBox(true);
            }
            sheet.addValidationData(dv);
        }
    }

    private static boolean isNumeric(String str) {
        if (Objects.nonNull(str) && "0.0".equals(str)) {
            return true;
        }
        for (int i = str.length(); --i >= 0; ) {
            if (!Character.isDigit(str.charAt(i))) {
                return false;
            }
        }
        return true;
    }

    private static String getString(String s) {
        if (s == null) {
            return "";
        }
        if (s.isEmpty()) {
            return s;
        }
        return s.trim();
    }

}

6.ExcelImport注解

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelImport {

    /**
     * 字段名称
     */
    String value();

    /**
     * 导出映射，格式如：0-未知;1-男;2-女
     */
    String kv() default "";

    /**
     * 是否为必填字段（默认为非必填）
     */
    boolean required() default false;

    /**
     * 最大长度（默认255）
     */
    int maxLength() default 255;

    /**
     * 最小长度(默认0)
     * @return
     */
    int minLength() default 0;

    /**
     * 导入唯一性验证（多个字段则取联合验证）
     */
    boolean unique() default false;

}

7.ExcelExport注解

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelExport {

    /**
     * 字段名称
     */
    String value();

    /**
     * 导出排序先后: 数字越小越靠前（默认按Java类字段顺序导出）
     */
    int sort() default 0;

    /**
     * 导出映射，格式如：0-未知;1-男;2-女
     */
    String kv() default "";

    /**
     * 导出模板示例值（有值的话，直接取该值，不做映射）
     */
    String example() default "";

}

8.User类

@Data
public class User {

    /**
     * 获取EXCEL行号
     */
    private int rowNum;
    /**
     * 获取错误信息
     */
    //private String rowData;
    /**
     * 获取错误的信息
     */
    private List<String> rowTips;

    /**
     * required = true表示当前字段为必填项,为空是会在rowTips中提示
     * unique = true 表示当前字段不可重复,重复时会在rowTips中提示
     */
    @ExcelImport(value = "姓名",required = true,unique = true)
    @ExcelExport(value = "姓名",sort = 1,example = "张三")
    private String name;

    @ExcelImport("年龄")
    @ExcelExport(value = "年龄",sort = 2,example = "18")
    private Integer age;

    @ExcelImport(value = "性别", kv = "men-男;women-女")
    @ExcelExport(value = "性别",sort = 6,example = "男")
    private String sex;

    /**
     * maxLength = 11 表示当前字段长度限制为11,不符合是提示错误
     */
    @ExcelImport(value = "电话",maxLength = 10,minLength = 10)
    @ExcelExport(value = "电话",sort = 4,example = "13726310899")
    private String tel;

    @ExcelImport("城市")
    @ExcelExport(value = "城市",sort = 5,example = "北京")
    private String city;

    @ExcelImport("头像")
    @ExcelExport(value = "头像",sort = 3,example = "牛头")
    private String avatar;

}

9.UserController类

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.TypeReference;
import com.zeng.demo.dao.User;
import com.zeng.demo.excel.ExcelUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.util.*;

@RestController
@RequestMapping("/user")
public class UserController {
    /**
     * 下载导入的模板
     * @param response
     */
    @GetMapping("/download")
    public void download(HttpServletResponse response) {
        ExcelUtils.exportTemplate(response, "用户表", User.class);
    }

    /**
     * 导入Excel数据为JSONArray
     * @param file
     * @return
     * @throws Exception
     */
    @PostMapping("/import1")
    public JSONArray importUser1(@RequestPart("file") MultipartFile file) throws Exception {
        JSONArray array = ExcelUtils.readMultipartFile(file);
        System.out.println("导入数据为:" + array);
        return array;
    }

    /**
     * 导入Excel数据为指定对象(这里的对象需要注意添加@ExcelImport注解才能有效)
     * @param file
     * @throws Exception
     */
    @PostMapping("/import2")
    public void importUser2(@RequestPart("file") MultipartFile file) throws Exception {
        List<User> users = ExcelUtils.readMultipartFile(file, User.class);
        for (User user : users) {
            System.out.println(user.toString());
        }
    }

    /**
     * 导入多个表
     * @param file
     * @throws Exception
     */
    @PostMapping("/import3")
    public void upload(@RequestPart("file") MultipartFile file) throws Exception {
        Map<String, JSONArray> map = ExcelUtils.readFileManySheet(file);
        map.forEach((key, value) -> {
            System.out.println("Sheet名称：" + key);
            System.out.println("Sheet数据：" + value);
            System.out.println("----------------------");
        });
    }

    @GetMapping("/export")
    public void export(HttpServletResponse response) {
        // 表头数据
        List<Object> head = Arrays.asList("姓名", "年龄", "性别", "头像");
        // 用户1数据
        List<Object> user1 = new ArrayList<>();
        user1.add("诸葛亮");
        user1.add(60);
        user1.add("男");
        user1.add("https://profile.csdnimg.cn/A/7/3/3_sunnyzyq");
        // 用户2数据
        List<Object> user2 = new ArrayList<>();
        user2.add("大乔");
        user2.add(28);
        user2.add("女");
        user2.add("https://profile.csdnimg.cn/6/1/9/0_m0_48717371");
        // 将数据汇总
        List<List<Object>> sheetDataList = new ArrayList<>();
        sheetDataList.add(head);
        sheetDataList.add(user1);
        sheetDataList.add(user2);
        // 导出数据
        ExcelUtils.export(response, "用户表", sheetDataList);
    }

    /**
     * 导出Excel数据
     * @param response
     */
    @GetMapping("/export2")
    public void export2(HttpServletResponse response) {
        // 表头数据(这里需要注意顺序与导出的顺序一直)
        Map<String, String> filedMap = new LinkedHashMap<>();
        filedMap.put("avatar", "头像");
        filedMap.put("name", "姓名");
        filedMap.put("age", "年龄");
        filedMap.put("sex", "性别");
        filedMap.put("tel", "电话");
        filedMap.put("city", "城市");
        //模拟查询到数据库的数据
        User user1 = new User();
        user1.setName("张三");
        user1.setSex("女");
        user1.setAge(17);
        user1.setTel("123123156456");
        user1.setCity("广东");
        user1.setAvatar("4564564");
        User user2 = new User();
        user2.setName("李四");
        user2.setSex("男");
        user2.setAge(18);
        user2.setTel("123123156456");
        user2.setCity("广西");
        user2.setAvatar("4564564");
        List<User> data = new ArrayList<>();
        data.add(user1);
        data.add(user2);
        List<Map<String, Object>> list = JSON.parseObject(JSON.toJSONString(data), new TypeReference<List<Map<String, Object>>>() {
        });
        ExcelUtils.exportToExcel(response,"用户表",filedMap,list);
    }

    /**
     * 导出多 Sheet 页实现
     */
    @GetMapping("/exportManySheet")
    public void exportManySheet(HttpServletResponse response) {
        Map<String, List<Map<String, Object>>> sheetMap = new HashMap<>();
        Map<String,Map<String, String>> fieldMap = new HashMap<>();
        getDateForManyExcel(sheetMap,fieldMap);
        ExcelUtils.exportManySheetToExcel(response,"多页用户表",fieldMap,sheetMap);
    }

    private void getDateForManyExcel(Map<String, List<Map<String, Object>>> sheetMap, Map<String, Map<String, String>> fieldMap) {
        //第一张表
        // 表头数据(这里需要注意顺序与导出的顺序一致)
        Map<String, String> filedMap1 = new LinkedHashMap<>();
        filedMap1.put("avatar", "头像");
        filedMap1.put("name", "姓名");
        filedMap1.put("age", "年龄");
        filedMap1.put("sex", "性别");
        filedMap1.put("tel", "电话");
        filedMap1.put("city", "城市");
        //模拟查询到数据库的数据
        User user1 = new User();
        user1.setName("张三");
        user1.setSex("女");
        user1.setAge(17);
        user1.setTel("123123156456");
        user1.setCity("广东");
        user1.setAvatar("4564564");
        User user2 = new User();
        user2.setName("李四");
        user2.setSex("男");
        user2.setAge(18);
        user2.setTel("123123156456");
        user2.setCity("广西");
        user2.setAvatar("4564564");
        List<User> data = new ArrayList<>();
        data.add(user1);
        data.add(user2);
        List<Map<String, Object>> list = JSON.parseObject(JSON.toJSONString(data), new TypeReference<List<Map<String, Object>>>() {
        });
        //第二张表
        // 表头数据(这里需要注意顺序与导出的顺序一致)
        Map<String, String> filedMap2 = new LinkedHashMap<>();
        filedMap2.put("age", "年龄");
        filedMap2.put("sex", "性别");
        filedMap2.put("tel", "电话");
        filedMap2.put("city", "城市");
        //模拟查询到数据库的数据
        User user3 = new User();
        user3.setSex("女");
        user3.setAge(17);
        user3.setTel("123123156456");
        user3.setCity("广东");
        User user4= new User();
        user4.setSex("男");
        user4.setAge(18);
        user4.setTel("123123156456");
        user4.setCity("广西");
        List<User> data2 = new ArrayList<>();
        data2.add(user3);
        data2.add(user4);
        List<Map<String, Object>> list2 = JSON.parseObject(JSON.toJSONString(data), new TypeReference<List<Map<String, Object>>>() {
        });
        sheetMap.put("sheet1",list);
        sheetMap.put("sheet2",list2);
        fieldMap.put("sheet1",filedMap1);
        fieldMap.put("sheet2",filedMap2);
    }

}

10.个性化配置

package com.midea.lmes.intelligent.logistics.domain.vmi.base.entity.base;

import lombok.AllArgsConstructor;
import lombok.Getter;

/**
 * @FileName: ExportSetterEnum
 * @author: zenghn
 * @CreatedTime: 2023/07/31 17:02
 * @description: 导出个性化配置枚举类
 */
@Getter
@AllArgsConstructor
public enum ExportSetterEnum {
    FONT_SIZE("FontSize", "字体大小,取值与EXCEL的字体大小相同"),
    HEIGHT("Height", "行高,取值单位为磅数"),
    AUTO_WIDTH("AutoWidth", "自动列宽,取值true 或 false"),
    NO_TOPIC("noTopic", "是否隐藏抬头行,取值true 或 false"),
    WRAP_TEXT("WrapText","自动换行,取值true 或 false"),
    GREY_HEAD("greyHead","是否使用灰色抬头,取值true 或 false");

    /**
     * 属性
     */
    private String property;

    /**
     * 名称
     */
    private String name;
}

Mr_Hellon
关注
  • 1
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 3
    评论
POI-Excel导入导出 详细实现代码
YuZongTao
08-27 6836
1.界面效果：                   1）点击批量导入，弹出文件选择框，选择文件，点击打开，文件开始上传。                                       2）重复导入人员校验提示信息                     2.页面代码： &lt;a class="zdr-dr-btn" onclick="fileClick()"&gt;批...
基于poi的excel导入导出工具类
04-03
基于poi的excel导入导出封装，poi版本 <groupId>org.apache.poi <artifactId>poi <version>4.1.0 <groupId>org.apache.poi <artifactId>poi-ooxml <version>4.1.0 </dependency>
POI导出导入Excel工具类(原来Excel导入导出这么简单)
weixin_45152583的博客
04-21 779
POI导出/导入Excel工具类(原来Excel导入导出这么简单) 一.首先废话不多说,先导包 <!--POI相关坐标--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <vers...
Excel表格导入/导出数据工具类
小泽的博客
05-15 251
java Excel表格数据导入/导出工具类
Java POI实现Excel导入导出
lgb1997的博客
03-22 3099
Java文件导出POI简介版本依赖excel常用类easyExcel POI 简介 Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。POI为“Poor Obfuscation Implementation”的首字母缩写，意为“简洁版的模糊实现”。 版本依赖 当前使用的poi版本为 4.1.2，使用新版本后基础操作没啥改变，但是样式设置和之前有一些变化。引入了两个jar，其中poi是操作03版及之前excel的
利用poi-ooxml实现 Excel表格导入数据库 与 从数据库导出Excel表格
werewolfwerewolf的博客
10-19 4022
return uid;本文一直围绕的是.xlsx后缀的表格展开的，对于.xls后缀的表格文件我们只需将创建表格实例化对象的 XSSFWorkbook 改为 HSSFWorkbook即可。t=N7T8Mybatis批量插入的四种方式https://blog.csdn.net/m0_56287495/article/details/131194750。
使用poi实现excel导入导出
m0_53434091的博客
11-20 635
Java引入poi依赖实现excel的导入导出功能
java excel导入导出 poi-ooxml 3.17
的博客
01-17 5075
java 使用poi-ooxml 3.17 excel的通用导入导出，导出的样式未做处理，可自行处理 可以在csdn下载或者在gitHub（https://github.com/aojd/excel-importAndExport.git）git clone 也可以在csdn搜索 【java poi-ooxml 3.17 excel通用导入导出】、【完整 excel通用导入导出】 或者...
POI 实现Excel导入导出
java全栈工程师
07-22 2692
Apache POI 是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程序对Microsoft Office格式档案读和写的功能。本文主要介绍通过SpringBoot集成POI工具实现Excel的导入和导出功能。
一文了解POI，再也不怕excel导入导出了
再小的帆也能远航，把分享变成一种习惯
03-11 2993
在Java日常开发过程中，实现Excel文件的导入导出功能是一项常见的需求。通过使用相关的Java库，如Apache POI、EasyPoi或EasyExcel，可以轻松地实现Excel文件的读写操作。而这篇文章将介绍如何在Java中使用Apache POI、EasyPoi 和EasyExcel库来进行Excel文件的导入和导出操作，帮助您快速掌握这一实用的技能。
常用工具类之使用POI实现导入导出Excel
weixin_53998054的博客
11-23 1396
常用工具类之使用POI实现导入导出Excel
Apache / poi 操作excel导入导出 【Springboot Web】
m0_51315555的博客
07-17 2891
Excel文件版本：2003版本扩展名为.xls使用HSSFWorkbook类操作(只能65536行，超过报错)2007版本扩展名为.xlsx使用XSSFWorkbook类操作XSSFWorkbook 优化使用(缓存) 使用SXSSFWorkbook类操作。
POI实现Excel导入导出并附带加载进度条
08-30
总之，这个项目是一个完整的Java解决方案，用于处理Excel文件的导入导出，同时具备用户友好的进度条显示。它利用了Apache POI的强大功能，结合maven的依赖管理，以及可能的GUI组件，提供了高效且直观的数据操作体验...
poi-execl导入导出所需jar包全
01-02
2. **Excel导入导出**：在Java中，使用Apache POI库可以方便地实现Excel文件的读写。例如，你可以创建一个新的工作簿，添加工作表，设置单元格的值，读取数据，以及应用各种格式和样式。导出时，这些操作会保存到...
Java poi 实现excel导入导出
10-23
在提供的“java-excel导入导出”压缩包中，可能包含了示例代码、说明文档以及测试数据，可以帮助初学者快速理解和使用Java POI进行Excel操作。通过学习和实践这些示例，你可以掌握Java POI库的基本用法，并能灵活...
easy-poi导入导出excel实例
03-15
《使用Easy-Poi进行Excel导入导出的实践详解》 在现代企业中，数据处理是一项不可或缺的任务，而Excel作为常见的数据存储和分析工具，其导入导出功能在各种业务场景中发挥着重要作用。Easy-Poi是一款优秀的Java库，...
091、Python 写Excel文件（使用openpyxl库）
不在同一频道上的呆子的博客
07-24 464
Python 写Excel文件（使用openpyxl库）
Linux Vim教程：多文件编辑与窗口管理
07-25 865
Vim作为一款功能强大的文本编辑器，支持多文件编辑和窗口管理。掌握这些高级技巧可以大大提高工作效率和文件处理能力。本文将详细介绍Vim中多文件编辑与窗口管理的各种方法和技巧，包括缓冲区、标签页、分屏、快速切换等。通过这些实用技巧，您可以更高效地在Vim中进行复杂的文本编辑任务。
EXCEL自动公式计算始终为0
最新发布
zixingcai的博客
07-25 218
此时就可以进行公式计算了。
poi带入excel工具类下载
01-11
POI（Poor Obfuscated Implementation）是一款用于操作Microsoft Office格式文件的Java API库。它的目标是提供一种简单、快速、可靠的方式来读取、创建和编辑这些类型的文件，包括Excel、Word和PowerPoint等。 POI库中的HSSFWorkbook类用于操作Excel文件。开发者可以使用该类在内存中创建一个Excel文件，并将数据填充到不同的工作表和单元格中。此外，HSSFWorkbook也可以打开现有的Excel文件，以便进行编辑和保存。 为了将POI带入Excel，我们可以使用POI提供的API来实现Excel文件的下载。首先，我们需要创建一个HSSFWorkbook对象，并设置工作表的名字。然后，可以利用HSSFWorkbook对象创建一个或多个工作表，并填充所需的数据。最后，我们将HSSFWorkbook写入OutputStream或将其保存到本地文件中。这样，使用我们编写的代码，用户就可以下载包含所需数据的Excel文件。 例如，假设我们要从数据库中获取一些用户信息，并将其导出到Excel文件中进行下载。我们可以使用POI库提供的API来实现这个需求。首先，我们连接数据库并查询所需的用户信息。然后，我们创建一个HSSFWorkbook对象并设置一个工作表名称，比如“用户信息”。接下来，我们使用结果集将用户信息逐行填充到工作表的不同单元格中。最后，我们将HSSFWorkbook写入到OutputStream中，并将其作为一个可下载的文件返回给用户。 总之，POI是一个非常实用的工具类，可以帮助我们在Java应用程序中操作Excel文件。无论是创建、读取还是编辑Excel，POI库都提供了简单且丰富的API来满足我们的需求。通过将POI带入Excel，我们可以方便地实现Excel文件的下载功能。
评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值