Excel合并单元格读取

旧城等待， 2022-05-23 08:18 374阅读 0赞

文件：

![70][]

输出：

南京\_区1\_A101\_192.168.1.5\_  
南京\_区1\_A102\_192.168.1.6\_  
南京\_区2\_A101\_192.168.1.7\_  
南京\_区2\_A201\_192.168.1.8\_  
南京\_区2\_A301\_192.168.1.9\_  
南京\_区2\_A401\_192.168.1.10\_  
南京\_区3\_B101\_192.168.1.11\_  
南京\_区3\_B102\_192.168.1.12\_  
无锡\_区4\_A101\_192.168.1.13\_  
无锡\_区4\_A201\_192.168.1.14\_  
无锡\_区4\_A301\_192.168.1.15\_  
无锡\_区4\_A401\_192.168.1.16\_  
无锡\_区5\_B101\_192.168.1.17\_  
无锡\_区5\_B102\_192.168.1.18\_

常州\_区6\_A101\_192.168.1.19\_

代码：

import java.io.File;
    import java.io.FileInputStream;
    import java.text.SimpleDateFormat;
    
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.DateUtil;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.usermodel.WorkbookFactory;
    import org.apache.poi.ss.util.CellRangeAddress;
    
    public class ExcelUtils {
    
    	public static void main(String[] args) {
    		getAllByExcel("F:/a.xlsx");
    		
    	}
    
    	public static void getAllByExcel(String filepath) {
    		try {
    			// 同时支持Excel 2003、2007
    			File excelFile = new File(filepath); // 创建文件对象
    			FileInputStream is = new FileInputStream(excelFile); // 文件流
    			Workbook workbook = WorkbookFactory.create(is); // 这种方式 Excel
    			
    			String[] res = readExcel(workbook, 0, 1, 0);
    			for (int i = 0; i < res.length; i++) {
    				System.out.println(res[i]);
    			}
    			
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    
    	}
    
    	private static String[] readExcel(Workbook wb, int sheetIndex, int startReadLine, int tailLine) {
    		Sheet sheet = wb.getSheetAt(sheetIndex);
    		Row row = null;
    		String[] res = new String[sheet.getLastRowNum() - tailLine + 1];
    
    		for (int i = startReadLine; i < sheet.getLastRowNum() - tailLine + 1; i++) {
    			
    			row = sheet.getRow(i);
    			res[i] = "";
    			for (Cell c : row) {
    				boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());
    				// 判断是否具有合并单元格
    				if (isMerge) {
    					String rs = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());
    					//System.out.print(rs + "_"+ row.getRowNum()+"_"+c.getColumnIndex() +"_");
    					res[i] += rs+ "_";
    				} else {
    					//System.out.print(c.getRichStringCellValue() + "");
    					res[i] += c.getRichStringCellValue()+ "_";
    				}
    			}
    			//System.out.println();
    		}
    		
    		if(startReadLine > 0){
    			String[] result = new String[res.length - startReadLine];
    			for (int i = 0; i < startReadLine; i++) {
    				for (int j = 0; j < res.length; j++) {
    					if(j == res.length - 1)
    						continue;
    					res[j] = res[j+1];
    				}
    			}
    			for (int i = 0; i < result.length; i++) {
    				result[i] = res[i];
    			}
    			return result;
    		}else{
    			return res;
    		}
    	}
    
    	private 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;
    	}
    
    	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;
    	}
    
    	private static String getCellValue(Cell cell) {
    		SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
    		String cellValue = "";
    		int cellType = cell.getCellType();
    		switch (cellType) {
    		case Cell.CELL_TYPE_STRING: // 文本
    			cellValue = cell.getStringCellValue();
    			break;
    		case Cell.CELL_TYPE_NUMERIC: // 数字、日期
    			if (DateUtil.isCellDateFormatted(cell)) {
    				cellValue = fmt.format(cell.getDateCellValue()); // 日期型
    			} else {
    				cellValue = String.valueOf((int) cell.getNumericCellValue()); // 数字
    			}
    			break;
    		case Cell.CELL_TYPE_BOOLEAN: // 布尔型
    			cellValue = String.valueOf(cell.getBooleanCellValue());
    			break;
    		case Cell.CELL_TYPE_BLANK: // 空白
    			cellValue = cell.getStringCellValue();
    			break;
    		case Cell.CELL_TYPE_ERROR: // 错误
    			cellValue = "错误";
    			break;
    		case Cell.CELL_TYPE_FORMULA: // 公式
    			cellValue = "错误";
    			break;
    		default:
    			cellValue = "错误";
    		}
    		return cellValue;
    	}
    }

maven：

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.15</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.15</version>
        </dependency>

已经拿到输出了还不任你宰割咯

这样也可以：

![70 1][]

输出：

南京1\_区1\_A101\_192.168.1.5\_  
南京2\_区1\_A102\_192.168.1.6\_  
南京3\_区2\_A101\_192.168.1.7\_  
南京4\_区2\_A201\_192.168.1.8\_  
南京5\_区2\_A301\_192.168.1.9\_  
南京6\_区2\_A401\_192.168.1.10\_  
南京7\_区3\_B101\_192.168.1.11\_  
南京8\_区3\_B102\_192.168.1.12\_  
无锡\_区4\_A101\_192.168.1.13\_  
无锡\_区4\_A201\_192.168.1.14\_  
无锡\_区4\_A301\_192.168.1.15\_  
无锡\_区4\_A401\_192.168.1.16\_  
无锡\_区5\_B101\_192.168.1.17\_  
无锡\_区5\_B102\_192.168.1.18\_  
常州\_区6\_A101\_192.168.1.19\_

[70]: /images/20220523/09d899d9d2904dcb99cad2597ef90569.png
[70 1]: /images/20220523/951a4a539ff74f0dacf75dbfefa56aa2.png