Attachment #27294 for bug #51196

View | Details | Raw Unified | Return to bug 51196
Collapse All | Expand All




/* ====================================================================
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at
   
   http://www.apache.org/licenses/LICENSE-2.0
   
   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
   ==================================================================== */

package org.apache.poi.ss.util.cellwalk;

/**
 * @author Roman Kashitsyn
 */
public interface CellWalkContext {

    /**
     * Returns ordinal number of cell in range.  Numeration starts
     * from top left cell and ends at bottom right cell. Here is a
     * brief example (number in cell is it's ordinal number):
     *
     * <table border="1">
     *   <tbody>
     *     <tr><td>1</td><td>2</td></tr>
     *     <tr><td>3</td><td>4</td></tr>
     *   </tbody>
     * </table>
     *
     * @return ordinal number of current cell
     */
    long getOrdinalNumber();

    /**
     * Returns number of current row.
     * @return number of current row
     */
    int getRowNumber();

    /**
     * Returns number of current column.
     * @return number of current column
     */
    int getColumnNumber();
}




/* ====================================================================
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at
   
   http://www.apache.org/licenses/LICENSE-2.0
   
   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
   ==================================================================== */

package org.apache.poi.ss.util.cellwalk;

import org.apache.poi.ss.usermodel.Cell;

/**
 * Represents callback for CellWalk traverse method.  
 * @author Roman Kashitsyn
 */
public interface CellHandler {

    /**
     * @param cell current cell
     * @param ctx information about invokation context
     */
    void onCell(Cell cell, CellWalkContext ctx);

}




/* ====================================================================
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at
   
   http://www.apache.org/licenses/LICENSE-2.0
   
   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
   ==================================================================== */

package org.apache.poi.ss.util.cellwalk;


import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.DataMarker;

/**
 * Traverse cell range.
 *
 * @author Roman Kashitsyn
 */
public class CellWalk {

    private Sheet sheet;
    private CellRangeAddress range;
    private boolean traverseEmptyCells;

    public CellWalk(DataMarker dm) {
	this(dm.getSheet(), dm.getRange());
    }

    public CellWalk(Sheet sheet, CellRangeAddress range) {
	this.sheet = sheet;
	this.range = range;
	this.traverseEmptyCells = false;
    }

    /**
     * Should we call handler on empty (blank) cells. Default is
     * false.
     * @return true if handler should be called on empty (blank)
     * cells, false otherwise.
     */
    public boolean isTraverseEmptyCells() {
	return traverseEmptyCells;
    }

    /**
     * Sets the traverseEmptyCells property.
     * @param traverseEmptyCells new property value
     */
    public void setTraverseEmptyCells(boolean traverseEmptyCells) {
	this.traverseEmptyCells = traverseEmptyCells;
    }

    /**
     * Traverse cell range from top left to bottom right cell.
     * @param handler handler to call on each appropriate cell
     */
    public void traverse(CellHandler handler) {
	int firstRow = range.getFirstRow();
	int lastRow = range.getLastRow();
	int firstColumn = range.getFirstColumn();
	int lastColumn = range.getLastColumn();
	final int width = lastColumn - firstColumn + 1;
	SimpleCellWalkContext ctx = new SimpleCellWalkContext();
	Row currentRow = null;
	Cell currentCell = null;

	for (ctx.rowNumber = firstRow; ctx.rowNumber <= lastRow; ++ctx.rowNumber) {
	    currentRow = sheet.getRow(ctx.rowNumber);
	    if (currentRow == null) {
		continue;
	    }
	    for (ctx.colNumber = firstColumn; ctx.colNumber <= lastColumn; ++ctx.colNumber) {
		currentCell = currentRow.getCell(ctx.colNumber);

		if (currentCell == null) {
		    continue;
		}
		if (isEmpty(currentCell) && !traverseEmptyCells) {
		    continue;
		}

		ctx.ordinalNumber =
		    (ctx.rowNumber - firstRow) * width +
		    (ctx.colNumber - firstColumn + 1);

		handler.onCell(currentCell, ctx);
	    }
	}
    }

    private boolean isEmpty(Cell cell) {
	return (cell.getCellType() == Cell.CELL_TYPE_BLANK);
    }

    /**
     * Inner class to hold walk context.
     * @author Roman Kashitsyn
     */
    private class SimpleCellWalkContext implements CellWalkContext {
	public long ordinalNumber = 0;
	public int rowNumber = 0;
	public int colNumber = 0;

	public long getOrdinalNumber() {
	    return ordinalNumber;
	}

	public int getRowNumber() {
	    return rowNumber;
	}

	public int getColumnNumber() {
	    return colNumber;
	}
    }
}




/* ====================================================================
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at
   
   http://www.apache.org/licenses/LICENSE-2.0
   
   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
   ==================================================================== */
package org.apache.poi.ss.util;

import java.util.Calendar;
import java.util.Date;

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;

/**
 * Class that provides useful sheet build capabilities. It can be used
 * in test cases to improve readability or in Swing applications with
 * tables.
 *
 * @author Roman Kashitsyn
 */
public class SheetBuilder {

    private Workbook workbook;
    private Object[][] cells;
    private boolean shouldCreateEmptyCells = false;

    public SheetBuilder(Workbook workbook, Object[][] cells) {
	this.workbook = workbook;
	this.cells = cells;
    }

    /**
     * @return true if null objects should be trated as empty cells
     *         false otherwise
     */
    public boolean getCreateEmptyCells() {
	return shouldCreateEmptyCells;
    }

    /**
     * @param shouldCreateEmptyCells true if null array elements should be
     *        trated as empty cells
     * @return this
     */
    public SheetBuilder setCreateEmptyCells(boolean shouldCreateEmptyCells) {
	this.shouldCreateEmptyCells = shouldCreateEmptyCells;
	return this;
    }

    /**
     * Builds sheet from parent workbook and 2D array with cell
     * values. Creates rows anyway (even if row contains only null
     * cells), creates cells only if corresponding property is true.
     * The conversion is performed in the following way:
     *
     * <ul>
     * <li>Numbers become numeric cells.</li>
     * <li><code>java.util.Date</code> or <code>java.util.Calendar</code>
     *     instances become date cells.</li>
     * <li>String with leading '=' char become formulas (leading '='
     *     trancated).</li>
     * <li>Other objects become strings via <code>Object.toString()</code>
     *     method.</li>
     * </ul>
     *
     * @return newly created sheet
     */
    public Sheet build() {
	Sheet sheet = workbook.createSheet();
	Row currentRow = null;
	Cell currentCell = null;

	for (int rowIndex = 0; rowIndex < cells.length; ++rowIndex) {
	    Object[] rowArray = cells[rowIndex];
	    currentRow = sheet.createRow(rowIndex);

	    for (int cellIndex = 0; cellIndex < rowArray.length; ++cellIndex) {
		Object cellValue = rowArray[cellIndex];
		if (cellValue != null || shouldCreateEmptyCells) {
		    currentCell = currentRow.createCell(cellIndex);
		    setCellValue(currentCell, cellValue);
		}
	    }
	}
	return sheet;
    }

    /**
     * Sets the cell value using object type information.
     * @param cell cell to change
     * @param value value to set
     */
    public void setCellValue(Cell cell, Object value) {
	if (value == null || cell == null) {
	    return;
	} else if (value instanceof Number) {
	    double doubleValue = ((Number) value).doubleValue();
	    cell.setCellValue(doubleValue);
	} else if (value instanceof Date) {
	    cell.setCellValue((Date) value);
	} else if (value instanceof Calendar) {
	    cell.setCellValue((Calendar) value);
	} else if (isFormulaDefinition(value)) {
	    cell.setCellFormula(getFormula(value));
	} else {
	    cell.setCellValue(value.toString());
	}
    }

    private boolean isFormulaDefinition(Object obj) {
	if (obj instanceof String) {
	    String str = (String) obj;
	    if (str.length() < 2) {
		return false;
	    } else {
		return ((String) obj).charAt(0) == '=';
	    }
	} else {
	    return false;
	}
    }

    private String getFormula(Object obj) {
	return ((String) obj).substring(1);
    }
}




/* ====================================================================
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
==================================================================== */

package org.apache.poi.xssf.usermodel.charts;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.ss.util.DataMarker;
import org.apache.poi.ss.util.cellwalk.CellWalk;
import org.apache.poi.ss.util.cellwalk.CellHandler;
import org.apache.poi.ss.util.cellwalk.CellWalkContext;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumData;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumVal;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTUnsignedInt;

/**
 * Package private class to fill chart's number reference with cached
 * numeric values. If a formula-typed cell referenced by data marker,
 * cell's value will be calculated and placed to cache. Numeric cells
 * will be placed to cache as is. Non-numeric cells will be ignored.
 *
 * @author Roman Kashitsyn
 */
class XSSFNumberCache {

    private CTNumData ctNumData;

    XSSFNumberCache(CTNumData ctNumData) {
	this.ctNumData = ctNumData;
    }

    /**
     * Builds new numeric cache container.
     * @param marker data marker to use for cache evaluation
     * @param ctNumRef parent number reference
     * @return numeric cache instance
     */
    static XSSFNumberCache buildCache(DataMarker marker, CTNumRef ctNumRef) {
	CellRangeAddress range = marker.getRange();
	int numOfPoints = range.getNumberOfCells();

	if (numOfPoints == 0) {
	    // Nothing to do.
	    return null;
	}

	XSSFNumberCache cache = new XSSFNumberCache(ctNumRef.addNewNumCache());
	cache.setPointCount(numOfPoints);

	Workbook wb = marker.getSheet().getWorkbook();
	FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

	CellWalk cellWalk = new CellWalk(marker);
	NumCacheCellHandler numCacheHandler = cache.new NumCacheCellHandler(evaluator);
	cellWalk.traverse(numCacheHandler);
	return cache;
    }
    
    /**
     * Returns total count of points in cache. Some (or even all) of
     * them might be empty.
     * @return total count of points in cache
     */
    long getPointCount() {
	CTUnsignedInt pointCount = ctNumData.getPtCount();
	if (pointCount != null) {
	    return pointCount.getVal();
	} else {
	    return 0L;
	}
    }

    /**
     * Returns cache value at specified index.
     * @param index index of the point in cache
     * @return point value
     */
    double getValueAt(int index) {
	/* TODO: consider more effective algorithm. Left as is since
	 * this method should be invoked mostly in tests. */
	for (CTNumVal pt : ctNumData.getPtList()) {
	    if (pt.getIdx() == index) {
		return Double.valueOf(pt.getV()).doubleValue();
	    }
	}
	return 0.0;
    }

    private void setPointCount(int numOfPoints) {
	ctNumData.addNewPtCount().setVal(numOfPoints);
    }

    private class NumCacheCellHandler implements CellHandler {

	private FormulaEvaluator evaluator;

	public NumCacheCellHandler(FormulaEvaluator evaluator) {
	    this.evaluator = evaluator;
	}

	public void onCell(Cell cell, CellWalkContext ctx) {
	    double pointValue = getOrEvalCellValue(cell);
	    /* Silently ignore non-numeric values.
	     * This is Office default behaviour. */
	    if (Double.isNaN(pointValue)) {
		return;
	    }

	    CTNumVal point = ctNumData.addNewPt();
	    point.setIdx(ctx.getOrdinalNumber());
	    point.setV(NumberToTextConverter.toText(pointValue));
	}

	private double getOrEvalCellValue(Cell cell) {
	    int cellType = cell.getCellType();

	    if (cellType == Cell.CELL_TYPE_NUMERIC) {
		return cell.getNumericCellValue();
	    } else if (cellType == Cell.CELL_TYPE_FORMULA) {
		CellValue value = evaluator.evaluate(cell);
		if (value.getCellType() == Cell.CELL_TYPE_NUMERIC) {
		    return value.getNumberValue();
		}
	    }
	    return Double.NaN;
	}

    }
}




   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

   http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
   ==================================================================== */

package org.apache.poi.xssf.usermodel.charts;


 */
public class XSSFScatterChartData implements ScatterChartData {

    /**
     * List of all data series.
     */
    private List<Serie> series;

    public XSSFScatterChartData() {
	series = new ArrayList<Serie>();
    }

    /**
     * Package private ScatterChartSerie implementation.
     */
    static class Serie implements ScatterChartSerie {
	private int id;
	private int order;
	private boolean useCache;
	private DataMarker xMarker;
	private DataMarker yMarker;
	private XSSFNumberCache lastCaclulatedXCache;
	private XSSFNumberCache lastCalculatedYCache;

	protected Serie(int id, int order) {
	    super();
	    this.id = id;
	    this.order = order;
	    this.useCache = true;
	}

	public void setXValues(DataMarker marker) {
	    xMarker = marker;
	}
		private boolean useCache;
		private DataMarker xMarker;
		private DataMarker yMarker;

	public void setYValues(DataMarker marker) {
	    yMarker = marker;
	}
			this.order = order;
			this.useCache = false;
		}

	/**
	 * @param useCache if true, cached results will be added on plot
	 */
	public void setUseCache(boolean useCache) {
	    this.useCache = useCache;
	}

	/**
	 * Returns last calculated number cache for X axis.
	 * @return last calculated number cache for X axis.
	 */
	XSSFNumberCache getLastCaculatedXCache() {
	    return lastCaclulatedXCache;
	}

	/**
	 * Returns last calculated number cache for Y axis.
	 * @return last calculated number cache for Y axis.
	 */
	XSSFNumberCache getLastCalculatedYCache() {
	    return lastCalculatedYCache;
	}

	protected void addToChart(CTScatterChart ctScatterChart) {
	    CTScatterSer scatterSer = ctScatterChart.addNewSer();
	    scatterSer.addNewIdx().setVal(this.id);
	    scatterSer.addNewOrder().setVal(this.order);

	    /* TODO: add some logic to automatically recognize cell
	     * types and choose appropriate data representation for
	     * X axis.
	     */
	    CTAxDataSource xVal = scatterSer.addNewXVal();
	    CTNumRef xNumRef = xVal.addNewNumRef();
	    xNumRef.setF(xMarker.formatAsString());

	    CTNumDataSource yVal = scatterSer.addNewYVal();
	    CTNumRef yNumRef = yVal.addNewNumRef();
	    yNumRef.setF(yMarker.formatAsString());
		}
	}

	    if (useCache) {
		/* We can not store cache since markers are not immutable */
		XSSFNumberCache.buildCache(xMarker, xNumRef);
		lastCalculatedYCache = XSSFNumberCache.buildCache(yMarker, yNumRef);
	    }
		series.add(newSerie);
		return newSerie;
	}
    }

    public ScatterChartSerie addSerie(DataMarker xMarker, DataMarker yMarker) {
	int numOfSeries = series.size();
	Serie newSerie = new Serie(numOfSeries, numOfSeries);
	newSerie.setXValues(xMarker);
	newSerie.setYValues(yMarker);
	series.add(newSerie);
	return newSerie;
    }

    public void fillChart(Chart chart, ChartAxis... axis) {
	if (!(chart instanceof XSSFChart)) {
	    throw new IllegalArgumentException("Chart must be instance of XSSFChart");
	}

	XSSFChart xssfChart = (XSSFChart) chart;
	CTPlotArea plotArea = xssfChart.getCTChart().getPlotArea();
	CTScatterChart scatterChart = plotArea.addNewScatterChart();
	addStyle(scatterChart);

	for (Serie s : series) {
	    s.addToChart(scatterChart);
		}
	}

	for (ChartAxis ax : axis) {
	    scatterChart.addNewAxId().setVal(ax.getId());
	}
    }

    public List<? extends Serie> getSeries() {
	return series;
    }

    private void addStyle(CTScatterChart ctScatterChart) {
	CTScatterStyle scatterStyle = ctScatterChart.addNewScatterStyle();
	scatterStyle.setVal(STScatterStyle.LINE_MARKER);
    }
}




/* ====================================================================
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at
   
   http://www.apache.org/licenses/LICENSE-2.0
   
   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
   ==================================================================== */
package org.apache.poi.xssf.usermodel.charts;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.DataMarker;
import org.apache.poi.ss.util.SheetBuilder;
import org.apache.poi.ss.usermodel.charts.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


import junit.framework.TestCase;

public class TestXSSFNumberCache extends TestCase {
    private static Object[][] plotData = {
	{0,      1,       2,       3,       4},
	{0, "=B1*2", "=C1*2", "=D1*2", "=E1*4"}
    };

    public void testFormulaCache() {
	Workbook wb = new XSSFWorkbook();
	Sheet sheet = new SheetBuilder(wb, plotData).build();
	Drawing drawing = sheet.createDrawingPatriarch();
	ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 1, 1, 10, 30);
	Chart chart = drawing.createChart(anchor);

	ChartAxis bottomAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.BOTTOM);
	ChartAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);

	ScatterChartData scatterChartData =
	    chart.getChartDataFactory().createScatterChartData();

	DataMarker xMarker = new DataMarker(sheet, CellRangeAddress.valueOf("A1:E1"));
	DataMarker yMarker = new DataMarker(sheet, CellRangeAddress.valueOf("A2:E2"));
	ScatterChartSerie serie = scatterChartData.addSerie(xMarker, yMarker);

	chart.plot(scatterChartData, bottomAxis, leftAxis);

	XSSFScatterChartData.Serie xssfScatterSerie =
	    (XSSFScatterChartData.Serie) serie;
	XSSFNumberCache yCache = xssfScatterSerie.getLastCalculatedYCache();

	assertEquals(5, yCache.getPointCount());
	assertEquals(4.0, yCache.getValueAt(3), 0.00001);
	assertEquals(16.0, yCache.getValueAt(5), 0.00001);
    }


}




   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

   http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
   ==================================================================== */

package org.apache.poi.xssf.usermodel.charts;

import junit.framework.TestCase;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.DataMarker;
import org.apache.poi.ss.util.SheetBuilder;
import org.apache.poi.ss.usermodel.charts.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Tests for XSSFScatterChartData.
 * @author Roman Kashitsyn
 */
public final class TestXSSFScatterChartData  extends TestCase {
 
	public void testOneSeriePlot() throws Exception {
		XSSFWorkbook wb = new XSSFWorkbook();
		XSSFSheet sheet = wb.createSheet();
		XSSFDrawing drawing = sheet.createDrawingPatriarch();
		XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 1, 1, 10, 30);
		XSSFChart chart = drawing.createChart(anchor);

    private static Object[][] plotData = new Object[][] {
	{1, 2, 3, 4, 5, 6, 7, 8, 9, 10},
	{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}
    };
    
    public void testOneSeriePlot() throws Exception {
	Workbook wb = new XSSFWorkbook();
	Sheet sheet = new SheetBuilder(wb, plotData).build();
	Drawing drawing = sheet.createDrawingPatriarch();
	ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 1, 1, 10, 30);
	Chart chart = drawing.createChart(anchor);

	ChartAxis bottomAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.BOTTOM);
	ChartAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);

	ScatterChartData scatterChartData =
	    chart.getChartDataFactory().createScatterChartData();
		ScatterChartSerie serie = scatterChartData.addSerie(xMarker, yMarker);

	DataMarker xMarker = new DataMarker(sheet, CellRangeAddress.valueOf("A1:A10"));
	DataMarker yMarker = new DataMarker(sheet, CellRangeAddress.valueOf("B1:B10"));
	ScatterChartSerie serie = scatterChartData.addSerie(xMarker, yMarker);

	assertEquals(1, scatterChartData.getSeries().size());
	}

	chart.plot(scatterChartData, bottomAxis, leftAxis);
    }

}




/* ====================================================================
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at
   
   http://www.apache.org/licenses/LICENSE-2.0
   
   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
   ==================================================================== */

package org.apache.poi.ss.util;

import java.util.Date;

import junit.framework.TestCase;

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 * Tests SheetBuilder.
 * @see org.apache.poi.ss.util.SheetBuilder
 */
public final class TestSheetBuilder extends TestCase {
    
    private static Object[][] testData = new Object[][] {
	{         1,     2,        3},
	{new Date(),  null,     null},
	{     "one", "two", "=A1+B2"}
    };

    public void testNotCreateEmptyCells() {
	Workbook wb = new HSSFWorkbook();
	Sheet sheet = new SheetBuilder(wb, testData).build();
	
	assertEquals(sheet.getPhysicalNumberOfRows(), 3);

	Row firstRow = sheet.getRow(0);
	Cell firstCell = firstRow.getCell(0);

	assertEquals(firstCell.getCellType(), Cell.CELL_TYPE_NUMERIC);
	assertEquals(1.0, firstCell.getNumericCellValue(), 0.00001);
	

	Row secondRow = sheet.getRow(1);
	assertNotNull(secondRow.getCell(0));
	assertNull(secondRow.getCell(2));

	Row thirdRow = sheet.getRow(2);
	assertEquals(Cell.CELL_TYPE_STRING, thirdRow.getCell(0).getCellType());
	String cellValue = thirdRow.getCell(0).getStringCellValue();
	assertEquals(testData[2][0].toString(), cellValue);
	
	assertEquals(Cell.CELL_TYPE_FORMULA, thirdRow.getCell(2).getCellType());
	assertEquals("A1+B2", thirdRow.getCell(2).getCellFormula());
    }

    public void testEmptyCells() {
	Workbook wb = new HSSFWorkbook();
	Sheet sheet = new SheetBuilder(wb, testData).setCreateEmptyCells(true).build();
	
	Cell emptyCell = sheet.getRow(1).getCell(1);
	assertNotNull(emptyCell);
	assertEquals(Cell.CELL_TYPE_BLANK, emptyCell.getCellType());
    }

}




/* ====================================================================
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at
   
   http://www.apache.org/licenses/LICENSE-2.0
   
   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
   ==================================================================== */
package org.apache.poi.ss.util.cellwalk;

import junit.framework.TestCase;

import java.util.Date;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.SheetBuilder;

public class TestCellWalk extends TestCase {

    private static Object[][] testData = new Object[][] {
	{   1,          2,  null},
	{null, new Date(),  null},
	{null,       null, "str"}
    };

    private CountCellHandler countCellHandler = new CountCellHandler();

    public void testNotTraverseEmptyCells() {
	Workbook wb = new HSSFWorkbook();
	Sheet sheet = new SheetBuilder(wb, testData).build();
	CellRangeAddress range = CellRangeAddress.valueOf("A1:C3");
	
	CellWalk cellWalk = new CellWalk(sheet, range);
	countCellHandler.reset();
	cellWalk.traverse(countCellHandler);

	assertEquals(4, countCellHandler.getVisitedCellsNumber());
	/* 1 + 2 + 5 + 9 */
	assertEquals(17L, countCellHandler.getOrdinalNumberSum());
    }


    private static class CountCellHandler implements CellHandler {

	private int cellsVisited = 0;
	private long ordinalNumberSum = 0L;

	public void onCell(Cell cell, CellWalkContext ctx) {
	    ++cellsVisited;
	    ordinalNumberSum += ctx.getOrdinalNumber();
	}

	public int getVisitedCellsNumber() {
	    return cellsVisited;
	}

	public long getOrdinalNumberSum() {
	    return ordinalNumberSum;
	}

	public void reset() {
	    cellsVisited = 0;
	    ordinalNumberSum = 0L;
	}
    }
}

Return to bug 51196

Line 0 Link Here

(-)src/java/org/apache/poi/ss/util/cellwalk/CellWalk.java (+129 lines)
		1	/* ====================================================================
		2	Licensed to the Apache Software Foundation (ASF) under one or more
		3	contributor license agreements. See the NOTICE file distributed with
		4	this work for additional information regarding copyright ownership.
		5	The ASF licenses this file to You under the Apache License, Version 2.0
		6	(the "License"); you may not use this file except in compliance with
		7	the License. You may obtain a copy of the License at
		8
		9	http://www.apache.org/licenses/LICENSE-2.0
		10
		11	Unless required by applicable law or agreed to in writing, software
		12	distributed under the License is distributed on an "AS IS" BASIS,
		13	WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
		14	See the License for the specific language governing permissions and
		15	limitations under the License.
		16	==================================================================== */
		17
		18	package org.apache.poi.ss.util.cellwalk;
		19
		20
		21	import org.apache.poi.ss.usermodel.Cell;
		22	import org.apache.poi.ss.usermodel.Row;
		23	import org.apache.poi.ss.usermodel.Sheet;
		24	import org.apache.poi.ss.util.CellRangeAddress;
		25	import org.apache.poi.ss.util.DataMarker;
		26
		27	/**
		28	* Traverse cell range.
		29	*
		30	* @author Roman Kashitsyn
		31	*/
		32	public class CellWalk {
		33
		34	private Sheet sheet;
		35	private CellRangeAddress range;
		36	private boolean traverseEmptyCells;
		37
		38	public CellWalk(DataMarker dm) {
		39	this(dm.getSheet(), dm.getRange());
		40	}
		41
		42	public CellWalk(Sheet sheet, CellRangeAddress range) {
		43	this.sheet = sheet;
		44	this.range = range;
		45	this.traverseEmptyCells = false;
		46	}
		47
		48	/**
		49	* Should we call handler on empty (blank) cells. Default is
		50	* false.
		51	* @return true if handler should be called on empty (blank)
		52	* cells, false otherwise.
		53	*/
		54	public boolean isTraverseEmptyCells() {
		55	return traverseEmptyCells;
		56	}
		57
		58	/**
		59	* Sets the traverseEmptyCells property.
		60	* @param traverseEmptyCells new property value
		61	*/
		62	public void setTraverseEmptyCells(boolean traverseEmptyCells) {
		63	this.traverseEmptyCells = traverseEmptyCells;
		64	}
65
66	/**
67	* Traverse cell range from top left to bottom right cell.
68	* @param handler handler to call on each appropriate cell
69	*/
70	public void traverse(CellHandler handler) {
71	int firstRow = range.getFirstRow();
72	int lastRow = range.getLastRow();
73	int firstColumn = range.getFirstColumn();
74	int lastColumn = range.getLastColumn();
75	final int width = lastColumn - firstColumn + 1;
76	SimpleCellWalkContext ctx = new SimpleCellWalkContext();
77	Row currentRow = null;
78	Cell currentCell = null;
79
80	for (ctx.rowNumber = firstRow; ctx.rowNumber <= lastRow; ++ctx.rowNumber) {
81	currentRow = sheet.getRow(ctx.rowNumber);
82	if (currentRow == null) {
83	continue;
84	}
85	for (ctx.colNumber = firstColumn; ctx.colNumber <= lastColumn; ++ctx.colNumber) {
86	currentCell = currentRow.getCell(ctx.colNumber);
87
88	if (currentCell == null) {
89	continue;
90	}
91	if (isEmpty(currentCell) && !traverseEmptyCells) {
92	continue;
93	}
94
95	ctx.ordinalNumber =
96	(ctx.rowNumber - firstRow) * width +
97	(ctx.colNumber - firstColumn + 1);
98
99	handler.onCell(currentCell, ctx);
100	}
101	}
102	}
103
104	private boolean isEmpty(Cell cell) {
105	return (cell.getCellType() == Cell.CELL_TYPE_BLANK);
106	}
107
108	/**
109	* Inner class to hold walk context.
110	* @author Roman Kashitsyn
111	*/
112	private class SimpleCellWalkContext implements CellWalkContext {
113	public long ordinalNumber = 0;
114	public int rowNumber = 0;
115	public int colNumber = 0;
116
117	public long getOrdinalNumber() {
118	return ordinalNumber;
119	}
120
121	public int getRowNumber() {
122	return rowNumber;
123	}
124
125	public int getColumnNumber() {
126	return colNumber;
127	}
128	}
129	}

Line 0 Link Here

(-)src/ooxml/java/org/apache/poi/xssf/usermodel/charts/XSSFNumberCache.java (+150 lines)
		1	/* ====================================================================
		2	Licensed to the Apache Software Foundation (ASF) under one or more
		3	contributor license agreements. See the NOTICE file distributed with
		4	this work for additional information regarding copyright ownership.
		5	The ASF licenses this file to You under the Apache License, Version 2.0
		6	(the "License"); you may not use this file except in compliance with
		7	the License. You may obtain a copy of the License at
		8
		9	http://www.apache.org/licenses/LICENSE-2.0
		10
		11	Unless required by applicable law or agreed to in writing, software
		12	distributed under the License is distributed on an "AS IS" BASIS,
		13	WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
		14	See the License for the specific language governing permissions and
		15	limitations under the License.
		16	==================================================================== */
		17
		18	package org.apache.poi.xssf.usermodel.charts;
		19
		20	import org.apache.poi.ss.usermodel.Cell;
		21	import org.apache.poi.ss.usermodel.Row;
		22	import org.apache.poi.ss.usermodel.Sheet;
		23	import org.apache.poi.ss.usermodel.FormulaEvaluator;
		24	import org.apache.poi.ss.usermodel.CellValue;
		25	import org.apache.poi.ss.usermodel.Workbook;
		26	import org.apache.poi.ss.util.CellRangeAddress;
		27	import org.apache.poi.ss.util.NumberToTextConverter;
		28	import org.apache.poi.ss.util.DataMarker;
		29	import org.apache.poi.ss.util.cellwalk.CellWalk;
		30	import org.apache.poi.ss.util.cellwalk.CellHandler;
		31	import org.apache.poi.ss.util.cellwalk.CellWalkContext;
		32	import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef;
		33	import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumData;
		34	import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumVal;
		35	import org.openxmlformats.schemas.drawingml.x2006.chart.CTUnsignedInt;
		36
		37	/**
		38	* Package private class to fill chart's number reference with cached
		39	* numeric values. If a formula-typed cell referenced by data marker,
		40	* cell's value will be calculated and placed to cache. Numeric cells
		41	* will be placed to cache as is. Non-numeric cells will be ignored.
		42	*
		43	* @author Roman Kashitsyn
		44	*/
		45	class XSSFNumberCache {
		46
		47	private CTNumData ctNumData;
		48
		49	XSSFNumberCache(CTNumData ctNumData) {
		50	this.ctNumData = ctNumData;
		51	}
		52
		53	/**
		54	* Builds new numeric cache container.
		55	* @param marker data marker to use for cache evaluation
		56	* @param ctNumRef parent number reference
		57	* @return numeric cache instance
		58	*/
		59	static XSSFNumberCache buildCache(DataMarker marker, CTNumRef ctNumRef) {
		60	CellRangeAddress range = marker.getRange();
		61	int numOfPoints = range.getNumberOfCells();
		62
		63	if (numOfPoints == 0) {
		64	// Nothing to do.
65	return null;
66	}
67
68	XSSFNumberCache cache = new XSSFNumberCache(ctNumRef.addNewNumCache());
69	cache.setPointCount(numOfPoints);
70
71	Workbook wb = marker.getSheet().getWorkbook();
72	FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
73
74	CellWalk cellWalk = new CellWalk(marker);
75	NumCacheCellHandler numCacheHandler = cache.new NumCacheCellHandler(evaluator);
76	cellWalk.traverse(numCacheHandler);
77	return cache;
78	}
79
80	/**
81	* Returns total count of points in cache. Some (or even all) of
82	* them might be empty.
83	* @return total count of points in cache
84	*/
85	long getPointCount() {
86	CTUnsignedInt pointCount = ctNumData.getPtCount();
87	if (pointCount != null) {
88	return pointCount.getVal();
89	} else {
90	return 0L;
91	}
92	}
93
94	/**
95	* Returns cache value at specified index.
96	* @param index index of the point in cache
97	* @return point value
98	*/
99	double getValueAt(int index) {
100	/* TODO: consider more effective algorithm. Left as is since
101	* this method should be invoked mostly in tests. */
102	for (CTNumVal pt : ctNumData.getPtList()) {
103	if (pt.getIdx() == index) {
104	return Double.valueOf(pt.getV()).doubleValue();
105	}
106	}
107	return 0.0;
108	}
109
110	private void setPointCount(int numOfPoints) {
111	ctNumData.addNewPtCount().setVal(numOfPoints);
112	}
113
114	private class NumCacheCellHandler implements CellHandler {
115
116	private FormulaEvaluator evaluator;
117
118	public NumCacheCellHandler(FormulaEvaluator evaluator) {
119	this.evaluator = evaluator;
120	}
121
122	public void onCell(Cell cell, CellWalkContext ctx) {
123	double pointValue = getOrEvalCellValue(cell);
124	/* Silently ignore non-numeric values.
125	* This is Office default behaviour. */
126	if (Double.isNaN(pointValue)) {
127	return;
128	}
129
130	CTNumVal point = ctNumData.addNewPt();
131	point.setIdx(ctx.getOrdinalNumber());
132	point.setV(NumberToTextConverter.toText(pointValue));
133	}
134
135	private double getOrEvalCellValue(Cell cell) {
136	int cellType = cell.getCellType();
137
138	if (cellType == Cell.CELL_TYPE_NUMERIC) {
139	return cell.getNumericCellValue();
140	} else if (cellType == Cell.CELL_TYPE_FORMULA) {
141	CellValue value = evaluator.evaluate(cell);
142	if (value.getCellType() == Cell.CELL_TYPE_NUMERIC) {
143	return value.getNumberValue();
144	}
145	}
146	return Double.NaN;
147	}
148
149	}
150	}

Lines 6-19 Link Here

(-)src/ooxml/java/org/apache/poi/xssf/usermodel/charts/XSSFScatterChartData.java (-71 / +102 lines)
6	(the "License"); you may not use this file except in compliance with	6	(the "License"); you may not use this file except in compliance with
7	the License. You may obtain a copy of the License at	7	the License. You may obtain a copy of the License at
8		8
9	http://www.apache.org/licenses/LICENSE-2.0	9	http://www.apache.org/licenses/LICENSE-2.0
10		10
11	Unless required by applicable law or agreed to in writing, software	11	Unless required by applicable law or agreed to in writing, software
12	distributed under the License is distributed on an "AS IS" BASIS,	12	distributed under the License is distributed on an "AS IS" BASIS,
13	WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.	13	WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14	See the License for the specific language governing permissions and	14	See the License for the specific language governing permissions and
15	limitations under the License.	15	limitations under the License.
16	==================================================================== */	16	==================================================================== */
17		17
18	package org.apache.poi.xssf.usermodel.charts;	18	package org.apache.poi.xssf.usermodel.charts;
19		19
Lines 53-145 Link Here
53	*/	53	*/
54	public class XSSFScatterChartData implements ScatterChartData {	54	public class XSSFScatterChartData implements ScatterChartData {
55		55
56	/**	56	/**
57	* List of all data series.	57	* List of all data series.
58	*/	58	*/
59	private List<Serie> series;	59	private List<Serie> series;
60		60
61	public XSSFScatterChartData() {	61	public XSSFScatterChartData() {
62	series = new ArrayList<Serie>();	62	series = new ArrayList<Serie>();
		63	}
		64
		65	/**
		66	* Package private ScatterChartSerie implementation.
		67	*/
		68	static class Serie implements ScatterChartSerie {
		69	private int id;
		70	private int order;
		71	private boolean useCache;
		72	private DataMarker xMarker;
		73	private DataMarker yMarker;
		74	private XSSFNumberCache lastCaclulatedXCache;
		75	private XSSFNumberCache lastCalculatedYCache;
		76
		77	protected Serie(int id, int order) {
		78	super();
		79	this.id = id;
		80	this.order = order;
		81	this.useCache = true;
63	}	82	}
64		83
65	public static class Serie implements ScatterChartSerie {	84	public void setXValues(DataMarker marker) {
66	private int id;	85	xMarker = marker;
67	private int order;	86	}
68	private boolean useCache;
69	private DataMarker xMarker;
70	private DataMarker yMarker;
71		87
72	public Serie(int id, int order) {	88	public void setYValues(DataMarker marker) {
73	super();	89	yMarker = marker;
74	this.id = id;	90	}
75	this.order = order;
76	this.useCache = false;
77	}
78		91
79	public void setXValues(DataMarker marker) {	92	/**
80	xMarker = marker;	93	* @param useCache if true, cached results will be added on plot
81	}	94	*/
		95	public void setUseCache(boolean useCache) {
		96	this.useCache = useCache;
		97	}
82		98
83	public void setYValues(DataMarker marker) {	99	/**
84	yMarker = marker;	100	* Returns last calculated number cache for X axis.
85	}	101	* @return last calculated number cache for X axis.
		102	*/
		103	XSSFNumberCache getLastCaculatedXCache() {
		104	return lastCaclulatedXCache;
		105	}
86		106
87	/**	107	/**
88	* @param useCache if true, cached results will be added on plot	108	* Returns last calculated number cache for Y axis.
89	*/	109	* @return last calculated number cache for Y axis.
90	public void setUseCache(boolean useCache) {	110	*/
91	this.useCache = useCache;	111	XSSFNumberCache getLastCalculatedYCache() {
92	}	112	return lastCalculatedYCache;
		113	}
93		114
94	protected void addToChart(CTScatterChart ctScatterChart) {	115	protected void addToChart(CTScatterChart ctScatterChart) {
95	CTScatterSer scatterSer = ctScatterChart.addNewSer();	116	CTScatterSer scatterSer = ctScatterChart.addNewSer();
96	scatterSer.addNewIdx().setVal(this.id);	117	scatterSer.addNewIdx().setVal(this.id);
97	scatterSer.addNewOrder().setVal(this.order);	118	scatterSer.addNewOrder().setVal(this.order);
98		119
99	CTAxDataSource xVal = scatterSer.addNewXVal();	120	/* TODO: add some logic to automatically recognize cell
100	CTNumRef numRef = xVal.addNewNumRef();	121	* types and choose appropriate data representation for
101	numRef.setF(xMarker.formatAsString());	122	* X axis.
		123	*/
		124	CTAxDataSource xVal = scatterSer.addNewXVal();
		125	CTNumRef xNumRef = xVal.addNewNumRef();
		126	xNumRef.setF(xMarker.formatAsString());
102		127
103	CTNumDataSource yVal = scatterSer.addNewYVal();	128	CTNumDataSource yVal = scatterSer.addNewYVal();
104	numRef = yVal.addNewNumRef();	129	CTNumRef yNumRef = yVal.addNewNumRef();
105	numRef.setF(yMarker.formatAsString());	130	yNumRef.setF(yMarker.formatAsString());
106	}
107	}
108		131
109	public XSSFScatterChartData.Serie addSerie(DataMarker xMarker, DataMarker yMarker) {	132	if (useCache) {
110	int numOfSeries = series.size();	133	/* We can not store cache since markers are not immutable */
111	Serie newSerie = new Serie(numOfSeries, numOfSeries);	134	XSSFNumberCache.buildCache(xMarker, xNumRef);
112	newSerie.setXValues(xMarker);	135	lastCalculatedYCache = XSSFNumberCache.buildCache(yMarker, yNumRef);
113	newSerie.setYValues(yMarker);	136	}
114	series.add(newSerie);
115	return newSerie;
116	}	137	}
		138	}
117		139
118	public void fillChart(Chart chart, ChartAxis... axis) {	140	public ScatterChartSerie addSerie(DataMarker xMarker, DataMarker yMarker) {
119	if (!(chart instanceof XSSFChart)) {	141	int numOfSeries = series.size();
120	throw new IllegalArgumentException("Chart must be instance of XSSFChart");	142	Serie newSerie = new Serie(numOfSeries, numOfSeries);
121	}	143	newSerie.setXValues(xMarker);
		144	newSerie.setYValues(yMarker);
		145	series.add(newSerie);
		146	return newSerie;
		147	}
122		148
123	XSSFChart xssfChart = (XSSFChart) chart;	149	public void fillChart(Chart chart, ChartAxis... axis) {
124	CTPlotArea plotArea = xssfChart.getCTChart().getPlotArea();	150	if (!(chart instanceof XSSFChart)) {
125	CTScatterChart scatterChart = plotArea.addNewScatterChart();	151	throw new IllegalArgumentException("Chart must be instance of XSSFChart");
126	addStyle(scatterChart);	152	}
127		153
128	for (Serie s : series) {	154	XSSFChart xssfChart = (XSSFChart) chart;
129	s.addToChart(scatterChart);	155	CTPlotArea plotArea = xssfChart.getCTChart().getPlotArea();
130	}	156	CTScatterChart scatterChart = plotArea.addNewScatterChart();
		157	addStyle(scatterChart);
131		158
132	for (ChartAxis ax : axis) {	159	for (Serie s : series) {
133	scatterChart.addNewAxId().setVal(ax.getId());	160	s.addToChart(scatterChart);
134	}
135	}	161	}
136		162
137	public List<? extends Serie> getSeries() {	163	for (ChartAxis ax : axis) {
138	return series;	164	scatterChart.addNewAxId().setVal(ax.getId());
139	}	165	}
		166	}
140		167
141	private void addStyle(CTScatterChart ctScatterChart) {	168	public List<? extends Serie> getSeries() {
142	CTScatterStyle scatterStyle = ctScatterChart.addNewScatterStyle();	169	return series;
143	scatterStyle.setVal(STScatterStyle.LINE_MARKER);	170	}
144	}	171
		172	private void addStyle(CTScatterChart ctScatterChart) {
		173	CTScatterStyle scatterStyle = ctScatterChart.addNewScatterStyle();
		174	scatterStyle.setVal(STScatterStyle.LINE_MARKER);
		175	}
145	}	176	}

Line 0 Link Here

(-)src/java/org/apache/poi/ss/util/SheetBuilder.java (+139 lines)
		1	/* ====================================================================
		2	Licensed to the Apache Software Foundation (ASF) under one or more
		3	contributor license agreements. See the NOTICE file distributed with
		4	this work for additional information regarding copyright ownership.
		5	The ASF licenses this file to You under the Apache License, Version 2.0
		6	(the "License"); you may not use this file except in compliance with
		7	the License. You may obtain a copy of the License at
		8
		9	http://www.apache.org/licenses/LICENSE-2.0
		10
		11	Unless required by applicable law or agreed to in writing, software
		12	distributed under the License is distributed on an "AS IS" BASIS,
		13	WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
		14	See the License for the specific language governing permissions and
		15	limitations under the License.
		16	==================================================================== */
		17	package org.apache.poi.ss.util;
		18
		19	import java.util.Calendar;
		20	import java.util.Date;
		21
		22	import org.apache.poi.ss.usermodel.Sheet;
		23	import org.apache.poi.ss.usermodel.Workbook;
		24	import org.apache.poi.ss.usermodel.Row;
		25	import org.apache.poi.ss.usermodel.Cell;
		26
		27	/**
		28	* Class that provides useful sheet build capabilities. It can be used
		29	* in test cases to improve readability or in Swing applications with
		30	* tables.
		31	*
		32	* @author Roman Kashitsyn
		33	*/
		34	public class SheetBuilder {
		35
		36	private Workbook workbook;
		37	private Object[][] cells;
		38	private boolean shouldCreateEmptyCells = false;
		39
		40	public SheetBuilder(Workbook workbook, Object[][] cells) {
		41	this.workbook = workbook;
		42	this.cells = cells;
		43	}
		44
		45	/**
		46	* @return true if null objects should be trated as empty cells
		47	* false otherwise
		48	*/
		49	public boolean getCreateEmptyCells() {
		50	return shouldCreateEmptyCells;
		51	}
		52
		53	/**
		54	* @param shouldCreateEmptyCells true if null array elements should be
		55	* trated as empty cells
		56	* @return this
		57	*/
		58	public SheetBuilder setCreateEmptyCells(boolean shouldCreateEmptyCells) {
		59	this.shouldCreateEmptyCells = shouldCreateEmptyCells;
		60	return this;
		61	}
		62
		63	/**
		64	* Builds sheet from parent workbook and 2D array with cell
65	* values. Creates rows anyway (even if row contains only null
66	* cells), creates cells only if corresponding property is true.
67	* The conversion is performed in the following way:
68	*
69	* <ul>
70	* <li>Numbers become numeric cells.</li>
71	* <li><code>java.util.Date</code> or <code>java.util.Calendar</code>
72	* instances become date cells.</li>
73	* <li>String with leading '=' char become formulas (leading '='
74	* trancated).</li>
75	* <li>Other objects become strings via <code>Object.toString()</code>
76	* method.</li>
77	* </ul>
78	*
79	* @return newly created sheet
80	*/
81	public Sheet build() {
82	Sheet sheet = workbook.createSheet();
83	Row currentRow = null;
84	Cell currentCell = null;
85
86	for (int rowIndex = 0; rowIndex < cells.length; ++rowIndex) {
87	Object[] rowArray = cells[rowIndex];
88	currentRow = sheet.createRow(rowIndex);
89
90	for (int cellIndex = 0; cellIndex < rowArray.length; ++cellIndex) {
91	Object cellValue = rowArray[cellIndex];
92	if (cellValue != null \|\| shouldCreateEmptyCells) {
93	currentCell = currentRow.createCell(cellIndex);
94	setCellValue(currentCell, cellValue);
95	}
96	}
97	}
98	return sheet;
99	}
100
101	/**
102	* Sets the cell value using object type information.
103	* @param cell cell to change
104	* @param value value to set
105	*/
106	public void setCellValue(Cell cell, Object value) {
107	if (value == null \|\| cell == null) {
108	return;
109	} else if (value instanceof Number) {
110	double doubleValue = ((Number) value).doubleValue();
111	cell.setCellValue(doubleValue);
112	} else if (value instanceof Date) {
113	cell.setCellValue((Date) value);
114	} else if (value instanceof Calendar) {
115	cell.setCellValue((Calendar) value);
116	} else if (isFormulaDefinition(value)) {
117	cell.setCellFormula(getFormula(value));
118	} else {
119	cell.setCellValue(value.toString());
120	}
121	}
122
123	private boolean isFormulaDefinition(Object obj) {
124	if (obj instanceof String) {
125	String str = (String) obj;
126	if (str.length() < 2) {
127	return false;
128	} else {
129	return ((String) obj).charAt(0) == '=';
130	}
131	} else {
132	return false;
133	}
134	}
135
136	private String getFormula(Object obj) {
137	return ((String) obj).substring(1);
138	}
139	}

Line 0 Link Here

(-)src/ooxml/testcases/org/apache/poi/xssf/usermodel/charts/TestXSSFNumberCache.java (+64 lines)
	1	/* ====================================================================
	2	Licensed to the Apache Software Foundation (ASF) under one or more
	3	contributor license agreements. See the NOTICE file distributed with
	4	this work for additional information regarding copyright ownership.
	5	The ASF licenses this file to You under the Apache License, Version 2.0
	6	(the "License"); you may not use this file except in compliance with
	7	the License. You may obtain a copy of the License at
	8
	9	http://www.apache.org/licenses/LICENSE-2.0
	10
	11	Unless required by applicable law or agreed to in writing, software
	12	distributed under the License is distributed on an "AS IS" BASIS,
	13	WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
	14	See the License for the specific language governing permissions and
	15	limitations under the License.
	16	==================================================================== */
	17	package org.apache.poi.xssf.usermodel.charts;
	18
	19	import org.apache.poi.ss.usermodel.*;
	20	import org.apache.poi.ss.util.CellRangeAddress;
	21	import org.apache.poi.ss.util.DataMarker;
	22	import org.apache.poi.ss.util.SheetBuilder;
	23	import org.apache.poi.ss.usermodel.charts.*;
	24	import org.apache.poi.xssf.usermodel.XSSFWorkbook;
	25
	26
	27	import junit.framework.TestCase;
	28
	29	public class TestXSSFNumberCache extends TestCase {
	30	private static Object[][] plotData = {
	31	{0, 1, 2, 3, 4},
	32	{0, "=B12", "=C12", "=D12", "=E14"}
	33	};
	34
	35	public void testFormulaCache() {
	36	Workbook wb = new XSSFWorkbook();
	37	Sheet sheet = new SheetBuilder(wb, plotData).build();
	38	Drawing drawing = sheet.createDrawingPatriarch();
	39	ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 1, 1, 10, 30);
	40	Chart chart = drawing.createChart(anchor);
	41
	42	ChartAxis bottomAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.BOTTOM);
	43	ChartAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);
	44
	45	ScatterChartData scatterChartData =
	46	chart.getChartDataFactory().createScatterChartData();
	47
	48	DataMarker xMarker = new DataMarker(sheet, CellRangeAddress.valueOf("A1:E1"));
	49	DataMarker yMarker = new DataMarker(sheet, CellRangeAddress.valueOf("A2:E2"));
	50	ScatterChartSerie serie = scatterChartData.addSerie(xMarker, yMarker);
	51
	52	chart.plot(scatterChartData, bottomAxis, leftAxis);
	53
	54	XSSFScatterChartData.Serie xssfScatterSerie =
	55	(XSSFScatterChartData.Serie) serie;
	56	XSSFNumberCache yCache = xssfScatterSerie.getLastCalculatedYCache();
	57
	58	assertEquals(5, yCache.getPointCount());
	59	assertEquals(4.0, yCache.getValueAt(3), 0.00001);
	60	assertEquals(16.0, yCache.getValueAt(5), 0.00001);
	61	}
	62
	63
	64	}

Line 0 Link Here

(-)src/testcases/org/apache/poi/ss/util/TestSheetBuilder.java (+77 lines)
		1	/* ====================================================================
		2	Licensed to the Apache Software Foundation (ASF) under one or more
		3	contributor license agreements. See the NOTICE file distributed with
		4	this work for additional information regarding copyright ownership.
		5	The ASF licenses this file to You under the Apache License, Version 2.0
		6	(the "License"); you may not use this file except in compliance with
		7	the License. You may obtain a copy of the License at
		8
		9	http://www.apache.org/licenses/LICENSE-2.0
		10
		11	Unless required by applicable law or agreed to in writing, software
		12	distributed under the License is distributed on an "AS IS" BASIS,
		13	WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
		14	See the License for the specific language governing permissions and
		15	limitations under the License.
		16	==================================================================== */
		17
		18	package org.apache.poi.ss.util;
		19
		20	import java.util.Date;
		21
		22	import junit.framework.TestCase;
		23
		24	import org.apache.poi.ss.usermodel.Workbook;
		25	import org.apache.poi.ss.usermodel.Sheet;
		26	import org.apache.poi.ss.usermodel.Row;
		27	import org.apache.poi.ss.usermodel.Cell;
		28	import org.apache.poi.hssf.usermodel.HSSFWorkbook;
		29
		30	/**
		31	* Tests SheetBuilder.
		32	* @see org.apache.poi.ss.util.SheetBuilder
		33	*/
		34	public final class TestSheetBuilder extends TestCase {
		35
		36	private static Object[][] testData = new Object[][] {
		37	{ 1, 2, 3},
		38	{new Date(), null, null},
		39	{ "one", "two", "=A1+B2"}
		40	};
		41
		42	public void testNotCreateEmptyCells() {
		43	Workbook wb = new HSSFWorkbook();
		44	Sheet sheet = new SheetBuilder(wb, testData).build();
		45
		46	assertEquals(sheet.getPhysicalNumberOfRows(), 3);
		47
		48	Row firstRow = sheet.getRow(0);
		49	Cell firstCell = firstRow.getCell(0);
		50
		51	assertEquals(firstCell.getCellType(), Cell.CELL_TYPE_NUMERIC);
		52	assertEquals(1.0, firstCell.getNumericCellValue(), 0.00001);
		53
		54
		55	Row secondRow = sheet.getRow(1);
		56	assertNotNull(secondRow.getCell(0));
		57	assertNull(secondRow.getCell(2));
		58
		59	Row thirdRow = sheet.getRow(2);
		60	assertEquals(Cell.CELL_TYPE_STRING, thirdRow.getCell(0).getCellType());
		61	String cellValue = thirdRow.getCell(0).getStringCellValue();
		62	assertEquals(testData[2][0].toString(), cellValue);
		63
		64	assertEquals(Cell.CELL_TYPE_FORMULA, thirdRow.getCell(2).getCellType());
65	assertEquals("A1+B2", thirdRow.getCell(2).getCellFormula());
66	}
67
68	public void testEmptyCells() {
69	Workbook wb = new HSSFWorkbook();
70	Sheet sheet = new SheetBuilder(wb, testData).setCreateEmptyCells(true).build();
71
72	Cell emptyCell = sheet.getRow(1).getCell(1);
73	assertNotNull(emptyCell);
74	assertEquals(Cell.CELL_TYPE_BLANK, emptyCell.getCellType());
75	}
76
77	}