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

springboot导出excel（easyexcel和poi 列下拉及表格锁定）_java easypoi 锁定列

作者：知新_RL | 2024-03-01 02:27:04

赞

踩

java easypoi 锁定列

最近做的项目导入的数据量比较大，直接用poi或者easypoi会可能会出现OOM的情况，综合考虑下用easyexcel，

pom引入所需包

  1.         <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
  2.         <dependency>
  3.             <groupId>com.alibaba</groupId>
  4.             <artifactId>easyexcel</artifactId>
  5.             <version>1.1.2-beta5</version>
  6.         </dependency>

excelUtil.java

  1. import com.alibaba.excel.EasyExcelFactory;
  2. import com.alibaba.excel.ExcelReader;
  3. import com.alibaba.excel.ExcelWriter;
  4. import com.alibaba.excel.metadata.BaseRowModel;
  5. import com.alibaba.excel.metadata.Sheet;
  6. import com.alibaba.excel.support.ExcelTypeEnum;
  7. import com.alibaba.excel.util.CollectionUtils;
  8. import com.cmbchina.ccd.oa.socialsecurity.model.bo.ExcelListener;
  9. import org.springframework.stereotype.Component;
  10. import org.springframework.web.multipart.MultipartFile;
  11.  
  12. import javax.servlet.ServletOutputStream;
  13. import javax.servlet.http.HttpServletResponse;
  14. import java.io.BufferedInputStream;
  15. import java.io.FileNotFoundException;
  16. import java.io.FileOutputStream;
  17. import java.io.IOException;
  18. import java.io.InputStream;
  19. import java.io.OutputStream;
  20. import java.util.ArrayList;
  21. import java.util.Collections;
  22. import java.util.HashMap;
  23. import java.util.List;
  24. import java.util.Map;
  25.  
  26. /**
  27.  * @description: excel工具类
  28.  * @author: kongwc
  29.  * @create: 2019-09-23
  30.  */
  31. @Component
  32. public class ExcelUtil {
  33.     private static Sheet initSheet;
  34.  
  35.     static {
  36.         initSheet = new Sheet(1, 0);
  37.         initSheet.setSheetName("sheet");
  38.         //设置自适应宽度
  39.         initSheet.setAutoWidth(Boolean.TRUE);
  40.     }
  41.  
  42.     /**
  43.      * 读取少于1000行数据
  44.      * 数据量少时，同步读取
  45.      *
  46.      * @param file 读取的文件
  47.      * @return
  48.      */
  49.     public List<Object> readLessThan1000Row(MultipartFile file) throws IOException {
  50.         return readLessThan1000RowBySheet(file, null);
  51.     }
  52.  
  53.     /**
  54.      * 读小于1000行数据
  55.      * filePath 文件绝对路径
  56.      * initSheet ：
  57.      * sheetNo: sheet页码，默认为1
  58.      * headLineMun: 从第几行开始读取数据，默认为0, 表示从第一行开始读取
  59.      * clazz: 返回数据List<Object> 中Object的类名
  60.      */
  61.     public List<Object> readLessThan1000RowBySheet(MultipartFile file, Sheet sheet) throws IOException {
  62.         if (file == null) {
  63.             //log.info("导入文件为空", file);
  64.             return null;
  65.         }
  66.         sheet = sheet != null ? sheet : initSheet;
  67.         InputStream fileStream = null;
  68.         try {
  69.             fileStream = file.getInputStream();
  70.             return EasyExcelFactory.read(fileStream, sheet);
  71.         } catch (FileNotFoundException e) {
  72.             //log.info("文件有误, 文件：{}", file);
  73.         } finally {
  74.             try {
  75.                 if (fileStream != null) {
  76.                     fileStream.close();
  77.                 }
  78.             } catch (IOException e) {
  79.                 //log.info("excel文件读取失败, 失败原因：{}", e);
  80.             }
  81.         }
  82.         return null;
  83.     }
  84.  
  85.     /**
  86.      * 生成excle
  87.      *
  88.      * @param filePath 绝对路径, 如：/home/Downloads/aaa.xlsx
  89.      * @param data     数据源
  90.      * @param head     表头
  91.      */
  92.     public static void writeBySimple(String filePath, List<List<Object>> data, List<String> head) {
  93.         writeSimpleBySheet(filePath, data, head, null);
  94.     }
  95.  
  96.     /**
  97.      * 生成excle
  98.      *
  99.      * @param filePath 绝对路径
  100.      * @param data     数据源
  101.      * @param sheet    excle页面样式
  102.      * @param head     表头
  103.      */
  104.     public static void writeSimpleBySheet(String filePath, List<List<Object>> data, List<String> head, Sheet sheet) {
  105.         sheet = (sheet != null) ? sheet : initSheet;
  106.  
  107.         if (head != null) {
  108.             List<List<String>> list = new ArrayList<>();
  109.             head.forEach(h -> list.add(Collections.singletonList(h)));
  110.             sheet.setHead(list);
  111.         }
  112.  
  113.         OutputStream outputStream = null;
  114.         ExcelWriter writer = null;
  115.         try {
  116.             outputStream = new FileOutputStream(filePath);
  117.             writer = EasyExcelFactory.getWriter(outputStream);
  118.             writer.write1(data, sheet);
  119.         } catch (FileNotFoundException e) {
  120.             //log.error("找不到文件或文件路径错误, 文件：{}", filePath);
  121.         } finally {
  122.             try {
  123.                 if (writer != null) {
  124.                     writer.finish();
  125.                 }
  126.                 if (outputStream != null) {
  127.                     outputStream.close();
  128.                 }
  129.  
  130.             } catch (IOException e) {
  131.                 //log.error("excel文件导出失败");
  132.             }
  133.         }
  134.  
  135.     }
  136.  
  137.     /**
  138.      * @Description 导出excel 支持一张表导出多个sheet
  139.      * @Param OutputStream 输出流
  140.      * Map<String, List>  sheetName和每个sheet的数据
  141.      * ExcelTypeEnum 要导出的excel的类型 有ExcelTypeEnum.xls 和有ExcelTypeEnum.xlsx
  142.      */
  143.     public void createExcel(HttpServletResponse response, Map<String, List<? extends BaseRowModel>> sheetNameAndDateList, ExcelTypeEnum type, String fileName) {
  144.         // if (checkParam(SheetNameAndDateList, type)) return;
  145.         try {
  146.             response.setContentType("multipart/form-data");
  147.             response.setCharacterEncoding("utf-8");
  148.             //解决导出文件名中文乱码
  149.             fileName = new String(fileName.getBytes(), "iso8859-1") + DateUtil.todayStr();
  150.             response.setHeader("Content-disposition", "attachment;filename=" + fileName + type.getValue());
  151.             ServletOutputStream out = response.getOutputStream();
  152.             ExcelWriter writer = new ExcelWriter(out, type, true);
  153.             setSheet(sheetNameAndDateList, writer);
  154.             writer.finish();
  155.             out.flush();
  156.         } catch (IOException e) {
  157.             e.printStackTrace();
  158.         }
  159.  
  160.     }
  161.  
  162.  
  163.     /**
  164.      * @Description //setSheet数据
  165.      */
  166.     private void setSheet(Map<String, List<? extends BaseRowModel>> SheetNameAndDateList, ExcelWriter writer) {
  167.         int sheetNum = 1;
  168.         for (Map.Entry<String, List<? extends BaseRowModel>> stringListEntry : SheetNameAndDateList.entrySet()) {
  169.             Sheet sheet = new Sheet(sheetNum, 0, stringListEntry.getValue().get(0).getClass());
  170.             sheet.setSheetName(stringListEntry.getKey());
  171.             writer.write(stringListEntry.getValue(), sheet);
  172.             sheetNum++;
  173.         }
  174.     }
  175.  
  176.     /**
  177.      * @Description 校验参数
  178.      */
  179.     private static boolean checkParam(Map<String, List<? extends BaseRowModel>> SheetNameAndDateList, ExcelTypeEnum type) {
  180.         if (CollectionUtils.isEmpty(SheetNameAndDateList)) {
  181.             //log.error("SheetNameAndDateList不能为空");
  182.             return true;
  183.         } else if (type == null) {
  184.             //log.error("导出的excel类型不能为空");
  185.             return true;
  186.         }
  187.         return false;
  188.     }
  189.  
  190.     /**
  191.      * 读取某个 sheet 的 Excel
  192.      *
  193.      * @param excel    文件
  194.      * @param rowModel 实体类映射，继承 BaseRowModel 类
  195.      * @return Excel 数据 list
  196.      */
  197.     public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel) throws IOException {
  198.         return readExcel(excel, rowModel, 1, 1);
  199.     }
  200.  
  201.     /**
  202.      * 读取某个 sheet 的 Excel
  203.      *
  204.      * @param excel    文件
  205.      * @param rowModel 实体类映射，继承 BaseRowModel 类
  206.      * @param sheetNo  sheet 的序号 从1开始
  207.      * @return Excel 数据 list
  208.      */
  209.     public Map<String, Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo) throws IOException {
  210.         Map<String, Object> result = new HashMap<>();
  211.         ExcelListener excelListener = new ExcelListener();
  212.         ExcelReader reader = getReader(excel, excelListener);
  213.         if (reader == null) {
  214.             return null;
  215.         }
  216.         reader.read(new Sheet(sheetNo, 0, rowModel.getClass()));
  217.         //校验表头
  218.         Boolean flag = false;
  219.         //维护实体类中 没有@ExcelProperty 放置在最后，会被映射出null表头
  220.         String head = excelListener.getImportHeads().replace("null,", "");
  221.         if (head.equals(excelListener.getModelHeads())) {
  222.             flag = true;
  223.         }
  224.         result.put("flag", flag);
  225.         result.put("datas", excelListener.getDatas());
  226.         return result;
  227.     }
  228.  
  229.     /**
  230.      * 读取某个 sheet 的 Excel
  231.      *
  232.      * @param excel       文件
  233.      * @param rowModel    实体类映射，继承 BaseRowModel 类
  234.      * @param sheetNo     sheet 的序号 从1开始
  235.      * @param headLineNum 表头行数，默认为1
  236.      * @return Excel 数据 list
  237.      */
  238.     public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo, int headLineNum) throws IOException {
  239.         ExcelListener excelListener = new ExcelListener();
  240.         ExcelReader reader = getReader(excel, excelListener);
  241.         if (reader == null) {
  242.             return null;
  243.         }
  244.         reader.read(new Sheet(sheetNo, headLineNum, rowModel.getClass()));
  245.         return excelListener.getDatas();
  246.     }
  247.  
  248.     /**
  249.      * 读取指定sheetName的Excel(多个 sheet)
  250.      *
  251.      * @param excel    文件
  252.      * @param rowModel 实体类映射，继承 BaseRowModel 类
  253.      * @return Excel 数据 list
  254.      * @throws IOException
  255.      */
  256.     public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, String sheetName) throws IOException {
  257.         ExcelListener excelListener = new ExcelListener();
  258.         ExcelReader reader = getReader(excel, excelListener);
  259.         if (reader == null) {
  260.             return null;
  261.         }
  262.         for (Sheet sheet : reader.getSheets()) {
  263.             if (rowModel != null) {
  264.                 sheet.setClazz(rowModel.getClass());
  265.             }
  266.             //读取指定名称的sheet
  267.             if (sheet.getSheetName().contains(sheetName)) {
  268.                 reader.read(sheet);
  269.                 break;
  270.             }
  271.         }
  272.         return excelListener.getDatas();
  273.     }
  274.  
  275.     /**
  276.      * 返回 ExcelReader
  277.      *
  278.      * @param excel         需要解析的 Excel 文件
  279.      * @param excelListener new ExcelListener()
  280.      * @throws IOException
  281.      */
  282.     private static ExcelReader getReader(MultipartFile excel, ExcelListener excelListener) throws IOException {
  283.         String filename = excel.getOriginalFilename();
  284.         if (filename != null && (filename.toLowerCase().endsWith(".xls") || filename.toLowerCase().endsWith(".xlsx"))) {
  285.             InputStream is = new BufferedInputStream(excel.getInputStream());
  286.             return new ExcelReader(is, null, excelListener, false);
  287.         } else {
  288.             return null;
  289.         }
  290.     }
  291. }

但是easyexcel导出的功能还不丰富，比如带下拉选择的时候，还是需要原生的poi才行，所以决定导入数据量大的时候用easyexcel，导出时就用poi导出，由于easyexcel已引入poi，所以如果引入poi的时候要注意版本一致

pom文件

  1. <!--poi相关 -->
  2.         <dependency>
  3.             <groupId>org.apache.poi</groupId>
  4.             <artifactId>poi-ooxml</artifactId>
  5.             <version>3.17</version>
  6.         </dependency>
  7.         <dependency>
  8.             <groupId>org.apache.poi</groupId>
  9.             <artifactId>poi</artifactId>
  10.             <version>3.17</version>
  11.         </dependency>
  12.         <dependency>
  13.             <groupId>org.apache.poi</groupId>
  14.             <artifactId>poi-scratchpad</artifactId>
  15.             <version>3.17</version>
  16.         </dependency>

PoiExcelUtil.java

  1. import com.alibaba.excel.util.StyleUtil;
  2. import com.cmbchina.ccd.oa.socialsecurity.model.bo.excel.ColumnName;
  3. import com.cmbchina.ccd.oa.socialsecurity.model.bo.excel.SelectData;
  4. import org.apache.commons.lang3.StringUtils;
  5. import org.apache.http.message.BasicNameValuePair;
  6. import org.apache.poi.hssf.util.HSSFColor;
  7. import org.apache.poi.ss.util.CellRangeAddressList;
  8. import org.apache.poi.xssf.usermodel.XSSFCell;
  9. import org.apache.poi.xssf.usermodel.XSSFCellStyle;
  10. import org.apache.poi.xssf.usermodel.XSSFDataValidation;
  11. import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
  12. import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
  13. import org.apache.poi.xssf.usermodel.XSSFFont;
  14. import org.apache.poi.xssf.usermodel.XSSFRow;
  15. import org.apache.poi.xssf.usermodel.XSSFSheet;
  16. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  17. import org.springframework.stereotype.Component;
  18.  
  19. import java.io.IOException;
  20. import java.io.InputStream;
  21. import java.lang.reflect.Field;
  22. import java.lang.reflect.Method;
  23. import java.text.DecimalFormat;
  24. import java.text.ParseException;
  25. import java.text.SimpleDateFormat;
  26. import java.util.ArrayList;
  27. import java.util.Date;
  28. import java.util.LinkedHashMap;
  29. import java.util.List;
  30. import java.util.Map;
  31.  
  32. /**
  33.  * Excel文件相关操作工具类
  34.  *
  35.  * @author kongwc
  36.  * @create 2018-05-09 18:23
  37.  */
  38.  
  39. @Component
  40. public class PoiExcelUtil<T> {
  41.     private static final String EXCEL_TEMPLATE = "classpath:/fileTemplate/EmailNoticeExcelTemplate.xlsx";
  42.     private static final String CANCEL_ORDER_EXCEL_TEMPLATE = "classpath:/fileTemplate/CancelOrder.xlsx";
  43.  
  44.     /**
  45.      * 没有定义转换时间格式
  46.      *
  47.      * @param clazz
  48.      * @param stream
  49.      * @return
  50.      * @throws NoSuchFieldException
  51.      * @throws InstantiationException
  52.      * @throws IllegalAccessException
  53.      * @throws ParseException
  54.      * @throws IOException
  55.      */
  56.     public List<T> excelToList(Class<T> clazz, InputStream stream)
  57.             throws NoSuchFieldException, InstantiationException, IllegalAccessException, ParseException, IOException {
  58.         return excelToListWithDateForMate(clazz, stream, null);
  59.     }
  60.  
  61.     /**
  62.      * 定义转换时间格式
  63.      *
  64.      * @param clazz
  65.      * @param stream
  66.      * @param dateFormat
  67.      * @return
  68.      * @throws NoSuchFieldException
  69.      * @throws InstantiationException
  70.      * @throws IllegalAccessException
  71.      * @throws ParseException
  72.      * @throws IOException
  73.      */
  74.     public List<T> excelToList(Class<T> clazz, InputStream stream, String dateFormat)
  75.             throws NoSuchFieldException, InstantiationException, IllegalAccessException, ParseException, IOException {
  76.         return excelToListWithDateForMate(clazz, stream, dateFormat);
  77.     }
  78.  
  79.     /**
  80.      * 将文件流转成列表
  81.      *
  82.      * @param clazz  泛型类型
  83.      * @param stream 文件流
  84.      * @return 列表
  85.      */
  86.     public List<T> excelToListWithDateForMate(Class<T> clazz, InputStream stream, String dateFormat)
  87.             throws IOException, IllegalAccessException, InstantiationException, NoSuchFieldException, ParseException,
  88.             ParseException {
  89.         if (dateFormat == null) {
  90.             dateFormat = "yyyy-MM-dd HH:mm:ss";
  91.         }
  92.         if (stream == null) {
  93.             return new ArrayList<>();
  94.         }
  95.  
  96.         XSSFSheet xssfSheet = new XSSFWorkbook(stream).getSheetAt(0);
  97.  
  98.         //开始、结束行号
  99.         int rowStart = xssfSheet.getFirstRowNum();
  100.         int rowEnd = xssfSheet.getLastRowNum();
  101.  
  102.         XSSFRow columnNames = xssfSheet.getRow(rowStart);
  103.         //列的开始，结束
  104.         int cellStart = columnNames.getFirstCellNum();
  105.         int cellEnd = columnNames.getLastCellNum();
  106.  
  107.         List<BasicNameValuePair> columnValues = new ArrayList<>();
  108.  
  109.         Field[] fields = clazz.getDeclaredFields();
  110.         for (int k = cellStart; k < cellEnd; k++) {
  111.             for (Field fieldItem : fields) {
  112.                 ColumnName cn = fieldItem.getAnnotation(ColumnName.class);
  113.                 if (cn != null) {
  114.                     String columnChineseName = fieldItem.getAnnotation(ColumnName.class).value();
  115.                     if (columnNames.getCell(k).getStringCellValue().equals(columnChineseName)) {
  116.                         columnValues.add(new BasicNameValuePair(fieldItem.getName(),
  117.                                 fieldItem.getGenericType().toString()));
  118.                         break;
  119.                     }
  120.                 }
  121.             }
  122.             //导入的列与实际列不一致
  123.             if (columnValues.size() <= k - cellStart) {
  124.                 return null;
  125.             }
  126.         }
  127.  
  128.         List<T> result = new ArrayList<>();
  129.         for (int i = rowStart + 1; i <= rowEnd; i++) {
  130.             T t = clazz.newInstance();
  131.             XSSFRow row = xssfSheet.getRow(i);
  132.             //不为空行
  133.             if (row != null) {
  134.                 for (int k = cellStart; k < cellEnd; k++) {
  135.                     XSSFCell cell = row.getCell(k);
  136.                     Field field = clazz.getDeclaredField(columnValues.get(k - cellStart).getName());
  137.                     field.setAccessible(true);
  138.                     switch (columnValues.get(k - cellStart).getValue()) {
  139.                         case "class java.lang.String":
  140.                             field.set(t, getCellValue(cell));
  141.                             break;
  142.                         case "class java.lang.Integer":
  143.                             field.set(t, (int) cell.getNumericCellValue());
  144.                             break;
  145.                         case "int":
  146.                             field.set(t, (int) cell.getNumericCellValue());
  147.                             break;
  148.                         case "class java.lang.Boolean":
  149.                             if("是".equals(getCellValue(cell))){
  150.                                 field.set(t, true);
  151.                             }
  152.                             else{
  153.                                 field.set(t, false);
  154.                             }
  155.                             break;
  156.  
  157.                         case "class java.util.Date":
  158.                             SimpleDateFormat format = new SimpleDateFormat(dateFormat);
  159.                             if (cell != null) {
  160.                                 if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
  161.                                     String dateStr = format.format(cell.getDateCellValue());
  162.                                     field.set(t, format.parse(dateStr));
  163.                                 } else {
  164.                                     if (StringUtils.isBlank(cell.getStringCellValue())) {
  165.                                         field.set(t, null);
  166.                                     } else {
  167.  
  168.                                         field.set(t, format.parse(cell.getStringCellValue()));
  169.                                     }
  170.                                 }
  171.                             }
  172.                             break;
  173.                         default:
  174.                             break;
  175.                     }
  176.                 }
  177.             }
  178.             result.add(t);
  179.         }
  180.         return result;
  181.     }
  182.  
  183.     /**
  184.      * 获取单元格内容
  185.      *
  186.      * @param cell 单元格
  187.      * @return 单元格的值
  188.      */
  189.     private Object getCellValue(XSSFCell cell) {
  190.         Object value = null;
  191.         if (cell != null) {
  192.             switch (cell.getCellType()) {
  193.                 case XSSFCell.CELL_TYPE_STRING:
  194.                     value = cell.getStringCellValue();
  195.                     break;
  196.                 case XSSFCell.CELL_TYPE_NUMERIC:
  197.                     DecimalFormat df = new DecimalFormat("0");
  198.                     value = df.format(cell.getNumericCellValue()) + "";
  199.                     break;
  200.                 default:
  201.             }
  202.         }
  203.  
  204.         return value;
  205.     }
  206.  
  207.     /**
  208.      * 将列表转成文件流
  209.      *
  210.      * @param clazz
  211.      * @param ts
  212.      * @return
  213.      * @throws Exception
  214.      */
  215.     public XSSFWorkbook listToExcel(Class<T> clazz, List<T> ts) throws Exception {
  216.         return this.listToExcel(clazz, ts, null);
  217.     }
  218.  
  219.     /**
  220.      * 将列表转成文件流
  221.      *
  222.      * @param clazz
  223.      * @param ts
  224.      * @return
  225.      * @throws Exception
  226.      */
  227.     public XSSFWorkbook listToExcel(Class<T> clazz, List<T> ts, String dataFormat) throws Exception {
  228.         return this.listToExcel(clazz, ts, null, dataFormat);
  229.     }
  230.  
  231.     /**
  232.      * 将列表转成文件流
  233.      *
  234.      * @param clazz
  235.      * @param ts
  236.      * @param sheetName
  237.      * @return
  238.      * @throws Exception
  239.      */
  240.     public XSSFWorkbook listToExcel(Class<T> clazz, List<T> ts, String sheetName, String dateFormat) throws Exception {
  241.         if (dateFormat == null) {
  242.             dateFormat = "yyyy-MM-dd HH:mm";
  243.         }
  244.         XSSFWorkbook workbook = new XSSFWorkbook();
  245.         this.createSheet(workbook, clazz, ts, (StringUtils.isBlank(sheetName)) ? "Sheet1" : sheetName, dateFormat, null, null);
  246.  
  247.         return workbook;
  248.     }
  249.  
  250.     /**
  251.      * 将列表转成文件流
  252.      *
  253.      * @param clazz
  254.      * @param ts
  255.      * @param sheetName
  256.      * @return
  257.      * @throws Exception
  258.      */
  259.     public XSSFWorkbook listToExcel(Class<T> clazz, List<T> ts, String sheetName, String dateFormat, SelectData selectData, List<Integer> lockedCols) throws Exception {
  260.         if (dateFormat == null) {
  261.             dateFormat = "yyyy-MM-dd HH:mm";
  262.         }
  263.         XSSFWorkbook workbook = new XSSFWorkbook();
  264.         this.createSheet(workbook, clazz, ts, (StringUtils.isBlank(sheetName)) ? "Sheet1" : sheetName, dateFormat, selectData, lockedCols);
  265.  
  266.         return workbook;
  267.     }
  268.  
  269.     /**
  270.      * @param clazz
  271.      * @param headerMap 表头字段属性集合 key 字段 value 字段含义(需有序)
  272.      * @param ts
  273.      * @param sheetName
  274.      * @return
  275.      * @throws Exception
  276.      */
  277.     public XSSFWorkbook listToExcelByMap(Class<T> clazz, Map<String, String> headerMap, List<T> ts, String sheetName)
  278.             throws Exception {
  279.         XSSFWorkbook workbook = new XSSFWorkbook();
  280.         this.createSheetByMap(workbook, headerMap, ts, clazz, (StringUtils.isBlank(sheetName)) ? "Sheet1" : sheetName);
  281.         return workbook;
  282.     }
  283.  
  284.  
  285.     /**
  286.      * 创建Excel的Sheet
  287.      *
  288.      * @param workbook
  289.      * @param clazz
  290.      * @param data
  291.      * @param sheetName
  292.      * @param selectData 下拉值
  293.      * @param lockedCols 锁定的列
  294.      * @throws Exception
  295.      */
  296.     private void createSheet(XSSFWorkbook workbook, Class clazz, List data, String sheetName, String dateFormat, SelectData selectData, List<Integer> lockedCols)
  297.             throws Exception {
  298.         Field[] fields = clazz.getDeclaredFields();
  299.         Map<String, String> columnInfos = new LinkedHashMap<>();
  300.         //设置表头
  301.         XSSFSheet sheet = workbook.createSheet(sheetName);
  302.         XSSFRow headRow = sheet.createRow(0);
  303.         for (int i = 0; i < fields.length; i++) {
  304.             ColumnName cn = fields[i].getAnnotation(ColumnName.class);
  305.             if (cn != null) {
  306.                 XSSFCell cell = headRow.createCell(i);
  307.                 sheet.setColumnWidth(i, 5000);
  308.                 //用easyExcel设置表头
  309.                 cell.setCellStyle(StyleUtil.buildDefaultCellStyle(workbook));
  310.                 String fieldType = fields[i].getGenericType().toString();
  311.                 cell.setCellValue(cn.value());
  312.                 columnInfos.put(fields[i].getName(), fieldType);
  313.             }
  314.         }
  315.         //列的下拉选择赋值
  316.         if(selectData != null){
  317.             //生成下拉框
  318.             String[] datas = selectData.getDatas();
  319.             XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
  320.             XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(datas);
  321.             CellRangeAddressList addressList = new CellRangeAddressList(selectData.getFirstRow(), selectData.getLastRow(), selectData.getFirstCol(), selectData.getLastCol());
  322.             XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(
  323.                     dvConstraint, addressList);
  324.             sheet.addValidationData(validation);
  325.         }
  326.         sheet.protectSheet("111");
  327.         //XSSFCellStyle bodyStyle = workbook.createCellStyle();
  328.         XSSFCellStyle lockStyle =  workbook.createCellStyle();
  329.         lockStyle.setLocked(true);
  330.         //单元格不锁定的样式
  331.         XSSFCellStyle unlockStyle =  workbook.createCellStyle();
  332.         unlockStyle.setLocked(false);
  333.         //bodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  334.         //设置内容
  335.         for (int i = 0; i < data.size(); i++) {
  336.             XSSFRow row = sheet.createRow(i + 1);
  337.             int j = 0;
  338.             for (Map.Entry<String, String> entry2 : columnInfos.entrySet()) {
  339.                 String columnName = entry2.getKey();
  340.                 String methodName = "get" + columnName.substring(0, 1).toUpperCase() + columnName.substring(1);
  341.                 Method method = clazz.getDeclaredMethod(methodName);
  342.                 Object value = method.invoke(data.get(i));
  343.                 XSSFCell cell = row.createCell(j);
  344.                 //判断是否要锁定列
  345.                 if(lockedCols != null && lockedCols.contains(j)){
  346.                     cell.setCellStyle(lockStyle);
  347.                 }else{
  348.                     cell.setCellStyle(unlockStyle);
  349.                 }
  350.  
  351.                 j++;
  352.                 if (value != null) {
  353.  
  354.                     switch (entry2.getValue()) {
  355.                         case "class java.lang.String":
  356.                             cell.setCellValue((String) value);
  357.                             break;
  358.                         case "int":
  359.                             cell.setCellValue((int) value);
  360.                             break;
  361.                         case "class java.lang.Integer":
  362.                             cell.setCellValue((int) value);
  363.                             break;
  364.                         case "class java.lang.Boolean":
  365.                             cell.setCellValue((boolean) value);
  366.                             break;
  367.                         case "class java.util.Date":
  368.                             //SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
  369.                             SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
  370.                             cell.setCellValue(sdf.format((Date) value));
  371.                             break;
  372.                         default:
  373.                     }
  374.                 } else {
  375.                     cell.setCellValue("");
  376.                 }
  377.             }
  378.         }
  379.     }
  380.  
  381.     /**
  382.      * 创建Excel的Sheet
  383.      *
  384.      * @param workbook
  385.      * @param headerMap 表头字段属性集合 key 字段 value 字段含义(需有序)
  386.      * @param data
  387.      * @param clazz
  388.      * @param sheetName
  389.      * @throws Exception boolean类型注意封装时的get/set方法
  390.      */
  391.     private void createSheetByMap(XSSFWorkbook workbook, Map<String, String> headerMap, List data, Class clazz,
  392.                                   String sheetName) throws Exception {
  393.  
  394.         //样式
  395.         XSSFFont font = workbook.createFont();
  396.         //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
  397.         font.setColor(HSSFColor.BLUE.index);
  398.         XSSFCellStyle headStyle = workbook.createCellStyle();
  399.         headStyle.setFont(font);
  400.         //headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  401.  
  402.         //设置表头
  403.         XSSFSheet sheet = workbook.createSheet(sheetName);
  404.         XSSFRow headRow = sheet.createRow(0);
  405.         int headerIdx = 0;
  406.         for (Map.Entry<String, String> headerEntry : headerMap.entrySet()) {
  407.             XSSFCell cell = headRow.createCell(headerIdx);
  408.             cell.setCellType(XSSFCell.CELL_TYPE_STRING);
  409.             sheet.setColumnWidth(headerIdx, 5000);
  410.             cell.setCellStyle(headStyle);
  411.             cell.setCellValue(headerEntry.getValue());
  412.             headerIdx++;
  413.         }
  414.         XSSFCellStyle bodyStyle = workbook.createCellStyle();
  415.         //bodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  416.  
  417.         //设置内容
  418.         XSSFRow dataRow;
  419.         Field field;
  420.         Method method;
  421.         XSSFCell cell;
  422.         StringBuilder sb = new StringBuilder();
  423.         for (int i = 0; i < data.size(); i++) {
  424.             dataRow = sheet.createRow(i + 1);
  425.             int j = 0;
  426.             for (Map.Entry<String, String> headerEntry : headerMap.entrySet()) {
  427.                 field = clazz.getDeclaredField(headerEntry.getKey());
  428.                 sb.append("get").append(field.getName().substring(0, 1).toUpperCase())
  429.                         .append(field.getName().substring(1));
  430.                 method = clazz.getDeclaredMethod(sb.toString());
  431.                 sb.delete(0, sb.length());
  432.                 Object value = method.invoke(data.get(i));
  433.                 cell = dataRow.createCell(j++);
  434.                 cell.setCellStyle(bodyStyle);
  435.                 if (value != null) {
  436.                     switch (field.getType().getName()) {
  437.                         case "java.lang.String":
  438.                             cell.setCellValue((String) value);
  439.                             break;
  440.                         case "int":
  441.                             cell.setCellValue((int) value);
  442.                             break;
  443.                         case "java.lang.Integer":
  444.                             cell.setCellValue((int) value);
  445.                             break;
  446.                         case "java.lang.Boolean":
  447.                             cell.setCellValue((boolean) value);
  448.                             break;
  449.                         case "java.util.Date":
  450.                             SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
  451.                             cell.setCellValue(sdf.format((Date) value));
  452.                             break;
  453.                         default:
  454.                     }
  455.                 } else {
  456.                     cell.setCellValue("");
  457.                 }
  458.             }
  459.         }
  460.     }
  461. }

demo

  1. @GetMapping("/exportExcel")
  2.     public void exportExcel(HttpServletResponse response) throws Exception {
  3.         List<Person> list=new ArrayList<>();
  4.         Person p1=new Person("末日");
  5.         Person p2=new Person("神灵");
  6.         list.add(p1);
  7.         list.add(p2);
  8.         String[] datas = new String[] {"部门","科室","岗位 "};
  9.         SelectData selectData = new SelectData(datas, 1, 100, 0, 0);
  10.         XSSFWorkbook applicantExcel = poiExcelUtil.listToExcel(Person.class, list, "kongtest", "yyyy-MM-dd", selectData);
  11.         // 输出附件
  12.         response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("下拉选择.xlsx", "UTF-8"));
  13.         OutputStream outStream = response.getOutputStream();
  14.         applicantExcel.write(outStream);
  15.         outStream.flush();
  16.     }
  17.  
  18. 导入
  19.  Map<String,Object> result = excelUtil.readExcel(file, new Person(),1);
  20.         List<Person> excelImports = new ArrayList<>();
  21.         Boolean flag = (Boolean) result.get("flag");
  22.         Response response;
  23.         List<Object> list;
  24.         if(flag){
  25.             list = (List<Object>) result.get("datas");
  26.             if(list != null && list.size() > 0){
  27.                 list = (List<Object>) result.get("datas");
  28.             }else{
  29.                 return BaseUtil.createResponse(ReturnCode.ERROR, "",         Arrays.asList(ReturnMessage.IMPORT_NULL));
  30.             }
  31.         }else{
  32.             return BaseUtil.createResponse(ReturnCode.ERROR, "", Arrays.asList(ReturnMessage.EXCEL_HEAD_ERROR));
  33.         }

 

声明：本文内容由网友自发贡献，转载请注明出处：【wpsshop博客】
推荐阅读
相关标签

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

  

闽ICP备14008679号