java 读写 excel poi读excel的一个小例子

http://www.apache.org/dyn/closer.cgi/poi/release/bin/poi-bin-3.7-20101029.tar.gz

 

poi读excel的一个小例子

 

---------------------------------------------------------

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