Formatting Charts

[skdzines]

I’m attempting to format a pie chart and am having trouble with some of the classes and methods. I’m using coldfusion in junction with the Java API. Below is the code I’m using to create the chart, and below the code are some of my questions.

<cfset wb = CreateObject(“java”, “com.aspose.cells.Workbook”).init()>

//get the Worksheets object
sheets = wb.getWorksheets();
//get the first sheet
sheet = sheets.getSheet(‘Sheet1’);
//get the cells object
cells = sheet.getCells();


//get the first cell and assign a value to it
title = cells.getCell(‘A1’);
title.setValue(“Demographic Data”);

titleStyle = title.getStyle();
font = titleStyle.getFont();
font.setName(“Arial”);
font.setSize(16);
title.setStyle(titleStyle);

startDescCol = “A”;
startDescRow = 3;

startValueCol = “C”;
startValueRow = 3;

for (i=1; i LTE ethnicity.recordcount; i=i+1){
ethn = JavaCast(‘String’,ethnicity.Num);
cells.getCell(startValueCol&startValueRow).setValue(ethn,true);
cells.getCell(startDescCol&startDescRow).setValue(ethnicity.Short_Desc);
startDescRow = startDescRow + 1;
startValueRow = startValueRow + 1;
}

endDescRow = startDescRow - 1;
endValueRow = startValueRow - 1;



//get chart objects
charts = sheet.getCharts();
chartRowPos = JavaCast(‘int’,startValueRow);

//Adding a chart to the worksheet
pieChart = charts.add(65600,chartRowPos,0,20,5);


//Accessing the instance of the newly added chart
chart = charts.getChart(pieChart);

chartTitle = chart.getTitle();
chartTitle.setText(“Ethnicity”);



//Adding NSeries (chart data source) to the chart ranging from “A1” cell to “B3”
series = chart.getNSeries();
series.add(“C3:”&startValueCol&endValueRow&"", true);

//Setting the data source for the category data of NSeries
series.setCategoryData(“A3:”&startDescCol&endDescRow&"");

plotArea = chart.getPlotArea();
area = plotArea.getArea();
area.setForegroundColor(Color.BLUE);


//save the file as a different one
wb.save(“E:\development\temp.xls”);



The first error I’m getting is saying that BLUE is undefined in the Element ‘Color’. What do I need to use to set the foreground color? Is there a numerical value I can pass and if so where do I find those color codes. I assume it is something similar to that of the charts.add() function where I have to pass the numerical representation of the chart type rather than the doing ‘ChartType.PIE’.


Second: How do I not display a border around the plotArea of the chart? I don’t see a function to set a boolean parameter to turn the border to OFF.

Third: Is there a way to display the point name and percentage next to the point on the chart rather than that information only being displayed when you hover over the point. Something similar to the image at this link

http://www.skdzines.com/images/pieChart.jpg

Thanks
Scott

[Warren]

Hi Scott,

1,You should use com.aspose.cells.Color Object to set the color.By the way,have the Cell.setValue method in <A href="</A> bean solved? Do you use another library?</FONT></FONT></P> <P><FONT face=Verdana>2, You can set the property of the border line Object to set the border of the plotArea.See following codes:</FONT></P> <P><FONT face=Verdana> PlotArea plotArea = chart.getPlotArea();<BR> Line line = plotArea.getBorder();<BR> line.setVisible(true);</FONT></P> <P><FONT face=Verdana>3,DataLabels dls = series.getDataLabels();<BR> dls.setPercentageShown(true);</FONT></P> <P><FONT face=Verdana></FONT> </P>

[skdzines]

Thanks for the reply Warren. I will give your suggestions a shot. Yes the cell.setValue has been corrected. I needed to us it in conjuction with the JavaCast function. Below is the code

ethn = JavaCast(‘String’,ethnicity.Num">);
cells.getCell(startValueCol&startValueRow).setValue(ethn,true);

Thanks again

Scott

[skdzines]

Warren,

The line.setVisible() function worked but I still want to get rid of the grey box around the pie chart. I’m using just the standard PIE chart.

Below is some of the code I am using:

chart = charts.getChart(ethPieChart);

plotArea = chart.getPlotArea();
line = plotArea.getBorder();
line.setVisible(false);
area = plotArea.getArea();
area.setForegroundColor(Color.WHITE);

chartTitle = chart.getTitle();
chartTitle.setText(“Ethnicity”);

It keeps failing at the setForegroundColor(). The coldfusion error I get is

Element WHITE is undefined in COLOR.

I am basing my code off of the following example from the Aspose Wiki site for setting Charts Appearance.

//Setting the foreground color of the plot area
 PlotArea plotArea = chart.getPlotArea();
 Area area = plotArea.getArea();
 area.setForegroundColor(Color.BLUE);


The following code is also failing.

//Adding NSeries (chart data source) to the chart ranging from “A1” cell to “B3”
series = chart.getNSeries();

dls = series.getDataLabels();
dls.setPercentageShown(true);

series.add(“C3:”&ethStartValueCol&ethEndValueRow&"", true);

//Setting the data source for the category data of NSeries
series.setCategoryData(“A3:”&ethStartDescCol&ethEndDescRow&"");


It is failing at the getDataLabels function. This is the error I receive:

The selected method getDataLabels was not found.

Thanks again for your help.

Scott

[Warren]

Hi Scott,

1, Element WHITE is undefined in COLOR :Please try the code :

area.setForegroundColor(com.aspose.cells.Color.BLUE);

2, Removes the grey box around the pie chart, you can set the plotArea is not visible. See the following codes:

Area area = plotArea.getArea();
area.setVisible(false);

3,You should use ASeries.getDataLabels method, please try the following codes:

ASeries aSeries = chart.getNSeries().get(0);
dls = aSeries.getDataLabels();
dls.setPercentageShown(true);

4, If you want to add a chart, you should use the constant member of ChartType not a numeric value. See the following code: charts.add(ChartType.PIE,7,3,20,9).

5, Which JRE version do you use? If you use JRE1.4, please use the Aspose.Cells for Java 1.4. And Please try the fix in https://forum.aspose.com/t/104295. If the error still occurs, could you post your sample project and some info about your environment? We will check it.

[skdzines]

Hi Warren,

Yes we are using JRun 4 JRE 1.4. Are you saying I should download the hotfix Version: 1.4.3.0? The link you provided doesn’t seem to work for me, it takes me to a page that says Access Denied.

Another question, is there any way in Aspose.Cells to generate an image of the chart that is being created so I can use that image to insert into a pdf file?

Thanks again for your help.

Scott

[skdzines]

Warren,

As stated in my last post, we are running Coldfusion MX and Macromedias JRun Server to manage our J2EE applications. We are running JRE 1.4. I have downloaded and installed the latest version from your site which is the Aspose.Cells For Java 1.7.5.0. I unzipped the contents and then referenced the Apose.Cells.jar file in JDK 1.4 from within my JRun console. I can access the classes fine, however, some things are not there such as the getDataLabels from the chart collection. This is what is available to me when I dump out the results for series = chart.getNSeries();

gives me:

object of com.aspose.cells.NSeries </th></tr> <tr> <td class="object"><br></td> <td> add (returns int)<br> add (returns int)<br> get (returns com.aspose.cells.ASeries)<br> size (returns int)<br> setCategoryData (returns void)<br> changeSeriesOrder (returns void)<br> getCategoryData (returns java.lang.String)<br> hashCode (returns int)<br> getClass (returns java.lang.Class)<br> wait (returns void)<br> wait (returns void)<br> wait (returns void)<br> equals (returns boolean)<br> notify (returns void)<br> notifyAll (returns void)<br> toString (returns java.lang.String)</td></tr></table><br>Also when attempting to use the constant member of the Chart Type instead of the numerical value it doesn't work. Am I missing a step? Below is some of the code I am using:<br><br>//get collection of charts<br> charts = sheet.getCharts();<br> ethChartRowPos = JavaCast('int',ethStartValueRow);<br> <br> //Adding a chart to the worksheet<br> ethPieChart = charts.add(pieChart,ethChartRowPos,0,20,5); // pieChart var is set above to 64<br><br> //Accessing the instance of the newly added chart<br> chart = charts.getChart(ethPieChart);<br><br>if I try <br>ethPieChart = charts.add(ChartType.PIE,ethChartRowPos,0,20,5); <br>instead, then my code fails<br><br>Thanks again for your help.<br><br>Scott<br>

[Warren]

Hi Scott,

1.It is not suppored to convert chart to image now.

2.You should reference all jars in JDK 1.4 of the Aspose.Cells for Java version.

3.NSeries Object is the ASeries colletion.The getDataLabels is the method of ASeries.You shoud get ASeries Object from NSeries first,then get Datalabels from the ASeries.See following codes:

NSeries nSeries = chart.getNSeries();
ASeries aSeries = nSeries.get(0);
DataLabels dls = aSeries.getDataLabels();

4.Which JRun verison or updater do you use?

[Warren]

Hi Scott,

We download JRun 4 SP1a for windows.It works fine with the attached hello.jsp in our test environment.We use Sun JDK1.4.2.

Please check the libraries in your ClassPath.It seems there is a library which has the same class name with Aspose.Cells for Java library.Maybe you should point out package of the calling classes.Such as com.aspose.cells.ChartType,com.aspose.cells.Worksheet,etc.

[skdzines]

Warren, thanks again.

It’s getting there slowly but surely. I’m getting my datalabels now and postitoned on the chart where I want them. I don’t see a method for setting the leader line to true anywhere in the DataLabels object or NSeries object. Is this supported in aspose.cells? Thanks again for your help.

Scott

[skdzines]

Another question, how do I go about retrieving data from another sheet to use as my NSeries for a chart on a different sheet?

Everything works except the DataSheet does not communicate with the ChartSheet. Below is my code.

<cfset wb = CreateObject(“java”, “com.aspose.cells.Workbook”).init()>

//get the Worksheets object
sheets = wb.getWorksheets();
//remove the first sheet
removeSheet = sheets.removeSheet(‘Sheet1’);

//create new sheets
chartSheet = sheets.addSheet(‘ChartSheet’);
dataSheet = sheets.addSheet(‘DataSheet’);

myChartSheet = sheets.getSheet(‘ChartSheet’);
myDataSheet = sheets.getSheet(‘DataSheet’);

//get the cells object
chartCells = myChartSheet.getCells();
dataCells = myDataSheet.getCells();

//set the type of charts used
pieChart = 64;

//get the first cell and assign a value to it
title = chartCells.getCell(‘A1’);
title.setValue(“Demographic Data”);
chartCells.setRowHeight(0, 30);

titleStyle = title.getStyle();
font = titleStyle.getFont();
font.setName(“Arial”);
font.setSize(16);
title.setStyle(titleStyle);


/**** This section creates the pie chart for Ethnicity ****/
ethStartDescCol = “A”;
ethStartDescRow = 3;

ethStartValueCol = “C”;
ethStartValueRow = 3;

for (i=1; i LTE ethnicity.recordcount; i=i+1){
ethn = JavaCast(‘String’,ethnicity.Num);
dataCells.getCell(ethStartValueCol&ethStartValueRow).setValue(ethn,true);
dataCells.getCell(ethStartDescCol&ethStartDescRow).setValue(ethnicity.Short_Desc);
ethStartDescRow = ethStartDescRow + 1;
ethStartValueRow = ethStartValueRow + 1;
}

ethEndDescRow = ethStartDescRow - 1;
ethEndValueRow = ethStartValueRow - 1;



//get collection of charts
charts = myChartSheet.getCharts();
//ethChartRowPos = JavaCast(‘int’,ethStartValueRow);

//Adding a chart to the worksheet
ethPieChart = charts.add(pieChart,2,0,15,5);


//Accessing the instance of the newly added chart
chart = charts.getChart(ethPieChart);
chartArea = chart.getChartArea();

plotArea = chart.getPlotArea();
line = plotArea.getBorder();
line.setVisible(false);
area = plotArea.getArea();
area.setVisible(false);

chartTitle = chart.getTitle();
font = chartTitle.getFont();
font.setName(“Arial”);
font.setSize(12);
font.setBold(true);
font.setUnderline(true);
chartTitle.setText(“Ethnicity”);



//Adding NSeries (chart data source) to the chart from “DataSheet”
nSeries = chart.getNSeries();

nSeries.add(“C3:”&ethStartValueCol&ethEndValueRow&"", true);

//Setting the data source for the category data of NSeries
nSeries.setCategoryData(“A3:”&ethStartDescCol&ethEndDescRow&"");

aSeries = nSeries.get(0);
dls = aSeries.getDataLabels();
dls.setPercentageShown(true);
dls.setValueShown(true);
dls.setLabelPosition(9); //BEST_FIT

//save the file as a different one
wb.save(“E:\development\temp.xls”);


I also want to know how I can position the title of the chart either to the left or right over the legend.

Thanks again.
Scott

[Warren]

Hi Scott,

Please try this fix.In this fix,we rename the name of the two jars ,please change your reference in the project.

1,Please call ASeries.setHasLeaderLine to set whether the series has leader lines.

2,If you want to retrieve data from another sheet , the data source's format should be "DataSheet!A1:B3".Your codes should chang to: nSeries.add("DataSheet!C3:"&ethStartValueCol&ethEndValueRow&"", true);

3,You can call Title.setX and setY method to drag Title to anywhere in the chart area.If you want to change the legend position,you can call Lengend.setPosition method.

[skdzines]

Warren,

I’ve replaced the 3 jar files you sent and referenced them in JRun 4. They are referenced as

C:\JRun4\servers\internet_staging\cfusion.ear\cfusion.war\WEB-INF\cfusion\lib\Aspose.Cells.jar
C:\JRun4\servers\internet_staging\cfusion.ear\cfusion.war\WEB-INF\cfusion\lib\backport-util-concurrent.jar
C:\JRun4\servers\internet_staging\cfusion.ear\cfusion.war\WEB-INF\cfusion\lib\dom4j-1.6.1.jar
C:\JRun4\servers\internet_staging\cfusion.ear\cfusion.war\WEB-INF\cfusion\lib\retrotranslator-runtime.jar

However I still don’t get the setHasLeaderLine method in the ASeries object. It says the method could not be found.

Here is my sample code:

//Adding NSeries (chart data source) to the chart from “DataSheet”
nSeries = chart.getNSeries();

nSeries.add(“C3:”&ethStartValueCol&ethEndValueRow&"", true);

//Setting the data source for the category data of NSeries
nSeries.setCategoryData(“A3:”&ethStartDescCol&ethEndDescRow&"");

aSeries = nSeries.get(0);
dls = aSeries.getDataLabels();
dls.setPercentageShown(true);
dls.setValueShown(true);
dls.setLabelPosition(9); //BEST_FIT
dls.setHasLeaderLine(true);

and this is the error in Coldfusion

Error Occurred While Processing Request

        <font>















	
	
    
	



<table cellpadding="0" cellspacing="0">
<tr>
    <td id="tableProps2" align="left">
        <h1 id="textSection1">
        The selected method setHasLeaderLine was not found.
        </h1></td></tr></table>I'm also still not seeing the setX and setY methods for the chart title. Below are the available methods that I have.<br><br></font><table class="cfdump_object"><tr><th class="object">object of com.aspose.cells.Title<br>
		
		</th></tr>
		

		
			
			
				
					<tr>
						<td class="object">Methods</td>
						<td>
				
				
					
					setText (returns void)<br>
				
					
					getText (returns java.lang.String)<br>
				
					
					getFont (returns com.aspose.cells.Font)<br>
				
					
					setFont (returns void)<br>
				
					
					getRotation (returns int)<br>
				
					
					setRotation (returns void)<br>
				
					
					getTextHorizontalAlignment (returns int)<br>
				
					
					getTextVerticalAlignment (returns int)<br>
				
					
					setTextHorizontalAlignment (returns void)<br>
				
					
					setTextVerticalAlignment (returns void)<br>
				
					
					getBorder (returns com.aspose.cells.Line)<br>
				
					
					getArea (returns com.aspose.cells.Area)<br>
				
					
					hashCode (returns int)<br>
				
					
					getClass (returns java.lang.Class)<br>
				
					
					wait (returns void)<br>
				
					
					wait (returns void)<br>
				
					
					wait (returns void)<br>
				
					
					equals (returns boolean)<br>
				
					
					notify (returns void)<br>
				
					
					notifyAll (returns void)<br>
				
					
					toString (returns java.lang.String)</td></tr></table><br><font><br>here is my code:<br><br>chartTitle = chart.getTitle();<br>     font = chartTitle.getFont();<br>     font.setName("Arial");<br>     font.setSize(12);<br>     font.setBold(true);<br>     font.setUnderline(true);<br>     chartTitle.setText("Ethnicity");<br>     chartTitle.setX(5);<br>     chartTitle.setY(5);<br><br><br>Thanks again for your help.<br><br>Scott<br></font><br>

[Warren]

Hi Scott,

Please try this fix.We have embedded the classes of the retrotranslator and backport-util-concurrent.So you only need to references to dom4j-1.6.1.jar and Aspose.Cells.jar.

[Warren]

Hi Scott,

It is caused by CMFL the the selected method could not be found.Could you use a Jsp or Servlet or a java object to call writing and openning the file.

About the method could not be found,I think that you shoud change some codes.The following is my opinion:

1 Cell.setValue method could not be found : please try Cell.setValue(JavaCast("int",ethnicity.Num )) to set int value.Because coldFusion does not use explicit types for variables,the first value type will be the variable type.

2 Color.BLUE could not be found : please try add <cfobject type="Java" class="Class" name="myColor"> code first,then call setColor(myColor.BLUE);

[Warren]

Hi Scott,

We will download ColdFusion tomorrow to try.

[skdzines]

Warren,

I really appreciate you taking the time and looking into these issues for me. It is slowly getting there. I can now see the method setHasLeaderLine being available in the aSeries object now which I have listed below:

object of com.aspose.cells.ASeries </th></tr> <tr> <td class="object"><font face="Arial" size="1">Methods</font></td> <td><font face="Arial" size="1"> getName (returns java.lang.String)<br> setName (returns void)<br> getType (returns int)<br> setType (returns void)<br> setValues (returns void)<br> getValues (returns java.lang.String)<br> getBorder (returns com.aspose.cells.Line)<br> getArea (returns com.aspose.cells.Area)<br> hasLeaderLine (returns boolean)<br> getDataLabels (returns com.aspose.cells.DataLabels)<br> setPlotOnSecondAxis (returns void)<br> setMarkerStyle (returns void)<br> getBubbleSizes (returns java.lang.String)<br> getChartPoints (returns com.aspose.cells.ChartPoints)<br> getMarkerBackgroundColor (returns com.aspose.cells.Color)<br> getMarkerForegroundColor (returns com.aspose.cells.Color)<br> getMarkerSize (returns int)<br> getMarkerStyle (returns short)<br> getSeriesNumber (returns int)<br> getTrendlines (returns com.aspose.cells.Trendlines)<br> getXErroBars (returns com.aspose.cells.ErrorBars)<br> getXValues (returns java.lang.String)<br> getYErroBars (returns com.aspose.cells.ErrorBars)<br> isColorVaried (returns boolean)<br> isPlotOnSecondAxis (returns boolean)<br> isSmooth (returns boolean)<br> setBubbleSizes (returns void)<br> setColorVaried (returns void)<br> setHasLeaderLine (returns void)<br> setMarkerBackgroundColor (returns void)<br> setMarkerForegroundColor (returns void)<br> setMarkerSize (returns void)<br> setSeriesNumber (returns void)<br> setSmooth (returns void)<br> setXValues (returns void)<br> hashCode (returns int)<br> getClass (returns java.lang.Class)<br> wait (returns void)<br> wait (returns void)<br> wait (returns void)<br> equals (returns boolean)<br> notify (returns void)<br> notifyAll (returns void)<br> toString (returns java.lang.String)</font></td></tr></table><br><br>However the following code executes fine, but the leaderLines are still not showing up when opening the excel file. Below is the code for setting them.<br><br>//Adding NSeries (chart data source) to the chart from "DataSheet"<br> nSeries = chart.getNSeries();<br> <br> nSeries.add("DataSheet!C3:"&ethStartValueCol&ethEndValueRow&"", true);<br> <br> //Setting the data source for the category data of NSeries<br> nSeries.setCategoryData("DataSheet!A3:"&ethStartDescCol&ethEndDescRow&""); <br> <br> aSeries = nSeries.get(0);<br> aSeries.setHasLeaderLine(true);<br> dls = aSeries.getDataLabels(); <br> dls.setPercentageShown(true);<br> dls.setValueShown(true);<br> dls.setLabelPosition(9); //BEST_FIT<br><br>As for the COLOR and CHARTTYPE issue I was having, that has been fixed by doing the following:<br><br><cfset myChart = CreateObject("java", "com.aspose.cells.ChartType")><br><cfset myColor = CreateObject("java", "com.aspose.cells.Color")><br><br>and then referencing myChart.PIE<br><br>Thanks again for your help.<br><br>It's getting there.<br><br>Scott<br>

[Warren]

Hi Scott,

Please try this fix.

After working with ColdFusion , we find why the cell.setValue(int ) could not be found.It seems the ColdFusion could not handle the cell.setValue(Object).If you want to use setValue(int) method , you should build another class ,see following code:

public class MyCell {
public MyCell() {
}

private Cell cell;
public setCell(Cell cell)
{
this.cell = cell;
}
public void setIntValue(int intValue)
{
cell.setValue(intValue);
}
public void setStringValue(String stringValue)
{
cell.setValue(stringValue);
}
public void setDoubleValue(double doubleValue)
{
cell.setValue(doubleValue);
}
......
}

[skdzines]

Thanks Warren,

That last fix worked for the leader lines. Everything else seems to be working so far as well. At least for the pie charts, not sure yet about other charts though cause I haven’t gotten to them yet. I’ll give the custom class a shot as well, although by doing

ethn = JavaCast(‘String’,ethnicity.Num);
dataCells.getCell(ethStartValueCol&ethStartValueRow).setValue(ethn,true);

Seems to work as well.

Thanks again for all of you help.

Scott