java poi读取excel操作示例（2个代码）

项目中要求读取excel文件内容，并将其转化为xml格式。常见读取excel文档一般使用poi和jexcelapi这两个工具。这里我们介绍使用poi实现读取excel文档。

/*
 * 使用poi读取excel文件
 */
import java.io.file;
import java.io.fileinputstream;
import java.util.arraylist;

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;

/**
 *
 * @author hanbin
 */
public class readexcel {

    /**
     * @param args the command line arguments
     */
    public static void main(string[] args)throws exception {
        read("d:\\demo.xls");
    }

    public static arraylist read(string filename){
        arraylist list = new arraylist();
        string sql = "";
        try{
            file f = new file(filename);
            fileinputstream fis = new fileinputstream(f);
            hssfworkbook wbs = new hssfworkbook(fis);
            hssfsheet childsheet = wbs.getsheetat(0);
            system.out.println("行数:" + childsheet.getlastrownum());
            for(int i = 4;i<childsheet.getlastrownum();i++){
                hssfrow row = childsheet.getrow(i);
                system.out.println("列数:" + row.getphysicalnumberofcells());
                if(null != row){
                    for(int k=1;k<row.getphysicalnumberofcells();k++){
                        hssfcell cell;
                        cell = row.getcell((short)k);
                       // system.out.print(getstringcellvalue(cell) + "\t");
                        list.add(getstringcellvalue(cell) + "\t");
                    }
                }
            }
        }catch(exception e){
            e.printstacktrace();
        }
        return list;
    }
    /**
     * 获取单元格数据内容为字符串类型的数据
     *
     * @param cell excel单元格
     * @return string 单元格数据内容
     */
    private static string getstringcellvalue(hssfcell cell) {
        string strcell = "";
        switch (cell.getcelltype()) {
        case hssfcell.cell_type_string:
            strcell = cell.getstringcellvalue();
            break;
        case hssfcell.cell_type_numeric:
            strcell = string.valueof(cell.getnumericcellvalue());
            break;
        case hssfcell.cell_type_boolean:
            strcell = string.valueof(cell.getbooleancellvalue());
            break;
        case hssfcell.cell_type_blank:
            strcell = "";
            break;
        default:
            strcell = "";
            break;
        }
        if (strcell.equals("") || strcell == null) {
            return "";
        }
        if (cell == null) {
            return "";
        }
        return strcell;
    }
}

再来一个例子

package edu.sjtu.erplab.poi;

import java.io.fileinputstream;
import java.io.filenotfoundexception;
import java.io.ioexception;
import java.io.inputstream;
import java.text.simpledateformat;
import java.util.date;
import java.util.hashmap;
import java.util.map;

import org.apache.poi.hssf.usermodel.hssfcell;
import org.apache.poi.hssf.usermodel.hssfdateutil;
import org.apache.poi.hssf.usermodel.hssfrow;
import org.apache.poi.hssf.usermodel.hssfsheet;
import org.apache.poi.hssf.usermodel.hssfworkbook;
import org.apache.poi.poifs.filesystem.poifsfilesystem;

/**
 * 操作excel表格的功能类
 */
public class excelreader {
    private poifsfilesystem fs;
    private hssfworkbook wb;
    private hssfsheet sheet;
    private hssfrow row;

    /**
     * 读取excel表格表头的内容
     * @param inputstream
     * @return string 表头内容的数组
     */
    public string[] readexceltitle(inputstream is) {
        try {
            fs = new poifsfilesystem(is);
            wb = new hssfworkbook(fs);
        } catch (ioexception e) {
            e.printstacktrace();
        }
        sheet = wb.getsheetat(0);
        row = sheet.getrow(0);
        // 标题总列数
        int colnum = row.getphysicalnumberofcells();
        system.out.println("colnum:" + colnum);
        string[] title = new string[colnum];
        for (int i = 0; i < colnum; i++) {
            //title[i] = getstringcellvalue(row.getcell((short) i));
            title[i] = getcellformatvalue(row.getcell((short) i));
        }
        return title;
    }

    /**
     * 读取excel数据内容
     * @param inputstream
     * @return map 包含单元格数据内容的map对象
     */
    public map<integer, string> readexcelcontent(inputstream is) {
        map<integer, string> content = new hashmap<integer, string>();
        string str = "";
        try {
            fs = new poifsfilesystem(is);
            wb = new hssfworkbook(fs);
        } catch (ioexception e) {
            e.printstacktrace();
        }
        sheet = wb.getsheetat(0);
        // 得到总行数
        int rownum = sheet.getlastrownum();
        row = sheet.getrow(0);
        int colnum = row.getphysicalnumberofcells();
        // 正文内容应该从第二行开始,第一行为表头的标题
        for (int i = 1; i <= rownum; i++) {
            row = sheet.getrow(i);
            int j = 0;
            while (j < colnum) {
                // 每个单元格的数据内容用"-"分割开，以后需要时用string类的replace()方法还原数据
                // 也可以将每个单元格的数据设置到一个javabean的属性中，此时需要新建一个javabean
                // str += getstringcellvalue(row.getcell((short) j)).trim() +
                // "-";
                str += getcellformatvalue(row.getcell((short) j)).trim() + "    ";
                j++;
            }
            content.put(i, str);
            str = "";
        }
        return content;
    }

    /**
     * 获取单元格数据内容为字符串类型的数据
     *
     * @param cell excel单元格
     * @return string 单元格数据内容
     */
    private string getstringcellvalue(hssfcell cell) {
        string strcell = "";
        switch (cell.getcelltype()) {
        case hssfcell.cell_type_string:
            strcell = cell.getstringcellvalue();
            break;
        case hssfcell.cell_type_numeric:
            strcell = string.valueof(cell.getnumericcellvalue());
            break;
        case hssfcell.cell_type_boolean:
            strcell = string.valueof(cell.getbooleancellvalue());
            break;
        case hssfcell.cell_type_blank:
            strcell = "";
            break;
        default:
            strcell = "";
            break;
        }
        if (strcell.equals("") || strcell == null) {
            return "";
        }
        if (cell == null) {
            return "";
        }
        return strcell;
    }

    /**
     * 获取单元格数据内容为日期类型的数据
     *
     * @param cell
     *            excel单元格
     * @return string 单元格数据内容
     */
    private string getdatecellvalue(hssfcell cell) {
        string result = "";
        try {
            int celltype = cell.getcelltype();
            if (celltype == hssfcell.cell_type_numeric) {
                date date = cell.getdatecellvalue();
                result = (date.getyear() + 1900) + "-" + (date.getmonth() + 1)
                        + "-" + date.getdate();
            } else if (celltype == hssfcell.cell_type_string) {
                string date = getstringcellvalue(cell);
                result = date.replaceall("[年月]", "-").replace("日", "").trim();
            } else if (celltype == hssfcell.cell_type_blank) {
                result = "";
            }
        } catch (exception e) {
            system.out.println("日期格式不正确!");
            e.printstacktrace();
        }
        return result;
    }

    /**
     * 根据hssfcell类型设置数据
     * @param cell
     * @return
     */
    private string getcellformatvalue(hssfcell cell) {
        string cellvalue = "";
        if (cell != null) {
            // 判断当前cell的type
            switch (cell.getcelltype()) {
            // 如果当前cell的type为numeric
            case hssfcell.cell_type_numeric:
            case hssfcell.cell_type_formula: {
                // 判断当前的cell是否为date
                if (hssfdateutil.iscelldateformatted(cell)) {
                    // 如果是date类型则，转化为data格式

                    //方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
                    //cellvalue = cell.getdatecellvalue().tolocalestring();

                    //方法2：这样子的data格式是不带带时分秒的：2011-10-12
                    date date = cell.getdatecellvalue();
                    simpledateformat sdf = new simpledateformat("yyyy-mm-dd");
                    cellvalue = sdf.format(date);

                }
                // 如果是纯数字
                else {
                    // 取得当前cell的数值
                    cellvalue = string.valueof(cell.getnumericcellvalue());
                }
                break;
            }
            // 如果当前cell的type为strin
            case hssfcell.cell_type_string:
                // 取得当前的cell字符串
                cellvalue = cell.getrichstringcellvalue().getstring();
                break;
            // 默认的cell值
            default:
                cellvalue = " ";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;

    }

    public static void main(string[] args) {
        try {
            // 对读取excel表格标题测试
            inputstream is = new fileinputstream("d:\\test2.xls");
            excelreader excelreader = new excelreader();
            string[] title = excelreader.readexceltitle(is);
            system.out.println("获得excel表格的标题:");
            for (string s : title) {
                system.out.print(s + " ");
            }

            // 对读取excel表格内容测试
            inputstream is2 = new fileinputstream("d:\\test2.xls");
            map<integer, string> map = excelreader.readexcelcontent(is2);
            system.out.println("获得excel表格的内容:");
            for (int i = 1; i <= map.size(); i++) {
                system.out.println(map.get(i));
            }

        } catch (filenotfoundexception e) {
            system.out.println("未找到指定路径的文件!");
            e.printstacktrace();
        }
    }
}