java解析Excel

package cn.com.dhcc.app.excel.service;


import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


import org.apache.log4j.Logger;
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 org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;


/**
 * 读取Excel
 * 需要自己新建一个实体类（WrStzrnsInfo）
 * @日期:2016-1-26上午9:16:37
 * @作者:hp
 * @版权所有：
 */
@Service
public class ReadExcel{
Logger logger = Logger.getLogger("MAIL");


public static DateFormat dtFmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";
public static final String EMPTY = "";
public static final String POINT = ".";
public static final String NOT_EXCEL_FILE = " : 文件不存在!";

/**
* 实时监测数据
* 读取Excel写入文件
* @param path [文件路径]
* @return [数据列表]
* @throws IOException
*/
public List<WrStzrnsInfo> readDeteExcel(String path) throws IOException {
if (path == null || EMPTY.equals(path)) {
return null;
} else {
String postfix = getPostfix(path);
if (!EMPTY.equals(postfix)) {
File file = new File(path);
if (!file.exists()) {
return null;
}
if (OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
return readXls(path);
} else if (OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
return readXlsx(path);
}
} else {
logger.debug(path + NOT_EXCEL_FILE);
}
}


return null;
}


/**
* 实时监测数据
* 读取 Excel 2010
* @param path [文件路径]
* @return
* @throws IOException
*/
public List<WrStzrnsInfo> readXlsx(String path) throws IOException {
InputStream is = new FileInputStream(path);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
WrStzrnsInfo WrStzrnsInfo = null;
List<WrStzrnsInfo> list = new ArrayList<WrStzrnsInfo>();
// 读表
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
// 读取行
for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow != null) {
WrStzrnsInfo = new WrStzrnsInfo();
XSSFCell stcd = xssfRow.getCell(0);
XSSFCell stNm = xssfRow.getCell(1);
XSSFCell tm = xssfRow.getCell(2);
XSSFCell upZ = xssfRow.getCell(3);
XSSFCell downZ = xssfRow.getCell(4);
XSSFCell q = xssfRow.getCell(5);
WrStzrnsInfo.setStcd(getValue(stcd));
WrStzrnsInfo.setStNm(getValue(stNm));
try {
WrStzrnsInfo.setTm(dtFmt.parse(getValue(tm)));
} catch (Exception e) {
throw new IOException("(时间格式错误)");
}
WrStzrnsInfo.setUpZ(Double.valueOf(getValue(upZ)));
WrStzrnsInfo.setDownZ(Double.valueOf(getValue(downZ)));
WrStzrnsInfo.setQ(Double.valueOf(getValue(q)));
WrStzrnsInfo.setTs(new Date());
list.add(WrStzrnsInfo);
}
}
}
return list;
}


/**
* 实时监测数据
* 读取 Excel 2003-2007
* @param path [文件路径]
* @return
* @throws IOException
*/
public List<WrStzrnsInfo> readXls(String path) throws IOException {
InputStream is = new FileInputStream(path);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
WrStzrnsInfo WrStzrnsInfo = null;
List<WrStzrnsInfo> list = new ArrayList<WrStzrnsInfo>();
// 读表
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 读取行
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow != null) {
WrStzrnsInfo = new WrStzrnsInfo();
HSSFCell stcd = hssfRow.getCell(0);
HSSFCell stNm = hssfRow.getCell(1);
HSSFCell tm = hssfRow.getCell(2);
HSSFCell upZ = hssfRow.getCell(3);
HSSFCell downZ = hssfRow.getCell(4);
HSSFCell q = hssfRow.getCell(5);
WrStzrnsInfo.setStcd(getValue(stcd));
WrStzrnsInfo.setStNm(getValue(stNm));
try {
WrStzrnsInfo.setTm(dtFmt.parse(getValue(tm)));
} catch (Exception e) {
throw new IOException("(时间格式错误)");
}
WrStzrnsInfo.setUpZ(Double.valueOf(getValue(upZ)));
WrStzrnsInfo.setDownZ(Double.valueOf(getValue(downZ)));
WrStzrnsInfo.setQ(Double.valueOf(getValue(q)));
WrStzrnsInfo.setTs(new Date());
list.add(WrStzrnsInfo);
}
}
}
return list;
}


@SuppressWarnings("static-access")
private String getValue(XSSFCell xssfRow) {
if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
return String.valueOf(xssfRow.getBooleanCellValue());
} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
return String.valueOf(xssfRow.getNumericCellValue());
} else {
return String.valueOf(xssfRow.getStringCellValue());
}
}


@SuppressWarnings("static-access")
private String getValue(HSSFCell hssfCell) {
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
return String.valueOf(hssfCell.getNumericCellValue());
} else {
return String.valueOf(hssfCell.getStringCellValue());
}
}


/**
* 获取Excel的文件格式
* @param path [文件路径]
* @return
*/
public String getPostfix(String path) {
if (path == null || EMPTY.equals(path.trim())) {
return EMPTY;
}
if (path.contains(POINT)) {
return path.substring(path.lastIndexOf(POINT) + 1, path.length());
}
return EMPTY;
}

public static void main(String[] args) throws IOException {
String LIB_PATH = "E://MailXlsx";
String STUDENT_INFO_XLS_PATH = LIB_PATH
+ "/实时监测数据_2016012608" + POINT + OFFICE_EXCEL_2003_POSTFIX;
String STUDENT_INFO_XLSX_PATH = LIB_PATH
+ "/实时监测数据_2016012608" + POINT + OFFICE_EXCEL_2010_POSTFIX;
String excel2003_2007 = STUDENT_INFO_XLS_PATH;
String excel2010 = STUDENT_INFO_XLSX_PATH;

// List<WrStzrnsInfo> list = new ReadExcel().readDeteExcel(excel2010);
// if (list == null) {
// list = new ReadExcel().readDeteExcel(excel2003_2007);
// }
// if (list != null) {
// for (WrStzrnsInfo vo : list) {
// System.out.println("stcd: " + vo.getStcd() + ", st_nm: "
// + vo.getStNm() + ", down_z: " + vo.getDownZ()
// + ", UpZ: " + vo.getUpZ() + ", " + "tm:" + vo.getTm()
// + ", q: " + vo.getQ());
// }
// }
}
}