Excel文件操作工具类

　　直接上代码package com.sccin.spboot.utils;  import com.sccin.spboot.pojo.exception.RunMsgException; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.Comment; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory;  import java.io.File; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.Collections; import java.util.Comparator; import java.util.HashMap; import java.util.List; import java.util.Map;  /**  * Excel文件操作  *  * @author zx  */ public final class ExcelUtil {      /**    * 结束标记    */   public static final String END_OF_TABLE = ＂$end_of_table＂;    /**    * 日志    */   private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtil.class);    private ExcelUtil() {   }    /**    * 读取Excel文件中的数据    *    * @param excelFile excel文件    * @param startLine 起始行（基于0）    * @return map数据    */   public static Map getData(File excelFile, int startLine) {     try (Workbook workbook = WorkbookFactory.create(excelFile)) {       Sheet sheet = workbook.getSheetAt(0);       int endLine = sheet.getLastRowNum();       return getData(excelFile, startLine, endLine);     } catch (IOException | InvalidFormatException e) {       LOGGER.error(e.getMessage(), e);     }     return Collections.emptyMap();   }    /**    * 读取Excel文件中的数据    *    * @param excelFile excel文件    * @param startLine 起始行（基于0）    * @param endLine   结束行（基于0）    * @return map数据    */   public static Map getData(File excelFile, int startLine, int endLine) {     Map map = new HashMap<>();     try (Workbook workbook = WorkbookFactory.create(excelFile)) {       Sheet sheet = workbook.getSheetAt(0);       for (int i = startLine; i <= endLine; i++) {         Row row = sheet.getRow(i);         if (row != null) {           rowToMap(row, map);         }       }     } catch (IOException | InvalidFormatException e) {       LOGGER.error(e.getMessage(), e);     }     return map;   }    /**    * 读取Excel文件中表格的数据    *    * @param excelFile excel文件    * @param titleLine 表格标题所在的行（基于0）    * @return 数据列表    */   public static List> getTableData(File excelFile, int titleLine) {     List> list = new ArrayList<>();     try (Workbook workbook = WorkbookFactory.create(excelFile)) {       Sheet sheet = workbook.getSheetAt(0);       // 表格标题所在的行       Row row = sheet.getRow(titleLine);       if (row != null) {         // 获取标题对应的索引         Map indexMap = getIndexMap(row);         // 获取表格中的数据         for (int i = titleLine + 1; i <= sheet.getLastRowNum(); i++) {           row = sheet.getRow(i);           if (row != null) {             Map map = new HashMap<>();             rowToMap(row, map, indexMap);             // 跳过空行             if (map.isEmpty()) {               continue;             }             list.add(map);             // 内容结束             if (map.get(END_OF_TABLE) != null) {               break;             }           }         }       }     } catch (IOException | InvalidFormatException e) {       LOGGER.error(e.getMessage(), e);       throw new RunMsgException(＂读取数据错误＂, e);     }     return list;   }    /**    * 读取Excel文件中表格的数据    *    * @param inputStream excel文件    * @param titleLine   表格标题所在的行（基于0）    * @return 数据列表    */   public static List> getTableData(InputStream inputStream, int titleLine) {     List> list = new ArrayList<>();     try (Workbook workbook = WorkbookFactory.create(inputStream)) {       Sheet sheet = workbook.getSheetAt(0);       // 表格标题所在的行       Row row = sheet.getRow(titleLine);       if (row != null) {         // 获取标题对应的索引         Map indexMap = getIndexMap(row);         // 获取表格中的数据         for (int i = titleLine + 1; i <= sheet.getLastRowNum(); i++) {           row = sheet.getRow(i);           if (row != null) {             Map map = new HashMap<>();             rowToMap(row, map, indexMap);             // 跳过空行             if (map.isEmpty()) {               continue;             }             list.add(map);             // 内容结束             if (map.get(END_OF_TABLE) != null) {               break;             }           }         }       }     } catch (IOException | InvalidFormatException e) {       LOGGER.error(e.getMessage(), e);       throw new RunMsgException(＂读取数据错误＂, e);     }     return list;   }    /**    * 返回Excel文件数据字段名称的索引    *    * @param row 行    * @return map    */   private static Map getIndexMap(Row row) {     Map map = new HashMap<>();     int max = row.getLastCellNum();     for (int i = 0; i <= max; i++) {       Cell cell = row.getCell(i);       if (cell == null) {         continue;       }       Comment comment = cell.getCellComment();       if (comment == null) {         continue;       }       map.put(i, comment.getString().getString().trim());     }     return map;   }    /**    * 行数据转存入map中，key为单元格索引在indexMap中的值，value为单元格的值    *    * @param row      行    * @param map      map    * @param indexMap index map    */   private static void rowToMap(Row row, Map map, Map indexMap) {     int max = row.getLastCellNum();     for (int i = 0; i <= max; i++) {       String key = indexMap.get(i);       if (key == null) {         continue;       }       Cell cell = row.getCell(i);       if (cell == null) {         continue;       }       Comment comment = cell.getCellComment();       // 如果表格数据结束，返回结束所在的行数       if ((comment != null) && END_OF_TABLE.equals(comment.getString().getString())) {         map.clear();         map.put(END_OF_TABLE, row.getRowNum());         return;       }       cell.setCellType(CellType.STRING);       String value = cell.getStringCellValue();       if (!value.isEmpty()) {         map.put(key, value);       }     }   }    /**    * 行数据转存入map中，key为单元格的批注，value为单元格的值    *    * @param row 行    * @param map map    */   private static void rowToMap(Row row, Map map) {     int max = row.getLastCellNum();     for (int i = 0; i <= max; i++) {       Cell cell = row.getCell(i);       if (cell == null) {         continue;       }       Comment comment = cell.getCellComment();       if (comment == null) {         continue;       }       String key = comment.getString().getString().trim();       cell.setCellType(CellType.STRING);       String value = cell.getStringCellValue();       map.putIfAbsent(key, value);     }   }   /**    * 读取Excel文件中的数据    *    * @param excelFile excel文件    * @param startLine 起始行（基于0）    * @param endLine   结束行（基于0）    * @return map数据    */   public static Map getDataOfIntegerMap(File excelFile, int startLine, int endLine) {     Map map = new HashMap<>();     try (Workbook workbook = WorkbookFactory.create(excelFile)) {       Sheet sheet = workbook.getSheetAt(0);       for (int i = startLine; i <= endLine; i++) {         Row row = sheet.getRow(i);         if (row != null) {           rowToMap2(row, map);         }       }     } catch (IOException | InvalidFormatException e) {       LOGGER.error(e.getMessage(), e);       System.out.println(e);     }     return map;   }   /**    * 读取Excel文件中表格的数据    *    * @param excelFile excel文件    * @param titleLine 表格批注所在的行（基于0）    * @return 数据列表    */   public static List> getTableData1(File excelFile, int titleLine) {     List> list = new ArrayList<>();     try (Workbook workbook = WorkbookFactory.create(excelFile)) {       Sheet sheet = workbook.getSheetAt(0);       // 表格标题所在的行       Row row = sheet.getRow(titleLine);       if (row != null) {         // 获取标题对应的索引         Map indexMap = getIndexMap(row);         // 获取表格中的数据         for (int i = titleLine + 1; i <= sheet.getLastRowNum(); i++) {           row = sheet.getRow(i);           if (row != null) {             Map map = new HashMap<>();             rowToMap1(row, map, indexMap);             // 跳过空行             if (map.isEmpty()) {               continue;             }             list.add(map);             // 内容结束             if (map.get(END_OF_TABLE) != null) {               break;             }           }         }       }     } catch (IOException | InvalidFormatException e) {       LOGGER.error(e.getMessage(), e);       throw new RunMsgException(e.getMessage(),e);     }     return list;   }   /**    * 行数据转存入map中，key为单元格索引在indexMap中的值，value为单元格的值    *    * @param row      行    * @param map      map    * @param indexMap index map    */   private static void rowToMap1(Row row, Map map, Map indexMap) {     int max = row.getLastCellNum();     for (int i = 0; i <= max; i++) {       String key = indexMap.get(i);       if (key == null) {         continue;       }       Cell cell = row.getCell(i);       if (cell == null) {         continue;       }       Comment comment = cell.getCellComment();       // 如果表格数据结束，返回结束所在的行数       if ((comment != null) && END_OF_TABLE.equals(comment.getString().getString())) {         map.clear();         map.put(END_OF_TABLE, StringUtil.getString(row.getRowNum()));         return;       }       cell.setCellType(CellType.STRING);       String value = cell.getStringCellValue();       if (!value.isEmpty()) {         map.put(key, value.trim());       }     }   }   /**    * 行数据转存入map中，key为单元格的批注，value为单元格的值    *    * @param row 行    * @param map map    */   private static void rowToMapString(Row row, Map map) {     int max = row.getLastCellNum();     for (int i = 0; i <= max; i++) {       Cell cell = row.getCell(i);       if (cell == null) {         continue;       }       Comment comment = cell.getCellComment();       if (comment == null) {         continue;       }       String key = comment.getString().getString().trim();       cell.setCellType(CellType.STRING);       String value = cell.getStringCellValue();       map.putIfAbsent(key, value);     }   }   /**    * 行数据转存入map中，key为单元格的批注，value为单元格的值    *    * @param row 行    * @param map map    */   private static void rowToMap2(Row row, Map map) {     int max = row.getLastCellNum();     for (int i = 0; i <= max; i++) {       Cell cell = row.getCell(i);       if (cell == null) {         continue;       }       Comment comment = cell.getCellComment();       if (comment == null) {         continue;       }       String key = comment.getString().getString().trim();       cell.setCellType(CellType.STRING);       String value = cell.getStringCellValue();       Map sonMap = new HashMap<>();       map.putIfAbsent(String.valueOf(i),new String[]{key,value});     }   }   /**    * 行数据转存入map中，key为单元格的批注，value为单元格的值    *    * @param row 行    * @param list 数组    */   private static void rowToMapOfList(Row row, List> list) {     int max = row.getLastCellNum();     HashMap cellMap = new HashMap<>();     for (int i = 0; i <= max; i++) {       Cell cell = row.getCell(i);       if (cell == null) {         continue;       }       cell.setCellType(CellType.STRING);       String value = StringUtil.getString(cell.getStringCellValue());       cellMap.put(String.valueOf(i),value);     }     list.add(cellMap);   }       /**    *    * 根据流读取Excel数据    *    *    * @param inputStream    * @param startLine 开始行    * @param isExcel2003    * @return    * @see [类、类#方法、类#成员]    */   public static Map read(InputStream inputStream, int startLine,boolean isExcel2003) {     Workbook wb = getWorkbook(inputStream, isExcel2003);     return getData(wb,startLine);   }    /**    * 读取Excel文件中的数据    *    * @param startLine 起始行（基于0）    * @param endLine   结束行（基于0）    * @return map数据    */   public static Map readOfStringMap(Workbook wb, int startLine, int endLine) {     Map map = new HashMap<>();     Sheet sheet = wb.getSheetAt(0);     for (int i = startLine; i <= endLine; i++) {       Row row = sheet.getRow(i);       if (row != null) {         rowToMap2(row, map);       }     }     return map;   }     /**    *    * @param workbook Excel对象    * @param startLine    * @return    */   public static Map getData(Workbook workbook, int startLine) {     Sheet sheet = workbook.getSheetAt(0);     int endLine = sheet.getLastRowNum();     return getWorkbookData(workbook, startLine, endLine);   }    public static Map getWorkbookData(Workbook workbook, int startLine, int endLine) {     Map map = new HashMap<>();     Sheet sheet = workbook.getSheetAt(0);     for (int i = startLine; i <= endLine; i++) {       Row row = sheet.getRow(i);       if (row != null) {         rowToMapString(row, map);       }     }     return map;   }    /**    * 读取Excel文件中的数据    *    * @param excelFile excel文件    * @param startLine 起始行（基于0）    * @param endLine   结束行（基于0）    * @return map数据    */   public static List> getDataWithList(File excelFile, int startLine, int endLine) {     List> result = new ArrayList<>();     try (Workbook workbook = WorkbookFactory.create(excelFile)) {       Sheet sheet = workbook.getSheetAt(0);       for (int i = startLine; i <= endLine; i++) {         Row row = sheet.getRow(i);         if (row != null) {           rowToMapOfList(row, result);         }       }     } catch (IOException | InvalidFormatException e) {       LOGGER.error(e.getMessage(), e);     }     return result;   }   /**    * 读取Excel文件中表格的数据    *    * @param titleLine 表格标题所在的行（基于0）    * @return 数据列表    */   public static List> getTableDataByInputStream(Workbook wb, int titleLine ) { //    Workbook wb = getWorkbook(inputStream, isExcel2003);     List> list = new ArrayList<>();       Sheet sheet = wb.getSheetAt(0);       // 表格标题所在的行       Row row = sheet.getRow(titleLine);       if (row != null) {         // 获取标题对应的索引         Map indexMap = getIndexMap(row);         // 获取表格中的数据         for (int i = titleLine + 1; i <= sheet.getLastRowNum(); i++) {           row = sheet.getRow(i);           if (row != null) {             Map map = new HashMap<>();             rowToMap1(row, map, indexMap);             // 跳过空行             if (map.isEmpty()) {               continue;             }             list.add(map);             // 内容结束             if (map.get(END_OF_TABLE) != null) {               break;             }           }         }       }     return list;   }    public static Workbook getWorkbook(InputStream inputStream, boolean isExcel2003) {     /** 根据版本选择创建Workbook的方式 */     Workbook wb = null;     try {       if (isExcel2003)       {         wb = new HSSFWorkbook(inputStream);       }       else       {         wb = new XSSFWorkbook(inputStream);       }     }catch (IOException e){       throw new RunMsgException(e.getMessage(),e);     }     return wb;   }    /**    * 给excel文件设置表格行数据    *    * @param sheet     excel工作表    * @param titleRows 标题所在的行    * @param datas     数据    */   public static void setTableData(Sheet sheet, List titleRows, List> datas) {     if (datas.isEmpty()) {       return;     }     Map indexMap = new HashMap<>();     for (Integer titleRow : titleRows) {       Row row = sheet.getRow(titleRow);       Map map = getIndexMap(row);       indexMap.putAll(map);     }     Integer start = titleRows.stream().max(Comparator.naturalOrder()).orElse(null);     if (start == null) {       return;     }     start += 1;     sheet.shiftRows(start, sheet.getLastRowNum(), datas.size(), true, false);     CellStyle cellStyle = sheet.getWorkbook().createCellStyle();     cellStyle.setBorderBottom(BorderStyle.THIN);     cellStyle.setBorderLeft(BorderStyle.THIN);     cellStyle.setBorderRight(BorderStyle.THIN);     cellStyle.setBorderTop(BorderStyle.THIN);     cellStyle.setWrapText(true);     cellStyle.setAlignment(HorizontalAlignment.CENTER);     cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);     for (int i = 0; i < datas.size(); i++) {       int rowNum = start + i;       Row row = sheet.createRow(rowNum);       Map map = datas.get(i);       for (Map.Entry entry : indexMap.entrySet()) {         Object value = map.get(entry.getValue());         Cell cell = row.createCell(entry.getKey());         cell.setCellStyle(cellStyle);         if (value != null) {           cell.setCellValue(value.toString());         }       }     }   }  }