Java Web使用POI导出Excel的方法详解

本文实例讲述了java web使用poi导出excel的方法。分享给大家供大家参考，具体如下：

采用spring mvc架构：

controller层代码如下

@controller
public class studentexportcontroller{
  @autowired
  private studentexportservice studentexportservice;
  @requestmapping(value = "/excel/export")
  public void exportexcel(httpservletrequest request, httpservletresponse response)
  throws exception {
    list<student> list = new arraylist<student>();
    list.add(new student(1000,"zhangsan","20"));
    list.add(new student(1001,"lisi","23"));
    list.add(new student(1002,"wangwu","25"));
    hssfworkbook wb = studentexportservice.export(list);
    response.setcontenttype("application/vnd.ms-excel");
    response.setheader("content-disposition", "attachment;filename=student.xls");
    outputstream ouputstream = response.getoutputstream();
    wb.write(ouputstream);
    ouputstream.flush();
    ouputstream.close();
  }
}

service层代码如下：

@service
public class studentexportservice {
  string[] excelheader = { "sno", "name", "age"};
  public hssfworkbook export(list<campaign> list) {
    hssfworkbook wb = new hssfworkbook();
    hssfsheet sheet = wb.createsheet("campaign");
    hssfrow row = sheet.createrow((int) 0);
    hssfcellstyle style = wb.createcellstyle();
    style.setalignment(hssfcellstyle.align_center);
    for (int i = 0; i < excelheader.length; i++) {
      hssfcell cell = row.createcell(i);
      cell.setcellvalue(excelheader[i]);
      cell.setcellstyle(style);
      sheet.autosizecolumn(i);
    }
    for (int i = 0; i < list.size(); i++) {
      row = sheet.createrow(i + 1);
      student student = list.get(i);
      row.createcell(0).setcellvalue(student.getsno());
      row.createcell(1).setcellvalue(student.getname());
      row.createcell(2).setcellvalue(student.getage());
    }
    return wb;
  }
}

前台的js代码如下：

<script>
function exportexcel(){
   location.href="excel/export" rel="external nofollow" ;
   <!--这里不能用ajax请求，ajax请求无法弹出下载保存对话框-->
 }
</script>

设置excel样式以及注意点：

string[] excelheader = { "所属区域（地市）", "机房", "机架资源情况", "", "", "", "",
  "", "端口资源情况", "", "", "", "", "", "机位资源情况", "", "", "设备资源情况",
  "", "", "ip资源情况", "", "", "", "", "网络设备数" };
string[] excelheader1 = { "", "", "总量(个)", "空闲(个)", "预占(个)", "实占(个)",
  "自用(个)", "其它(个)", "总量(个) ", "在用(个)", "空闲(个)", "总带宽(m)",
  "在用带宽(m)", "空闲带宽(m)", "总量(个)", "在用(个)", "空闲(个)", "设备总量(个)",
  "客户设备(个)", "电信设备(个)", "总量(个)", "空闲(个)", "预占用(个)", "实占用(个)",
  "自用(个)", "" };
// 单元格列宽
int[] excelheaderwidth = { 150, 120, 100, 100, 100, 100, 100, 100, 100,
  100, 100, 120, 120, 120, 120, 120, 120, 150, 150, 150, 120,
  120, 150, 150, 120, 150 };
hssfworkbook wb = new hssfworkbook();
hssfsheet sheet = wb.createsheet("机房报表统计");
hssfrow row = sheet.createrow((int) 0);
hssfcellstyle style = wb.createcellstyle();
// 设置居中样式
style.setalignment(hssfcellstyle.align_center); // 水平居中
style.setverticalalignment(hssfcellstyle.vertical_center); // 垂直居中
// 设置合计样式
hssfcellstyle style1 = wb.createcellstyle();
font font = wb.createfont();
font.setcolor(hssfcolor.red.index);
font.setboldweight(font.boldweight_bold); // 粗体
style1.setfont(font);
style1.setalignment(hssfcellstyle.align_center); // 水平居中
style1.setverticalalignment(hssfcellstyle.vertical_center); // 垂直居中
// 合并单元格
// first row (0-based) last row (0-based) first column (0-based) last
// column (0-based)
sheet.addmergedregion(new cellrangeaddress(0, 1, 0, 0));
sheet.addmergedregion(new cellrangeaddress(0, 1, 1, 1));
sheet.addmergedregion(new cellrangeaddress(0, 0, 2, 7));
sheet.addmergedregion(new cellrangeaddress(0, 0, 8, 13));
sheet.addmergedregion(new cellrangeaddress(0, 0, 14, 16));
sheet.addmergedregion(new cellrangeaddress(0, 0, 17, 19));
sheet.addmergedregion(new cellrangeaddress(0, 0, 20, 24));
sheet.addmergedregion(new cellrangeaddress(0, 1, 25, 25));
// 设置列宽度（像素）
for (int i = 0; i < excelheaderwidth.length; i++) {
  sheet.setcolumnwidth(i, 32 * excelheaderwidth[i]);
}
// 添加表格头
for (int i = 0; i < excelheader.length; i++) {
  hssfcell cell = row.createcell(i);
  cell.setcellvalue(excelheader[i]);
  cell.setcellstyle(style);
}
row = sheet.createrow((int) 1);
for (int i = 0; i < excelheader1.length; i++) {
  hssfcell cell = row.createcell(i);
  cell.setcellvalue(excelheader1[i]);
  cell.setcellstyle(style);
}

注意点1：合并单元格    new cellrangeaddress(int,int,int,int)
first row (0-based) ,last row (0-based), first column (0-based),last column (0-based)

注意点2：合并单元格
string[] excelheader = { "所属区域（地市）", "机房", "机架资源情况", "", "", "", "","", "端口资源情况", "", "", "", "", "", "机位资源情况", "", "", "设备资源情况","", "", "ip资源情况", "", "", "", "", "网络设备数" };

合并以后的单元格虽然是一个，但是仍然要保留其单元格内容，此处用空字符串代替，否则后续表头显示不出

注意点3：填充单元格

正确写法：

hssfcell cell = row.createcell(i);
cell.setcellvalue(excelheader1[i]);
cell.setcellstyle(style);

错误写法：

row.createcell(i).setcellvalue(excelheader1[i]);
row.createcell(i).setcellstyle(style);

本人为了省一个hssfcell对象，使用了错误写法，导致hssfcell对象创建了2次，最后只保留了样式，而内容无法显示

更多关于java相关内容感兴趣的读者可查看本站专题：《java数据结构与算法教程》、《java文件与目录操作技巧汇总》、《java操作dom节点技巧总结》和《java缓存操作技巧汇总》

希望本文所述对大家java程序设计有所帮助。