Excel导出

已于 2024-04-01 14:48:49 修改
阅读量404 收藏 8
点赞数 7
分类专栏： 蓝凌OA系统ssh框架 文章标签： ssh excel 运维
于 2024-02-19 09:45:44 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_66737223/article/details/136162644
版权

目录

一、前端jsp

 二、后端

2.1、ProExcelDownloadAction

2.2、IProExcelDownloadService 

2.3、ProExcelDownloadServiceImp 

三、拓展 

3.1、前端jsp

 3.2、后端

一、前端jsp

<div style="display: inline-block; float:right;vertical-align: middle;">
     <ui:toolbar id="toolbarOperate" count="3">
         <c:import url="/pro/word/common/common_downloadExcel.jsp">
             <c:param name="type" value="zzygxx" />
          </c:import>
     </ui:toolbar>
</div>

 注：具体的前端界面代码不一样，可以参考pro/word/common/common_downloadExcel.jsp路径下的三个导出文件和hr/staff/hr_staff_person_info/index.jsp界面

或者采用以下的代码风格(即hr/staff/hr_staff_person_info/index.jsp界面)

<ui:button order="0" text="导出" onclick="downloadExcel()"></ui:button>
<script>
    seajs.use(['lui/jquery', 'lui/dialog'], function ($, dialog) {
        window.downloadExcel = function () {
            var values = null;
            $("input[name='List_Selected']:checked").each(function () {
                values = values + "," + $(this).val();
            });
            if (values == null) {
                dialog.alert('<bean:message key="page.noSelect"/>');
                return;
            } else {
                values = values.substring(values.indexOf(",") + 1);
            }
            var url = "/pro/word/proExcelDownload.do?method=downloadExcel&type=zzygxx";
            submitForm('${LUI_ContextPath}' + url, "post", "json", encodeURI(values));
        }
    });
    window.submitForm = function (url, name, key, values) {
        var newWindow = window.parent.open(name);
        if (!newWindow)
            return false;
        var html = "";
        html += "<html><head></head><body><form id='formid' method='post' action='" + url + "'>";
        if (values) {
            html += "<input type='hidden' name='values' value='" + values + "'/>";
        }
        html += "</form><script type='text/javascript'>document.getElementById('formid').submit();";
        html += "<\/script></body></html>".toString().replace(/^.+?\*|\\(?=\/)|\*.+?$/gi, "");
        newWindow.document.write(html);
        return newWindow;
    };
</script>

 二、后端

2.1、ProExcelDownloadAction

else if ("zzygxx".equals(type)) {
  // 导出的数据
  getProExcelDownloadServiceImp().exportZzygxxTable(request, response);
}

注： src/com/landray/kmss/pro/word/actions/ProExcelDownloadAction.java路径下

2.2、IProExcelDownloadService 

void exportZzygxxTable(HttpServletRequest request, HttpServletResponse response) throws Exception;

2.3、ProExcelDownloadServiceImp 

@Override
public void exportZzygxxTable(HttpServletRequest request, HttpServletResponse response) throws Exception {
    ExcelWriter writer = ExcelUtil.getWriter();
    writer.renameSheet(0, "在职员工信息");
    // 跳过前两行
    writer.passRows(2);
    //全局
    CellStyle cellStyle = writer.getCellStyle();
    //创建标题字体
    Font font = writer.createFont();
    font.setFontHeightInPoints((short) 11);
    font.setFontName("宋体");
    cellStyle.setFont(font);
    //全局  宽15
    writer.setColumnWidth(-1, 30);
    //全局  高25
    writer.setDefaultRowHeight(15);
    cellStyle.setWrapText(true);
    //标题样式
    CellStyle headCellStyle = writer.getHeadCellStyle();
    headCellStyle.setAlignment(HorizontalAlignment.CENTER);
    Font headFont = writer.createFont();
    headFont.setFontName("宋体");
    headFont.setFontHeightInPoints((short) 12);
    headFont.setBold(true);
    headCellStyle.setFont(headFont);
    //第一行单独标题样式
    CellStyle oneHeadCellStyle = writer.createCellStyle();
    // 设置边框
    oneHeadCellStyle.setBorderBottom(BorderStyle.THIN);
    oneHeadCellStyle.setBorderTop(BorderStyle.THIN);
    oneHeadCellStyle.setBorderLeft(BorderStyle.THIN);
    oneHeadCellStyle.setBorderRight(BorderStyle.THIN);
    oneHeadCellStyle.setAlignment(HorizontalAlignment.CENTER);
    oneHeadCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    Font oneHeadFont = writer.createFont();
    oneHeadFont.setFontName("宋体");
    oneHeadFont.setFontHeightInPoints((short) 15);
    oneHeadFont.setBold(true);
    oneHeadCellStyle.setFont(oneHeadFont);
    //第二行单独标题样式
    CellStyle twoHeadCellStyle = writer.createCellStyle();
    // 设置边框
    twoHeadCellStyle.setBorderBottom(BorderStyle.THIN);
    twoHeadCellStyle.setBorderTop(BorderStyle.THIN);
    twoHeadCellStyle.setBorderLeft(BorderStyle.THIN);
    twoHeadCellStyle.setBorderRight(BorderStyle.THIN);
    twoHeadCellStyle.setAlignment(HorizontalAlignment.LEFT);
    twoHeadCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    Font twoHeadFont = writer.createFont();
    twoHeadFont.setFontName("宋体");
    twoHeadFont.setFontHeightInPoints((short) 15);
    twoHeadFont.setBold(true);
    twoHeadCellStyle.setFont(twoHeadFont);
    int lastColumn = 32;
    // 单独设置样式，数据写入后再设置
    writer.setRowHeight(0, 28);
    writer.setRowHeight(1, 20);
    writer.setColumnWidth(0, 10);
    writer.merge(0, 1, 0, lastColumn, "在职员工信息", oneHeadCellStyle);
    //一次性写出内容，强制输出标题
    writer.write(getZzygxxArrayList(request, response), true);
    //导出的为xls
    response.setContentType("application/vnd.ms-excel;charset=utf-8");
    String fileName = new String("在职员工信息.xls".getBytes("GBK"), "iso8859-1");
    response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
    ServletOutputStream out = response.getOutputStream();
    writer.flush(out, true);
    writer.close();
    IoUtil.close(out);
}

注：在此段代码中设置导出Excel文件的样式等等   

writer.write(getZzygxxArrayList(request, response), true)；需要单独编写对应的是导出的内容

 int lastColumn = 32；表示标题横跨的单元格个数

private ArrayList<Map<String, Object>> getZzygxxArrayList(HttpServletRequest request, HttpServletResponse response) throws Exception {
    IHrStaffPersonInfoService hrStaffPersonInfoService = (IHrStaffPersonInfoService) SpringBeanUtil.getBean("hrStaffPersonInfoService");
    ArrayList<Map<String, Object>> rows = new ArrayList<>();
    String valueStr = request.getParameter("values");
    String[] values = valueStr.split(",");
    List<HrStaffPersonInfo> list = hrStaffPersonInfoService.findByPrimaryKeys(values);
    for (int i = 0; i < list.size(); i++) {
        HrStaffPersonInfo hrStaffPersonInfo = list.get(i);
        Map<String, Object> row = new LinkedHashMap<>();
        row.put("序号", String.valueOf(i + 1));// 序号
        row.put("姓名", hrStaffPersonInfo.getFdName());
        row.put("手机", hrStaffPersonInfo.getFdMobileNo());
        row.put("所在部门(全路径)", hrStaffPersonInfo.getFdOrgParentsName());
        row.put("职务", hrStaffPersonInfo.getFdStaffingLevel() == null ? "" : hrStaffPersonInfo.getFdStaffingLevel().getFdName());
        //任职资格
        String fdQualification = hrStaffPersonInfo.getFdQualification();
        if (StringUtils.isNotEmpty(fdQualification)) {
            String rzzg = "";
            if (fdQualification.contains("pzr")) {
                rzzg += "批准人,";
            }
            if (fdQualification.contains("hdr")) {
                rzzg += "核定人,";
            }
            if (fdQualification.contains("sjzfzr")) {
                rzzg += "设计总负责人,";
            }
            if (fdQualification.contains("scr")) {
                rzzg += "审查人,";
            }
            if (fdQualification.contains("xmfzr")) {
                rzzg += "项目负责人,";
            }
            if (fdQualification.contains("zyfzr")) {
                rzzg += "专业负责人,";
            }
            if (fdQualification.contains("jhr")) {
                rzzg += "校核人,";
            }
            if (fdQualification.contains("jcy")) {
                rzzg += "检查员,";
            }
            if (fdQualification.contains("sjr")) {
                rzzg += "设计人,";
            }
            if (fdQualification.contains("kcr")) {
                rzzg += "勘测人,";
            }
            if (rzzg.endsWith(",")) {
                rzzg = rzzg.substring(0, rzzg.length() - 1);
            }
            row.put("任职资格", rzzg);
        } else {
            row.put("任职资格", "");
        }
        rows.add(row);
    }
    return rows;
}

注：在此段代码中编写导出的列和具体的列的取值

三、拓展 

上面的导出是针对于可以获取每一行对应的 id(即每一行前面有对应的框可以勾选) ，获取不到的可以采用以下的方式实现根据查询条件导出

3.1、前端jsp

<div style="display: inline-block; float:right;vertical-align: middle;">
    <ui:toolbar id="toolbarOperate" count="3">
        <c:import url="/pro/word/common/common_downloadExcel.jsp">
            <c:param name="type" value="lybb"/>
        </c:import>
    </ui:toolbar>
</div>

在对应的列表写一个按钮指定对应的导出方法和导出页面 

if (${param.type=="lybb"}) {
    var params = "";
    params += '&&fdDeptId=' + $("[name=fdDeptId]").val();
    params += '&&fdNumberNo=' + $("[name=fdNumberNo]").val();
    params += '&&docCreatorId=' + $("[name=docCreatorId]").val();
    params += '&&fdName=' + $("[name=fdName]").val();
    submitForm('${LUI_ContextPath}' + '/pro/word/proExcelDownload.do?method=downloadExcel&type=${param.type}' + params, "post", "json", encodeURI(values));
    return;
}

通过 param 携带查询条件的参数，传给对应的后端方法

 3.2、后端

 此处参考的是办公用品管理模块的领用报表统计，仅说明与普通报表不同的地方

private ArrayList<Map<String, Object>> getlybbList(HttpServletRequest request, HttpServletResponse response) throws Exception {
    ArrayList<Map<String, Object>> rows = new ArrayList<>();
    ISysOrgElementService sysOrgElementService = (ISysOrgElementService) SpringBeanUtil.getBean("sysOrgElementService");
    List list = listDetail(request, response);
    for (int i = 0; i < list.size(); i++) {
        Object[] value = (Object[]) list.get(i);
        Map<String, Object> row = new LinkedHashMap<>();
        row.put("序号", String.valueOf(i + 1));
        //领用部门需要二级显示
        String ryId = value[9].toString();
        SysOrgElement sysOrgElement = (SysOrgElement) sysOrgElementService.findByPrimaryKey(ryId);
        SysOrgElement fdParent = sysOrgElement.getFdParent();
        String depart = fdParent.getFdName() + "_" + sysOrgElement.getFdName();
        row.put("领用部门", depart);
        row.put("领用者", value[1]);
        row.put("物品编码", value[2]);
        row.put("物资类别", value[3]);
        row.put("物资名称", value[4]);
        row.put("规格", value[5]);
        row.put("计量单位", value[6]);
        row.put("数量", value[7]);
        rows.add(row);
    }
    return rows;
}
public List listDetail(HttpServletRequest request,
                           HttpServletResponse response) throws Exception {


        String selectSql = " SELECT bm,llz,wzbm,sslb,wzmc,ggxh,jldw,sl,lysj,bmId,llzId" +
                " FROM" +
                " (SELECT element.fd_name AS bm,element.fd_id AS bmId,element2.fd_name AS llz,element2.fd_id AS llzId,details.fd_no AS wzbm,cg.fd_name AS sslb,details.fd_name AS wzmc,details.fd_specification AS ggxh,material.fd_measurement AS jldw,details.fd_applicationnumber AS sl,souvenirs.doc_create_time AS lysj" +
                " FROM `cost_apply_souvenirs_details` as details" +
                " LEFT JOIN cost_apply_souvenirs AS souvenirs" +
                " ON details.doc_main_id=souvenirs.fd_id" +
                " LEFT JOIN sys_org_element  as element" +
                " ON souvenirs.doc_dept_id=element.fd_id" +
                " LEFT JOIN sys_org_element as element1" +
                " ON souvenirs.fd_company_id=element1.fd_id" +
                " LEFT JOIN sys_org_element as element2" +
                " ON souvenirs.doc_creator_id =element2.fd_id" +
                " LEFT JOIN km_oitems_total_inventory as inventory" +
                " ON details.fd_total_inventory_ids=inventory.fd_id" +
                " LEFT JOIN mm_managt_material as material" +
                " ON inventory.mm_managt_material_id=material.fd_id" +
                " LEFT JOIN mm_managt_mtl_cg  as cg" +
                " ON material.doc_template_id=cg.fd_id" +
                " WHERE souvenirs.doc_status='30'" +
                " union all" +
                " SELECT ele2.fd_name AS 领用部门,ele2.fd_id AS 领用部门Id,ele1.fd_name AS 领用者,ele1.fd_id AS 领用者Id,trolley.fd_no AS 物资编码,c.fd_name AS 所属类别,trolley.fd_name AS 物资名称,trolley.fd_specification AS 规格型号,mater.fd_measurement AS 计量单位,trolley.fd_application_number AS 数量,application.doc_create_time AS 领用时间" +
                " FROM km_oitems_shopping_trolley  AS trolley" +
                " LEFT JOIN km_oitems_budger_application AS application" +
                " ON trolley.fd_application_id=application.fd_id" +
                " LEFT JOIN sys_org_element  as ele1" +
                " ON application.doc_creator_id=ele1.fd_id" +
                " LEFT JOIN sys_org_element  as ele2" +
                " ON application.doc_dept_id=ele2.fd_id" +
                " LEFT JOIN km_oitems_total_inventory AS invent" +
                " ON trolley.fd_total_inventory_ids=invent.fd_id" +
                " LEFT JOIN  mm_managt_material as mater" +
                " ON invent.mm_managt_material_id=mater.fd_id" +
                " LEFT JOIN mm_managt_mtl_cg  as c" +
                " ON mater.doc_template_id=c.fd_id" +
                " WHERE application.doc_status='30') AS ly ";


        //拼where语句
        String whereSql = " 1=1 ";


        String fdDeptId = request.getParameter("fdDeptId");
        if (StringUtil.isNotNull(fdDeptId)) {
            whereSql += " and  bmId = :fdDeptId ";
        }

        String fdNumberNo = request.getParameter("fdNumberNo");
        if (StringUtil.isNotNull(fdNumberNo)) {
            whereSql += " and  wzbm like :fdNumberNo ";
        }

        String docCreatorId = request.getParameter("docCreatorId");
        if (StringUtil.isNotNull(docCreatorId)) {
            whereSql += " and  llzId = :docCreatorId ";
        }

        String fdName = request.getParameter("fdName");
        if (StringUtil.isNotNull(fdName)) {
            whereSql += " and  wzmc like :fdName ";
        }


        if (StringUtil.isNotNull(whereSql)) {
            selectSql += " where " + whereSql;
        }

        IKmOitemsBudgerApplicationService kmOitemsBudgerApplicationService = (IKmOitemsBudgerApplicationService) SpringBeanUtil.getBean("kmOitemsBudgerApplicationService");

        //nativeQuery1只为查询总数据条数 start
        NativeQuery nativeQuery1 = kmOitemsBudgerApplicationService.getBaseDao().getHibernateSession().createNativeQuery(selectSql);

        if (StringUtil.isNotNull(fdDeptId)) {
            nativeQuery1.setParameter("fdDeptId", fdDeptId);
        }

        if (StringUtil.isNotNull(fdNumberNo)) {
            nativeQuery1.setParameter("fdNumberNo", "%" + fdNumberNo + "%");
        }

        if (StringUtil.isNotNull(docCreatorId)) {
            nativeQuery1.setParameter("docCreatorId", docCreatorId);
        }

        if (StringUtil.isNotNull(fdName)) {
            nativeQuery1.setParameter("fdName", "%" + fdName + "%");
        }

        List<Object[]> list1 = nativeQuery1.list();
        return list1;
    }

通过 String fdName = request.getParameter("fdName"); 方法获取前端查询条件传过来的参数

15岁自学Java
关注
专栏目录
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值