asked on

Creating charts with jxls

I have installed and set up jxls.
I am not sure how I can run the example shown here:
I have attached the code I am using.

Please let me know what "templateFileName" in the code is, and how I can create one.

Thanks!

for_yan

I don't think I can see the code

dshrenik

ASKER

Sorry, here it is.

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import net.sf.jxls.exception.ParsePropertyException;
import net.sf.jxls.transformer.XLSTransformer;

public class TestJXL {
	
	public TestJXL() throws ParsePropertyException, InvalidFormatException, IOException{
		ArrayList<Employee> staff = new ArrayList<Employee>();
		staff.add(new Employee("Derek", 35, 3000, 0.30));
		staff.add(new Employee("Elsa", 28, 1500, 0.15));
		staff.add(new Employee("Oleg", 32, 2300, 0.25));
		staff.add(new Employee("Neil", 34, 2500, 0.00));
		staff.add(new Employee("Maria", 34, 1700, 0.15));
		staff.add(new Employee("John", 35, 2800, 0.20));
		staff.add(new Employee("Leonid", 29, 1700, 0.20));
		HashMap<String, ArrayList<Employee>> beans = new HashMap<String, ArrayList<Employee>>();
		beans.put("employee", staff);
		XLSTransformer transformer = new XLSTransformer();
		transformer.markAsFixedSizeCollection("employee");
		String templateFileName = "";
		String destFileName = "";
		transformer.transformXLS(templateFileName, beans, destFileName);
	}
	
	public static void main(String [] args) throws ParsePropertyException, InvalidFormatException, IOException{
		new TestJXL();
	}
}

Open in new window

ASKER CERTIFIED SOLUTION

for_yan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

for_yan

It gives example of template file for their casee
in

http://rgagnon.com/javadetails/java-0516.html

in JXLS section

Employees               
Name                Age             Payment             Bonus
${employee.name}    ${employee.age} ${employee.payment} ${employee.bonus}
                                    $[SUM(@employee.payment@)]

Open in new window

dshrenik

ASKER

If possible, could you attach a sample xls template? How do I select the chart type through Java?

for_yan

My guess is that you doeble click this .csv file - it willopen in Excel,
then save it as Excel - and try to run tyher example giving this file name.xls file as a template
jxls-temp.csv

dshrenik

ASKER

I'm getting these errors:

May 19, 2011 3:10:18 PM net.sf.jxls.formula.FormulaPart extractDefaultValue
SEVERE: Can't parse default value constant for SUM(@employee.payment@) formula part. Integer expected after '@' symbol
Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/commons/jexl2/JexlContext
	at net.sf.jxls.parser.CellParser.parseCellExpression(CellParser.java:138)
	at net.sf.jxls.parser.CellParser.parseCellValue(CellParser.java:202)
	at net.sf.jxls.parser.CellParser.parseCell(CellParser.java:55)
	at net.sf.jxls.transformer.SheetTransformer.parseCells(SheetTransformer.java:203)
	at net.sf.jxls.transformer.SheetTransformer.parseRow(SheetTransformer.java:273)
	at net.sf.jxls.transformer.SheetTransformer.transformSheet(SheetTransformer.java:80)
	at net.sf.jxls.transformer.XLSTransformer.transformWorkbook(XLSTransformer.java:243)
	at net.sf.jxls.transformer.XLSTransformer.transformXLS(XLSTransformer.java:218)
	at net.sf.jxls.transformer.XLSTransformer.transformXLS(XLSTransformer.java:198)
	at TestJXL.<init>(TestJXL.java:27)
	at TestJXL.main(TestJXL.java:31)
Caused by: java.lang.ClassNotFoundException: org.apache.commons.jexl2.JexlContext
	at java.net.URLClassLoader$1.run(Unknown Source)
	at java.security.AccessController.doPrivileged(Native Method)
	at java.net.URLClassLoader.findClass(Unknown Source)
	at java.lang.ClassLoader.loadClass(Unknown Source)
	at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
	at java.lang.ClassLoader.loadClass(Unknown Source)
	... 11 more

Open in new window

SOLUTION

for_yan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mick Barry

you need jexl

http://commons.apache.org/jexl/

dshrenik

ASKER

I do have jexl

Mick Barry

make sure you have added the jar to your classpath

dshrenik

ASKER

I got the latest version of jexl.
I now get these errors

May 19, 2011 3:25:14 PM net.sf.jxls.formula.FormulaPart extractDefaultValue
SEVERE: Can't parse default value constant for SUM(@employee.payment@) formula part. Integer expected after '@' symbol
Exception in thread "main" java.lang.NullPointerException
	at net.sf.jxls.util.Util.duplicateRowCollectionProperty(Util.java:239)
	at net.sf.jxls.transformer.CollectionRowTransformer.processRowCollections(CollectionRowTransformer.java:116)
	at net.sf.jxls.transformer.CollectionRowTransformer.transform(CollectionRowTransformer.java:66)
	at net.sf.jxls.transformer.SheetTransformer.transformSheet(SheetTransformer.java:88)
	at net.sf.jxls.transformer.XLSTransformer.transformWorkbook(XLSTransformer.java:243)
	at net.sf.jxls.transformer.XLSTransformer.transformXLS(XLSTransformer.java:218)
	at net.sf.jxls.transformer.XLSTransformer.transformXLS(XLSTransformer.java:198)
	at TestJXL.<init>(TestJXL.java:27)
	at TestJXL.main(TestJXL.java:31)

Open in new window

dshrenik

ASKER

I have added it in Build path -> Add external archives

for_yan

maybe in that place in template in cell C4 you can chnage to $[SUM(C3)] like in some templates in the src.jar

dshrenik

ASKER

I get these errors now:

Exception in thread "main" java.lang.NullPointerException
	at net.sf.jxls.util.Util.duplicateRowCollectionProperty(Util.java:239)
	at net.sf.jxls.transformer.CollectionRowTransformer.processRowCollections(CollectionRowTransformer.java:116)
	at net.sf.jxls.transformer.CollectionRowTransformer.transform(CollectionRowTransformer.java:66)
	at net.sf.jxls.transformer.SheetTransformer.transformSheet(SheetTransformer.java:88)
	at net.sf.jxls.transformer.XLSTransformer.transformWorkbook(XLSTransformer.java:243)
	at net.sf.jxls.transformer.XLSTransformer.transformXLS(XLSTransformer.java:218)
	at net.sf.jxls.transformer.XLSTransformer.transformXLS(XLSTransformer.java:198)
	at TestJXL.<init>(TestJXL.java:27)
	at TestJXL.main(TestJXL.java:31)

Open in new window

Mick Barry

looks like a problem with your data

dshrenik

ASKER

Here's my java code

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import net.sf.jxls.exception.ParsePropertyException;
import net.sf.jxls.transformer.XLSTransformer;

public class TestJXL {
	
	public TestJXL() throws ParsePropertyException, InvalidFormatException, IOException{
		ArrayList<Employee> staff = new ArrayList<Employee>();
		staff.add(new Employee("Derek", 35, 3000, 0.30));
		staff.add(new Employee("Elsa", 28, 1500, 0.15));
		staff.add(new Employee("Oleg", 32, 2300, 0.25));
		staff.add(new Employee("Neil", 34, 2500, 0.00));
		staff.add(new Employee("Maria", 34, 1700, 0.15));
		staff.add(new Employee("John", 35, 2800, 0.20));
		staff.add(new Employee("Leonid", 29, 1700, 0.20));
		HashMap<String, ArrayList<Employee>> beans = new HashMap<String, ArrayList<Employee>>();
		beans.put("employee", staff);
		XLSTransformer transformer = new XLSTransformer();
		transformer.markAsFixedSizeCollection("employee");
		String templateFileName = "C:/Users/dshrenik/Brahms/Charts_BRAHMS/jxlsTemplate.xls";
		String destFileName = "C:/Users/dshrenik/Brahms/Charts_BRAHMS/jxlsOutput.xls";
		transformer.transformXLS(templateFileName, beans, destFileName);
	}
	
	public static void main(String [] args) throws ParsePropertyException, InvalidFormatException, IOException{
		new TestJXL();
	}
}

Open in new window

for_yan

rather look at this src.jar file - it has examples of codes and templates - these were obvuiously tested
as oopposed to that other one from rgagnon

for_yan

and waht is your template for this code?

Anyway, I think it is simpler for the beginning to look at what they already ran from that jar

dshrenik

ASKER

The template that you provided. I think they match.
Anyways, I will try your examples from the jar.

for_yan

I guees they should have all these filed names correctly.

But I think it would take less time to try siomomething
which they distribute themsleves as examples

dshrenik

ASKER

If possible, please answer this question:
https://www.experts-exchange.com/questions/27050141/Create-charts-using-Java-code.html

Thanks!