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;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.hibernate.criterion.DetachedCriteria;
import org.springframework.dao.DataAccessException;

import cn.bstar.gale.boss.dao.OperatorDao;
import cn.bstar.gale.boss.framework.BusinessException;
import cn.bstar.gale.boss.framework.TransException;
import cn.bstar.gale.boss.model.Department;
import cn.bstar.gale.boss.model.Operator;
import cn.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("======" + new Date()
??????????????????????? + ": [error] generator HSSFWorkbook failed");
??????????????? throw new BusinessException("error.export.excel.fail");
??????????? }
??????? } catch (IOException e) {
??????????? logger.error("======" + new Date() + ": [error] exportAllOperator "
??????????????????? + e);
??????????? throw new BusinessException("error.export.excel.fail");
??????? }
??? }

??? private HSSFWorkbook generateExcel(List operatorList) {
??????? HSSFWorkbook wb = new HSSFWorkbook();
??????? HSSFSheet sheet = 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;
??????? // 创建一个样式
??????? HSSFCellStyle centerStyle = wb.createCellStyle();
??????? // 居中对齐
??????? centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

??????? // sheet 创建一行
??????? HSSFRow row = 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 (int i = 0; i < operatorList.size(); i++) {
??????????? Operator operator = (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)
??????????? throws TransException {
??????? // 读取左上端单元
??????? HSSFRow row = null;
??????? HSSFCell cell = null;
??????? Operator o = null;
??????? Department department = null;
??????? List oldLogIdList= null;
??????? String newLogId=null;
??????? // 创建对Excel工作簿文件的引用
??????? HSSFWorkbook workbook;
??????? try {
??????????? //得到已经存在的人员登录帐户列表
??????????? oldLogIdList=operatorDao.getAllOperatorLogIds();
??????????? workbook = new HSSFWorkbook(is);
??????? } catch (Exception e) {
??????????? logger.error("======" + new Date() + ": [error] enportAllOperator "
??????????????????? + e);
??????????? throw new TransException("error.data.access");
??????? }

??????? // 用getSheetAt(int index)按索引引用，
??????? // 在Excel文档中，第一张工作表的缺省索引是0，
??????? HSSFSheet sheet = workbook.getSheetAt(0);
??????? List<Operator> operatorList = new ArrayList<Operator>();
??????? Date date = new Date();
??????? for (Iterator it = 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 = new Department();
??????????? if (cellNotBlank(cell)) {
??????????????? department.setDeptId(Long.valueOf(getStringCellValue(cell)));
??????????????? o.setDepartment(department);
??????????? }
??????????? // 创建时间
??????????? o.setDateCreated(date);
??????????? o.setCreatedBy(createdBy);
??????????? operatorList.add(o);
??????? }
??????? try {
??????????? for (int i = 0; i < operatorList.size(); i++) {
??????????????? operatorDao.addOperator(operatorList.get(i));
??????????? }
??????? } catch (Exception be) {
??????????? logger.error("======" + new Date() + ": [error] enportAllOperator "
??????????????????? + be);
??????????? throw new TransException("error.data.access");
??????? }
??? }

??? private void createCell(int index, String lable, HSSFCellStyle centerStyle,
??????????? HSSFRow row, HSSFCell cell) {
??????? 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 (int i = 1; i < time; i++) {
??????????? sheet.setColumnWidth((short) i,
??????????????????? (short) ((40 * 8) / ((double) 1 / 20)));
??????? }
??? }

??? private void setCellValue(int index, String value, HSSFRow row) {
??????? HSSFCell cell = 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))) {
??????????? return true;
??????? }
??????? return false;
??? }

?

?? private String getStringCellValue(HSSFCell cell){
??????? String value=null;
??????? switch(cell.getCellType())
??????? {
??????????? case HSSFCell.CELL_TYPE_STRING:
??????????????????????? value=cell.getStringCellValue().trim();break;
??????????? case HSSFCell.CELL_TYPE_NUMERIC:
??????????????????????? String number=String.valueOf(cell.getNumericCellValue());
??????????????????????? value=number.substring(0,number.indexOf("."));break;
??????????? case HSSFCell.CELL_TYPE_FORMULA:
??????????????????????? value=String.valueOf(cell.getCellFormula()).trim();break;
??????????? case HSSFCell.CELL_TYPE_BOOLEAN:
??????????????????????? value=String.valueOf(cell.getBooleanCellValue()).trim();break;
??????? }
??????? return value;
??? }
}