1.POI可以操作的excel版本
poi.jar可以操作的EXcel版本?
poi.jar中只能操作97-2003版本的Excel
2.Excel组成:
1.excel文档后缀名.xls结尾
2.excel文档有若干个(小页)组成,小页之间空间,彼此独立
3.每一个【小页】都有自己的名称以及位置
4.每一个【小页】都会包含大量的【单元格】这些单元格是Excel保存数据的基本单元
5.【单元格】{保存数据,坐在行数,所在列数}
3.poi提供的API:
1.HSSFWorkbook------》描述内存中的Excel文档的对象
2.HSSFSheet---------》描述内存中的【小页】对象
3.HSSFCell-----------》描述内存中的【单元格】对象
4.HSSFRow------------》描述内存中的【行】对象
4.poi的maven依赖:
<!--export的依赖poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!-- 使用poi生成excel表格 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>3.9</version>
</dependency>
5.把页面数据以Excel导出:
response.reset();清除buffer。设置页面不缓存注意事项:
response.setHeader("Content- disposition","attachment;filename="+new String(str.getBytes("gb2312"),"iso8859-1")); // 客户使用目标另存为对话框保存指定文件
response.setHeader("Content_Length",length);设置头文件的长度为指定文件的长度
6.实例1
/**
* 导出表格
*/
@RequestMapping("/download")
public void downloadExcel(HttpServletRequest request,HttpServletResponse response){
HSSFWorkbook workbook = new HSSFWorkbook();// 创建一个Excel文件
HSSFSheet sheet = workbook.createSheet();// 创建一个Excel的Sheet
// 设置列宽
sheet.setColumnWidth(0, 1000);
sheet.setColumnWidth(1, 3500);
sheet.setColumnWidth(2, 3500);
sheet.setColumnWidth(3, 6500);
sheet.setColumnWidth(4, 6500);
sheet.setColumnWidth(5, 6500);
sheet.setColumnWidth(6, 6500);
sheet.setColumnWidth(7, 6500);
// Sheet样式
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setBorderBottom(CellStyle.BORDER_THIN); //下边框
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);//左边框
cellStyle.setBorderTop(CellStyle.BORDER_THIN);//上边框
cellStyle.setBorderRight(CellStyle.BORDER_THIN);//右边框
cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
// 设置列的样式
for (int i = 0; i <= 7; i++) {
sheet.setDefaultColumnStyle((short) i, cellStyle);
}
// 设置字体
HSSFFont headfont = workbook.createFont();
headfont.setFontName("黑体");
headfont.setFontHeightInPoints((short) 22);// 字体大小
headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
//字体
HSSFFont font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 10);
try {
// 第一行
HSSFRow row1 = sheet.createRow(0);
row1.setHeight((short) 450);
HSSFCell cell = row1.createCell(0);
cell.setCellValue(new HSSFRichTextString("序号"));
cell.setCellStyle(cellStyle);
cell = row1.createCell(1);
cell.setCellValue(new HSSFRichTextString("部门"));
cell.setCellStyle(cellStyle);
cell = row1.createCell(2);
cell.setCellValue(new HSSFRichTextString("员工姓名"));
cell.setCellStyle(cellStyle);
cell = row1.createCell(3);
cell.setCellValue(new HSSFRichTextString("工号"));
cell.setCellStyle(cellStyle);
cell = row1.createCell(4);
cell.setCellValue(new HSSFRichTextString("手机号"));
cell.setCellStyle(cellStyle);
cell = row1.createCell(5);
cell.setCellValue(new HSSFRichTextString("申请人姓名"));
cell.setCellStyle(cellStyle);
cell = row1.createCell(6);
cell.setCellValue(new HSSFRichTextString("申请人身份证号"));
cell.setCellStyle(cellStyle);
cell = row1.createCell(7);
cell.setCellValue(new HSSFRichTextString("申请人手机号"));
cell.setCellStyle(cellStyle);
// 访问数据库,得到数据集
List<UserInfo> list = qrCodeService.queryList();
for (int j = 0; j < list.size(); j++) {
UserInfo u = list.get(j);
// 遍历数据集创建Excel的行
HSSFRow row = sheet.getRow(1 + j);
if (null == row) {
row = sheet.createRow(1 + j);
}
//设置行高
row.setHeight((short) 450);
cell = row.createCell(0);
cell.setCellValue(u.getId());
cell.setCellStyle(cellStyle);
cell = row.createCell(1);
cell.setCellValue(u.getDepartment());
cell.setCellStyle(cellStyle);
cell = row.createCell(2);
cell.setCellValue(new HSSFRichTextString(u.getEmployeeName()));
cell.setCellStyle(cellStyle);
cell = row.createCell(3);
cell.setCellValue(new HSSFRichTextString(u.getJobNumber()));
cell.setCellStyle(cellStyle);
cell = row.createCell(4);
cell.setCellValue(new HSSFRichTextString(u.getEmployeePhone()));
cell.setCellStyle(cellStyle);
cell = row.createCell(5);
cell.setCellValue(new HSSFRichTextString(u.getUserName()));
cell.setCellStyle(cellStyle);
cell = row.createCell(6);
cell.setCellValue(new HSSFRichTextString(u.getUserIDCard()));
cell.setCellStyle(cellStyle);
cell = row.createCell(7);
cell.setCellValue(new HSSFRichTextString(u.getUserPhone()));
cell.setCellStyle(cellStyle);
}
String filename = "未命名.xls";//设置下载时客户端Excel的名称
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename=" + filename);
response.addHeader("Cache-Control", "no-cache");
OutputStream ouputStream = response.getOutputStream();
workbook.write(ouputStream);
ouputStream.flush();
ouputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}