java jxl实现导出数据下载excel

最新推荐文章于 2024-07-20 23:59:39 发布

萌新比克o

最新推荐文章于 2024-07-20 23:59:39 发布

阅读量737

点赞数

文章标签： jxl java 导出成excel

本文链接：https://blog.csdn.net/weixin_42443924/article/details/86540263

版权

1.创建下载jsp界面

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ page language="java" import="java.util.*" pageEncoding="gb2312"%>
<%@ page import="java.io.*" %>
<%@ page import="client.ClientBizImpl" %>
<%
    //下载时的文件名
    String fname = "科队领导考评表和执勤质量考评情况";
    OutputStream os = response.getOutputStream();//取得输出流
    response.reset();//清空输出流
    String deptId=request.getParameter("deptId");
    String time=request.getParameter("time");
    //下面是对中文文件名的处理
    response.setCharacterEncoding("UTF-8");//设置相应内容的编码格式
    fname = java.net.URLEncoder.encode(fname,"UTF-8");
    response.setHeader("Content-Disposition","attachment;filename="+new String(fname.getBytes("UTF-8"),"GBK")+".xls");
    response.setContentType("application/msexcel");//定义输出类型
    ClientBizImpl clientBiz=new ClientBizImpl();
    clientBiz.exportExcel(deptId,time,os);

%>
<html>
<head>

    <title></title>

</head>

<body>
</body>
</html>

2.excel导出代码

/**
 * 导出数据
 * @param deptId
 * @param time
 * @param os
 * @throws WriteException
 * @throws IOException
 */
public void exportExcel(String deptId,String time,OutputStream os)throws WriteException,IOException{
    //创建工作薄
    WritableWorkbook workbook = Workbook.createWorkbook(os);
    //创建新的一页
    WritableSheet sheet = workbook.createSheet("First Sheet", 0);
    //标题
    String head = selectGroup(deptId)+time+"月份科队领导得分考评表";
    String title= selectGroup(deptId)+time+"月份执勤质量考评情况";
    //标题样式
    WritableFont headFont = new WritableFont(WritableFont.ARIAL, 15,WritableFont.BOLD); //表头标题样式
    WritableCellFormat wcf_head = new WritableCellFormat(headFont);
    wcf_head.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); // 文字垂直对齐
    wcf_head.setAlignment(Alignment.CENTRE); // 文字水平对齐
    wcf_head.setBorder(Border.ALL,BorderLineStyle.THIN,Colour.BLACK);//设置表头边框线
    sheet.addCell(new Label(0, 0, head, wcf_head));
    //设置表头高度
    sheet.setRowView(0, 600);
    //列样式设置
    WritableFont cloumFont = new WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.WHITE); //列名
    WritableCellFormat wcf_cloum = new WritableCellFormat(cloumFont);
    wcf_cloum.setBackground(Colour.OCEAN_BLUE);//设置背景颜色
    wcf_cloum.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); // 文字垂直对齐
    wcf_cloum.setAlignment(Alignment.CENTRE); // 文字水平对齐
    wcf_cloum.setBorder(Border.ALL,BorderLineStyle.THIN,Colour.BLACK);//设置边框线
    //正文样式
    WritableFont textFont = new WritableFont(WritableFont.ARIAL, 10); //正文
    WritableCellFormat wcf_text = new WritableCellFormat(textFont);
    wcf_text.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); // 正文垂直对齐
    wcf_text.setAlignment(Alignment.CENTRE); // 正文水平对齐
    wcf_text.setBorder(Border.ALL,BorderLineStyle.THIN,Colour.BLACK);//设置边框线
    //导出领导每月考评表
    int rowFlag=this.kldExcel(sheet,deptId,time,wcf_text,wcf_cloum);
    sheet.addCell(new Label(0, rowFlag, title, wcf_head));
    //设置标题高度
    sheet.setRowView(rowFlag,600);
    sheet.mergeCells(0,rowFlag,7,rowFlag);
    //导出检查员
    this.jcyExcel(sheet,deptId,time,wcf_text,wcf_cloum,rowFlag);
    //把创建的内容写入到输出流中，并关闭输出流
    workbook.write();
    workbook.close();
    os.close();


}

/**
 * 科队领导得分考评表
 * @param sheet
 * @param deptId
 * @param time
 * @param wcf_text
 * @param wcf_cloum
 * @return
 * @throws WriteException
 * @throws IOException
 */
public int kldExcel(WritableSheet sheet,String deptId,String time, WritableCellFormat wcf_text,WritableCellFormat wcf_cloum)throws WriteException,IOException{
    //设置列名高宽度
    for(int a=0;a<7;a++){
        sheet.setColumnView(a, 20);
    }
    //合并标题行
    sheet.mergeCells(0, 0, 6, 0);
    //标题列
    sheet.addCell(new Label(0,1,"科队",wcf_cloum));
    sheet.addCell(new Label(1,1,"科领导",wcf_cloum));
    sheet.addCell(new Label(2,1,"基准分",wcf_cloum));
    sheet.addCell(new Label(3,1,"查获违法违规分",wcf_cloum));
    sheet.addCell(new Label(4,1,"业务督察检查分",wcf_cloum));
    sheet.addCell(new Label(5,1,"证研情报分",wcf_cloum));
    sheet.addCell(new Label(6,1,"总分",wcf_cloum));
    //查询报表数据
    GetScoreForReport gsfr = (GetScoreForReport)ResourceManage.getContext("getScoreForReport");
    List list= null;
    //获取科室名称
    String depname=gsfr.getdepnameBydepid(deptId);
    String jzf="0";
    double xs = gsfr.xishu();
    BigDecimal b = new BigDecimal(Double.toString(0.0));
    //行数标记
    int flag=0;
    if(StringUtils.isNotBlank(depname)){
        list = gsfr.getDataForGRDFKLD(deptId,time,time);
        if(StringUtils.equals("2430",deptId)){         //二线队改为部门id
            jzf = gsfr.getScoreByfilte_new(deptId,time);
            b=new BigDecimal(jzf).multiply(new BigDecimal(Double.toString(xs)));//二线队*3.3改为可变系数
        }else{
            jzf = gsfr.getScoreByfilte(deptId,time);
            b=new BigDecimal(jzf);
        }
        //基准分
        jzf=b.setScale(2, BigDecimal.ROUND_HALF_UP).toString();
        //用户信息
        DataTable dataTable=(DataTable) list.get(0);
        //违法违规分
        DataTable wfwgf=(DataTable) list.get(1);
        //督查检查分
        DataTable dcjcf=(DataTable) list.get(2);
        //证研情报分
        DataTable zyqbf=(DataTable) list.get(3);
        for(int i=0;i<dataTable.getRow();i++){
            //科队
            sheet.addCell(new Label(0,i+2,depname,wcf_text));
            //科领导
            sheet.addCell(new Label(1,i+2,dataTable.getData(i).getString("SU02"),wcf_text));
            //基准分
            sheet.addCell(new Label(2,i+2,jzf,wcf_text));
            //违法违规分
            sheet.addCell(new Label(3,i+2,wfwgf.getData(i).getString("WFWGFS"),wcf_text));
            //督查检查分
            sheet.addCell(new Label(4,i+2,dcjcf.getData(i).getString("RCYWFS"),wcf_text));
            //证研情报分
            sheet.addCell(new Label(5,i+2,zyqbf.getData(i).getString("ZYQBFS"),wcf_text));
            //总分
            String zf= new BigDecimal(jzf).add(new BigDecimal(wfwgf.getData(i).getString("WFWGFS"))).add(new BigDecimal(dcjcf.getData(i).getString("RCYWFS"))).add(new BigDecimal(zyqbf.getData(i).getString("ZYQBFS"))).toString();
            sheet.addCell(new Label(6,i+2,zf,wcf_text));
            flag++;
        }
    }
    return flag+10;
}

/**
 * 检查员执勤质量考评情况
 * @param sheet
 * @param deptId
 * @param time
 * @param wcf_text
 * @param wcf_cloum
 * @throws WriteException
 * @throws IOException
 */
public void jcyExcel(WritableSheet sheet,String deptId,String time, WritableCellFormat wcf_text,WritableCellFormat wcf_cloum,int rowFlag)throws WriteException,IOException{
    //设置列名高宽度
    for(int a=0;a<9;a++){
        sheet.setColumnView(a, 20);
    }
    //合并标题行
    sheet.mergeCells(rowFlag, rowFlag, 7, rowFlag);
    if(StringUtils.equals("2430",deptId)){
        //标题列
        sheet.addCell(new Label(0,rowFlag+1,"姓名",wcf_cloum));
        sheet.addCell(new Label(1,rowFlag+1,"重点人员、遣返人员审查基准分",wcf_cloum));
        sheet.addCell(new Label(2,rowFlag+1,"偷渡案件办理加扣分",wcf_cloum));
        sheet.addCell(new Label(3,rowFlag+1,"日常业务分",wcf_cloum));
        sheet.addCell(new Label(4,rowFlag+1,"证研情报分",wcf_cloum));
        sheet.addCell(new Label(5,rowFlag+1,"API预查重点分数",wcf_cloum));
        sheet.addCell(new Label(6,rowFlag+1,"验放人数分",wcf_cloum));
        sheet.addCell(new Label(7,rowFlag+1,"总分",wcf_cloum));
    }else{
        //标题列
        sheet.addCell(new Label(0,rowFlag+1,"科队",wcf_cloum));
        sheet.addCell(new Label(1,rowFlag+1,"姓名",wcf_cloum));
        sheet.addCell(new Label(2,rowFlag+1,"验放人数分",wcf_cloum));
        sheet.addCell(new Label(3,rowFlag+1,"查获违法违规分",wcf_cloum));
        sheet.addCell(new Label(4,rowFlag+1,"日常业务分",wcf_cloum));
        sheet.addCell(new Label(5,rowFlag+1,"证研情报分",wcf_cloum));
        sheet.addCell(new Label(6,rowFlag+1,"科领导奖励分",wcf_cloum));
        sheet.addCell(new Label(7,rowFlag+1,"总分",wcf_cloum));
    }
    //查询报表数据
    GetScoreForReport gsfr = (GetScoreForReport)ResourceManage.getContext("getScoreForReport");
    List list= null;
    //获取科室名称
    String depname=gsfr.getdepnameBydepid(deptId);
    //分数总计
    Double total=0.00;
    DecimalFormat decimalFormat=new DecimalFormat("#.00");
    if(StringUtils.isNotBlank(depname)){
        if(StringUtils.equals("2430",deptId)){
            list = gsfr.getDataForGRDFEXD(deptId,time,time);
            DataTable list0=(DataTable) list.get(0);
            DataTable list1=(DataTable) list.get(1);
            DataTable list2=(DataTable) list.get(2);
            DataTable list3=(DataTable) list.get(3);
            DataTable list4=(DataTable) list.get(4);
            DataTable list5=(DataTable) list.get(5);
            DataTable list7=(DataTable) list.get(7);
            for(int i=0;i<list0.getRow();i++){
                //执勤七科
                sheet.addCell(new Label(0,rowFlag+2+i,list0.getData(i).getString("SU02"),wcf_text));
                sheet.addCell(new Label(1,rowFlag+2+i,list1.getData(i).getString("RYQFFS"),wcf_text));
                sheet.addCell(new Label(2,rowFlag+2+i,list2.getData(i).getString("TDAJFS"),wcf_text));
                sheet.addCell(new Label(3,rowFlag+2+i,list3.getData(i).getString("RCYWFS"),wcf_text));
                sheet.addCell(new Label(4,rowFlag+2+i,list4.getData(i).getString("ZYQBFS"),wcf_text));
                sheet.addCell(new Label(5,rowFlag+2+i,list5.getData(i).getString("APIYCZD"),wcf_text));
                sheet.addCell(new Label(6,rowFlag+2+i,list7.getData(i).getString("YFFS"),wcf_text));
                //总分
                BigDecimal sum= new BigDecimal(list1.getData(i).getString("RYQFFS")).add(new BigDecimal(list2.getData(i).getString("TDAJFS"))).add(new BigDecimal(list3.getData(i).getString("RCYWFS"))).add(new BigDecimal(list4.getData(i).getString("ZYQBFS"))).add(new BigDecimal(list5.getData(i).getString("APIYCZD"))).add(new BigDecimal(list7.getData(i).getString("YFFS")));
                sheet.addCell(new Label(7,rowFlag+2+i,sum.toString(),wcf_text));
                total+=sum.doubleValue();
                if(list0.getRow()-i == 1){
                    //合并单元格
                    sheet.mergeCells(0, rowFlag+3+i, 6, rowFlag+3+i);
                    //合计总分
                    sheet.addCell(new Label(0,rowFlag+3+i,"总计",wcf_text));
                    sheet.addCell(new Label(7,rowFlag+3+i,decimalFormat.format(total),wcf_text));
                }
            }
        }else{
            list = gsfr.getDataForGRDF(deptId,time,time);
            DataTable list0=(DataTable) list.get(0);
            DataTable list1=(DataTable) list.get(1);
            DataTable list2=(DataTable) list.get(2);
            DataTable list3=(DataTable) list.get(3);
            DataTable list4=(DataTable) list.get(4);
            DataTable list5=(DataTable) list.get(5);
            for(int i=0;i<list0.getRow();i++){
                //其余科
                sheet.addCell(new Label(0,rowFlag+2+i,depname,wcf_text));
                sheet.addCell(new Label(1,rowFlag+2+i,list0.getData(i).getString("SU02"),wcf_text));
                sheet.addCell(new Label(2,rowFlag+2+i,list1.getData(i).getString("YFFS"),wcf_text));
                sheet.addCell(new Label(3,rowFlag+2+i,list2.getData(i).getString("WFWGFS"),wcf_text));
                sheet.addCell(new Label(4,rowFlag+2+i,list3.getData(i).getString("RCYWFS"),wcf_text));
                sheet.addCell(new Label(5,rowFlag+2+i,list4.getData(i).getString("ZYQBFS"),wcf_text));
                sheet.addCell(new Label(6,rowFlag+2+i,list5.getData(i).getString("KLDFS"),wcf_text));
                //总分
                BigDecimal sum= new BigDecimal(list1.getData(i).getString("YFFS")).add(new BigDecimal(list2.getData(i).getString("WFWGFS"))).add(new BigDecimal(list3.getData(i).getString("RCYWFS"))).add(new BigDecimal(list4.getData(i).getString("ZYQBFS"))).add(new BigDecimal(list5.getData(i).getString("KLDFS")));
                sheet.addCell(new Label(7,rowFlag+2+i,sum.toString(),wcf_text));
                total+=sum.doubleValue();
                if(list0.getRow()-i == 1){
                    sheet.mergeCells(0, rowFlag+3+i, 6, rowFlag+3+i);
                    sheet.addCell(new Label(0,rowFlag+3+i,"总计",wcf_text));
                    sheet.addCell(new Label(7,rowFlag+3+i,decimalFormat.format(total),wcf_text));
                }
            }
        }
    }
}

3.最后效果图

萌新比克o

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
java jxl实现导出数据下载excel

1.创建下载jsp界面&lt;%@ page contentType="text/html;charset=UTF-8" language="java" %&gt;&lt;%@ page language="java" import="java.util.*" pageEncoding="gb2312"%&gt;&lt;%@ page import=&quot
复制链接

扫一扫