导出功能实现

最新推荐文章于 2022-08-12 09:41:47 发布

勇敢的兵

最新推荐文章于 2022-08-12 09:41:47 发布

阅读量831

点赞数

分类专栏： 05软件开发文章标签： poi

本文链接：https://blog.csdn.net/qq_37251897/article/details/103555314

版权

05软件开发专栏收录该内容

32 篇文章 0 订阅

订阅专栏

文章目录

在这里插入图片描述

1、引入FileSaver框架

<script src="js/FileSaver.js"></script>

2、导出button

<div id="toolbar" class="toolbar">
    <button type="button" class="btn btn-primary" onclick="add();">
        <span class="glyphicon glyphicon-plus" aria-hidden="true"></span> 添加
    </button>
    <button type="button" class="btn btn-primary" onclick="exportData();">
        <span class="glyphicon glyphicon-export" aria-hidden="true"></span> 导出
    </button>
</div>

3、js函数

function exportData(){
    //window.open("export" ,"_blank");
    var xhr ;
    if(window.XMLHttpRequest){//code for IE7+,Firefox,Chrome,Opera,Safari
        xhr = new XMLHttpRequest();
    }else{//code for IE6,IE5
        xhr = new ActiveXObject("Microsoft.XMLHTTP");
    }
    var url = 'export';
    xhr.open("post", url, true);
    //设置响应类型为blob类型
    xhr.responseType = "blob";    
    xhr.onload = function () {
        if (this.status == "200") {
            var name = xhr.getResponseHeader("Content-disposition");
            var fileName = name.substring(20, name.length);
            //获取响应文件流　　
            var blob = this.response;
            if (blob && blob.size > 0) {
                saveAs(blob, fileName);//处理二进制数据，让浏览器认识它
            }
        }
    }
    xhr.send();
}

4、模板文件

在这里插入图片描述

5、添加依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.1</version>
</dependency>

6、文件导出

    /**
     * 导出学生列表
     * @return
     */
    @PostMapping(value = "/export")
    @ResponseBody
    public ActionResult export(){
        ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletResponse response = requestAttributes.getResponse();
        //1.创建工作簿
        //String path = servletContext.getRealPath("/");
        //path = path+"/make/students.xls";   //得到模板文件所在位置

        File file = null;
        try {
            file = ResourceUtils.getFile("classpath:make/students.xls");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            return new ActionResult(-1,"出现异常",null);
        }

        InputStream is = null;     //根据文件，得到指定的文件流
        try {
            is = new FileInputStream(file);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            return new ActionResult(-1,"出现异常",null);
        }

        //根据文件流，加载指定的工作簿
        //它只能操作excel2003版本
        Workbook wb = null;
        try {
            wb = new HSSFWorkbook(is);
        } catch (IOException e) {
            e.printStackTrace();
            return new ActionResult(-1,"出现异常",null);
        }

        //2.读取工作表
        Sheet sheet = wb.getSheetAt(0);   //0代表工作表的下标

        //抽取出一些公用变量
        Row nRow=null;
        Cell nCell = null;

        int rowNo=1;//行号
        int cellNo=0;//列号

        //===========================================数据内容
        nRow = sheet.getRow(rowNo);//读取第2行

        //
        CellStyle snoCellStyle = nRow.getCell(cellNo++).getCellStyle();//读取单元格的样式
        String str = nRow.getCell(cellNo).getStringCellValue();//读取单元格的内容
        System.out.println(str);

        CellStyle snameCellStyle = nRow.getCell(cellNo++).getCellStyle();//读取单元格的样式
        CellStyle isMaleCellStyle = nRow.getCell(cellNo++).getCellStyle();//读取单元格的样式
        CellStyle birthCellStyle = nRow.getCell(cellNo++).getCellStyle();//读取单元格的样式

        Connection conn = null;
        PreparedStatement stmt = null;
        List<Student> list = new ArrayList<Student>();
        try {
            conn = dbUtil.getConnection();
            stmt = conn.prepareStatement("SELECT * FROM STUDENT ORDER BY SNO");
            ResultSet rs = stmt.executeQuery();
            while(rs.next()) {
                list.add(new Student(rs.getInt(1),rs.getString(2),rs.getString(3).equals("男"),rs.getDate(4),rs.getString(5)));
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return new ActionResult(-1,"出现异常",null);
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

        //遍历学生列表
        for(Student stu :list){
            //产生一个新行
            nRow = sheet.createRow(rowNo++);
            //nRow.setHeightInPoints(24f);//设置行高

            cellNo=0;

            nCell = nRow.createCell(cellNo++);//创建单元格
            nCell.setCellValue(stu.getSno());//设置单元格内容
            nCell.setCellStyle(snoCellStyle);    //设置单元格样式

            nCell = nRow.createCell(cellNo++);//创建单元格
            nCell.setCellValue(stu.getSname());//设置单元格内容
            nCell.setCellStyle(snameCellStyle);    //设置单元格样式

            nCell = nRow.createCell(cellNo++);//创建单元格
            nCell.setCellValue(stu.isMale());//设置单元格内容
            nCell.setCellStyle(isMaleCellStyle);    //设置单元格样式

            nCell = nRow.createCell(cellNo++);//创建单元格
            nCell.setCellValue(stu.getBirth());//设置单元格内容
            nCell.setCellStyle(birthCellStyle);    //设置单元格样式

        }

        //输出
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();//内存的缓冲区
        try {
            wb.write(byteArrayOutputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }

        DownloadUtil downloadUtil = new DownloadUtil();
        Calendar cal = Calendar.getInstance();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        //response.setHeader("Transfer-Encoding", "chunked");
        String returnName = "students" + sdf.format(cal.getTime()) + ".xls";
        try {
            downloadUtil.download(byteArrayOutputStream, response, returnName);
        } catch (IOException e) {
            e.printStackTrace();
            return new ActionResult(-1,"出现异常",null);
        }
        return null;
    }