parseExcel

java 代码

/**
 *
 */
package cn.flyingsoft.oais.service.exchange.entity.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
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 cn.flyingsoft.oais.di.ip.entity.DataMap;
import cn.flyingsoft.oais.di.ip.entity.Field;
import cn.flyingsoft.oais.di.ip.entity.ValueNumber;
import cn.flyingsoft.oais.di.ip.entity.ValueText;
import cn.flyingsoft.oais.service.exchange.entity.SimplePkg;

/**解析xsl文件
 * @author jin 20070308
 *
 */
public class ParseExcel {

    private HSSFWorkbook workBook;
    private SimplePkg simplePkg = null  ;
    private HSSFSheet sheet;

    public ParseExcel(){

    }

    //获取workBook
    private HSSFWorkbook getWorkBook(FileInputStream fileInputStream){
        try {
            workBook = new HSSFWorkbook(fileInputStream) ;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workBook;

    }

    //获取sheet
    private HSSFSheet getSheet(){
        sheet = workBook.getSheetAt(0) ;
        return sheet;

    }

    //获取总行数
    private int getRowNum(){
        int RowNum = sheet.getLastRowNum();
        return RowNum ;
    }

    //获取单元类型 Numeric:0 String:1 Formula:2 Blank:3 Boolean:4 Error:5
    private short getCellType(HSSFCell cell){
        short cellType = 3;
        try{
            cellType = (short) cell.getCellType();
        }
        catch(Exception ex){
            cellType = 3;
        }
        return cellType;
    }

    //获取所有row的值
    public List getRowValues(File file,int level){
        FileInputStream fileInputStream;
        try {
            fileInputStream = new FileInputStream(file);
            workBook = this.getWorkBook(fileInputStream) ;
            sheet = this.getSheet() ;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        List list = new ArrayList() ;
        DataMap dataMap ;
        /**
         * 获取第一行
         */
        String[] title = new String[sheet.getRow(0).getLastCellNum()] ;
        for (short n=0; n< title.length; n++){
            title[n] =  sheet.getRow(0).getCell(n).getStringCellValue().trim() ;
        }
        /**
         * 遍历所有行
         */
        for(int i = 1 ;i<=this.getRowNum(); i++){
            simplePkg = new SimplePkg() ;
            simplePkg.setLevel(level) ;
            dataMap = simplePkg.getDescription() ;
            HSSFRow row = sheet.getRow(i);
            short cellNum = row.getLastCellNum();
            HSSFCell cell = null;
            /**
             * 遍历所有列
             */
            for(short j = 0 ; j                 cell = row.getCell(j);
                if(getCellType(cell) == 0) dataMap.put(new Field(title[j]),new ValueNumber(String.valueOf(cell.getNumericCellValue()).trim())) ;
                else if(getCellType(cell) == 1) dataMap.put(new Field(title[j]),new ValueText(cell.getStringCellValue().trim())) ;
                else if(getCellType(cell) == 2) dataMap.put(new Field(title[j]),new ValueText(cell.getCellFormula().trim())) ;
                else if(getCellType(cell) == 4) dataMap.put(new Field(title[j]),new ValueText(new Boolean(cell.getBooleanCellValue()).toString().trim())) ;
                simplePkg.setDescription(dataMap) ;
            }
            list.add(simplePkg) ;
        }
        return list ;
    }

    //
    public List returnSimList(List files){
        List simplePkgs = new ArrayList() ;
        List rowValues = new ArrayList() ;
        int count = 1 ;
        for(File file:files){
            simplePkg = new SimplePkg () ;
            rowValues = this.getRowValues(file,count) ;
            for (SimplePkg simplePkg : rowValues){
                simplePkgs.add(simplePkg) ;
                simplePkg.setAllLevel(files.size()) ;
            }
            count ++ ;
        }
        return simplePkgs;
    }

    public static void main(String args[]) throws IOException{
        List files = new ArrayList() ;
        files.add(new File("E:\\zc-01.xls")) ;
        files.add(new File("E:\\zc-01h.xls")) ;
//        files.add(new File("E:\\W2005HJ.xls"));
        ParseExcel parseExcel = new ParseExcel()  ;
        List simplePkgs = parseExcel.returnSimList(files) ;
        SimplePkg simplePkg = new SimplePkg () ;

        System.out.println(simplePkg);
    }
}

分享到：

JDom + XPath 解析XML | JAVA中的四种XML解析比较

• 2007-03-29 17:30
• 浏览 1243
• 评论(0)
• 查看更多