Java 类org.apache.poi.ss.usermodel.CreationHelper 实例源码

private void formatCellDate(Sheet sheet, Cell cell, String format) {
    CellStyle style = wb.createCellStyle();
    CreationHelper createHelper = wb.getCreationHelper();
    style.setDataFormat(createHelper.createDataFormat().getFormat(format));
    cell.setCellStyle(style);

}

/**
 * @작성자 : KYJ
 * @작성일 : 2016. 9. 9. 
 * @param sheet
 * @throws Exception
 */
final static void createDefaultLogo(Sheet sheet) throws Exception {
    Workbook workbook = sheet.getWorkbook();
    byte[] defaultLogoImage = getDefaultLogoImage();
    if(defaultLogoImage == null)
        return;
    int pictureIdx = workbook.addPicture(defaultLogoImage, Workbook.PICTURE_TYPE_PNG);

    CreationHelper creationHelper = workbook.getCreationHelper();
    ClientAnchor anchor = creationHelper.createClientAnchor(); //new XSSFClientAnchor();
    //          anchor.setAnchorType(AnchorType.MOVE_DONT_RESIZE);
    Drawing createDrawingPatriarch = sheet.createDrawingPatriarch();
    anchor.setDx1(0);
    anchor.setCol1(0);
    anchor.setRow1(0);

    //#1 테이블 셀의 너비에 의존적이지않게 사이즈조절.
    anchor.setAnchorType(AnchorType.MOVE_DONT_RESIZE);
    Picture createPicture = createDrawingPatriarch.createPicture(anchor, pictureIdx);
    //#2 테이블 셀의 너비에 의존적이지않게 사이즈조절.
    createPicture.resize();
}

/**
 * See the comment for the given cell
 * 
 * @param cell
 *            the cell
 * @param message
 *            the comment message
 */
public static void setComment(HSSFCell cell, String message) {
    Drawing drawing = cell.getSheet().createDrawingPatriarch();
    CreationHelper factory = cell.getSheet().getWorkbook().getCreationHelper();

    // 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(cell.getRowIndex());
    anchor.setRow2(cell.getRowIndex() + 1);
    anchor.setDx1(100);
    anchor.setDx2(1000);
    anchor.setDy1(100);
    anchor.setDy2(1000);

    // Create the comment and set the text+author
    Comment comment = drawing.createCellComment(anchor);
    RichTextString str = factory.createRichTextString(message);
    comment.setString(str);
    comment.setAuthor("TURNUS");
    // Assign the comment to the cell
    cell.setCellComment(comment);
}

/**
 * could be interesting for spring batch
 *
 * @param inputResource source of the data read by lines
 * @param outputResource generated .xlsx resource
 */
public void process(Resource inputResource, Resource outputResource) {
    SXSSFWorkbook wb = new SXSSFWorkbook(flushSize);
    try {
        wb.setCompressTempFiles(true); // temp files will be gzipped
        final Sheet sh = wb.createSheet();

        InputStreamReader is = new InputStreamReader(inputResource.getInputStream());
        BufferedReader br = new BufferedReader(is);
        final CreationHelper createHelper = sh.getWorkbook().getCreationHelper();

        br.lines().forEachOrdered(string -> createRow(string, sh, createHelper));

        FileOutputStream out = new FileOutputStream(outputResource.getFile());

        wb.write(out);
        IOUtils.closeQuietly(out);
    } catch (IOException e) {
       logger.error("IOE", e);
    } finally {
        if (wb != null) {
            // dispose of temporary files backing this workbook on disk
            wb.dispose();
        }
    }
}

private static void fillFirstRow(Sheet currSheet, Map<String, Object> attributes, CreationHelper crHelper) {
    Row firstRow = currSheet.createRow(0);

    int idx = 0;
    firstRow.createCell(idx++).setCellValue("Value");
    firstRow.createCell(idx++).setCellValue("Context");
    firstRow.createCell(idx++).setCellValue("Doc");
    firstRow.createCell(idx++).setCellValue("Page");
    firstRow.createCell(idx++).setCellValue("Region");
    firstRow.createCell(idx++).setCellValue("Line");
    firstRow.createCell(idx++).setCellValue("Word");

    Iterator<String> attributeIterator = attributes.keySet().iterator();
    for (int i = 0; i < attributes.size(); i++){
        String attributeName = attributeIterator.next();
        //logger.debug("attributeName " + attributeName);
        firstRow.createCell(i+idx).setCellValue(crHelper.createRichTextString(attributeName));  
    }

}

/**
 * Set a link to a cell. The link type should one of {@link Hyperlink}
 * 
 * @param wb
 *            the workbook which contains the cell
 * @param cell
 *            the cell where the link is stored
 * @param address
 *            the cell destination address
 * @param linkType
 *            the type selected among {@link Hyperlink}
 */
public static void setLink(Workbook wb, HSSFCell cell, String address, int linkType) {
    CreationHelper helper = wb.getCreationHelper();
    CellStyle style = wb.createCellStyle();
    Font font = wb.createFont();
    font.setUnderline(Font.U_SINGLE);
    font.setColor(IndexedColors.BLUE.getIndex());
    style.setFont(font);

    Hyperlink link = helper.createHyperlink(linkType);
    link.setAddress(address);
    cell.setHyperlink(link);
    cell.setCellStyle(style);
}

protected Cell getCell(Column column) {
    Cell cell = CellUtil.getCell(getRow(), column.getIndex());

    ColumnOption option = getColumnOption(column);
    if (option != null) {
        Optional<String> formatOption = option.getDataFormat();
        if (formatOption.isPresent()) {
            String formatString = formatOption.get();
            CellStyle style = styleMap.get(formatString);
            if (style == null) {
                Workbook book = sheet.getWorkbook();
                style = book.createCellStyle();
                CreationHelper helper = book.getCreationHelper();
                short fmt = helper.createDataFormat().getFormat(formatString);
                style.setDataFormat(fmt);
                styleMap.put(formatString, style);
            }
            cell.setCellStyle(style);
        }
    }

    return cell;
}

/**
 * セルにハイパーリンクを設定する。
 * 
 * @param cell セル
 * @param type リンクタイプ
 * @param address ハイパーリンクアドレス
 * @see org.apache.poi.common.usermodel.Hyperlink
 */
public static void setHyperlink( Cell cell, HyperlinkType hyperlinkType, String address) {

    Workbook wb = cell.getRow().getSheet().getWorkbook();

    CreationHelper createHelper = wb.getCreationHelper();

    Hyperlink link = createHelper.createHyperlink( hyperlinkType);
    if ( link instanceof HSSFHyperlink) {
        (( HSSFHyperlink) link).setTextMark( address);
    } else if ( link instanceof XSSFHyperlink) {
        (( XSSFHyperlink) link).setAddress( address);
    }

    cell.setHyperlink( link);
}

public String getFormula2(Point point, Cell cell) {

    if(Utils.equals(comment, "空文字")) {
        return null;

    }

    // ダミーでリンクも設定する
    final CreationHelper helper = cell.getSheet().getWorkbook().getCreationHelper();
    final Hyperlink link = helper.createHyperlink(Hyperlink.LINK_URL);
    link.setAddress(comment);
    cell.setHyperlink(link);

    final int rowNumber = point.y + 1;
    return String.format("HYPERLINK(D%s,\"リンク\"&A%s)", rowNumber, rowNumber);
}

private Comment createCellComment(String author, String comment) {

        // comments only supported for XLSX
        if (data.sheet instanceof XSSFSheet) {
            CreationHelper factory = data.wb.getCreationHelper();
            Drawing drawing = data.sheet.createDrawingPatriarch();

            ClientAnchor anchor = factory.createClientAnchor();
            Comment cmt = drawing.createCellComment(anchor);
            RichTextString str = factory.createRichTextString(comment);
            cmt.setString(str);
            cmt.setAuthor(author);
            return cmt;

        }
        return null;

    }

/**
 * <p>Place the Hyperlink in the Cell, which replaces any other value left
 * behind in the Cell.</p>
 * @return Whether the first <code>Cell</code> in the <code>Block</code>
 *    associated with this <code>Tag</code> was processed.
 */
public boolean process()
{
   TagContext context = getContext();
   Sheet sheet = context.getSheet();
   Block block = context.getBlock();
   int left = block.getLeftColNum();
   int top = block.getTopRowNum();
   // It should exist in this Cell; this Tag was found in it.
   Row row = sheet.getRow(top);
   Cell cell = row.getCell(left);
   SheetUtil.setCellValue(cell, myValue);

   CreationHelper helper = sheet.getWorkbook().getCreationHelper();
   Hyperlink hyperlink = helper.createHyperlink(myLinkType);
   hyperlink.setAddress(myAddress);
   cell.setHyperlink(hyperlink);

   BlockTransformer transformer = new BlockTransformer();
   transformer.transform(context, getWorkbookContext());

   return true;
}

/**
 * Find any <code>Expressions</code> embedded in the given string, evaluate
 * them, and replace the expressions with the resulting values.  If the
 * entire string consists of one <code>Expression</code>, then the returned
 * value may be any <code>Object</code>.
 *
 * @param richTextString The rich text string, with possibly embedded
 * expressions.
 * @param helper A <code>CreationHelper</code> that can create the proper
 *    <code>RichTextString</code>.
 * @param beans A <code>Map</code> mapping strings to objects.
 * @return A new string, with any embedded expressions replaced with the
 *    expression string values.
 */
public static Object evaluateString(RichTextString richTextString,
   CreationHelper helper, Map<String, Object> beans)
{
   String value = richTextString.getString();
   List<Expression> expressions = getExpressions(value);
   if (value.startsWith(Expression.BEGIN_EXPR) && value.endsWith(Expression.END_EXPR) && expressions.size() == 1)
   {
      Expression expression = new Expression(value.substring(2, value.length() - 1));
      Object result = expression.evaluate(beans);
      if (result instanceof String)
      {
         return RichTextStringUtil.replaceAll(richTextString, helper, value, (String) result, true);
      }
      else
      {
         return result;
      }
   }
   else
   {
      return replaceExpressions(richTextString, helper, expressions, beans);
   }
}

/**
 * Replace all expressions with their evaluated results.  This attempts to
 * preserve any formatting within the <code>RichTextString</code>.
 * @param richTextString The entire string, with possibly many expressions
 *    and possibly embedded formatting.
 * @param helper A <code>CreationHelper</code> that can create the proper
 *    <code>RichTextString</code>.
 * @param expressions A <code>List</code> of <code>Expressions</code>.
 * @param beans A <code>Map</code> of beans to provide context for the
 *    <code>Expressions</code>.
 * @return A <code>RichTextString</code> with all expressions replaced with
 *    their evaluated results, and formatted preserved as best as possible.
 */
private static RichTextString replaceExpressions(RichTextString richTextString,
   CreationHelper helper, List<Expression> expressions, Map<String, Object> beans)
{
   ArrayList<String> exprStrings = new ArrayList<String>(expressions.size());
   ArrayList<String> exprValues = new ArrayList<String>(expressions.size());
   for (Expression expr : expressions)
   {
      exprStrings.add(BEGIN_EXPR + expr.myExpression + END_EXPR);
      Object result = expr.evaluate(beans);
      if (result != null)
         exprValues.add(result.toString());
      else
         exprValues.add("");
   }
   return RichTextStringUtil.replaceValues(richTextString, helper, exprStrings, exprValues);
}

private Comment createCellComment(String author, String comment) {

        // comments only supported for XLSX
        if (data.sheet instanceof XSSFSheet) {
            CreationHelper factory = data.wb.getCreationHelper();
            Drawing drawing = data.sheet.createDrawingPatriarch();

            ClientAnchor anchor = factory.createClientAnchor();
            Comment cmt = drawing.createCellComment(anchor);
            RichTextString str = factory.createRichTextString(comment);
            cmt.setString(str);
            cmt.setAuthor(author);
            return cmt;

        }
        return null;

    }

private Comment createCellComment(String author, String comment) {

        // comments only supported for XLSX
        if (data.sheet instanceof XSSFSheet) {
            CreationHelper factory = data.wb.getCreationHelper();
            Drawing drawing = data.sheet.createDrawingPatriarch();

            ClientAnchor anchor = factory.createClientAnchor();
            Comment cmt = drawing.createCellComment(anchor);
            RichTextString str = factory.createRichTextString(comment);
            cmt.setString(str);
            cmt.setAuthor(author);
            return cmt;

        }
        return null;

    }

public synchronized Workbook getWorkbook()
{
  if (this.workbook == null)
  {
    this.workbook = new SXSSFWorkbook(10);
    this.style = this.workbook.createCellStyle();

    CreationHelper helper = this.workbook.getCreationHelper();
    this.style.setDataFormat(helper.createDataFormat().getFormat("MM/DD/YY"));

    XSSFWorkbook wb = this.workbook.getXSSFWorkbook();
    POIXMLProperties props = wb.getProperties();

    POIXMLProperties.CustomProperties custProp = props.getCustomProperties();
    custProp.addProperty("dataset", this.context.getId(this.sheetName));
  }

  return this.workbook;
}

private Comment createCellComment( String author, String comment ) {

    // comments only supported for XLSX
    if ( data.sheet instanceof XSSFSheet ) {
      CreationHelper factory = data.wb.getCreationHelper();
      Drawing drawing = data.sheet.createDrawingPatriarch();

      ClientAnchor anchor = factory.createClientAnchor();
      Comment cmt = drawing.createCellComment( anchor );
      RichTextString str = factory.createRichTextString( comment );
      cmt.setString( str );
      cmt.setAuthor( author );
      return cmt;

    }
    return null;

  }

/**
 * 抽象出图片生成业务代码
 * 
 * @throws IOException
 */
private void extractPicturePortion(String svgString, XSSFWorkbook wb,
        XSSFSheet sheet, int startCol, int endCol, int startRow, int endRow)
        throws IOException {
    // 图片
    if (org.apache.commons.lang3.StringUtils.isNotBlank(svgString)) {
        byte[] safeDataBytes = new BASE64Decoder().decodeBuffer(svgString);
        int pictureIdx = wb.addPicture(safeDataBytes,
                Workbook.PICTURE_TYPE_JPEG);
        CreationHelper helper = wb.getCreationHelper();
        // Create the drawing patriarch. This is the top level container for
        // all shapes.
        Drawing drawing = sheet.createDrawingPatriarch();
        // add a picture shape
        ClientAnchor anchor = helper.createClientAnchor();
        // set top-left corner of the picture,
        // subsequent call of Picture#resize() will operate relative to it
        anchor.setCol1(startCol);
        anchor.setCol2(endCol);
        anchor.setRow1(startRow);
        anchor.setRow2(endRow);
        anchor.setDx1(0);
        anchor.setDy1(0);
        anchor.setDx2(0);
        anchor.setDy2(0);
        anchor.setAnchorType(ClientAnchor.MOVE_DONT_RESIZE);
        Picture pict = drawing.createPicture(anchor, pictureIdx);
        pict.resize(1);
    }
}

/** 设置超链接属性
 * @param cell
 * @param processor
 * @param returnVal
 * @param current
 */
private static void setHyperlink(Cell cell, LinkProcessor processor, Object returnVal, Object current) {
    int linkType = 0 ;      
    String prefix = "";
    switch (processor.getLinkType()) {
    case Url:
        linkType = Hyperlink.LINK_URL;
        prefix = "http";
        break;
    case Document:
        linkType = Hyperlink.LINK_DOCUMENT;
        break;
    case Email:
        linkType = Hyperlink.LINK_EMAIL;
        prefix = "mailto:";
        break;
    case File:
        linkType = Hyperlink.LINK_FILE;
        break;
    }
    CreationHelper creationHelper = cell.getSheet().getWorkbook().getCreationHelper();
    org.apache.poi.ss.usermodel.Hyperlink hyperlink = creationHelper.createHyperlink(linkType);
    String address = processor.getLinkAddress(returnVal, current);
    if(!address.startsWith(prefix)){
        if(linkType == Hyperlink.LINK_EMAIL){
            address = prefix +  address;                
        }else{
            address = "http://" + address;
        }
    }
    hyperlink.setAddress(address);
    cell.setHyperlink(hyperlink);
}

public static void evaluateFormulas(String fileName) throws IOException {
  xLogger.fine("Entering evaluateFormulas. fileName: {0}", fileName);
  // Create a InoutStream from the bytes in the cloud storage.
  // Create a template workbook
  // Evaluate the formulas
  // Save the workbook.
  if (fileName == null || fileName.isEmpty()) {
    xLogger.severe("Cannot evaluate formulas in a null or empty file");
    return;
  }

  InputStream is = null;
  OutputStream os = null;
  // Create a workbook from the bytes
  try {
    // Get the template bytes from GCS ( Note: By now the data has been added to the appropriate sheet/s)
    is = _storageUtil.getInputStream(CustomReportsExportMgr.CUSTOMREPORTS_BUCKETNAME, fileName);
    if (is == null) {
      xLogger.severe("Failed to create Input stream for {0}", fileName);
      return;
    }
    Workbook
        templateWb =
        WorkbookFactory.create(
            is); // From the bytes downloaded from the google cloud storage, form the Workbook
    if (templateWb != null) {
      CreationHelper createHelper = templateWb.getCreationHelper();
      xLogger.fine("Created createHelper. {0}", createHelper);
      if (createHelper != null) {
        FormulaEvaluator evaluator = createHelper.createFormulaEvaluator();
        xLogger.fine("Created evaluator. {0}", evaluator);
        if (evaluator != null) {
          evaluator.evaluateAll();
          xLogger.fine("After evaluator.evaluateAll");
          templateWb.setForceFormulaRecalculation(
              true); // Added this line because some formula cells were not getting updated even after calling evaluateAll
          xLogger.fine("After templateWb.setForceFormulaRecalculation");
          // Write to file
          xLogger.fine("Now creating baos");
          os =
              _storageUtil
                  .getOutputStream(CustomReportsExportMgr.CUSTOMREPORTS_BUCKETNAME, fileName,
                      false);
          xLogger.fine("os: {0}", os);
          templateWb.write(os); // Write the workbook to OutputStream
          xLogger.fine("Wrote templateWb to baos");
        } // end if evaluator != null
      } // end if createHelper != null
    } // end if templateWb != null
  } catch (Exception e) {
    xLogger.severe("{0} while evaluating formulas in the file {1}. Message: {2}",
        e.getClass().getName(), fileName, e.getMessage(), e);
  } finally {
    if (is != null) {
      is.close();
    }
    if (os != null) {
      os.close();
    }
  }
  xLogger.fine("Exiting evaluateFormulas");
}

ExcelTestHelper(final String parent, boolean generateXls) throws Exception {
  this.xls = generateXls;

  // Create a test Excel sheet with all types of supported data
  Workbook wb = generateXls ? new HSSFWorkbook() : new XSSFWorkbook();

  CreationHelper creationHelper = wb.getCreationHelper();
  DataFormat dataFormat = creationHelper.createDataFormat();
  short fmt = dataFormat.getFormat("yyyy-mm-dd hh:mm:ss");
  CellStyle style = wb.createCellStyle();
  style.setDataFormat(fmt);

  Sheet sheetWithHeader = wb.createSheet("Sheet 1");

  // Create header row
  Row headerRow = sheetWithHeader.createRow((short) 0);
  headerRow.createCell(0).setCellValue("Number");
  headerRow.createCell(1).setCellValue("String1");
  headerRow.createCell(2).setCellValue("String2");
  headerRow.createCell(3).setCellValue("MyTime");
  headerRow.createCell(4).setCellValue("Formula");
  headerRow.createCell(5).setCellValue("Boolean");
  headerRow.createCell(6).setCellValue("Error");
  generateSheetData(sheetWithHeader, style, (short)1);

  Sheet sheetWithoutHeader = wb.createSheet("Sheet 2");
  generateSheetData(sheetWithoutHeader, style, (short)0);

  testFilePath = new File(parent, "excelTestFile").getPath();

  // Write the output to a file
  FileOutputStream fileOut = new FileOutputStream(testFilePath);
  wb.write(fileOut);
  fileOut.close();
}

/**
 * 抽象出图片生成业务代码
 * 
 * @throws IOException
 */
private void extractPicturePortion(String svgString, XSSFWorkbook wb,
        XSSFSheet sheet, int startCol, int endCol, int startRow, int endRow)
        throws IOException {
    // 图片
    if (org.apache.commons.lang3.StringUtils.isNotBlank(svgString)) {
        byte[] safeDataBytes = new BASE64Decoder().decodeBuffer(svgString);
        int pictureIdx = wb.addPicture(safeDataBytes,
                Workbook.PICTURE_TYPE_JPEG);
        CreationHelper helper = wb.getCreationHelper();
        // Create the drawing patriarch. This is the top level container for
        // all shapes.
        Drawing drawing = sheet.createDrawingPatriarch();
        // add a picture shape
        ClientAnchor anchor = helper.createClientAnchor();
        // set top-left corner of the picture,
        // subsequent call of Picture#resize() will operate relative to it
        anchor.setCol1(startCol);
        anchor.setCol2(endCol);
        anchor.setRow1(startRow);
        anchor.setRow2(endRow);
        anchor.setDx1(0);
        anchor.setDy1(0);
        anchor.setDx2(0);
        anchor.setDy2(0);
        anchor.setAnchorType(ClientAnchor.MOVE_DONT_RESIZE);
        Picture pict = drawing.createPicture(anchor, pictureIdx);
        pict.resize(1);
    }
}

private static void ExcelTest(String Path) {
    Workbook wb = new XSSFWorkbook();
    Sheet employees = wb.createSheet(WorkbookUtil.createSafeSheetName("Mitarbeiter"));

    wb.getNumberOfSheets();
    wb.getSheet("test").getLastRowNum();

    Sheet s2 = wb.createSheet(WorkbookUtil.createSafeSheetName("Schorsch"));

    CreationHelper crHelper = wb.getCreationHelper();
    Row firstRow = employees.createRow(0);
    firstRow.createCell(0).setCellValue(crHelper.createRichTextString("Vorname"));
    firstRow.createCell(1).setCellValue(crHelper.createRichTextString("Nachname"));
    firstRow.createCell(2).setCellValue(crHelper.createRichTextString("Geburtsdatum"));

    Row secondRow = employees.createRow(1);
    secondRow.createCell(0).setCellValue(crHelper.createRichTextString("Santa"));
    secondRow.createCell(1).setCellValue(crHelper.createRichTextString("Claus"));
    secondRow.createCell(2).setCellValue(crHelper.createDataFormat().getFormat("1823-12-23"));

    Row thirdRow = employees.createRow(2);
    thirdRow.createCell(0).setCellValue(crHelper.createRichTextString("Oster"));
    thirdRow.createCell(1).setCellValue(crHelper.createRichTextString("Hase"));
    thirdRow.createCell(2).setCellValue(crHelper.createDataFormat().getFormat("1682-01-01"));

    CellStyle formatTableHead = wb.createCellStyle();
    formatTableHead.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
    formatTableHead.setFillPattern(CellStyle.SOLID_FOREGROUND);

    Font fontTableHead = wb.createFont();
    fontTableHead.setColor(IndexedColors.WHITE.getIndex());
    formatTableHead.setFont(fontTableHead);

    firstRow.getCell(0).setCellStyle(formatTableHead);
    firstRow.getCell(1).setCellStyle(formatTableHead);
    firstRow.getCell(2).setCellStyle(formatTableHead);

    CellStyle formatGebDate  = wb.createCellStyle();
    formatGebDate.setDataFormat(crHelper.createDataFormat().getFormat("dd.mm.yy"));

    secondRow.getCell(2).setCellStyle(formatGebDate);
    thirdRow.getCell(2).setCellStyle(formatGebDate);

    employees.autoSizeColumn(0);
    employees.autoSizeColumn(1);
    employees.autoSizeColumn(2);

    FileOutputStream fOut;
    try {
        fOut = new FileOutputStream(Path);
        wb.write(fOut);
        fOut.close();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}

private CellStyle getDateCellStyle(Sheet sheet) {
    if (dateCellStyle == null) {
        dateCellStyle = sheet.getWorkbook().createCellStyle();
        CreationHelper createHelper = sheet.getWorkbook().getCreationHelper();
        dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("mm/dd/yyyy hh:mm:ss"));  // TODO flexible
    }
    return dateCellStyle;
}

public static HSSFCellStyle createCelStyleForTime(HSSFWorkbook workBook)
{
    HSSFCellStyle cellStyle = workBook.createCellStyle();
    CreationHelper createHelper = workBook.getCreationHelper();
    cellStyle.setDataFormat(
        createHelper.createDataFormat().getFormat("m/d/yy h:mm:ss"));
    return cellStyle;
}

public void setValue(int rowNumber, int columnNumber, String value) throws CasterException {
    if(value==null) value="";
    Sheet sheet = workbook.getSheet(getExcelSheetName());

    // get Row
    Row row = sheet.getRow(rowNumber);
    if(row==null) row = sheet.createRow(rowNumber);

    // get Cell
    Cell cell = row.getCell(columnNumber);
    CellStyle style = null;
    if(cell != null) {
        style = cell.getCellStyle();
        row.removeCell(cell);
    }
    cell = row.createCell(columnNumber);
    if(style != null) cell.setCellStyle(style);

       CreationHelper createHelper = workbook.getCreationHelper();
       boolean isFormula=style != null && style.getDataFormatString().equals("@");


       if(!isFormula && Decision.isNumeric(value)) {
        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
        double dbl = Caster.toDoubleValue(value);
           cell.setCellValue(dbl);
           _expandColumnWidth(sheet,Caster.toString(dbl),columnNumber);
    }
       else if(StringUtil.isEmpty("")) {
           cell.setCellType(Cell.CELL_TYPE_BLANK);
           cell.setCellValue(createHelper.createRichTextString(""));
       }
       else {
           cell.setCellType(Cell.CELL_TYPE_STRING);
           cell.setCellValue(createHelper.createRichTextString(value));
           _expandColumnWidth(sheet,value,columnNumber);
       } 


}

private static void createSheet(XSSFWorkbook wb, Report report) {
    String sheetName = Long.toString(report.getAppId());//name of sheet


    XSSFSheet sheet = wb.createSheet(sheetName);
    CellStyle cellStyle = wb.createCellStyle();
    CreationHelper createHelper = wb.getCreationHelper();
    cellStyle.setDataFormat(
            createHelper.createDataFormat().getFormat(DATE_FORMAT));

    createFirstRow(sheet);
    int r = 1;
    //iterating r number of rows
    for (Review review : report.getReviews()) {
        XSSFRow row = sheet.createRow(r);

        row.createCell(0).setCellValue(review.getVersion());
        row.createCell(1).setCellValue(review.getRate());
        row.createCell(2).setCellValue(review.getTitle());
        row.createCell(3).setCellValue(review.getBody());

        XSSFCell dateCell = row.createCell(4);
        dateCell.setCellValue(review.getDate());
        dateCell.setCellStyle(cellStyle);

        row.createCell(5).setCellValue(review.getCountry().getName());
        r++;
    }
}

protected void addChart(HSSFWorkbook workbook, byte[] chart, String name) {
    int pictureIndex = workbook.addPicture(chart, HSSFWorkbook.PICTURE_TYPE_PNG);
    HSSFSheet sheet = workbook.createSheet(name);
    addTitle(sheet, name, 0);
    Drawing drawing = sheet.createDrawingPatriarch();
    CreationHelper helper = workbook.getCreationHelper();
    ClientAnchor anchor = helper.createClientAnchor();
    anchor.setRow1(1);
    anchor.setCol1(0);
    Picture picture = drawing.createPicture(anchor, pictureIndex);
    picture.resize();
}

public static void main(String[] args) throws Exception
{
    String dataPath = "src/featurescomparison/workingwithworkbook/adddataincells/data/";

    Workbook wb = new HSSFWorkbook();

    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet("new sheet");

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

    // Or do it on one line.
    row.createCell(1).setCellValue(1.2);
    row.createCell(2).setCellValue(
    createHelper.createRichTextString("This is a string"));
    row.createCell(3).setCellValue(true);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream(dataPath + "DataInCells_Apache_Out.xls");
    wb.write(fileOut);
    fileOut.close();

    // Print message
    System.out.println("Data Added Successfully");
}

public static void main(String[] args) throws Exception
{
    String dataPath = "src/featurescomparison/workingwithworkbook/addimages/data/";

    //create a new workbook
    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();

    //add picture data to this workbook.
    InputStream is = new FileInputStream(dataPath + "aspose.jpg");
    byte[] bytes = IOUtils.toByteArray(is);
    int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
    is.close();

    CreationHelper helper = wb.getCreationHelper();

    //create sheet
    Sheet sheet = wb.createSheet();

    // Create the drawing patriarch.  This is the top level container for all shapes. 
    Drawing drawing = sheet.createDrawingPatriarch();

    //add a picture shape
    ClientAnchor anchor = helper.createClientAnchor();
    //set top-left corner of the picture,
    //subsequent call of Picture#resize() will operate relative to it
    anchor.setCol1(3);
    anchor.setRow1(2);
    Picture pict = drawing.createPicture(anchor, pictureIdx);

    //auto-size picture relative to its top-left corner
    pict.resize();

    //save workbook
    String file = dataPath + "ApacheImage.xls";
    if(wb instanceof XSSFWorkbook) file += "x";
    FileOutputStream fileOut = new FileOutputStream(file);
    wb.write(fileOut);
    fileOut.close();
    System.out.println("Done...");
}

private CellStyle createDateStyle(Workbook wb) {
    CreationHelper createHelper = wb.getCreationHelper();
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(
            "yyyy-MM-dd HH:mm:ss"));
    cellStyle.setAlignment(CellStyle.ALIGN_LEFT);
    return cellStyle;
}

@Override
public void render(OutputStream outputStream, Object model, String mimeType, Map<String, Object> parameters) {
    Validate.isTrue(StringUtils.equals(mimeType, "application/xls"));
    try {
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet("new sheet");

        CreationHelper createHelper = wb.getCreationHelper();
        // Create a row and put some cells in it. Rows are 0 based.
        Row row = sheet.createRow((short) 0);

        // Or do it on one line.
        CustomerBean bean = (CustomerBean) model;
        row.createCell(1).setCellValue(bean.getCustomerNo());
        row.createCell(2).setCellValue(createHelper.createRichTextString(bean.getFirstName()));
        row.createCell(3).setCellValue(createHelper.createRichTextString(bean.getLastName()));
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
        Cell cell = row.createCell(4);
        cell.setCellValue(bean.getBirthDate());
        cell.setCellStyle(cellStyle);
        row.createCell(5).setCellValue(bean.getMailingAddress());
        row.createCell(6).setCellValue(bean.getMarried());
        row.createCell(7).setCellValue(bean.getNumberOfKids());
        row.createCell(8).setCellValue(bean.getFavouriteQuote());
        row.createCell(9).setCellValue(bean.getEmail());
        row.createCell(10).setCellValue(bean.getLoyaltyPoints());

        wb.write(outputStream);
        outputStream.close();
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

private void initColumnCellStyles() {
    columnCellStyles = new CellStyle[tableRDesc.getRootNode().getChildren().size()];
    for (int col = 0; col < tableRDesc.getRootNode().getChildren().size(); col++) {
        Node<TableRDescBase> columnNode = tableRDesc.getRootNode().getChildren().get(col);
        ColRDesc colRenderDesc = (ColRDesc) columnNode.getNodeElement();

        FDesc fdesc = getFieldDescForColumn(columnNode);
        CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

        CreationHelper createHelper = sheet.getWorkbook().getCreationHelper();
        if (fdesc instanceof LongFDesc) {
            cellStyle.setAlignment(CellStyle.ALIGN_RIGHT);
            cellStyle.setDataFormat(
                createHelper.createDataFormat().getFormat(
                    IFConsts.NumberFormat.defaultExcelWholeNumberFormat));
        } else if (fdesc instanceof DoubleFDesc) {
            cellStyle.setAlignment(CellStyle.ALIGN_RIGHT);
            cellStyle.setDataFormat(
                createHelper.createDataFormat().getFormat(
                    IFConsts.NumberFormat.defaultExcelNumberFormat));
        } else {
            cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        }

        String dataFormatOverride = null;
        if (colRenderDesc.getPropValue(ColRDesc.EXCEL_NUMBERFORMAT) != null) {
            dataFormatOverride = colRenderDesc.getPropValue(ColRDesc.EXCEL_NUMBERFORMAT);
        }
        if (dataFormatOverride != null) {
            cellStyle
                .setDataFormat(createHelper.createDataFormat().getFormat(dataFormatOverride));
        }

        columnCellStyles[col] = cellStyle;
    }

}

public CellStyle getOrCreateCellStyle(String format) {
    if (definedStyles == null) {
        definedStyles = new HashMap<String, CellStyle>();
    }
    if (!definedStyles.containsKey(format)) {
        CreationHelper createHelper = sheet.getWorkbook().getCreationHelper();
        CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(format));
        definedStyles.put(format, cellStyle);
    }
    return definedStyles.get(format);
}

public void fillCellStyle (@Nonnull final Workbook aWB,
                           @Nonnull final CellStyle aCS,
                           @Nonnull final CreationHelper aCreationHelper)
{
  if (m_eAlign != null)
    aCS.setAlignment (m_eAlign);
  if (m_eVAlign != null)
    aCS.setVerticalAlignment (m_eVAlign);
  aCS.setWrapText (m_bWrapText);
  if (m_sDataFormat != null)
    aCS.setDataFormat (aCreationHelper.createDataFormat ().getFormat (m_sDataFormat));
  if (m_eFillBackgroundColor != null)
    aCS.setFillBackgroundColor (m_eFillBackgroundColor.getIndex ());
  if (m_eFillForegroundColor != null)
    aCS.setFillForegroundColor (m_eFillForegroundColor.getIndex ());
  if (m_eFillPattern != null)
    aCS.setFillPattern (m_eFillPattern);
  if (m_eBorderTop != null)
    aCS.setBorderTop (m_eBorderTop);
  if (m_eBorderRight != null)
    aCS.setBorderRight (m_eBorderRight);
  if (m_eBorderBottom != null)
    aCS.setBorderBottom (m_eBorderBottom);
  if (m_eBorderLeft != null)
    aCS.setBorderLeft (m_eBorderLeft);
  if (m_nFontIndex >= 0)
    aCS.setFont (aWB.getFontAt (m_nFontIndex));
}

protected CellStyle createDateFormatCellStyle(final Workbook wb) {
    final CreationHelper createHelper = wb.getCreationHelper();
    final short dateFormat = createHelper.createDataFormat().getFormat("yyyy-mm-dd");
    final CellStyle dateCellStyle = wb.createCellStyle();
    dateCellStyle.setDataFormat(dateFormat);
    return dateCellStyle;
}

/**
 * Replace all occurrences of the given collection expression name with the
 * given item name, in preparation for implicit collections processing
 * loops.
 * @param sheet The <code>Sheet</code> on which the <code>Block</code> lies.
 * @param block The <code>Block</code> in which to perform the replacement.
 * @param collExpr The collection expression string to replace.
 * @param itemName The item name that replaces the collection expression.
 */
public static void setUpBlockForImplicitCollectionAccess(Sheet sheet, Block block,
   String collExpr, String itemName)
{
   int left = block.getLeftColNum();
   int right = block.getRightColNum();
   int top = block.getTopRowNum();
   int bottom = block.getBottomRowNum();
   CreationHelper helper = sheet.getWorkbook().getCreationHelper();
   // Look at the given range of Cells in the given range of rows.
   for (int rowNum = top; rowNum <= bottom; rowNum++)
   {
      Row row = sheet.getRow(rowNum);
      if (row != null)
      {
         for (int cellNum = left; cellNum <= right; cellNum++)
         {
            Cell cell = row.getCell(cellNum);
            if (cell != null && cell.getCellType() == Cell.CELL_TYPE_STRING)
            {
               RichTextString value = cell.getRichStringCellValue();
               cell.setCellValue(RichTextStringUtil.replaceAll(
                  value, helper, collExpr, itemName, false, 0, true));
            }
         }
      }
   }
}

@Override
public void inlineToXls(HSSFPatriarch patriarch, HSSFCell resultCell, Object paramValue, Matcher paramsMatcher) {
    try {
        Image image = new Image(paramValue, paramsMatcher);
        if (image.isValid()) {
            HSSFSheet sheet = resultCell.getSheet();
            HSSFWorkbook workbook = sheet.getWorkbook();

            int pictureIdx = workbook.addPicture(image.imageContent, Workbook.PICTURE_TYPE_JPEG);

            CreationHelper helper = workbook.getCreationHelper();
            ClientAnchor anchor = helper.createClientAnchor();
            anchor.setCol1(resultCell.getColumnIndex());
            anchor.setRow1(resultCell.getRowIndex());
            anchor.setCol2(resultCell.getColumnIndex());
            anchor.setRow2(resultCell.getRowIndex());
            if (patriarch == null) {
                throw new IllegalArgumentException(String.format("No HSSFPatriarch object provided. Charts on this sheet could cause this effect. Please check sheet %s", resultCell.getSheet().getSheetName()));
            }
            HSSFPicture picture = patriarch.createPicture(anchor, pictureIdx);
            Dimension size = ImageUtils.getDimensionFromAnchor(picture);
            double actualHeight = size.getHeight() / EMU_PER_PIXEL;
            double actualWidth = size.getWidth() / EMU_PER_PIXEL;
            picture.resize((double) image.width / actualWidth, (double) image.height / actualHeight);
        }
    } catch (IllegalArgumentException e) {
        throw new ReportFormattingException("An error occurred while inserting bitmap to xls file", e);
    }
}

@Override
public void setCellValue(Cell cell, String value)
{
  CreationHelper helper = cell.getSheet().getWorkbook().getCreationHelper();

  cell.setCellValue(helper.createRichTextString(value));
}