POI还是挺好用的，没用任何问题！
---------------------------------------------------------
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;
}
}
分享到：
相关推荐
POI读取excel的例子
poi读取Exceljar包和例子 poi-2.5.1-final-20040804.jar poi-contrib-2.5.1-final-20040804.jar poi-scratchpad-2.5.1-final-20040804.jar ReadXL.java
通过对excel某一行某一列的读取，和某个文件夹路径中的文件进行文件名匹配
实现了JAVA 窗口，读取EXCEL文件，用poi读取EXCEL内容只是一个小例子
poi 导出EXCEL 例子 poi 导出EXCEL 例子poi 导出EXCEL 例子poi 导出EXCEL 例子
poi读取excel2007和2003兼容工具例子.利用简单工厂方法
POI把Excel表格数据导入到数据库的小例子
之前用jxl发现不支持excel2007，不得以就去用poi实现excel的读取了！本人亲测的一个实例，支持excel2007！
NULL 博文链接：https://topbox163.iteye.com/blog/1114166
POI使用excel模板循环输出行到文件并导出的一个小例子
这是一个关于poi导出Excel的详细的例子。里面涉及到poi操作Excel的详尽引用，包括设置行的高度、列的宽度、合并单元格、设置单元格的样式等等，一个例子足以掌握poi导出Excel的操作。
基于poi的java操作excel小demo，有兴趣的童鞋可以下载，自己总结，与大家共享，例子不大。但是入门一定会有帮助。很快！
poi excel通过模板导出excel表格 且带有公式 导出的excel表格里面的公式均可计算 有简单的公式也有难的公司 在此例子里面都可以跟着excel一起到处并可计算
POI操作Excel POI操作Excel POI操作Excel 里边包含了几个小例子 及相关Jar包
struts2中利用poi导出excel的简单例子
poi 读取03和07excel文档的小例子，含jar包。
一个很不错的入门级的poi小实例，想要这方面资源的童鞋注意了啊，绝对不能错过哦 。。。。。
自己写的一个poi解析excel 的工具类。里面有些实用的方法，满足基本的解析要求了。
apache的poi操作excel 例子中有完整的架包，详细的注解
自己做的一个完整导出功能，struts2+poi导出excel 经过自己严格测试通过的。