在web开发中,有一个经典的功能,就是数据的导入导出。特别是数据的导出,在生产管理或者财务系统中用的非常普遍,因为这些系统经常要做一些报表打印的工作。而数据导出的格式一般是EXCEL。
现在主流的操作Excel文件的开源工具有很多,用得比较多的就是Apache的POI及JExcelAPI。这里我们用Apache POI!我们先去Apache的大本营下载POI的jar包:http://poi.apache.org/ 。
在这里我抽出来我项目中的一段代码测试版的代码给大家演示,基本满足数据的导出需求
public class PoiToExcelTestAction extends BaseAction<Staff>{
/**
*
*/
private static final long serialVersionUID = 1L;
public String stafftoExcel() throws Exception {
//获取所有员工集合
List<Staff> staffList= this.staffService.findAll();
System.out.println(staffList.size());
// 获取总列数
int CountColumnNum =18; //xls.size();
// 创建Excel文档
@SuppressWarnings("resource")
HSSFWorkbook hwb = new HSSFWorkbook();
// Admin xlsDto = null;
// sheet 对应一个工作页
HSSFSheet sheet = hwb.createSheet("人员信息表");
HSSFSheet sheet2 = hwb.createSheet("管理员信息表2");
//第一个工作页的内容
HSSFRow firstrow = sheet.createRow(0); // 下标为0的行开始
HSSFCell[] firstcell = new HSSFCell[CountColumnNum];
String[] names = new String[CountColumnNum];
names[0] = "姓名";
names[1] = "性别";
names[2] = "年龄";
names[3] = "身份证号码";
names[4] = "手机号码";
names[5] = "员工编号";
names[6] = "服务次数";
names[7] = "满意度";
names[8] = "员工邮箱";
names[9] = "微信号码";
names[10] = "入职时间";
names[11] = "在职状态";
names[12] = "权限";
names[13] = "登陆用户名";
names[14] = "员工备注";
names[15] = "登陆密码";
names[16] = "员工预约服务数";
names[17] = "工作报告数";
for (int j = 0; j < CountColumnNum; j++) {
firstcell[j] = firstrow.createCell(j);
firstcell[j].setCellValue(new HSSFRichTextString(names[j]));
}
for (int i = 0; i <staffList.size(); i++) {
// 创建一行
HSSFRow row = sheet.createRow(i+1);
// 得到要插入的每一条记录
Staff staff = staffList.get(i);
for (int colu = 0; colu <= CountColumnNum; colu++) {
// 在一行内循环
HSSFCell name = row.createCell(0);
name.setCellValue(staff.getStaffName());
HSSFCell xm = row.createCell(1);
xm.setCellValue(staff.getSex());
HSSFCell yxsmc = row.createCell(2);
yxsmc.setCellValue(staff.getAge());
HSSFCell kcm = row.createCell(3);
kcm.setCellValue(staff.getIdcard());
HSSFCell cj = row.createCell(4);
cj.setCellValue(staff.getPhoneNum());
HSSFCell zk = row.createCell(5);
zk.setCellValue(staff.getPhoneNum());
HSSFCell time = row.createCell(6);
time.setCellValue(staff.getStaffCode());
HSSFCell xh = row.createCell(7);
xh.setCellValue(staff.getServiceNum());
HSSFCell email = row.createCell(8);
email.setCellValue(staff.getStaffEmail());
HSSFCell wx = row.createCell(9);
wx.setCellValue(staff.getWeixinNum());
HSSFCell time1 = row.createCell(10);
time1.setCellValue(staff.getJoinTime());
HSSFCell zz = row.createCell(11);
zz.setCellValue(staff.getNowState());
HSSFCell qx = row.createCell(12);
qx.setCellValue(staff.getStorerole().toString());
HSSFCell longinname = row.createCell(13);
longinname.setCellValue(staff.getLoginname());
HSSFCell bz = row.createCell(14);
bz.setCellValue(staff.getStaffDetail());
HSSFCell loginpas = row.createCell(15);
loginpas.setCellValue(staff.getLoginpassword());
HSSFCell xiaofei = row.createCell(16);
xiaofei.setCellValue(staff.getXiaoFeies().size());
HSSFCell bg = row.createCell(17);
bg.setCellValue(staff.getReports().size());
}
}
//第二个工作页内容
HSSFRow firstrow1 = sheet2.createRow(0); // 下标为0的行开始
HSSFCell[] firstcell1 = new HSSFCell[7];
String[] names1 = new String[7];
names1[0] = "测试";
names1[1] = "测试";
names1[2] = "测试";
names1[3] = "测试";
names1[4] = "测试";
names1[5] = "职务";
names1[6] = "注册时间";
for (int j = 0; j < 7; j++) {
firstcell1[j] = firstrow1.createCell(j);
firstcell1[j].setCellValue(new HSSFRichTextString(names1[j]));
}
// for (int i = 0; i < xls.size(); i++) {
// 创建一行
HSSFRow row1 = sheet2.createRow(1);
// 得到要插入的每一条记录
// xlsDto = xls.get(i);
for (int colu = 0; colu <= CountColumnNum; colu++) {
// 在一行内循环
HSSFCell xh = row1.createCell(0);
xh.setCellValue("132");
HSSFCell xm = row1.createCell(1);
xm.setCellValue("132");
HSSFCell yxsmc = row1.createCell(2);
yxsmc.setCellValue("132");
HSSFCell kcm = row1.createCell(3);
kcm.setCellValue("123");
HSSFCell cj = row1.createCell(4);
cj.setCellValue("123");
HSSFCell zk = row1.createCell(5);
zk.setCellValue("123");
HSSFCell time = row1.createCell(6);
time.setCellValue(new Date().toString());
}
// }
// 创建文件输出流,准备输出电子表格
try{
OutputStream out = new FileOutputStream("e:管理员信息表.xls");
hwb.write(out);
out.close();
System.out.println("数据库导出成功");
}catch(Exception ex){
}
return null;
}
}