对Excel表格操作的常用工具类

た入场券 2024-04-08 12:19 34阅读 0赞

## 注意 ##

`excel读取的行列数，都是从零开始的`

## 需要引入的包 ##

import com.alibaba.excel.EasyExcel;
    import com.alibaba.excel.ExcelWriter;
    import com.alibaba.excel.metadata.data.WriteCellData;
    import com.alibaba.excel.util.BooleanUtils;
    import com.alibaba.excel.write.handler.CellWriteHandler;
    import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
    import com.alibaba.excel.write.metadata.WriteSheet;
    import com.alibaba.excel.write.metadata.style.WriteCellStyle;
    import com.alibaba.excel.write.metadata.style.WriteFont;
    import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;

## 获取单元格的值 ##

获取这个单元格的值

/**
     * 获取单元格的值
     *
     * @param cell 需要判断的单元格
     * @return     单元格的值
     */
    public static String getCellValue(Cell cell) {
        
        if (cell != null) {
        
            //判断cell类型
            switch (cell.getCellType()) {
        
                case NUMERIC: {
        //数字
                    return String.valueOf(cell.getNumericCellValue());
                }
                case FORMULA: {
        //公式
                    //判断cell是否为日期格式
                    if (DateUtil.isCellDateFormatted(cell)) {
        
                        //转换为日期格式YYYY-mm-dd
                        return String.valueOf(cell.getDateCellValue());
                    } else {
        
                        //数字
                        return String.valueOf(cell.getNumericCellValue());
                    }
                }
                case STRING: {
        //字符串型
                    return cell.getRichStringCellValue().getString();
                }
                default:
                    return  "";
            }
        } else {
        
            return  "";
        }
    }

## 获取所有合并行的集合 ##

/**
      * 合并单元格处理,获取合并行
      *
      * @param sheet sheet
      * @return List<CellRangeAddress>
      */
     public static List<CellRangeAddress> getCombineCell(Sheet sheet) {
        
         List<CellRangeAddress> list = new ArrayList<>();
         //获得一个 sheet 中合并单元格的数量
         int sheetMergerCount = sheet.getNumMergedRegions();
         //遍历所有的合并单元格
         for (int i = 0; i < sheetMergerCount; i++) {
        
             //获得合并单元格保存进list中
             CellRangeAddress ca = sheet.getMergedRegion(i);
             list.add(ca);
         }
         return list;
     }

## 获取合并行所占的行数 ##

获取到的是合并行在表格中最后所占的行数是第几行  
如

> `这个获取到的便是8`  
> ![在这里插入图片描述][8be212750776483a9939428fc145d4bb.png]

/**
    *  获取合并行所占的行数
    * @param listCombineCell 存放合并单元格的list
    * @param cell            需要判断的单元格
    * @return                合并的行数
    */
    public static int getRowNum(List<CellRangeAddress> listCombineCell, Cell cell) {
        
       int xr = 0, firstC, lastC, firstR, lastR;
       for (CellRangeAddress ca : listCombineCell) {
        
           //获得合并单元格的起始行, 结束行, 起始列, 结束列
           firstC = ca.getFirstColumn();
           lastC = ca.getLastColumn();
           firstR = ca.getFirstRow();
           lastR = ca.getLastRow();
           if (cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR) {
        
               if (cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC) {
        
                   xr = lastR;
               }
           }
    
       }
       return xr;
    
    }

## 获取合并单元格的值(通过所有合并行集合、单元格、sheet判断) ##

`这个只需要知道是哪一个单元格即可`

/**
     * 判断单元格是否为合并单元格，是的话则将单元格的值返回
     *
     * @param listCombineCell 存放合并单元格的list
     * @param cell            需要判断的单元格
     * @param sheet           sheet
     * @return                单元格的值
     */
    public static String isCombineCell(List<CellRangeAddress> listCombineCell, Cell cell, Sheet sheet) {
        
        int firstC, lastC, firstR, lastR;
        String cellValue = null;
        for (CellRangeAddress ca : listCombineCell) {
        
            //获得合并单元格的起始行, 结束行, 起始列, 结束列
            firstC = ca.getFirstColumn();
            lastC = ca.getLastColumn();
            firstR = ca.getFirstRow();
            lastR = ca.getLastRow();
            if (cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR) {
        
                if (cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC) {
        
                    Row fRow = sheet.getRow(firstR);
                    Cell fCell = fRow.getCell(firstC);
                    cellValue = getCellValue(fCell);
                    break;
                }
            } else {
        
                cellValue = "";
            }
        }
        return cellValue;
    }

## 获取合并单元格的值(通过行数、列数、sheet判断) ##

`这个需要清楚具体是哪一行哪一列，具体的数字`

/**
     * 获取合并单元格的值, 如果不是合并单元格则输出为null
     *
     * @param row    行下标
     * @param column 列下标
     * @return 合并单元格的值
     */
    public static String getMergedRegionValue(Sheet sheet, int row, int column) {
        
        int sheetMergeCount = sheet.getNumMergedRegions();
    
        for (int i = 0; i < sheetMergeCount; i++) {
        
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
    
            if (row >= firstRow && row <= lastRow) {
        
                if (column >= firstColumn && column <= lastColumn) {
        
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell);
                }
            }
        }
    
        return null;
    }

## 判断指定的单元格是否是合并单元格 ##

/**
      * 判断指定的单元格是否是合并单元格
      *
      * @param row    行下标
      * @param column 列下标
      * @return       单元格是否是合并单元格
      */
     public static boolean isMergedRegion(Sheet sheet, int row, int column) {
        
         int sheetMergeCount = sheet.getNumMergedRegions();
         for (int i = 0; i < sheetMergeCount; i++) {
        
             CellRangeAddress range = sheet.getMergedRegion(i);
             int firstColumn = range.getFirstColumn();
             int lastColumn = range.getLastColumn();
             int firstRow = range.getFirstRow();
             int lastRow = range.getLastRow();
             if (row >= firstRow && row <= lastRow) {
        
                 if (column >= firstColumn && column <= lastColumn) {
        
                     return true;
                 }
             }
         }
         return false;
     }

## 读取Excel文件（传入String类型的excel地址） ##

//读取excel
    public static Workbook readExcel(String filePath) {
        
        if (filePath == null) {
        
            return null;
        }
        String extString;
        extString = filePath.substring(filePath.lastIndexOf("."));
        InputStream is;
        try {
        
            is = new FileInputStream(filePath);
            if (".xls".equals(extString)) {
        
                return new HSSFWorkbook(is);
            } else if (".xlsx".equals(extString)) {
        
                return new XSSFWorkbook(is);
            } else {
        
                return null;
            }
        } catch (IOException e) {
        
            e.printStackTrace();
        }
        return null;
    }

## 读取Excel文件（传入MultipartFile类型数据） ##

//读取excel
    public static Workbook readExcel(MultipartFile excelFile) throws IOException {
        
        if (excelFile == null) {
        
            return null;
        }
    
        InputStream excel = excelFile.getInputStream();
        String excelName = excelFile.getOriginalFilename();
        if (excelName == null){
        
            return null;
        }
        String excelSuffix = excelName.substring(excelName.lastIndexOf("."));
    
        try {
        
            if (".xls".equals(excelSuffix)) {
        
                return new HSSFWorkbook(excel);
            } else if (".xlsx".equals(excelSuffix)) {
        
                return new XSSFWorkbook(excel);
            } else {
        
                return null;
            }
        } catch (IOException e) {
        
            e.printStackTrace();
        }
        return null;
    }

[8be212750776483a9939428fc145d4bb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/0e87b3823c7f4943829dd92abb4c5629.png