Attachment #27445 for bug #51196

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





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

    public void testSheetName() {
        final String sheetName = "TEST SHEET NAME";
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = new SheetBuilder(wb, testData).setSheetName(sheetName).build();
        assertEquals(sheetName, sheet.getSheetName());
    }
}




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

    }
}




/* ====================================================================
   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.usermodel.charts;

import junit.framework.TestCase;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.SheetBuilder;

/**
 * Tests for {@link org.apache.poi.ss.usermodel.charts.DataSources}.
 *
 * @author Roman Kashitsyn
 */
public class TestDataSources extends TestCase {

    private static final Object[][] numericCells = {
            {0.0,      1.0,       2.0,     3.0,      4.0},
            {0.0, "=B1*2",  "=C1*2", "=D1*2", "=E1*2"}
    };

    private static final Object[][] stringCells = {
            {  1,    2,    3,   4,    5},
            {"A", "B", "C", "D", "E"}
    };

    private static final Object[][] mixedCells = {
            {1.0, "2.0", 3.0, "4.0", 5.0, "6.0"}
    };

    public void testNumericArrayDataSource() {
        Double[] doubles = new Double[]{1.0, 2.0, 3.0, 4.0, 5.0};
        ChartDataSource<Double> doubleDataSource = DataSources.fromArray(doubles);
        assertTrue(doubleDataSource.isNumeric());
        assertFalse(doubleDataSource.isReference());
        assertDataSourceIsEqualToArray(doubleDataSource, doubles);
    }

    public void testStringArrayDataSource() {
        String[] strings = new String[]{"one", "two", "three", "four", "five"};
        ChartDataSource<String> stringDataSource = DataSources.fromArray(strings);
        assertFalse(stringDataSource.isNumeric());
        assertFalse(stringDataSource.isReference());
        assertDataSourceIsEqualToArray(stringDataSource, strings);
    }

    public void testNumericCellDataSource() {
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = new SheetBuilder(wb, numericCells).build();
        CellRangeAddress numCellRange = CellRangeAddress.valueOf("A2:E2");
        ChartDataSource<Number> numDataSource = DataSources.fromNumericCellRange(sheet, numCellRange);
        assertTrue(numDataSource.isReference());
        assertTrue(numDataSource.isNumeric());
        assertEquals(numericCells[0].length, numDataSource.getPointCount());
        for (int i = 0; i < numericCells[0].length; ++i) {
            assertEquals(((Number) numericCells[0][i]).doubleValue() * 2,
                    numDataSource.getPointAt(i).doubleValue(), 0.00001);
        }
    }

    public void testStringCellDataSource() {
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = new SheetBuilder(wb, stringCells).build();
        CellRangeAddress numCellRange = CellRangeAddress.valueOf("A2:E2");
        ChartDataSource<String> numDataSource = DataSources.fromStringCellRange(sheet, numCellRange);
        assertTrue(numDataSource.isReference());
        assertFalse(numDataSource.isNumeric());
        assertEquals(numericCells[0].length, numDataSource.getPointCount());
        for (int i = 0; i < stringCells[1].length; ++i) {
            assertEquals(stringCells[1][i], numDataSource.getPointAt(i));
        }
    }

    public void testMixedCellDataSource() {
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = new SheetBuilder(wb, mixedCells).build();
        CellRangeAddress mixedCellRange = CellRangeAddress.valueOf("A1:F1");
        ChartDataSource<String> strDataSource = DataSources.fromStringCellRange(sheet, mixedCellRange);
        ChartDataSource<Number> numDataSource = DataSources.fromNumericCellRange(sheet, mixedCellRange);
        for (int i = 0; i < mixedCells[0].length; ++i) {
            if (i % 2 == 0) {
                assertNull(strDataSource.getPointAt(i));
                assertEquals(((Number) mixedCells[0][i]).doubleValue(),
                        numDataSource.getPointAt(i).doubleValue(), 0.00001);
            } else {
                assertNull(numDataSource.getPointAt(i));
                assertEquals(mixedCells[0][i], strDataSource.getPointAt(i));
            }
        }
    }

    public void testIOBExceptionOnInvalidIndex() {
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = new SheetBuilder(wb, numericCells).build();
        CellRangeAddress rangeAddress = CellRangeAddress.valueOf("A2:E2");
        ChartDataSource<Number> numDataSource = DataSources.fromNumericCellRange(sheet, rangeAddress);
        IndexOutOfBoundsException exception = null;
        try {
            numDataSource.getPointAt(-1);
        } catch (IndexOutOfBoundsException e) {
            exception = e;
        }
        assertNotNull(exception);

        exception = null;
        try {
            numDataSource.getPointAt(numDataSource.getPointCount());
        } catch (IndexOutOfBoundsException e) {
            exception = e;
        }
        assertNotNull(exception);
    }

    private <T> void assertDataSourceIsEqualToArray(ChartDataSource<T> ds, T[] array) {
        assertEquals(ds.getPointCount(), array.length);
        for (int i = 0; i < array.length; ++i) {
            assertEquals(ds.getPointAt(i), array[i]);
        }
    }
}




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

/**
 * A base for all charts data types.
 *
 * @author Roman Kashitsyn
 */
public interface ChartData {

	/**
	 * Fills a charts with data specified by implementation.
	 *
	 * @param chart a charts to fill in
	 * @param axis charts axis to use
	 */
	void fillChart(Chart chart, ChartAxis... axis);
}

Lines 31-37 Link Here

(-)src/ooxml/java/org/apache/poi/xssf/usermodel/charts/XSSFChartDataFactory.java (-1 / +1 lines)
31	}	31	}
32		32
33	/**	33	/**
34	* @return new scatter chart data instance	34	* @return new scatter charts data instance
35	*/	35	*/
36	public XSSFScatterChartData createScatterChartData() {	36	public XSSFScatterChartData createScatterChartData() {
37	return new XSSFScatterChartData();	37	return new XSSFScatterChartData();




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

/**
 * Class {@code SheetBuilder} provides an easy way of building workbook sheets
 * from 2D array of Objects. It can be used in test cases to improve code
 * readability or in Swing applications with tables.
 *
 * @author Roman Kashitsyn
 */
public class SheetBuilder {

    private final Workbook workbook;
    private final Object[][] cells;
    private boolean shouldCreateEmptyCells = false;
    private String sheetName = null;

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

    /**
     * Returns {@code true} if null array elements should be treated as empty
     * cells.
     *
     * @return {@code true} if null objects should be treated as empty cells
     *         and {@code false} otherwise
     */
    public boolean getCreateEmptyCells() {
        return shouldCreateEmptyCells;
    }

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

    /**
     * Specifies name of the sheet to build. If not specified, default name (provided by
     * workbook) will be used instead.
     * @param sheetName sheet name to use
     * @return {@code this}
     */
    public SheetBuilder setSheetName(String sheetName) {
        this.sheetName = sheetName;
        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 if either corresponding array value is not
     * null or createEmptyCells property is true.
     * The conversion is performed in the following way:
     * <p/>
     * <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 '='
     * will be truncated).</li>
     * <li>Other objects become strings via <code>Object.toString()</code>
     * method call.</li>
     * </ul>
     *
     * @return newly created sheet
     */
    public Sheet build() {
        Sheet sheet = (sheetName == null) ? workbook.createSheet() : workbook.createSheet(sheetName);
        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
     */
    private 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);
    }
}





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

import java.util.List;
import java.util.ArrayList;

import org.apache.poi.ss.usermodel.Chart;
import org.apache.poi.ss.usermodel.charts.ChartAxis;
import org.apache.poi.ss.usermodel.charts.ChartDataSource;
import org.apache.poi.ss.usermodel.charts.ScatterChartData;
import org.apache.poi.ss.usermodel.charts.ScatterChartSerie;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.*;

import java.util.ArrayList;
import java.util.List;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTScatterStyle;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTScatterSer;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumFmt;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTValAx;
import org.openxmlformats.schemas.drawingml.x2006.chart.STScatterStyle;
import org.openxmlformats.schemas.drawingml.x2006.chart.STCrosses;
import org.openxmlformats.schemas.drawingml.x2006.chart.STCrossBetween;
import org.openxmlformats.schemas.drawingml.x2006.chart.STOrientation;
import org.openxmlformats.schemas.drawingml.x2006.chart.STTickLblPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STAxPos;

import org.apache.poi.xssf.usermodel.XSSFChart;

/**
 * Represents DrawingML scatter charts.
 *
 * @author Roman Kashitsyn
 */

    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 ChartDataSource<?> xs;
        private ChartDataSource<? extends Number> ys;
	private DataMarker yMarker;
	private XSSFNumberCache lastCaclulatedXCache;
	private XSSFNumberCache lastCalculatedYCache;

        protected Serie(int id, int order,
                        ChartDataSource<?> xs,
                        ChartDataSource<? extends Number> ys) {
            super();
            this.id = id;
            this.order = order;
            this.xs = xs;
            this.ys = ys;
        }

        /**
	    xMarker = marker;
	}

	public void setYValues(DataMarker marker) {
	    yMarker = marker;
	}

	/**
         * Returns data source used for X axis values.
         * @return data source used for X axis values
         */
        public ChartDataSource<?> getXValues() {
            return xs;
        }

        /**
         * Returns data source used for Y axis values.
         * @return data source used for Y axis values
         */
        public ChartDataSource<? extends Number> getYValues() {
            return ys;
        }

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

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

            CTAxDataSource xVal = scatterSer.addNewXVal();
	     * types and choose appropriate data representation for
	     * X axis.
	     */
	    CTAxDataSource xVal = scatterSer.addNewXVal();
            XSSFChartUtil.buildAxDataSource(xVal, xs);
	    xNumRef.setF(xMarker.formatAsString());

            CTNumDataSource yVal = scatterSer.addNewYVal();
            XSSFChartUtil.buildNumDataSource(yVal, ys);
	    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);
        }
    }
    }

    public ScatterChartSerie addSerie(ChartDataSource<?> xs,
                                      ChartDataSource<? extends Number> ys) {
        if (!ys.isNumeric()) {
            throw new IllegalArgumentException("Y axis data source must be numeric.");
        }
        int numOfSeries = series.size();
        Serie newSerie = new Serie(numOfSeries, numOfSeries, xs, ys);
	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.ss.usermodel.charts;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * Class {@code DataSources} is a factory for {@link ChartDataSource} instances.
 *
 * @author Roman Kashitsyn
 */
public class DataSources {

    private DataSources() {
    }

    public static <T> ChartDataSource<T> fromArray(T[] elements) {
        return new ArrayDataSource<T>(elements);
    }

    public static ChartDataSource<Number> fromNumericCellRange(Sheet sheet, CellRangeAddress cellRangeAddress) {
        return new AbstractCellRangeDataSource<Number>(sheet, cellRangeAddress) {
            public Number getPointAt(int index) {
                CellValue cellValue = getCellValueAt(index);
                if (cellValue != null && cellValue.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    return Double.valueOf(cellValue.getNumberValue());
                } else {
                    return null;
                }
            }

            public boolean isNumeric() {
                return true;
            }
        };
    }

    public static ChartDataSource<String> fromStringCellRange(Sheet sheet, CellRangeAddress cellRangeAddress) {
        return new AbstractCellRangeDataSource<String>(sheet, cellRangeAddress) {
            public String getPointAt(int index) {
                CellValue cellValue = getCellValueAt(index);
                if (cellValue != null && cellValue.getCellType() == Cell.CELL_TYPE_STRING) {
                    return cellValue.getStringValue();
                } else {
                    return null;
                }
            }

            public boolean isNumeric() {
                return false;
            }
        };
    }

    private static class ArrayDataSource<T> implements ChartDataSource<T> {

        private final T[] elements;

        public ArrayDataSource(T[] elements) {
            this.elements = elements;
        }

        public int getPointCount() {
            return elements.length;
        }

        public T getPointAt(int index) {
            return elements[index];
        }

        public boolean isReference() {
            return false;
        }

        public boolean isNumeric() {
            Class<?> arrayComponentType = elements.getClass().getComponentType();
            return (Number.class.isAssignableFrom(arrayComponentType));
        }

        public String getFormulaString() {
            throw new UnsupportedOperationException("Literal data source can not be expressed by reference.");
        }
    }

    private abstract static class AbstractCellRangeDataSource<T> implements ChartDataSource<T> {
        private final Sheet sheet;
        private final CellRangeAddress cellRangeAddress;
        private final int numOfCells;
        private FormulaEvaluator evaluator;

        protected AbstractCellRangeDataSource(Sheet sheet, CellRangeAddress cellRangeAddress) {
            this.sheet = sheet;
            // Make copy since CellRangeAddress is mutable.
            this.cellRangeAddress = cellRangeAddress.copy();
            this.numOfCells = this.cellRangeAddress.getNumberOfCells();
            this.evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
        }

        public int getPointCount() {
            return numOfCells;
        }

        public boolean isReference() {
            return true;
        }

        public String getFormulaString() {
            return cellRangeAddress.formatAsString(sheet.getSheetName(), true);
        }

        protected CellValue getCellValueAt(int index) {
            if (index < 0 || index >= numOfCells) {
                throw new IndexOutOfBoundsException("Index must be between 0 and " +
                        (numOfCells - 1) + " (inclusive), given: " + index);
            }
            int firstRow = cellRangeAddress.getFirstRow();
            int firstCol = cellRangeAddress.getFirstColumn();
            int lastCol = cellRangeAddress.getLastColumn();
            int width = lastCol - firstCol + 1;
            int rowIndex = firstRow + index / width;
            int cellIndex = firstCol + index % width;
            Row row = sheet.getRow(rowIndex);
            return (row == null) ? null : evaluator.evaluate(row.getCell(cellIndex));
        }
    }
}




/* ====================================================================
   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.usermodel.charts;

/**
 * Represents data model of the charts.
 *
 * @param <T> type of points the data source contents
 * @author Roman Kashitsyn
 */
public interface ChartDataSource<T> {

    /**
     * Return number of points contained by data source.
     *
     * @return number of points contained by data source
     */
    int getPointCount();

    /**
     * Returns point value at specified index.
     *
     * @param index index to value from
     * @return point value at specified index.
     * @throws {@code IndexOutOfBoundsException} if index
     *                parameter not in range {@code 0 <= index <= pointCount}
     */
    T getPointAt(int index);

    /**
     * Returns {@code true} if charts data source is valid cell range.
     *
     * @return {@code true} if charts data source is valid cell range
     */
    boolean isReference();

    /**
     * Returns {@code true} if data source points should be treated as numbers.
     *
     * @return {@code true} if data source points should be treated as numbers
     */
    boolean isNumeric();

    /**
     * Returns formula representation of the data source. It is only applicable
     * for data source that is valid cell range.
     *
     * @return formula representation of the data source
     * @throws {@code UnsupportedOperationException} if the data source is not a
     *                reference.
     */
    String getFormulaString();
}

Lines 332-338 Link Here

(-)src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java (-1 / +1 lines)
332	}	332	}
333		333
334	/**	334	/**
335	* Problems with XSSFWorkbook.removeSheetAt when workbook contains chart	335	* Problems with XSSFWorkbook.removeSheetAt when workbook contains charts
336	*/	336	*/
337	public void testBug47813() {	337	public void testBug47813() {
338	XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("47813.xlsx");	338	XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("47813.xlsx");





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

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.DataMarker;
import org.apache.poi.ss.usermodel.charts.ChartDataFactory;

/**
 * Represents scatter charts serie.
 *
 * @author Roman Kashitsyn
 */
public interface ScatterChartSerie {

    /**
     * @return data source used for X axis values
     */
    ChartDataSource<?> getXValues();

    /**
     * @return data source used for Y axis values
     */
    ChartDataSource<? extends Number> getYValues();

}

Lines 18-24 Link Here

(-)src/java/org/apache/poi/ss/usermodel/charts/ChartDataFactory.java (-1 / +1 lines)
18	package org.apache.poi.ss.usermodel.charts;	18	package org.apache.poi.ss.usermodel.charts;
19		19
20	/**	20	/**
21	* A factory for different chart data types.	21	* A factory for different charts data types.
22	*	22	*
23	* @author Roman Kashitsyn	23	* @author Roman Kashitsyn
24	*/	24	*/




/*
 *  ====================================================================
 *    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 org.apache.poi.ss.usermodel.Sheet;

/**
 * Represents data marker used in charts.
 * @author Roman Kashitsyn
 */
public class DataMarker {
	
	private Sheet sheet;
	private CellRangeAddress range;

	/**
	 * @param sheet the sheet where data located.
	 * @param range the range within that sheet.
	 */
	public DataMarker(Sheet sheet, CellRangeAddress range) {
		this.sheet = sheet;
		this.range = range;
	}

	/**
	 * Returns the sheet marker points to.
	 * @return sheet marker points to.
	 */
	public Sheet getSheet() {
		return sheet;
	}

	/**
	 * Sets sheet marker points to.
	 * @param sheet new sheet for the marker.
	 */
	public void setSheet(Sheet sheet) {
		this.sheet = sheet;
	}

	/**
	 * Returns range of the marker.
	 * @return range of cells marker points to.
	 */
	public CellRangeAddress getRange() {
		return range;
	}

	/**
	 * Sets range of the marker.
	 * @param range new range for the marker.
	 */
	public void setRange(CellRangeAddress range) {
		this.range = range;
	}

	/**
	 * Formats data marker using canonical format, for example
	 * `SheetName!$A$1:$A$5'.
	 * @return formatted data marker.
	 */
    public String formatAsString() {
		String sheetName = (sheet == null) ? (null) : (sheet.getSheetName());
		if (range == null) {
			return null;
		} else {
			return range.formatAsString(sheetName, true);
		}
    }
}





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.

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





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 {

    private static final Object[][] plotData = {
            {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J"},
            {  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();

        ChartDataSource<String> xs = DataSources.fromStringCellRange(sheet, CellRangeAddress.valueOf("A1:J1"));
        ChartDataSource<Number> ys = DataSources.fromNumericCellRange(sheet, CellRangeAddress.valueOf("A2:J2"));
        ScatterChartSerie serie = scatterChartData.addSerie(xs, ys);

        assertNotNull(serie);
        assertEquals(1, scatterChartData.getSeries().size());
        assertTrue(scatterChartData.getSeries().contains(serie));

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


}




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

import java.io.FileOutputStream;
import java.util.Date;

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

/**
 * Illustrates how to create a simple scatter chart.
 *
 * @author Roman Kashitsyn
 */
public class ScatterChart {

    public static void main(String[] args) throws Exception {
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("Sheet 1");
        final int NUM_OF_ROWS = 3;
        final int NUM_OF_COLUMNS = 10;
		final int NUM_OF_COLUMNS = 10;

        // Create a row and put some cells in it. Rows are 0 based.
        Row row;
        Cell cell;
        for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++) {
            row = sheet.createRow((short) rowIndex);
            for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) {
                cell = row.createCell((short) colIndex);
                cell.setCellValue(colIndex * (rowIndex + 1));
            }
        }

        Drawing drawing = sheet.createDrawingPatriarch();
        ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 15);

        Chart chart = drawing.createChart(anchor);
        ChartLegend legend = chart.getOrCreateLegend();
        legend.setPosition(LegendPosition.TOP_RIGHT);

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

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

        ChartDataSource<Number> xs = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1));
        ChartDataSource<Number> ys1 = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1));
        ChartDataSource<Number> ys2 = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1));


        data.addSerie(xs, ys1);
        data.addSerie(xs, ys2);

        chart.plot(data, bottomAxis, leftAxis);

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("ooxml-scatter-chart.xlsx");
        wb.write(fileOut);
        fileOut.close();
    }
}

Lines 40-46 Link Here

(-)src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFChartSheet.java (-1 / +1 lines)
40	XSSFChartSheet sheet = (XSSFChartSheet)wb.getSheetAt(2);	40	XSSFChartSheet sheet = (XSSFChartSheet)wb.getSheetAt(2);
41		41
42	for(Row row : sheet) {	42	for(Row row : sheet) {
43	fail("Row iterator for chart sheets should return zero rows");	43	fail("Row iterator for charts sheets should return zero rows");
44	}	44	}
45	//access to a arbitrary row	45	//access to a arbitrary row
46	assertEquals(null, sheet.getRow(1));	46	assertEquals(null, sheet.getRow(1));




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

import java.util.List;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.DataMarker;


/**
 * @author Roman Kashitsyn
 */
public interface ScatterChartData extends ChartData {
	/**
	 * @param xs data source to be used for X axis values
	 * @param ys data source to be used for Y axis values
	 * @return a new scatter charts serie
	 */
	ScatterChartSerie addSerie(ChartDataSource<?> xs, ChartDataSource<? extends Number> ys);

	/**
	 * @return list of all series




/* ====================================================================
   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.charts.ChartDataSource;
import org.openxmlformats.schemas.drawingml.x2006.chart.*;

/**
 * Package private class with utility methods.
 *
 * @author Roman Kashitsyn
 */
class XSSFChartUtil {

    private XSSFChartUtil() {}

    /**
     * Builds CTAxDataSource object content from POI ChartDataSource.
     * @param ctAxDataSource OOXML data source to build
     * @param dataSource POI data source to use
     */
    public static void buildAxDataSource(CTAxDataSource ctAxDataSource, ChartDataSource<?> dataSource) {
        if (dataSource.isNumeric()) {
            if (dataSource.isReference()) {
                buildNumRef(ctAxDataSource.addNewNumRef(), dataSource);
            } else {
                buildNumLit(ctAxDataSource.addNewNumLit(), dataSource);
            }
        } else {
            if (dataSource.isReference()) {
                buildStrRef(ctAxDataSource.addNewStrRef(), dataSource);
            } else {
                buildStrLit(ctAxDataSource.addNewStrLit(), dataSource);
            }
        }
    }

    /**
     * Builds CTNumDataSource object content from POI ChartDataSource
     * @param ctNumDataSource OOXML data source to build
     * @param dataSource POI data source to use
     */
    public static void buildNumDataSource(CTNumDataSource ctNumDataSource,
                                          ChartDataSource<? extends Number> dataSource) {
        if (dataSource.isReference()) {
            buildNumRef(ctNumDataSource.addNewNumRef(), dataSource);
        } else {
            buildNumLit(ctNumDataSource.addNewNumLit(), dataSource);
        }
    }

    private static void buildNumRef(CTNumRef ctNumRef, ChartDataSource<?> dataSource) {
        ctNumRef.setF(dataSource.getFormulaString());
        CTNumData cache = ctNumRef.addNewNumCache();
        fillNumCache(cache, dataSource);
    }

    private static void buildNumLit(CTNumData ctNumData, ChartDataSource<?> dataSource) {
        fillNumCache(ctNumData, dataSource);
    }

    private static void buildStrRef(CTStrRef ctStrRef, ChartDataSource<?> dataSource) {
        ctStrRef.setF(dataSource.getFormulaString());
        CTStrData cache = ctStrRef.addNewStrCache();
        fillStringCache(cache, dataSource);
    }

    private static void buildStrLit(CTStrData ctStrData, ChartDataSource<?> dataSource) {
        fillStringCache(ctStrData, dataSource);
    }

    private static void fillStringCache(CTStrData cache, ChartDataSource<?> dataSource) {
        int numOfPoints = dataSource.getPointCount();
        cache.addNewPtCount().setVal(numOfPoints);
        for (int i = 0; i < numOfPoints; ++i) {
            Object value = dataSource.getPointAt(i);
            if (value != null) {
                CTStrVal ctStrVal = cache.addNewPt();
                ctStrVal.setIdx(i);
                ctStrVal.setV(value.toString());
            }
        }

    }

    private static void fillNumCache(CTNumData cache, ChartDataSource<?> dataSource) {
        int numOfPoints = dataSource.getPointCount();
        cache.addNewPtCount().setVal(numOfPoints);
        for (int i = 0; i < numOfPoints; ++i) {
            Number value = (Number) dataSource.getPointAt(i);
            if (value != null) {
                CTNumVal ctNumVal = cache.addNewPt();
                ctNumVal.setIdx(i);
                ctNumVal.setV(value.toString());
            }
        }
    }
}

Return to bug 51196

Lines 17-53 Link Here

(-)src/ooxml/java/org/apache/poi/xssf/usermodel/charts/XSSFScatterChartData.java (-112 / +70 lines)
17		17
18	package org.apache.poi.xssf.usermodel.charts;	18	package org.apache.poi.xssf.usermodel.charts;
19		19
20	import java.util.List;
21	import java.util.ArrayList;
22
23	import org.apache.poi.ss.usermodel.Chart;	20	import org.apache.poi.ss.usermodel.Chart;
24	import org.apache.poi.ss.usermodel.Sheet;	21	import org.apache.poi.ss.usermodel.charts.ChartAxis;
25	import org.apache.poi.ss.util.DataMarker;	22	import org.apache.poi.ss.usermodel.charts.ChartDataSource;
26	import org.apache.poi.ss.usermodel.charts.ScatterChartData;	23	import org.apache.poi.ss.usermodel.charts.ScatterChartData;
27	import org.apache.poi.ss.usermodel.charts.ScatterChartSerie;	24	import org.apache.poi.ss.usermodel.charts.ScatterChartSerie;
28	import org.apache.poi.ss.usermodel.charts.ChartDataFactory;	25	import org.apache.poi.xssf.usermodel.XSSFChart;
29	import org.apache.poi.ss.usermodel.charts.ChartAxis;	26	import org.openxmlformats.schemas.drawingml.x2006.chart.*;
30		27
31	import org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea;	28	import java.util.ArrayList;
32	import org.openxmlformats.schemas.drawingml.x2006.chart.CTScatterChart;	29	import java.util.List;
33	import org.openxmlformats.schemas.drawingml.x2006.chart.CTScatterStyle;
34	import org.openxmlformats.schemas.drawingml.x2006.chart.CTScatterSer;
35	import org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource;
36	import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef;
37	import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource;
38	import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumFmt;
39	import org.openxmlformats.schemas.drawingml.x2006.chart.CTValAx;
40	import org.openxmlformats.schemas.drawingml.x2006.chart.STScatterStyle;
41	import org.openxmlformats.schemas.drawingml.x2006.chart.STCrosses;
42	import org.openxmlformats.schemas.drawingml.x2006.chart.STCrossBetween;
43	import org.openxmlformats.schemas.drawingml.x2006.chart.STOrientation;
44	import org.openxmlformats.schemas.drawingml.x2006.chart.STTickLblPos;
45	import org.openxmlformats.schemas.drawingml.x2006.chart.STAxPos;
46		30
47	import org.apache.poi.xssf.usermodel.XSSFChart;
48		31
49	/**	32	/**
50	* Represents DrawingML scatter chart.	33	* Represents DrawingML scatter charts.
51	*	34	*
52	* @author Roman Kashitsyn	35	* @author Roman Kashitsyn
53	*/	36	*/
Lines 59-176 Link Here
59	private List<Serie> series;	42	private List<Serie> series;
60		43
61	public XSSFScatterChartData() {	44	public XSSFScatterChartData() {
62	series = new ArrayList<Serie>();	45	series = new ArrayList<Serie>();
63	}	46	}
64		47
65	/**	48	/**
66	* Package private ScatterChartSerie implementation.	49	* Package private ScatterChartSerie implementation.
67	*/	50	*/
68	static class Serie implements ScatterChartSerie {	51	static class Serie implements ScatterChartSerie {
69	private int id;	52	private int id;
70	private int order;	53	private int order;
71	private boolean useCache;	54	private ChartDataSource<?> xs;
72	private DataMarker xMarker;	55	private ChartDataSource<? extends Number> ys;
73	private DataMarker yMarker;
74	private XSSFNumberCache lastCaclulatedXCache;
75	private XSSFNumberCache lastCalculatedYCache;
76		56
77	protected Serie(int id, int order) {	57	protected Serie(int id, int order,
		58	ChartDataSource<?> xs,
		59	ChartDataSource<? extends Number> ys) {
78	super();	60	super();
79	this.id = id;	61	this.id = id;
80	this.order = order;	62	this.order = order;
81	this.useCache = true;	63	this.xs = xs;
		64	this.ys = ys;
82	}	65	}
83		66
84	public void setXValues(DataMarker marker) {	67	/**
85	xMarker = marker;
86	}
87
88	public void setYValues(DataMarker marker) {
89	yMarker = marker;
90	}
91
92	/**
93	* @param useCache if true, cached results will be added on plot	68	* Returns data source used for X axis values.
		69	* @return data source used for X axis values
94	*/	70	*/
95	public void setUseCache(boolean useCache) {	71	public ChartDataSource<?> getXValues() {
96	this.useCache = useCache;	72	return xs;
97	}	73	}
98		74
99	/**	75	/**
100	* Returns last calculated number cache for X axis.	76	* Returns data source used for Y axis values.
101	* @return last calculated number cache for X axis.	77	* @return data source used for Y axis values
102	*/	78	*/
103	XSSFNumberCache getLastCaculatedXCache() {	79	public ChartDataSource<? extends Number> getYValues() {
104	return lastCaclulatedXCache;	80	return ys;
105	}	81	}
106		82
107	/**	83	protected void addToChart(CTScatterChart ctScatterChart) {
108	* Returns last calculated number cache for Y axis.	84	CTScatterSer scatterSer = ctScatterChart.addNewSer();
109	* @return last calculated number cache for Y axis.	85	scatterSer.addNewIdx().setVal(this.id);
110	*/	86	scatterSer.addNewOrder().setVal(this.order);
111	XSSFNumberCache getLastCalculatedYCache() {
112	return lastCalculatedYCache;
113	}
114
115	protected void addToChart(CTScatterChart ctScatterChart) {
116	CTScatterSer scatterSer = ctScatterChart.addNewSer();
117	scatterSer.addNewIdx().setVal(this.id);
118	scatterSer.addNewOrder().setVal(this.order);
119		87
120	/* TODO: add some logic to automatically recognize cell	88	CTAxDataSource xVal = scatterSer.addNewXVal();
121	* types and choose appropriate data representation for
122	* X axis.
123	*/
124	CTAxDataSource xVal = scatterSer.addNewXVal();
125	CTNumRef xNumRef = xVal.addNewNumRef();	89	XSSFChartUtil.buildAxDataSource(xVal, xs);
126	xNumRef.setF(xMarker.formatAsString());
127		90
128	CTNumDataSource yVal = scatterSer.addNewYVal();	91	CTNumDataSource yVal = scatterSer.addNewYVal();
129	CTNumRef yNumRef = yVal.addNewNumRef();	92	XSSFChartUtil.buildNumDataSource(yVal, ys);
130	yNumRef.setF(yMarker.formatAsString());
131
132	if (useCache) {
133	/* We can not store cache since markers are not immutable */
134	XSSFNumberCache.buildCache(xMarker, xNumRef);
135	lastCalculatedYCache = XSSFNumberCache.buildCache(yMarker, yNumRef);
136	}	93	}
137	}	94	}
138	}
139		95
140	public ScatterChartSerie addSerie(DataMarker xMarker, DataMarker yMarker) {	96	public ScatterChartSerie addSerie(ChartDataSource<?> xs,
		97	ChartDataSource<? extends Number> ys) {
		98	if (!ys.isNumeric()) {
		99	throw new IllegalArgumentException("Y axis data source must be numeric.");
		100	}
141	int numOfSeries = series.size();	101	int numOfSeries = series.size();
142	Serie newSerie = new Serie(numOfSeries, numOfSeries);	102	Serie newSerie = new Serie(numOfSeries, numOfSeries, xs, ys);
143	newSerie.setXValues(xMarker);
144	newSerie.setYValues(yMarker);
145	series.add(newSerie);	103	series.add(newSerie);
146	return newSerie;	104	return newSerie;
147	}	105	}
148		106
149	public void fillChart(Chart chart, ChartAxis... axis) {	107	public void fillChart(Chart chart, ChartAxis... axis) {
150	if (!(chart instanceof XSSFChart)) {	108	if (!(chart instanceof XSSFChart)) {
151	throw new IllegalArgumentException("Chart must be instance of XSSFChart");	109	throw new IllegalArgumentException("Chart must be instance of XSSFChart");
152	}	110	}
153		111
154	XSSFChart xssfChart = (XSSFChart) chart;	112	XSSFChart xssfChart = (XSSFChart) chart;
155	CTPlotArea plotArea = xssfChart.getCTChart().getPlotArea();	113	CTPlotArea plotArea = xssfChart.getCTChart().getPlotArea();
156	CTScatterChart scatterChart = plotArea.addNewScatterChart();	114	CTScatterChart scatterChart = plotArea.addNewScatterChart();
157	addStyle(scatterChart);	115	addStyle(scatterChart);
158		116
159	for (Serie s : series) {	117	for (Serie s : series) {
160	s.addToChart(scatterChart);	118	s.addToChart(scatterChart);
161	}	119	}
162		120
163	for (ChartAxis ax : axis) {	121	for (ChartAxis ax : axis) {
164	scatterChart.addNewAxId().setVal(ax.getId());	122	scatterChart.addNewAxId().setVal(ax.getId());
165	}	123	}
166	}	124	}
167		125
168	public List<? extends Serie> getSeries() {	126	public List<? extends Serie> getSeries() {
169	return series;	127	return series;
170	}	128	}
171		129
172	private void addStyle(CTScatterChart ctScatterChart) {	130	private void addStyle(CTScatterChart ctScatterChart) {
173	CTScatterStyle scatterStyle = ctScatterChart.addNewScatterStyle();	131	CTScatterStyle scatterStyle = ctScatterChart.addNewScatterStyle();
174	scatterStyle.setVal(STScatterStyle.LINE_MARKER);	132	scatterStyle.setVal(STScatterStyle.LINE_MARKER);
175	}	133	}
176	}	134	}

Lines 29-77 Link Here

(-)src/testcases/org/apache/poi/ss/util/TestSheetBuilder.java (-31 / +38 lines)
29		29
30	/**	30	/**
31	* Tests SheetBuilder.	31	* Tests SheetBuilder.
		32	*
32	* @see org.apache.poi.ss.util.SheetBuilder	33	* @see org.apache.poi.ss.util.SheetBuilder
33	*/	34	*/
34	public final class TestSheetBuilder extends TestCase {	35	public final class TestSheetBuilder extends TestCase {
35		36
36	private static Object[][] testData = new Object[][] {	37	private static Object[][] testData = new Object[][]{
37	{ 1, 2, 3},	38	{1, 2, 3},
38	{new Date(), null, null},	39	{new Date(), null, null},
39	{ "one", "two", "=A1+B2"}	40	{"one", "two", "=A1+B2"}
40	};	41	};
41		42
42	public void testNotCreateEmptyCells() {	43	public void testNotCreateEmptyCells() {
43	Workbook wb = new HSSFWorkbook();	44	Workbook wb = new HSSFWorkbook();
44	Sheet sheet = new SheetBuilder(wb, testData).build();	45	Sheet sheet = new SheetBuilder(wb, testData).build();
45		46
46	assertEquals(sheet.getPhysicalNumberOfRows(), 3);	47	assertEquals(sheet.getPhysicalNumberOfRows(), 3);
47		48
48	Row firstRow = sheet.getRow(0);	49	Row firstRow = sheet.getRow(0);
49	Cell firstCell = firstRow.getCell(0);	50	Cell firstCell = firstRow.getCell(0);
50		51
51	assertEquals(firstCell.getCellType(), Cell.CELL_TYPE_NUMERIC);	52	assertEquals(firstCell.getCellType(), Cell.CELL_TYPE_NUMERIC);
52	assertEquals(1.0, firstCell.getNumericCellValue(), 0.00001);	53	assertEquals(1.0, firstCell.getNumericCellValue(), 0.00001);
53		54
54		55
55	Row secondRow = sheet.getRow(1);	56	Row secondRow = sheet.getRow(1);
56	assertNotNull(secondRow.getCell(0));	57	assertNotNull(secondRow.getCell(0));
57	assertNull(secondRow.getCell(2));	58	assertNull(secondRow.getCell(2));
58		59
59	Row thirdRow = sheet.getRow(2);	60	Row thirdRow = sheet.getRow(2);
60	assertEquals(Cell.CELL_TYPE_STRING, thirdRow.getCell(0).getCellType());	61	assertEquals(Cell.CELL_TYPE_STRING, thirdRow.getCell(0).getCellType());
61	String cellValue = thirdRow.getCell(0).getStringCellValue();	62	String cellValue = thirdRow.getCell(0).getStringCellValue();
62	assertEquals(testData[2][0].toString(), cellValue);	63	assertEquals(testData[2][0].toString(), cellValue);
63		64
64	assertEquals(Cell.CELL_TYPE_FORMULA, thirdRow.getCell(2).getCellType());	65	assertEquals(Cell.CELL_TYPE_FORMULA, thirdRow.getCell(2).getCellType());
65	assertEquals("A1+B2", thirdRow.getCell(2).getCellFormula());	66	assertEquals("A1+B2", thirdRow.getCell(2).getCellFormula());
66	}	67	}
67		68
68	public void testEmptyCells() {	69	public void testEmptyCells() {
69	Workbook wb = new HSSFWorkbook();	70	Workbook wb = new HSSFWorkbook();
70	Sheet sheet = new SheetBuilder(wb, testData).setCreateEmptyCells(true).build();	71	Sheet sheet = new SheetBuilder(wb, testData).setCreateEmptyCells(true).build();
71		72
72	Cell emptyCell = sheet.getRow(1).getCell(1);	73	Cell emptyCell = sheet.getRow(1).getCell(1);
73	assertNotNull(emptyCell);	74	assertNotNull(emptyCell);
74	assertEquals(Cell.CELL_TYPE_BLANK, emptyCell.getCellType());	75	assertEquals(Cell.CELL_TYPE_BLANK, emptyCell.getCellType());
75	}	76	}
76		77
77	}	78	public void testSheetName() {
		79	final String sheetName = "TEST SHEET NAME";
		80	Workbook wb = new HSSFWorkbook();
		81	Sheet sheet = new SheetBuilder(wb, testData).setSheetName(sheetName).build();
		82	assertEquals(sheetName, sheet.getSheetName());
		83	}
		84	}

Lines 1-150 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	}

Line 0 Link Here

(-)src/testcases/org/apache/poi/ss/usermodel/charts/TestDataSources.java (+136 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.usermodel.charts;
		18
		19	import junit.framework.TestCase;
		20	import org.apache.poi.hssf.usermodel.HSSFWorkbook;
		21	import org.apache.poi.ss.usermodel.Sheet;
		22	import org.apache.poi.ss.usermodel.Workbook;
		23	import org.apache.poi.ss.util.CellRangeAddress;
		24	import org.apache.poi.ss.util.SheetBuilder;
		25
		26	/**
		27	* Tests for {@link org.apache.poi.ss.usermodel.charts.DataSources}.
		28	*
		29	* @author Roman Kashitsyn
		30	*/
		31	public class TestDataSources extends TestCase {
		32
		33	private static final Object[][] numericCells = {
		34	{0.0, 1.0, 2.0, 3.0, 4.0},
		35	{0.0, "=B12", "=C12", "=D12", "=E12"}
		36	};
		37
		38	private static final Object[][] stringCells = {
		39	{ 1, 2, 3, 4, 5},
		40	{"A", "B", "C", "D", "E"}
		41	};
		42
		43	private static final Object[][] mixedCells = {
		44	{1.0, "2.0", 3.0, "4.0", 5.0, "6.0"}
		45	};
		46
		47	public void testNumericArrayDataSource() {
		48	Double[] doubles = new Double[]{1.0, 2.0, 3.0, 4.0, 5.0};
		49	ChartDataSource<Double> doubleDataSource = DataSources.fromArray(doubles);
		50	assertTrue(doubleDataSource.isNumeric());
		51	assertFalse(doubleDataSource.isReference());
		52	assertDataSourceIsEqualToArray(doubleDataSource, doubles);
		53	}
		54
		55	public void testStringArrayDataSource() {
		56	String[] strings = new String[]{"one", "two", "three", "four", "five"};
		57	ChartDataSource<String> stringDataSource = DataSources.fromArray(strings);
		58	assertFalse(stringDataSource.isNumeric());
		59	assertFalse(stringDataSource.isReference());
		60	assertDataSourceIsEqualToArray(stringDataSource, strings);
		61	}
		62
		63	public void testNumericCellDataSource() {
		64	Workbook wb = new HSSFWorkbook();
65	Sheet sheet = new SheetBuilder(wb, numericCells).build();
66	CellRangeAddress numCellRange = CellRangeAddress.valueOf("A2:E2");
67	ChartDataSource<Number> numDataSource = DataSources.fromNumericCellRange(sheet, numCellRange);
68	assertTrue(numDataSource.isReference());
69	assertTrue(numDataSource.isNumeric());
70	assertEquals(numericCells[0].length, numDataSource.getPointCount());
71	for (int i = 0; i < numericCells[0].length; ++i) {
72	assertEquals(((Number) numericCells[0][i]).doubleValue() * 2,
73	numDataSource.getPointAt(i).doubleValue(), 0.00001);
74	}
75	}
76
77	public void testStringCellDataSource() {
78	Workbook wb = new HSSFWorkbook();
79	Sheet sheet = new SheetBuilder(wb, stringCells).build();
80	CellRangeAddress numCellRange = CellRangeAddress.valueOf("A2:E2");
81	ChartDataSource<String> numDataSource = DataSources.fromStringCellRange(sheet, numCellRange);
82	assertTrue(numDataSource.isReference());
83	assertFalse(numDataSource.isNumeric());
84	assertEquals(numericCells[0].length, numDataSource.getPointCount());
85	for (int i = 0; i < stringCells[1].length; ++i) {
86	assertEquals(stringCells[1][i], numDataSource.getPointAt(i));
87	}
88	}
89
90	public void testMixedCellDataSource() {
91	Workbook wb = new HSSFWorkbook();
92	Sheet sheet = new SheetBuilder(wb, mixedCells).build();
93	CellRangeAddress mixedCellRange = CellRangeAddress.valueOf("A1:F1");
94	ChartDataSource<String> strDataSource = DataSources.fromStringCellRange(sheet, mixedCellRange);
95	ChartDataSource<Number> numDataSource = DataSources.fromNumericCellRange(sheet, mixedCellRange);
96	for (int i = 0; i < mixedCells[0].length; ++i) {
97	if (i % 2 == 0) {
98	assertNull(strDataSource.getPointAt(i));
99	assertEquals(((Number) mixedCells[0][i]).doubleValue(),
100	numDataSource.getPointAt(i).doubleValue(), 0.00001);
101	} else {
102	assertNull(numDataSource.getPointAt(i));
103	assertEquals(mixedCells[0][i], strDataSource.getPointAt(i));
104	}
105	}
106	}
107
108	public void testIOBExceptionOnInvalidIndex() {
109	Workbook wb = new HSSFWorkbook();
110	Sheet sheet = new SheetBuilder(wb, numericCells).build();
111	CellRangeAddress rangeAddress = CellRangeAddress.valueOf("A2:E2");
112	ChartDataSource<Number> numDataSource = DataSources.fromNumericCellRange(sheet, rangeAddress);
113	IndexOutOfBoundsException exception = null;
114	try {
115	numDataSource.getPointAt(-1);
116	} catch (IndexOutOfBoundsException e) {
117	exception = e;
118	}
119	assertNotNull(exception);
120
121	exception = null;
122	try {
123	numDataSource.getPointAt(numDataSource.getPointCount());
124	} catch (IndexOutOfBoundsException e) {
125	exception = e;
126	}
127	assertNotNull(exception);
128	}
129
130	private <T> void assertDataSourceIsEqualToArray(ChartDataSource<T> ds, T[] array) {
131	assertEquals(ds.getPointCount(), array.length);
132	for (int i = 0; i < array.length; ++i) {
133	assertEquals(ds.getPointAt(i), array[i]);
134	}
135	}
136	}

Lines 25-139 Link Here

(-)src/java/org/apache/poi/ss/util/SheetBuilder.java (-62 / +81 lines)
25	import org.apache.poi.ss.usermodel.Cell;	25	import org.apache.poi.ss.usermodel.Cell;
26		26
27	/**	27	/**
28	* Class that provides useful sheet build capabilities. It can be used	28	* Class {@code SheetBuilder} provides an easy way of building workbook sheets
29	* in test cases to improve readability or in Swing applications with	29	* from 2D array of Objects. It can be used in test cases to improve code
30	* tables.	30	* readability or in Swing applications with tables.
31	*	31	*
32	* @author Roman Kashitsyn	32	* @author Roman Kashitsyn
33	*/	33	*/
34	public class SheetBuilder {	34	public class SheetBuilder {
35		35
36	private Workbook workbook;	36	private final Workbook workbook;
37	private Object[][] cells;	37	private final Object[][] cells;
38	private boolean shouldCreateEmptyCells = false;	38	private boolean shouldCreateEmptyCells = false;
		39	private String sheetName = null;
39		40
40	public SheetBuilder(Workbook workbook, Object[][] cells) {	41	public SheetBuilder(Workbook workbook, Object[][] cells) {
41	this.workbook = workbook;	42	this.workbook = workbook;
42	this.cells = cells;	43	this.cells = cells;
43	}	44	}
44		45
45	/**	46	/**
46	* @return true if null objects should be trated as empty cells	47	* Returns {@code true} if null array elements should be treated as empty
47	* false otherwise	48	* cells.
		49	*
		50	* @return {@code true} if null objects should be treated as empty cells
		51	* and {@code false} otherwise
48	*/	52	*/
49	public boolean getCreateEmptyCells() {	53	public boolean getCreateEmptyCells() {
50	return shouldCreateEmptyCells;	54	return shouldCreateEmptyCells;
51	}	55	}
52		56
53	/**	57	/**
54	* @param shouldCreateEmptyCells true if null array elements should be	58	* Specifies if null array elements should be treated as empty cells.
55	* trated as empty cells	59	*
56	* @return this	60	* @param shouldCreateEmptyCells {@code true} if null array elements should be
		61	* treated as empty cells
		62	* @return {@code this}
57	*/	63	*/
58	public SheetBuilder setCreateEmptyCells(boolean shouldCreateEmptyCells) {	64	public SheetBuilder setCreateEmptyCells(boolean shouldCreateEmptyCells) {
59	this.shouldCreateEmptyCells = shouldCreateEmptyCells;	65	this.shouldCreateEmptyCells = shouldCreateEmptyCells;
60	return this;	66	return this;
61	}	67	}
62		68
63	/**	69	/**
		70	* Specifies name of the sheet to build. If not specified, default name (provided by
		71	* workbook) will be used instead.
		72	* @param sheetName sheet name to use
		73	* @return {@code this}
		74	*/
		75	public SheetBuilder setSheetName(String sheetName) {
		76	this.sheetName = sheetName;
		77	return this;
		78	}
		79
		80	/**
64	* Builds sheet from parent workbook and 2D array with cell	81	* Builds sheet from parent workbook and 2D array with cell
65	* values. Creates rows anyway (even if row contains only null	82	* values. Creates rows anyway (even if row contains only null
66	* cells), creates cells only if corresponding property is true.	83	* cells), creates cells if either corresponding array value is not
		84	* null or createEmptyCells property is true.
67	* The conversion is performed in the following way:	85	* The conversion is performed in the following way:
68	*	86	* <p/>
69	* <ul>	87	* <ul>
70	* <li>Numbers become numeric cells.</li>	88	* <li>Numbers become numeric cells.</li>
71	* <li><code>java.util.Date</code> or <code>java.util.Calendar</code>	89	* <li><code>java.util.Date</code> or <code>java.util.Calendar</code>
72	* instances become date cells.</li>	90	* instances become date cells.</li>
73	* <li>String with leading '=' char become formulas (leading '='	91	* <li>String with leading '=' char become formulas (leading '='
74	* trancated).</li>	92	* will be truncated).</li>
75	* <li>Other objects become strings via <code>Object.toString()</code>	93	* <li>Other objects become strings via <code>Object.toString()</code>
76	* method.</li>	94	* method call.</li>
77	* </ul>	95	* </ul>
78	*	96	*
79	* @return newly created sheet	97	* @return newly created sheet
80	*/	98	*/
81	public Sheet build() {	99	public Sheet build() {
82	Sheet sheet = workbook.createSheet();	100	Sheet sheet = (sheetName == null) ? workbook.createSheet() : workbook.createSheet(sheetName);
83	Row currentRow = null;	101	Row currentRow = null;
84	Cell currentCell = null;	102	Cell currentCell = null;
85		103
86	for (int rowIndex = 0; rowIndex < cells.length; ++rowIndex) {	104	for (int rowIndex = 0; rowIndex < cells.length; ++rowIndex) {
87	Object[] rowArray = cells[rowIndex];	105	Object[] rowArray = cells[rowIndex];
88	currentRow = sheet.createRow(rowIndex);	106	currentRow = sheet.createRow(rowIndex);
89		107
90	for (int cellIndex = 0; cellIndex < rowArray.length; ++cellIndex) {	108	for (int cellIndex = 0; cellIndex < rowArray.length; ++cellIndex) {
91	Object cellValue = rowArray[cellIndex];	109	Object cellValue = rowArray[cellIndex];
92	if (cellValue != null \|\| shouldCreateEmptyCells) {	110	if (cellValue != null \|\| shouldCreateEmptyCells) {
93	currentCell = currentRow.createCell(cellIndex);	111	currentCell = currentRow.createCell(cellIndex);
94	setCellValue(currentCell, cellValue);	112	setCellValue(currentCell, cellValue);
95	}	113	}
96	}	114	}
97	}	115	}
98	return sheet;	116	return sheet;
99	}	117	}
100		118
101	/**	119	/**
102	* Sets the cell value using object type information.	120	* Sets the cell value using object type information.
103	* @param cell cell to change	121	*
		122	* @param cell cell to change
104	* @param value value to set	123	* @param value value to set
105	*/	124	*/
106	public void setCellValue(Cell cell, Object value) {	125	private void setCellValue(Cell cell, Object value) {
107	if (value == null \|\| cell == null) {	126	if (value == null \|\| cell == null) {
108	return;	127	return;
109	} else if (value instanceof Number) {	128	} else if (value instanceof Number) {
110	double doubleValue = ((Number) value).doubleValue();	129	double doubleValue = ((Number) value).doubleValue();
111	cell.setCellValue(doubleValue);	130	cell.setCellValue(doubleValue);
112	} else if (value instanceof Date) {	131	} else if (value instanceof Date) {
113	cell.setCellValue((Date) value);	132	cell.setCellValue((Date) value);
114	} else if (value instanceof Calendar) {	133	} else if (value instanceof Calendar) {
115	cell.setCellValue((Calendar) value);	134	cell.setCellValue((Calendar) value);
116	} else if (isFormulaDefinition(value)) {	135	} else if (isFormulaDefinition(value)) {
117	cell.setCellFormula(getFormula(value));	136	cell.setCellFormula(getFormula(value));
118	} else {	137	} else {
119	cell.setCellValue(value.toString());	138	cell.setCellValue(value.toString());
120	}	139	}
121	}	140	}
122		141
123	private boolean isFormulaDefinition(Object obj) {	142	private boolean isFormulaDefinition(Object obj) {
124	if (obj instanceof String) {	143	if (obj instanceof String) {
125	String str = (String) obj;	144	String str = (String) obj;
126	if (str.length() < 2) {	145	if (str.length() < 2) {
127	return false;	146	return false;
128	} else {	147	} else {
129	return ((String) obj).charAt(0) == '=';	148	return ((String) obj).charAt(0) == '=';
130	}	149	}
131	} else {	150	} else {
132	return false;	151	return false;
133	}	152	}
134	}	153	}
135		154
136	private String getFormula(Object obj) {	155	private String getFormula(Object obj) {
137	return ((String) obj).substring(1);	156	return ((String) obj).substring(1);
138	}	157	}
139	}	158	}

Lines 1-87 Link Here

(-)src/java/org/apache/poi/ss/util/DataMarker.java (-87 lines)
1	/*
2	* ====================================================================
3	* Licensed to the Apache Software Foundation (ASF) under one or more
4	* contributor license agreements. See the NOTICE file distributed with
5	* this work for additional information regarding copyright ownership.
6	* The ASF licenses this file to You under the Apache License, Version 2.0
7	* (the "License"); you may not use this file except in compliance with
8	* the License. You may obtain a copy of the License at
9	*
10	* http://www.apache.org/licenses/LICENSE-2.0
11	*
12	* Unless required by applicable law or agreed to in writing, software
13	* distributed under the License is distributed on an "AS IS" BASIS,
14	* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15	* See the License for the specific language governing permissions and
16	* limitations under the License.
17	* ====================================================================
18	*/
19
20	package org.apache.poi.ss.util;
21
22	import org.apache.poi.ss.usermodel.Sheet;
23
24	/**
25	* Represents data marker used in charts.
26	* @author Roman Kashitsyn
27	*/
28	public class DataMarker {
29
30	private Sheet sheet;
31	private CellRangeAddress range;
32
33	/**
34	* @param sheet the sheet where data located.
35	* @param range the range within that sheet.
36	*/
37	public DataMarker(Sheet sheet, CellRangeAddress range) {
38	this.sheet = sheet;
39	this.range = range;
40	}
41
42	/**
43	* Returns the sheet marker points to.
44	* @return sheet marker points to.
45	*/
46	public Sheet getSheet() {
47	return sheet;
48	}
49
50	/**
51	* Sets sheet marker points to.
52	* @param sheet new sheet for the marker.
53	*/
54	public void setSheet(Sheet sheet) {
55	this.sheet = sheet;
56	}
57
58	/**
59	* Returns range of the marker.
60	* @return range of cells marker points to.
61	*/
62	public CellRangeAddress getRange() {
63	return range;
64	}
65
66	/**
67	* Sets range of the marker.
68	* @param range new range for the marker.
69	*/
70	public void setRange(CellRangeAddress range) {
71	this.range = range;
72	}
73
74	/**
75	* Formats data marker using canonical format, for example
76	* `SheetName!$A$1:$A$5'.
77	* @return formatted data marker.
78	*/
79	public String formatAsString() {
80	String sheetName = (sheet == null) ? (null) : (sheet.getSheetName());
81	if (range == null) {
82	return null;
83	} else {
84	return range.formatAsString(sheetName, true);
85	}
86	}
87	}

Lines 17-28 Link Here

(-)src/java/org/apache/poi/ss/util/cellwalk/CellWalk.java (-51 / +50 lines)
17		17
18	package org.apache.poi.ss.util.cellwalk;	18	package org.apache.poi.ss.util.cellwalk;
19		19
20
21	import org.apache.poi.ss.usermodel.Cell;	20	import org.apache.poi.ss.usermodel.Cell;
22	import org.apache.poi.ss.usermodel.Row;	21	import org.apache.poi.ss.usermodel.Row;
23	import org.apache.poi.ss.usermodel.Sheet;	22	import org.apache.poi.ss.usermodel.Sheet;
24	import org.apache.poi.ss.util.CellRangeAddress;	23	import org.apache.poi.ss.util.CellRangeAddress;
25	import org.apache.poi.ss.util.DataMarker;
26		24
27	/**	25	/**
28	* Traverse cell range.	26	* Traverse cell range.
Lines 35-129 Link Here
35	private CellRangeAddress range;	33	private CellRangeAddress range;
36	private boolean traverseEmptyCells;	34	private boolean traverseEmptyCells;
37		35
38	public CellWalk(DataMarker dm) {
39	this(dm.getSheet(), dm.getRange());
40	}
41		36
42	public CellWalk(Sheet sheet, CellRangeAddress range) {	37	public CellWalk(Sheet sheet, CellRangeAddress range) {
43	this.sheet = sheet;	38	this.sheet = sheet;
44	this.range = range;	39	this.range = range;
45	this.traverseEmptyCells = false;	40	this.traverseEmptyCells = false;
46	}	41	}
47		42
48	/**	43	/**
49	* Should we call handler on empty (blank) cells. Default is	44	* Should we call handler on empty (blank) cells. Default is
50	* false.	45	* false.
		46	*
51	* @return true if handler should be called on empty (blank)	47	* @return true if handler should be called on empty (blank)
52	* cells, false otherwise.	48	* cells, false otherwise.
53	*/	49	*/
54	public boolean isTraverseEmptyCells() {	50	public boolean isTraverseEmptyCells() {
55	return traverseEmptyCells;	51	return traverseEmptyCells;
56	}	52	}
57		53
58	/**	54	/**
59	* Sets the traverseEmptyCells property.	55	* Sets the traverseEmptyCells property.
		56	*
60	* @param traverseEmptyCells new property value	57	* @param traverseEmptyCells new property value
61	*/	58	*/
62	public void setTraverseEmptyCells(boolean traverseEmptyCells) {	59	public void setTraverseEmptyCells(boolean traverseEmptyCells) {
63	this.traverseEmptyCells = traverseEmptyCells;	60	this.traverseEmptyCells = traverseEmptyCells;
64	}	61	}
65		62
66	/**	63	/**
67	* Traverse cell range from top left to bottom right cell.	64	* Traverse cell range from top left to bottom right cell.
		65	*
68	* @param handler handler to call on each appropriate cell	66	* @param handler handler to call on each appropriate cell
69	*/	67	*/
70	public void traverse(CellHandler handler) {	68	public void traverse(CellHandler handler) {
71	int firstRow = range.getFirstRow();	69	int firstRow = range.getFirstRow();
72	int lastRow = range.getLastRow();	70	int lastRow = range.getLastRow();
73	int firstColumn = range.getFirstColumn();	71	int firstColumn = range.getFirstColumn();
74	int lastColumn = range.getLastColumn();	72	int lastColumn = range.getLastColumn();
75	final int width = lastColumn - firstColumn + 1;	73	final int width = lastColumn - firstColumn + 1;
76	SimpleCellWalkContext ctx = new SimpleCellWalkContext();	74	SimpleCellWalkContext ctx = new SimpleCellWalkContext();
77	Row currentRow = null;	75	Row currentRow = null;
78	Cell currentCell = null;	76	Cell currentCell = null;
79		77
80	for (ctx.rowNumber = firstRow; ctx.rowNumber <= lastRow; ++ctx.rowNumber) {	78	for (ctx.rowNumber = firstRow; ctx.rowNumber <= lastRow; ++ctx.rowNumber) {
81	currentRow = sheet.getRow(ctx.rowNumber);	79	currentRow = sheet.getRow(ctx.rowNumber);
82	if (currentRow == null) {	80	if (currentRow == null) {
83	continue;	81	continue;
84	}	82	}
85	for (ctx.colNumber = firstColumn; ctx.colNumber <= lastColumn; ++ctx.colNumber) {	83	for (ctx.colNumber = firstColumn; ctx.colNumber <= lastColumn; ++ctx.colNumber) {
86	currentCell = currentRow.getCell(ctx.colNumber);	84	currentCell = currentRow.getCell(ctx.colNumber);
87		85
88	if (currentCell == null) {	86	if (currentCell == null) {
89	continue;	87	continue;
90	}	88	}
91	if (isEmpty(currentCell) && !traverseEmptyCells) {	89	if (isEmpty(currentCell) && !traverseEmptyCells) {
92	continue;	90	continue;
93	}	91	}
94		92
95	ctx.ordinalNumber =	93	ctx.ordinalNumber =
96	(ctx.rowNumber - firstRow) * width +	94	(ctx.rowNumber - firstRow) * width +
97	(ctx.colNumber - firstColumn + 1);	95	(ctx.colNumber - firstColumn + 1);
98		96
99	handler.onCell(currentCell, ctx);	97	handler.onCell(currentCell, ctx);
100	}	98	}
101	}	99	}
102	}	100	}
103		101
104	private boolean isEmpty(Cell cell) {	102	private boolean isEmpty(Cell cell) {
105	return (cell.getCellType() == Cell.CELL_TYPE_BLANK);	103	return (cell.getCellType() == Cell.CELL_TYPE_BLANK);
106	}	104	}
107		105
108	/**	106	/**
109	* Inner class to hold walk context.	107	* Inner class to hold walk context.
		108	*
110	* @author Roman Kashitsyn	109	* @author Roman Kashitsyn
111	*/	110	*/
112	private class SimpleCellWalkContext implements CellWalkContext {	111	private class SimpleCellWalkContext implements CellWalkContext {
113	public long ordinalNumber = 0;	112	public long ordinalNumber = 0;
114	public int rowNumber = 0;	113	public int rowNumber = 0;
115	public int colNumber = 0;	114	public int colNumber = 0;
116		115
117	public long getOrdinalNumber() {	116	public long getOrdinalNumber() {
118	return ordinalNumber;	117	return ordinalNumber;
119	}	118	}
120		119
121	public int getRowNumber() {	120	public int getRowNumber() {
122	return rowNumber;	121	return rowNumber;
123	}	122	}
124		123
125	public int getColumnNumber() {	124	public int getColumnNumber() {
126	return colNumber;	125	return colNumber;
127	}	126	}
128	}	127	}
129	}	128	}

Lines 21-62 Link Here

(-)src/ooxml/testcases/org/apache/poi/xssf/usermodel/charts/TestXSSFScatterChartData.java (-21 / +22 lines)
21		21
22	import org.apache.poi.ss.usermodel.*;	22	import org.apache.poi.ss.usermodel.*;
23	import org.apache.poi.ss.util.CellRangeAddress;	23	import org.apache.poi.ss.util.CellRangeAddress;
24	import org.apache.poi.ss.util.DataMarker;
25	import org.apache.poi.ss.util.SheetBuilder;	24	import org.apache.poi.ss.util.SheetBuilder;
26	import org.apache.poi.ss.usermodel.charts.*;	25	import org.apache.poi.ss.usermodel.charts.*;
27	import org.apache.poi.xssf.usermodel.XSSFWorkbook;	26	import org.apache.poi.xssf.usermodel.XSSFWorkbook;
28		27
29	/**	28	/**
30	* Tests for XSSFScatterChartData.	29	* Tests for XSSFScatterChartData.
		30	*
31	* @author Roman Kashitsyn	31	* @author Roman Kashitsyn
32	*/	32	*/
33	public final class TestXSSFScatterChartData extends TestCase {	33	public final class TestXSSFScatterChartData extends TestCase {
34		34
35	private static Object[][] plotData = new Object[][] {	35	private static final Object[][] plotData = {
36	{1, 2, 3, 4, 5, 6, 7, 8, 9, 10},	36	{"A", "B", "C", "D", "E", "F", "G", "H", "I", "J"},
37	{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}	37	{ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10}
38	};	38	};
39		39
40	public void testOneSeriePlot() throws Exception {	40	public void testOneSeriePlot() throws Exception {
41	Workbook wb = new XSSFWorkbook();	41	Workbook wb = new XSSFWorkbook();
42	Sheet sheet = new SheetBuilder(wb, plotData).build();	42	Sheet sheet = new SheetBuilder(wb, plotData).build();
43	Drawing drawing = sheet.createDrawingPatriarch();	43	Drawing drawing = sheet.createDrawingPatriarch();
44	ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 1, 1, 10, 30);	44	ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 1, 1, 10, 30);
45	Chart chart = drawing.createChart(anchor);	45	Chart chart = drawing.createChart(anchor);
46		46
47	ChartAxis bottomAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.BOTTOM);	47	ChartAxis bottomAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.BOTTOM);
48	ChartAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);	48	ChartAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);
49		49
50	ScatterChartData scatterChartData =	50	ScatterChartData scatterChartData =
51	chart.getChartDataFactory().createScatterChartData();	51	chart.getChartDataFactory().createScatterChartData();
52		52
53	DataMarker xMarker = new DataMarker(sheet, CellRangeAddress.valueOf("A1:A10"));	53	ChartDataSource<String> xs = DataSources.fromStringCellRange(sheet, CellRangeAddress.valueOf("A1:J1"));
54	DataMarker yMarker = new DataMarker(sheet, CellRangeAddress.valueOf("B1:B10"));	54	ChartDataSource<Number> ys = DataSources.fromNumericCellRange(sheet, CellRangeAddress.valueOf("A2:J2"));
55	ScatterChartSerie serie = scatterChartData.addSerie(xMarker, yMarker);	55	ScatterChartSerie serie = scatterChartData.addSerie(xs, ys);
56		56
57	assertEquals(1, scatterChartData.getSeries().size());	57	assertNotNull(serie);
		58	assertEquals(1, scatterChartData.getSeries().size());
		59	assertTrue(scatterChartData.getSeries().contains(serie));
58		60
59	chart.plot(scatterChartData, bottomAxis, leftAxis);	61	chart.plot(scatterChartData, bottomAxis, leftAxis);
60	}	62	}
61
62	}	63	}

Lines 20-82 Link Here

(-)src/examples/src/org/apache/poi/xssf/usermodel/examples/ScatterChart.java (-41 / +38 lines)
20	package org.apache.poi.xssf.usermodel.examples;	20	package org.apache.poi.xssf.usermodel.examples;
21		21
22	import java.io.FileOutputStream;	22	import java.io.FileOutputStream;
23	import java.util.Date;
24
25	import org.apache.poi.ss.usermodel.*;	23	import org.apache.poi.ss.usermodel.*;
26	import org.apache.poi.ss.util.*;	24	import org.apache.poi.ss.util.*;
27	import org.apache.poi.ss.usermodel.charts.*;	25	import org.apache.poi.ss.usermodel.charts.*;
28	import org.apache.poi.xssf.usermodel.*;	26	import org.apache.poi.xssf.usermodel.XSSFWorkbook;
29	import org.apache.poi.xssf.usermodel.charts.*;
30	import org.apache.poi.xssf.usermodel.XSSFWorkbook;
31		27
32	/**	28	/**
33	* Illustrates how to create a simple scatter chart.	29	* Illustrates how to create a simple scatter chart.
		30	*
		31	* @author Roman Kashitsyn
34	*/	32	*/
35	public class ScatterChart {	33	public class ScatterChart {
36		34
37	public static void main(String[]args) throws Exception {	35	public static void main(String[] args) throws Exception {
38	Workbook wb = new XSSFWorkbook();	36	Workbook wb = new XSSFWorkbook();
39	CreationHelper creationHelper = wb.getCreationHelper();	37	Sheet sheet = wb.createSheet("Sheet 1");
40	Sheet sheet = wb.createSheet("Sheet 1");	38	final int NUM_OF_ROWS = 3;
41	final int NUM_OF_ROWS = 3;	39	final int NUM_OF_COLUMNS = 10;
42	final int NUM_OF_COLUMNS = 10;
43		40
44	// Create a row and put some cells in it. Rows are 0 based.	41	// Create a row and put some cells in it. Rows are 0 based.
45	Row row;	42	Row row;
46	Cell cell;	43	Cell cell;
47	for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++) {	44	for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++) {
48	row = sheet.createRow((short)rowIndex);	45	row = sheet.createRow((short) rowIndex);
49	for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) {	46	for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) {
50	cell = row.createCell((short)colIndex);	47	cell = row.createCell((short) colIndex);
51	cell.setCellValue(colIndex * (rowIndex + 1));	48	cell.setCellValue(colIndex * (rowIndex + 1));
52	}	49	}
53	}	50	}
54		51
55	Drawing drawing = sheet.createDrawingPatriarch();	52	Drawing drawing = sheet.createDrawingPatriarch();
56	ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 15);	53	ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 15);
57		54
58	Chart chart = drawing.createChart(anchor);	55	Chart chart = drawing.createChart(anchor);
59	ChartLegend legend = chart.getOrCreateLegend();	56	ChartLegend legend = chart.getOrCreateLegend();
60	legend.setPosition(LegendPosition.TOP_RIGHT);	57	legend.setPosition(LegendPosition.TOP_RIGHT);
61		58
62	ScatterChartData data = chart.getChartDataFactory().createScatterChartData();	59	ScatterChartData data = chart.getChartDataFactory().createScatterChartData();
63		60
64	ValueAxis bottomAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.BOTTOM);	61	ValueAxis bottomAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.BOTTOM);
65	ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);	62	ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);
66		63
67	DataMarker xMarker = new DataMarker(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1));	64	ChartDataSource<Number> xs = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1));
68	DataMarker y1Marker = new DataMarker(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1));	65	ChartDataSource<Number> ys1 = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1));
69	DataMarker y2Marker = new DataMarker(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1));	66	ChartDataSource<Number> ys2 = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1));
70		67
71		68
72	data.addSerie(xMarker, y1Marker);	69	data.addSerie(xs, ys1);
73	data.addSerie(xMarker, y2Marker);	70	data.addSerie(xs, ys2);
74		71
75	chart.plot(data, bottomAxis, leftAxis);	72	chart.plot(data, bottomAxis, leftAxis);
76		73
77	// Write the output to a file	74	// Write the output to a file
78	FileOutputStream fileOut = new FileOutputStream("ooxml-scatter-chart.xlsx");	75	FileOutputStream fileOut = new FileOutputStream("ooxml-scatter-chart.xlsx");
79	wb.write(fileOut);	76	wb.write(fileOut);
80	fileOut.close();	77	fileOut.close();
81	}	78	}
82	}	79	}