poi 導入excel 數據轉對象

poi 導入excel 數據轉對象

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 list = new LinkedList();

		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 itRow = sheet.rowIterator();
		
		int j = 0;
		while (itRow.hasNext()) {
			HSSFRow row = itRow.next();
			// 讀行格
			Iterator 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());
		}

	}

}