- 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());
- }
- }
- }