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;

}

}