Excell charts for clustered 3d bar and 3d cylinder shown correctly by order, and the setting for
chart.getCategoryAxis().setPlotOrderReversed(true/false) works correctly.
If I try to use chart.toImage( pictureBytes, imageOptions ); then I have problem
reversed plot order for category axis is ignored.
Also for chart as OLE object it’s ignored.
Reversed value axis is correct.
Hi,
Kindly attach your template file and output chart image here, we will check your issue soon.
Also, which version of the product you are using?
Thank you.
Hi, excel contain correct reversed chart
PP contains chart as OLE with incorrect order
and word also as image.
Also for the same chart which are correctly shown in excel if I got toImage, then I had incorrect order.
Hi,
Thanks for providing us the template Excel file.
After an initial test, I have found the issue as you have mentioned. It is a Chart-to-Image feature issue. I have logged a ticket with an id: CELLSJAVA-40043. We will figure it out soon.
Thank you.
Thanks, one moment how do I know about fix the bug?
Hi,
When the bug will be fixed. We will update you on this thread by posting. Also, we will set the issue flag as resolved later on and after some time, it will reflect on the top left corner of this thread as resolved.
unfortunately new version doesn’t affect the my problems with the order in bars and the order in legend.
Hi,
It works fine with new fix v7.0.3.3 here as I tested. Please find attached the bitmaps that is taken from the first chart in your template Excel file.
I used a simplest code:
Workbook workbook = new Workbook(“prolems+bars-1.xlsx”);
Chart chart = workbook.getWorksheets().get(0).getCharts().get(0);
chart.toImage(“myimgrevers_1.bmp”);
Please make sure that you are using the latest fixed version i.e. v7.0.3.3.
If you still find the issue, kindly give us your sample code same as mine and attach the output image file here, we will check it soon.
Thank you.
The issues you have found earlier (filed as CELLSJAVA-40043) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by aspose.notifier.
Hi
Amjad, I have problem again. I found that if if open exist excel file and generate new file with different format for example pdf, then it’s ok. Flag reversed category axis is fine works. But if create new file add the same charts with the save settings, the result is failed. toImage ignored the flag “reversed” for category axis.
The next java code opens exist file and converts exist charts to image, also add new chart with selected chart.getCategoryAxis().setPlotOrderReversed( true ).
if exist file it’s ok, category axis on image is converted, if the file is new the flag is ignored.
public class mainFormatToPdf
{
public static void main(String[] args){
try
{
//any exist file
String path = “C:\Users\dbk\AppData\Local\Temp\Copy of prolems bars 7.0.4 f-rev s-not rev.xlsx”;
Workbook workbook = new Workbook( path );
//for new excel file
// Workbook workbook = new Workbook( );
addConvertedCharts( workbook );
addCustomCharts( workbook.getWorksheets().add( “added worksheet” ) );
//Save in Excel2003 format
workbook.save( "C:\Users\dbk\AppData\Local\Temp\exist file reversed " + System.currentTimeMillis() + “.xls”,
FileFormatType.EXCEL_97_TO_2003 );
}
catch( FileNotFoundException e )
{
}
catch( IOException e )
{
}
catch( Exception e )
{
}
}
private static void addConvertedCharts( final Workbook workbook )
{
final Worksheet worksheet = workbook.getWorksheets().get( 0 );
ChartCollection collection = worksheet.getCharts();
for(int i =0 ; i< collection.getCount();i++ ) {
Chart chart = collection.get( i );
try// to show in excel the chart as image, it is used for debug of aspose behavior
{
ImageOrPrintOptions imageOptions = new ImageOrPrintOptions ();
imageOptions.setImageFormat( ImageFormat.getPng() );
imageOptions.setHorizontalResolution( 352);
imageOptions.setVerticalResolution( 300);
java.io.ByteArrayOutputStream pictureBytes = new java.io.ByteArrayOutputStream();
chart.toImage( pictureBytes, imageOptions );
final byte[] data = pictureBytes.toByteArray();
final int rowPosition = 160 + i * 13;
worksheet.getShapes().addPicture( rowPosition, 0, new java.io.ByteArrayInputStream( data ) ,100,100 );
}
catch( Exception e ){ }
}
}
static void addCustomCharts( final Worksheet worksheet ){
Cells cells = worksheet.getCells();
//Adding a sample value to “A1” cell
cells.get( “A1” ).setValue( 50 );
cells.get( “B1” ).setValue( 20 );
cells.get( “C1” ).setValue( 90 );
//Adding a sample value to “C1” cell as category data
cells.get( “D1” ).setValue( “Q1” );
//
//Adding a chart to the worksheet
ChartCollection charts = worksheet.getCharts();
//Accessing the instance of the newly added chart
int chartIndex = charts.add( ChartType.BAR_3_D_CLUSTERED, 5, 0, 15, 5 );
Chart chart = charts.get( chartIndex );
chart.getCategoryAxis().setPlotOrderReversed( true );
//Adding NSeries (chart data source) to the chart ranging from “A1” cell to “B4”
SeriesCollection nSeries = chart.getNSeries();
nSeries.add( “A1:C1”, true );
//Setting the data source for the category data of NSeries
nSeries.setCategoryData( “D1:D1” );
try// to show in excel the chart as image, it is used for debug of aspose behavior
{
ImageOrPrintOptions imageOptions = new ImageOrPrintOptions ();
imageOptions.setImageFormat( ImageFormat.getPng() );
imageOptions.setHorizontalResolution( 525 );
imageOptions.setVerticalResolution(300 );
java.io.ByteArrayOutputStream pictureBytes = new java.io.ByteArrayOutputStream();
chart.toImage( pictureBytes, imageOptions );
final byte[] data = pictureBytes.toByteArray();
worksheet.getShapes().addPicture( 15, 1, new java.io.ByteArrayInputStream( data ) ,100,100 );
}
catch( Exception e ){ }
}
}
Hi,
After an initial test, I could not find the issue. I could not properly evaluate your issue properly with my template Excel file having sample charts using your code. I appreciate if you could give us your input and output Excel files here, also give us the screen shots demonstrating the issue area with red circles, it will help us to evaluate your issue properly and figuring your issue out soon.
Thank you.
The initial file “prolems bars-3.xlsx” contains two charts with reverted category axis.
exist file 1324975137828.xlsx is the result of converting the initial file.
The first step. The file was opened, exist charts were converted to image and added to the result.
The second step. New chart was added to new sheet with reverted category axis, the chart is placed as excel chart and as image. All is ok for this case.
new file 1324975162369.xlsx is the second result for the same code but for new excel file.
The first step hasn’t result because workbook doesn’t contain chart.
Only the second step has effect, but the order on image is wrong for category axis.
Hi,
I still unable to find the issue. I am using latest fix/version v7.0.4.2 (please try it). I used a simple dummy xlsx file as input file that does not contain charts in it. Here is the complete code and please find attached the output file here. Please check it, the output image in the sheet is fine as chart.
Sample code:
import java.io.*;import com.aspose.cells.*;
public class Test1
{
public static void main(String[] args){
try
{
//This is a simplest xlsx file which does not contain charts.
String path = “e:\test\book1.xlsx”;
Workbook workbook = new Workbook( path );
//for new excel file
// Workbook workbook = new Workbook( );
addConvertedCharts( workbook );
addCustomCharts( workbook.getWorksheets().add( “added worksheet” ) );
//Save in Excel2003 format
workbook.save( “myoutput.xlsx”);
}
catch( Exception e )
{
System.out.println (e.getMessage());
}
}
private static void addConvertedCharts( final Workbook workbook )
{
final Worksheet worksheet = workbook.getWorksheets().get( 0 );
ChartCollection collection = worksheet.getCharts();
for(int i =0 ; i< collection.getCount();i++ ) {
Chart chart = collection.get( i );
try// to show in excel the chart as image, it is used for debug of aspose behavior
{
ImageOrPrintOptions imageOptions = new ImageOrPrintOptions ();
imageOptions.setImageFormat( ImageFormat.getPng() );
imageOptions.setHorizontalResolution( 352);
imageOptions.setVerticalResolution( 300);
java.io.ByteArrayOutputStream pictureBytes = new java.io.ByteArrayOutputStream();
chart.toImage( pictureBytes, imageOptions );
final byte[] data = pictureBytes.toByteArray();
final int rowPosition = 160 + i * 13;
worksheet.getShapes().addPicture( rowPosition, 0, new java.io.ByteArrayInputStream( data ) ,100,100 );
}
catch( Exception e ){ }
}
}
static void addCustomCharts( final Worksheet worksheet ){
Cells cells = worksheet.getCells();
//Adding a sample value to “A1” cell
cells.get( “A1” ).setValue( 50 );
cells.get( “B1” ).setValue( 20 );
cells.get( “C1” ).setValue( 90 );
//Adding a sample value to “C1” cell as category data
cells.get( “D1” ).setValue( “Q1” );
//
//Adding a chart to the worksheet
ChartCollection charts = worksheet.getCharts();
//Accessing the instance of the newly added chart
int chartIndex = charts.add( ChartType.BAR_3_D_CLUSTERED, 5, 0, 15, 5 );
Chart chart = charts.get( chartIndex );
chart.getCategoryAxis().setPlotOrderReversed( true );
//Adding NSeries (chart data source) to the chart ranging from “A1” cell to “B4”
SeriesCollection nSeries = chart.getNSeries();
nSeries.add( “A1:C1”, true );
//Setting the data source for the category data of NSeries
nSeries.setCategoryData( “D1:D1” );
try// to show in excel the chart as image, it is used for debug of aspose behavior
{
ImageOrPrintOptions imageOptions = new ImageOrPrintOptions ();
imageOptions.setImageFormat( ImageFormat.getPng() );
imageOptions.setHorizontalResolution( 525 );
imageOptions.setVerticalResolution(300 );
java.io.ByteArrayOutputStream pictureBytes = new java.io.ByteArrayOutputStream();
chart.toImage( pictureBytes, imageOptions );
final byte[] data = pictureBytes.toByteArray();
worksheet.getShapes().addPicture( 15, 1, new java.io.ByteArrayInputStream( data ) ,100,100 );
}
catch( Exception e ){ }
}
}
If you still find the issue with v7.0.4.2, please give us runnable code and template files, we will check it soon.
Thank you.
Hi,
Sorry for the confusion,
Now, I can find the issue as you mentioned. All I need to do it to instantiate new Workbook and add a chart to a worksheet and then to render image of the chart, the image has issue regarding order as mentioned.
Updated Sample code:
import java.io.*; import com.aspose.cells.*;
public class Test1
{
public static void main(String[] args){
try
{
//This is a simplest xlsx file which does not contain charts.
String path = “e:\test\book1.xlsx”;
//This will work fine.
// Workbook workbook = new Workbook( path );
//for new excel file – This will not work fine regarding chart image
Workbook workbook = new Workbook( );
addConvertedCharts( workbook );
addCustomCharts( workbook.getWorksheets().add( “added worksheet” ) );
//Save in Excel2003 format
workbook.save( “myoutput.xlsx”);
}
catch( Exception e )
{
System.out.println (e.getMessage());
}
}
private static void addConvertedCharts( final Workbook workbook )
{
final Worksheet worksheet = workbook.getWorksheets().get( 0 );
ChartCollection collection = worksheet.getCharts();
for(int i =0 ; i< collection.getCount();i++ ) {
Chart chart = collection.get( i );
try// to show in excel the chart as image, it is used for debug of aspose behavior
{
ImageOrPrintOptions imageOptions = new ImageOrPrintOptions ();
imageOptions.setImageFormat( ImageFormat.getPng() );
imageOptions.setHorizontalResolution( 352);
imageOptions.setVerticalResolution( 300);
java.io.ByteArrayOutputStream pictureBytes = new java.io.ByteArrayOutputStream();
chart.toImage( pictureBytes, imageOptions );
final byte[] data = pictureBytes.toByteArray();
final int rowPosition = 160 + i * 13;
worksheet.getShapes().addPicture( rowPosition, 0, new java.io.ByteArrayInputStream( data ) ,100,100 );
}
catch( Exception e ){ }
}
}
static void addCustomCharts( final Worksheet worksheet ){
Cells cells = worksheet.getCells();
//Adding a sample value to “A1” cell
cells.get( “A1” ).setValue( 50 );
cells.get( “B1” ).setValue( 20 );
cells.get( “C1” ).setValue( 90 );
//Adding a sample value to “C1” cell as category data
cells.get( “D1” ).setValue( “Q1” );
//
//Adding a chart to the worksheet
ChartCollection charts = worksheet.getCharts();
//Accessing the instance of the newly added chart
int chartIndex = charts.add( ChartType.BAR_3_D_CLUSTERED, 5, 0, 15, 5 );
Chart chart = charts.get( chartIndex );
chart.getCategoryAxis().setPlotOrderReversed( true );
//Adding NSeries (chart data source) to the chart ranging from “A1” cell to “B4”
SeriesCollection nSeries = chart.getNSeries();
nSeries.add( “A1:C1”, true );
//Setting the data source for the category data of NSeries
nSeries.setCategoryData( “D1:D1” );
try// to show in excel the chart as image, it is used for debug of aspose behavior
{
ImageOrPrintOptions imageOptions = new ImageOrPrintOptions ();
imageOptions.setImageFormat( ImageFormat.getPng() );
imageOptions.setHorizontalResolution( 525 );
imageOptions.setVerticalResolution(300 );
java.io.ByteArrayOutputStream pictureBytes = new java.io.ByteArrayOutputStream();
chart.toImage( pictureBytes, imageOptions );
final byte[] data = pictureBytes.toByteArray();
worksheet.getShapes().addPicture( 15, 1, new java.io.ByteArrayInputStream( data ) ,100,100 );
}
catch( Exception e ){ }
}
}
I have reopened your issue again, we will look into it soon to figure it out.
Thank you.
Hi,
Please try the following code. You must confirm the new Workbook’s file format must be same as Save’s file format.
C#
//The new Workbook’s format must be same as Save’s format.
int fileFormatType = FileFormatType.EXCEL_97_TO_2003;
//or
// int fileFormatType = = FileFormatType.XLSX;
Workbook workbook = new Workbook(fileFormatType);
addConvertedCharts( workbook );
addCustomCharts( workbook.getWorksheets().add( “added worksheet” ) );
workbook.save( path + “myoutput.xls”, fileFormatType);
//or
// workbook.save( path + “myoutput.xlsx”, fileFormatType);
Hi,
I thank you for your solution. It works.
I’ve only just had time to check it out.