使用POI创建Excel对象,存储数据库信息
案例:
此时项目请求的页面是index.jsp,项目是WebDemo,在页面中从数据库中获取有关Employees对象的信息,并保存Excel表:
- 引入poi依赖
<!-- POI相关依赖 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.1</version>
</dependency>
- JSP页面
使用的技术:
Bootstrap3,JS
<div class="divc">
<a href="${app}/emp/download">
<button type="button" id="myButton" data-loading-text="正在下载..." class="btn btn-primary" autocomplete="off">
导出在职员工信息
</button>
</a>
</div>
- Action后台
使用技术POI
List<Employees> employees = employeesService.selectAllEmployees2();
HSSFWorkbook wb = new HSSFWorkbook(); //创建excel对象
HSSFSheet sheet = wb.createSheet("员工信息汇总"); //创建工作表
// 设置默认列宽
sheet.setDefaultColumnWidth(18);
//单元格样式
HSSFCellStyle cellStyle = wb.createCellStyle();
HSSFCellStyle cellStyle2 = wb.createCellStyle();
HSSFCellStyle cellStyle3 = wb.createCellStyle();
cellStyle3.setWrapText(true); //自动换行
//创建字体
HSSFFont font = wb.createFont();
HSSFFont font2 = wb.createFont();
HSSFFont font3 = wb.createFont();
font.setFontHeightInPoints((short) 16); //设置字体大小
font2.setFontHeightInPoints((short) 14); //设置字体大小
font3.setFontHeightInPoints((short) 12); //设置字体大小
font.setBold(true); //粗体显示
font2.setBold(true); //粗体显示
//将字体应用到单元格样式
cellStyle.setFont(font);
cellStyle2.setFont(font2);
cellStyle3.setFont(font3);
cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
cellStyle2.setAlignment(HorizontalAlignment.CENTER); //水平居中
cellStyle3.setAlignment(HorizontalAlignment.CENTER); //水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直居中
cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER); //垂直居中
cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER); //垂直居中
// 设置边框
cellStyle2.setBorderBottom(BorderStyle.THIN); //下边框
cellStyle3.setBorderBottom(BorderStyle.THIN); //下边框
cellStyle2.setBorderLeft(BorderStyle.THIN);//左边框
cellStyle3.setBorderLeft(BorderStyle.THIN);//左边框
cellStyle2.setBorderTop(BorderStyle.THIN);//上边框
cellStyle3.setBorderTop(BorderStyle.THIN);//上边框
cellStyle2.setBorderRight(BorderStyle.THIN);//右边框
cellStyle3.setBorderRight(BorderStyle.THIN);//右边框
//创建第一行 标题
HSSFRow row1 = sheet.createRow(0);
//创建第二行 表头
HSSFRow row2 = sheet.createRow(1);
//合并单元格CellRangeAddress构造参数依次表示起始行,截至行,起始列, 截至列
CellRangeAddress cellRangeAddress =new CellRangeAddress(0,0,0,5);
sheet.addMergedRegion(cellRangeAddress);
//单元格赋值
//设置行高
row1.setHeight((short)(30*20));
row2.setHeight((short)(30*18));
row1.createCell(0).setCellValue("北京***科技有限公司职员信息表");
//设置单元格样式
row1.getCell(0).setCellStyle(cellStyle);
//表头
String[] s = {"ID", "姓名", "年龄", "性别", "职业", "部门"};
for (int i = 0; i < s.length; i++) {
//第二行赋值
Cell cell = row2.createCell(i);
cell.setCellValue(s[i]);
cell.setCellStyle(cellStyle2);
}
//创建第三行 内容
for (int i = 0; i < employees.size(); i++) {
HSSFRow row3 = sheet.createRow(i+2);
row3.setHeight((short)(30*16));
//第三行赋值
row3.createCell(0).setCellValue(employees.get(i).getId()); //ID
row3.createCell(1).setCellValue(employees.get(i).getEmpName()); //name
row3.createCell(2).setCellValue(employees.get(i).getEmpAge()); //age
row3.createCell(3).setCellValue(employees.get(i).getEmpSex()); //sex
row3.createCell(4).setCellValue(employees.get(i).getEmpPost()); //职位
row3.createCell(5).setCellValue(employees.get(i).getSection().getSection_name()); //部门名称
for (int j = 0; j <s.length ; j++) {
row3.getCell(j).setCellStyle(cellStyle3);
}
}
//数据以io流的方式发送给浏览器
try {
Date day = new Date();
response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(day+"北京***科技有限公司职员信息表", "UTF-8")+".xls");
OutputStream outputStream=response.getOutputStream();
wb.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}