We have been using Apose Cells for Java 1.9.4.0 and the graphs we have been creating worked great. The first column contains date strings and these strings are used for the X axis labels.
When I drop in the new jar file for 2.0.1.0 these labels become a number like 1, 2, 3 ,4 etc instead of the dates that had been displaying.
Did something change in terms of defining the "Category (X) axis labels" as it shows in Excel in the Series tab of the Source Data dialog?
Thanks,
Steve
Hi Steve,
Could you create a sample code with template files and post us here to show the issue you have mentioned. We will check it soon.
Thank you.
Hi Amjad,
Attached is code that duplicates the problem.
I tried several versions of Aspose Cells for Java and found that starting with 2.0.0.0 things stopped working. Here are the versions I had and tested to see if they worked or not:
1.9.4.0 - Labels appear as found in column A
1.9.5.1 - Labels appear as found in column A
1.9.5.20 - Labels appear as found in column A
1.9.5.33 - Labels appear as found in column A
2.0.0.0 - Labels appear as numbers starting at 1
2.0.1.0 - Labels still appear as number starting at 1
I have also attached xls files created by this code. One is using Aspose Cells 1.9.4.0 and the other is using 2.0.1.0.
Let me know if you find something.
Thanks,
Steve
Hi Steve,
I think you may modify your code a bit, see the following your updated code:
.............
/*
***************************************************************
** Setup the chart
*/
Charts charts = linechart.getCharts();
//Adding a chart to the worksheet
Chart chart = charts.addChart(ChartType.LINE,0,0,42,20);
//Set the title
Title title = chart.getTitle();
title.setText("Work Server %busy");
Font titleFont = title.getFont();
titleFont.setBold(true);
title.setFont(titleFont);
// Reset total number of rows to chart for now
if (iTotRows > iMaxRows)
iTotRows = iMaxRows;
NSeries nSeries = chart.getNSeries();
//Adding NSeries (chart data source) to the chart ranging from "B5" cell to "C7"
String cellName = null;
String lastCellName = null;
StringBuilder dataString = null;;
ASeries aSeries = null;
// Loop through all columns B-G (1-6)
for (int i=1; i<7; i++) {
cell = cells.getCell (4, i);
cellName = cell.getName();
cell = cells.getCell (iTotRows + 4, i);
lastCellName = cell.getName();
/* Build string of the data range */
dataString = new StringBuilder("'");
dataString.append("CPU Data" + "'!" + cellName + ":" + lastCellName);
int iSeriesIndex = nSeries.add(dataString.toString(), true);
aSeries = nSeries.get(iSeriesIndex);
aSeries.getArea().setForegroundColor(lineColors[i-1]);
aSeries.getBorder().setColor(lineColors[i-1]);
cell = cells.getCell (4, i);
cellName = cell.getName();
aSeries.setName("='" + "CPU Data" + "'!" + cellName);
}
//Setting the data source for the category data of NSeries
cell = cells.getCell (4, 0);
cellName = cell.getName();
cell = cells.getCell (iTotRows + 4, 0);
lastCellName = cell.getName();
/* Build string of the data range */
dataString = new StringBuilder("'");
dataString.append("CPU Data" + "'!" + cellName + ":" + lastCellName);
nSeries.setCategoryData(dataString.toString());
// Try again
CategoryAxis catAxis = chart.getCategoryAxis();
catAxis.setCategoryType(CategoryType.CATEGORY_SCALE);
catAxis.setTickLabelSpacing(12);
ValueAxis valAxis = chart.getValueAxis();
valAxis.setMaxValue(1.0);
//Set the Value Axis title
Title vTitle = valAxis.getTitle();
vTitle.setText("%busy");
vTitle.setRotation(90);
chart.getValueAxis().setMinValue(0.0);
// Remove any leading sheets before the ones we created.
for(int i = worksheets.size() - (iNewSheets + 1); i >= 0; --i) {
worksheets.removeSheet(i);
}
workbook.save(outputPath, fileFormatType);
}
Thank you.
Hi Steve,
To update you further, actually, we made some enhancements for chart support in our newer versions. Now, we try to do the same as MS Excel does. Alternatively, using the newer versions of the component (e.g v2.0.1), you may also change your following code, i.e..,
Hi Amjad,
Your suggestion worked but why would I have to wait and set the Category Data last? It appears in 1.9.5.0 and earlier it didn't matter when I set it.
Also thanks for the suggestion for simplifying my code but the example I sent is loosely based on the logic of my actual code. In my code the column I have to define for the Category Data may not always be the first column. I have to identify the "date" column and then use it.
Steve
Hi Steve,
Thank you for considering Aspose.
The change of setting CategoryData is because we have re-built our Chart model. Now, we attach the CategoryData with ASeries object. If there is no ASeries in NSeries collection, setting CategoryData takes no effect. We believe this is the same approach as of MS Excel, where user cannot set CategoryData before adding ASeries.
Thank You & Best Regards,
I understand. It would help if you would update your documentation for the setCategoryData method to explain this relationship. When you are building a chart programatically it isn't obvious that there is a relationship between setting the Category Data and having at least one Aseries entry defined. I did try this in Excel and it is obvious that you can't set the Category (X) axis labels until you have at least one series added but it's easy to see this since the UI doesn't even show the fields until you add the series.
Thanks, Steve