poi 导入excel 数据转对象
package org.rui.xls;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedList;
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.rui.bean.User;
public class ExportXls
{
public static void main(String[] args) throws FileNotFoundException,
IOException
{
List<User> list = new LinkedList<User>();
String file = "C:/Users/lenovo/Downloads/营销空间数据导入模板.xls";
// 创建对Excel工作簿文件的引用
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file));
// 本例是按名引用(让我们假定那张表有着缺省名"Sheet1")
// HSSFSheet sheet = workbook.getSheet("Sheet1");
HSSFSheet sheet = workbook.getSheetAt(0);
// 读取一行
// HSSFRow row = sheet.getRow(0);
// it读取行
Iterator<HSSFRow> itRow = sheet.rowIterator();
int j = 0;
while (itRow.hasNext()) {
HSSFRow row = itRow.next();
// 读行格
Iterator<HSSFCell> it = row.cellIterator();
int cellIndex = 0;
User user = new User();
while (it.hasNext()) {
HSSFCell ce = it.next();
// 检查是否合法
if (j == 0) {
String titleName = ce.getStringCellValue();
Class clz = user.getClass();
if (!isOk(clz, titleName)) {
System.out.println("表格格式不符合导入的数据格式!");
return;
}
} else {
switch (cellIndex)
{
case 0:// 第一格
Double d = ce.getNumericCellValue();
Integer id = Integer.parseInt(new DecimalFormat("0")
.format(d));
user.setId(id);
break;
case 1:
user.setName(ce.getStringCellValue());
break;
case 2:
// DecimalFormat df = new DecimalFormat("#.00");
// String Stringd = df.format(ce.getNumericCellValue());
user.setPrice(ce.getNumericCellValue());
break;
case 3:
user.setDate(ce.getDateCellValue());
break;
default:
break;
}
}
cellIndex++;
}
if (j != 0) {
list.add(user);
}
j++;
}
System.out.println("=============================================");
for (User u : list) {
System.out.println(u.getId() + " \t " + "name:" + u.getName()
+ " \t " + u.getPrice() + " \t " + u.getDate());
}
}
/**
* 检查表格是否和对象一致
*
* @param clz
* @param titleName
* @return
*/
public static boolean isOk(Class clz, String titleName)
{
boolean isExist = false;
Field[] fa = clz.getDeclaredFields();
for (int i = 0; i < fa.length; i++) {
// System.out.println(fa[i].getName());
if (titleName.equals(fa[i].getName())) {
isExist = true;
break;
}
}
return isExist;
}
}
/**
* output:
* =============================================
1 name:粘地 1.0 Fri Oct 10 00:00:00 CST 2014
2 name:小夺 555.0 Fri Oct 10 00:00:00 CST 2014
3 name:无可奈何花落去 66.0 Fri Oct 10 00:00:00 CST 2014
4 name:夺 88.88 Fri Oct 10 00:00:00 CST 2014
5 name:魂牵梦萦 55.0 Fri Oct 10 00:00:00 CST 2014
* ***/
//之前例子,这样看比较好理解
// 读取数据
// if (HSSFCell.CELL_TYPE_NUMERIC == ce.getCellType()) {
//
// /** 在excel里,日期也是数字,在此要进行判断 */
// if (HSSFDateUtil.isCellDateFormatted(ce)) {
// DateFormat format = new SimpleDateFormat(
// "yyyy/MM/dd HH:mm:ss");
// System.out.println("date:"
// + format.format(ce.getDateCellValue()));
// } else {
// System.out.println("numeric:"
// + ce.getNumericCellValue() + "");
// }
// } else if (HSSFCell.CELL_TYPE_STRING == ce.getCellType()) {
// System.out.println("x:" + ce.getStringCellValue());
//
package org.rui.bean;
import java.lang.reflect.Field;
import java.util.Date;
public class User
{
private Integer id;
private String name;
private Double price;
private Date date;
public Integer getId()
{
return id;
}
public void setId(Integer id)
{
this.id = id;
}
public String getName()
{
return name;
}
public void setName(String name)
{
this.name = name;
}
public Double getPrice()
{
return price;
}
public void setPrice(Double price)
{
this.price = price;
}
public Date getDate()
{
return date;
}
public void setDate(Date date)
{
this.date = date;
}
public static void main(String[] args)
{
Class clz = User.class;
Field[] fa = clz.getDeclaredFields();
for (int i = 0; i < fa.length; i++) {
System.out.println(fa[i].getName());
}
}
}