Excel模板数据初始化并下载

参考资料：https://github.com/wck810160078/jiaxin_shop/blob/master/src/main/java/com/jiaxin/shop/utils/ExcelUtil.java

功能描述：下载excel模板时在指定的sheet页初始化相关数据，并下载excel至本地

 

    @Override
    public Object downloadExcelTemplate(HttpServletResponse response) {
        try{
            //表头
            String title= "表头名称" ;
            //列名
            String[] headers  = {"序号", "编码", "名称"};
　　　　　　　//下载的文件名
            String fileName = "示例信息模板";
            //创建工作簿对象，获取项目中指定目录下的excel模板
            org.springframework.core.io.Resource resource = new DefaultResourceLoader().getResource("exceltemplate"+ File.separator+"ExcelTemplate.xlsx");
            byte[] buffer = new byte[(int) resource.contentLength()];
            //将excel模板初始化成workbook类
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(resource.getInputStream()) ;

            //获取要初始化的sheet页
            XSSFSheet xssfSheet = xssfWorkbook.getSheet("Sheet初始化属性");
            //设置表头
            ExcelUtil.creatExcelTitle(xssfWorkbook,xssfSheet,title,headers.length -1);
            //设置标题
            ExcelUtil.creatExcelHeader(xssfWorkbook,xssfSheet,headers);

            TData tData;
            QueryWrapper<TData> queryWrapper = new QueryWrapper<TData>();
            queryWrapper.eq("flag", 0);//未删除
            queryWrapper.eq("status",1);//启用状态
            List<TData> datas = dataMapper.selectList(queryWrapper);
            for(int i=0; i<datas.size(); i++){
                //创建行（内容行）
                XSSFRow xssfRowContent = xssfSheet.createRow(i + 2) ;
                tData = datas.get(i);
                //设置数据格式
                XSSFDataFormat xssfDataFormat = xssfWorkbook.createDataFormat();
                //序号
                XSSFCell xssfCellContent0 = xssfRowContent.createCell(0) ;
                xssfCellContent0.setCellStyle(ExcelUtil.contentCellStyle(xssfWorkbook,false));
                xssfCellContent0.setCellValue(i+1);
                //编码
                XSSFCell xssfCellContent1 = xssfRowContent.createCell(1) ;
                xssfCellContent1.setCellStyle(ExcelUtil.contentCellStyle(xssfWorkbook,false)) ;
                xssfCellContent1.setCellValue(tSpaceHospital.getId());
                //名称
                XSSFCell xssfCellContent2 = xssfRowContent.createCell(2) ;
                xssfCellContent2.setCellStyle(ExcelUtil.contentCellStyle(xssfWorkbook,false));
                xssfCellContent2.setCellValue(tSpaceHospital.getName());
            }
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            // 通知浏览器以附件的形式下载处理，设置返回头要注意文件名有中文
            response.setHeader("Content-disposition", "attachment;filename=" + java.net.URLEncoder.encode(fileName, "UTF8") + ".xlsx");
            response.setHeader("Content-Length", "" + buffer.length);
            response.setContentType("application/octet-stream");
            response.setCharacterEncoding("utf-8");
            //写入excel
            ExcelUtil.writeExcel(response,xssfWorkbook,os,URLEncoder.encode(fileName, "UTF-8")+".xlsx");
            response.flushBuffer();
        }catch (Exception e){
            e.printStackTrace();
        }
        return object;
    }