private CoverageResults() {
    fileName = "Coverage-results.xls";
    workbook = new HSSFWorkbook();

    allElementsSheet = workbook.createSheet("All Elements");
    packagesSheet = workbook.createSheet("Packages");
    boundClassesSheet = workbook.createSheet("Bound Classes");
    allClassesSheet = workbook.createSheet("All Classes");
    boundMethodsSheet = workbook.createSheet("Bound Methods");
    allMethodsSheet = workbook.createSheet("All Methods");

    normalFont = workbook.createFont();
    normalFont.setFontName(HSSFFont.FONT_ARIAL);
    normalFont.setFontHeightInPoints((short) 10);

    boldFont = workbook.createFont();
    boldFont.setFontName(HSSFFont.FONT_ARIAL);
    boldFont.setFontHeightInPoints((short) 10);
    boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    centerAlignmentCellStyle = workbook.createCellStyle();
    centerAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    leftAlignmentCellStyle = workbook.createCellStyle();
    leftAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);

    rightAlignmentCellStyle = workbook.createCellStyle();
    rightAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

    dataFormatCellStyle = workbook.createCellStyle();
    CreationHelper createHelper = workbook.getCreationHelper();
    dataFormatCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd/MM/yyyy"));
  }
Пример #2
0
  /*
   * Don't hardcode US Locale for datetimes in Excel.  Experiment a bit.
   */
  public static void issue26() throws InvalidFormatException, IOException {

    System.out.println("Testing issue26 =================================");
    Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet("Sheet1");
    Row row = sheet.createRow(0);

    // first cell
    Cell cell0 = row.createCell(0);
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm:ss"));
    cell0.setCellValue(new Date(1385903303326L)); // 12/1/2013 08:08 AM
    cell0.setCellStyle(cellStyle);

    // second cell using another format with French locale
    CellStyle cs2 = wb.createCellStyle();
    String excelFormatPrefix = DateFormatConverter.getPrefixForLocale(Locale.FRENCH);
    System.out.println("The LOCALE prefix is: " + excelFormatPrefix);
    String excelFormatPattern = excelFormatPrefix + "dd MMM, yyyy;@";
    System.out.println(
        "Converted pattern in FRENCH locale is: "
            + DateFormatConverter.convert(Locale.FRENCH, "m/d/yy h:mm:ss"));

    DataFormat df = wb.createDataFormat();
    cs2.setDataFormat(df.getFormat(excelFormatPattern));
    Cell cell1 = row.createCell(1);
    cell1.setCellValue(new Date(1385903303326L));
    cell1.setCellStyle(cs2);

    FileOutputStream out = new FileOutputStream("/tmp/issue26_out.xlsx");
    wb.write(out);
    out.close();
    System.out.println("Wrote /tmp/issue26_out.xlsx");
  }
  @Override
  public void createCellStyles(Workbook wb) {
    if (wb == null) {
      return;
    }
    CreationHelper createHelper = wb.getCreationHelper();

    // Headers
    CellStyle headerStyle = wb.createCellStyle();
    headerStyle.setFillBackgroundColor(IndexedColors.BLACK.getIndex());
    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    Font font = wb.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.WHITE.index);
    headerStyle.setFont(font);

    cellStyleMap.put(STYLE_HELP_HEADER, headerStyle);
    cellStyleMap.put(STYLE_RECORD_HEADER, headerStyle);
    cellStyleMap.put(STYLE_LOCATION_HEADER, headerStyle);
    cellStyleMap.put(STYLE_TAXONOMY_HEADER, headerStyle);

    // Date and Time
    CellStyle dateStyle = wb.createCellStyle();
    dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("D MMM YYYY"));
    cellStyleMap.put(STYLE_DATE_CELL, dateStyle);

    CellStyle timeStyle = wb.createCellStyle();
    timeStyle.setDataFormat(createHelper.createDataFormat().getFormat("HH:MM"));
    cellStyleMap.put(STYLE_TIME_CELL, timeStyle);
  }
Пример #4
0
 private CellStyle getDateFormatStyle(HSSFCell poiCell) {
   final HSSFWorkbook poiWorkbook = poiCell.getRow().getSheet().getWorkbook();
   CellStyle dateFormatStyle = poiWorkbook.createCellStyle();
   CreationHelper createHelper = poiWorkbook.getCreationHelper();
   dateFormatStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy/mm/dd hh:MM:ss"));
   dateFormatStyle.setAlignment(CellStyle.ALIGN_LEFT);
   return dateFormatStyle;
 }
Пример #5
0
  @Override
  public void setDateTimePattern(String dateTimePattern) {

    this.dateTimePattern = dateTimePattern;
    DateTimeConverter dtConverter = (DateTimeConverter) converters.lookup(Date.class);
    dtConverter.setDateTimePattern(dateTimePattern);
    dateCellStyle = workbook.createCellStyle();
    CreationHelper helper = workbook.getCreationHelper();
    dateCellStyle.setDataFormat(helper.createDataFormat().getFormat(dateTimePattern));
  }
Пример #6
0
  private void makeTemporalCell(Cell retVal, Date cellObj, String format) {
    CreationHelper creationHelper = workbook.getCreationHelper();
    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat(format));
    retVal.setCellStyle(cellStyle);

    if (null != cellObj) {
      Calendar calendar = Calendar.getInstance();
      calendar.setTime((Date) cellObj);
      retVal.setCellValue(calendar);
    }
  }
Пример #7
0
 public void outputIntAdapter(DataBean dataBean, Object fieldValue, String fieldName, Cell cell)
     throws AdapterException {
   log.debug(
       "in DefaultOutputAdapter:outputIntAdapter fieldName:{} fieldValue:{}",
       fieldName,
       fieldValue);
   if (ObjectHelper.isNullOrEmptyString(fieldValue)) return;
   Workbook workbook = cell.getSheet().getWorkbook();
   CellStyle cellStyle = workbook.createCellStyle();
   CreationHelper createHelper = workbook.getCreationHelper();
   cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#"));
   cell.setCellValue(NumberUtils.format(fieldValue, 0));
   cell.setCellStyle(cellStyle);
 }
Пример #8
0
 /**
  * ハイパーリンクの設定
  *
  * @param sheet シート
  * @param nRow 対象行番号
  * @param nColumn 対象列番号
  * @param value ハイパーリンクテキスト
  * @param url ハイパーリンク先URL
  */
 public static void setHyperLink(
     XSSFSheet sheet, int nRow, int nColumn, String value, String url) {
   assert sheet != null;
   XSSFWorkbook workbook = sheet.getWorkbook();
   CreationHelper helper = workbook.getCreationHelper();
   Hyperlink hyperlink = helper.createHyperlink(Hyperlink.LINK_URL);
   hyperlink.setAddress(url);
   XSSFRow row = getRowAnyway(sheet, nRow);
   XSSFCell cell = getCellAnyway(row, nColumn);
   cell.setCellValue(value);
   cell.setHyperlink(hyperlink);
   // ハイパーリンクテキストの装飾
   XSSFFont font = workbook.createFont();
   XSSFCellStyle style = workbook.createCellStyle();
   // font.setColor(new XSSFColor(new Color(0, 0, 255)));
   font.setUnderline(XSSFFont.U_SINGLE);
   style.setFont(font);
   cell.setCellStyle(style);
 }
Пример #9
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);
    }
  }
Пример #10
0
 public void outputNumericAdapter(
     DataBean dataBean, Object fieldValue, String fieldName, Cell cell) throws AdapterException {
   log.debug(
       "in DefaultOutputAdapter:outputNumericAdapter fieldName:{} fieldValue:{}",
       fieldName,
       fieldValue);
   if (ObjectHelper.isNullOrEmptyString(fieldValue)) return;
   OutputNumericConfig config = dataBean.getOutputConfig(fieldName);
   Workbook workbook = cell.getSheet().getWorkbook();
   CellStyle cellStyle = workbook.createCellStyle();
   CreationHelper createHelper = workbook.getCreationHelper();
   StringBuilder format = new StringBuilder("0");
   for (int i = 0; i < config.floatCount(); i++) {
     if (i == 0) format.append(".");
     format.append("0");
   }
   cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(format.toString()));
   cell.setCellValue(NumberUtils.format(fieldValue, config.floatCount()));
   cell.setCellStyle(cellStyle);
 }
  @Override
  public void renderCell(Element element, int rowIndex, int columnIndex) {
    Cell cell = sheet.getRow(rowIndex).createCell(columnIndex);

    Double numericValue;

    if (isDateCell(element)) {
      DateFormat df = new SimpleDateFormat(getDateCellFormat(element));

      try {
        cell.setCellValue(df.parse(getElementText(element)));
      } catch (ParseException pex) {
        System.out.println("Invalid Usage");
      }
    } else if ((numericValue = getNumericValue(element)) != null) {
      cell.setCellValue(numericValue);
    } else {
      cell = sheet.getRow(rowIndex).createCell(columnIndex, Cell.CELL_TYPE_STRING);
      cell.setCellValue(getElementText(element));
    }

    Style style = styleMapper.getStyleForElement(element);
    cell.setCellStyle(styleGenerator.getStyle(cell, style));

    if (isDateCell(element)) {
      CreationHelper createHelper = sheet.getWorkbook().getCreationHelper();
      cell.getCellStyle()
          .setDataFormat(createHelper.createDataFormat().getFormat(getDateCellFormat(element)));
    }

    String commentText;

    if ((commentText = getCellCommentText(element)) != null) {
      ExcelCellCommentGenerator.addCellComment(cell, commentText, getCellCommentDimension(element));
    }

    if (definesFreezePane(element)) {
      sheet.createFreezePane(columnIndex, rowIndex);
    }
  }
Пример #12
0
  /**
   * Creates a new instance.
   *
   * @param workbook target workbook
   * @throws IllegalArgumentException if some parameters were {@code null}
   */
  WorkbookInfo(Workbook workbook) {
    if (workbook == null) {
      throw new IllegalArgumentException("workbook must not be null"); // $NON-NLS-1$
    }
    this.workbook = workbook;

    Font font = workbook.createFont();

    commonStyle = workbook.createCellStyle();
    commonStyle.setFont(font);
    commonStyle.setBorderTop(BorderStyle.THIN);
    commonStyle.setBorderBottom(BorderStyle.THIN);
    commonStyle.setBorderLeft(BorderStyle.THIN);
    commonStyle.setBorderRight(BorderStyle.THIN);

    titleStyle = workbook.createCellStyle();
    titleStyle.cloneStyleFrom(commonStyle);
    titleStyle.setLocked(true);
    titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    titleStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
    titleStyle.setAlignment(HorizontalAlignment.CENTER);

    CreationHelper helper = workbook.getCreationHelper();
    DataFormat df = helper.createDataFormat();

    dataStyle = workbook.createCellStyle();
    dataStyle.cloneStyleFrom(commonStyle);

    dateDataStyle = workbook.createCellStyle();
    dateDataStyle.cloneStyleFrom(commonStyle);
    dateDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd")); // $NON-NLS-1$

    timeDataStyle = workbook.createCellStyle();
    timeDataStyle.cloneStyleFrom(commonStyle);
    timeDataStyle.setDataFormat(df.getFormat("hh:mm:ss")); // $NON-NLS-1$

    datetimeDataStyle = workbook.createCellStyle();
    datetimeDataStyle.cloneStyleFrom(commonStyle);
    datetimeDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd hh:mm:ss")); // $NON-NLS-1$
  }
  public CellStyle getNumberFormat(
      int j,
      Map<Integer, CellStyle> decimalFormats,
      Sheet sheet,
      CreationHelper createHelper,
      CellType celltype) {

    int mapPosition = j;

    if (celltype.equals(CellType.TOTAL)) {
      mapPosition = j + 90000;
    } else if (celltype.equals(CellType.SUBTOTAL)) {
      mapPosition = j + 80000;
    } else if (celltype.equals(CellType.CF)) {
      mapPosition = j + 60000;
    }

    if (decimalFormats.get(mapPosition) != null) return decimalFormats.get(mapPosition);

    if (celltype.equals(CellType.CF)) {
      j = this.getCalculatedFieldDecimals();
    }

    String decimals = "";

    for (int i = 0; i < j; i++) {
      decimals += "0";
    }

    CellStyle cellStyle = this.buildDataCellStyle(sheet);
    DataFormat df = createHelper.createDataFormat();
    String format = "#,##0";
    if (decimals.length() > 0) {
      format += "." + decimals;
    }
    cellStyle.setDataFormat(df.getFormat(format));

    if (celltype.equals(CellType.TOTAL)) {
      cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
    }
    if (celltype.equals(CellType.CF)) {
      cellStyle.setFillForegroundColor(IndexedColors.DARK_YELLOW.getIndex());
    }
    if (celltype.equals(CellType.SUBTOTAL)) {
      cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    }

    decimalFormats.put(mapPosition, cellStyle);
    return cellStyle;
  }
Пример #14
0
 /**
  * 导出时间适配器
  *
  * @param fieldValue
  * @param fieldName
  * @return
  * @throws AdapterException
  */
 public void outputDateAdapter(DataBean dataBean, Object fieldValue, String fieldName, Cell cell)
     throws AdapterException {
   log.debug(
       "in DefaultOutputAdapter:outputDateAdapter fieldName:{} fieldValue:{}",
       fieldName,
       fieldValue);
   Date date = null;
   if (fieldValue == null) {
     log.debug("fieldValue is null return");
     cell.setCellValue("");
     return;
   } else if (fieldValue instanceof Date) {
     log.debug("fieldValue instanceof Date ");
     date = (Date) fieldValue;
   } else if (fieldValue instanceof String) {
     log.debug("fieldValue instanceof String ");
     InputDateConfig config = dataBean.getInputConfig(fieldName);
     try {
       date = DateUtil.formatToDate((String) fieldValue, config.format());
     } catch (ParseException e) {
       throw new AdapterException(fieldName, Message.DATE_TYPE_ERROR, cell);
     }
   } else if (fieldValue instanceof Long) {
     log.debug("fieldValue instanceof Long ");
     date = new Date((Long) fieldValue);
   } else {
     throw new AdapterException(fieldName, Message.DATE_TYPE_ERROR, cell);
   }
   Workbook workbook = cell.getSheet().getWorkbook();
   OutputDateConfig outputConfig = dataBean.getOutputConfig(fieldName);
   CellStyle cellStyle = cell.getCellStyle();
   if (cellStyle == null) cellStyle = workbook.createCellStyle();
   CreationHelper createHelper = workbook.getCreationHelper();
   cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(outputConfig.format()));
   cell.setCellStyle(cellStyle);
   cell.setCellValue(date);
 }
  /**
   * 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);
      }
    }
  }
  /**
   * 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;
    }
  }
Пример #17
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++;
    }
  }
  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;
  }
  /**
   * 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;
    }
  }
Пример #20
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;
  }
  public void process2HTTP(
      ExportModel expModel, UISimpleViewExport uis, HttpServletResponse hsr, DateTimeHelper dth) {
    try {
      String strFileName = uis.getDownloadFileName();

      Workbook wbCurrent = null;
      if (strFileName.toLowerCase().endsWith(".xlsx")) {
        wbCurrent = new XSSFWorkbook();
      } else {
        wbCurrent = new HSSFWorkbook();
      }
      HashMap<String, CellStyle> hsCS = new HashMap<String, CellStyle>();
      CreationHelper cr = wbCurrent.getCreationHelper();
      CellStyle csDate = wbCurrent.createCellStyle();
      csDate.setDataFormat(cr.createDataFormat().getFormat(dth.getDFDate().toPattern()));

      CellStyle csDateTime = wbCurrent.createCellStyle();
      csDateTime.setDataFormat(cr.createDataFormat().getFormat(dth.getDFDateTime().toPattern()));

      CellStyle csTime = wbCurrent.createCellStyle();
      csTime.setDataFormat(cr.createDataFormat().getFormat(dth.getDFTime().toPattern()));

      hsCS.put("DATE", csDate);
      hsCS.put("TIME", csTime);
      hsCS.put("DATETIME", csDateTime);

      Sheet sh = wbCurrent.createSheet("SVE Export");
      int nRowCount = 0;

      // BUILDING HEADER
      if (uis.isIncludeHeader()) {
        Row rw = sh.createRow(nRowCount);
        int nCol = 0;
        for (ExportColumn expColumn : expModel.getColumns()) {
          rw.createCell(nCol).setCellValue(expColumn.getColumnName());
          nCol++;
        }
        nRowCount++;
      }
      // Processing Values
      for (ExportDataRow expRow : expModel.getRows()) {
        Row rw = sh.createRow(nRowCount);
        int nCol = 0;
        for (ExportColumn expColumn : expModel.getColumns()) {
          Cell clCurrent = rw.createCell(nCol);
          setCellValue(expRow.getValue(expColumn.getPosition()), clCurrent, expColumn, hsCS);
          nCol++;
        }
        nRowCount++;
      }
      for (int nCol = 0; nCol < expModel.getColumns().size(); nCol++) {
        sh.autoSizeColumn(nCol);
      }
      if (strFileName.toLowerCase().endsWith(".xlsx")) {
        hsr.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
      } else if (strFileName.toLowerCase().endsWith("xls")) {
        hsr.setContentType("application/vnd.ms-excel");

      } else {
        hsr.setContentType("application/octet-stream");
      }
      hsr.addHeader("Content-disposition", "inline; filename=\"" + strFileName + "\"");
      OutputStream os = hsr.getOutputStream();
      ByteArrayOutputStream bos = new ByteArrayOutputStream();
      wbCurrent.write(bos);
      bos.writeTo(os);
      os.close();
    } catch (Exception e) {
      ErrorPageBuilder.getInstance()
          .processError(hsr, "Error during SVE-Generation (Workbook Export)", e);
    }
  }
Пример #22
0
  @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();
    }
  }