导出excel

1、引入layui.js

<script src="${base.contextPath}/lib/layui/layui.js"></script>

2.1、使用layer

//region 使用layer
var layer;
layui.use('layer', function(){
    layer = layui.layer;
    //layer.msg('标签选择不能为空', {icon: 3,time:1000});
});
//endregion

2.2 前台js

//region  生成excel
function excprtExcel() {
    var data = $('#grid').data("kendoGrid").dataSource.data();
    data[0].customerName = customerName;

    //region校验非空&&
    for (var i = 0; i < data.length;i++){
        var surveyManner = data[i].surveyManner;
        if (!ifNotNull(surveyManner)){
            layer.msg('第'+(i+1)+'行调研方式不能为空', {icon: 2,time:1000});
            return;
        }
    }
    //endregion

    var index = layer.load(2, {time: 1000*1000}); //又换了种风格，并且设定最长等待1000秒

    //region请求生成文件并导出
    $.ajax({
        type:"POST",
        contentType:"application/json;charset=UTF-8",
        //contentType用于整个对象的传参，控制层以@RequestBody Dto dto获取
        url:"${base.contextPath}/hawk/survey/search/gener/generExcel",
        data:JSON.stringify(data),
        dataType:'json',
        async:false,
        success:function (result) {
            if(result.success){
                //进行下载
                var file = result.message

                var $inputToken=$('<input>').attr({name:"${_csrf.parameterName}",value:"${_csrf.token}",type:"hidden"});
                var file =$('<input>').attr({name:"file",value:file,type:"hidden"});
                var $form = $("<form>");
                $form.attr({
                    target: 'id_iframe',//注意这个对应的是下面的div   id=iframe的那个框
                    method: 'post',
                    action: '${base.contextPath}/hawk/survey/search/gener/download'
                });
                $form.append($inputToken);
                $form.append(file);
                $form.id='smbForm';
                $("#batchDiv").empty().append($form);//这个对应的是div    batchDiv
                $($form).submit();
                $("#batchDiv").empty();
                layer.close(index);
            }else{
                layer.close(index);
                return kendo.ui.showErrorDialog({message:'ERROR:'+result.message});

            }
        }
    });
    //endregion
}
//endregion

3、在服务器中生成文件

3.1controller

@RequestMapping(value = "/hawk/survey/search/gener/generExcel")
@ResponseBody
public ResponseData generExcel(HttpServletRequest request, @RequestBody List<SurveySearchImoprt> dto) {

    /**
     *
     * 功能描述: 在服务器生成excel
     *
     * @param: [request, dto]
     * @return: com.hand.hap.system.dto.ResponseData
     * @auther: leizhe
     * @date: 2019/4/25 10:46
     */


    ResponseData responseData = new ResponseData();
    try {
        String excelName = service.generExcel(request, dto);
        responseData.setSuccess(true);
        responseData.setMessage(excelName);
    } catch (Exception e) {
        e.printStackTrace();
        responseData.setSuccess(false);
        responseData.setMessage(e.getMessage());
    }
    return responseData;
}

3.2impl

public String generExcel(HttpServletRequest request, List<SurveySearchImoprt> dto) {

        /**
         *
         * 功能描述: 在服务器中生成excel
         *
         * @param: [request, dto]
         * @return: java.lang.String
         * @auther: leizhe
         * @date: 2019/4/25 10:48
         */

        //获取项目名称
        String customerName = dto.get(0).getCustomerName();


        //调研方式
        List<SurveySearchImoprt> Tarzan_Communicate_Type = surveySearchImoprtMapper.qeuryFastCode("Tarzan_Communicate_Type");
        //调研模块
        List<SurveySearchImoprt> Tarzan_Survey_Module = surveySearchImoprtMapper.qeuryFastCode("Tarzan_Survey_Module");



        //创建excel
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFCellStyle style = wb.createCellStyle();
        //设置单元格居中
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);//向左对齐
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        style.setWrapText(true);//自动换行

        //定义页面名称
        XSSFSheet sheet1 = wb.createSheet("导出模板");

        //设定宽度
        sheet1.setColumnWidth(1, 18 * 256);
        sheet1.setColumnWidth(2, 14 * 256);
        sheet1.setColumnWidth(7, 30 * 256);
        sheet1.setColumnWidth(8, 40 * 256);


        //region创建第一行

        //第一行的样式
        XSSFCellStyle style1 = createStyle1(wb, 20);
        XSSFRow RowOne =sheet1.createRow(0);
        XSSFCell oneCel = RowOne.createCell(0);
        oneCel.setCellValue(customerName + "调研计划");
        oneCel.setCellStyle(style1);
        CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 1, 0, 9);
        sheet1.addMergedRegion(new CellRangeAddress(0, 1, 0, 9));//从第一行开始，到第15+1行结束，从第一列开始，到底2+1列

        //endregion


//        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  //填充单元格
//        style.setFillForegroundColor(HSSFColor.YELLOW.index);    //填黄色
        //region 创建第二行
        //region 创建第二行的style
        XSSFCellStyle styleLine2 = wb.createCellStyle();
        //设置单元格居中
        styleLine2.setAlignment(HSSFCellStyle.ALIGN_LEFT);//向左对齐
        styleLine2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中
        styleLine2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        styleLine2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        styleLine2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        styleLine2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        styleLine2.setWrapText(true);//自动换行
        styleLine2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  //填充单元格
        styleLine2.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);    //填黄色
        //endregion

        //创建第二行
        XSSFRow firstRow =sheet1.createRow(2);
        //调研方式，占一列
        XSSFCell cell = firstRow.createCell(0);
        cell.setCellValue("调研方式");
        cell.setCellStyle(styleLine2);
        //sheet1.addMergedRegion(new CellRangeAddress(rowEnd+1, rowEnd+1, 0, 0));
        //日期
        XSSFCell cell1 = firstRow.createCell(1);
        cell1.setCellValue("日期");
        cell1.setCellStyle(styleLine2);

        //预计时长
        XSSFCell cell2 = firstRow.createCell(2);//
        cell2.setCellValue("预计时长(min)");
        cell2.setCellStyle(styleLine2);

        //调研部门
        XSSFCell cell3 = firstRow.createCell(3);
        cell3.setCellValue("调研部门");
        cell3.setCellStyle(styleLine2);

        //模块
        XSSFCell cell4 = firstRow.createCell(4);
        cell4.setCellValue("模块");
        cell4.setCellStyle(styleLine2);

        //调研编号
        XSSFCell cell5 = firstRow.createCell(5);
        cell5.setCellValue("调研编号");
        cell5.setCellStyle(styleLine2);

        //调研对象
        XSSFCell cell6 = firstRow.createCell(6);
        cell6.setCellValue("调研对象");
        cell6.setCellStyle(styleLine2);

        //调研内容
        XSSFCell cell7 = firstRow.createCell(7);
        cell7.setCellValue("调研内容");
        cell7.setCellStyle(styleLine2);
        //sheet1.addMergedRegion(new CellRangeAddress(2, 2, 7, 10));

        //回复纪要
        XSSFCell cell8 = firstRow.createCell(8);
        cell8.setCellValue("回复纪要");
        cell8.setCellStyle(styleLine2);
        //sheet1.addMergedRegion(new CellRangeAddress(2, 2, 11, 14));

        //备注
        XSSFCell cell9 = firstRow.createCell(9);
        cell9.setCellValue("备注");
        cell9.setCellStyle(styleLine2);
        //endregion

        int rowIndex = 3;
        //region 循环创建数据
        for (int i = 0; i < dto.size(); i++) {
            SurveySearchImoprt surveySearchImoprt = dto.get(i);

            if (surveySearchImoprt.getEnableFlag().equals("Y")){
                //调研方式
                String surveyManner = surveySearchImoprt.getSurveyManner();
                //日期
                Date surveyTime = surveySearchImoprt.getSurveyTime();
                String surveyTimeStr = "";
                if (surveyTime != null) {
                    surveyTimeStr = formatTime(surveyTime, "yyyy-MM-dd HH:mm:ss");
                }
                //预计时长
                String continueDuration = surveySearchImoprt.getContinueDuration();
                //调研部门 null
                //模块
                String surveyModule = surveySearchImoprt.getSurveyModule();

                //调研对象
                String surveyObject = surveySearchImoprt.getSurveyObject();

                //回复纪要  null
                //备注  null

                SurveySearch surveySearch = new SurveySearch();
                surveySearch.setCurrentModule(surveyModule);
                surveySearch.setEnableFlag("Y");
                surveySearch.setApplicableScenario("10");
                //查询当前模块下对应的所有编号的信息
                List<SurveySearch>  surveySearches = surveySearchMapper.mySelect(surveySearch);
                //region查找 applicableScenario 字段存在 “10” 的对象
                /*ArrayList<SurveySearch> surveySearches = new ArrayList<>();
                for (int j = 0; j < surveySearches1.size(); j++) {
                    SurveySearch search = surveySearches1.get(j);
                    String applicableScenario = search.getApplicableScenario();
                    if (getApplicableScenario(applicableScenario,"10")){
                        surveySearches.add(search);
                    }
                }*/
                //endregion

                if (surveySearches.size()>0){
                    XSSFRow row = sheet1.createRow(rowIndex);

                    //调研方式
                    XSSFCell cellLine = row.createCell(0);
                    String meaning = Tarzan_Communicate_Type.stream().filter(t -> t.getValue().equals(surveyManner)).findFirst().get().getMeaning();
                    cellLine.setCellValue(meaning);
                    cellLine.setCellStyle(style);
                    sheet1.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex + surveySearches.size()-1, 0, 0));

                    //日期
                    XSSFCell cellLine1 = row.createCell(1);
                    cellLine1.setCellValue(surveyTimeStr);
                    cellLine1.setCellStyle(style);
                    sheet1.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex + surveySearches.size()-1, 1, 1));
                    //预计时长
                    XSSFCell cellLine2 = row.createCell(2);
                    cellLine2.setCellValue(continueDuration);
                    cellLine2.setCellStyle(style);
                    sheet1.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex + surveySearches.size()-1, 2, 2));
                    //调研部门
                    XSSFCell cellLine3 = row.createCell(3);
                    cellLine3.setCellValue(surveyObject);
                    cellLine3.setCellStyle(style);
                    sheet1.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex + surveySearches.size()-1, 3, 3));
                    //模块 surveyModule
                    XSSFCell cellLine4 = row.createCell(4);
                    String surveyModuleMeaning = Tarzan_Survey_Module.stream().filter(t -> t.getValue().equals(surveyModule)).findFirst().get().getMeaning();
                    cellLine4.setCellValue(surveyModuleMeaning);
                    cellLine4.setCellStyle(style);
                    sheet1.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex + surveySearches.size()-1, 4, 4));



                    for (int j = 0; j < surveySearches.size(); j++) {
                        SurveySearch search = surveySearches.get(j);
                        //调研编号
                        String surveyIdentifier = search.getSurveyIdentifier();
                        //调研内容
                        String researchContents = search.getResearchContents();

                        if (j == 0){//如果是第一次，直接只用上面生成的那一行
                            //调研编号
                            XSSFCell cellLine5 = row.createCell(5);
                            cellLine5.setCellValue(surveyIdentifier);
                            cellLine5.setCellStyle(style);
                            //调研对象
                            XSSFCell cellLine6 = row.createCell(6);
                            cellLine6.setCellValue("");
                            cellLine6.setCellStyle(style);
                            //调研内容
                            XSSFCell cellLine7 = row.createCell(7);
                            cellLine7.setCellValue(researchContents);
                            cellLine7.setCellStyle(style);
                            //回复纪要
                            XSSFCell cell11 = row.createCell(8);
                            cell11.setCellValue("");
                            cell11.setCellStyle(style);
                            //备注
                            XSSFCell cellLine9 = row.createCell(9);
                            cellLine9.setCellValue("");
                            cellLine9.setCellStyle(style);
                        }else {
                            XSSFRow rowNotCurrentLine = sheet1.createRow(rowIndex);

                            //region 前五个字段，这里写只是为了边框显示没问题
                            XSSFCell cellLineLine = rowNotCurrentLine.createCell(0);
                            cellLineLine.setCellValue("");
                            cellLineLine.setCellStyle(style);

                            XSSFCell cellLineLine1 = rowNotCurrentLine.createCell(1);
                            cellLineLine1.setCellValue("");
                            cellLineLine1.setCellStyle(style);

                            XSSFCell cellLineLine2 = rowNotCurrentLine.createCell(2);
                            cellLineLine2.setCellValue("");
                            cellLineLine2.setCellStyle(style);

                            XSSFCell cellLineLine3 = rowNotCurrentLine.createCell(3);
                            cellLineLine3.setCellValue("");
                            cellLineLine3.setCellStyle(style);

                            XSSFCell cellLineLine4 = rowNotCurrentLine.createCell(4);
                            cellLineLine4.setCellValue("");
                            cellLineLine4.setCellStyle(style);
                            //endregion

                            //调研编号
                            XSSFCell cellLine5 = rowNotCurrentLine.createCell(5);
                            cellLine5.setCellValue(surveyIdentifier);
                            cellLine5.setCellStyle(style);
                            //调研对象
                            XSSFCell cellLine6 = rowNotCurrentLine.createCell(6);
                            cellLine6.setCellValue("");
                            cellLine6.setCellStyle(style);
                            //调研内容
                            XSSFCell cellLine7 = rowNotCurrentLine.createCell(7);
                            cellLine7.setCellValue(researchContents);
                            cellLine7.setCellStyle(style);
                            //回复纪要
                            XSSFCell cell11 = rowNotCurrentLine.createCell(8);
                            cell11.setCellValue("");
                            cell11.setCellStyle(style);
                            //备注
                            XSSFCell cellLine9 = rowNotCurrentLine.createCell(9);
                            cellLine9.setCellValue("");
                            cellLine9.setCellStyle(style);
                        }
                        rowIndex++;
                    }
                }
            }


        }
        //endregion


        //冻结
        sheet1.createFreezePane( 0, 3, 0, 3 );

        //region 在指定位置生成excel
        String path = "";
        try {
            path = SurveySearchImoprtServiceImpl.class.getResource("/").toURI().getPath() + "temp/";
            System.out.println(path);
        } catch (URISyntaxException e) {
            e.printStackTrace();
        }
        String fileNamePath = path+"jjzz"+ UUID.randomUUID()+".xlsx";
        try {
            File file = new File(path);
            if (!file.exists()){
                file.mkdir();
            }

            FileOutputStream fileOutputStream = new FileOutputStream(fileNamePath);
            wb.write(fileOutputStream);
            fileOutputStream.close();
            wb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        //endregion


        /*第一个参数表示要冻结的列数；
            第二个参数表示要冻结的行数，这里只冻结列所以为0；
            第三个参数表示右边区域可见的首列序号，从1开始计算；
            第四个参数表示下边区域可见的首行序号，也是从1开始计算，这里是冻结列，所以为0；*/
        return fileNamePath;
    }

 

4下载

4.1controller

@RequestMapping(value = "/hawk/survey/search/gener/download")
@ResponseBody
public void download(HttpServletRequest request, String file, HttpServletResponse response) {
    /**
     *
     * 功能描述:  下载
     *
     * @param: [request, file, response]
     * @return: void
     * @auther: leizhe
     * @date: 2019/4/25 18:00
     */
    service.download(file,request,response);

}

4.2impl

 

@Override
    public void download(String file, HttpServletRequest request, HttpServletResponse response) {
        /**
         *
         * 功能描述:下载excel并删除服务器中的文件
         *
         * @param: [file, request, response]
         * @return: void
         * @auther: leizhe
         * @date: 2019/4/25 18:54
         */
        SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMddHHmmss");

        String fileName = "提纲";
        try {
//            fileName = URLEncoder.encode("精益制造", "UTF-8");
            fileName = new String(fileName.getBytes("utf-8"),"ISO-8859-1");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }

        response.reset();
        response.setContentType("application/octet-stream" + ";charsets=" + "UTF-8");
        response.addHeader("Content-Disposition", "attachment; filename=\"" + "" + fileName +sdf.format(new Date())+ ".xlsx" + "\"");

        BufferedInputStream inputStream = null;
        BufferedOutputStream out = null;
        try {
            inputStream = new BufferedInputStream(new FileInputStream(file));
            out = new BufferedOutputStream(response.getOutputStream());
            IOUtils.copy(inputStream, out);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(inputStream);
            IOUtils.closeQuietly(out);
            File file1 = new File(file);
            if (file1.exists()) {
                file1.delete();
            }
        }
    }