Dimensions and style does not match when copying charts

dvector · October 26, 2018, 10:00am

Hi,

I am trying to copy charts from one sheet to another. My input file is charts.xlsx and the output file is chartsCopy.xlsx

chartsCopy.xlsx.zip (15.8 KB)
charts.zip (56.8 KB)

The code I am using is :

Workbook oldWorkbook = new Workbook("/Users/charts.xlsx");
    Workbook newWorkbook = new Workbook();
    Worksheet oldSheet = oldWorkbook.getWorksheets().get(0);
    Worksheet newSheet = newWorkbook.getWorksheets().get(0);

    ChartCollection oldChartCollection = oldSheet.getCharts();


    int count = oldChartCollection.getCount();

    for(int i=0; i<count; i++) {
        Chart chart = oldChartCollection.get(i);
        ChartShape shape = chart.getChartObject();

        try {
            newSheet.getShapes().addCopy(shape,
                    shape.getUpperLeftRow(),
                    shape.getTop(),
                    shape.getUpperLeftColumn(),
                    shape.getLeft()
            );
        } catch (Exception e) {

        }

    }
    newWorkbook.save("/Users/chartsCopy.xlsx");

The problem is the chart dimensions and the style do not match and I am getting a error that there might be links to another sheet in the output file. Am i doing anything wrong? Can you please review the code.

Thanks.

amjad.sahi · October 26, 2018, 11:25am

@dvector,

Thanks for the template file and sample code.

I have checked your template file and found your underlying charts’ data source is external (data source is referring to some external file “3.xlsb” , etc.). Aspose.Cells does not support to render charts with external references as their data sources. I guess this is the reason for your issue. Please modify your template file to specify data sources in your template file (i.e., “charts.xlsx”) sheet and then try your scenario/case.

In case, you still find the issue, kindly do provide your updated template file and sample code, we will check it soon.

dvector · October 26, 2018, 12:40pm

Hi,

Thanks for pointing that out. I created a fresh file and ran the following code :

Workbook oldWorkbook = new Workbook("/Users/subhrojitn/Downloads/charts1.xlsx");
    Workbook newWorkbook = new Workbook();
    Worksheet oldSheet = oldWorkbook.getWorksheets().get(0);
    Worksheet newSheet = newWorkbook.getWorksheets().get(0);

    Cells oldCells = oldSheet.getCells();
    Cells newCells = newSheet.getCells();
    int maxRow = oldCells.getMaxDataRow();
    int maxCol = oldCells.getMaxDataColumn();

    StyleFlag styleFlag = new StyleFlag();
    styleFlag.setAll(true);

    for (int r=0; r<=maxRow ; r++) {
        for (int c = 0; c <= maxCol; c++) {

            Cell oldCell = oldCells.get(r,c);
            Cell newCell = newCells.get(r,c);

            int type = oldCell.getType();

            switch(type) {
                case CellValueType.IS_NUMERIC:
                    newCell.setValue(oldCell.getDoubleValue());
                    break;
                case CellValueType.IS_DATE_TIME:
                    newCell.setValue(oldCell.getDateTimeValue());
                    break;
                case CellValueType.IS_STRING:
                    newCell.setValue(oldCell.getDisplayStringValue());
            }

            newCell.setStyle(oldCell.getDisplayStyle());
        }
    }


    ChartCollection oldChartCollection = oldSheet.getCharts();


    int count = oldChartCollection.getCount();

    for(int i=0; i<count; i++) {
        Chart chart = oldChartCollection.get(i);
        ChartShape shape = chart.getChartObject();

        try {
            newSheet.getShapes().addCopy(shape,
                    shape.getUpperLeftRow(),
                    shape.getTop(),
                    shape.getUpperLeftColumn(),
                    shape.getLeft()
            );
        } catch (Exception e) {

        }

    }
    newWorkbook.save("/Users/subhrojitn/Downloads/chartsCopy1.xlsx");

I am still getting the dimension and style problems:

chartsCopy1.xlsx.zip (15.1 KB)
charts1.xlsx.zip (56.3 KB)

amjad.sahi · October 26, 2018, 2:11pm

@dvector,

Please add a line to your code segment, it will work fine for formatting/style issue:
e.g
Sample code:

…
ChartCollection oldChartCollection = oldSheet.getCharts();

int count = oldChartCollection.getCount();

for(int i=0; i<count; i++) {
    Chart chart = oldChartCollection.get(i);
    ChartShape shape = chart.getChartObject();

    try {
        newSheet.getShapes().addCopy(shape,
                shape.getUpperLeftRow(),
                shape.getTop(),
                shape.getUpperLeftColumn(),
                shape.getLeft()
        );
    } catch (Exception e) {

    }

}

newWorkbook.copyTheme(oldWorkbook);
newWorkbook.save("/Users/subhrojitn/Downloads/chartsCopy1.xlsx");

dvector · October 29, 2018, 6:59am

Hello,
Thanks for your reply. The change you suggested takes care of the styles. Now that the styles are being copied fine, the dimension still does not match.

I am attaching the updated file. The charts are on top of each other:

chartsCopy3.xlsx.zip (15.2 KB)

amjad.sahi · October 29, 2018, 9:21am

@dvector,

Thanks for the output file and details.

You are right. I am able to observe the issue as you mentioned. Although using Workbook.copyTheme() method, the styles are retained but still the charts’ dimensions are not matched when copying charts, the charts are on top of each other.

I have logged a ticket with an id “CELLSJAVA-42743” for your issue. We will look into it soon.

Once we have an update on it, we will let you know here.

ahsaniqbalsidiqui · October 31, 2018, 6:56am

@dvector,

Please try the following code to copy data and style:

    Workbook oldWorkbook = new Workbook(sourceFileDir + "charts1.xlsx");
    Workbook newWorkbook = new Workbook();
    newWorkbook.copyTheme(oldWorkbook);
    Style defaultStyle = newWorkbook.getDefaultStyle();
    defaultStyle.copy(oldWorkbook.getDefaultStyle());
    newWorkbook.setDefaultStyle(defaultStyle);
    Worksheet oldSheet = oldWorkbook.getWorksheets().get(0);
    Worksheet newSheet = newWorkbook.getWorksheets().get(0);

    Cells oldCells = oldSheet.getCells();
    Cells newCells = newSheet.getCells();
    int maxRow = oldCells.getMaxDataRow();
    int maxCol = oldCells.getMaxDataColumn();

    StyleFlag styleFlag = new StyleFlag();
    styleFlag.setAll(true);

    Range oldRange = oldCells.createRange(0, 0, maxRow + 1, maxCol + 1);
    Range newRange = newCells.createRange(0, 0, maxRow + 1, maxCol + 1);
    PasteOptions options = new PasteOptions();
    options.setPasteType(PasteType.VALUES_AND_NUMBER_FORMATS);
    newRange.copy(oldRange, options);
    options.setPasteType(PasteType.COLUMN_WIDTHS);
    newRange.copy(oldRange, options);

Let us know your feedback.

dvector · October 31, 2018, 11:09am

Hi,

This is great. I have attached the output file. While the horizontal dimensions are fine now, the vertical dimensions are still off. Is there any way to improve that?

chartsCopy4.xlsx.zip (14.4 KB)

amjad.sahi · October 31, 2018, 12:56pm

@dvector,

Thanks for sample file.

Please try to add the following lines of code to your code segment, it will work fine:
e.g
Sample code:

…
PasteOptions options = new PasteOptions();
  options.setPasteType(PasteType.VALUES_AND_NUMBER_FORMATS);
  newRange.copy(oldRange, options);
 
  options.setPasteType(PasteType.COLUMN_WIDTHS);
    newRange.copy(oldRange, options);
options.setPasteType(PasteType.ROW_HEIGHTS);
newRange.copy(oldRange, options);

…

amjad.sahi · November 2, 2018, 12:05pm

@dvector,

Please try the following sample code with the fix/version: Aspose.Cells for Java v18.10.3 (attached):
e.g
Sample code:

sourceFileDir = "d:/filetemp/";
		Workbook oldWorkbook = new Workbook(sourceFileDir + "charts1.xlsx");
        Workbook newWorkbook = new Workbook();
        newWorkbook.copyTheme(oldWorkbook);
        Style defaultStyle = newWorkbook.getDefaultStyle();
        defaultStyle.copy(oldWorkbook.getDefaultStyle());
        newWorkbook.setDefaultStyle(defaultStyle);
        Worksheet oldSheet = oldWorkbook.getWorksheets().get(0);
        AutoFitterOptions fitOptions = new AutoFitterOptions();
        fitOptions.setOnlyAuto(true);
        oldSheet.autoFitColumns(fitOptions);
        Worksheet newSheet = newWorkbook.getWorksheets().get(0);

        Cells oldCells = oldSheet.getCells();
        Cells newCells = newSheet.getCells();
        int maxRow = oldCells.getMaxDataRow();
        int maxCol = oldCells.getMaxDataColumn();

        StyleFlag styleFlag = new StyleFlag();
        styleFlag.setAll(true);

        Range oldRange = oldCells.createRange(0, 0, maxRow + 1, maxCol + 1);
        Range newRange = newCells.createRange(0, 0, maxRow + 1, maxCol + 1);
        PasteOptions options = new PasteOptions();
        options.setPasteType(PasteType.VALUES_AND_NUMBER_FORMATS);
        newRange.copy(oldRange, options);
        options.setPasteType(PasteType.COLUMN_WIDTHS);
        newRange.copy(oldRange, options);
		  System.out.println(Locale.getDefault());

Let us know your feedback.
Aspose.Cells for Java_v18.10.3.zip (6.3 MB)

dvector · November 29, 2018, 9:31am

Hi,

This does not seem to work. I am using Aspose version aspose-cells-18.10.5.jar
Input file: w3.xlsx.zip (49.5 KB)
Output file: w3Copy.xlsx.zip (18.9 KB)

Code used:

Workbook oldWorkbook = new Workbook( "/Users/w3.xlsx");
    Workbook newWorkbook = new Workbook();
    newWorkbook.copyTheme(oldWorkbook);
    Style defaultStyle = newWorkbook.getDefaultStyle();
    defaultStyle.copy(oldWorkbook.getDefaultStyle());
    newWorkbook.setDefaultStyle(defaultStyle);


    int count = oldWorkbook.getWorksheets().getCount();
    for(int i=0;i<count;i++) {
        Worksheet oldSheet = oldWorkbook.getWorksheets().get(i);

        Worksheet newSheet ;
        try {
            newSheet = newWorkbook.getWorksheets().get(i);
        } catch (Exception e) {
            newWorkbook.getWorksheets().add();
            newSheet = newWorkbook.getWorksheets().get(i);
        }

        Cells oldCells = oldSheet.getCells();
        Cells newCells = newSheet.getCells();
        int maxRow = oldCells.getMaxDataRow();
        int maxCol = oldCells.getMaxDataColumn();

        StyleFlag styleFlag = new StyleFlag();
        styleFlag.setAll(true);
        if(maxRow<0) {
            maxRow=0;
        }
        if(maxCol<0) {
            maxCol=0;
        }

        ChartCollection oldChartCollection = oldSheet.getCharts();

        int count2 = oldChartCollection.getCount();

        for(int j=0; j<count2; j++) {
            Chart chart = oldChartCollection.get(j);
            ChartShape shape = chart.getChartObject();

            try {
                newSheet.getShapes().addCopy(shape,
                        shape.getUpperLeftRow(),
                        shape.getTop(),
                        shape.getUpperLeftColumn(),
                        shape.getLeft()
                );
            } catch (Exception e) {

            }

        }

        Range oldRange = oldCells.createRange(0, 0, maxRow + 100, maxCol + 100);
        Range newRange = newCells.createRange(0, 0, maxRow + 100, maxCol + 100);
        PasteOptions options = new PasteOptions();
        options.setPasteType(PasteType.VALUES_AND_NUMBER_FORMATS);
        newRange.copy(oldRange, options);
        options.setPasteType(PasteType.COLUMN_WIDTHS);
        newRange.copy(oldRange, options);
        options.setPasteType(PasteType.ROW_HEIGHTS);
        newRange.copy(oldRange, options);
    }
    

    newWorkbook.save("/Users/w3Copy.xlsx");

The problem seems to be the width of the charts. It is always more than the original while the height is correct. Can you please check this?

Thanks

amjad.sahi · November 29, 2018, 11:01am

@dvector,

Thanks for the template file and sample code.

Please try the following updated sample code with your template file, it will work Ok (though there might be a slight difference for column widths but charts are ok and you have to live with it):
e.g
Sample code:

Workbook oldWorkbook = new Workbook("f:\\files\\w3.xlsx");
	    Workbook newWorkbook = new Workbook();
	    newWorkbook.copyTheme(oldWorkbook);
	    Style defaultStyle = newWorkbook.getDefaultStyle();
	    defaultStyle.copy(oldWorkbook.getDefaultStyle());
	    newWorkbook.setDefaultStyle(defaultStyle);

	    int count = oldWorkbook.getWorksheets().getCount();
	    for(int i=0;i<count;i++) {
	        Worksheet oldSheet = oldWorkbook.getWorksheets().get(i);
	        Worksheet newSheet ;
	        try {
	            newSheet = newWorkbook.getWorksheets().get(i);	           
	        } catch (Exception e) {
	            newWorkbook.getWorksheets().add();
	            newSheet = newWorkbook.getWorksheets().get(i);
	        }
	        
	        Cells oldCells = oldSheet.getCells();
	        Cells newCells = newSheet.getCells();
	        Range range = oldCells.getMaxDisplayRange();
	        System.out.println(oldCells.getMaxDisplayRange().toString());
	        int maxRow = range.getFirstRow() + range.getRowCount()-1;
	        int maxCol = range.getFirstColumn() + range.getColumnCount()-1;
	        
	        System.out.println(maxRow);
	        System.out.println(maxCol);
	        
	        StyleFlag styleFlag = new StyleFlag();
	        styleFlag.setAll(true);
	        
	        Range oldRange = oldCells.createRange(0, 0, maxRow + 1, maxCol + 1);
	        Range newRange = newCells.createRange(0, 0, maxRow + 1, maxCol + 1);
	        PasteOptions options = new PasteOptions();
	        options.setPasteType(PasteType.ALL);
	        newRange.copy(oldRange, options);	     
	        options.setPasteType(PasteType.COLUMN_WIDTHS);
	        newRange.copy(oldRange, options);
	        options.setPasteType(PasteType.ROW_HEIGHTS);
	        newRange.copy(oldRange, options);
	    }
	    

	    newWorkbook.save("f:\\files\\out1.xlsx");

Hope, this helps a bit.

dvector · November 29, 2018, 11:24am

Hi,

This works but this copies the formulas due to PasteType.ALL as well which i am trying to avoid. Is there anyway to prevent formulas from being copied while keeping the rest the same? Also since you are already using PasteType.ALL do I need the lines with PasteType.COLUMN_WIDTHS and PasteType.ROW_HEIGHTS ?

amjad.sahi · November 29, 2018, 11:32am

@dvector,

Please try the following updated code segment (last part) instead, it will remove formulas and replace with calculated values in the cells for your needs:
e.g
Sample code:

…
Range oldRange = oldCells.createRange(0, 0, maxRow + 1, maxCol + 1);
Range newRange = newCells.createRange(0, 0, maxRow + 1, maxCol + 1);
PasteOptions options = new PasteOptions();
options.setPasteType(PasteType.ALL);
newRange.copy(oldRange, options);
newRange.getWorksheet().getCells().removeFormulas();

}
  newWorkbook.save("f:\\files\\out1.xlsx");
…

Hope, this helps a bit.

dvector · November 29, 2018, 11:39am

This looks great. I will test out and let you know