直接上代码

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<String, Object> 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<String, Object> getData(File excelFile, int startLine, int endLine) {
    Map<String, Object> 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<Map<String, Object>> getTableData(File excelFile, int titleLine) {
    List<Map<String, Object>> list = new ArrayList<>();
    try (Workbook workbook = WorkbookFactory.create(excelFile)) {
      Sheet sheet = workbook.getSheetAt(0);
      // 表格标题所在的行
      Row row = sheet.getRow(titleLine);
      if (row != null) {
        // 获取标题对应的索引
        Map<Integer, String> indexMap = getIndexMap(row);
        // 获取表格中的数据
        for (int i = titleLine + 1; i <= sheet.getLastRowNum(); i++) {
          row = sheet.getRow(i);
          if (row != null) {
            Map<String, Object> 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<Map<String, Object>> getTableData(InputStream inputStream, int titleLine) {
    List<Map<String, Object>> list = new ArrayList<>();
    try (Workbook workbook = WorkbookFactory.create(inputStream)) {
      Sheet sheet = workbook.getSheetAt(0);
      // 表格标题所在的行
      Row row = sheet.getRow(titleLine);
      if (row != null) {
        // 获取标题对应的索引
        Map<Integer, String> indexMap = getIndexMap(row);
        // 获取表格中的数据
        for (int i = titleLine + 1; i <= sheet.getLastRowNum(); i++) {
          row = sheet.getRow(i);
          if (row != null) {
            Map<String, Object> 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<Integer, String> getIndexMap(Row row) {
    Map<Integer, String> 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<String, Object> map, Map<Integer, String> 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<String, Object> 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<String,String[]> getDataOfIntegerMap(File excelFile, int startLine, int endLine) {
    Map<String, String[]> 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<Map<String, String>> getTableData1(File excelFile, int titleLine) {
    List<Map<String, String>> list = new ArrayList<>();
    try (Workbook workbook = WorkbookFactory.create(excelFile)) {
      Sheet sheet = workbook.getSheetAt(0);
      // 表格标题所在的行
      Row row = sheet.getRow(titleLine);
      if (row != null) {
        // 获取标题对应的索引
        Map<Integer, String> indexMap = getIndexMap(row);
        // 获取表格中的数据
        for (int i = titleLine + 1; i <= sheet.getLastRowNum(); i++) {
          row = sheet.getRow(i);
          if (row != null) {
            Map<String, String> 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<String, String> map, Map<Integer, String> 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<String, String> 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<String, String[]> 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<String, String> 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<Map<String,String>> list) {
    int max = row.getLastCellNum();
    HashMap<String, String> 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<String, String> 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<String, String[]> readOfStringMap(Workbook wb, int startLine, int endLine) {
    Map<String, String[]> 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<String, String> getData(Workbook workbook, int startLine) {
    Sheet sheet = workbook.getSheetAt(0);
    int endLine = sheet.getLastRowNum();
    return getWorkbookData(workbook, startLine, endLine);
  }

  public static Map<String, String> getWorkbookData(Workbook workbook, int startLine, int endLine) {
    Map<String, String> 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<Map<String,String>> getDataWithList(File excelFile, int startLine, int endLine) {
    List<Map<String,String>> 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<Map<String, String>> getTableDataByInputStream(Workbook wb, int titleLine ) {
//    Workbook wb = getWorkbook(inputStream, isExcel2003);
    List<Map<String, String>> list = new ArrayList<>();
      Sheet sheet = wb.getSheetAt(0);
      // 表格标题所在的行
      Row row = sheet.getRow(titleLine);
      if (row != null) {
        // 获取标题对应的索引
        Map<Integer, String> indexMap = getIndexMap(row);
        // 获取表格中的数据
        for (int i = titleLine + 1; i <= sheet.getLastRowNum(); i++) {
          row = sheet.getRow(i);
          if (row != null) {
            Map<String, String> 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<Integer> titleRows, List<Map<String, Object>> datas) {
    if (datas.isEmpty()) {
      return;
    }
    Map<Integer, String> indexMap = new HashMap<>();
    for (Integer titleRow : titleRows) {
      Row row = sheet.getRow(titleRow);
      Map<Integer, String> 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<String, Object> map = datas.get(i);
      for (Map.Entry<Integer, String> 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());
        }
      }
    }
  }

}
Excel 文件操作工具类

直接上代码

相关推荐

取消回复欢迎你发表评论:

Google 黑客常用搜索语句一览原力计划

npx简介（npxvip是哪国的）

在 Android 模拟器上运行 ARM 应用（android模拟器原理）

GB28181,B接口协议之SIPRTSPRTPRTMP协议从入门到精通

安装使用Hoppscotch构建API请求访问与测试

手机实时提取SIM卡打电话的信令和声音-辅助外设与商用通话方案

Python自动化办公——后台截图（python 自动截图）

轻松转换!AppleNumbers到Excel的快捷教程

电脑端腾讯文档如何导出excel

用OpenCV测量图像中物体的大小（基于opencv的物体尺寸检测算法实现）

Excel 文件操作工具类

直接上代码

相关推荐

取消回复欢迎 你 发表评论:

Google 黑客常用搜索语句一览 原力计划

npx简介（npxvip是哪国的）

在 Android 模拟器上运行 ARM 应用（android模拟器原理）

GB28181,B接口协议之SIPRTSPRTPRTMP协议从入门到精通

安装使用Hoppscotch构建API请求访问与测试

手机实时提取SIM卡打电话的信令和声音-辅助外设与商用通话方案

Python自动化办公——后台截图（python 自动截图）

轻松转换!AppleNumbers到Excel的快捷教程

电脑端腾讯文档如何导出excel

用OpenCV测量图像中物体的大小（基于opencv的物体尺寸检测算法实现）

取消回复欢迎你发表评论:

Google 黑客常用搜索语句一览原力计划
