devbox
我家小花儿
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

java导出excel（带图片）_java导出excel带图片

作者：我家小花儿 | 2024-03-20 11:34:15

赞

踩

java导出excel带图片

先看一下导出效果，

controller

表头数据一定要放在最前面

List<Object> head = Arrays.asList("姓名","年龄","性别","证件类别","证件号","联系电话","地区","详细地址","报名时间","所属分组","年度","参赛类别1","作品名称1","作品1","参赛类别2","作品名称2","作品2");

List<List<Object>> sheetDataList = new ArrayList<>();

sheetDataList.add(head);

  1.  /**
  2.      * 导出报名列表
  3.      */
  4.     @GetMapping("/export")
  5.     public void export(HttpServletResponse response, ExMessage exMessage) throws MalformedURLException {
  6.         List<ExMessage> list = exMessageService.selectExMessageList(exMessage);
  7.         // 表头数据
  8.         List<Object> head = Arrays.asList("姓名","年龄","性别","证件类别","证件号","联系电话","地区","详细地址","报名时间","所属分组","年度","参赛类别1","作品名称1","作品1","参赛类别2","作品名称2","作品2");
  9.         List<List<Object>> sheetDataList = new ArrayList<>();
  10.         sheetDataList.add(head);
  11.         SimpleDateFormat ymd=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
  12.  
  13.         // 用户数据
  14.         for (ExMessage em:list){
  15.             List<Object> user = new ArrayList<>();
  16.             user.add(em.getName());
  17.             user.add(em.getAge());
  18.             user.add(em.getSex());
  19.             user.add(em.getCred());
  20.             user.add(em.getIdNumber());
  21.             user.add(em.getPhone());
  22.             user.add(em.getArea());
  23.             user.add(em.getAddress());
  24.             user.add(ymd.format(em.getCreateTime()));
  25.             user.add(em.getExGroup());
  26.             user.add(em.getYear());
  27.             user.add(em.getTakeTypeOne());
  28.             user.add(em.getProjNameOne());
  29. //图片的话转成url放入链接就可以
  30.             user.add(new URL(em.getProjFileOne()));
  31.             user.add(em.getTakeTypeTwo());
  32.             user.add(em.getProjNameTwo());
  33.             user.add(new URL(em.getProjFileTwo()));
  34.             sheetDataList.add(user);
  35.         }
  36.         // 导出数据
  37.         ExcelUtils.export(response,"报名信息表", sheetDataList);
  38.     }
ExcelUtils
  1. package com.ruoyi.expo.controller;
  2.  
  3.  
  4. import com.alibaba.fastjson2.JSONArray;
  5. import com.alibaba.fastjson2.JSONObject;
  6. import org.apache.poi.hssf.usermodel.HSSFDataValidation;
  7. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  8. import org.apache.poi.poifs.filesystem.POIFSFileSystem;
  9. import org.apache.poi.ss.usermodel.*;
  10. import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
  11. import org.apache.poi.ss.util.CellRangeAddress;
  12. import org.apache.poi.ss.util.CellRangeAddressList;
  13. import org.apache.poi.xssf.streaming.SXSSFWorkbook;
  14. import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
  15. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  16. import org.springframework.web.multipart.MultipartFile;
  17.  
  18. import javax.servlet.ServletOutputStream;
  19. import javax.servlet.http.HttpServletResponse;
  20. import java.io.*;
  21. import java.lang.reflect.Field;
  22. import java.math.BigDecimal;
  23. import java.math.RoundingMode;
  24. import java.net.URL;
  25. import java.text.NumberFormat;
  26. import java.text.SimpleDateFormat;
  27. import java.util.*;
  28. import java.util.Map.Entry;
  29. import java.util.regex.Pattern;
  30.  
  31. /**
  32.  * Excel导入导出工具类
  33.  * 原文链接（不定时增加新功能）: https://zyqok.blog.csdn.net/article/details/121994504
  34.  *
  35.  * @author: cyf
  36.  * @Date: 2023/3/16 15:53
  37.  */
  38.  
  39. @SuppressWarnings("unused")
  40. public class ExcelUtils {
  41.  
  42.     private static final String XLSX = ".xlsx";
  43.     private static final String XLS = ".xls";
  44.     public static final String ROW_MERGE = "row_merge";
  45.     public static final String COLUMN_MERGE = "column_merge";
  46.     private static final String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
  47.     private static final String ROW_NUM = "rowNum";
  48.     private static final String ROW_DATA = "rowData";
  49.     private static final String ROW_TIPS = "rowTips";
  50.     private static final int CELL_OTHER = 0;
  51.     private static final int CELL_ROW_MERGE = 1;
  52.     private static final int CELL_COLUMN_MERGE = 2;
  53.     private static final int IMG_HEIGHT = 30;
  54.     private static final int IMG_WIDTH = 30;
  55.     private static final char LEAN_LINE = '/';
  56.     private static final int BYTES_DEFAULT_LENGTH = 10240;
  57.     private static final NumberFormat NUMBER_FORMAT = NumberFormat.getNumberInstance();
  58.  
  59.  
  60.     public static <T> List<T> readFile(File file, Class<T> clazz) throws Exception {
  61.         JSONArray array = readFile(file);
  62.         return getBeanList(array, clazz);
  63.     }
  64.  
  65.     public static <T> List<T> readMultipartFile(MultipartFile mFile, Class<T> clazz) throws Exception {
  66.         JSONArray array = readMultipartFile(mFile);
  67.         return getBeanList(array, clazz);
  68.     }
  69.  
  70.     public static JSONArray readFile(File file) throws Exception {
  71.         return readExcel(null, file);
  72.     }
  73.  
  74.     public static JSONArray readMultipartFile(MultipartFile mFile) throws Exception {
  75.         return readExcel(mFile, null);
  76.     }
  77.  
  78.     public static Map<String, JSONArray> readFileManySheet(File file) throws Exception {
  79.         return readExcelManySheet(null, file);
  80.     }
  81.  
  82.     public static Map<String, JSONArray> readFileManySheet(MultipartFile file) throws Exception {
  83.         return readExcelManySheet(file, null);
  84.     }
  85.  
  86.     private static <T> List<T> getBeanList(JSONArray array, Class<T> clazz) throws Exception {
  87.         List<T> list = new ArrayList<>();
  88.         Map<Integer, String> uniqueMap = new HashMap<>(16);
  89.         for (int i = 0; i < array.size(); i++) {
  90.             list.add(getBean(clazz, array.getJSONObject(i), uniqueMap));
  91.         }
  92.         return list;
  93.     }
  94.  
  95.     /**
  96.      * 获取每个对象的数据
  97.      */
  98.     private static <T> T getBean(Class<T> c, JSONObject obj, Map<Integer, String> uniqueMap) throws Exception {
  99.         T t = c.newInstance();
  100.         Field[] fields = c.getDeclaredFields();
  101.         List<String> errMsgList = new ArrayList<>();
  102.         boolean hasRowTipsField = false;
  103.         StringBuilder uniqueBuilder = new StringBuilder();
  104.         int rowNum = 0;
  105.         for (Field field : fields) {
  106.             // 行号
  107.             if (field.getName().equals(ROW_NUM)) {
  108.                 rowNum = obj.getInteger(ROW_NUM);
  109.                 field.setAccessible(true);
  110.                 field.set(t, rowNum);
  111.                 continue;
  112.             }
  113.             // 是否需要设置异常信息
  114.             if (field.getName().equals(ROW_TIPS)) {
  115.                 hasRowTipsField = true;
  116.                 continue;
  117.             }
  118.             // 原始数据
  119.             if (field.getName().equals(ROW_DATA)) {
  120.                 field.setAccessible(true);
  121.                 field.set(t, obj.toString());
  122.                 continue;
  123.             }
  124.             // 设置对应属性值
  125.             setFieldValue(t, field, obj, uniqueBuilder, errMsgList);
  126.         }
  127.         // 数据唯一性校验
  128.         if (uniqueBuilder.length() > 0) {
  129.             if (uniqueMap.containsValue(uniqueBuilder.toString())) {
  130.                 Set<Integer> rowNumKeys = uniqueMap.keySet();
  131.                 for (Integer num : rowNumKeys) {
  132.                     if (uniqueMap.get(num).equals(uniqueBuilder.toString())) {
  133.                         errMsgList.add(String.format("数据唯一性校验失败,(%s)与第%s行重复)", uniqueBuilder, num));
  134.                     }
  135.                 }
  136.             } else {
  137.                 uniqueMap.put(rowNum, uniqueBuilder.toString());
  138.             }
  139.         }
  140.         // 失败处理
  141.         if (errMsgList.isEmpty() && !hasRowTipsField) {
  142.             return t;
  143.         }
  144.         StringBuilder sb = new StringBuilder();
  145.         int size = errMsgList.size();
  146.         for (int i = 0; i < size; i++) {
  147.             if (i == size - 1) {
  148.                 sb.append(errMsgList.get(i));
  149.             } else {
  150.                 sb.append(errMsgList.get(i)).append(";");
  151.             }
  152.         }
  153.         // 设置错误信息
  154.         for (Field field : fields) {
  155.             if (field.getName().equals(ROW_TIPS)) {
  156.                 field.setAccessible(true);
  157.                 field.set(t, sb.toString());
  158.             }
  159.         }
  160.         return t;
  161.     }
  162.  
  163.     private static <T> void setFieldValue(T t, Field field, JSONObject obj, StringBuilder uniqueBuilder, List<String> errMsgList) {
  164.         // 获取 ExcelImport 注解属性
  165.         ExcelImport annotation = field.getAnnotation(ExcelImport.class);
  166.         if (annotation == null) {
  167.             return;
  168.         }
  169.         String cname = annotation.value();
  170.         if (cname.trim().length() == 0) {
  171.             return;
  172.         }
  173.         // 获取具体值
  174.         String val = null;
  175.         if (obj.containsKey(cname)) {
  176.             val = getString(obj.getString(cname));
  177.         }
  178.         if (val == null) {
  179.             return;
  180.         }
  181.         field.setAccessible(true);
  182.         // 判断是否必填
  183.         boolean require = annotation.required();
  184.         if (require && val.isEmpty()) {
  185.             errMsgList.add(String.format("[%s]不能为空", cname));
  186.             return;
  187.         }
  188.         // 数据唯一性获取
  189.         boolean unique = annotation.unique();
  190.         if (unique) {
  191.             if (uniqueBuilder.length() > 0) {
  192.                 uniqueBuilder.append("--").append(val);
  193.             } else {
  194.                 uniqueBuilder.append(val);
  195.             }
  196.         }
  197.         // 判断是否超过最大长度
  198.         int maxLength = annotation.maxLength();
  199.         if (maxLength > 0 && val.length() > maxLength) {
  200.             errMsgList.add(String.format("[%s]长度不能超过%s个字符(当前%s个字符)", cname, maxLength, val.length()));
  201.         }
  202.         // 判断当前属性是否有映射关系
  203.         LinkedHashMap<String, String> kvMap = getKvMap(annotation.kv());
  204.         if (!kvMap.isEmpty()) {
  205.             boolean isMatch = false;
  206.             for (String key : kvMap.keySet()) {
  207.                 if (kvMap.get(key).equals(val)) {
  208.                     val = key;
  209.                     isMatch = true;
  210.                     break;
  211.                 }
  212.             }
  213.             if (!isMatch) {
  214.                 errMsgList.add(String.format("[%s]的值不正确(当前值为%s)", cname, val));
  215.                 return;
  216.             }
  217.         }
  218.         // 其余情况根据类型赋值
  219.         String fieldClassName = field.getType().getSimpleName();
  220.         try {
  221.             if ("String".equalsIgnoreCase(fieldClassName)) {
  222.                 field.set(t, val);
  223.             } else if ("boolean".equalsIgnoreCase(fieldClassName)) {
  224.                 field.set(t, Boolean.valueOf(val));
  225.             } else if ("int".equalsIgnoreCase(fieldClassName) || "Integer".equals(fieldClassName)) {
  226.                 try {
  227.                     field.set(t, Integer.valueOf(val));
  228.                 } catch (NumberFormatException e) {
  229.                     errMsgList.add(String.format("[%s]的值格式不正确(当前值为%s)", cname, val));
  230.                 }
  231.             } else if ("double".equalsIgnoreCase(fieldClassName)) {
  232.                 field.set(t, Double.valueOf(val));
  233.             } else if ("long".equalsIgnoreCase(fieldClassName)) {
  234.                 field.set(t, Long.valueOf(val));
  235.             } else if ("BigDecimal".equalsIgnoreCase(fieldClassName)) {
  236.                 field.set(t, new BigDecimal(val));
  237.             } else if ("Date".equalsIgnoreCase(fieldClassName)) {
  238.                 try {
  239.                     field.set(t, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(val));
  240.                 } catch (Exception e) {
  241.                     field.set(t, new SimpleDateFormat("yyyy-MM-dd").parse(val));
  242.                 }
  243.             }
  244.         } catch (Exception e) {
  245.             e.printStackTrace();
  246.         }
  247.     }
  248.  
  249.     private static Map<String, JSONArray> readExcelManySheet(MultipartFile mFile, File file) throws IOException {
  250.         Workbook book = getWorkbook(mFile, file);
  251.         if (book == null) {
  252.             return Collections.emptyMap();
  253.         }
  254.         Map<String, JSONArray> map = new LinkedHashMap<>();
  255.         for (int i = 0; i < book.getNumberOfSheets(); i++) {
  256.             Sheet sheet = book.getSheetAt(i);
  257.             JSONArray arr = readSheet(sheet);
  258.             map.put(sheet.getSheetName(), arr);
  259.         }
  260.         book.close();
  261.         return map;
  262.     }
  263.  
  264.     private static JSONArray readExcel(MultipartFile mFile, File file) throws IOException {
  265.         Workbook book = getWorkbook(mFile, file);
  266.         if (book == null) {
  267.             return new JSONArray();
  268.         }
  269.         JSONArray array = readSheet(book.getSheetAt(0));
  270.         book.close();
  271.         return array;
  272.     }
  273.  
  274.     private static Workbook getWorkbook(MultipartFile mFile, File file) throws IOException {
  275.         boolean fileNotExist = (file == null || !file.exists());
  276.         if (mFile == null && fileNotExist) {
  277.             return null;
  278.         }
  279.         // 解析表格数据
  280.         InputStream in;
  281.         String fileName;
  282.         if (mFile != null) {
  283.             // 上传文件解析
  284.             in = mFile.getInputStream();
  285.             fileName = getString(mFile.getOriginalFilename()).toLowerCase();
  286.         } else {
  287.             // 本地文件解析
  288.             in = new FileInputStream(file);
  289.             fileName = file.getName().toLowerCase();
  290.         }
  291.         Workbook book;
  292.         if (fileName.endsWith(XLSX)) {
  293.             book = new XSSFWorkbook(in);
  294.         } else if (fileName.endsWith(XLS)) {
  295.             POIFSFileSystem poifsFileSystem = new POIFSFileSystem(in);
  296.             book = new HSSFWorkbook(poifsFileSystem);
  297.         } else {
  298.             return null;
  299.         }
  300.         in.close();
  301.         return book;
  302.     }
  303.  
  304.     private static JSONArray readSheet(Sheet sheet) {
  305.         // 首行下标
  306.         int rowStart = sheet.getFirstRowNum();
  307.         // 尾行下标
  308.         int rowEnd = sheet.getLastRowNum();
  309.         // 获取表头行
  310.         Row headRow = sheet.getRow(rowStart);
  311.         if (headRow == null) {
  312.             return new JSONArray();
  313.         }
  314.         int cellStart = headRow.getFirstCellNum();
  315.         int cellEnd = headRow.getLastCellNum();
  316.         Map<Integer, String> keyMap = new HashMap<>();
  317.         for (int j = cellStart; j < cellEnd; j++) {
  318.             // 获取表头数据
  319.             String val = getCellValue(headRow.getCell(j));
  320.             if (val != null && val.trim().length() != 0) {
  321.                 keyMap.put(j, val);
  322.             }
  323.         }
  324.         // 如果表头没有数据则不进行解析
  325.         if (keyMap.isEmpty()) {
  326.             return (JSONArray) Collections.emptyList();
  327.         }
  328.         // 获取每行JSON对象的值
  329.         JSONArray array = new JSONArray();
  330.         // 如果首行与尾行相同，表明只有一行，返回表头数据
  331.         if (rowStart == rowEnd) {
  332.             JSONObject obj = new JSONObject();
  333.             // 添加行号
  334.             obj.put(ROW_NUM, 1);
  335.             for (int i : keyMap.keySet()) {
  336.                 obj.put(keyMap.get(i), "");
  337.             }
  338.             array.add(obj);
  339.             return array;
  340.         }
  341.         for (int i = rowStart + 1; i <= rowEnd; i++) {
  342.             Row eachRow = sheet.getRow(i);
  343.             JSONObject obj = new JSONObject();
  344.             // 添加行号
  345.             obj.put(ROW_NUM, i + 1);
  346.             StringBuilder sb = new StringBuilder();
  347.             for (int k = cellStart; k < cellEnd; k++) {
  348.                 if (eachRow != null) {
  349.                     String val = getCellValue(eachRow.getCell(k));
  350.                     // 所有数据添加到里面，用于判断该行是否为空
  351.                     sb.append(val);
  352.                     obj.put(keyMap.get(k), val);
  353.                 }
  354.             }
  355.             if (sb.length() > 0) {
  356.                 array.add(obj);
  357.             }
  358.         }
  359.         return array;
  360.     }
  361.  
  362.     private static String getCellValue(Cell cell) {
  363.         // 空白或空
  364.         if (cell == null || cell.getCellTypeEnum() == CellType.BLANK) {
  365.             return "";
  366.         }
  367.         // String类型
  368.         if (cell.getCellTypeEnum() == CellType.STRING) {
  369.             String val = cell.getStringCellValue();
  370.             if (val == null || val.trim().length() == 0) {
  371.                 return "";
  372.             }
  373.             return val.trim();
  374.         }
  375.         // 数字类型
  376.         if (cell.getCellTypeEnum() == CellType.NUMERIC) {
  377.             String s = cell.getNumericCellValue() + "";
  378.             // 去掉尾巴上的小数点0
  379.             if (Pattern.matches(".*\\.0*", s)) {
  380.                 return s.split("\\.")[0];
  381.             } else {
  382.                 return s;
  383.             }
  384.         }
  385.         // 布尔值类型
  386.         if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
  387.             return cell.getBooleanCellValue() + "";
  388.         }
  389.         // 错误类型
  390.         return cell.getCellFormula();
  391.     }
  392.  
  393.     public static <T> void exportTemplate(HttpServletResponse response, String fileName, Class<T> clazz) {
  394.         exportTemplate(response, fileName, fileName, clazz, false);
  395.     }
  396.  
  397.     public static <T> void exportTemplate(HttpServletResponse response, String fileName, String sheetName,
  398.                                           Class<T> clazz) {
  399.         exportTemplate(response, fileName, sheetName, clazz, false);
  400.     }
  401.  
  402.     public static <T> void exportTemplate(HttpServletResponse response, String fileName, Class<T> clazz,
  403.                                           boolean isContainExample) {
  404.         exportTemplate(response, fileName, fileName, clazz, isContainExample);
  405.     }
  406.  
  407.     public static <T> void exportTemplate(HttpServletResponse response, String fileName, String sheetName,
  408.                                           Class<T> clazz, boolean isContainExample) {
  409.         // 获取表头字段
  410.         List<ExcelClassField> headFieldList = getExcelClassFieldList(clazz);
  411.         // 获取表头数据和示例数据
  412.         List<List<Object>> sheetDataList = new ArrayList<>();
  413.         List<Object> headList = new ArrayList<>();
  414.         List<Object> exampleList = new ArrayList<>();
  415.         Map<Integer, List<String>> selectMap = new LinkedHashMap<>();
  416.         for (int i = 0; i < headFieldList.size(); i++) {
  417.             ExcelClassField each = headFieldList.get(i);
  418.             headList.add(each.getName());
  419.             exampleList.add(each.getExample());
  420.             LinkedHashMap<String, String> kvMap = each.getKvMap();
  421.             if (kvMap != null && kvMap.size() > 0) {
  422.                 selectMap.put(i, new ArrayList<>(kvMap.values()));
  423.             }
  424.         }
  425.         sheetDataList.add(headList);
  426.         if (isContainExample) {
  427.             sheetDataList.add(exampleList);
  428.         }
  429.         // 导出数据
  430.         export(response, fileName, sheetName, sheetDataList, selectMap);
  431.     }
  432.  
  433.     private static <T> List<ExcelClassField> getExcelClassFieldList(Class<T> clazz) {
  434.         // 解析所有字段
  435.         Field[] fields = clazz.getDeclaredFields();
  436.         boolean hasExportAnnotation = false;
  437.         Map<Integer, List<ExcelClassField>> map = new LinkedHashMap<>();
  438.         List<Integer> sortList = new ArrayList<>();
  439.         for (Field field : fields) {
  440.             ExcelClassField cf = getExcelClassField(field);
  441.             if (cf.getHasAnnotation() == 1) {
  442.                 hasExportAnnotation = true;
  443.             }
  444.             int sort = cf.getSort();
  445.             if (map.containsKey(sort)) {
  446.                 map.get(sort).add(cf);
  447.             } else {
  448.                 List<ExcelClassField> list = new ArrayList<>();
  449.                 list.add(cf);
  450.                 sortList.add(sort);
  451.                 map.put(sort, list);
  452.             }
  453.         }
  454.         Collections.sort(sortList);
  455.         // 获取表头
  456.         List<ExcelClassField> headFieldList = new ArrayList<>();
  457.         if (hasExportAnnotation) {
  458.             for (Integer sort : sortList) {
  459.                 for (ExcelClassField cf : map.get(sort)) {
  460.                     if (cf.getHasAnnotation() == 1) {
  461.                         headFieldList.add(cf);
  462.                     }
  463.                 }
  464.             }
  465.         } else {
  466.             headFieldList.addAll(map.get(0));
  467.         }
  468.         return headFieldList;
  469.     }
  470.  
  471.     private static ExcelClassField getExcelClassField(Field field) {
  472.         ExcelClassField cf = new ExcelClassField();
  473.         String fieldName = field.getName();
  474.         cf.setFieldName(fieldName);
  475.         ExcelExport annotation = field.getAnnotation(ExcelExport.class);
  476.         // 无 ExcelExport 注解情况
  477.         if (annotation == null) {
  478.             cf.setHasAnnotation(0);
  479.             cf.setName(fieldName);
  480.             cf.setSort(0);
  481.             return cf;
  482.         }
  483.         // 有 ExcelExport 注解情况
  484.         cf.setHasAnnotation(1);
  485.         cf.setName(annotation.value());
  486.         String example = getString(annotation.example());
  487.         if (!example.isEmpty()) {
  488.             if (isNumeric(example) && example.length() < 8) {
  489.                 cf.setExample(Double.valueOf(example));
  490.             } else {
  491.                 cf.setExample(example);
  492.             }
  493.         } else {
  494.             cf.setExample("");
  495.         }
  496.         cf.setSort(annotation.sort());
  497.         // 解析映射
  498.         String kv = getString(annotation.kv());
  499.         cf.setKvMap(getKvMap(kv));
  500.         return cf;
  501.     }
  502.  
  503.     private static LinkedHashMap<String, String> getKvMap(String kv) {
  504.         LinkedHashMap<String, String> kvMap = new LinkedHashMap<>();
  505.         if (kv.isEmpty()) {
  506.             return kvMap;
  507.         }
  508.         String[] kvs = kv.split(";");
  509.         if (kvs.length == 0) {
  510.             return kvMap;
  511.         }
  512.         for (String each : kvs) {
  513.             String[] eachKv = getString(each).split("-");
  514.             if (eachKv.length != 2) {
  515.                 continue;
  516.             }
  517.             String k = eachKv[0];
  518.             String v = eachKv[1];
  519.             if (k.isEmpty() || v.isEmpty()) {
  520.                 continue;
  521.             }
  522.             kvMap.put(k, v);
  523.         }
  524.         return kvMap;
  525.     }
  526.  
  527.     /**
  528.      * 导出表格到本地
  529.      *
  530.      * @param file      本地文件对象
  531.      * @param sheetData 导出数据
  532.      */
  533.     public static void exportFile(File file, List<List<Object>> sheetData) {
  534.         if (file == null) {
  535.             System.out.println("文件创建失败");
  536.             return;
  537.         }
  538.         if (sheetData == null) {
  539.             sheetData = new ArrayList<>();
  540.         }
  541.         Map<String, List<List<Object>>> map = new HashMap<>();
  542.         map.put(file.getName(), sheetData);
  543.         export(null, file, file.getName(), map, null);
  544.     }
  545.  
  546.     /**
  547.      * 导出表格到本地
  548.      *
  549.      * @param <T>      导出数据类似，和K类型保持一致
  550.      * @param filePath 文件父路径（如：D:/doc/excel/）
  551.      * @param fileName 文件名称（不带尾缀，如：学生表）
  552.      * @param list     导出数据
  553.      * @throws IOException IO异常
  554.      */
  555.     public static <T> File exportFile(String filePath, String fileName, List<T> list) throws IOException {
  556.         File file = getFile(filePath, fileName);
  557.         List<List<Object>> sheetData = getSheetData(list);
  558.         exportFile(file, sheetData);
  559.         return file;
  560.     }
  561.  
  562.     /**
  563.      * 获取文件
  564.      *
  565.      * @param filePath filePath 文件父路径（如：D:/doc/excel/）
  566.      * @param fileName 文件名称（不带尾缀，如：用户表）
  567.      * @return 本地File文件对象
  568.      */
  569.     private static File getFile(String filePath, String fileName) throws IOException {
  570.         String dirPath = getString(filePath);
  571.         String fileFullPath;
  572.         if (dirPath.isEmpty()) {
  573.             fileFullPath = fileName;
  574.         } else {
  575.             // 判定文件夹是否存在，如果不存在，则级联创建
  576.             File dirFile = new File(dirPath);
  577.             if (!dirFile.exists()) {
  578.                 boolean mkdirs = dirFile.mkdirs();
  579.                 if (!mkdirs) {
  580.                     return null;
  581.                 }
  582.             }
  583.             // 获取文件夹全名
  584.             if (dirPath.endsWith(String.valueOf(LEAN_LINE))) {
  585.                 fileFullPath = dirPath + fileName + XLSX;
  586.             } else {
  587.                 fileFullPath = dirPath + LEAN_LINE + fileName + XLSX;
  588.             }
  589.         }
  590.         System.out.println(fileFullPath);
  591.         File file = new File(fileFullPath);
  592.         if (!file.exists()) {
  593.             boolean result = file.createNewFile();
  594.             if (!result) {
  595.                 return null;
  596.             }
  597.         }
  598.         return file;
  599.     }
  600.  
  601.     private static <T> List<List<Object>> getSheetData(List<T> list) {
  602.         // 获取表头字段
  603.         List<ExcelClassField> excelClassFieldList = getExcelClassFieldList(list.get(0).getClass());
  604.         List<String> headFieldList = new ArrayList<>();
  605.         List<Object> headList = new ArrayList<>();
  606.         Map<String, ExcelClassField> headFieldMap = new HashMap<>();
  607.         for (ExcelClassField each : excelClassFieldList) {
  608.             String fieldName = each.getFieldName();
  609.             headFieldList.add(fieldName);
  610.             headFieldMap.put(fieldName, each);
  611.             headList.add(each.getName());
  612.         }
  613.         // 添加表头名称
  614.         List<List<Object>> sheetDataList = new ArrayList<>();
  615.         sheetDataList.add(headList);
  616.         // 获取表数据
  617.         for (T t : list) {
  618.             Map<String, Object> fieldDataMap = getFieldDataMap(t);
  619.             Set<String> fieldDataKeys = fieldDataMap.keySet();
  620.             List<Object> rowList = new ArrayList<>();
  621.             for (String headField : headFieldList) {
  622.                 if (!fieldDataKeys.contains(headField)) {
  623.                     continue;
  624.                 }
  625.                 Object data = fieldDataMap.get(headField);
  626.                 if (data == null) {
  627.                     rowList.add("");
  628.                     continue;
  629.                 }
  630.                 ExcelClassField cf = headFieldMap.get(headField);
  631.                 // 判断是否有映射关系
  632.                 LinkedHashMap<String, String> kvMap = cf.getKvMap();
  633.                 if (kvMap == null || kvMap.isEmpty()) {
  634.                     rowList.add(data);
  635.                     continue;
  636.                 }
  637.                 String val = kvMap.get(data.toString());
  638.                 if (isNumeric(val)) {
  639.                     rowList.add(Double.valueOf(val));
  640.                 } else {
  641.                     rowList.add(val);
  642.                 }
  643.             }
  644.             sheetDataList.add(rowList);
  645.         }
  646.         return sheetDataList;
  647.     }
  648.  
  649.     private static <T> Map<String, Object> getFieldDataMap(T t) {
  650.         Map<String, Object> map = new HashMap<>();
  651.         Field[] fields = t.getClass().getDeclaredFields();
  652.         try {
  653.             for (Field field : fields) {
  654.                 String fieldName = field.getName();
  655.                 field.setAccessible(true);
  656.                 Object object = field.get(t);
  657.                 map.put(fieldName, object);
  658.             }
  659.         } catch (IllegalArgumentException | IllegalAccessException e) {
  660.             e.printStackTrace();
  661.         }
  662.         return map;
  663.     }
  664.  
  665.     public static void exportEmpty(HttpServletResponse response, String fileName) {
  666.         List<List<Object>> sheetDataList = new ArrayList<>();
  667.         List<Object> headList = new ArrayList<>();
  668.         headList.add("导出无数据");
  669.         sheetDataList.add(headList);
  670.         export(response, fileName, sheetDataList);
  671.     }
  672.  
  673.     public static void export(HttpServletResponse response, String fileName, List<List<Object>> sheetDataList) {
  674.         export(response, fileName, fileName, sheetDataList, null);
  675.     }
  676.  
  677.     public static void exportManySheet(HttpServletResponse response, String fileName, Map<String, List<List<Object>>> sheetMap) {
  678.         export(response, null, fileName, sheetMap, null);
  679.     }
  680.  
  681.  
  682.     public static void export(HttpServletResponse response, String fileName, String sheetName,
  683.                               List<List<Object>> sheetDataList) {
  684.         export(response, fileName, sheetName, sheetDataList, null);
  685.     }
  686.  
  687.     public static void export(HttpServletResponse response, String fileName, String sheetName,
  688.                               List<List<Object>> sheetDataList, Map<Integer, List<String>> selectMap) {
  689.  
  690.         Map<String, List<List<Object>>> map = new HashMap<>();
  691.         map.put(sheetName, sheetDataList);
  692.         export(response, null, fileName, map, selectMap);
  693.     }
  694.  
  695.     public static <T, K> void export(HttpServletResponse response, String fileName, List<T> list, Class<K> template) {
  696.         // list 是否为空
  697.         boolean lisIsEmpty = list == null || list.isEmpty();
  698.         // 如果模板数据为空，且导入的数据为空，则导出空文件
  699.         if (template == null && lisIsEmpty) {
  700.             exportEmpty(response, fileName);
  701.             return;
  702.         }
  703.         // 如果 list 数据，则导出模板数据
  704.         if (lisIsEmpty) {
  705.             exportTemplate(response, fileName, template);
  706.             return;
  707.         }
  708.         // 导出数据
  709.         List<List<Object>> sheetDataList = getSheetData(list);
  710.         export(response, fileName, sheetDataList);
  711.     }
  712.  
  713.     public static void export(HttpServletResponse response, String fileName, List<List<Object>> sheetDataList, Map<Integer, List<String>> selectMap) {
  714.         export(response, fileName, fileName, sheetDataList, selectMap);
  715.     }
  716.  
  717.     private static void export(HttpServletResponse response, File file, String fileName,
  718.                                Map<String, List<List<Object>>> sheetMap, Map<Integer, List<String>> selectMap) {
  719.         // 整个 Excel 表格 book 对象
  720.         SXSSFWorkbook book = new SXSSFWorkbook();
  721.         // 每个 Sheet 页
  722.         Set<Entry<String, List<List<Object>>>> entries = sheetMap.entrySet();
  723.         for (Entry<String, List<List<Object>>> entry : entries) {
  724.             List<List<Object>> sheetDataList = entry.getValue();
  725.             Sheet sheet = book.createSheet(entry.getKey());
  726.             Drawing<?> patriarch = sheet.createDrawingPatriarch();
  727.             // 设置表头背景色（灰色）
  728.             CellStyle headStyle = book.createCellStyle();
  729.             headStyle.setFillForegroundColor(IndexedColors.GREY_80_PERCENT.index);
  730.             headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  731.             headStyle.setAlignment(HorizontalAlignment.CENTER);
  732.             headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
  733.             // 设置表身背景色（默认色）
  734.             CellStyle rowStyle = book.createCellStyle();
  735.             rowStyle.setAlignment(HorizontalAlignment.CENTER);
  736.             rowStyle.setVerticalAlignment(VerticalAlignment.CENTER);
  737.             // 设置表格列宽度（默认为15个字节）
  738.             sheet.setDefaultColumnWidth(15);
  739.             // 创建合并算法数组
  740.             int rowLength = sheetDataList.size();
  741.             int columnLength = sheetDataList.get(0).size();
  742.             int[][] mergeArray = new int[rowLength][columnLength];
  743.             for (int i = 0; i < sheetDataList.size(); i++) {
  744.                 // 每个 Sheet 页中的行数据
  745.                 Row row = sheet.createRow(i);
  746.                 List<Object> rowList = sheetDataList.get(i);
  747.                 for (int j = 0; j < rowList.size(); j++) {
  748.                     // 每个行数据中的单元格数据
  749.                     Object o = rowList.get(j);
  750.                     int v = 0;
  751.                     if (o instanceof URL) {
  752.                         // 如果要导出图片的话, 链接需要传递 URL 对象
  753.                         setCellPicture(book, row, patriarch, i, j, (URL) o);
  754.                     } else {
  755.                         Cell cell = row.createCell(j);
  756.                         if (i == 0) {
  757.                             // 第一行为表头行，采用灰色底背景
  758.                             v = setCellValue(cell, o, headStyle);
  759.                         } else {
  760.                             // 其他行为数据行，默认白底色
  761.                             v = setCellValue(cell, o, rowStyle);
  762.                         }
  763.                     }
  764.                     mergeArray[i][j] = v;
  765.                 }
  766.             }
  767.             // 合并单元格
  768.             mergeCells(sheet, mergeArray);
  769.             // 设置下拉列表
  770.             setSelect(sheet, selectMap);
  771.         }
  772.         // 写数据
  773.         if (response != null) {
  774.             // 前端导出
  775.             try {
  776.                 write(response, book, fileName);
  777.             } catch (IOException e) {
  778.                 e.printStackTrace();
  779.             }
  780.         } else {
  781.             // 本地导出
  782.             FileOutputStream fos;
  783.             try {
  784.                 fos = new FileOutputStream(file);
  785.                 ByteArrayOutputStream ops = new ByteArrayOutputStream();
  786.                 book.write(ops);
  787.                 fos.write(ops.toByteArray());
  788.                 fos.close();
  789.             } catch (Exception e) {
  790.                 e.printStackTrace();
  791.             }
  792.         }
  793.     }
  794.  
  795.     /**
  796.      * 合并当前Sheet页的单元格
  797.      *
  798.      * @param sheet      当前 sheet 页
  799.      * @param mergeArray 合并单元格算法
  800.      */
  801.     private static void mergeCells(Sheet sheet, int[][] mergeArray) {
  802.         // 横向合并
  803.         for (int x = 0; x < mergeArray.length; x++) {
  804.             int[] arr = mergeArray[x];
  805.             boolean merge = false;
  806.             int y1 = 0;
  807.             int y2 = 0;
  808.             for (int y = 0; y < arr.length; y++) {
  809.                 int value = arr[y];
  810.                 if (value == CELL_COLUMN_MERGE) {
  811.                     if (!merge) {
  812.                         y1 = y;
  813.                     }
  814.                     y2 = y;
  815.                     merge = true;
  816.                 } else {
  817.                     merge = false;
  818.                     if (y1 > 0) {
  819.                         sheet.addMergedRegion(new CellRangeAddress(x, x, (y1 - 1), y2));
  820.                     }
  821.                     y1 = 0;
  822.                     y2 = 0;
  823.                 }
  824.             }
  825.             if (y1 > 0) {
  826.                 sheet.addMergedRegion(new CellRangeAddress(x, x, (y1 - 1), y2));
  827.             }
  828.         }
  829.         // 纵向合并
  830.         int xLen = mergeArray.length;
  831.         int yLen = mergeArray[0].length;
  832.         for (int y = 0; y < yLen; y++) {
  833.             boolean merge = false;
  834.             int x1 = 0;
  835.             int x2 = 0;
  836.             for (int x = 0; x < xLen; x++) {
  837.                 int value = mergeArray[x][y];
  838.                 if (value == CELL_ROW_MERGE) {
  839.                     if (!merge) {
  840.                         x1 = x;
  841.                     }
  842.                     x2 = x;
  843.                     merge = true;
  844.                 } else {
  845.                     merge = false;
  846.                     if (x1 > 0) {
  847.                         sheet.addMergedRegion(new CellRangeAddress((x1 - 1), x2, y, y));
  848.                     }
  849.                     x1 = 0;
  850.                     x2 = 0;
  851.                 }
  852.             }
  853.             if (x1 > 0) {
  854.                 sheet.addMergedRegion(new CellRangeAddress((x1 - 1), x2, y, y));
  855.             }
  856.         }
  857.     }
  858.  
  859.     private static void write(HttpServletResponse response, SXSSFWorkbook book, String fileName) throws IOException {
  860.         response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
  861.         response.setCharacterEncoding("utf-8");
  862.         String name = new String(fileName.getBytes("GBK"), "ISO8859_1") + XLSX;
  863.         response.addHeader("Content-Disposition", "attachment;filename=" + name);
  864.         ServletOutputStream out = response.getOutputStream();
  865.         book.write(out);
  866.         out.flush();
  867.         out.close();
  868.     }
  869.  
  870.     private static int setCellValue(Cell cell, Object o, CellStyle style) {
  871.         // 设置样式
  872.         cell.setCellStyle(style);
  873.         // 数据为空时
  874.         if (o == null) {
  875.             cell.setCellType(CellType.STRING);
  876.             cell.setCellValue("");
  877.             return CELL_OTHER;
  878.         }
  879.         // 是否为字符串
  880.         if (o instanceof String) {
  881.             String s = o.toString();
  882.             // 当数字类型长度超过8位时，改为字符串类型显示（Excel数字超过一定长度会显示为科学计数法）
  883.             if (isNumeric(s) && s.length() < 8) {
  884.                 cell.setCellType(CellType.NUMERIC);
  885.                 cell.setCellValue(Double.parseDouble(s));
  886.                 return CELL_OTHER;
  887.             } else {
  888.                 cell.setCellType(CellType.STRING);
  889.                 cell.setCellValue(s);
  890.             }
  891.             if (s.equals(ROW_MERGE)) {
  892.                 return CELL_ROW_MERGE;
  893.             } else if (s.equals(COLUMN_MERGE)) {
  894.                 return CELL_COLUMN_MERGE;
  895.             } else {
  896.                 return CELL_OTHER;
  897.             }
  898.         }
  899.         // 是否为字符串
  900.         if (o instanceof Integer || o instanceof Long || o instanceof Double || o instanceof Float) {
  901.             cell.setCellType(CellType.NUMERIC);
  902.             cell.setCellValue(Double.parseDouble(o.toString()));
  903.             return CELL_OTHER;
  904.         }
  905.         // 是否为Boolean
  906.         if (o instanceof Boolean) {
  907.             cell.setCellType(CellType.BOOLEAN);
  908.             cell.setCellValue((Boolean) o);
  909.             return CELL_OTHER;
  910.         }
  911.         // 如果是BigDecimal，则默认3位小数
  912.         if (o instanceof BigDecimal) {
  913.             cell.setCellType(CellType.NUMERIC);
  914.             cell.setCellValue(((BigDecimal) o).setScale(3, RoundingMode.HALF_UP).doubleValue());
  915.             return CELL_OTHER;
  916.         }
  917.         // 如果是Date数据，则显示格式化数据
  918.         if (o instanceof Date) {
  919.             cell.setCellType(CellType.STRING);
  920.             cell.setCellValue(formatDate((Date) o));
  921.             return CELL_OTHER;
  922.         }
  923.         // 如果是其他，则默认字符串类型
  924.         cell.setCellType(CellType.STRING);
  925.         cell.setCellValue(o.toString());
  926.         return CELL_OTHER;
  927.     }
  928.  
  929.     private static void setCellPicture(SXSSFWorkbook wb, Row sr, Drawing<?> patriarch, int x, int y, URL url) {
  930.         // 设置图片宽高
  931.         sr.setHeight((short) (IMG_WIDTH * IMG_HEIGHT));
  932.         // （jdk1.7版本try中定义流可自动关闭）
  933.         try (InputStream is = url.openStream(); ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
  934.             byte[] buff = new byte[BYTES_DEFAULT_LENGTH];
  935.             int rc;
  936.             while ((rc = is.read(buff, 0, BYTES_DEFAULT_LENGTH)) > 0) {
  937.                 outputStream.write(buff, 0, rc);
  938.             }
  939.             // 设置图片位置
  940.             XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, y, x, y + 1, x + 1);
  941.             // 设置这个，图片会自动填满单元格的长宽
  942.             anchor.setAnchorType(AnchorType.MOVE_AND_RESIZE);
  943.             patriarch.createPicture(anchor, wb.addPicture(outputStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
  944.         } catch (Exception e) {
  945.             e.printStackTrace();
  946.         }
  947.     }
  948.  
  949.     private static String formatDate(Date date) {
  950.         if (date == null) {
  951.             return "";
  952.         }
  953.         SimpleDateFormat format = new SimpleDateFormat(DATE_FORMAT);
  954.         return format.format(date);
  955.     }
  956.  
  957.     private static void setSelect(Sheet sheet, Map<Integer, List<String>> selectMap) {
  958.         if (selectMap == null || selectMap.isEmpty()) {
  959.             return;
  960.         }
  961.         Set<Entry<Integer, List<String>>> entrySet = selectMap.entrySet();
  962.         for (Entry<Integer, List<String>> entry : entrySet) {
  963.             int y = entry.getKey();
  964.             List<String> list = entry.getValue();
  965.             if (list == null || list.isEmpty()) {
  966.                 continue;
  967.             }
  968.             String[] arr = new String[list.size()];
  969.             for (int i = 0; i < list.size(); i++) {
  970.                 arr[i] = list.get(i);
  971.             }
  972.             DataValidationHelper helper = sheet.getDataValidationHelper();
  973.             CellRangeAddressList addressList = new CellRangeAddressList(1, 65000, y, y);
  974.             DataValidationConstraint dvc = helper.createExplicitListConstraint(arr);
  975.             DataValidation dv = helper.createValidation(dvc, addressList);
  976.             if (dv instanceof HSSFDataValidation) {
  977.                 dv.setSuppressDropDownArrow(false);
  978.             } else {
  979.                 dv.setSuppressDropDownArrow(true);
  980.                 dv.setShowErrorBox(true);
  981.             }
  982.             sheet.addValidationData(dv);
  983.         }
  984.     }
  985.  
  986.     private static boolean isNumeric(String str) {
  987.         if (Objects.nonNull(str) && "0.0".equals(str)) {
  988.             return true;
  989.         }
  990.         for (int i = str.length(); --i >= 0; ) {
  991.             if (!Character.isDigit(str.charAt(i))) {
  992.                 return false;
  993.             }
  994.         }
  995.         return true;
  996.     }
  997.  
  998.     private static String getString(String s) {
  999.         if (s == null) {
  1000.             return "";
  1001.         }
  1002.         if (s.isEmpty()) {
  1003.             return s;
  1004.         }
  1005.         return s.trim();
  1006.     }
  1007.  
  1008. }
ExcelImport
  1. package com.ruoyi.expo.controller;
  2.  
  3.  
  4. import java.lang.annotation.ElementType;
  5. import java.lang.annotation.Retention;
  6. import java.lang.annotation.RetentionPolicy;
  7. import java.lang.annotation.Target;
  8.  
  9. /**
  10.  * @author: cyf
  11.  * @Date: 2023/3/16 15:55
  12.  */
  13.  
  14. @Target(ElementType.FIELD)
  15. @Retention(RetentionPolicy.RUNTIME)
  16. public @interface ExcelImport {
  17.  
  18.     /** 字段名称 */
  19.     String value();
  20.  
  21.     /** 导出映射，格式如：0-未知;1-男;2-女 */
  22.     String kv() default "";
  23.  
  24.     /** 是否为必填字段（默认为非必填） */
  25.     boolean required() default false;
  26.  
  27.     /** 最大长度（默认255） */
  28.     int maxLength() default 255;
  29.  
  30.     /** 导入唯一性验证（多个字段则取联合验证） */
  31.     boolean unique() default false;
  32.  
  33. }
ExcelExport
  1. package com.ruoyi.expo.controller;
  2.  
  3.  
  4. import java.lang.annotation.ElementType;
  5. import java.lang.annotation.Retention;
  6. import java.lang.annotation.RetentionPolicy;
  7. import java.lang.annotation.Target;
  8.  
  9. /**
  10.  * @author: cyf
  11.  * @Date: 2023/3/16 15:56
  12.  */
  13. @Target(ElementType.FIELD)
  14. @Retention(RetentionPolicy.RUNTIME)
  15. public @interface ExcelExport {
  16.  
  17.     /** 字段名称 */
  18.     String value();
  19.  
  20.     /** 导出排序先后: 数字越小越靠前（默认按Java类字段顺序导出） */
  21.     int sort() default 0;
  22.  
  23.     /** 导出映射，格式如：0-未知;1-男;2-女 */
  24.     String kv() default "";
  25.  
  26.     /** 导出模板示例值（有值的话，直接取该值，不做映射） */
  27.     String example() default "";
  28.  
  29. }
ExcelClassField
  1. package com.ruoyi.expo.controller;
  2.  
  3.  
  4. import java.util.LinkedHashMap;
  5.  
  6. /**
  7.  * @author: cyf
  8.  * @Date: 2023/3/16 15:55
  9.  */
  10. public class ExcelClassField {
  11.  
  12.     /** 字段名称 */
  13.     private String fieldName;
  14.  
  15.     /** 表头名称 */
  16.     private String name;
  17.  
  18.     /** 映射关系 */
  19.     private LinkedHashMap<String, String> kvMap;
  20.  
  21.     /** 示例值 */
  22.     private Object example;
  23.  
  24.     /** 排序 */
  25.     private int sort;
  26.  
  27.     /** 是否为注解字段：0-否，1-是 */
  28.     private int hasAnnotation;
  29.  
  30.     public String getFieldName() {
  31.         return fieldName;
  32.     }
  33.  
  34.     public void setFieldName(String fieldName) {
  35.         this.fieldName = fieldName;
  36.     }
  37.  
  38.     public String getName() {
  39.         return name;
  40.     }
  41.  
  42.     public void setName(String name) {
  43.         this.name = name;
  44.     }
  45.  
  46.     public LinkedHashMap<String, String> getKvMap() {
  47.         return kvMap;
  48.     }
  49.  
  50.     public void setKvMap(LinkedHashMap<String, String> kvMap) {
  51.         this.kvMap = kvMap;
  52.     }
  53.  
  54.     public Object getExample() {
  55.         return example;
  56.     }
  57.  
  58.     public void setExample(Object example) {
  59.         this.example = example;
  60.     }
  61.  
  62.     public int getSort() {
  63.         return sort;
  64.     }
  65.  
  66.     public void setSort(int sort) {
  67.         this.sort = sort;
  68.     }
  69.  
  70.     public int getHasAnnotation() {
  71.         return hasAnnotation;
  72.     }
  73.  
  74.     public void setHasAnnotation(int hasAnnotation) {
  75.         this.hasAnnotation = hasAnnotation;
  76.     }
  77.  
  78. }
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/我家小花儿/article/detail/273049
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号