java导入使用Apache导入Excel的简单实现
package com.bruce.geekway.tonglian.job;
import java.io.File;
import java.io.FileInputStream;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.Date;
import java.util.Iterator;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.bruce.geekway.utils.SeasonUtils;
public class TestImportHS300ExcelData {
public static void main(String[] args) throws Exception {
String[] fieldList = new String[]{"stockDate","open","high","low","close","turnover","volume"};
TestImportHS300ExcelData t = new TestImportHS300ExcelData();
t.importExcel(new File("/Users/yanghl/Downloads/hs30002.xlsx"),HS300StockData.class,fieldList);
}
public void importExcel(File file,Class clazz,String[] fieldList) throws Exception {
try {
Workbook book = null;
Sheet sheet = null;
// 得到工作表
if(file.getName().endsWith("xlsx")){
book = new XSSFWorkbook(new FileInputStream(file));
}else if(file.getName().endsWith("xls")){
book = new HSSFWorkbook(new FileInputStream(file));
}
// 得到第一页
sheet = book.getSheetAt(0);
// 得到第一面的所有行
Iterator<Row> row = sheet.rowIterator();
/**
* 标题解析
*/
// 得到第一行,也就是标题行
Row title = row.next();
// 得到第一行的所有列
Iterator<Cell> cellTitle = title.cellIterator();
// 循环标题所有的列
StringBuilder sb = new StringBuilder();
while (cellTitle.hasNext()) {
Cell cell = cellTitle.next();
sb.append(cell.getStringCellValue().trim()).append(" ");
}
System.out.println(sb.toString());
/**
* 解析内容行
*/
while (row.hasNext()) {
// 标题下的第一行
Row rown = row.next();
System.out.println(" row num = " + rown.getRowNum());
// 行的所有列
Iterator<Cell> cellbody = rown.cellIterator();
Object result = clazz.newInstance();
// 遍历一行的列
while (cellbody.hasNext()) {
// System.out.println("lastRow = " + sheet.getLastRowNum() + " nowRown =" +rown.getRowNum());
//cell.getNumericCellValue() 会有默认值 cell.toString() 不会有默认值
//遍历每一列
Cell cell = cellbody.next();
//反射获取对象的字段
Field filed = result.getClass().getDeclaredField(fieldList[cell.getColumnIndex()]);
filed.setAccessible(true);
if(filed.getType() == Date.class){
filed.set(result, cell.getDateCellValue());
continue;
}
if(filed.getType() == Double.class){
filed.set(result, cell.getNumericCellValue()==0 ? null : cell.getNumericCellValue());
continue;
}
if(filed.getType() == BigDecimal.class){
filed.set(result, getRoundForString(cell.toString()));
continue;
}
if(filed.getType() == Integer.class){
filed.set(result, StringUtils.isBlank(cell.toString()) ? null : Integer.parseInt(cell.toString()));
continue;
}
if(filed.getType() == String.class){
filed.set(result, cell.getStringCellValue());
}
}
System.out.println("++++" + SeasonUtils.getGson().toJson(result));
}
} catch (Exception e) {
e.printStackTrace();
throw e;
}
}
public static BigDecimal getRoundForString(String val) {
if(StringUtils.isBlank(val)) return null;
return new BigDecimal(val).setScale(2, BigDecimal.ROUND_HALF_UP);
}
}
class HS300StockData implements Serializable{
/**
*
*/
private static final long serialVersionUID = 1L;
private Date stockDate;
private String stockCode;
private String stockName;
private Double open ; //开盘价
private BigDecimal close; //收盘价
private BigDecimal high; //最低
private BigDecimal low; //最高
private BigDecimal preClose; //早日收盘价
private BigDecimal volume;//成交量
private BigDecimal turnover;//成交额
public Date getStockDate() {
return stockDate;
}
public void setStockDate(Date stockDate) {
this.stockDate = stockDate;
}
public String getStockCode() {
return stockCode;
}
public void setStockCode(String stockCode) {
this.stockCode = stockCode;
}
public String getStockName() {
return stockName;
}
public void setStockName(String stockName) {
this.stockName = stockName;
}
public Double getOpen() {
return open;
}
public void setOpen(Double open) {
this.open = open;
}
public BigDecimal getClose() {
return close;
}
public void setClose(BigDecimal close) {
this.close = close;
}
public BigDecimal getHigh() {
return high;
}
public void setHigh(BigDecimal high) {
this.high = high;
}
public BigDecimal getLow() {
return low;
}
public void setLow(BigDecimal low) {
this.low = low;
}
public BigDecimal getPreClose() {
return preClose;
}
public void setPreClose(BigDecimal preClose) {
this.preClose = preClose;
}
public BigDecimal getVolume() {
return volume;
}
public void setVolume(BigDecimal volume) {
this.volume = volume;
}
public BigDecimal getTurnover() {
return turnover;
}
public void setTurnover(BigDecimal turnover) {
this.turnover = turnover;
}
}
参考地址:
http://blog.csdn.net/hiping_cao/article/details/24460423
http://www.cnblogs.com/mabaishui/p/5826864.html