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中所有的 sheetsheet=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;    } }