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

发布时间：2020-12-14 14:28:06 所属栏目：Java 来源：网络整理

导读：本篇章节讲解Java Web使用POI导出Excel的方法。供大家参考研究具体如下：采用Spring mvc架构： Controller层代码如下 @Controllerpublic class StudentExportController{ @Autowired private StudentExportService studentExportService; @Reques

本篇章节讲解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,150,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));
sheet.addMergedRegion(new CellRangeAddress(0,1));
sheet.addMergedRegion(new CellRangeAddress(0,2,7));
sheet.addMergedRegion(new CellRangeAddress(0,8,13));
sheet.addMergedRegion(new CellRangeAddress(0,14,16));
sheet.addMergedRegion(new CellRangeAddress(0,17,19));
sheet.addMergedRegion(new CellRangeAddress(0,20,24));
sheet.addMergedRegion(new CellRangeAddress(0,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)
first row (0-based),last row (0-based),first column (0-based),last column (0-based)

注意点2：合并单元格
String[] excelHeader = { "所属区域（地市）","网络设备数" };

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

注意点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程序设计有所帮助。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!