java实现下载excel功能

1,获取服务器现有excel文件

public List<Object[]> getObject(String filePath){
        
        log.info("**文件路径为:**"+filePath);
        
        List<Object[]> mediacelList = new ArrayList<Object[]>();
        
        // 构造 Workbook 对象，execelFile 是传入文件路径(获得Excel工作区)  
        Workbook book = null;
        try
        {
            book = new HSSFWorkbook(new FileInputStream(filePath));
        } catch (Exception e)
        {
            e.printStackTrace();
            log.error("***获取服务器文件***",e);
        }
        
        // 读取表格的第一个sheet页  
        Sheet sheet = book.getSheetAt(0);  
        
        // 定义 row、cell  
        Row row;  
        
        // 总共有多少行,从0开始  
        int totalRows = sheet.getLastRowNum() ;  
        
        // 循环输出表格中的内容,首先循环取出行,再根据行循环取出列  
        for (int i = 0; i <= totalRows; i++) { 
            
            Object[] ob = new Object[23];
            
            row = sheet.getRow(i);  
            
            // 处理空行  
            if(row == null){  
                continue ;  
            } 
            
            // 总共有多少列,从0开始  
            int totalCells = row.getLastCellNum() ;  
            
            for (int j = 0; j < totalCells; j++) {
                
                 Object cell = row.getCell(j); 
                 ob[j] = cell;
            }  
            mediacelList.add(ob);
        }  
        return mediacelList;
    }

2,将步骤1返回的数据转换成excel所需要的字符串格式

    private String dataToString(List data) {
        
        StringBuffer temp = new StringBuffer();
        
        if (data != null && data.size() > 0) {
            
            for (int i = 0; i < data.size(); i++) {
                Object[] obj = (Object[]) data.get(i);
                if (obj == null)
                    break;
                for (int j = 0; j <= obj.length - 2; j++) {
                    if (obj[j] == null) {
                        log.info("obj[j]为空===============" + obj[j]);
                        temp.append(" \t");
                    } else {
                        log.info("obj[" + j + "]==========================="
                                + obj[j]);
                        temp.append(obj[j].toString() + " \t");
                    }
                }
                if (obj[obj.length - 1] == null) {
                    temp.append(" \n");
                } else {
                    temp.append(obj[obj.length - 1].toString() + "\n");
                }
            }
        }
        return temp.toString();
    }

3,执行下载功能

    @RequestMapping("/downLoadFailRecord")
    public ModelAndView downLoadFailRecord(
            HttpServletRequest request,HttpServletResponse response,
            @ModelAttribute("filePath")String filePath) throws Exception{
        
        log.info("=========下载啦啦啦啦=======进入downLoadFailRecord===========filePath："+filePath);
        
        String excelData = "";
        
        String str = "医路通保存失败统计记录";
        
        excelData = medicalService.getFailMedical(filePath);
        
        log.info("*******解析的字符串为:"+excelData);
        
        response.setContentType("application/ms-txt;charset=UTF-8"); 
        response.addHeader("Content-Disposition","attachment; filename=" + new String(str.getBytes("GBK"),"ISO8859_1") + ".xls"); 
        response.getWriter().write(excelData);
        return null;
    }

 ps:excel文件的xls格式默认纯数字的数据将不能正常显示,所以根据实际经验下载excel文件时设置成csv体验会更好一点

步骤如下:

2,2,将步骤1返回的数据转换成excel所需要的字符串格式,各个字段用逗号(,)隔开

    private String dataToString(List data) {
        
        StringBuffer temp = new StringBuffer();
        
        if (data == null || data.size() <= 0) {
            return null;
        }
        
        for (int i = 0; i < data.size(); i++) {
            
            Object[] obj = (Object[]) data.get(i);
            
            if (obj == null){
                break;
            }
            
            for (int j = 0; j <= obj.length - 2; j++) {
                
                if (obj[j] == null) {
                    temp.append(" ,");
                } else {
                    temp.append(obj[j].toString() + " \t,");
                }
            }
            
            if (obj[obj.length - 1] == null) {
                temp.append(" \n");
            } else {
                temp.append(obj[obj.length - 1].toString() + "\n");
            }
        }
    
        return temp.toString();
    }

 

3,2执行下载功能

    @RequestMapping("/downLoadFailRecord")
    public ModelAndView downLoadFailRecord(
            HttpServletRequest request,HttpServletResponse response,
            @ModelAttribute("filePath")String filePath) throws Exception{
        
        log.debug("====进入downLoadFailRecord===========filePath："+filePath);
        
        String excelData = "";
        
        String str = "医路通失败统计记录";
        
        excelData = medicalService.getFailMedical(filePath);
        
        response.setContentType("application/ms-txt;charset=UTF-8"); 
        response.addHeader("Content-Disposition","attachment; filename=" + new String(str.getBytes("GBK"),"ISO8859_1") + ".csv"); 
        response.getWriter().write(excelData);
        return null;
    }

 拓展:有时候下载的csv存在乱码,则把csv文件另存为...一下就好了,就好了,尽然就好了!!!