java读取含有合并单元格的Excel

灰太狼 2024-02-05 16:50 43阅读 0赞

java读取含有合并单元格的Excel  
Excel如下：  
![在这里插入图片描述][75c9b4e7af6d40bfb77caeb2434352b3.png]

import java.io.File;
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.*;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
    import org.apache.poi.ss.util.CellRangeAddress;
    
    
    public class ExcelUtil {
        
    
        public static void main(String[] args){
        
            ExcelUtil excelUtil = new ExcelUtil();
            //读取excel数据
            List<Map<String,String>> result = excelUtil.readExcelToObj("C:\\Users\\miracle\\Desktop\\合并单元格.xlsx");
            for(Map<String,String> map:result){
        
                System.out.println(map);
            }
    
        }
        /**
         * 读取excel数据
         * @param path
         */
        private List<Map<String,String>> readExcelToObj(String path) {
        
    
            Workbook wb = null;
            List<Map<String,String>> result = null;
            try {
        
                wb = WorkbookFactory.create(new File(path));
                result = readExcel(wb, 0, 1, 0);
            } catch (InvalidFormatException e) {
        
                e.printStackTrace();
            } catch (IOException e) {
        
                e.printStackTrace();
            }
            return result;
        }
    
        /**
         * 读取excel文件
         * @param wb
         * @param sheetIndex sheet页下标：从0开始
         * @param startReadLine 开始读取的行:从0开始
         * @param tailLine 去除最后读取的行
         */
        private List<Map<String,String>> readExcel(Workbook wb,int sheetIndex, int startReadLine, int tailLine) {
        
            Sheet sheet = wb.getSheetAt(sheetIndex);
            Row row = null;
            List<Map<String,String>> result = new ArrayList<Map<String,String>>();
            for(int i=startReadLine; i<sheet.getLastRowNum()-tailLine+1; i++) {
        
    
                row = sheet.getRow(i);
                Map<String,String> map = new HashMap<String,String>();
                for(Cell c : row) {
        
                    String returnStr = "";
    
                    boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());
                    //判断是否具有合并单元格
                    if(isMerge) {
        
                        String rs = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());
    //                    System.out.print(rs + "------ ");
                        returnStr = rs;
                    }else {
        
                        //设置单元格类型
                        c.setCellType(CellType.STRING);
    //                    System.out.print(c.getRichStringCellValue()+"++++ ");
                        returnStr = c.getRichStringCellValue().getString();
                    }
                    if(c.getColumnIndex()==0){
        
                        map.put("class",returnStr);
                    }else if(c.getColumnIndex()==1){
        
                        map.put("course",returnStr);
                    }else if(c.getColumnIndex()==2){
        
                        map.put("student",returnStr);
                    }else if(c.getColumnIndex()==3){
        
                        map.put("score",returnStr);
                    }
    
                }
                result.add(map);
    //            System.out.println();
    
            }
            return result;
    
        }
    
        /**
         * 获取合并单元格的值
         * @param sheet
         * @param row
         * @param column
         * @return
         */
        public 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 sheet
         * @param row
         * @param column
         * @return
         */
        private boolean isMergedRow(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;
        }
    
        /**
         * 判断指定的单元格是否是合并单元格
         * @param sheet
         * @param row 行下标
         * @param column 列下标
         * @return
         */
        private 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;
        }
    
        /**
         * 判断sheet页中是否含有合并单元格
         * @param sheet
         * @return
         */
        private boolean hasMerged(Sheet sheet) {
        
            return sheet.getNumMergedRegions() > 0 ? true : false;
        }
    
        /**
         * 合并单元格
         * @param sheet
         * @param firstRow 开始行
         * @param lastRow 结束行
         * @param firstCol 开始列
         * @param lastCol 结束列
         */
        private void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        
            sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
        }
    
        /**
         * 获取单元格的值
         * @param cell
         * @return
         */
        public String getCellValue(Cell cell){
        
    
            if(cell == null) return "";
    
            if(cell.getCellType() == Cell.CELL_TYPE_STRING){
        
    
                return cell.getStringCellValue();
    
            }else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
        
    
                return String.valueOf(cell.getBooleanCellValue());
    
            }else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){
        
    
                return cell.getCellFormula() ;
    
            }else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
        
    
                return String.valueOf(cell.getNumericCellValue());
    
            }
            return "";
        }
        /**
         * 从excel读取内容
         */
        public static void readContent(String fileName)  {
        
            boolean isE2007 = false;    //判断是否是excel2007格式
            if(fileName.endsWith("xlsx"))
                isE2007 = true;
            try {
        
                InputStream input = new FileInputStream(fileName);  //建立输入流
                Workbook wb  = null;
                //根据文件格式(2003或者2007)来初始化
                if(isE2007)
                    wb = new XSSFWorkbook(input);
                else
                    wb = new HSSFWorkbook(input);
                Sheet sheet = wb.getSheetAt(0);     //获得第一个表单
                Iterator<Row> rows = sheet.rowIterator(); //获得第一个表单的迭代器
                while (rows.hasNext()) {
        
                    Row row = rows.next();  //获得行数据
                    System.out.println("Row #" + row.getRowNum());  //获得行号从0开始
                    Iterator<Cell> cells = row.cellIterator();    //获得第一行的迭代器
                    while (cells.hasNext()) {
        
                        Cell cell = cells.next();
                        System.out.println("Cell #" + cell.getColumnIndex());
                        switch (cell.getCellType()) {
           //根据cell中的类型来输出数据
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                System.out.println(cell.getNumericCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_STRING:
                                System.out.println(cell.getStringCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN:
                                System.out.println(cell.getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_FORMULA:
                                System.out.println(cell.getCellFormula());
                                break;
                            default:
                                System.out.println("unsuported sell type======="+cell.getCellType());
                                break;
                        }
                    }
                }
            } catch (IOException ex) {
        
                ex.printStackTrace();
            }
        }
    
    }

执行结果如下：

{
        score=10, student=张三, course=语文, class=一（1）班}
    {
        score=20, student=李四, course=语文, class=一（1）班}
    {
        score=30, student=王五, course=语文, class=一（1）班}
    {
        score=40, student=赵六, course=数学, class=一（1）班}
    {
        score=50, student=田七, course=数学, class=一（1）班}
    {
        score=60, student=周扒皮, course=数学, class=一（1）班}

[75c9b4e7af6d40bfb77caeb2434352b3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/bd0cdb850f4c473fa91f7a2eca007570.png