jxl.Workbook类导出考勤Excel(.xls)

于 2019-01-04 00:05:35 发布
阅读量377 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： 程序基础 文章标签： 导出Excel .xls workbook
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/javawebui/article/details/85731201
这篇博客展示了如何利用Java的jxl库来创建一个考勤统计报表的Excel文件(.xls)。通过获取请求参数，设定文件路径，创建并写入数据到工作簿中，实现了动态导出考勤数据的功能。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

<%@ page language=“java” pageEncoding=“GBK” %>
<%@page import=“cn.project.attendance.report.dao.impl.SearchAttendanceImpl” %>
<%@page import=“cn.project.attendance.report.entity.attendance” %>
<%@page import=“jxl.Workbook” %>
<%@page import=“jxl.format.UnderlineStyle” %>
<%@page import=“jxl.format.VerticalAlignment” %>
<%@ page import=“jxl.write.*” %>
<%@ page import=“weaver.general.BaseBean” %>
<%@ page import=“weaver.general.Util” %>
<%@ page import=“java.io.File” %>
<%@ page import=“java.io.FileInputStream” %>
<%@ page import=“java.io.FileOutputStream” %>
<%@ page import=“java.io.OutputStream” %>
<%@ page import=“java.text.SimpleDateFormat” %>
<%@ page import=“java.util.Date” %>
<%@ page import=“java.util.List” %>
<%
BaseBean bae = new BaseBean();
//定义Excel文件路径
String filePathName = new SimpleDateFormat(“yyyyMMddHHmmss”).format(new Date()) + “.xls”;
String filePath = application.getRealPath("/") + “report\signfile\” + filePathName;
String formDate = Util.null2String(request.getParameter(“formDate”)); //开始日期
String toDate = Util.null2String(request.getParameter(“toDate”)); //结束日期
String userid = Util.null2String(request.getParameter(“userid”)); //结束日期
String cuserid = Util.null2String(request.getParameter(“cuserid”));// 当前用户id
String usekind = Util.null2String(request.getParameter(“usekind”));// 用工性质
String departmentId = Util.null2String(request.getParameter(“departmentId”));// 结束日期
BaseBean base = new BaseBean();
OutputStream os = null;
try {
os = new FileOutputStream(filePath);
// 打开文件
WritableWorkbook book = Workbook.createWorkbook(os);
// 生成名为“第一页”的工作表，参数0表示这是第一页
WritableSheet sheetOne = book.createSheet(“考勤统计报表”, 0);

    /**
     * 定义单元格样式
     */
    WritableFont wf_title = new WritableFont(WritableFont.ARIAL, 20, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色
    WritableFont wf_head = new WritableFont(WritableFont.ARIAL, 11, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色
    WritableCellFormat wcf_title = new WritableCellFormat(wf_title); // 单元格定义
    wcf_title.setBackground(jxl.format.Colour.LIGHT_GREEN); // 设置单元格的背景颜色
    wcf_title.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式
    wcf_title.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); // 设置边框
    WritableCellFormat wcf_title1 = new WritableCellFormat(wf_title); // 单元格定义
    wcf_title1.setBackground(jxl.format.Colour.LIGHT_GREEN); // 设置单元格的背景颜色
    wcf_title1.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式
    wcf_title1.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); // 设置边框
    WritableCellFormat wcf_head1 = new WritableCellFormat(wf_head);
    wcf_head1.setBackground(jxl.format.Colour.LIGHT_GREEN);
    wcf_head1.setAlignment(jxl.format.Alignment.CENTRE);
    wcf_head1.setVerticalAlignment(VerticalAlignment.CENTRE);
    wcf_head1.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK);
    wcf_head1.setWrap(true); // 自动换行
    sheetOne.setColumnView(0, 15); // 设置列的宽度
    sheetOne.setColumnView(1, 15); // 设置列的宽度
    sheetOne.setColumnView(2, 10); // 设置列的宽度
    sheetOne.setColumnView(3, 10); // 设置列的宽度
    sheetOne.setColumnView(4, 10); // 设置列的宽度
    sheetOne.setColumnView(5, 10); // 设置列的宽度
    sheetOne.setColumnView(6, 10); // 设置列的宽度
    sheetOne.setColumnView(7, 10); // 设置列的宽度
    sheetOne.setColumnView(8, 10); // 设置列的宽度
    sheetOne.setColumnView(9, 10); // 设置列的宽度
    sheetOne.setColumnView(10, 10); // 设置列的宽度
    sheetOne.setColumnView(11, 10); // 设置列的宽度
    sheetOne.setColumnView(12, 10); // 设置列的宽度
    sheetOne.setColumnView(13, 10); // 设置列的宽度
    sheetOne.setColumnView(14, 10); // 设置列的宽度
    sheetOne.setColumnView(15, 10); // 设置列的宽度
    sheetOne.setColumnView(16, 10); // 设置列的宽度
    sheetOne.setColumnView(17, 10); // 设置列的宽度
    sheetOne.setColumnView(18, 10); // 设置列的宽度
    sheetOne.setColumnView(19, 10); // 设置列的宽度
    sheetOne.setColumnView(20, 10); // 设置列的宽度
    sheetOne.setColumnView(21, 10); // 设置列的宽度
    // 在Label对象的构造子中指名单元格位置是第一列第一行(0,0)
    // 以及单元格内容为test
    Label title = new Label(0, 0, formDate + "至" + toDate + "考勤情况统计", wcf_title);
    Label titleOne = new Label(0, 1, "部门名称", wcf_head1);
    Label column1 = new Label(1, 1, "姓名", wcf_head1);
    Label column11 = new Label(2, 1, "用工性质", wcf_head1);
    Label titleTwo = new Label(3, 1, "考勤情况", wcf_head1);
    Label column2 = new Label(3, 2, "应出勤（天）", wcf_head1);
    Label column3 = new Label(4, 2, "工作时长（小时）", wcf_head1);
    Label column4 = new Label(5, 2, "迟到（次）", wcf_head1);
    Label column5 = new Label(6, 2, "早退（次）", wcf_head1);
    Label column6 = new Label(7, 2, "请假（天）", wcf_head1);
    Label column7 = new Label(17, 2, "值班（天）", wcf_head1);
    Label column0 = new Label(18, 2, "值班（天）", wcf_head1);
    Label column8 = new Label(19, 2, "出差（天）", wcf_head1);
    Label column9 = new Label(20, 2, "公出（天）", wcf_head1);
    Label column10 = new Label(21, 2, "旷工（次）", wcf_head1);
    Label c1 = new Label(7, 3, "事假", wcf_head1);
    Label c2 = new Label(8, 3, "病假", wcf_head1);
    Label c3 = new Label(9, 3, "探亲假", wcf_head1);
    Label c4 = new Label(10, 3, "年假", wcf_head1);
    Label c5 = new Label(11, 3, "婚假", wcf_head1);
    Label c6 = new Label(12, 3, "产假", wcf_head1);
    Label c7 = new Label(13, 3, "丧假", wcf_head1);
    Label c8 = new Label(14, 3, "辐射假", wcf_head1);
    Label c9 = new Label(15, 3, "产检假", wcf_head1);
    Label c10 = new Label(16, 3, "调休假", wcf_head1);
    Label c11 = new Label(17, 3, "关联调休", wcf_head1);
    Label c12 = new Label(18, 3, "其他", wcf_head1);

    sheetOne.addCell(title);
    sheetOne.addCell(titleOne);
    sheetOne.addCell(titleTwo);
    sheetOne.addCell(column1);
    sheetOne.addCell(column2);
    sheetOne.addCell(column3);
    sheetOne.addCell(column4);
    sheetOne.addCell(column5);
    sheetOne.addCell(column6);
    sheetOne.addCell(column7);
    sheetOne.addCell(column8);
    sheetOne.addCell(column9);
    sheetOne.addCell(column10);
    sheetOne.addCell(column0);
    sheetOne.addCell(column11);

    sheetOne.addCell(c1);
    sheetOne.addCell(c2);
    sheetOne.addCell(c3);
    sheetOne.addCell(c4);
    sheetOne.addCell(c5);
    sheetOne.addCell(c6);
    sheetOne.addCell(c7);
    sheetOne.addCell(c8);
    sheetOne.addCell(c9);
    sheetOne.addCell(c10);
    sheetOne.addCell(c11);
    sheetOne.addCell(c12);
    sheetOne.mergeCells(0, 0, 21, 0);// 头
    sheetOne.mergeCells(0, 1, 0, 3);//  部门
    sheetOne.mergeCells(1, 1, 1, 3); // 姓名
    sheetOne.mergeCells(2, 1, 2, 3); // 用工性质
    sheetOne.mergeCells(3, 1, 21, 1);//考勤情况
    sheetOne.mergeCells(7, 2, 16, 2);//请假
    sheetOne.mergeCells(17, 2, 18, 2);//加班
    sheetOne.mergeCells(19, 2, 19, 3);//出差
    sheetOne.mergeCells(20, 2, 20, 3);//公出
    sheetOne.mergeCells(21, 2, 21, 3);//旷工
    sheetOne.mergeCells(3, 2, 3, 3);//应出勤（天）
    sheetOne.mergeCells(4, 2, 4, 3);//工作时长（小时）
    sheetOne.mergeCells(5, 2, 5, 3);//迟到（次）
    sheetOne.mergeCells(6, 2, 6, 3);//早退（次）
		/* 动态数据 */
    int count = 4;
    SearchAttendanceImpl search = new SearchAttendanceImpl();
    List<attendance> list = search.getAttendaceDate(formDate, toDate, cuserid, userid, usekind, departmentId);
    for (int i = 0; i < list.size(); i++) {
        attendance att = list.get(i);
        Label label = new Label(0, count, att.getDeptname(), wcf_head1);
        sheetOne.addCell(label);
        label = new Label(1, count, att.getUsername(), wcf_head1);
        sheetOne.addCell(label);
        label = new Label(2, count, att.getUseKind(), wcf_head1);
        sheetOne.addCell(label);
        label = new Label(3, count, att.getAttDay(), wcf_head1);
        sheetOne.addCell(label);
        label = new Label(4, count, att.getWorktime(), wcf_head1);
        sheetOne.addCell(label);
        label = new Label(5, count, att.getLate(), wcf_head1);
        sheetOne.addCell(label);
        label = new Label(6, count, att.getLeaveEarly(), wcf_head1);
        sheetOne.addCell(label);
        label = new Label(7, count, att.getCasualLeave(), wcf_head1);
        sheetOne.addCell(label);
        label = new Label(8, count, att.getSickLeave(), wcf_head1);
        sheetOne.addCell(label);
        label = new Label(9, count, att.getHomeLeave(), wcf_head1);
        sheetOne.addCell(label);
        label = new Label(10, count, att.getAnnualLeave(), wcf_head1);
        sheetOne.addCell(label);
        label = new Label(11, count, att.getMarriageLeave(), wcf_head1);
        sheetOne.addCell(label);
        label = new Label(12, count, att.getMaternityLeave(), wcf_head1);
        sheetOne.addCell(label);
        label = new Label(13, count, att.getFuneralLeave(), wcf_head1);
        sheetOne.addCell(label);
        label = new Label(14, count, att.getRadiateLeave(), wcf_head1);
        sheetOne.addCell(label);
        label = new Label(15, count, att.getPreCheckLeave(), wcf_head1);
        sheetOne.addCell(label);
        label = new Label(16, count, att.getFallls(), wcf_head1);
        sheetOne.addCell(label);
        label = new Label(17, count, att.getIsRelevanceFalls(), wcf_head1);
        sheetOne.addCell(label);
        label = new Label(18, count, att.getNotisRelevanceFalls(), wcf_head1);
        sheetOne.addCell(label);
        label = new Label(19, count, att.getEvectionDay(), wcf_head1);
        sheetOne.addCell(label);
        label = new Label(20, count, att.getTravelDay(), wcf_head1);
        sheetOne.addCell(label);
        label = new Label(21, count, att.getAbsenteeismDay(), wcf_head1);
        sheetOne.addCell(label);
        count++;
    }
    book.write();
    book.close();
} catch (Exception e) {
    base.writeLog("[考勤报表生成异常]" + e.toString());
}
FileInputStream input = null;
ServletOutputStream output = null;
File file = null;
try {
    output = response.getOutputStream();  //取得输出流
    out.clear();
    file = new File(filePath);
    response.reset();
    response.setContentType("application/vnd.ms-excel;charset=ISO-8859-1"); //设定输出内容类型
    response.setCharacterEncoding("utf-8");
    //    response.setHeader("Content-Disposition", "attachment;filename=" + filePathName);// 设定输出文件头
    // response.setHeader("Content-Disposition", "attachment;filename=");// 设定输出文件头
    String fname =new String((formDate + "至" + toDate + "考勤情况统计").getBytes("gb2312"),"iso8859-1");
    response.setHeader("Content-Disposition", "attachment;filename=" + fname+".xls");
    long fileLength = file.length();
    String length1 = String.valueOf(fileLength);
    response.setHeader("Content_Length", length1);
    input = new FileInputStream(file);
    byte[] b = new byte[1024];
    int n;
    while ((n = input.read(b)) != -1) {
        output.write(b, 0, n);
    }
    output.flush();
} catch (Exception e) {
    e.printStackTrace();
} finally {
    if (input != null) {
        input.close();
        input = null;
    }
    if (output != null) {
        output.close();
        output = null;
        out.clear();
        out = pageContext.pushBody();
    }
    if (file != null) {
        file.delete();
    }
}
bae.writeLog("生成路径结束");

%>

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值