java 用poi框架读取excel 2010

1、下载 最新的 poi  http://poi.apache.org/download.html 

    

2、解压 把相关jar包引进项目 ,excel 2010 用的是 XSSH 开头的相关包，相关的类在 名称包含OOXML的相关jar包内。

 

3、下面是一个简单的例子

     import java.io.BufferedInputStream;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.text.DecimalFormat;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Arrays;

import java.util.Date;

import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;

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;

public class Excel2010Operation {

 

public static void main(String[] args) throws Exception {

// File file = new File("src\\excelResourceFile\\privilege.xlsx");

File file = new File("src\\excelUserFile\\excelUserFile.xlsx");

Excel2010Operation excel = new Excel2010Operation();

List> workBook = excel.getData(file,"all", 0);

for (List sheet : workBook) {

 

for (String[] row : sheet) {

 

for (int col = 0; col < row.length; col++) {

System.out.print(row[col] + "\t");

}

System.out.println();

}

System.out.println();

System.out.println("……………… one sheet read end …………………………………");

System.out.println();

}

}

 

 //ignoreRows 表示读取某个表时，忽略的行数。

public List> getData(File file,String privilegeType,int ignoreRows)

throws FileNotFoundException, IOException {

List> sheets = new ArrayList>();// 一个list存储一个工作簿

BufferedInputStream in = new BufferedInputStream(new FileInputStream(

file));

XSSFWorkbook workbook = new XSSFWorkbook(in);

// start read a sheet

sheets = this.getWorkBook(workbook, privilegeType, ignoreRows);

// end read a sheet

in.close();

return sheets;

}

// start 获取excle 一个 工作簿(workBook)的记录

public List> getWorkBook(XSSFWorkbook workbook,String privilegeType,

int ignoreRows) {

// 一个list存储一个工作簿

List> sheets = new ArrayList>();

for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {

List result = new ArrayList();// 一个list存储一个sheet的所有行

XSSFSheet sheet = workbook.getSheetAt(sheetIndex);

String sheetName=sheet.getSheetName(); //获取sheet的名字

 

if("ALL".equalsIgnoreCase(privilegeType)){}

else if(!sheetName.equalsIgnoreCase(privilegeType) ){

continue;

}

//System.out.println(sheetName);

// 获取表格，ignoreRows为忽略的行数；

result = getExcelSheet(sheet, ignoreRows);

sheets.add(result);

}

return sheets;

}

// end 获取excle 一个 工作簿(workBook)的记录

// start 获取excle 一个表(sheet)的记录

public List getExcelSheet(XSSFSheet xssfSheet, int ignoreRows) {

XSSFSheet sheet = xssfSheet;

// 一个list存储一个workBook的所有行

List result = new ArrayList();

 

for (int rowIndex = ignoreRows; rowIndex <= sheet.getLastRowNum(); rowIndex++) {

XSSFRow row = sheet.getRow(rowIndex);

if (row == null) {

                 continue;

                 }

 

String values[] = getExcelRole(row);

result.add(values);

}

return result;

}

// end 获取excle 一个表(sheet)的记录

// start 获取excle 一行的记录

public String[] getExcelRole(XSSFRow xSSFRow) {

XSSFRow row = xSSFRow;

int rowSize = 0;

XSSFCell cell = null;

if (row == null) {

            System.out.println("空空");

return null;

}

int tempRowSize = row.getLastCellNum() + 1;// 获取列数

if (tempRowSize > rowSize) {

rowSize = tempRowSize;// 获取列数

}

String[] values = new String[rowSize];// values store a row 's all

// column value;

Arrays.fill(values, "");

boolean hasValue = false;

// start all column for a row

for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {

String value = "";

cell = row.getCell(columnIndex);

if (cell != null) {

// 注意：一定要设成这个，否则可能会出现乱码

// cell.setEncoding(HSSFCell.ENCODING_UTF_16);

switch (cell.getCellType()) {

case XSSFCell.CELL_TYPE_STRING:

value = cell.getStringCellValue();

break;

case XSSFCell.CELL_TYPE_NUMERIC:

if (HSSFDateUtil.isCellDateFormatted(cell)) {

Date date = cell.getDateCellValue();

if (date != null) {

value = new SimpleDateFormat("yyyy-MM-dd")

.format(date);

} else {

value = "";

}

} else {

value = new DecimalFormat("0").format(cell

.getNumericCellValue());

}

break;

case XSSFCell.CELL_TYPE_FORMULA:

// 导入时如果为公式生成的数据则无值

if (!cell.getStringCellValue().equals("")) {

value = cell.getStringCellValue();

} else {

value = cell.getNumericCellValue() + "";

}

break;

case XSSFCell.CELL_TYPE_BLANK:

break;

case XSSFCell.CELL_TYPE_ERROR:

value = "";

break;

case XSSFCell.CELL_TYPE_BOOLEAN:

value = (cell.getBooleanCellValue() == true ? "Y"

: "N");

break;

default:

value = "";

}

}

if (columnIndex == 0 && value.trim().equals("")) {

break;

}

values[columnIndex] = this.rightTrim(value);

hasValue = true;

}

return values;

}

// end 获取excle 一行的记录

public String rightTrim(String str) {

if (str == null) {

return "";

}

int length = str.length();

for (int i = length - 1; i >= 0; i--) {

if (str.charAt(i) != 0x20) {

break;

}

length--;

}

return str.substring(0, length);

}

}