Page 1 of 1

Change plot LineColor in Calc, sometimes useless

Posted: Tue Jul 05, 2016 12:10 pm
by zhenzhang20
I installed OpenOffice 4.1.2 32bit and SDK 4.1.2 on my machine.

I found a demo under : C:\Program Files (x86)\OpenOffice 4\sdk\examples\java\Spreadsheet\ChartTypeChange.java.
I modified part of the demo to change Line Color, but sometimes, the line color changed as excepted. But sometimes, the LineColor change failed(some line color does not change).

What I changed base on demo:
1 add two more lines in stringValues
2 only use line type "com.sun.star.chart.LineDiagram" in stringChartType
3 try to use below command to change Line Color: xDiagram.getDataRowProperties(0).setPropertyValue("LineColor", new Integer(10000));

Issue:
sometimes only part of lines color changed. But others did not. In below img, line of "Rival in business 3" color change failed
[img]
issued.PNG
[/img]

My code:

Code: Select all

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


import com.sun.star.awt.Rectangle;

import com.sun.star.beans.PropertyValue;
import com.sun.star.beans.XPropertySet;

import com.sun.star.chart.XDiagram;
import com.sun.star.chart.XChartDocument;

import com.sun.star.container.XNameAccess;
import com.sun.star.container.XIndexAccess;

import com.sun.star.document.XEmbeddedObjectSupplier;

import com.sun.star.frame.XComponentLoader;

import com.sun.star.lang.XComponent;
import com.sun.star.lang.XMultiServiceFactory;
import com.sun.star.lang.XMultiComponentFactory;

import com.sun.star.sheet.XSpreadsheets;
import com.sun.star.sheet.XSpreadsheet;
import com.sun.star.sheet.XSpreadsheetDocument;
import com.sun.star.sheet.XCellRangeAddressable;

import com.sun.star.table.XTableChart;
import com.sun.star.table.XTableCharts;
import com.sun.star.table.XCell;
import com.sun.star.table.XCellRange;
import com.sun.star.table.XTableChartsSupplier;
import com.sun.star.table.CellRangeAddress;

import com.sun.star.uno.UnoRuntime;
import com.sun.star.uno.XInterface;
import com.sun.star.uno.XComponentContext;



/** This class loads an OpenOffice.org Calc document and changes the type of the
 * embedded chart.
 * @author Bertram Nolte
 */
public class ChartTypeChange {
    
    /** Table chart, which type will be changed.
     */
    private XTableChart xtablechart = null;
    
    /** Service factory
     */
    private XMultiComponentFactory xMCF = null;
    
    /** Component context
     */
    private XComponentContext xCompContext = null;
    
    /** Beginning of the program.
     * @param args No arguments will be passed to the class.
     */
    public static void main(String args[]) {
        try {
            ChartTypeChange charttypechange = new ChartTypeChange();
            
            // Double array holding all values the chart should be based on.
            String[][] stringValues = {
                { "", "Jan", "Feb", "Mar", "Apr", "Mai" },
                { "Profit", "12.3", "43.2", "5.1", "76", "56.8" },
                { "Rival in business", "12.2", "12.6", "17.7", "20.4", "100" },
                { "Rival in business 2", "14.2", "14.6", "19.7", "22.4", "120" },
                { "Rival in business 3", "16.2", "16.6", "21.7", "24.4", "140" },
            };
            
            // Create the chart with
            charttypechange.getChart( stringValues );
            
            String[] stringChartType = {
                "com.sun.star.chart.LineDiagram",
//                "com.sun.star.chart.BarDiagram",
//                "com.sun.star.chart.PieDiagram",
//                "com.sun.star.chart.NetDiagram",
//                "com.sun.star.chart.XYDiagram",
//                "com.sun.star.chart.StockDiagram",
//                "com.sun.star.chart.AreaDiagram"
            };
            
            for ( int intCounter = 0; intCounter < stringChartType.length;
                  intCounter++ ) {
                charttypechange.changeChartType( stringChartType[ intCounter ],
                                                 false );
                Thread.sleep( 3000 );
            }
            
            System.exit(0);
        }
        catch( Exception exception ) {
            System.err.println( exception );
        }
    }
    
    /** The constructor connects to the OpenOffice.org.
     * @param args Parameters for this constructor (connection string).
     * @throws Exception All exceptions are thrown from this method.
     */
    public ChartTypeChange()
        throws Exception {
        
        /* Bootstraps a component context. Component context to be granted
           to a component for running. Arbitrary values can be retrieved
           from the context. */
        xCompContext = com.sun.star.comp.helper.Bootstrap.bootstrap();
        
        /* Gets the service manager instance to be used (or null). This method has
           been added for convenience, because the service manager is a often used
           object. */
        xMCF = xCompContext.getServiceManager();        
    }
    
    /** This method will change the type of a specified chart.
     * @param stringType The chart will be converted to this type.
     * @param booleanIs3D If the chart should be displayed in 3D this parameter should be set to true.
     * @throws Exception All exceptions are thrown from this method.
     */
    public void changeChartType( String stringType, boolean booleanIs3D )
    throws Exception {
        XEmbeddedObjectSupplier xEmbeddedObjSupplier = (XEmbeddedObjectSupplier)
            UnoRuntime.queryInterface(XEmbeddedObjectSupplier.class, xtablechart);
        XInterface xInterface = xEmbeddedObjSupplier.getEmbeddedObject();
        
        XChartDocument xChartDoc = (XChartDocument)UnoRuntime.queryInterface(
            XChartDocument.class, xInterface);
        XDiagram xDiagram = (XDiagram) xChartDoc.getDiagram();
        XMultiServiceFactory xMSF = (XMultiServiceFactory)
            UnoRuntime.queryInterface( XMultiServiceFactory.class, xChartDoc );
        Object object = xMSF.createInstance( stringType );
        xDiagram = (XDiagram) UnoRuntime.queryInterface(XDiagram.class, object);
        
        XPropertySet xPropSet = (XPropertySet) UnoRuntime.queryInterface(
            XPropertySet.class, xDiagram );
        xPropSet.setPropertyValue( "Dim3D", new Boolean( booleanIs3D ) );
        
        xDiagram.getDataRowProperties(0).setPropertyValue("LineColor", new Integer(10000));
        xDiagram.getDataRowProperties(1).setPropertyValue("LineColor", new Integer(10000));
        xDiagram.getDataRowProperties(2).setPropertyValue("LineColor", new Integer(10000));
        xDiagram.getDataRowProperties(3).setPropertyValue("LineColor", new Integer(10000));
        
        xChartDoc.setDiagram(xDiagram);
    }
    
    /** Loading an OpenOffice.org Calc document and getting a chart by name.
     * @param stringFileName Name of the OpenOffice.org Calc document which should
     *                       be loaded.
     * @param stringChartName Name of the chart which should get a new chart type.
     */
    public void getChart( String stringFileName, String stringChartName ) {
        try {
            /* A desktop environment contains tasks with one or more
               frames in which components can be loaded. Desktop is the
               environment for components which can instanciate within
               frames. */
            XComponentLoader xComponentloader = (XComponentLoader)
                UnoRuntime.queryInterface( XComponentLoader.class,
                    xMCF.createInstanceWithContext("com.sun.star.frame.Desktop",
                                                   xCompContext ) );
            
            // Load a Writer document, which will be automaticly displayed
            XComponent xComponent = xComponentloader.loadComponentFromURL(
            "file:///" + stringFileName, "_blank", 0,
            new PropertyValue[0] );
            
            // Query for the interface XSpreadsheetDocument
            XSpreadsheetDocument xSpreadSheetDocument = ( XSpreadsheetDocument )
                UnoRuntime.queryInterface( XSpreadsheetDocument.class, xComponent );
            
            XSpreadsheets xSpreadsheets = xSpreadSheetDocument.getSheets() ;
            
            XIndexAccess xIndexAccess = (XIndexAccess)
                UnoRuntime.queryInterface(XIndexAccess.class, xSpreadsheets );
            
            XSpreadsheet xSpreadsheet = (XSpreadsheet) UnoRuntime.queryInterface(
                XSpreadsheet.class, xIndexAccess.getByIndex(0));
            
            XTableChartsSupplier xTableChartsSupplier = ( XTableChartsSupplier )
                UnoRuntime.queryInterface( XTableChartsSupplier.class, xSpreadsheet );
            
            xIndexAccess = (XIndexAccess) UnoRuntime.queryInterface(
                XIndexAccess.class, xTableChartsSupplier.getCharts() );
            
            this.xtablechart = (XTableChart) UnoRuntime.queryInterface(
                XTableChart.class,  xIndexAccess.getByIndex( 0 ) );
        }
        catch( Exception exception ) {
            System.err.println( exception );
        }
    }
    
    /** Creating an empty OpenOffice.org Calc document, inserting data, and getting a
     * chart by name.
     * @param stringValues Double array with the values for the chart.
     */
    public void getChart( String[][] stringValues ) {
        try {
            /* A desktop environment contains tasks with one or more
               frames in which components can be loaded. Desktop is the
               environment for components which can instanciate within
               frames. */
            XComponentLoader xcomponentloader = ( XComponentLoader )
                UnoRuntime.queryInterface( XComponentLoader.class,
                                           xMCF.createInstanceWithContext(
                                               "com.sun.star.frame.Desktop",
                                               xCompContext ) );
            
            // Create an empty calc document, which will be automaticly displayed
            XComponent xComponent = xcomponentloader.loadComponentFromURL(
            "private:factory/scalc", "_blank", 0,
            new PropertyValue[0] );
            
            // Query for the interface XSpreadsheetDocument
            XSpreadsheetDocument xspreadsheetdocument = ( XSpreadsheetDocument )
                UnoRuntime.queryInterface( XSpreadsheetDocument.class, xComponent );
            
            // Get all sheets of the spreadsheet document.
            XSpreadsheets xspreadsheets = xspreadsheetdocument.getSheets() ;
            
            // Get the index of the spreadsheet document.
            XIndexAccess xindexaccess = (XIndexAccess) UnoRuntime.queryInterface(
                XIndexAccess.class, xspreadsheets );
            
            // Get the first spreadsheet.
            XSpreadsheet xspreadsheet = (XSpreadsheet) UnoRuntime.queryInterface(
                XSpreadsheet.class, xindexaccess.getByIndex(0));
            
            // The double array will written to the spreadsheet
            for ( int intY = 0; intY < stringValues.length; intY++ ) {
                for ( int intX = 0; intX < stringValues[ intY ].length;
                intX++ ) {
                    // Insert the value to the cell, specified by intY and intX.
                    this.insertIntoCell( intY, intX,
                    stringValues[ intY ][ intX ], xspreadsheet, "" );
                }
            }
            
            // Create a rectangle, which holds the size of the chart.
            Rectangle rectangle = new Rectangle();
            rectangle.X = 500;
            rectangle.Y = 3000;
            rectangle.Width = 25000;
            rectangle.Height = 11000;
            
            // Get the cell range of the spreadsheet.
            XCellRange xcellrange = ( XCellRange ) UnoRuntime.queryInterface(
                XCellRange.class, xspreadsheet );
            
            // Create the Unicode of the character for the column name.
            char charRectangle = ( char ) ( 65 + stringValues.length - 1 );
            
            // Get maximum length all rows in the double array.
            int intMaximumWidthRow = 0;
            for ( int intRow = 0; intRow < stringValues.length; intRow++ ) {
                if ( stringValues[ intRow ].length > intMaximumWidthRow ) {
                    intMaximumWidthRow = stringValues[ intRow ].length;
                }
            }
            
            // Get the cell range of the written values.
            XCellRange xcellrangeChart = xcellrange.getCellRangeByName( "A1:" +
            charRectangle + intMaximumWidthRow );
            
            // Get the addressable cell range.
            XCellRangeAddressable xcellrangeaddressable =
            ( XCellRangeAddressable ) UnoRuntime.queryInterface(
                XCellRangeAddressable.class, xcellrangeChart );
            
            // Get the cell range address.
            CellRangeAddress cellrangeaddress = xcellrangeaddressable.getRangeAddress();
            
            // Create the cell range address for the chart.
            CellRangeAddress[] cellrangeaddressChart =
            new CellRangeAddress[ 1 ];
            cellrangeaddressChart[ 0 ] = cellrangeaddress;
            
            // Get the table charts supplier of the spreadsheet.
            XTableChartsSupplier xtablechartssupplier = ( XTableChartsSupplier )
                UnoRuntime.queryInterface( XTableChartsSupplier.class, xspreadsheet );
            
            // Get all table charts of the spreadsheet.
            XTableCharts xtablecharts = xtablechartssupplier.getCharts();
            
            // Create a table chart with all written values.
            xtablecharts.addNewByName( "Example", rectangle,
            cellrangeaddressChart, true, true );
            
            // Get the created table chart.
            this.xtablechart = ( XTableChart ) UnoRuntime.queryInterface(
                XTableChart.class, (( XNameAccess ) UnoRuntime.queryInterface(
                    XNameAccess.class, xtablecharts ) ).getByName( "Example" ));
        }
        catch( Exception exception ) {
            System.err.println( exception );
        }
    }
    
    /** Inserting a given value to a cell, that is specified by the parameters intX
     * and intY.
     * @param intX Column on the spreadsheet.
     * @param intY Row on the spreadsheet.
     * @param stringValue Value to be inserted to a cell.
     * @param xspreadsheet Spreadsheet of the cell, which will be changed.
     * @param stringFlag If the value of stringFlag is "V", the stringValue
     *                   will be converted to the
     * float type. Otherwise the stringValue will be written as a formula.
     */
    public static void insertIntoCell( int intX, int intY, String stringValue,
                                       XSpreadsheet xspreadsheet, String stringFlag )
    {
        XCell xcell = null;
        
        try {
            xcell = xspreadsheet.getCellByPosition( intX, intY );
        }
        catch ( com.sun.star.lang.IndexOutOfBoundsException exception ) {
            System.out.println( "Could not get cell." );
        }
        if ( stringFlag.equals( "V" ) ) {
            xcell.setValue( ( new Float( stringValue ) ).floatValue() );
        }
        else {
            xcell.setFormula( stringValue );
        }
    }
}


My Computer Info:
Win 7 64bit
JDK 1.7.0_75 32bit

Re: Change plot LineColor in Calc, sometimes useless

Posted: Mon Jan 21, 2019 2:28 am
by misitu
Yup, I have the same problem.

I posted on Bugzilla but they obsoleted it. Not sure why as I supplied test results.

The issue for me is that I can choose colours for the first two lines, after that the colours revert to the installed table. Line 3 has colour 1 from the table, etc.

This applies to a basic program as a whole so if you have two charts the limitation applies throughout. After 2 colours you are back to the table.

It's very annoying!

I have a current AOO - 4.1.5.

I will try to contact the team. Maybe it's a simple bit of code that someone overlooked in testing.

Cheers!