java操作excel

java 解析excel （jxl），并转化为对象

public static List<Object> getListForObject(File file, Class<?> obj) {
        List<Object> list=new ArrayList<Object>();
        Map<String,Object> methods = new HashMap<String,Object>();
        Method m[] = HgEnterpriseTown.class.getMethods();
        for (int i = 0; i < m.length; i++) {
            Method method = m[i];
            String methodName = method.getName().toUpperCase();
            methods.put(methodName, method);
        }
        Workbook book;
        try {
            WorkbookSettings workbookSettings = new WorkbookSettings();
            workbookSettings.setEncoding("UTF-8");
            book = Workbook.getWorkbook(file, workbookSettings);
            Sheet sheet = book.getSheet(0);
            for (int i = 1; i < sheet.getRows(); i++) {
                Object o = HgEnterpriseTown.class.newInstance();
                for (int j = 0; j < sheet.getColumns(); j++) {
                    Cell cell = sheet.getCell(j, i);
                    Cell cell2 = sheet.getCell(j, 0);
                    String result = cell.getContents();
                    String result2 = cell2.getContents();
                    Object r=null;
                    Method setMethod = (Method) methods.get("SET"
                            + result2.toUpperCase());
                    if(setMethod!=null){
                        Class<?>[] parameterTypes = setMethod.getParameterTypes();
                        for(Class<?> c : parameterTypes){
                            if(c==int.class){
                                r=Integer.parseInt(result);
                            }else if(c==String.class){
                                r=result;
                            }
                        }
                    }
                    if(r!=null){
                    setMethod.invoke(o, r);
                    }
                }
                list.add(o);
            }
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

poi

package myexceltest;
import jxl.*;
import java.io.*;
import jxl.write.*;
/**
 * <p>java读取Excel表格,拷贝、更新Excel工作薄 </p>
 * <p>Description: Java开发人员可以读取Excel文件的内容,更新Excel工作薄,开发人员
 *     也可以用程序生成新的Excel表格,不过我觉得在我们工作中几乎没有这方面需求,我
 *     就不再列出来了,如果有哪位同事需要的话,我再把程序写出来
 * </p>
 * <p>Copyright: Copyright (c) KongZhong Corparation 2005</p>
 * <p>程序开发环境为jb9</p>
 * @author 张丽鹏
 * @version 1.0
 */
public class Test1 {
  public static void main(String[] args) {
    jxl.Workbook rwb = null;
    try{
      //构建Workbook对象, 只读Workbook对象
      //直接从本地文件创建Workbook
      //从输入流创建Workbook
      InputStream is = new FileInputStream("D:/jb9Test/MyExcelTest/Book1.xls");
      rwb = Workbook.getWorkbook(is);
      //Sheet(术语：工作表)就是Excel表格左下角的Sheet1,Sheet2,Sheet3但在程序中
      //Sheet的下标是从0开始
      //获取第一张Sheet表
       Sheet rs = rwb.getSheet(0);
       //获取Sheet表中所包含的总列数
       int rsColumns = rs.getColumns();
       //获取Sheet表中所包含的总行数
       int rsRows = rs.getRows();
       //获取指定单元格的对象引用
       for(int i=0;i<rsRows;i++){
         for(int j=0;j<rsColumns;j++){
           Cell cell = rs.getCell(j,i);
           System.out.print(cell.getContents()+" ");
         }
         System.out.println();
       }
       //利用已经创建的Excel工作薄创建新的可写入的Excel工作薄
       jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(new File("D:/jb9Test/MyExcelTest/Book2.xls"),rwb);
       //读取第一张工作表
       jxl.write.WritableSheet ws = wwb.getSheet(0);
       //获得第一个单元格对象
       jxl.write.WritableCell wc = ws.getWritableCell(0, 0);
       //判断单元格的类型, 做出相应的转化
       if (wc.getType() == CellType.LABEL) {
         Label l = (Label) wc;
         l.setString("The value has been modified.");
       }
       //写入Excel对象
       wwb.write();
       wwb.close();
    }catch(Exception e){
      e.printStackTrace();
    }
    finally{
      //操作完成时，关闭对象，释放占用的内存空间
      rwb.close();
    }
  }
}

读取excel文件并转换成javabean

HSSFWorkbook wb=null;
HSSFSheet sheet=null;
FileInputStream fw=null;
/*要想把excel中的每一行数据转换成javabean对象则用反射技术
* javabean中的属性个数要与excel中的列数一样，不然可能报错,属性顺序与列的顺序也要一样
* */

try{
fw=new FileInputStream("F:/atguigu download/person.xls");
POIFSFileSystem fs=new POIFSFileSystem(fw);
wb=new HSSFWorkbook(fs);
fw.close();
}catch(IOException e){
e.printStackTrace();
}finally{
if(fw!=null){
fw.close();
}
}
HSSFRow row=null;
List<Person> dataList=new ArrayList<Person>();
for(int i=0;i<wb.getNumberOfSheets();i++){
//循环excel中所有的 sheet
sheet=wb.getSheetAt(i);
for(int j=1;j<sheet.getPhysicalNumberOfRows();j++){
//循环每一个sheet中的每一行
row=sheet.getRow(j);
Person person=new Person();
Field[] beanFiled=person.getClass().getDeclaredFields(); 
for(int z=0;z<row.getPhysicalNumberOfCells();z++){
//循环每一行中的所有列,就是得到单元格中的数据 
try {
//强制反射,让private 的属性也可以访问
beanFiled[z].setAccessible(true);
//把得到的属性进行赋值，就是把读取到的单元格中的数据赋给对应的属性
beanFiled[z].set(person, getStrinCellValue(row.getCell(z)));
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
dataList.add(person);
}
}
for(Person p : dataList){
System.out.println(p.getPersonNo()+"***"+p.getUserName()+"***"+p.getClasses()+"***"+p.getSex()+"***"+p.getClasses());
}

例

package com.berchina.iec.agency.util.execl;

import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.berchina.iec.agency.util.ConvertUtil;
import com.berchina.iec.agency.util.StringUtils;

public class T123 {

    private static Logger logger = LogManager.getLogger(ReaderFileUtil.class);

    public static final String READER_BASE_ROOT = "io";

    public static final String START_INDEX = "startRow";

    public static final String TITLES = "titles";

    /**
     * fullFilePath 目标excel的磁盘路径
     * clz 需要转换对象的class
     * @param fullFilePath
     * @param clz
     * @return
     * @throws Exception
     */
    @SuppressWarnings("unchecked")
    public static <T> List<T> transToObject(String fullFilePath,Class<T> clz) throws Exception{
        InputStream is = null;
        try {
            is = new FileInputStream(fullFilePath);
            Workbook wb = ReaderFileUtil.loadWorkBookByPath(fullFilePath,is);
            Sheet sheet = wb.getSheetAt(0);
            Map<String,Object> map = readExcelTitle(sheet);
            int start = Integer.parseInt(map.get(START_INDEX).toString());
            List<String> titles = (List<String>)map.get(TITLES);
            int last = sheet.getLastRowNum();
            List<T> lst = new ArrayList<T>();
            for(int i = (start+1);i<=last;i++){
                List<String> values = getRowValues(sheet, i);
                T t = transToObject(titles, values, clz);
                lst.add(t);
            }
            return lst;
        } catch (Exception e) {
            throw e;
        }finally{
            if(is!=null){
                is.close();
            }
        }
    }

    private static <T> T transToObject(List<String> titles,List<String> values,Class<T> clz) throws Exception{
        T t = clz.newInstance();
        int size = titles.size();
        for(int i = 0;i<size;i++){
            if(values.size()<=i){
                break;
            }
            String title = titles.get(i);
            String value = values.get(i);
            setValue(t,clz,title,value);
        }
        return t;
    }

    private static void setValue(Object o,Class<?>clz,String title,String value) throws Exception{
        Method m = null;
        if(title.indexOf(".")!=-1){
            String[] titleSplit = title.split("\\.");
            m = getSetMethod(titleSplit, clz);
        }else{
            m = getSetMethod(title, clz);
        }
        if(m == null){
            logger.info(title+"在"+clz.getName()+"中不存在");
            return;
        }
        setValue(o, m, title,value);
    }

    private static void setValue(Object o,Method method,String title,String value) throws Exception{
        Class<?>[] clazz = method.getParameterTypes();  
        String type = clazz.getName();
        if(StringUtils.isEmpty(value)){
            return;
        }
        if("java.lang.String".equals(type)){
            method.invoke(o, value);
        }else if("java.util.Date".equals(type)){
            Date d = null;
            if(value.length()>10){
                d = ConvertUtil.convertObj2Time(value);
            }else{
                d = ConvertUtil.convertObj2Date(value);
            }
            method.invoke(o, d);
        }else if("java.lang.Integer".equals(type)||"int".equals(type)){
            Integer i = ConvertUtil.convertObj2Int(value);
            method.invoke(o, i);
        }else if("java.lang.Long".equals(type)||"long".equals(type)){
            Long l = ConvertUtil.convertObj2Long(value);
            method.invoke(o, l);
        }else if("java.lang.Short".equals(type)||"short".equals(type)){
            Short s = ConvertUtil.convertObj2Short(value);
            method.invoke(o, s);
        }else if("java.lang.Boolean".equals(type)||"boolean".equals(type)){
            Boolean b = ConvertUtil.convertObj2Boolean(value);
            method.invoke(o, b);
        }else if("java.math.BigDecimal".equals(type)){
            BigDecimal b = ConvertUtil.convertObj2BigDecimal(value);
            method.invoke(o, b);
        }else{
            Method getMethodName = o.getClass().getMethod(method.getName().replace("set", "get"));
            Object returnValue = getMethodName.invoke(o);
            Class<?> returnClass = Class.forName(type);
            if(returnValue == null){
                returnValue = returnClass.newInstance();
                method.invoke(o, returnValue);
            }
            title = title.substring(title.indexOf(".")+1);
            setValue(returnValue, returnClass, title, value);
        }
    }

    private static Method getSetMethod(String propName,Class<?> clz){
        Method[]methods = clz.getMethods();
        for(Method method : methods){
            if(method.getName().toLowerCase().equals("set"+propName.toLowerCase())){
                Class<?>[] clazz = method.getParameterTypes();
                if(clazz.length == 1){
                    return method;
                }
            }
        }
        return null;
    }

    private static Map<String,Object> readExcelTitle(Sheet sheet) throws Exception{
        int m = 0;
        Map<String,Object> map = new HashMap<String,Object>();
        int last = sheet.getLastRowNum();
        while(m<=last){
            Cell cell = sheet.getRow(m).getCell(0);
            if(cell!=null){
                String cellValue = cell.getStringCellValue();
                if(!cellValue.startsWith("#")){
                    List<String> lstStr = getRowValues(sheet, m);
                    map.put(START_INDEX, m);
                    map.put(TITLES, lstStr);
                    return map;
                }
            }
            m++;
        }
        throw new Exception("Excel格式不正确");
    }

    /**
     * 获得行数据
     * @param sheet
     * @param rowIndex
     * @return
     */
    private static List<String> getRowValues(Sheet sheet,int rowIndex){
        List<String> lstStr = new ArrayList<String>();
        Row row = sheet.getRow(rowIndex);
        int last = row.getLastCellNum();
        for(int i = 0;i<last;i++){
            lstStr.add(getCellValue(row, i));
        }
        return lstStr;
    }

    /**
     * 获得列数据
     * @param row
     * @param colIndex
     * @return
     */
    private static String getCellValue(Row row,int colIndex){
        String cellValue = "";
        if(colIndex < row.getFirstCellNum()){
            cellValue = "";
        }else{
            Cell cell = row.getCell(colIndex);
            if(cell == null){
                cellValue = "";
            }else{
                int cellType = cell.getCellType();
                if(Cell.CELL_TYPE_FORMULA == cellType){
                    cellType = cell.getCachedFormulaResultType();
                }
                if(Cell.CELL_TYPE_BLANK ==cellType){
                    cellValue = "";
                }else if(Cell.CELL_TYPE_BOOLEAN == cellType){
                    Boolean b = cell.getBooleanCellValue();
                    cellValue = b.toString();
                }else if(Cell.CELL_TYPE_ERROR == cellType){
                    cellValue = "";
                }else if(Cell.CELL_TYPE_NUMERIC == cellType){
                    //判断cell是否为日期格式
                    if(isCellDateFormatted(cell)){
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        if(cell.getDateCellValue()!=null){
                            cellValue = sdf.format(cell.getDateCellValue());
                        }
                    }else if(isCellTimeFormatted(cell)){
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        if(cell.getDateCellValue()!=null){
                            cellValue = sdf.format(cell.getDateCellValue());
                        }
                    }else{
                        double d = cell.getNumericCellValue();
                        cellValue = String.format("%.2f", d);
                        if(cellValue.matches("^\\d+\\.0+$")){
                            DecimalFormat df = new DecimalFormat("#");
                            cellValue = df.format(d);
                        }
                    }
                }else if(Cell.CELL_TYPE_STRING == cellType){
                    cellValue = cell.getStringCellValue();
                }
            }
        }
        if(cellValue!=null){
            cellValue = cellValue.trim();
        }
        return cellValue;
    }

    // 判断cell是否为日期格式
    public static boolean isCellDateFormatted(Cell cell) {
        if (cell == null)
            return false;
        boolean bDate = false;

        double d = cell.getNumericCellValue();
        if (DateUtil.isValidExcelDate(d)) {
            CellStyle style = cell.getCellStyle();
            if (style == null)
                return false;
            int i = style.getDataFormat();
            String f = style.getDataFormatString();
            if (f.indexOf("y") > -1 && f.indexOf("m") > -1
                    && f.indexOf("d") > -1) {
                return true;
            }
        }
        return bDate;
    }

    public static boolean isCellTimeFormatted(Cell cell) {
        if (cell == null)
            return false;
        boolean bDate = false;

        double d = cell.getNumericCellValue();
        if (DateUtil.isValidExcelDate(d)) {
            CellStyle style = cell.getCellStyle();
            if (style == null)
                return false;
            int i = style.getDataFormat();
            String f = style.getDataFormatString();
            if (f.indexOf("mm") > -1 && f.indexOf("ss") > -1
                    && f.indexOf("h") > -1) {
                return true;
            }
        }
        return bDate;
    } 

}