Exemple #1
0
  /**
   * 添加单元格&内容.
   *
   * @param ws
   * @param cel
   * @param row
   * @param valObject
   */
  private void addCell(WritableSheet ws, int cel, int row, Object valObj) throws WriteException {

    if (valObj == null) {
      Label cr = new Label(cel, row, "");
      ws.addCell(cr);
      return;
    }

    @SuppressWarnings("rawtypes")
    Class clazz2 = valObj.getClass();
    if (clazz2 == String.class) {
      Label cr = new Label(cel, row, (String) valObj);
      ws.addCell(cr);
      return;
    } else if (clazz2 == int.class || clazz2 == Integer.class) {
      Number cr = new Number(cel, row, Double.parseDouble(((Integer) valObj).toString()));
      ws.addCell(cr);
      return;
    } else if (clazz2 == long.class || clazz2 == Long.class) {
      Number cr = new Number(cel, row, Double.parseDouble(((Long) valObj).toString()));
      ws.addCell(cr);
      return;
    } else if (clazz2 == float.class || clazz2 == Float.class) {
      Number cr = new Number(cel, row, Double.parseDouble(((Float) valObj).toString()));
      ws.addCell(cr);
      return;
    } else if (clazz2 == double.class || clazz2 == Double.class) {
      Number cr = new Number(cel, row, Double.parseDouble(((Double) valObj).toString()));
      ws.addCell(cr);
      return;
    } else if (clazz2 == Date.class) {
      DateTime cr = new DateTime(cel, row, (Date) valObj);
      ws.addCell(cr);
      return;
    } else if (clazz2 == boolean.class || clazz2 == Boolean.class) {
      jxl.write.Boolean cr = new jxl.write.Boolean(cel, row, (Boolean) valObj);
      ws.addCell(cr);
      return;
    } else if (clazz2 == BigDecimal.class) {
      NumberFormat nf = new NumberFormat("0.00");
      WritableCellFormat wcf = new WritableCellFormat(nf);
      wcf.setFont(new WritableFont(WritableFont.createFont("宋体"), PS));
      Number cr = new Number(cel, row, ((BigDecimal) valObj).doubleValue(), wcf);
      ws.addCell(cr);
      return;
    } else if (clazz2 == Short.class) {
      Number cr = new Number(cel, row, Double.parseDouble(((Short) valObj).toString()));
      ws.addCell(cr);
      return;
    }
  }
  /**
   *
   *
   * <DL>
   *   <DT>メソッド記述:
   *   <DD><br>
   * </DL>
   *
   * @param MCtlListVo , String
   * @return int
   * @throws BizException
   */
  public boolean exportEXCEL(String strFilePath) throws BizException {
    try {
      List<MCtlVo> lstMCtlVo = null;

      /** データある場合、データ取得 */
      lstMCtlVo = dao.exportEXCEL();

      if (lstMCtlVo == null || lstMCtlVo.size() == 0) {
        return false;
      }
      WritableWorkbook workbook = Workbook.createWorkbook(new File(strFilePath));
      WritableSheet s1 = workbook.createSheet("M_CTL", 0);

      FontName fontName = WritableFont.createFont("MS ゴシック");
      WritableFont fontFormat = new WritableFont(fontName, 12);

      // header format (color, padding, border)
      WritableCellFormat headFormat = new WritableCellFormat(fontFormat);
      headFormat.setAlignment(Alignment.CENTRE);
      headFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
      headFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
      headFormat.setBackground(Colour.VERY_LIGHT_YELLOW);

      // left format
      WritableCellFormat leftCellFormat = new WritableCellFormat(fontFormat);
      leftCellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
      leftCellFormat.setAlignment(Alignment.LEFT);

      // center format
      WritableCellFormat centerCellFormat = new WritableCellFormat(fontFormat);
      centerCellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
      centerCellFormat.setAlignment(Alignment.CENTRE);

      // right format
      WritableCellFormat rightCellFormat = new WritableCellFormat(fontFormat);
      rightCellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
      rightCellFormat.setAlignment(Alignment.RIGHT);

      final String[] excelHeader = {
        "USERID",
        "KEY",
        "NAME",
        "DATA",
        "HELP",
        "入力桁数",
        "入力桁数(小数桁)",
        "入力属性地",
        "メンテフラグ",
        "コントロールフラグ",
        "変更可否フラグ",
        "登録ユーザー名",
        "登録PC名",
        "登録日付",
        "登録時刻",
        "最終更新ユーザー名",
        "最終更新PC名",
        "最終更新日付",
        "最終更新時刻"
      };

      int columnCount = excelHeader.length;
      for (int i = 0; i < columnCount; i++) {
        Label lbHeader = new Label(i, 0, excelHeader[i], headFormat);
        s1.addCell(lbHeader);
        if (i == 2 || i == 3) s1.setColumnView(i, 60);
        else if (i == 4) s1.setColumnView(i, 150);
        else s1.setColumnView(i, 20);
      }
      for (int i = 0; i < lstMCtlVo.size(); i++) {
        int column = 0;
        MCtlVo mCtlVo = (MCtlVo) lstMCtlVo.get(i);

        s1.addCell(new Label(column++, i + 1, mCtlVo.getUserid(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getCKey(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getCName(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getCData(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getCHelp(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getCBm(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getCDecbm(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getCAttr(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getMtnFlg(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getCntFlg(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getUpdFlg(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getAddUserView(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getAddPc(), leftCellFormat));
        s1.addCell(
            new Label(
                column++,
                i + 1,
                DateUtils.getDateWithSplitYobi(mCtlVo.getAddDate()),
                centerCellFormat));
        s1.addCell(
            new Label(
                column++,
                i + 1,
                DateUtils.getTimeWithSplit(mCtlVo.getAddTime()),
                centerCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getLastupUserView(), leftCellFormat));
        s1.addCell(new Label(column++, i + 1, mCtlVo.getLastupPc(), leftCellFormat));
        s1.addCell(
            new Label(
                column++,
                i + 1,
                DateUtils.getDateWithSplitYobi(mCtlVo.getLastupDate()),
                centerCellFormat));
        s1.addCell(
            new Label(
                column++,
                i + 1,
                DateUtils.getTimeWithSplit(mCtlVo.getLastupTime()),
                centerCellFormat));
      }
      workbook.write();
      workbook.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
    return true;
  }
  public void createEmployeesFile(List<Employee> list, OutputStream os) throws Exception {
    WritableWorkbook wbook = Workbook.createWorkbook(os); // 建立excel文件
    WritableSheet wsheet = wbook.createSheet("Sheet1", 0); // 工作表名称

    // 设置公司名
    WritableFont companyfont =
        new WritableFont(
            WritableFont.createFont("宋体"),
            18,
            WritableFont.BOLD,
            false,
            jxl.format.UnderlineStyle.NO_UNDERLINE,
            jxl.format.Colour.BLACK);
    WritableCellFormat companyFormat = new WritableCellFormat(companyfont);
    companyFormat.setAlignment(jxl.format.Alignment.CENTRE);
    companyFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
    Label excelCompany = new Label(0, 0, "桐庐富伟针织有限公司员工花名册", companyFormat);
    wsheet.addCell(excelCompany);
    wsheet.mergeCells(0, 0, 13, 0);
    wsheet.setRowView(0, 800);

    // 设置Excel字体
    WritableFont wfont =
        new WritableFont(
            WritableFont.createFont("宋体"),
            10,
            WritableFont.BOLD,
            false,
            jxl.format.UnderlineStyle.NO_UNDERLINE,
            jxl.format.Colour.BLACK);
    WritableCellFormat titleFormat = new WritableCellFormat(wfont);
    titleFormat.setAlignment(jxl.format.Alignment.CENTRE);
    titleFormat.setBorder(
        jxl.format.Border.ALL,
        jxl.format.BorderLineStyle.THIN,
        jxl.format.Colour.BLACK); // BorderLineStyle边框

    WritableFont wfont2 =
        new WritableFont(
            WritableFont.createFont("宋体"),
            10,
            WritableFont.NO_BOLD,
            false,
            jxl.format.UnderlineStyle.NO_UNDERLINE,
            jxl.format.Colour.BLACK);
    WritableCellFormat titleFormat2 = new WritableCellFormat(wfont2);
    titleFormat2.setAlignment(jxl.format.Alignment.CENTRE);
    titleFormat2.setBorder(
        jxl.format.Border.ALL,
        jxl.format.BorderLineStyle.THIN,
        jxl.format.Colour.BLACK); // BorderLineStyle边框

    String[] title = {
      "编号", "姓名", "性别", "入厂日期", "身份证号码", "联系方式", "岗位", "部门", "家庭住址", "现居住地", "合同期限", "用工形式", "离职时间",
      "时薪"
    };
    // 设置Excel表头
    int col = 0;
    int merge_col = 0;
    int columnBestWidth[] = new int[title.length + 1]; // 保存最佳列宽数据的数组
    for (int i = 0; i < title.length; i++, col++) {
      columnBestWidth[col] = title[i].getBytes().length;
      Label excelTitle = new Label(col, 1, title[i], titleFormat);

      if (title[i].equals("合同期限")) {
        merge_col = col;
        col++;
        columnBestWidth[col] = title[i].getBytes().length;
      }
      wsheet.addCell(excelTitle);
    }

    wsheet.setRowView(1, 400);
    wsheet.mergeCells(merge_col, 1, merge_col + 1, 1);

    int c = 2; // 用于循环时Excel的行号 		

    for (Employee employee : list) {
      wsheet.setRowView(c, 400);
      Label content1 = new Label(0, c, employee.getNumber(), titleFormat2);
      Label content2 = new Label(1, c, employee.getName(), titleFormat2);
      Label content3 = new Label(2, c, employee.getSex(), titleFormat2);
      Label content4 =
          new Label(3, c, DateTool.formatDateYMD(employee.getEnter_at()), titleFormat2);
      Label content5 = new Label(4, c, employee.getId_card(), titleFormat2);
      Label content6 = new Label(5, c, employee.getTel(), titleFormat2);
      Label content7 = new Label(6, c, employee.getJob(), titleFormat2);
      Label content8 =
          new Label(7, c, SystemCache.getDepartmentName(employee.getDepartmentId()), titleFormat2);
      Label content9 = new Label(8, c, employee.getAddress_home(), titleFormat2);
      Label content10 = new Label(9, c, employee.getAddress(), titleFormat2);
      Label content11 =
          new Label(10, c, DateTool.formatDateYMD(employee.getAgreement_at()), titleFormat2);
      Label content12 =
          new Label(11, c, DateTool.formatDateYMD(employee.getAgreement_end()), titleFormat2);
      Label content13 = new Label(12, c, employee.getEmployee_type(), titleFormat2);
      Label content14 =
          new Label(13, c, DateTool.formatDateYMD(employee.getLeave_at(), "/"), titleFormat2);
      Label content15 =
          new Label(
              14,
              c,
              employee.getHour_salary() == null ? "" : String.valueOf(employee.getHour_salary()),
              titleFormat2);

      wsheet.addCell(content1);
      wsheet.addCell(content2);
      wsheet.addCell(content3);
      wsheet.addCell(content4);
      wsheet.addCell(content5);
      wsheet.addCell(content6);
      wsheet.addCell(content7);
      wsheet.addCell(content8);
      wsheet.addCell(content9);
      wsheet.addCell(content10);
      wsheet.addCell(content11);
      wsheet.addCell(content12);
      wsheet.addCell(content13);
      wsheet.addCell(content14);
      wsheet.addCell(content15);

      int width1 = content1.getContents().getBytes().length;
      int width2 = content2.getContents().getBytes().length;
      int width3 = content3.getContents().getBytes().length;
      int width4 = content4.getContents().getBytes().length;
      int width5 = content5.getContents().getBytes().length;
      int width6 = content6.getContents().getBytes().length;
      int width7 = content7.getContents().getBytes().length;
      int width8 = content8.getContents().getBytes().length;
      int width9 = content9.getContents().getBytes().length;
      int width10 = content10.getContents().getBytes().length;
      int width11 = content11.getContents().getBytes().length;
      int width12 = content12.getContents().getBytes().length;
      int width13 = content13.getContents().getBytes().length;
      int width14 = content14.getContents().getBytes().length;
      int width15 = content15.getContents().getBytes().length;
      if (columnBestWidth[0] < width1) {
        columnBestWidth[0] = width1;
      }
      if (columnBestWidth[1] < width2) {
        columnBestWidth[1] = width2;
      }
      if (columnBestWidth[2] < width3) {
        columnBestWidth[2] = width3;
      }
      if (columnBestWidth[3] < width4) {
        columnBestWidth[3] = width4;
      }
      if (columnBestWidth[4] < width5) {
        columnBestWidth[4] = width5;
      }
      if (columnBestWidth[5] < width6) {
        columnBestWidth[5] = width6;
      }
      if (columnBestWidth[6] < width7) {
        columnBestWidth[6] = width7;
      }
      if (columnBestWidth[7] < width8) {
        columnBestWidth[7] = width8;
      }
      if (columnBestWidth[8] < width9) {
        columnBestWidth[8] = width9;
      }
      if (columnBestWidth[9] < width10) {
        columnBestWidth[9] = width10;
      }
      if (columnBestWidth[10] < width11) {
        columnBestWidth[10] = width11;
      }
      if (columnBestWidth[11] < width12) {
        columnBestWidth[11] = width12;
      }
      if (columnBestWidth[12] < width13) {
        columnBestWidth[12] = width13;
      }
      if (columnBestWidth[13] < width14) {
        columnBestWidth[13] = width14;
      }
      if (columnBestWidth[14] < width15) {
        columnBestWidth[14] = width15;
      }
      c++;
    }
    for (int p = 0; p < columnBestWidth.length; ++p) {
      wsheet.setColumnView(p, columnBestWidth[p] + 1);
    }
    wbook.write(); // 写入文件
    wbook.close();
    os.close();
  }