相信在大部分的web项目中都会有导出导入Excel的需求，今天我们就来看看如何用Java代码去实现 用POI导出Excel表格。

一、pom引用

pom文件中，添加以下依赖

查看代码
                    org.apache.poi            poi            5.2.2            compile                            org.apache.poi            poi-ooxml            5.2.2            compile

二、工具类util

1.ExcelClassField

查看代码
package com.***; import lombok.Data; import java.util.LinkedHashMap; /** * @description: excel字段 * @author: *** * @date: 2022/6/21 */@Datapublic class ExcelClassField {    /**     * 字段名称     */    private String fieldName;     /**     * 表头名称     */    private String name;     /**     * 映射关系     */    private LinkedHashMap kvMap;     /**     * 示例值     */    private Object example;     /**     * 排序     */    private int sort;     /**     * 是否为注解字段：0-否，1-是     */    private int hasAnnotation; }

2.ExcelExport（导出Excel用）

查看代码
package com.***; import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target; /** * @description: excel导出 * @author: *** * @date: 2022/6/21 */@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 ""; }

3.ExcelImport（导入Excel用）

查看代码
package com.***; import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target; /** * @description: excel导入 * @author: *** * @date: 2022/6/21 */@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;     /**     * 导入唯一性验证（多个字段则取联合验证）     */    boolean unique() default false; }

4.ExcelUtils

查看代码
package com.***;import com.***.utils.DateUtil;import com.alibaba.fastjson.JSONArray;import com.alibaba.fastjson.JSONObject;import com.***.uitl.StringUtils;import org.apache.poi.hssf.usermodel.HSSFDataValidation;import org.apache.poi.hssf.usermodel.HSSFWorkbook;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.SXSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFClientAnchor;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.springframework.web.context.request.RequestContextHolder;import org.springframework.web.context.request.ServletRequestAttributes;import org.springframework.web.multipart.MultipartFile; import javax.servlet.ServletOutputStream;import javax.servlet.http.HttpServletRequest;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.net.URLEncoder;import java.text.NumberFormat;import java.text.SimpleDateFormat;import java.util.*;import java.util.Map.Entry;import java.util.regex.Pattern;  /** * @description: 导出报表util * @author: *** * @date: 2022/6/21 */public class ExcelUtils {    public static final String ROW_MERGE = "row_merge";    public static final String COLUMN_MERGE = "column_merge";    private static final String XLSX = ".xlsx";    private static final String XLS = ".xls";    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  List readFile(File file, Class clazz) throws Exception {        JSONArray array = readFile(file);        return getBeanList(array, clazz);    }     public static  List readMultipartFile(MultipartFile mFile, Class 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 readFileManySheet(File file) throws Exception {        return readExcelManySheet(null, file);    }     public static Map readFileManySheet(MultipartFile file) throws Exception {        return readExcelManySheet(file, null);    }     private static  List getBeanList(JSONArray array, Class clazz) throws Exception {        List list = new ArrayList();        Map 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 getBean(Class c, JSONObject obj, Map uniqueMap) throws Exception {        T t = c.newInstance();        Field[] fields = c.getDeclaredFields();        List 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 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, sb.toString());            }        }        return t;    }     private static  void setFieldValue(T t, Field field, JSONObject obj, StringBuilder uniqueBuilder, List 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()));        }        // 判断当前属性是否有映射关系        LinkedHashMap 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)) {                if (StringUtils.isEmpty(val)) {                    field.set(t, new BigDecimal(0));                } else {                    field.set(t, new BigDecimal(val));                }            } else if ("Date".equalsIgnoreCase(fieldClassName)) {                field.set(t, DateUtil.getDate(val, DateUtil.YYYYMMDDHHMMSS));            } else {                field.set(t, val);            }        } catch (Exception e) {            e.printStackTrace();        }    }     private static Map readExcelManySheet(MultipartFile mFile, File file) throws IOException {        Workbook book = getWorkbook(mFile, file);        if (book == null) {            return Collections.emptyMap();        }        Map 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;        String fileName;        if (mFile != null) {            // 上传文件解析            in = mFile.getInputStream();            fileName = getString(mFile.getOriginalFilename()).toLowerCase();        } else {            // 本地文件解析            in = new FileInputStream(file);            fileName = file.getName().toLowerCase();        }        Workbook book;        if (fileName.endsWith(XLSX)) {            book = new XSSFWorkbook(in);        } else if (fileName.endsWith(XLS)) {            POIFSFileSystem poifsFileSystem = new POIFSFileSystem(in);            book = new HSSFWorkbook(poifsFileSystem);        } else {            return 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 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  0) {                array.add(obj);            }        }        return array;    }     private static String getCellValue(Cell cell) {        // 空白或空        if (cell == null || cell.getCellType() == CellType.BLANK) {            return "";        }        // String类型        if (cell.getCellType() == CellType.STRING) {            String val = cell.getStringCellValue();            if (val == null || val.trim().length() == 0) {                return "";            }            return val.trim();        }        // 数字类型        if (cell.getCellType() == CellType.NUMERIC) {            String s = cell.getNumericCellValue() + "";            // 去掉尾巴上的小数点0            if (Pattern.matches(".*\\.0*", s)) {                return s.split("\\.")[0];            } else {                return s.trim();            }        }        // 布尔值类型        if (cell.getCellType() == CellType.BOOLEAN) {            return cell.getBooleanCellValue() + "";        }        // 错误类型        return cell.getCellFormula();    }     public static  void exportTemplate(HttpServletResponse response, String fileName, Class clazz, String dateFormat) {        exportTemplate(response, fileName, fileName, clazz, false, dateFormat);    }     public static  void exportTemplate(HttpServletResponse response, String fileName, String sheetName,                                          Class clazz, String dateFormat) {        exportTemplate(response, fileName, sheetName, clazz, false, dateFormat);    }     public static  void exportTemplate(HttpServletResponse response, String fileName, Class clazz,                                          boolean isContainExample, String dateFormat) {        exportTemplate(response, fileName, fileName, clazz, isContainExample, dateFormat);    }     public static  void exportTemplate(HttpServletResponse response, String fileName, String sheetName,                                          Class clazz, boolean isContainExample, String dateFormat) {        // 获取表头字段        List headFieldList = getExcelClassFieldList(clazz);        // 获取表头数据和示例数据        List<List> sheetDataList = new ArrayList();        List headList = new ArrayList();        List exampleList = new ArrayList();        Map<Integer, List> 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 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, dateFormat);    }     private static  List getExcelClassFieldList(Class clazz) {        // 解析所有字段        Field[] fields = clazz.getDeclaredFields();        boolean hasExportAnnotation = false;        Map<Integer, List> map = new LinkedHashMap();        List 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 list = new ArrayList();                list.add(cf);                sortList.add(sort);                map.put(sort, list);            }        }        Collections.sort(sortList);        // 获取表头        List 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)) {                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 getKvMap(String kv) {        LinkedHashMap 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> sheetData, String dateFormat) {        if (file == null) {            System.out.println("文件创建失败");            return;        }        if (sheetData == null) {            sheetData = new ArrayList();        }        Map<String, List<List>> map = new HashMap();        map.put(file.getName(), sheetData);        export(null, file, file.getName(), map, null, dateFormat);    }     /**     * 导出表格到本地     *     * @param       导出数据类似，和K类型保持一致     * @param filePath 文件父路径（如：D:/doc/excel/）     * @param fileName 文件名称（不带尾缀，如：学生表）     * @param list     导出数据     * @throws IOException IO异常     */    public static  File exportFile(String filePath, String fileName, List list, String dateFormat) throws IOException {        File file = getFile(filePath, fileName);        List<List> sheetData = getSheetData(list);        exportFile(file, sheetData, dateFormat);        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()) {                dirFile.mkdirs();            }            // 获取文件夹全名            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()) {            file.createNewFile();        }        return file;    }     private static  List<List> getSheetData(List list) {        // 获取表头字段        List excelClassFieldList = getExcelClassFieldList(list.get(0).getClass());        List headFieldList = new ArrayList();        List headList = new ArrayList();        Map headFieldMap = new HashMap();        for (ExcelClassField each : excelClassFieldList) {            String fieldName = each.getFieldName();            headFieldList.add(fieldName);            headFieldMap.put(fieldName, each);            headList.add(each.getName());        }        // 添加表头名称        List<List> sheetDataList = new ArrayList();        sheetDataList.add(headList);        // 获取表数据        for (T t : list) {            Map fieldDataMap = getFieldDataMap(t);            Set fieldDataKeys = fieldDataMap.keySet();            List rowList = new ArrayList();            for (String headField : headFieldList) {                if (!fieldDataKeys.contains(headField)) {                    continue;                }                Object data = fieldDataMap.get(headField);                if (data == null) {                    rowList.add("");                    continue;                }                // 超防止过11位的数字自动转换为科学计数法                String string = data.toString();                if (isNumeric(string) && string.length() > 11) {                    data = new BigDecimal(string).toPlainString();                }                 ExcelClassField cf = headFieldMap.get(headField);                // 判断是否有映射关系                LinkedHashMap kvMap = cf.getKvMap();                if (kvMap == null || kvMap.isEmpty()) {                    rowList.add(data);                    continue;                }                String val = kvMap.get(data.toString());                if (val == null) {                    rowList.add(data);                    continue;                }                if (isNumeric(val)) {                    rowList.add(Double.valueOf(val));                } else {                    rowList.add(val);                }            }            sheetDataList.add(rowList);        }        return sheetDataList;    }     private static  Map getFieldDataMap(T t) {        Map 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, String dateFormat) {        List<List> sheetDataList = new ArrayList();        List headList = new ArrayList();        headList.add("导出无数据");        sheetDataList.add(headList);        export(response, fileName, sheetDataList, dateFormat);    }     public static void export(HttpServletResponse response, String fileName, List<List> sheetDataList, String dateFormat) {        export(response, fileName, fileName, sheetDataList, null, dateFormat);    }     public static void exportManySheet(HttpServletResponse response, String fileName, Map<String, List<List>> sheetMap, String dateFormat) {        export(response, null, fileName, sheetMap, null, dateFormat);    }      public static void export(HttpServletResponse response, String fileName, String sheetName,                              List<List> sheetDataList, String dateFormat) {        export(response, fileName, sheetName, sheetDataList, null, dateFormat);    }     public static void export(HttpServletResponse response, String fileName, String sheetName,                              List<List> sheetDataList, Map<Integer, List> selectMap, String dateFormat) {         Map<String, List<List>> map = new HashMap();        map.put(sheetName, sheetDataList);        export(response, null, fileName, map, selectMap, dateFormat);    }     public static  void export(HttpServletResponse response, String fileName, List list, Class template) {        String dateFormat = DATE_FORMAT;        // list 是否为空        boolean lisIsEmpty = list == null || list.isEmpty();        // 如果模板数据为空，且导入的数据为空，则导出空文件        if (template == null && lisIsEmpty) {            exportEmpty(response, fileName, dateFormat);            return;        }        // 如果 list 数据，则导出模板数据        if (lisIsEmpty) {            exportTemplate(response, fileName, template, dateFormat);            return;        }        // 导出数据        List<List> sheetDataList = getSheetData(list);        export(response, fileName, sheetDataList, dateFormat);    }     public static  void export(HttpServletResponse response, String fileName, List list, Class template, String dateFormat) {        // list 是否为空        boolean lisIsEmpty = list == null || list.isEmpty();        // 如果模板数据为空，且导入的数据为空，则导出空文件        if (template == null && lisIsEmpty) {            exportEmpty(response, fileName, dateFormat);            return;        }        // 如果 list 数据，则导出模板数据        if (lisIsEmpty) {            exportTemplate(response, fileName, template, dateFormat);            return;        }        // 导出数据        List<List> sheetDataList = getSheetData(list);        export(response, fileName, sheetDataList, dateFormat);    }     public static void export(HttpServletResponse response, String fileName, List<List> sheetDataList, Map<Integer, List> selectMap, String dateFormat) {        export(response, fileName, fileName, sheetDataList, selectMap, dateFormat);    }     private static void export(HttpServletResponse response, File file, String fileName,                               Map<String, List<List>> sheetMap, Map<Integer, List> selectMap, String dateFormat) {        // 整个 Excel 表格 book 对象        SXSSFWorkbook book = new SXSSFWorkbook();        // 每个 Sheet 页        Set<Entry<String, List<List>>> entries = sheetMap.entrySet();        for (Entry<String, List<List>> entry : entries) {            List<List> 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);            //创建一个DataFormat对象            DataFormat format = book.createDataFormat();            rowStyle.setDataFormat(format.getFormat("@"));            // 设置表格列宽度（默认为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 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, dateFormat);                        } else {                            // 其他行为数据行，默认白底色                            v = setCellValue(cell, o, rowStyle, dateFormat);                        }                    }                    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;            try {                fos = new FileOutputStream(file);                ByteArrayOutputStream ops = new ByteArrayOutputStream();                book.write(ops);                fos.write(ops.toByteArray());                fos.close();            } catch (Exception e) {                e.printStackTrace();            }        }    }     /**     * 合并当前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  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  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);*/        /*String formFileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);        response.setHeader("Content-Disposition", String.format("attachment; filename=\"%s\";", formFileName));        response.setContentType("multipart/form-data");        response.setCharacterEncoding("UTF-8");*/        ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();        assert attributes != null;        HttpServletRequest request = attributes.getRequest();        response.setContentType("multipart/form-data;charset=UTF-8");        String userAgent = request.getHeader("USER-AGENT");        try {            if (userAgent.contains("MSIE") || userAgent.contains("Edge")) {// IE浏览器                fileName = URLEncoder.encode(fileName, "UTF8");            } else if (userAgent.contains("Mozilla")) {// google,火狐浏览器                fileName = new String(fileName.getBytes(), "ISO8859-1");            } else {                fileName = URLEncoder.encode(fileName, "UTF8");// 其他浏览器            }            response.setHeader("Content-disposition", "attachment; filename=" + fileName);        } catch (UnsupportedEncodingException e) {            e.printStackTrace();        }        ServletOutputStream out = response.getOutputStream();        book.write(out);        out.flush();        out.close();    }     private static int setCellValue(Cell cell, Object o, CellStyle style, String dateFormat) {        // 设置样式        cell.setCellStyle(style);        // 数据为空时        if (o == null) {            cell.setCellType(CellType.STRING);            cell.setCellValue("");            return CELL_OTHER;        }        // 是否为字符串        if (o instanceof String) {            String s = o.toString();            if (isNumeric(s)) {                if (s.length() > 11) {                    cell.setCellType(CellType.STRING);                    cell.setCellValue(s);                    return CELL_OTHER;                } else {                    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, dateFormat));            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, String dateFormat) {        if (date == null) {            return "";        }        if (dateFormat == null) {            dateFormat = DATE_FORMAT;        }        SimpleDateFormat format = new SimpleDateFormat(dateFormat);        return format.format(date);    }     private static void setSelect(Sheet sheet, Map<Integer, List> selectMap) {        if (selectMap == null || selectMap.isEmpty()) {            return;        }        Set<Entry<Integer, List>> entrySet = selectMap.entrySet();        for (Entry<Integer, List> entry : entrySet) {            int y = entry.getKey();            List list = entry.getValue();            if (list == null || list.isEmpty()) {                continue;            }            String[] arr = new String[list.size()];            for (int i = 0; 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();    } }
三、相关业务代码
1.service层
    /*** 车运运单信息导出*/    List exportBillInfo(List billIdList);
2.impl实现类
 实现类里的代码，需求各位根据自己的业务场景进行改动，将需要导出的数据查询出来
 
 查看代码
    @Override    public List exportBillInfo(List billIdList) {        if (StringUtils.isEmpty(billIdList)) {            throw new ServiceException("车辆运单ID不能为空");        }        BillDto billDto = new BillDto();        billDto.setBillIdList(billIdList);        List billList = billFacadeService.queryBillList(billDto);        if (StringUtils.isEmpty(billList)) {            billList = new ArrayList();        }        return InvoiceWrapper.instance.convertTo(billList);    }
3.controller层
controller层的代码需要注意的是：
1.因为导出Excel一般都是通过浏览器进行下载的，所以入参中需要加入HttpServletResponse
2.调用封装的工具类ExcelUtils中的export方法就可以了
 
 查看代码
    /**     * 车运运单信息导出     */    @Log    @PostMapping("/exportBillInfo")    public void exportBillInfo(@RequestBody @Valid BillListDto dto, HttpServletResponse response) {        List list = invoiceFacadeService.exportBillInfo(dto.getBillIdList());        ExcelUtils.export(response, "车运运单信息", list, ExportBillVo.class);    }
最终导出的Excel文件：
相关文章
Copyright ©  maxssl.com 版权所有 浙ICP备2022011180号