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);
}
}
}