java 读写 excel poi读excel的一个小例子
http://www.apache.org/dyn/closer.cgi/poi/release/bin/poi-bin-3.7-20101029.tar.gz
---------------------------------------------------------
package com.sztelecom.reportnet.action;
importjava.io.IOException;
importjava.io.InputStream;
importjava.io.OutputStream;
importjava.util.ArrayList;
importjava.util.Date;
importjava.util.Iterator;
import java.util.List;import org.apache.log4j.Logger;
importorg.apache.poi.hssf.usermodel.HSSFCell;
importorg.apache.poi.hssf.usermodel.HSSFCellStyle;
importorg.apache.poi.hssf.usermodel.HSSFRow;
importorg.apache.poi.hssf.usermodel.HSSFSheet;
importorg.apache.poi.hssf.usermodel.HSSFWorkbook;
importorg.hibernate.criterion.DetachedCriteria;
import org.springframework.dao.DataAccessException;import cn.bstar.gale.boss.dao.OperatorDao;
importcn.bstar.gale.boss.framework.BusinessException;
importcn.bstar.gale.boss.framework.TransException;
importcn.bstar.gale.boss.model.Department;
importcn.bstar.gale.boss.model.Operator;
importcn.bstar.gale.boss.service.ExcelService;
import cn.bstar.gale.boss.util.Tools;public class Test implements ExcelService {
private Logger logger = Logger.getLogger(Test.class);
private OperatorDao operatorDao;
public void exportAllOperator(OutputStream os) throws BusinessException { DetachedCriteria dc = DetachedCriteria.forClass(Operator.class);
HSSFWorkbook wb = null; List operatorList = new ArrayList();
try { operatorList = operatorDao.findOperatorByDc(dc);
if (operatorList.size() < 1) {
return;
}
wb = generateExcel(operatorList);if (wb != null) {
wb.write(os);
os.flush();
}else{
logger.error("======"+newDate()
+":[error]generatorHSSFWorkbookfailed");
thrownewBusinessException("error.export.excel.fail");
}
}catch(IOExceptione){
logger.error("======"+newDate()+":[error]exportAllOperator"
+e);
thrownewBusinessException("error.export.excel.fail");
}
}private HSSFWorkbook generateExcel(List operatorList) {
HSSFWorkbookwb=newHSSFWorkbook();
HSSFSheetsheet=wb.createSheet();
wb.setSheetName(0,"人员记录",HSSFCell.ENCODING_UTF_16);
sheet.setColumnWidth((short)0,(short)((15*8)/((double)1/20)));
sheetIterator(11, sheet);HSSFCell cell = null;
//创建一个样式
HSSFCellStylecenterStyle=wb.createCellStyle();
//居中对齐
centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// sheet 创建一行
HSSFRowrow=sheet.createRow((short)0);
//设定列名
row.createCell((short) 0).setCellValue("");createCell(1, "操作员姓名", centerStyle, row, cell);
createCell(2,"操作员ID",centerStyle,row,cell);
createCell(3,"登录密码",centerStyle,row,cell);
createCell(4,"性别",centerStyle,row,cell);
createCell(5,"身份证号码",centerStyle,row,cell);
createCell(6,"专长",centerStyle,row,cell);
createCell(7,"可用状态",centerStyle,row,cell);
createCell(8,"电话",centerStyle,row,cell);
createCell(9,"E-mail",centerStyle,row,cell);
createCell(10,"学历",centerStyle,row,cell);
createCell(11, "部门编号", centerStyle, row, cell);int s = 1;
for(inti=0;i<operatorList.size();i++){
Operatoroperator=(Operator)operatorList.get(i);
row=sheet.createRow(s);
row.createCell((short)0).setCellValue(s);
setCellValue(1,operator.getNickName(),row);
setCellValue(2,operator.getLogId(),row);
setCellValue(3,operator.getPassword(),row);
setCellValue(4,operator.getSex().toString(),row);
setCellValue(5,operator.getIdCard(),row);
setCellValue(6,operator.getSkill(),row);
setCellValue(7,operator.getStatus().toString(),row);
setCellValue(8,operator.getTelNo(),row);
setCellValue(9,operator.getEmail(),row);
setCellValue(10,operator.getGraduation(),row);
if(operator.getDepartment()!=null){
setCellValue(11,operator.getDepartment().getDeptId()
.toString(),row);
}
s++;
}return wb; }
public void setOperatorDao(OperatorDao operatorDao) {
this.operatorDao=operatorDao;
}public void importAllOperator(InputStream is, Long createdBy)
throwsTransException{
//读取左上端单元
HSSFRowrow=null;
HSSFCellcell=null;
Operatoro=null;
Departmentdepartment=null;
ListoldLogIdList=null;
StringnewLogId=null;
//创建对Excel工作簿文件的引用
HSSFWorkbookworkbook;
try{
//得到已经存在的人员登录帐户列表
oldLogIdList=operatorDao.getAllOperatorLogIds();
workbook=newHSSFWorkbook(is);
}catch(Exceptione){
logger.error("======"+newDate()+":[error]enportAllOperator"
+e);
thrownewTransException("error.data.access");
}// 用getSheetAt(int index)按索引引用，
//在Excel文档中，第一张工作表的缺省索引是0，
HSSFSheetsheet=workbook.getSheetAt(0);
List<Operator>operatorList=newArrayList<Operator>();
Datedate=newDate();
for(Iteratorit=sheet.rowIterator();it.hasNext();){
row = (HSSFRow) it.next();//第一行不是数据
if(row==sheet.getRow(0)){
continue;
}//判断是否重复插入
cell=row.getCell((short)2);
if(cellNotBlank(cell)){
newLogId=getStringCellValue(cell);
//如果数据库中已经存在，则不重复插入
if(oldLogIdList.contains(newLogId)){
continue;
}
}else{
//此字段不能为空
continue;
}o = new Operator(); o.setLogId(getStringCellValue(cell));
cell = row.getCell((short) 1);
if(cellNotBlank(cell)){
o.setNickName(getStringCellValue(cell));
}cell = row.getCell((short) 3);
if(cellNotBlank(cell)){
o.setPassword(getStringCellValue(cell));
}cell = row.getCell((short) 4);
if(cellNotBlank(cell)){
o.setSex(Long.valueOf(getStringCellValue(cell)));
}cell = row.getCell((short) 5);
if(cellNotBlank(cell)){
o.setIdCard(getStringCellValue(cell));
}cell = row.getCell((short) 6);
if(cellNotBlank(cell)){
o.setSkill(getStringCellValue(cell));
}cell = row.getCell((short) 7);
if(cellNotBlank(cell)){
o.setStatus(Long.valueOf(getStringCellValue(cell)));
}cell = row.getCell((short) 8);
if(cellNotBlank(cell)){
o.setTelNo(getStringCellValue(cell));
}cell = row.getCell((short) 9);
if(cellNotBlank(cell)){
o.setEmail(getStringCellValue(cell));
}cell = row.getCell((short) 10);
if(cellNotBlank(cell)){
o.setGraduation(getStringCellValue(cell));
}cell = row.getCell((short) 11);
department=newDepartment();
if(cellNotBlank(cell)){
department.setDeptId(Long.valueOf(getStringCellValue(cell)));
o.setDepartment(department);
}
//创建时间
o.setDateCreated(date);
o.setCreatedBy(createdBy);
operatorList.add(o);
}
try{
for(inti=0;i<operatorList.size();i++){
operatorDao.addOperator(operatorList.get(i));
}
}catch(Exceptionbe){
logger.error("======"+newDate()+":[error]enportAllOperator"
+be);
thrownewTransException("error.data.access");
}
}private void createCell(int index, String lable, HSSFCellStyle centerStyle,
HSSFRowrow,HSSFCellcell){
cell=row.createCell((short)index);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(centerStyle);
cell.setCellValue(lable);
}private void sheetIterator(int time, HSSFSheet sheet) {
for(inti=1;i<time;i++){
sheet.setColumnWidth((short)i,
(short)((40*8)/((double)1/20)));
}
}private void setCellValue(int index, String value, HSSFRow row) {
HSSFCellcell=null;
cell=row.createCell((short)index);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(value);
}private boolean cellNotBlank(HSSFCell cell) {
if(cell!=null&&!Tools.isBlank(getStringCellValue(cell))){
returntrue;
}
returnfalse;
}
private String getStringCellValue(HSSFCell cell){
Stringvalue=null;
switch(cell.getCellType())
{
caseHSSFCell.CELL_TYPE_STRING:
value=cell.getStringCellValue().trim();break;
caseHSSFCell.CELL_TYPE_NUMERIC:
Stringnumber=String.valueOf(cell.getNumericCellValue());
value=number.substring(0,number.indexOf("."));break;
caseHSSFCell.CELL_TYPE_FORMULA:
value=String.valueOf(cell.getCellFormula()).trim();break;
caseHSSFCell.CELL_TYPE_BOOLEAN:
value=String.valueOf(cell.getBooleanCellValue()).trim();break;
}
returnvalue;
}
}