Java Web利用POI导出Excel简单例子

官方下载:http://poi.apache.org/download.html这里可以下载到它的最新版本和文档

 

常用组件:

HSSFWorkbook                      excel的文档对象

HSSFSheet                         excel的表单

HSSFRow                           excel的行

HSSFCell                          excel的格子单元

HSSFFont                          excel字体

HSSFDataFormat                    日期格式

HSSFHeader                        sheet头

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

样式:

HSSFCellStyle                       cell样式

辅助操作包括:

HSSFDateUtil                        日期

HSSFPrintSetup                      打印

HSSFErrorConstants                  错误信息表

 

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

 1 @Controller    
 2 public class StudentExportController{    
 3     
 4     @Autowired    
 5     private StudentExportService studentExportService;    
 6     
 7     @RequestMapping(value = "/excel/export")    
 8     public void exportExcel(HttpServletRequest request, HttpServletResponse response)     
 9     throws Exception {    
10             
11         List<Student> list = new ArrayList<Student>();    
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 @Service    
 2 public class StudentExportService {    
 3     
 4     String[] excelHeader = { "Sno", "Name", "Age"};    
 5     public HSSFWorkbook export(List<Campaign> list) {    
 6         HSSFWorkbook wb = new HSSFWorkbook();    
 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         return wb;    
27     }    
28 }    

三、前端调用

1 <script>    
2 function exportExcel(){    
3      location.href="excel/export";    
4      <!--这里不能用ajax请求,ajax请求无法弹出下载保存对话框-->    
5  }    
6 </script>    

设置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 = new HSSFWorkbook();  
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次,最后只保留了样式,而内容无法显示

 

posted @ 2017-03-27 16:44  Ramesses  阅读(351)  评论(0编辑  收藏  举报