java webpoi 导出excel 2007_Java Web利用POI导出Excel简单例子

常用组件：

HSSFWorkbook excel的文档对象

HSSFSheet excel的表单

HSSFRow excel的行

HSSFCell excel的格子单元

HSSFFont excel字体

HSSFDataFormat 日期格式

HSSFHeader sheet头

HSSFFooter sheet尾(只有打印的时候才能看到效果)

样式：

HSSFCellStyle cell样式

辅助操作包括：

HSSFDateUtil 日期

HSSFPrintSetup 打印

HSSFErrorConstants 错误信息表

一、控制器，将HSSFWorkbook【excel】对象流输出下载到本地

1 @Controller2 public classStudentExportController{3

4 @Autowired5 privateStudentExportService studentExportService;6

7 @RequestMapping(value = "/excel/export")8 public voidexportExcel(HttpServletRequest request, HttpServletResponse response)9 throwsException {10

11 List list = new ArrayList();12 list.add(new Student(1000,"zhangsan","20"));13 list.add(new Student(1001,"lisi","23"));14 list.add(new Student(1002,"wangwu","25"));15 HSSFWorkbook wb =studentExportService.export(list);16 response.setContentType("application/vnd.ms-excel");17 response.setHeader("Content-disposition", "attachment;filename=student.xls");18 OutputStream ouputStream =response.getOutputStream();19 wb.write(ouputStream);20 ouputStream.flush();21 ouputStream.close();22 }23 }

二、逻辑处理方法，用于生成HSSFWorkbook【excel】对象

1 @Service2 public classStudentExportService {3

4 String[] excelHeader = { "Sno", "Name", "Age"};5 public HSSFWorkbook export(Listlist) {6 HSSFWorkbook wb = newHSSFWorkbook();7 HSSFSheet sheet = wb.createSheet("Campaign");8 HSSFRow row = sheet.createRow((int) 0);9 HSSFCellStyle style =wb.createCellStyle();10 style.setAlignment(HSSFCellStyle.ALIGN_CENTER);11

12 for (int i = 0; i < excelHeader.length; i++) {13 HSSFCell cell =row.createCell(i);14 cell.setCellValue(excelHeader[i]);15 cell.setCellStyle(style);16 sheet.autoSizeColumn(i);17 }18

19 for (int i = 0; i < list.size(); i++) {20 row = sheet.createRow(i + 1);21 Student student =list.get(i);22 row.createCell(0).setCellValue(student.getSno());23 row.createCell(1).setCellValue(student.getName());24 row.createCell(2).setCellValue(student.getAge());25 }26 returnwb;27 }28 }

三、前端调用

1

2 functionexportExcel(){3 location.href="excel/export";4

5 }6

设置Excel样式以及注意点：

1 String[] excelHeader = { "所属区域(地市)", "机房", "机架资源情况", "", "", "", "",2 "", "端口资源情况", "", "", "", "", "", "机位资源情况", "", "", "设备资源情况",3 "", "", "IP资源情况", "", "", "", "", "网络设备数"};4 String[] excelHeader1 = { "", "", "总量(个)", "空闲(个)", "预占(个)", "实占(个)",5 "自用(个)", "其它(个)", "总量(个) ", "在用(个)", "空闲(个)", "总带宽(M)",6 "在用带宽(M)", "空闲带宽(M)", "总量(个)", "在用(个)", "空闲(个)", "设备总量(个)",7 "客户设备(个)", "电信设备(个)", "总量(个)", "空闲(个)", "预占用(个)", "实占用(个)",8 "自用(个)", ""};9 //单元格列宽

10 int[] excelHeaderWidth = { 150, 120, 100, 100, 100, 100, 100, 100, 100,11 100, 100, 120, 120, 120, 120, 120, 120, 150, 150, 150, 120,12 120, 150, 150, 120, 150};13

14 HSSFWorkbook wb = newHSSFWorkbook();15 HSSFSheet sheet = wb.createSheet("机房报表统计");16 HSSFRow row = sheet.createRow((int) 0);17 HSSFCellStyle style =wb.createCellStyle();18 //设置居中样式

19 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平居中

20 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直居中

21

22 //设置合计样式

23 HSSFCellStyle style1 =wb.createCellStyle();24 Font font =wb.createFont();25 font.setColor(HSSFColor.RED.index);26 font.setBoldweight(Font.BOLDWEIGHT_BOLD); //粗体

27 style1.setFont(font);28 style1.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平居中

29 style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直居中

30

31 //合并单元格

32 //first row (0-based) last row (0-based) first column (0-based) last

33 //column (0-based)

34 sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));35 sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));36 sheet.addMergedRegion(new CellRangeAddress(0, 0, 2, 7));37 sheet.addMergedRegion(new CellRangeAddress(0, 0, 8, 13));38 sheet.addMergedRegion(new CellRangeAddress(0, 0, 14, 16));39 sheet.addMergedRegion(new CellRangeAddress(0, 0, 17, 19));40 sheet.addMergedRegion(new CellRangeAddress(0, 0, 20, 24));41 sheet.addMergedRegion(new CellRangeAddress(0, 1, 25, 25));42 //设置列宽度(像素)

43 for (int i = 0; i < excelHeaderWidth.length; i++) {44 sheet.setColumnWidth(i, 32 *excelHeaderWidth[i]);45 }46

47 //添加表格头

48 for (int i = 0; i < excelHeader.length; i++) {49 HSSFCell cell =row.createCell(i);50 cell.setCellValue(excelHeader[i]);51 cell.setCellStyle(style);52 }53 row = sheet.createRow((int) 1);54 for (int i = 0; i < excelHeader1.length; i++) {55 HSSFCell cell =row.createCell(i);56 cell.setCellValue(excelHeader1[i]);57 cell.setCellStyle(style);58 }

注意点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次，最后只保留了样式，而内容无法显示