POI5.0将Excel之xlsx转为String[][]字符串二维数组简单试用

---

POI5.0将Excel之xlsx转为String[][]字符串二维数组简单试用

---

一、POI是什么？

Apache POI是Office文档的Java处理包

二、使用步骤

1.Maven引入

ooxml已包含对poi和schemas的引用

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

2.示例代码

代码如下（示例）：

import java.io.InputStream;

import org.apache.poi.xssf.usermodel.*;

public class Try1 {
	
	public static String[][] sheet2Sar2d(XSSFSheet sheet){
		String[][] result = null;
		try {
			result = new String[sheet.getLastRowNum()][];
			XSSFRow row = null;
			XSSFCell cell = null;
			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);
					result[r][c] = cell==null ? "" : cell.toString();
				}
			}
		}catch(Exception exception) {exception.printStackTrace();}
		return result;
	}
	
	public static void main(String[] arguments) throws Exception{
		InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("xls010.xlsx");
		XSSFWorkbook workbook = new XSSFWorkbook(is);
		
		String[][] ar2d = sheet2Sar2d(workbook.getSheetAt(0));
		for(int r=0; r<ar2d.length; r++) {
			for(int c=0; c<ar2d[r].length; c++) {
				System.out.append(ar2d[r][c]).append(',');
			}
			System.out.println();
		}
		is.close();
		workbook.close();
	}

}

示例代码210923

	@SuppressWarnings("deprecation")
	public static String[][] rcFrSheet(Sheet sheet){
		String[][] rlt = null; Row row = null; Cell cell = null;
		int  rowCount = sheet.getLastRowNum()+1; //getLastRowNum()要+1 , getLastCellNum()不用
		int  colCount = sheet.getRow(0).getLastCellNum();
		try {
			rlt = new String[rowCount][];  
			for(int r=0; r<rlt.length; r++) {
				row = sheet.getRow(r);
				rlt[r] = new String[colCount];
				for(int c=0; c<rlt[r].length ; c++) {
					cell = row.getCell(c);
					if(cell==null) {
						rlt[r][c]="";
					}else {
						if(cell.getCellType()==CellType.STRING) {rlt[r][c]=cell.getStringCellValue();}
						else if(cell.getCellType()==CellType.NUMERIC) {
							if(DateUtil.isCellDateFormatted(cell)) {
								Date date = cell.getDateCellValue();
								String dtStr;
								if(date.getSeconds()!=0 && date.getMinutes()!=0 && date.getHours()!=0)
								{dtStr = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);}
								else{dtStr = new SimpleDateFormat("yyyy-MM-dd").format(date);}
								rlt[r][c]=dtStr;
							}else{
								String str = cell.toString();
								if(str.endsWith(".0"))str=str.substring(0, str.length()-2);
								rlt[r][c]=str;
							};
						}else {
							rlt[r][c]=cell.toString();
						}
					}
				}
			}
		}catch(Exception exception) {exception.printStackTrace();}
		if(isPln) {
			for(int r=0;r<rlt.length;r++) {	for(int c=0;c<rlt[r].length;c++) {	System.out.append(rlt[r][c]).append("   ");	}System.out.println();		}
		}
		return rlt;
	}
	public static String[][] rcFrExcelSheet0(InputStream is){
		String sar2d[][] = null; Workbook workbook=null;
		try {
			workbook = WorkbookFactory.create(is);
			sar2d = rcFrSheet(workbook.getSheetAt(0));
		}catch(Exception exception) {exception.printStackTrace();}
		finally {
			try {is.close();}catch(Exception e) {}
			try {workbook.close();}catch(Exception e) {}
		}
		return sar2d;
	}
	
	public static class Aj extends ArrayList<LinkedHashMap<String,String>>{	private static final long serialVersionUID = 1L;}
	public static Aj ajFrRc(String[][] ar2d) {
		String heads[] = ar2d[0];
		Aj aj = new Aj();
		for(int r=1; r<ar2d.length; r++) {
			String rsar[] = ar2d[r];
			LinkedHashMap<String, String> lh = new LinkedHashMap<String, String>();
			for(int c=0;c<heads.length;c++) {		lh.put(heads[c], rsar[c]);			}			aj.add(lh);
		}
		return aj;
	}
	public static Aj ajFrExcelSheet0(InputStream is) {	String ar2d[][] = rcFrExcelSheet0(is);	return ajFrRc(ar2d);	}

---

总结

HSSF：Excel97-2003版本，扩展名为.xls。一个sheet最大行数65536，最大列数256。
XSSF：Excel2007版本开始，扩展名为.xlsx。一个sheet最大行数1048576，最大列数16384。
SXSSF：是在XSSF基础上，POI3.8版本开始提供的支持低内存占用的操作方式，扩展名为.xlsx。
Workbook用完要关闭
使用setCellType()要指定HSSF或XSSF , 否则已过时 , 或者直接用toString()