java使用poi解析Excel文件

本文取自http://www.cnblogs.com/hongten/p/java_poi_excel_xls_xlsx.html

java中读取Excel文件并解析

• Excel2007及以前的文件使用[HSSFWorkbook]6
• Excel2007后的文件使用[XSSFWorkbook]6
  

  ---

使用poi的jar包。

Common.java

public class Common {public static final String OFFICE_EXCEL_2003_POSTFIX &＃61; "xls";public static final String OFFICE_EXCEL_2010_POSTFIX &＃61; "xlsx";public static final String EMPTY &＃61; "";public static final String POINT &＃61; ".";public static final String NOT_FILE &＃61; "Not File!";
}

Util.java

public class Util {public static String getPostfix(String path){if( path &＃61;&＃61; null || Common.EMPTY.equals(path.trim())){return Common.EMPTY;}if(path.contains(Common.POINT)){return path.substring(path.lastIndexOf(Common.POINT) &＃43; 1,path.length());}return Common.EMPTY;}
}

ReadExcel.java

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import com.manniu.ninecloud.entities.Devices;/*** &＃64;Description 解析Excel文件* &＃64;data 2015-06-01* &＃64;author guo*/
public class ReadExcel {public List<Devices> readExcel(String path) throws IOException{if (path &＃61;&＃61; null || Common.EMPTY.equals(path)) {return null;}else {String postfix &＃61; Util.getPostfix(path);if(!Common.EMPTY.equals(postfix)){if(Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)){return readXlsx(path);}else if (Common.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {return readXls(path);}} else {System.out.println(path&＃43;Common.NOT_FILE);}}return null;}/*** &＃64;读取2007及之前的Excel文档*/public List<Devices> readXlsx(String path) throws IOException {InputStream io &＃61; new FileInputStream(path);XSSFWorkbook xsBook &＃61; new XSSFWorkbook(io);Devices devices &＃61; null;List<Devices> list &＃61; new ArrayList<Devices>();//循环行for (int rowNum &＃61; 0; rowNum (); rowNum&＃43;&＃43;) {XSSFSheet xSheet &＃61; xsBook.getSheetAt(rowNum);if (xSheet &＃61;&＃61; null) {continue;}for (int num &＃61; 1; num (); num&＃43;&＃43;) {XSSFRow xRow &＃61; xSheet.getRow(num);if (xRow !&＃61; null) {devices &＃61; new Devices();devices.setState(Integer.parseInt(getValue(xRow.getCell(0))));devices.setType(Integer.parseInt(getValue(xRow.getCell(1))));devices.setModel(getValue(xRow.getCell(2)));devices.setVer(getValue(xRow.getCell(3)));devices.setPn(getValue(xRow.getCell(4)));devices.setVn(getValue(xRow.getCell(5)));devices.setSn(getValue(xRow.getCell(6)));list.add(devices);}}}return list;}//读取Excel2007前的.xls文件public List<Devices> readXls(String path) throws IOException {InputStream io &＃61; new FileInputStream(path);HSSFWorkbook hBook &＃61; new HSSFWorkbook(io);Devices devices &＃61; null;List<Devices> list &＃61; new ArrayList<Devices>();for (int rowNum &＃61; 0; rowNum (); rowNum&＃43;&＃43;) {HSSFSheet hSheet &＃61; hBook.getSheetAt(rowNum);if (hSheet &＃61;&＃61; null) {continue;}for (int num &＃61; 1; num (); num&＃43;&＃43;) {HSSFRow sRow &＃61; hSheet.getRow(num);if (sRow !&＃61; null) {devices &＃61; new Devices();devices.setState(Integer.parseInt(getValue(sRow.getCell(0))));devices.setType(Integer.parseInt(getValue(sRow.getCell(1))));devices.setModel(getValue(sRow.getCell(2)));devices.setVer(getValue(sRow.getCell(3)));devices.setPn(getValue(sRow.getCell(4)));devices.setVn(getValue(sRow.getCell(5)));devices.setSn(getValue(sRow.getCell(6)));list.add(devices);}}}return list;}&＃64;SuppressWarnings("static-access")private String getValue(XSSFCell xCell){if(xCell.getCellType() &＃61;&＃61; xCell.CELL_TYPE_BOOLEAN){return String.valueOf(xCell.getBooleanCellValue());}else if (xCell.getCellType() &＃61;&＃61; xCell.CELL_TYPE_NUMERIC) {return String.valueOf(xCell.getNumericCellValue());}else {return String.valueOf(xCell.getStringCellValue());} }&＃64;SuppressWarnings("static-access")private String getValue(HSSFCell hCell){if (hCell.getCellType() &＃61;&＃61; hCell.CELL_TYPE_BOOLEAN) {return String.valueOf(hCell.getBooleanCellValue());}else if (hCell.getCellType() &＃61;&＃61; hCell.CELL_TYPE_NUMERIC) {return String.valueOf(hCell.getNumericCellValue());}else {return String.valueOf(hCell.getStringCellValue());}}//Test mainpublic static void main(String[] args) throws IOException {String excel2003 &＃61; "D://test/test.xls";String excel2010 &＃61; "D://test/test1.xlsx";ReadExcel re &＃61; new ReadExcel();List list &＃61; re.readExcel(excel2003);if(list !&＃61; null){for (Devices devices : list) {System.out.println(devices.getState()&＃43;"--"&＃43;devices.getType());}}}
}

poi 包下载地址