HSSF EXCEL 导出

1.所需依赖jar包

 1 <dependency>
 2             <groupId>org.apache.poi</groupId>
 3             <artifactId>poi</artifactId>
 4             <version>3.9</version>
 5         </dependency>
 6         <dependency>
 7             <groupId>org.apache.poi</groupId>
 8             <artifactId>poi-ooxml</artifactId>
 9             <version>3.9</version>
10 </dependency>

View Code

2.样式

 1 public static HSSFCellStyle getGroomHeaderStyle(HSSFWorkbook wb) {
 2         // 设置边框
 3         HSSFCellStyle style = wb.createCellStyle();
 4         style.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框
 5         style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
 6         style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
 7         style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
 8 
 9         // 单元格字体
10         HSSFFont f = wb.createFont();
11         f.setFontHeightInPoints((short) 10);// 字号
12         f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);// 加粗
13         style.setFont(f);
14         style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
15         style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
16         // style.setRotation(short rotation);//单元格内容的旋转的角度
17 
18         // 设置背景和字体颜色
19 
20         style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
21         style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
22 
23         // 设置自动换行
24         // style.setWrapText(true);
25         return style;
26     }

View Code

3.获取数据源并生成excel

 1   @RequestMapping(value = "/exportSysUserInfo", method = {RequestMethod.GET, RequestMethod.POST})
 2     public void exportSysUserInfo(HttpServletRequest request, HttpServletResponse response, Integer[] userIds, String usernameTx, String loginTx) throws UnsupportedEncodingException {
 3         String filePath = request.getSession().getServletContext()
 4                 .getRealPath("/").replace("\\", "/")
 5                 + "/";
 6         filePath = filePath + ExcelExportUtil.TEMP_DOWN_EXCEL_PATH + "/"
 7                 + ".xls";
 8 
 9         SysUser sysUser = (SysUser) request.getSession().getAttribute("userSession");
10         Integer isAdmin = sysUser.getIsAdmin();
11         Integer comId = sysUser.getComId();
12 
13         try {
14             if (null != usernameTx && !"".equals(usernameTx)) {
15                 if (usernameTx.equals(new String(usernameTx.getBytes("iso8859-1"), "iso8859-1"))) {
16                     usernameTx = new String(usernameTx.getBytes("iso8859-1"), "utf-8");
17                 }
18             }
19 
20             if (null != loginTx && !"".equals(loginTx)) {
21                 if (loginTx.equals(new String(loginTx.getBytes("iso8859-1"), "iso8859-1"))) {
22                     loginTx = new String(loginTx.getBytes("iso8859-1"), "utf-8");
23                 }
24             }
25 
26             List<SysUserExport> sysUserExportList = sysUserService.getListExportSysUser(userIds, usernameTx, loginTx, isAdmin, comId);
27 
28             HSSFWorkbook wb = new HSSFWorkbook();
29             // 工作簿
30             HSSFSheet sheet = wb.createSheet("科技人员信息");
31 
32             // 单元格样式
33             HSSFCellStyle cellStyle = ExcelExportUtil.getGroomCellStyle(wb);
34             // 工作簿 头部 标题样式
35             HSSFCellStyle headStyle = ExcelExportUtil.getGroomHeaderStyle(wb);
36             // 创建表格头部
37             createHeaderRow(sheet, headStyle);
38 
39             HSSFRow row = null;
40             // 添加数据
41             for (int i = 0; i < sysUserExportList.size(); i++) {
42                 SysUserExport sysUserExport = sysUserExportList.get(i);
43 
44                 //出生日期
45                 sysUserExport.setBirthday(sysUserExport.getBirthday().substring(0, 10));
46 
47                 row = sheet.createRow(i + 1);
48                 ExcelExportUtil.createCell(row, 0, cellStyle, sysUserExport.getUsername());//用户名
49                 ExcelExportUtil.createCell(row, 1, cellStyle, sysUserExport.getGender());//工号(登录名)
50                 ExcelExportUtil.createCell(row, 2, cellStyle, sysUserExport.getBirthday());//工号(登录名)
51                 ExcelExportUtil.createCell(row, 3, cellStyle, sysUserExport.getTelePhone());//电话
52                 ExcelExportUtil.createCell(row, 4, cellStyle, sysUserExport.getPosition());//岗位
53                 ExcelExportUtil.createCell(row, 5, cellStyle, sysUserExport.getNote());//工作职责
54                 ExcelExportUtil.createCell(row, 6, cellStyle, sysUserExport.getComName());//所属组织
55                 ExcelExportUtil.createCell(row, 7, cellStyle, sysUserExport.getDepName());//所属部门
56                 //新增字段
57                 ExcelExportUtil.createCell(row, 8, cellStyle, sysUserExport.getIsComputer());//计算机相关专业
58                 ExcelExportUtil.createCell(row, 9, cellStyle, sysUserExport.getMajorName());//专业名称
59                 ExcelExportUtil.createCell(row, 10, cellStyle, sysUserExport.getEducation());//学历
60                 ExcelExportUtil.createCell(row, 11, cellStyle, sysUserExport.getWorkingYears());//从事银行工作年限
61                 ExcelExportUtil.createCell(row, 12, cellStyle, sysUserExport.getIsBadRecord());//不良记录信息
62                 ExcelExportUtil.createCell(row, 13, cellStyle, sysUserExport.getIsSignAgreement());//签订保密协议
63                 ExcelExportUtil.createCell(row, 14, cellStyle, sysUserExport.getEducationFileName());//学历证书
64                 ExcelExportUtil.createCell(row, 15, cellStyle, sysUserExport.getJobFileName());//工作简历
65 
66             }
67 
68             FileOutputStream fos = new FileOutputStream(filePath);
69             wb.write(fos);
70             fos.close();
71         } catch (FileNotFoundException e) {
72             log.info("导出找不到文件！");
73         } catch (IOException e) {
74             log.info("文件流异常！");
75         }
76         // 下载文件
77         ExcelExportUtil.downLoadExcel(request, response, filePath, "科技人员信息");
78     }

View Code

4.生成excel表头

 1     private void createHeaderRow(HSSFSheet sheet, HSSFCellStyle style) {
 2         // 工作表的第0行，表头部分
 3         HSSFRow row = sheet.createRow(0);
 4 
 5         // 设置列宽度，第一个参数代表列id(从0开始),第2个参数代表宽度值 参考 ："2012-08-10"的宽度为2500
 6         sheet.setColumnWidth(0, 3766);
 7         sheet.setColumnWidth(1, 3766);
 8         sheet.setColumnWidth(2, 3766);
 9         sheet.setColumnWidth(3, 4500);
10         sheet.setColumnWidth(4, 3766);
11         sheet.setColumnWidth(5, 10000);
12         sheet.setColumnWidth(6, 6000);
13         sheet.setColumnWidth(7, 6000);
14         //新增字段
15         sheet.setColumnWidth(8, 3766);
16         sheet.setColumnWidth(9, 6000);
17         sheet.setColumnWidth(10, 3766);
18         sheet.setColumnWidth(11, 6000);
19         sheet.setColumnWidth(12, 3766);
20         sheet.setColumnWidth(13, 3766);
21         sheet.setColumnWidth(14, 6000);
22         sheet.setColumnWidth(15, 6000);
23 
24         // 设置行高
25         row.setHeight((short) 500);
26         //人员基本信息
27         ExcelExportUtil.createCell(row, 0, style, "用户名");
28         ExcelExportUtil.createCell(row, 1, style, "性别");
29         ExcelExportUtil.createCell(row, 2, style, "出生日期");
30         ExcelExportUtil.createCell(row, 3, style, "电话");
31         ExcelExportUtil.createCell(row, 4, style, "岗位");
32         ExcelExportUtil.createCell(row, 5, style, "工作职责");
33         ExcelExportUtil.createCell(row, 6, style, "所属组织");
34         ExcelExportUtil.createCell(row, 7, style, "所属部门");
35         //人员详细信息
36         ExcelExportUtil.createCell(row, 8, style, "计算机相关专业");
37         ExcelExportUtil.createCell(row, 9, style, "专业名称");
38         ExcelExportUtil.createCell(row, 10, style, "学历");
39         ExcelExportUtil.createCell(row, 11, style, "从事银行工作年限");
40         ExcelExportUtil.createCell(row, 12, style, "不良记录信息");
41         ExcelExportUtil.createCell(row, 13, style, "签订保密协议");
42         ExcelExportUtil.createCell(row, 14, style, "学历证书");
43         ExcelExportUtil.createCell(row, 15, style, "工作简历");
44     }

View Code

转载于:https://www.cnblogs.com/HotDogHome/p/9468434.html