org.apache.poi.ss.usermodel.CreationHelper Java Examples

private void setImageValue(CellData cellData, Cell cell) {
    Sheet sheet = cell.getSheet();
    int index = sheet.getWorkbook().addPicture(cellData.getImageValue(), HSSFWorkbook.PICTURE_TYPE_PNG);
    Drawing drawing = sheet.getDrawingPatriarch();
    if (drawing == null) {
        drawing = sheet.createDrawingPatriarch();
    }
    CreationHelper helper = sheet.getWorkbook().getCreationHelper();
    ClientAnchor anchor = helper.createClientAnchor();
    anchor.setDx1(0);
    anchor.setDx2(0);
    anchor.setDy1(0);
    anchor.setDy2(0);
    anchor.setCol1(cell.getColumnIndex());
    anchor.setCol2(cell.getColumnIndex() + 1);
    anchor.setRow1(cell.getRowIndex());
    anchor.setRow2(cell.getRowIndex() + 1);
    anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
    drawing.createPicture(anchor, index);
}
 

@Test
void testToValueFormulaNumericLong() throws Exception {
  CellValue cellValue = new CellValue(1.2342151234E10);

  Cell cell = mock(Cell.class);

  FormulaEvaluator formulaEvaluator = mock(FormulaEvaluator.class);
  when(formulaEvaluator.evaluate(cell)).thenReturn(cellValue);

  CreationHelper creationHelper = mock(CreationHelper.class);
  when(creationHelper.createFormulaEvaluator()).thenReturn(formulaEvaluator);

  Workbook workbook = mock(Workbook.class);
  when(workbook.getCreationHelper()).thenReturn(creationHelper);

  Sheet sheet = mock(Sheet.class);
  when(sheet.getWorkbook()).thenReturn(workbook);

  when(cell.getCellTypeEnum()).thenReturn(FORMULA);
  when(cell.getSheet()).thenReturn(sheet);
  when(cell.getNumericCellValue()).thenReturn(1.2342151234E10);
  assertEquals("12342151234", toValue(cell));
}
 

@Override
public T toObject(final Cell cell) throws XlsMapperException {

    final ProcessCase processCase = ProcessCase.Load;
    final String formattedValue = Utils.trim(configuration.getCellFormatter().format(cell), trimmed);

    // デフォルト値の設定
    if(isEmptyCell(formattedValue, cell) && defaultValue.isPresent(processCase)) {
        return defaultValue.get(processCase);
    }

    // 数式のセルの場合、予め評価しておく
    final Cell evaluatedCell;
    if(cell.getCellTypeEnum().equals(CellType.FORMULA)) {
        final Workbook workbook = cell.getSheet().getWorkbook();
        final CreationHelper helper = workbook.getCreationHelper();
        final FormulaEvaluator evaluator = helper.createFormulaEvaluator();

        evaluatedCell = evaluator.evaluateInCell(cell);
    } else {
        evaluatedCell = cell;
    }

    return parseCell(evaluatedCell, formattedValue);
}
 

@Override
protected void setupCell(final Cell cell, final Optional<URI> cellValue) throws TypeBindException {
    
    // 既存のハイパーリンクを削除
    // 削除しないと、Excelの見た目上はリンクは変わっているが、データ上は2重にリンクが設定されている。
    cell.removeHyperlink();
    
    if(cellValue.isPresent()) {
        final CreationHelper helper = cell.getSheet().getWorkbook().getCreationHelper();
        final Hyperlink link = helper.createHyperlink(HyperlinkType.URL);
        link.setAddress(cellValue.get().toString());
        cell.setHyperlink(link);
        
        cell.setCellValue(cellValue.get().toString());
        
    } else {
        cell.setCellType(CellType.BLANK);
    }
    
}
 

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(HyperlinkType.URL);
            link.setAddress(comment);
            cell.setHyperlink(link);

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

private CellStyle getDecimalNumberFormat(int j, Sheet sheet, CreationHelper createHelper, CellStyle dCellStyle) {

		if (decimalFormats.get(j) != null)
			return decimalFormats.get(j);
		String decimals = "";
		for (int i = 0; i < j; i++) {
			decimals += "0";
		}

		CellStyle cellStyleDoub = this.buildCellStyle(sheet); // cellStyleDoub is the default cell style for doubles
		cellStyleDoub.cloneStyleFrom(dCellStyle);
		DataFormat df = createHelper.createDataFormat();
		String format = "#,##0";
		if (decimals.length() > 0) {
			format += "." + decimals;
		}
		cellStyleDoub.setDataFormat(df.getFormat(format));

		decimalFormats.put(j, cellStyleDoub);
		return cellStyleDoub;
	}
 

@Override
protected void setupCell(final Cell cell, final Optional<CellLink> cellValue) throws TypeBindException {
    
    // 既存のハイパーリンクを削除
    // 削除しないと、Excelの見た目上はリンクは変わっているが、データ上は2重にリンクが設定されている。
    cell.removeHyperlink();
    
    if(cellValue.isPresent()) {
        final CreationHelper helper = cell.getSheet().getWorkbook().getCreationHelper();
        final HyperlinkType type = POIUtils.judgeLinkType(cellValue.get().getLink());
        final Hyperlink link = helper.createHyperlink(type);
        link.setAddress(cellValue.get().getLink());
        cell.setHyperlink(link);
        
        cell.setCellValue(cellValue.get().getLabel());
        
    } else {
        cell.setCellType(CellType.BLANK);
    }
    
}
 

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();
}
 

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 void fillFirstRow(boolean indexed, Sheet currSheet, Map<String, Object> attributes, CreationHelper crHelper) {
	Row firstRow = currSheet.createRow(0);
	
	int idx = 0;
	if (indexed){
		firstRow.createCell(idx++).setCellValue("Value");
		firstRow.createCell(idx++).setCellValue("Context");
	}
	else{
		firstRow.createCell(idx++).setCellValue("Type");	
	}

	firstRow.createCell(idx++).setCellValue("Imagename");
	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");
	
	if (indexed){
		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)); 	
		}
	}
	
}
 

/** writing headers so first row. */
private void writeHeader(RowMetadata metadata) {
    CreationHelper createHelper = this.workbook.getCreationHelper();
    Row headerRow = this.sheet.createRow(rowIdx++);
    int cellIdx = 0;
    for (ColumnMetadata columnMetadata : metadata.getColumns()) {
        // TODO apply some formatting as it's an header cell?
        headerRow.createCell(cellIdx++).setCellValue(createHelper.createRichTextString(columnMetadata.getName()));
    }
}
 

public ExcelCell link(String url, String label) {
    final CreationHelper creationHelper = row().sheet().workbook().poiWorkbook().getCreationHelper();
    final Hyperlink hl = creationHelper.createHyperlink(HyperlinkType.URL);
    hl.setAddress(url);
    hl.setLabel(label);
    currentCell.setCellValue(label);
    currentCell.setHyperlink(hl);
    style(LINK);
    return this;
}
 

@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);
    }
}
 

public FastExcelTextExtractor( final ExcelColorProducer colorProducer, final ExcelFontFactory fontFactory,
    final CreationHelper creationHelper ) {
  this.colorProducer = colorProducer;
  this.formatBuffer = new ArrayList<RichTextFormat>();
  this.fontFactory = fontFactory;
  this.creationHelper = creationHelper;
  this.formatBufferStack = new FastStack<RichTextFormat>();
}
 

public ExcelTextExtractor( final OutputProcessorMetaData metaData, final ExcelColorProducer colorProducer,
    final CreationHelper creationHelper, final ExcelFontFactory fontFactory ) {
  super( metaData );
  this.creationHelper = creationHelper;
  this.fontFactory = fontFactory;
  if ( colorProducer == null ) {
    throw new NullPointerException();
  }
  this.colorProducer = colorProducer;
  this.formatBuffer = new ArrayList<RichTextFormat>();
  this.formatBufferStack = new FastStack<RichTextFormat>();
}
 

@Test
public void testExcelRendering() throws Exception {
  URL resource = getClass().getResource( "rich-text-sample1.prpt" );
  ResourceManager mgr = new ResourceManager();
  MasterReport report = (MasterReport) mgr.createDirectly( resource, MasterReport.class ).getResource();
  report.getReportConfiguration()
      .setConfigProperty( ClassicEngineCoreModule.COMPLEX_TEXT_CONFIG_OVERRIDE_KEY, "true" );
  report.getReportHeader().getElement( 0 ).getStyle().setStyleProperty( TextStyleKeys.DIRECTION, TextDirection.LTR );
  report.getReportHeader().getElement( 1 ).getStyle().setStyleProperty( TextStyleKeys.DIRECTION, TextDirection.RTL );
  report.getReportHeader().removeElement( 0 );
  report.getReportHeader().getStyle().setStyleProperty( ElementStyleKeys.BACKGROUND_COLOR, Color.YELLOW );
  report.getReportFooter().clear();

  LogicalPageBox logicalPageBox = DebugReportRunner.layoutPage( report, 0 );

  RenderNode second = MatchFactory.findElementByName( logicalPageBox, "second" );
  assertTrue( second instanceof RenderBox );

  ExcelOutputProcessorMetaData metaData =
      new ExcelOutputProcessorMetaData( ExcelOutputProcessorMetaData.PAGINATION_FULL );
  metaData.initialize( report.getConfiguration() );

  XSSFWorkbook hssfWorkbook = new XSSFWorkbook();
  ExcelColorProducer colorProducer = new StaticExcelColorSupport();
  ExcelFontFactory ff = new ExcelFontFactory( hssfWorkbook, colorProducer );
  CreationHelper ch = hssfWorkbook.getCreationHelper();
  ExcelTextExtractor te = new ExcelTextExtractor( metaData, colorProducer, ch, ff );

  Object compute = te.compute( (RenderBox) second );
  assertTrue( compute instanceof RichTextString );
  XSSFRichTextString rt = (XSSFRichTextString) compute;
  assertEquals( 4, rt.numFormattingRuns() );
}
 

@Test
public void testFastExcelRendering() throws Exception {
  URL resource = getClass().getResource( "rich-text-sample1.prpt" );
  ResourceManager mgr = new ResourceManager();
  MasterReport report = (MasterReport) mgr.createDirectly( resource, MasterReport.class ).getResource();
  report.getReportConfiguration()
      .setConfigProperty( ClassicEngineCoreModule.COMPLEX_TEXT_CONFIG_OVERRIDE_KEY, "true" );
  report.getReportHeader().getElement( 0 ).getStyle().setStyleProperty( TextStyleKeys.DIRECTION, TextDirection.LTR );
  report.getReportHeader().getElement( 1 ).getStyle().setStyleProperty( TextStyleKeys.DIRECTION, TextDirection.RTL );
  report.getReportHeader().removeElement( 0 );
  report.getReportHeader().getStyle().setStyleProperty( ElementStyleKeys.BACKGROUND_COLOR, Color.YELLOW );
  report.getReportFooter().clear();

  ExpressionRuntime runtime =
      new GenericExpressionRuntime( new DefaultTableModel(), 0, new DefaultProcessingContext( report ) );

  RichTextStyleResolver resolver = new RichTextStyleResolver( runtime.getProcessingContext(), report );
  resolver.resolveRichTextStyle( report );

  XSSFWorkbook hssfWorkbook = new XSSFWorkbook();
  ExcelColorProducer colorProducer = new StaticExcelColorSupport();
  ExcelFontFactory ff = new ExcelFontFactory( hssfWorkbook, colorProducer );
  CreationHelper ch = hssfWorkbook.getCreationHelper();
  FastExcelTextExtractor te = new FastExcelTextExtractor( colorProducer, ff, ch );

  Element element = report.getReportHeader().getElement( 0 );
  Object compute = te.compute( element, runtime );
  assertTrue( compute instanceof RichTextString );
  XSSFRichTextString rt = (XSSFRichTextString) compute;
  assertEquals( 4, rt.numFormattingRuns() );
}
 

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;
}
 

/**
 * 导出Excel的例子
 * 因为类上面注解是@Controller，此方法需要@ResponseBody注解；如果类是RestController，则不需要ResponseBody
 * @return
 * @throws Exception
 */
@ResponseBody
@GetMapping("/export")
public ResponseEntity<byte[]> exportExcel() throws Exception{
    logger.trace("exportExcel");
    HttpHeaders responseHeaders = new HttpHeaders();
    responseHeaders.setContentDispositionFormData("attachment",new String("导出的文件名.xlsx".getBytes(), "ISO8859-1"));
    responseHeaders.setContentType(MediaType.APPLICATION_OCTET_STREAM);

    //中文文件名需要用iso8859-1编码
    InputStream templateIs = this.getClass().getResourceAsStream("/excel-templates/templet.xlsx");
    XSSFWorkbook workbook = new XSSFWorkbook(templateIs);
    XSSFSheet sheet = workbook.getSheetAt(0);

    List<SampleItem> list = getDataList();

    CellStyle cellStyle = workbook.createCellStyle();
    CreationHelper createHelper = workbook.getCreationHelper();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy/mm/dd"));

    for (int i=0; i<list.size(); i++) {
        SampleItem si = list.get(i);

        XSSFRow row = sheet.createRow(i + 1);

        Cell cell1 = row.createCell(0);
        cell1.setCellValue(si.getDate());
        cell1.setCellStyle(cellStyle);

        Cell cell2 = row.createCell(1);
        cell2.setCellValue(si.getName());

        Cell cell3 = row.createCell(2);
        cell3.setCellValue(si.getScore());
    }

    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    workbook.write(bos);
    workbook.close();
    return new ResponseEntity<byte[]>(bos.toByteArray(), responseHeaders, HttpStatus.OK);
}
 

@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
    List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
    // 这里可以对cell进行任何操作
    LOGGER.info("第{}行，第{}列写入完成。", cell.getRowIndex(), cell.getColumnIndex());
    if (isHead && cell.getColumnIndex() == 0) {
        CreationHelper createHelper = writeSheetHolder.getSheet().getWorkbook().getCreationHelper();
        Hyperlink hyperlink = createHelper.createHyperlink(HyperlinkType.URL);
        hyperlink.setAddress("https://github.com/alibaba/easyexcel");
        cell.setHyperlink(hyperlink);
    }
}
 

@Test
public void test2() throws IOException {
	 //create a new workbook
	XSSFWorkbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    String imgPath = "D:\\Xiaoi\\logo\\logo.png";
    //add picture data to this workbook.
    InputStream is = new FileInputStream(imgPath);
    byte[] bytes = IOUtils.toByteArray(is);
    int pictureIdx = wb.addPicture(bytes, XSSFWorkbook.PICTURE_TYPE_PNG);
    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);
    anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
    Picture pict = drawing.createPicture(anchor, pictureIdx);

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

    //save workbook
    String file = "E:\\ConvertTester\\excel\\picture.xls";
    if(wb instanceof XSSFWorkbook) file += "x";
    try (OutputStream fileOut = new FileOutputStream(file)) {
        wb.write(fileOut);
    }
}
 

ActionResult<Wo> execute(EffectivePerson effectivePerson, Boolean stream) throws Exception {
	try (EntityManagerContainer emc = EntityManagerContainerFactory.instance().create();
			Workbook workbook = new XSSFWorkbook();
			ByteArrayOutputStream output = new ByteArrayOutputStream()) {
		ActionResult<Wo> result = new ActionResult<>();
		Business business = new Business(emc);
		Sheet sheet = workbook.createSheet("loginRecord");
		Row row = sheet.createRow(0);
		row.createCell(0).setCellValue("name");
		row.createCell(1).setCellValue("lastLoginTime");
		row.createCell(2).setCellValue("lastLoginAddress");
		row.createCell(3).setCellValue("lastLoginClient");
		int line = 0;
		Cell cell = null;
		CellStyle dateCellStyle = workbook.createCellStyle();
		CreationHelper createHelper = workbook.getCreationHelper();
		dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat(DateTools.format_yyyyMMdd));
		for (Person o : this.list(business)) {
			row = sheet.createRow(++line);
			row.createCell(0).setCellValue(o.getName());
			cell = row.createCell(1);
			if (null == o.getLastLoginTime()) {
				cell.setCellValue("");
			} else {
				cell.setCellValue(o.getLastLoginTime());
				cell.setCellStyle(dateCellStyle);
			}
			row.createCell(2).setCellValue(o.getLastLoginAddress());
			row.createCell(3).setCellValue(o.getLastLoginClient());
		}
		String name = "loginRecord_" + DateTools.formatDate(new Date()) + ".xlsx";
		workbook.write(output);
		Wo wo = new Wo(output.toByteArray(), this.contentType(stream, name), this.contentDisposition(stream, name));
		result.setData(wo);
		return result;
	}
}
 

public Workbook export(String exportLimit, boolean showLimitExportMessage) {
	Workbook workbook = this.instantiateWorkbook();
	CreationHelper createHelper = workbook.getCreationHelper();
	Sheet sheet = workbook.createSheet("new sheet");
	for (int j = 0; j < 50; j++) {
		sheet.createRow(j);
	}
	fillSheet(sheet, workbook, createHelper, 0, exportLimit, showLimitExportMessage);
	return workbook;
}
 

public void fillSheet(Sheet sheet, Workbook wb, CreationHelper createHelper, int startRow, String exportLimit, boolean showLimitExportMessage) {
	// we enrich the JSON object putting every node the descendants_no
	// property: it is useful when merging cell into rows/columns headers
	// and when initializing the sheet
	if (dataStore != null && !dataStore.isEmpty()) {
		if (showLimitExportMessage) {
			fillMessageHeader(sheet, exportLimit);
			startRow = 1;
		}
		CellStyle[] cellTypes = fillSheetHeader(sheet, wb, createHelper, startRow, DEFAULT_START_COLUMN);
		fillSheetData(sheet, wb, createHelper, cellTypes, startRow + 1, DEFAULT_START_COLUMN);
	}
}
 

/**
 * 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 = 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;
	}
}
 

public int fillAlreadyCreatedSheet(Sheet sheet, CrossTab cs, CreationHelper createHelper, int startRow, Locale locale)
		throws JSONException, SerializationException {
	// we enrich the JSON object putting every node the descendants_no
	// property: it is useful when merging cell into rows/columns headers
	// and when initializing the sheet
	int totalRowNum = commonFillSheet(sheet, cs, createHelper, startRow, locale);
	return totalRowNum;
}
 

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 = 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;
}
 

public int commonFillSheet(Sheet sheet, CrossTab cs, CreationHelper createHelper, int startRow, Locale locale)
		throws SerializationException, JSONException {
	int columnsDepth = cs.getColumnsRoot().getSubTreeDepth();
	int rowsDepth = cs.getRowsRoot().getSubTreeDepth();

	MeasureFormatter measureFormatter = new MeasureFormatter(cs);
	int rowsNumber = cs.getDataMatrix().length;
	// + 1 because there may be also the bottom row with the totals
	int totalRowsNumber = columnsDepth + rowsNumber + 1;
	for (int i = 0; i < totalRowsNumber + 5; i++) {
		sheet.createRow(startRow + i);
	}

	CellStyle memberCellStyle = this.buildHeaderCellStyle(sheet);
	CellStyle dimensionCellStyle = this.buildDimensionCellStyle(sheet);

	// build headers for column first ...
	buildColumnsHeader(sheet, cs, cs.getColumnsRoot().getChilds(), startRow, rowsDepth - 1, createHelper, locale, memberCellStyle, dimensionCellStyle);
	// ... then build headers for rows ....
	buildRowsHeaders(sheet, cs, cs.getRowsRoot().getChilds(), columnsDepth - 1 + startRow, 0, createHelper, locale, memberCellStyle);
	// then put the matrix data
	buildDataMatrix(sheet, cs, columnsDepth + startRow - 1, rowsDepth - 1, createHelper, measureFormatter);

	buildRowHeaderTitle(sheet, cs, columnsDepth - 2, 0, startRow, createHelper, locale, dimensionCellStyle);

	return startRow + totalRowsNumber;
}
 

private Map<String, CellStyle> createHSSFCellStyles(Workbook wb, int[] contextBgColor, int[] contextFontColor, int contextFontSize, int contextFontAlign, int[] headerBgColor,
		int[] headerFontColor, int headerFontSize, int headerAlign) {
	Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

	HSSFWorkbook workbook = (HSSFWorkbook) wb;
	HSSFPalette palette = workbook.getCustomPalette();
	palette.setColorAtIndex((short) 11, (byte) contextBgColor[0], (byte) contextBgColor[1], (byte) contextBgColor[2]);
	palette.setColorAtIndex((short) 12, (byte) contextFontColor[0], (byte) contextFontColor[1], (byte) contextFontColor[2]);
	palette.setColorAtIndex((short) 13, (byte) headerBgColor[0], (byte) headerBgColor[1], (byte) headerBgColor[2]);
	palette.setColorAtIndex((short) 14, (byte) headerFontColor[0], (byte) headerFontColor[1], (byte) headerFontColor[2]);

	HSSFFont headerFont = workbook.createFont();
	headerFont.setCharSet(HSSFFont.DEFAULT_CHARSET);
	headerFont.setFontName("宋体");
	headerFont.setColor((short) 14);
	headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
	headerFont.setFontHeightInPoints((short) headerFontSize);
	CellStyle headerStyle = this.createBorderCellStyle(workbook, true);

	headerStyle.setFont(headerFont);
	headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	headerStyle.setFillForegroundColor((short) 13);
	this.setCellStyleAligment(headerStyle, headerAlign);
	headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	styles.put(GridStyleType.headerStyle.name(), headerStyle);

	HSSFFont dataFont = workbook.createFont();
	dataFont.setColor((short) 12);
	dataFont.setFontHeightInPoints((short) contextFontSize);
	dataFont.setCharSet(HSSFFont.DEFAULT_CHARSET);
	dataFont.setFontName("宋体");

	CellStyle dataAlignLeftStyle = this.createBorderCellStyle(workbook, true);
	dataAlignLeftStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	dataAlignLeftStyle.setFillForegroundColor((short) 11);
	dataAlignLeftStyle.setFont(dataFont);
	dataAlignLeftStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
	dataAlignLeftStyle.setWrapText(true);
	dataAlignLeftStyle.setAlignment(CellStyle.ALIGN_LEFT);
	styles.put(GridStyleType.dataAlignLeftStyle.name(), dataAlignLeftStyle);

	CellStyle dataAlignCenterStyle = this.createBorderCellStyle(workbook, true);
	dataAlignCenterStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	dataAlignCenterStyle.setFillForegroundColor((short) 11);
	dataAlignCenterStyle.setFont(dataFont);
	dataAlignCenterStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
	dataAlignCenterStyle.setWrapText(true);
	dataAlignCenterStyle.setAlignment(CellStyle.ALIGN_CENTER);
	styles.put(GridStyleType.dataAlignCenterStyle.name(), dataAlignCenterStyle);

	CellStyle dataAlignRightStyle = this.createBorderCellStyle(workbook, true);
	dataAlignRightStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	dataAlignRightStyle.setFillForegroundColor((short) 11);
	dataAlignRightStyle.setFont(dataFont);
	dataAlignRightStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
	dataAlignRightStyle.setWrapText(true);
	dataAlignRightStyle.setAlignment(CellStyle.ALIGN_RIGHT);
	styles.put(GridStyleType.dataAlignRightStyle.name(), dataAlignRightStyle);

	CellStyle dateStyle = this.createBorderCellStyle(workbook, true);
	CreationHelper helper = workbook.getCreationHelper();
	dateStyle.setDataFormat(helper.createDataFormat().getFormat("m/d/yy h:mm"));
	dateStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	dateStyle.setFillForegroundColor((short) 11);
	dateStyle.setFont(dataFont);
	dateStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
	this.setCellStyleAligment(dateStyle, contextFontAlign);
	styles.put(GridStyleType.dateStyle.name(), dateStyle);

	return styles;
}
 

/**
 *
 * @param sheet
 *            ...
 * @param workbook
 *            ...
 * @param createHelper
 *            ...
 * @param beginRowHeaderData
 *            header's vertical offset. Expressed in number of rows
 * @param beginColumnHeaderData
 *            header's horizontal offset. Expressed in number of columns
 *
 * @return ...
 */
private CellStyle[] fillSheetHeader(Sheet sheet, Workbook workbook, CreationHelper createHelper, int beginRowHeaderData, int beginColumnHeaderData) {

	CellStyle[] cellTypes;

	logger.trace("IN");

	try {

		IMetaData dataStoreMetaData = dataStore.getMetaData();
		int colnumCount = dataStoreMetaData.getFieldCount();

		Row headerRow = sheet.getRow(beginRowHeaderData);
		CellStyle headerCellStyle = buildHeaderCellStyle(sheet);

		cellTypes = new CellStyle[colnumCount];
		for (int j = 0; j < colnumCount; j++) {
			Cell cell = headerRow.createCell(j + beginColumnHeaderData);
			cell.setCellType(getCellTypeString());
			String fieldName = dataStoreMetaData.getFieldAlias(j);
			IFieldMetaData fieldMetaData = dataStoreMetaData.getFieldMeta(j);
			String format = (String) fieldMetaData.getProperty("format");
			String alias = fieldMetaData.getAlias();
			String scaleFactorHeader = (String) fieldMetaData.getProperty(ADDITIONAL_DATA_FIELDS_OPTIONS_SCALE_FACTOR);

			String header;
			if (extractedFields != null && j < extractedFields.size() && extractedFields.get(j) != null) {
				Field field = (Field) extractedFields.get(j);
				fieldName = field.getAlias();
				if (field.getPattern() != null) {
					format = field.getPattern();
				}
			}
			CellStyle aCellStyle = this.buildCellStyle(sheet);
			if (format != null) {
				short formatInt = this.getBuiltinFormat(format);
				aCellStyle.setDataFormat(formatInt);
				cellTypes[j] = aCellStyle;
			}

			if (alias != null && !alias.equals("")) {
				header = alias;
			} else {
				header = fieldName;
			}

			header = MeasureScaleFactorOption.getScaledName(header, scaleFactorHeader, locale);
			cell.setCellValue(createHelper.createRichTextString(header));

			cell.setCellStyle(headerCellStyle);

		}

	} catch (Throwable t) {
		throw new SpagoBIRuntimeException("An unexpected error occured while filling sheet header", t);
	} finally {
		logger.trace("OUT");
	}

	return cellTypes;
}