POI5.0.0 Sheet转二维字符串数组 20210317

---

前言

笔记参考

一、Maven引入

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

二、代码

1.主代码

	public static String[][] sheet2Sar2d(Sheet sheet){
		String[][] result = null; Row row = null; Cell cell = null;
		try {
			result = new String[sheet.getLastRowNum()+1][];  //getLastRowNum()要+1 , getLastCellNum()不用
			for(int r=0; r<result.length; r++) {
				row = sheet.getRow(r);
				result[r] = row==null ? new String[0] : new String[row.getLastCellNum()];
				for(int c=0; c<result[r].length ; c++) {
					cell = row.getCell(c);
					if(cell==null) {
						result[r][c]="";
					}else {
						if(cell.getCellType()==CellType.STRING) {result[r][c]=cell.getStringCellValue();}
						else if(cell.getCellType()==CellType.NUMERIC) {
							if(DateUtil.isCellDateFormatted(cell)) {
								Date date = cell.getDateCellValue();
								String dtStr = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
								result[r][c]=dtStr;
							}else{
								String str = cell.toString();
								if(str.endsWith(".0"))str=str.substring(0, str.length()-2);
								result[r][c]=str;
							};
						}else {
							result[r][c]=cell.toString();
						}
					}
				}
			}
		}catch(Exception exception) {exception.printStackTrace();}
		return result;
	}
	public static String[][] excelSheet0ToSar2d(InputStream is){
		String sar2d[][] = null; Workbook workbook=null;
		try {
			workbook = WorkbookFactory.create(is);
			sar2d = sheet2Sar2d(workbook.getSheetAt(0));
			is.close(); workbook.close();
		}catch(Exception exception) {
			exception.printStackTrace();
		}
		finally {
			try {is.close(); }catch(Exception e) {}
			try { workbook.close();}catch(Exception e) {}
		}
		return sar2d;
	}

2.主代码和测试代码

	public static String[][] sheet2Sar2d(Sheet sheet){
		String[][] result = null; Row row = null; Cell cell = null;
		try {
			result = new String[sheet.getLastRowNum()+1][];  //getLastRowNum()要+1 , getLastCellNum()不用
			for(int r=0; r<result.length; r++) {
				row = sheet.getRow(r);
				result[r] = row==null ? new String[0] : new String[row.getLastCellNum()];
				for(int c=0; c<result[r].length ; c++) {
					cell = row.getCell(c);
					if(cell==null) {
						result[r][c]="";
					}else {
						if(cell.getCellType()==CellType.STRING) {result[r][c]=cell.getStringCellValue();}
						else if(cell.getCellType()==CellType.NUMERIC) {
							if(DateUtil.isCellDateFormatted(cell)) {
								Date date = cell.getDateCellValue();
								String dtStr = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
								result[r][c]=dtStr;
							}else{
								String str = cell.toString();
								if(str.endsWith(".0"))str=str.substring(0, str.length()-2);
								result[r][c]=str;
							};
						}else {
							result[r][c]=cell.toString();
						}
					}
				}
			}
		}catch(Exception exception) {exception.printStackTrace();}
		return result;
	}
	public static String[][] excelSheet0ToSar2d(InputStream is){
		String sar2d[][] = null; Workbook workbook=null;
		try {
			workbook = WorkbookFactory.create(is);
			sar2d = sheet2Sar2d(workbook.getSheetAt(0));
			is.close(); workbook.close();
		}catch(Exception exception) {
			exception.printStackTrace();
		}
		finally {
			try {is.close(); }catch(Exception e) {}
			try { workbook.close();}catch(Exception e) {}
		}
		return sar2d;
	}
	
	public static void main(String[] arguments) throws Exception{
		InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("xls010.xlsx");
		InputStream is1 = ClassLoader.getSystemClassLoader().getResourceAsStream("xls010.xls");
		printExcel(is);
		printExcel(is1);
	}
	
	public static void printExcel(InputStream is) {
		String[][] sar2d = excelSheet0ToSar2d(is);
		for(int r=0; r<sar2d.length; r++) {
			for(int c=0; c<sar2d[r].length; c++) {
				System.out.append(sar2d[r][c]).append(',');
			}
			System.out.println();
		}
	}

总结

getLastRowNum()比行数少1 , getLastCellNum()等于列数
WorkbookFactory神器可以自动识别xls和xlsx

Workbook workbook = WorkbookFactory.create(is);

日期和数字都是CellType.NUMERIC
DateUtil.isCellDateFormatted(cell)可以判断是否是日期格式