操作ｅｘｃｅｌ文件，从文件中读取数据。

java 代码

import org.apache.log4j.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import java.util.*;
import java.io.*;
/**
*
* <p>Title: POIBean.java</p>
*
* <p>Description: 该类用于操作ｅｘｃｅｌ文件，从文件中读取数据。</p>
* @version 1.0
*/
public class POIUtil
{
    //定义日志记录器
    private static final Logger log = Logger.getLogger(POIUtil.class);

    private static POIUtil poiUtil = null;

    //单例
    POIUtil()
    {
    }

    public static POIUtil getInstance()
    {
        if (poiUtil == null)
        {
            poiUtil = new POIUtil();
        }
        return poiUtil;
    }

     /*******读取xls表格中的数据********
     * @param filePath : 文件完整路径名
     * @param worksheet: 读取的工作表，0为第一张工作表
     * @param start    : 开始读的行数
     * @param length   : 读取的列数长度，从第一列开始
     * @param link     : 连接各个单元格的值
     * @param maxRows  : 读取的最多行数
     * @throws IOException
     */
    public ArrayList readData(String filePath, int worksheet, int start,
                              int length, String link, final int maxRows) throws IOException
    {
        try
        {
            File file = new File(filePath);
            if (!file.exists())
            {
                log.error("read file failed : " + filePath + " is not exist !");
                return null;
            }
        }
        catch (Exception e)
        {
            log.error(e.toString());
            return null;
        }
        ArrayList arrayList = new ArrayList();
        FileInputStream fis = null;
        try
        {
            fis = new FileInputStream(filePath);
            POIFSFileSystem fs = new POIFSFileSystem(fis);
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(worksheet);
            //不存在worksheet
            if (sheet == null)
            {
                log.error("can not get the specified sheet at " + worksheet);
                return null;
            }
            //所有的行数
            int times = 0;
            for (int i = start; i <= sheet.getLastRowNum(); i++)
            {
                //用于单元格内容为空时，确定其坐标
                String division = "";
                //整行数据都不为空时，存储这一行的数据
                StringBuffer rowValue = new StringBuffer("");
                times++;
                //超过读的行数
                if (times > maxRows)
                {
                    break;
                }
                HSSFRow row = sheet.getRow(i);
                //行值不为空
                if (row != null)
                {
                    //所有的列数
                    for (int j = 0; j < length; j++)
                    {
                        HSSFCell cell = row.getCell((short) j);
                        if (cell == null)
                        {
                            division = "@" + (i + 1) + ":" + (j + 1);
                            rowValue.append(division);
                            rowValue.append(link);
                            log.debug("cell " + (i + 1) + ":" + (j + 1) +
                                      "is null");
                            continue;
                        }
                        switch (cell.getCellType())
                        {
                            //单元格内容是数字
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                rowValue.append(new Double(cell.
                                        getNumericCellValue()).intValue());
                                rowValue.append("@" + (i + 1) + ":" + (j + 1));
                                rowValue.append(link);
                                break;

                                //单元格内容是字符串
                            case HSSFCell.CELL_TYPE_STRING:
                                rowValue.append(cell.getStringCellValue());
                                rowValue.append("@" + (i + 1) + ":" + (j + 1));
                                rowValue.append(link);
                                break;
                            default:
                                division = "@" + (i + 1) + ":" + (j + 1);
                                rowValue.append(division);
                                rowValue.append(link);
                        }
                    }
                    arrayList.add(rowValue.toString());
                }
                //行值为空,登记位置
                else
                {
                    log.debug("row " + (i + 1) + "is null");
                }
            }
        }
        catch (IOException e)
        {
            log.error(e.toString());
            throw new IOException("IO Exception was found");
        }
        finally
        {
            // 关闭流
            if (fis != null)
            {
                fis.close();
            }
        }
        return arrayList;
    }

    /**
     * 对Excel文件进行判断
     * @param File file  Excel文件
     * @param sheetNames Sheet的名字
     * @return boolean  是否是真文件  true 真　　false 加
     */
    public boolean isFile(File file, String[] sheetNames) throws Exception
    {
        //记录日志
        log.debug("enter getCounts()");

        //参数判断
        if (file == null || !file.exists() || sheetNames == null || sheetNames.length == 0)
        {
            log.error("param is not valid");
            throw new IllegalArgumentException("param is not valid");
        }

        //定义流变量
        POIFSFileSystem fs = null;
        FileInputStream fis = null;
        HSSFWorkbook hssfWorkBook = null;

        boolean isValid = false;

        //默认的sheet个数为0
        int counts = 0;
        try
        {
            //读入文件
            fis = new FileInputStream(file);
            fs = new POIFSFileSystem(fis);

            hssfWorkBook = new HSSFWorkbook(fs);
            counts = hssfWorkBook.getNumberOfSheets();

            //对Sheet的个数进行判断
            if (counts != sheetNames.length)
            {
                return isValid;
            }

            String tempSheetName = null;

            //再判断sheet名
            for (int i = 0; i < counts; i++)
            {
                tempSheetName = hssfWorkBook.getSheetName(i);
                if (!tempSheetName.equals(sheetNames[i].trim()))
                {
                    return isValid;
                }
            }
            isValid = true;
        }
        catch(IOException ioe)
        {
            log.error(ioe.toString());
            return isValid;
        }
        catch(Exception e)
        {
            log.error(e.toString());
            throw new Exception();
        }
        finally
        {
            try
            {
                fis.close();
            }
            catch(Exception e)
            {
                log.error(e.toString());
                return isValid;
            }
        }

        return isValid;
    }



    //测试
    public static void main(String[] args)
    {
        POIUtil poiUtil = new POIUtil();

        ArrayList list = null;
        try
        {
            list=poiUtil.readData("D:\\task模板.xls",0,0,2,"@@@",3);
        }
        catch(IOException ioe)
        {

        }

        if (list == null)
        {
            System.out.println("======================");
        }
        else
        {
            String temp = null;
            String[] str = new String[2];
            String[] tempStr = new String[2];

            for (int i = 0; i < list.size(); i++)
            {
                temp = (String)list.get(i);
                System.out.println("========row" + temp);
                str = temp.split("@@@");
                for (int j = 0; j < str.length; j++)
                {
                    tempStr = str[j].split("@");
                    System.out.println(tempStr.length);
                    System.out.println(tempStr[0]);
                    System.out.println(tempStr[1]);
                }
            }
        }
    }

}

分享到：

对ｘｍｌ文档操作的常用方法 | 复习jdbc

• 2007-12-08 15:14
• 浏览 1886
• 评论(0)
• 查看更多