Java后台实现Excel文件下载

依赖

使用的是poi

开发中经常会设计到excel的处理，如导出Excel，导入Excel到数据库中，操作Excel目前有两个框架，一个是apache 的poi， 另一个是 Java Excel

Apache POI 简介是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程式对Microsoft Office（Excel、WORD、PowerPoint、Visio等）格式档案读和写的功能。POI为“Poor Obfuscation Implementation”的首字母缩写，意为“可怜的模糊实现”。

官方主页： http://poi.apache.org/index.html
API文档： http://poi.apache.org/apidocs/index.html

<!--Excel解析-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>
        </dependencies>

实现目的

最近公司的项目需要实现页面下载excel文件的功能

请求

在controller层创建一个请求方法

@RequestMapping(value = "/fypclXls",method = RequestMethod.GET)
	public void fypclXls(@RequestParam Map<String,Object> params,HttpServletRequest request,HttpServletResponse response){
		//Query query = new Query(params);
		costDeviationService.fypclXls(params,request,response);
	}

HSSFWorkbook方法

在业务层去创建一个方法

 public HSSFWorkbook getHSSFWorkbook(String sheetName,String[] title,String[][] values,HSSFWorkbook workbook){
 //sheetName 表名称
 //title 表格第一行的表头名称
 //values 存放的内容
 //workbook 实现api的对象
        if (workbook == null){
            workbook = new HSSFWorkbook();
        }
        HSSFSheet sheet = workbook.createSheet(sheetName);
        HSSFRow row = sheet.createRow(0);
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFCell cell = null;
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(cellStyle);
        }
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i+1);
            for (int j = 0; j < values[i].length; j++) {
                row.createCell(j).setCellValue(values[i][j]);
            }
        }
        return workbook;
    }

setResponseHeader方法

实现页面下载文件的方法

public void setResponseHeader(HttpServletRequest request,HttpServletResponse response, String fileName)  {
//fileName 文件名称
        try {
            String agent = request.getHeader("USER-AGENT").toLowerCase();
            if(StringUtils.contains(agent, "Mozilla")){
                fileName = new String(fileName.getBytes(), "ISO8859-1");
            }else {
                fileName = URLEncoder.encode(fileName, "utf8");
            }
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");// 设置contentType为excel格式
            response.setHeader("Content-Disposition", "Attachment;Filename="+ fileName);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

fypclXls方法实现整个下载过程

 @Override
    public void fypclXls(Map<String, Object> params,HttpServletRequest request,HttpServletResponse response) {
    //业务查询出的数据
        List<CostDeviationDo> list = this.list(params);
        //标题
        String[] title = {"病案号","患者姓名","诊断编码","诊断名称","手术编码","手术名称","实际住院费用","均次费用","CV","分值","分值单价","费用偏离率"};
        //xls表名
        String fileName = "费用偏离率.xls";
        String sheetName = "费用偏离率";
        String[][] content = new String[list.size()][title.length];
        for (int i = 0; i < list.size(); i++) {
        //把数据遍历添加到数组中
            CostDeviationDo costDeviationDo = list.get(i);
            content[i][0] = costDeviationDo.getBah();
            content[i][1] = costDeviationDo.getXm();
            content[i][2] = costDeviationDo.getZdbm();
            content[i][3] = costDeviationDo.getZdmc();
            content[i][4] = costDeviationDo.getSsbm();
            content[i][5] = costDeviationDo.getSsmc();
            content[i][6] = String.valueOf(costDeviationDo.getZfy());
            content[i][7] = String.valueOf(costDeviationDo.getCjfy());
            content[i][8] = String.valueOf(costDeviationDo.getCv());
            content[i][9] = String.valueOf(costDeviationDo.getFz());
            content[i][10] = String.valueOf(costDeviationDo.getFzdj());
            content[i][11] = String.valueOf(costDeviationDo.getFypcl());
        }
        try {
        //使用方法得到api对象
            HSSFWorkbook hssfWorkbook = getHSSFWorkbook(sheetName, title, content, null);
            //实现页面下载
            setResponseHeader(request,response,fileName);
           //创建页面输出流对象
            ServletOutputStream outputStream = response.getOutputStream();
            //把文件写入输出流的对象中
            hssfWorkbook.write(outputStream);
            //outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }