コード例 #1
0
  protected void writeCellValue(
      Row row,
      int cellIdx,
      CreationHelper chelp,
      CellStyle cellStyle,
      Object value,
      Column column) {
    TypeCastGraph tcast = getCastGraph();
    //        String str = tcast.cast(value, String.class);

    Cell cell = row.getCell(cellIdx);
    if (cell == null) cell = row.createCell(cellIdx);

    if (value instanceof String) {
      cell.setCellValue(chelp.createRichTextString((String) value));
      cell.setCellType(Cell.CELL_TYPE_STRING);
    } else if (value instanceof java.util.Date) {
      java.util.Date date = (java.util.Date) value;
      String textDate = getDateFormat().format(date);
      cell.setCellValue(chelp.createRichTextString((String) textDate));
    } else if (value instanceof java.util.Calendar) {
      cell.setCellValue((java.util.Calendar) value);
    } else if (value instanceof Boolean) {
      cell.setCellValue((Boolean) value);
      cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
    } else if (value instanceof Double) {
      cell.setCellValue((Double) value);
      cell.setCellType(Cell.CELL_TYPE_NUMERIC);
    } else if (value instanceof Float) {
      cell.setCellValue((Float) value);
      cell.setCellType(Cell.CELL_TYPE_NUMERIC);
    } else {
      String textValue = tcast.cast(value, String.class);
      if (textValue != null) {
        cell.setCellValue(chelp.createRichTextString(textValue));
        cell.setCellType(Cell.CELL_TYPE_STRING);
      }
    }

    if (cellStyle != null) {
      cell.setCellStyle(cellStyle);
    }
  }
コード例 #2
0
  /**
   * Add the title of the columns in the row headers
   *
   * @param sheet
   * @param titles list of titles
   * @param columnHeadersNumber number of column headers
   * @param startColumn first column of the crosstab in the xls
   * @param startRow first row of the crosstab in the xls
   * @param createHelper
   * @throws JSONException
   */
  protected void buildRowHeaderTitle(
      Sheet sheet,
      CrossTab cs,
      int columnHeadersNumber,
      int startColumn,
      int startRow,
      CreationHelper createHelper,
      Locale locale,
      CellStyle cellStyle)
      throws JSONException {
    List<String> titles = cs.getRowHeadersTitles();

    if (titles != null) {

      Row row = sheet.getRow(startRow + columnHeadersNumber);
      for (int i = 0; i < titles.size(); i++) {

        Cell cell = row.createCell(startColumn + i);
        String text = titles.get(i);
        cell.setCellValue(createHelper.createRichTextString(text));
        cell.setCellType(this.getCellTypeString());
        cell.setCellStyle(cellStyle);
      }
      if (cs.isMeasureOnRow()) {
        Cell cell = row.createCell(startColumn + titles.size());
        String text = "Measures";
        if (locale != null) {
          text =
              EngineMessageBundle.getMessage("worksheet.export.crosstab.header.measures", locale);
        }
        cell.setCellValue(createHelper.createRichTextString(text));
        cell.setCellType(this.getCellTypeString());
        cell.setCellStyle(cellStyle);
      }
    }
  }
コード例 #3
0
  /**
   * Builds the rows' headers recursively with this order: |-----|-----|-----| | | | 3 | | | |-----|
   * | | 2 | 4 | | | |-----| | 1 | | 5 | | |-----|-----| | | | 7 | | | 6 |-----| | | | 8 |
   * |-----|-----|-----| | | | 11 | | 9 | 10 |-----| | | | 12 | |-----|-----|-----|
   *
   * @param sheet The sheet of the XLS file
   * @param siblings The siblings nodes of the headers structure
   * @param rowNum The row number where the first sibling must be inserted
   * @param columnNum The column number where the siblings must be inserted
   * @param createHelper The file creation helper
   * @throws JSONException
   */
  protected void buildRowsHeaders(
      Sheet sheet,
      CrossTab cs,
      List<Node> siblings,
      int rowNum,
      int columnNum,
      CreationHelper createHelper,
      Locale locale,
      CellStyle cellStyle)
      throws JSONException {
    int rowsCounter = rowNum;

    for (int i = 0; i < siblings.size(); i++) {
      Node aNode = siblings.get(i);
      List<Node> childs = aNode.getChilds();
      Row row = sheet.getRow(rowsCounter);
      Cell cell = row.createCell(columnNum);
      String text = (String) aNode.getDescription();

      if (cs.isMeasureOnRow() && (childs == null || childs.size() <= 0)) {
        // apply the measure scale factor
        text = MeasureScaleFactorOption.getScaledName(text, cs.getMeasureScaleFactor(text), locale);
      }
      cell.setCellValue(createHelper.createRichTextString(text));
      cell.setCellType(this.getCellTypeString());

      cell.setCellStyle(cellStyle);

      int descendants = aNode.getLeafsNumber();
      if (descendants > 1) {
        sheet.addMergedRegion(
            new CellRangeAddress(
                rowsCounter, // first row (0-based)
                rowsCounter + descendants - 1, // last row  (0-based)
                columnNum, // first column (0-based)
                columnNum // last column  (0-based)
                ));
      }

      if (childs != null && childs.size() > 0) {
        buildRowsHeaders(
            sheet, cs, childs, rowsCounter, columnNum + 1, createHelper, locale, cellStyle);
      }
      int increment = descendants > 1 ? descendants : 1;
      rowsCounter = rowsCounter + increment;
    }
  }
コード例 #4
0
  // <editor-fold defaultstate="collapsed" desc="writeMetaData">
  protected void writeMetaData(
      Row row, int cellStart, CreationHelper createHelper, CellStyle cellStyle, MetaData meta) {
    int cellIdx = cellStart;
    for (Column column : meta.getColumns()) {
      String colName = column.getName();
      if (colName == null) continue;

      Cell cell = row.getCell(cellIdx);
      if (cell == null) {
        cell = row.createCell(cellIdx);
      }

      cell.setCellValue(createHelper.createRichTextString(colName));
      if (cellStyle != null) cell.setCellStyle(cellStyle);

      cellIdx++;
    }
  }
コード例 #5
0
  protected int buildDataMatrix(
      Sheet sheet,
      CrossTab cs,
      int rowOffset,
      int columnOffset,
      CreationHelper createHelper,
      MeasureFormatter measureFormatter)
      throws JSONException {

    CellStyle cellStyleForNA = buildNACellStyle(sheet);

    Map<Integer, CellStyle> decimalFormats = new HashMap<Integer, CellStyle>();
    int endRowNum = 0;
    for (int i = 0; i < cs.getDataMatrix().length; i++) {
      for (int j = 0; j < cs.getDataMatrix()[0].length; j++) {
        String text = (String) cs.getDataMatrix()[i][j];
        int rowNum = rowOffset + i;
        int columnNum = columnOffset + j;
        Row row = sheet.getRow(rowNum);
        if (row == null) {
          row = sheet.createRow(rowNum);
        }
        endRowNum = rowNum;
        Cell cell = row.createCell(columnNum);
        try {
          double value = Double.parseDouble(text);
          int decimals = measureFormatter.getFormatXLS(i, j);
          Double valueFormatted = measureFormatter.applyScaleFactor(value, i, j);
          cell.setCellValue(valueFormatted);
          cell.setCellType(this.getCellTypeNumeric());
          cell.setCellStyle(
              getNumberFormat(decimals, decimalFormats, sheet, createHelper, cs.getCellType(i, j)));
        } catch (NumberFormatException e) {
          logger.debug("Text " + text + " is not recognized as a number");
          cell.setCellValue(createHelper.createRichTextString(text));
          cell.setCellType(this.getCellTypeString());
          cell.setCellStyle(cellStyleForNA);
        }
      }
    }
    return endRowNum;
  }
コード例 #6
0
  /**
   * Builds the columns' headers recursively with this order:
   * |------------------------------------------| | 1 | 9 |
   * |------------------------------------------| | 2 | 5 | 10 |
   * |-----------|-----------------|------------| | 3 | 4 | 6 | 7 | 8 | 11 | 12 |
   * |------------------------------------------|
   *
   * @param sheet The sheet of the XLS file
   * @param siblings The siblings nodes of the headers structure
   * @param rowNum The row number where the siblings must be inserted
   * @param columnNum The column number where the first sibling must be inserted
   * @param createHelper The file creation helper
   * @param dimensionCellStyle The cell style for cells containing dimensions (i.e. attributes'
   *     names)
   * @param memberCellStyle The cell style for cells containing members (i.e. attributes' values)
   * @throws JSONException
   */
  protected void buildColumnsHeader(
      Sheet sheet,
      CrossTab cs,
      List<Node> siblings,
      int rowNum,
      int columnNum,
      CreationHelper createHelper,
      Locale locale,
      CellStyle memberCellStyle,
      CellStyle dimensionCellStyle)
      throws JSONException {
    int columnCounter = columnNum;

    for (int i = 0; i < siblings.size(); i++) {
      Node aNode = (Node) siblings.get(i);
      List<Node> childs = aNode.getChilds();
      Row row = sheet.getRow(rowNum);
      Cell cell = row.createCell(columnCounter);
      String text = (String) aNode.getDescription();
      if (!cs.isMeasureOnRow() && (childs == null || childs.size() <= 0)) {
        // apply the measure scale factor
        text = MeasureScaleFactorOption.getScaledName(text, cs.getMeasureScaleFactor(text), locale);
      }

      cell.setCellValue(createHelper.createRichTextString(text));
      cell.setCellType(this.getCellTypeString());
      int descendants = aNode.getLeafsNumber();
      if (descendants > 1) {
        sheet.addMergedRegion(
            new CellRangeAddress(
                rowNum, // first row (0-based)
                rowNum, // last row  (0-based)
                columnCounter, // first column (0-based)
                columnCounter + descendants - 1 // last column  (0-based)
                ));
      }

      /*
       * Now we have to set the style properly according to the nature of
       * the node: if it contains the name of a dimension or a member.
       * Since the structure foresees that a list of members follows a
       * dimension, we calculate the position of the node with respect to
       * the leaves; in case it is odd, the cell contains a dimension; in
       * case it is even, the cell contains a dimension.
       */
      int distanceToLeaves = aNode.getDistanceFromLeaves();
      if (!cs.isMeasureOnRow()) {
        distanceToLeaves--;
      }
      boolean isDimensionNameCell = distanceToLeaves > 0 && (distanceToLeaves % 2) == 1;
      if (isDimensionNameCell) {
        cell.setCellStyle(dimensionCellStyle);
      } else {
        cell.setCellStyle(memberCellStyle);
      }

      if (childs != null && childs.size() > 0) {
        buildColumnsHeader(
            sheet,
            cs,
            childs,
            rowNum + 1,
            columnCounter,
            createHelper,
            locale,
            memberCellStyle,
            dimensionCellStyle);
      }
      int increment = descendants > 1 ? descendants : 1;
      columnCounter = columnCounter + increment;
    }
  }
コード例 #7
0
ファイル: Test1.java プロジェクト: naily/iph
  @Test
  public void createex() {
    Workbook wb = new HSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillPattern(CellStyle.NO_FILL);

    Sheet sheet = wb.createSheet("数据日志");
    Sheet sheet2 = wb.createSheet("second sheet");
    String safeName =
        WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns " O'Brien's sales   "
    Sheet sheet3 = wb.createSheet(safeName);

    Header header = sheet.getHeader();
    header.setCenter("Center Header");
    header.setLeft("Left Header");
    header.setRight(
        HSSFHeader.font("Stencil-Normal", "Italic")
            + HSSFHeader.fontSize((short) 16)
            + "Right w/ Stencil-Normal Italic font and size 16");

    sheet.setColumnWidth(0, 15 * 256); // .autoSizeColumn(0 ); // 调整第一列宽度
    sheet.setColumnWidth(1, 17 * 256); // 调整第二列宽度
    sheet.setColumnWidth(3, 17 * 256); // 调整第三列宽度
    sheet.autoSizeColumn(2); // 调整第四列宽度

    // Create a row and put some cells in it. Rows are 0 based.
    Row row = sheet.createRow((short) 1);
    // Create a cell and put a value in it.
    Cell cell = row.createCell(0);
    cell.setCellValue("ID");
    cell.setCellStyle(cellStyle);

    // Or do it on one line.
    row.createCell(1).setCellValue("表名");
    row.createCell(2).setCellValue(createHelper.createRichTextString("操作类型"));
    row.createCell(3).setCellValue("操作日期");
    row.createCell(4).setCellValue("操作者");

    List<Log> list = this.logList();
    Row rw;
    for (int i = 2; i < list.size(); i++) {
      Log log = list.get(i);
      rw = sheet.createRow((short) i);

      rw.createCell(0).setCellValue(createHelper.createRichTextString(log.getId()));
      rw.createCell(1).setCellValue(log.getDataTable());
      rw.createCell(2).setCellValue(this.convertActionType(log.getActionType()));
      rw.createCell(3)
          .setCellValue(DateUtil.convertDateToString(log.getLogDate(), DateUtil.pattern2));
      rw.createCell(4).setCellValue(log.getAdminId());
    }

    String bp = this.getClass().getResource("/").toString();
    try {
      System.out.println(bp);
      File f = new File("workbook.xls");
      FileOutputStream fileOut = new FileOutputStream(f);
      wb.write(fileOut);
      fileOut.close();
    } catch (FileNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }
コード例 #8
0
  public File generateXLSResponse(QueryResult queryResult, Locale locale, String baseURL) {
    File excelFile = new File("export_parts.xls");
    // Blank workbook
    XSSFWorkbook workbook = new XSSFWorkbook();

    // Create a blank sheet
    XSSFSheet sheet = workbook.createSheet("Parts Data");

    String header = StringUtils.join(queryResult.getQuery().getSelects(), ";");
    String[] columns = header.split(";");

    Map<Integer, String[]> data = new HashMap<>();
    String[] headerFormatted = createXLSHeaderRow(header, columns, locale);
    data.put(1, headerFormatted);

    Map<Integer, String[]> commentsData = new HashMap<>();
    String[] headerComments = createXLSHeaderRowComments(header, columns);
    commentsData.put(1, headerComments);

    List<String> selects = queryResult.getQuery().getSelects();
    int i = 1;
    for (QueryResultRow row : queryResult.getRows()) {
      i++;
      data.put(i, createXLSRow(selects, row, baseURL));
      commentsData.put(i, createXLSRowComments(selects, row));
    }

    // Iterate over data and write to sheet
    Set<Integer> keyset = data.keySet();
    int rownum = 0;

    for (Integer key : keyset) {

      Row row = sheet.createRow(rownum++);
      String[] objArr = data.get(key);
      int cellnum = 0;
      for (String obj : objArr) {
        Cell cell = row.createCell(cellnum++);
        cell.setCellValue(obj);
      }

      CreationHelper factory = workbook.getCreationHelper();
      Drawing drawing = sheet.createDrawingPatriarch();
      String[] commentsObjArr = commentsData.get(key);
      cellnum = 0;
      for (String commentsObj : commentsObjArr) {
        if (commentsObj.length() > 0) {
          Cell cell = row.getCell(cellnum) != null ? row.getCell(cellnum) : row.createCell(cellnum);

          // When the comment box is visible, have it show in a 1x3 space
          ClientAnchor anchor = factory.createClientAnchor();
          anchor.setCol1(cell.getColumnIndex());
          anchor.setCol2(cell.getColumnIndex() + 1);
          anchor.setRow1(row.getRowNum());
          anchor.setRow2(row.getRowNum() + 1);

          Comment comment = drawing.createCellComment(anchor);
          RichTextString str = factory.createRichTextString(commentsObj);
          comment.setString(str);

          // Assign the comment to the cell
          cell.setCellComment(comment);
        }
        cellnum++;
      }
    }

    // Define header style
    Font headerFont = workbook.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontHeightInPoints((short) 10);
    headerFont.setFontName("Courier New");
    headerFont.setItalic(true);
    headerFont.setColor(IndexedColors.WHITE.getIndex());
    CellStyle headerStyle = workbook.createCellStyle();
    headerStyle.setFont(headerFont);
    headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    // Set header style
    for (int j = 0; j < columns.length; j++) {
      Cell cell = sheet.getRow(0).getCell(j);
      cell.setCellStyle(headerStyle);

      if (cell.getCellComment() != null) {
        String comment = cell.getCellComment().getString().toString();

        if (comment.equals(QueryField.CTX_PRODUCT_ID)
            || comment.equals(QueryField.CTX_SERIAL_NUMBER)
            || comment.equals(QueryField.PART_MASTER_NUMBER)) {
          for (int k = 0; k < queryResult.getRows().size(); k++) {
            Cell grayCell =
                sheet.getRow(k + 1).getCell(j) != null
                    ? sheet.getRow(k + 1).getCell(j)
                    : sheet.getRow(k + 1).createCell(j);
            grayCell.setCellStyle(headerStyle);
          }
        }
      }
    }

    try {
      // Write the workbook in file system
      FileOutputStream out = new FileOutputStream(excelFile);
      workbook.write(out);
      out.close();
    } catch (Exception e) {
      LOGGER.log(Level.FINEST, null, e);
    }
    return excelFile;
  }