所需jar包:
xmlbeans-2.3.0.jar
poi-ooxml-schemas-3.8-20120326.jarpoi-ooxml-3.8-20120326.jar
poi-3.8-20120326.jar
dom4j-1.6.1.jar
// 本例为本地路径的文件,如果是从服务器或者插件上转之后的file对象,需要通过一定手段进行转换,
提供一种参考:
File tempFile = File.createTempFile("random", suffix);
FileUtils.writeByteArrayToFile(tempFile, file.getBytes());
/**
*
*/
package poi;
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 domain.Param;
/**
* @author liuyunlong
*/
public class ExcelPoi {
/**
* @param args
* @throws IOException
*/
public static void main(String[] args) throws IOException {
ExcelPoi excelPoi = new ExcelPoi();
String fileDir = "D:\\excel\\params2.xls";
int beginIndex = fileDir.lastIndexOf(".");
int endIndex = fileDir.length();
String suffix = fileDir.substring(beginIndex, endIndex);
List<Param> paramList = new ArrayList<Param>();
InputStream inputStream = new FileInputStream(fileDir);
if (".xls".equals(suffix)) {
paramList = excelPoi.readXls(inputStream);
} else {
paramList = excelPoi.readXlsx(inputStream);
}
for (int i = 0; i < paramList.size(); i++) {
System.out.println("file suffix= "+ suffix +" and params=====" + paramList.get(i).toString());
}
}
/**
* 后缀为.xlsx
*
* @param fileDir
* @throws IOException
*/
private List<Param> readXlsx(InputStream inputStream) throws IOException {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
List<Param> paramList = new ArrayList<Param>();
// 循环工作表Sheet
int sheetNumbers = xssfWorkbook.getNumberOfSheets();
for (int numSheet = 0; numSheet < sheetNumbers; numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
// 循环当前Sheet的行Row
int rowNumbers = xssfSheet.getLastRowNum();
for (int rowNum = 0; rowNum <= rowNumbers; rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow == null) {
continue;
}
// 循环当前Row的列Cell
Param param = new Param();
int cellNumbers = xssfRow.getLastCellNum();
if (2 == cellNumbers) {// 2列分别获取两列中的值作为key和value
for (int cellNum = 0; cellNum <= cellNumbers; cellNum++) {
XSSFCell xssfCell = xssfRow.getCell(cellNum);
if (xssfCell == null) {
continue;
}
if (cellNum == 0) {
param.setParamKey(xssfCell.toString());
} else if (cellNum == 1) {
param.setParamValue(xssfCell.toString());
}
}
} else if (1 == cellNumbers) {// 1列,必须用":"或":"分隔开,否则不解析
XSSFCell xssfCell = xssfRow.getCell(0);
String xssStr = xssfCell.toString();
if (null != xssfCell && (xssStr.indexOf(":") > -1 || xssStr.indexOf(":") > -1)) {
String[] paramFix = null;
if (xssStr.contains(":")) {
paramFix = xssStr.split(":");
} else if (xssStr.contains(":")) {
paramFix = xssStr.split(":");
}
if (null != paramFix && paramFix.length > 0) {
param.setParamKey(paramFix[0]);
param.setParamValue(paramFix[1]);
}
} else {
continue;
}
} else {// 非1或2列为非法文档,不解析
continue;
}
paramList.add(param);
}
}
return paramList;
}
/**
* 后缀为.xls
*
* @param fileDir
* @throws IOException
*/
private List<Param> readXls(InputStream inputStream) throws IOException {
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);
List<Param> paramList = new ArrayList<Param>();
// 循环工作表Sheet
int sheetNumbers = hssfWorkbook.getNumberOfSheets();
for (int numSheet = 0; numSheet < sheetNumbers; numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 循环当前Sheet的行Row
int rowNumbers = hssfSheet.getLastRowNum();
for (int rowNum = 0; rowNum <= rowNumbers; rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow == null) {
continue;
}
// 循环当前Row的列Cell
Param param = new Param();
int cellNumbers = hssfRow.getLastCellNum();
if (2 == cellNumbers) {// 2列分别获取两列中的值作为key和value
for (int cellNum = 0; cellNum <= cellNumbers; cellNum++) {
HSSFCell hssfCell = hssfRow.getCell(cellNum);
if (hssfCell == null) {
continue;
}
if (cellNum == 0) {
param.setParamKey(hssfCell.toString());
} else if (cellNum == 1) {
param.setParamValue(hssfCell.toString());
}
}
} else if (1 == cellNumbers) {// 1列,必须用":"或":"分隔开,否则不解析
HSSFCell hssfCell = hssfRow.getCell(0);
String hssStr = hssfCell.toString();
if (null != hssfCell && (hssStr.indexOf(":") > -1 || hssStr.indexOf(":") > -1)) {
String[] paramFix = null;
if (hssStr.contains(":")) {
paramFix = hssStr.split(":");
} else if (hssStr.contains(":")) {
paramFix = hssStr.split(":");
}
if (null != paramFix && paramFix.length > 0) {
param.setParamKey(paramFix[0]);
param.setParamValue(paramFix[1]);
}
} else {
continue;
}
} else {// 非1或2列为非法文档,不解析
continue;
}
paramList.add(param);
}
}
return paramList;
}
}
/**
*
*/
package domain;
/**
* @author liuyunlong
*
*/
public class Param {
public Param() {
super();
}
private String paramKey;
private String paramValue;
public String getParamKey() {
return paramKey;
}
public void setParamKey(String paramKey) {
this.paramKey = paramKey;
}
public String getParamValue() {
return paramValue;
}
public void setParamValue(String paramValue) {
this.paramValue = paramValue;
}
@Override
public String toString() {
return "Param [paramKey=" + paramKey + ", paramValue=" + paramValue + "]";
}
}