java实现文件导出excel

1. 在pom.xml中添加导出excel的依赖。

	<!-- 导出excel的依赖 -->
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-ooxml</artifactId>
		<version>3.14-beta1</version>
	</dependency>

2. 在jsp页面添加“导出excel”的超链接

<a href="${pageContext.request.contextPath }/download">导出EXCEL</a>

3. 在控制层编写接收“导出excel”请求的方法。

@RequestMapping("/download")
	public void download(HttpServletResponse response) throws Exception {
		//查询所有员工信息
		List<Employee> emList=empoloyeeService.getAllEmployee();
        //创建一个工作簿
        @SuppressWarnings("resource")
		Workbook workbook=new XSSFWorkbook(); 
		Sheet sheet = workbook.createSheet("登录日志");
        //创建单元格，并设置值表头 设置表头居中
        CellStyle titleStyle = workbook.createCellStyle();
        //水平居中
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //垂直居中
        titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //字体样式
        XSSFFont font = (XSSFFont) workbook.createFont();
        font.setFontName("仿宋_GB2312");
        //粗体显示
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints((short) 12);
        titleStyle.setFont(font);
       
        //定义标题
        String[] titles ={"员工编号","员工姓名","职位","入职日期","薪水","奖金","所在部门"};
        //行
        Row row = sheet.createRow(0);
        //写入标题行
        for (int i =0;i<titles.length;i++){
            Cell cell=row.createCell(i);
            cell.setCellValue(titles[i]);
            //标题行设置宽高 256为单个字符所占的宽度 1.2d表示比实际宽度大20% 12*256表示最低宽度为12个字符
            sheet.setColumnWidth(i, (int)(titles[i].getBytes().length * 1.2d * 256 > 12 * 256 ? titles[i].getBytes().length * 1.2d * 256 : 12 * 256));
            cell.setCellStyle(titleStyle);
        }
        //创建单元格，并设置值表头 设置表头居中
        CellStyle cellStyle = workbook.createCellStyle();
        //水平居中
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //垂直居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //自动换行
        cellStyle.setWrapText(true);
        //根据标题查询行数
        for (int i = 0;i<emList.size();i++){
            //创建第i+1行
            row=sheet.createRow(i+1);
            //得到第一个字段的值
            Employee mid=emList.get(i);
            //创建第i个列
            Cell empnoCell =row.createCell(0);
            //写入第i+1行第i列  写入第一个字符
            empnoCell.setCellValue(mid.getEmpno());		            
            //写第二个字符
            Cell enameCell =row.createCell(1);
            enameCell.setCellValue(mid.getEname());
            Cell jobCell =row.createCell(2);
            jobCell.setCellValue(mid.getJob());
            Cell hiredateCell =row.createCell(3);
            hiredateCell.setCellValue(mid.getHiredate());
            Cell salCell =row.createCell(4);
            salCell.setCellValue(mid.getSal());
            Cell commCell =row.createCell(5);
            commCell.setCellValue(mid.getComm());
            Cell dnameCell =row.createCell(6);
            dnameCell.setCellValue(mid.getDepartment().getDname());
            //设置居中
			row.getCell(0).setCellStyle(cellStyle);
			row.getCell(1).setCellStyle(cellStyle);
			row.getCell(2).setCellStyle(cellStyle);
			row.getCell(3).setCellStyle(cellStyle);
			row.getCell(4).setCellStyle(cellStyle);
			row.getCell(5).setCellStyle(cellStyle);
			row.getCell(6).setCellStyle(cellStyle);
        }
        //创建文件名称
        String fileName= URLEncoder.encode("员工信息.xlsx","UTF-8");
        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition","attachment;filename="+fileName);
        response.setHeader("filename",fileName);      
        //写入文件
        workbook.write(response.getOutputStream());   
	}

4. 测试。测试结果如下：