poi对excel 的基本操作

Java POI对EXCEL的基本操作

接口分为2003版和2007版两类不兼容

2003前缀为HSSF
2007前缀为XSSF

以2007版举例实现DAO 操作

package com.booway.homework.dao.impl;

import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import com.booway.homework.dao.IUserDao;
import com.booway.homework.model.User;
import com.booway.homework.utils.POIUtil;

public class UserDaoImplForPOI implements IUserDao
{
    /**
     * *********ID对应excel表的第1列***********
     */
    public static final int ID = 0;

    /**
     * *********USERNAME对应excel表的第2列***********
     */
    public static final int USERNAME = 1;

    /**
     * *********NICKNAME对应excel表的第3列***********
     */
    public static final int NICKNAME = 2;

    /**
     * *********PASSWORD对应excel表的第4列***********
     */
    public static final int PASSWORD = 3;

    /**
     * *********SEX对应excel表的第5列***********
     */
    public static final int SEX = 4;

    /**
     * *********MAIL对应excel表的第6列***********
     */
    public static final int MAIL = 5;

    /**
     * *********TYPE对应excel表的第7列***********
     */
    public static final int TYPE = 6;

    /**
     * *********SHEETID对应excel的第1张表***********
     */
    public static final int SHEETID = 0;

    /**
     * 创建logger日志
     */

    public static Logger logger = Logger.getLogger(UserDaoImplForPOI.class);

    /**
     * 实现增加功能
     */
    @Override
    public boolean add(User u)
    {
        /**
         * 如果该id存在则不添加
         */
        if (loadById(u.getId()) != null)
        {
            return false;
        }
        try
        {
            // 得到Sheet对象
            Sheet sheet = POIUtil.getSheet(SHEETID);
            if (sheet == null)
            {
                return false;
            }

            // 得到该表最后一条数据的物理行
            int rs = sheet.getPhysicalNumberOfRows();

            // 如果该行等于65536代表该行存满
            if (rs == 65536)
            {
                logger.debug(SHEETID + "该表已存满");
                return false;
            }

            // 创建新的行
            Row row = sheet.createRow(rs);

            // 为该行的列赋值
            row.createCell(ID).setCellValue(u.getId());
            row.createCell(USERNAME).setCellValue(u.getUserName());
            row.createCell(NICKNAME).setCellValue(u.getNickName());
            row.createCell(PASSWORD).setCellValue(u.getPassWord());
            row.createCell(SEX).setCellValue(u.getSex());
            row.createCell(MAIL).setCellValue(u.getMail());
            row.createCell(TYPE).setCellValue(u.getType());
            POIUtil.write();

            logger.debug("添加用户" + u);
            return true;
        } catch (Exception e)
        {
            logger.error(e.toString());
        } finally
        {
            POIUtil.closeWorkbook();
        }
        return false;
    }

    /**
     * 实现删除功能
     */

    @Override
    public boolean deleteById(String id)
    {
        try
        {
            Sheet sheet = POIUtil.getSheet(SHEETID);
            if (sheet == null)
            {
                return false;
            }
            //遍历所有行，如果ID相同删除
            int rs = sheet.getPhysicalNumberOfRows();
            for (int r = 0; r < rs; r++)
            {
                Row row = sheet.getRow(r);
                if (row.getCell(ID).getStringCellValue().equals(id))
                {
                    sheet.shiftRows(r + 1, rs, -1);
                    POIUtil.write();
                    logger.debug(id + "该id信息已删除");
                    return true;
                }
            }
        } catch (Exception e)
        {
            logger.error(e.toString());
        } finally
        {
            POIUtil.closeWorkbook();
        }
        return false;

    }

    /**
     * 实现更新功能
     */

    @Override
    public boolean updateById(User u)
    {
        try
        {
            Sheet sheet = POIUtil.getSheet(SHEETID);
            if (sheet == null)
            {
                return false;
            }
            int rs = sheet.getPhysicalNumberOfRows();
            for (int r = 0; r < rs; r++)
            {
                Row row = sheet.getRow(r);
                if (row.getCell(ID).getStringCellValue().equals(u.getId()))
                {
                    row.getCell(ID).setCellValue(u.getId());
                    row.getCell(USERNAME).setCellValue(u.getUserName());
                    row.getCell(NICKNAME).setCellValue(u.getNickName());
                    row.getCell(PASSWORD).setCellValue(u.getPassWord());
                    row.getCell(SEX).setCellValue(u.getSex());
                    row.getCell(MAIL).setCellValue(u.getMail());
                    row.getCell(TYPE).setCellValue(u.getType());
                    return true;
                }
            }
        } catch (Exception e)
        {
            logger.error(e.toString());
        } finally
        {
            POIUtil.closeWorkbook();
        }
        return false;

    }

    /**
     * 查询单个数据
     */
    @Override
    public User loadById(String id)
    {
        User u = null;
        try
        {
            Sheet sheet = POIUtil.getSheet(SHEETID);
            if (sheet == null)
            {
                return u;
            }
            int rs = sheet.getPhysicalNumberOfRows();
            for (int r = 0; r < rs; r++)
            {
                Row row = sheet.getRow(r);
                if (row.getCell(ID).getStringCellValue().equals(id))
                {
                    u = new User();
                    u.setId(row.getCell(ID).getStringCellValue());
                    u.setUserName(row.getCell(USERNAME).getStringCellValue());
                    u.setNickName(row.getCell(NICKNAME).getStringCellValue());
                    u.setPassWord(row.getCell(PASSWORD).getStringCellValue());
                    u.setSex(row.getCell(SEX).getStringCellValue());
                    u.setMail(row.getCell(MAIL).getStringCellValue());
                    u.setType(row.getCell(TYPE).getStringCellValue());
                    return u;
                }
            }
        } catch (Exception e)
        {
            logger.error(e.toString());
        } finally
        {
            POIUtil.closeWorkbook();
        }
        return u;

    }

    /**
     * 查询总数据数
     */

    @Override
    public int count()
    {
        try
        {
            Sheet sheet = POIUtil.getSheet(SHEETID);
            if (sheet == null)
            {
                return -1;
            }
            int rs = sheet.getPhysicalNumberOfRows();
            return rs - 1;

        } catch (Exception e)
        {
            logger.error(e.toString());
        } finally
        {
            POIUtil.closeWorkbook();
        }
        return -1;

    }

    /**
     * 查询某一段数据数，index为查询的起始值，len代表查询数据个数，可用来实现分页
     */

    @Override
    public List<User> page(int index, int len)
    {
        List<User> list = new ArrayList<User>();
        User u = null;
        try
        {
            Sheet sheet = POIUtil.getSheet(SHEETID);
            if (sheet == null)
            {
                return list;
            }
            int end = sheet.getPhysicalNumberOfRows();
            if (end - index >= len + index)
            {
                end = len + index;
            }

            for (int r = index; r < end; r++)
            {
                Row row = sheet.getRow(r);
                u = new User();
                u.setId(row.getCell(ID).getStringCellValue());
                u.setUserName(row.getCell(USERNAME).getStringCellValue());
                u.setNickName(row.getCell(NICKNAME).getStringCellValue());
                u.setPassWord(row.getCell(PASSWORD).getStringCellValue());
                u.setSex(row.getCell(SEX).getStringCellValue());
                u.setMail(row.getCell(MAIL).getStringCellValue());
                u.setType(row.getCell(TYPE).getStringCellValue());
                list.add(u);
            }
        } catch (Exception e)
        {
            logger.error(e.toString());
        } finally
        {
            POIUtil.closeWorkbook();
        }
        return list;

    }

}

 

package com.booway.homework.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 *
 *
 * POIUtil工厂类
 */
public class POIUtil
{
    public static Logger logger = Logger.getLogger(POIUtil.class);
    public static String path = null;
    public static Workbook workbook = null;
    public static File file = null;
    static
    {
        Properties prop = new Properties();
        InputStream in = null;
        try
        {
            in = POIUtil.class.getResourceAsStream("/poi.properties");
            prop.load(in);
            path = prop.getProperty("excel_path");
            file = new File(path);
        } catch (Exception e)
        {
            logger.error(e.toString());

        } finally
        {
            CloseUtil.closeStream(in);
        }

    }

    public static Sheet getSheet(int sheetId) throws FileNotFoundException, IOException
    {

        workbook = new XSSFWorkbook(new FileInputStream(file));
        Sheet sheet = workbook.getSheetAt(sheetId);
        return sheet;

    }

    public static void closeWorkbook()
    {
        CloseUtil.closeStream(workbook);
    }

    public static void write() throws IOException
    {
        FileOutputStream os = null;
        try
        {
            os = new FileOutputStream(file);
            workbook.write(os);
        } finally
        {
            CloseUtil.closeStream(os);
        }
    }

}