Excel文件读取

java中要读取Excel文件并解析。目前的excel文件主要是xls的97-2000格式和xlsx的2007格式。

以下提供简单的操作示例。

 

1,WorkBook

解析文件格式：xls

 

WorkbookSettings workbookSettings = new WorkbookSettings();
        workbookSettings.setEncoding("ISO-8859-1");
        StringBuffer s = new StringBuffer("");
        List<String> l = new ArrayList<String>();
        try {
			Workbook book = Workbook.getWorkbook(new File("F:\\test2.xls"),workbookSettings);
			Sheet sheet = book.getSheet(0);//只读取excel的第一页
			for (int i = 1; i < sheet.getRows(); i++) {
				for(int j=0;j<sheet.getColumns();j++){
			    	 Cell cell = sheet.getCell(j, i);// 第一列第i行
			         String tmpValue=cell.getContents();
			         tmpValue=tmpValue.replaceAll("\177", "").replaceAll("\\s", "");//公共组件excel导出时，单元值有加一个特殊字符，处理后，用导出的excel做查询条件才会正常
			         s.append(tmpValue+"||");
				}
				l.add(s.toString());
				s.delete(0, s.length());
			}
			for(String ss:l){
				System.out.println(ss);
			}
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} 

 

 

2, POI-HSSF

读取文件格式：xls

程序未终止时,可修改文件.

try{
			InputStream input = new FileInputStream("F:\\testBatchOpen.xlsx");
			POIFSFileSystem pf = new POIFSFileSystem(input);
			HSSFWorkbook wb = new HSSFWorkbook(pf);
			HSSFSheet sheet = wb.getSheetAt(0);
			sheet.removeRow(sheet.getRow(0));
			Iterator rows = sheet.rowIterator();
			while(rows.hasNext()){
				HSSFRow row = (HSSFRow)rows.next();
				System.out.print(row.getCell(0).toString()+";");
				System.out.print(row.getCell(1).toString()+";");
				System.out.print(row.getCell(2).toString()+";");
				System.out.print(row.getCell(3).toString()+";");
				System.out.print(row.getCell(4).toString());
				System.out.println();
			}
		}catch(Exception e){
			e.printStackTrace();
		}

 

 

3，POI-XSSF

读取文件格式：xlsx

try {
InputStream in = new FileInputStream("F:/testBatchOpen.xlsx");

			XSSFWorkbook xwb = new XSSFWorkbook(in);
			// 读取第一章表格内容   
			XSSFSheet sheet = xwb.getSheetAt(0); 
			XSSFRow row;
			for(int i=sheet.getFirstRowNum()+1;i<sheet.getLastRowNum()+1;i++){
				row = sheet.getRow(i);
				System.out.print(row.getCell(0).toString()+";");
				System.out.print(row.getCell(1).toString()+";");
				System.out.print(row.getCell(2).toString()+";");
				System.out.print(row.getCell(3).toString()+";");
				System.out.print(row.getCell(4).toString());
				System.out.println();
			}
		} catch (IOException e) {
			e.printStackTrace();
		}   

 

 

jar包：

WorkBook-jxl.jar

POI-HSSF-poi-3.5-FINAL-20090928.jar

POI-XSSF-poi-ooxml-3.8-beta3-20110606.jar,poi-ooxml-schemas-3.8-beta3-20110606.jar

地址：http://poi.apache.org/