官方下载：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次，最后只保留了样式，而内容无法显示