/**
  * 取报表标题单元格格式
  *
  * @return
  */
 public WritableCellFormat getFt_title() {
   try {
     ft_title = new WritableCellFormat();
     ft_title.setFont(new WritableFont(WritableFont.TIMES, 16, WritableFont.BOLD));
     ft_title.setAlignment(Alignment.CENTRE);
     ft_title.setVerticalAlignment(VerticalAlignment.CENTRE);
     ft_title.setBorder(Border.ALL, BorderLineStyle.THIN);
   } catch (Exception e) {
     log.info(e);
   }
   return ft_title;
 }
 /**
  * 单元格右对齐且黑体
  *
  * @return
  */
 public WritableCellFormat getFt_item_right_bold() {
   try {
     ft_item_right_bold = new WritableCellFormat();
     ft_item_right_bold.setFont(new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD));
     ft_item_right_bold.setAlignment(Alignment.RIGHT);
     ft_item_right_bold.setVerticalAlignment(VerticalAlignment.CENTRE);
     ft_item_right_bold.setBorder(Border.ALL, BorderLineStyle.THIN);
   } catch (Exception e) {
     log.info(e);
   }
   return ft_item_right_bold;
 }
 /**
  * 单元格左对齐
  *
  * @return
  */
 public WritableCellFormat getFt_item_left() {
   try {
     ft_item_left = new WritableCellFormat();
     ft_item_left.setFont(new WritableFont(WritableFont.TIMES, 10, WritableFont.NO_BOLD));
     ft_item_left.setAlignment(Alignment.LEFT);
     ft_item_left.setVerticalAlignment(VerticalAlignment.CENTRE);
     ft_item_left.setBorder(Border.ALL, BorderLineStyle.THIN);
   } catch (Exception e) {
     log.info(e);
   }
   return ft_item_left;
 }
 /**
  * 单元格右对齐并底色为灰色
  *
  * @return
  */
 public WritableCellFormat getFt_item_rightgray() {
   try {
     ft_item_rightgray = new WritableCellFormat();
     ft_item_rightgray.setFont(new WritableFont(WritableFont.TIMES, 10, WritableFont.NO_BOLD));
     ft_item_rightgray.setAlignment(Alignment.RIGHT);
     ft_item_rightgray.setVerticalAlignment(VerticalAlignment.CENTRE);
     ft_item_rightgray.setBorder(Border.ALL, BorderLineStyle.THIN);
     ft_item_rightgray.setBackground(jxl.format.Colour.GRAY_25);
   } catch (Exception e) {
     log.info(e);
   }
   return ft_item_rightgray;
 }
Exemple #5
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;
    }
  }
  private void generateEmployeePerformanceReport(WritableWorkbook workbook, String key)
      throws Exception {
    if (key.length() > 2) {
      WritableFont headerfont = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD);
      WritableCellFormat headerFormat = new WritableCellFormat(headerfont);
      headerFormat.setAlignment(Alignment.CENTRE);

      WritableFont feemergefont = new WritableFont(WritableFont.ARIAL, 13, WritableFont.BOLD);
      WritableCellFormat feemergeFormat = new WritableCellFormat(feemergefont);
      feemergeFormat.setAlignment(Alignment.CENTRE);
      feemergeFormat.setBackground(Colour.GRAY_25);
      feemergeFormat.setBorder(Border.ALL, BorderLineStyle.THIN);

      WritableFont font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
      WritableCellFormat numbercellformat = new WritableCellFormat(NumberFormats.FLOAT);
      numbercellformat.setFont(font);
      numbercellformat.setBorder(Border.ALL, BorderLineStyle.THIN);

      key = key.substring(1, key.length() - 1);

      WritableSheet sheet = workbook.createSheet("Report For Selected Employees", 0);

      JSONArray employeeTourRecords = getEmployeeTourRecords(key);

      HashMap<Integer, HashMap<Integer, Object[]>> employeeTourDetailsMap =
          new HashMap<Integer, HashMap<Integer, Object[]>>();
      HashMap<Integer, HashMap<Integer, Object[]>> employeeTourRequestAndDetailsMap =
          new HashMap<Integer, HashMap<Integer, Object[]>>();
      putRecordsIntoMap(
          employeeTourDetailsMap, employeeTourRecords, employeeTourRequestAndDetailsMap);
      int row = 5;
      int column = 0;
      sheet.mergeCells(column + 2, row - 4, column + 8, row - 4);

      putHeaderDefault(
          feemergeFormat,
          sheet,
          "Employee Tour Expense Sheet For Selected Employees",
          row - 4,
          column + 2);

      putHeaderDefault(feemergeFormat, sheet, "S. No.", row, column++);
      putHeaderDefault(feemergeFormat, sheet, "Employee Code", row, column++);
      putHeaderDefault(feemergeFormat, sheet, "Employee Name", row, column++);
      putHeaderDefault(feemergeFormat, sheet, "Tour Code", row, column++);
      putHeaderDefault(feemergeFormat, sheet, "Advance", row, column++);
      putHeaderDefault(feemergeFormat, sheet, "City", row, column++);
      putHeaderDefault(feemergeFormat, sheet, "From Date", row, column++);
      putHeaderDefault(feemergeFormat, sheet, "To Date", row, column++);
      putHeaderDefault(feemergeFormat, sheet, "Expected Amount", row, column++);
      putHeaderDefault(feemergeFormat, sheet, "Actual Amount", row, column++);

      int serNo = 0;
      for (Integer employeeId : employeeTourRequestAndDetailsMap.keySet()) {
        HashMap<Integer, Object[]> tourRequestDetailsMap =
            employeeTourRequestAndDetailsMap.get(employeeId);
        for (Integer tourId : tourRequestDetailsMap.keySet()) {
          Object[] details = tourRequestDetailsMap.get(tourId);
          if (details != null) {
            column = 0;
            serNo++;
            row++;
            // int tourId = Translator.integerValue(details[0]);
            Object employeeName = details[1];
            Object employeeCode = details[2];
            Object advance = details[3];
            Object departOn = details[4];
            Object ariveOn = details[5];
            Object tourCode = details[6];
            Object totalEstimatedAmount = details[7];
            Object totalActualAmount = details[8];
            putHeaderDefault(headerFormat, sheet, serNo, row, column++);
            putHeaderDefault(headerFormat, sheet, employeeCode, row, column++);
            putHeaderDefault(headerFormat, sheet, employeeName, row, column++);
            putHeaderDefault(headerFormat, sheet, tourCode, row, column++);
            putHeader(numbercellformat, sheet, Translator.doubleValue(advance), row, column++);
            putHeaderDefault(headerFormat, sheet, "", row, column++);
            putHeaderDefault(headerFormat, sheet, departOn, row, column++);
            putHeaderDefault(headerFormat, sheet, ariveOn, row, column++);
            putHeaderDefault(headerFormat, sheet, "", row, column++);
            putHeaderDefault(headerFormat, sheet, "", row, column++);

            HashMap<Integer, Object[]> tourDetailsMap = employeeTourDetailsMap.get(tourId);
            if (tourDetailsMap != null) {
              for (Integer tourDetailKeys : tourDetailsMap.keySet()) {
                details = tourDetailsMap.get(tourDetailKeys);
                // details[0] = fromDate;
                // details[1] = toDate;
                // details[2] = estmateAmount;
                // details[3] = actualAmount;
                // details[4] = cityName;
                column = 5;
                row++;
                putHeaderDefault(headerFormat, sheet, details[4], row, column++);
                putHeaderDefault(headerFormat, sheet, details[0], row, column++);
                putHeaderDefault(headerFormat, sheet, details[1], row, column++);
                putHeader(
                    numbercellformat, sheet, Translator.doubleValue(details[2]), row, column++);
                putHeader(
                    numbercellformat, sheet, Translator.doubleValue(details[3]), row, column++);
              }
              column = 7;
              row++;
              putHeaderDefault(headerFormat, sheet, "Total", row, column++);
              putHeader(
                  numbercellformat,
                  sheet,
                  Translator.doubleValue(totalEstimatedAmount),
                  row,
                  column++);
              putHeader(
                  numbercellformat,
                  sheet,
                  Translator.doubleValue(totalActualAmount),
                  row,
                  column++);
            }
          }
        }
      }
      workbook.write();
      workbook.close();
    }
  }