用poi进行简单地excel读取操作（统计调查表并绘图）

import org.apache.poi.hssf.usermodel.*;

import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;



import java.io.*;

import java.util.Iterator;





/**

 * Created by IntelliJ IDEA.

 * User: tedeyang

 * Date: 2009-6-4

 * Time: 17:06:12

 * To change this template use File | Settings | File Templates.

 */

public class ReadExcel {

    public static void main(String[] args) throws Exception {

       if(args.length<2)

        new ReadExcel("D:\\Documents\\工作文档\\技术组\\考核\\技术使用调查\\", "result.xls");

       else

           new ReadExcel(args[0],args[1]);

    }



    Workbook book;

    Sheet sheet;



    ReadExcel(String fileDir, String resultFile) throws Exception {



        File result = new File(fileDir, resultFile);

        FileOutputStream out = new FileOutputStream(result);



        book = new HSSFWorkbook();

        sheet = book.createSheet();



        File[] xlss = new File(fileDir).listFiles(new FilenameFilter() {

            public boolean accept(File f, String s) {

                //read xls

                return (s.startsWith("实际开发技术调查表")) ;

            }

        });

        int i = 1;

        for(File f :xlss){

            parseXls(f, sheet, i++);

        }

        book.write(out);

        out.close();

    }



    /**

     * This method is used to display the Excel content to command line.

     */

    @SuppressWarnings("unchecked")

    public void parseXls(File f, Sheet sheet, int i) throws Exception {

        String depart = "";

        InputStream inputStream = new FileInputStream(f);

        POIFSFileSystem fileSystem = new POIFSFileSystem(inputStream);

        HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);

        HSSFSheet s = workBook.getSheetAt(0);

        Iterator rows = s.rowIterator();

        String name = s.getRow(2).getCell(2).toString();

        String n = f.getName();

        name = n.substring(n.indexOf('-')+1,n.indexOf('.'));

        setCellValue(0,i,name);

        int count = 0;

        System.out.println("人员："+name);

        while (rows.hasNext()) {

            HSSFRow row = (HSSFRow) rows.next();

            Iterator cells = row.cellIterator();

            while (cells.hasNext()) {

                HSSFCell cell = (HSSFCell) cells.next();

                if (cell.getColumnIndex() < 2 || cell.getRowIndex() < 4) continue;

                HSSFRichTextString richTextString = cell.getRichStringCellValue();

                //create

                if (cell.getCellType()==HSSFCell.CELL_TYPE_STRING) {

                    ++count;

                    setCellValue(count,0,richTextString.getString());

                    setCellValue(count,i,value(cell));

                }

            }                  

        }

        inputStream.close();



    }



    private void setCellValue(int r, int c, String name) {

        if(sheet.getRow(r)==null){

            sheet.createRow(r).createCell(c).setCellValue(name);

        }else if(sheet.getRow(r).getCell(c)==null){

            sheet.getRow(r).createCell(c).setCellValue(name);

        }else{

            sheet.getRow(r).getCell(c).setCellValue(name);

        }

    }

     private void setCellValue(int r, int c, double v) {

        if(sheet.getRow(r)==null){

            sheet.createRow(r).createCell(c).setCellValue(v);

        }else if(sheet.getRow(r).getCell(c)==null){

            sheet.getRow(r).createCell(c).setCellValue(v);

        } else{

            sheet.getRow(r).getCell(c).setCellValue(v);

        }

    }



    private double value(HSSFCell cell) {

        int color = cell.getCellStyle().getFillForegroundColor();

        switch (color) {

            case 42: //main use

                return 5;

            case 55:           //a little

                return 1;

            case 23:

                return 0;                //never

            default:

                return 0;

        }

    }



}

 

偶然翻到以前为自己办公方便写的小程序，记录一下，因为POI的API用起来不是很直观。