Excel image quality - awful

[DeanoUK]

When printing using the JExcelApiExporter to format to an XLS spreadsheet the embedded JFreeChart, which is added as an image into the report, is of a vastly inferior quality to that of the PDF output.

 

Why is this and how can I get it up to the quality of the PDF output?

 

I've noticed via a search of this topic in the forums that it gets asked a lot, but hasn't been answered - and this is an essential part of what I need to do.

 

Any help would be appreciated.

[rckrll106]

Any update on this?

Images and charts are pretty bad when rendered. I find it hard to believe this hasn't been discussed before. I'm not sure if opening the .xls file in Open Office has the same issue but this is definitely a problem with Microsoft Excel. To reproduce, just create a jrxml with an image and a chart and export to Excel.

As far as I can tell it is because we are reducing the image quality by using the BufferedImage. I was able to hack a way around this however it breaks the "Scale Image" options. The hack always fills the frame yet the image quality is what you would expect. Let me say I don't find this an optimal solution yet its much better than fuzzy images (which is unacceptable). Plus you can always resize the image frame. Again, not an optimal solution but maybe this could be a start to a better solution?!

I made the changes to the ImageChartRendererFactory and not to the DrawChartRenderer because it broke the pdf export.

As a result, if you use the JRXlsExporter and render an Image or Chart (using the image renderer) everything renders with much better resolution.

Code:

/* JRXlsExporter.java */				//Get the image bytes from the renderer. 				byte[] imageBytes = renderer.getImageData();								//Images distort using BufferedImage however using the byte[] we can render images with				//the best resolution. Note - images will always stretch to the size of the image box.				//Until there is a better solution, manage this by resizing the image box to the exact				//size of the image.				int imgIndex = 0;				if(imageBytes != null && element.getScaleImage() == JRImage.SCALE_IMAGE_RETAIN_SHAPE){					imgIndex = workbook.addPicture(imageBytes, HSSFWorkbook.PICTURE_TYPE_PNG);				}else{									imgIndex = workbook.addPicture(JRImageLoader.loadImageDataFromAWTImage(bi, JRRenderable.IMAGE_TYPE_PNG), HSSFWorkbook.PICTURE_TYPE_PNG);				}								patriarch.createPicture(anchor, imgIndex);/* ImageChartRendererFactory.java */			//chart.draw((Graphics2D)bi.getGraphics(), rectangle);						//Scale the image for better resolution			bi = ChartUtil.writeScaledChart(chart, (int)rectangle.getWidth(), (int)rectangle.getHeight(), 4, 4);/* ChartUtil.java added a method to scale a chart for better resolution*/	public static BufferedImage writeScaledChart(JFreeChart chart, int width, int height, int widthScaleFactor, int heightScaleFactor)	{	    double desiredWidth = width * widthScaleFactor;	    double desiredHeight = height * heightScaleFactor;	    double defaultWidth = width;	    double defaultHeight = height;	    boolean scale = false;	    if(widthScaleFactor != 1 || heightScaleFactor != 1)	        scale = true;	    double scaleX = desiredWidth / defaultWidth;	    double scaleY = desiredHeight / defaultHeight;	    BufferedImage image = new BufferedImage((int)desiredWidth, (int)desiredHeight, 2);	    Graphics2D g2 = image.createGraphics();	    if(scale)	    {	        AffineTransform saved = g2.getTransform();	        g2.transform(AffineTransform.getScaleInstance(scaleX, scaleY));	        chart.draw(g2, new java.awt.geom.Rectangle2D.Double(0.0D, 0.0D, defaultWidth, defaultHeight), null, null);	        g2.setTransform(saved);	        g2.dispose();	    } else	    {	        chart.draw(g2, new java.awt.geom.Rectangle2D.Double(0.0D, 0.0D, defaultWidth, defaultHeight), null, null);	    }	    	    return image;	    	}	

[teodord]

Hi,

First, let me explain why the image quality is bad in XLS and RTF and then maybe you can suggest us a solution.

Charts (including those created with JFreeChart) are Scalable Vector Graphics (SVG), and in PDF, we are able to render them as SVG, because the PDF specifications and the iText library we use allow us to do that.

This means that no matter how deep you zoom in, the charts will still be rendered as a series of lines and shapes and the quality will be the same.

However, the XLS and RTF specifications, or the library we use to creat these formats, do not allow embedding SVG in the documents. The only way to make those charts appear is to transform them into pictures (PNG).

When you have a picture, zooming into it only makes those pixels bigger and bigger, and for you that means degrading chart quality.

 

The solution that has been proposed in the past and we are considering it now is to introduce a parameter somewhere that would tell what resolution to use when creating the pictures. By default, we use 72dpi, but others who would want better picture quality in XLS and RTF could set it to 96dpi or even 300dpi for that matter.

The trouble with this solution is that the problem would still be there, but visible only at higher zoom rations. Also, the generated files will be considered significantly bigger, because of the much bigger pictures that we embed into them.

 

Please suggest a solution for the problem, if you have one.

 

Thank you,

Teodor

 

[dbdbdb]

Hi Teodor,

It would be great if we can introduce a parameter to change the default resolution!
In my opinion, this is the best approach for this issue. Sometimes we don't care about the size of the file, because it runs locally.

I'll download the sources, and if I had success, I'll post the patch. ;)

Thanks,
Daniel

Post Edited by dbdbdb at 05/27/2009 19:29

[csagar]

Hi,

I am facing the same problem. The images and values displayed as the labels on bars are very distorted .I need to export the chart in HTML format. I have tried changing the property from 'px' to 'pt' .But it didn't help. I get the same quality of image in both the cases. Also tried by changing the render type from Default to SVG . But all efforts in vain.

if you have any success in above mentioned solution please let me know. I need to implement as soon as possible.

 

 

Thanks in advance.

[nsushkin]

See if this helps:

http://jasperforge.org/plugins/espforum/view.php?group_id=102&forumid=103&topicid=57893

 

Nicholas Sushkin

 

[dwfletcher]

Teodor:

I'm also having problems with image resolution. Specifically, I am converting our application to use Jasper Reports as the report generator instead of a commercial engine. Because I'm doing a conversion, lots of my customers already have reports with charts that have sufficient resolution when displayed (using HTML or RTF). However, the current commercial engine is smart enough to know that Windows typically uses 96 DPI and generates its PNG charts based on that resolution. Now with Jasper, all of my charts are generated at 72 DPI and "zoomed" when displayed in HTML or RTF, causing poor quality to the point of not being able to read axis labels. My customers will not be satisfied...

I'm looking for a simple/straight forward way of changing the resolution at which my charts are generated. I could override the JRHtmlExporter class, but I would have to override the exportReport() method in it's entirety which is not a good idea since it exports everything else as well, not just charts.

Any help?

Duane Fletcher

[lucianc]

I suggest the following workaround (until we sort out how we'll implement this in JR):

You can write a chart renderer factory which produces rasterized images adjusted for a certain DPI.  JR already includes a chart renderer factory that produces rasterized images, so using that as a starting point we can write something like the class below.  The class uses a property called net.sf.jasperreports.chart.dpi which provides the desired chart image resolution.

To use this chart renderer factory, the following properties need to be set in jasperreports.properties:

net.sf.jasperreports.chart.render.type=image_dpi
net.sf.jasperreports.chart.renderer.factory.image_dpi=jr.ImageDPIChartRendererFactory
net.sf.jasperreports.chart.dpi=96
 

Regards,

Lucian

Code:

package jr;import java.awt.Graphics2D;import java.awt.geom.Rectangle2D;import java.awt.image.BufferedImage;import java.util.List;import net.sf.jasperreports.charts.util.ChartHyperlinkProvider;import net.sf.jasperreports.charts.util.ChartRendererFactory;import net.sf.jasperreports.charts.util.ChartUtil;import net.sf.jasperreports.engine.JRException;import net.sf.jasperreports.engine.JRRenderable;import net.sf.jasperreports.engine.JRRuntimeException;import net.sf.jasperreports.engine.util.JRImageLoader;import net.sf.jasperreports.engine.util.JRProperties;import net.sf.jasperreports.renderers.JRSimpleImageMapRenderer;import org.jfree.chart.JFreeChart;public class ImageDPIChartRendererFactory implements ChartRendererFactory{		public JRRenderable getRenderer(		JFreeChart chart, 		ChartHyperlinkProvider chartHyperlinkProvider,		Rectangle2D rectangle		)	{		int imageDPI = JRProperties.getIntegerProperty("net.sf.jasperreports.chart.dpi", 72);		double scale = ((double) imageDPI)/72d;				BufferedImage bi = 			new BufferedImage(				(int) (scale * rectangle.getWidth()),				(int) (scale * rectangle.getHeight()),				BufferedImage.TYPE_INT_ARGB				);		List areaHyperlinks = null;		Graphics2D graphics = (Graphics2D) bi.getGraphics();		graphics.scale(scale, scale);		if (chartHyperlinkProvider != null && chartHyperlinkProvider.hasHyperlinks())		{			areaHyperlinks = ChartUtil.getImageAreaHyperlinks(chart, chartHyperlinkProvider, graphics, rectangle);		}		else		{			chart.draw(graphics, rectangle);		}		try		{			return new JRSimpleImageMapRenderer(JRImageLoader.loadImageDataFromAWTImage(bi, JRRenderable.IMAGE_TYPE_PNG), areaHyperlinks);		}		catch (JRException e)		{			throw new JRRuntimeException(e);		}	}}

[dwfletcher]

Lucian - Thanks. I will review your solution and give it a try. I really appreciate the suggestion.

 

Duane Fletcher

[dwfletcher]

Awesome! This works perfectly for me!

 

Thanks again!

Duane Fletcher

[juhap]

I guess the ultimate solution would be to put the images to Excel in EMF format. Enhanced Metafile is the Windows  way of displaying vector graphics, so it should provide best quality.

 

However the EMF is not that well supported in Java world, there does not seem to be that many (open source) libraries for it and for example Batik does not support it.

 

One thing I was able to find is http://java.freehep.org/vectorgraphics/index.html which allows one to save images drawn on Graphics2D in different formats - including EMF. I don't have experience with the library, so I don't know the quality of the output. But if somebody wants to experiment, maybe this could be a start. FreeHEP vectorgraphics library is licensed with LGPL so no problems there.

 

br, Juha

Post Edited by juhap at 09/03/2009 12:08

[juhap]

I was able to change the JExcel exporter to use the FreeHEP library. The other Excel exporter I could not get working, since POI seems to be having some issues with EMF exports(1).  After my change the image quality is very good, at least with bar graphs. You can zoom in as much as you want and it still looks sharp.

 

I'm attaching the edited JExcelApiExporter.java from 3.6.0 that does the trick. In addition to this you need to download the FreeHEP vector graphics libraries from ftp://ftp.slac.stanford.edu/software/freehep/VectorGraphics/v2.1.1/ (note: at least I had some issues connecting to the ftp server, try with some ftp-client if your browser does not work)

 

I would appreciate if somebody from the Jasper team could take a look at this and see if this could be included in the standard distribution. 

(1) http://markmail.org/message/baeab4dkmswlkixf

 

[zawmn83]

Hi lucian

I'm new to jasper report and java.

Could you tell me more detail steps to apply this wrokaround?

Do I need to creat a java file ImageDPIChartRendererFactory and complie?

Where do I need to put output ImageDPIChartRendererFactory.class ?

Do I need change jasper report properties from iReport ?

 

lucian
Wrote:

I suggest the following workaround (until we sort out how we'll implement this in JR): You can write a chart renderer factory which produces rasterized images adjusted for a certain DPI.  JR already includes a chart renderer factory that produces rasterized images, so using that as a starting point we can write something like the class below.  The class uses a property called net.sf.jasperreports.chart.dpi which provides the desired chart image resolution. To use this chart renderer factory, the following properties need to be set in jasperreports.properties: net.sf.jasperreports.chart.render.type=image_dpi net.sf.jasperreports.chart.renderer.factory.image_dpi=jr.ImageDPIChartRendererFactory net.sf.jasperreports.chart.dpi=96   Regards, Lucian Code:

[zawmn83]

When I set report properties net.sf.jasperreports.chart.render.type=image_dpi, the following error occur.

I'm using jasper report 3.5.3. 

Fatal error: Uncaught [[o:Exception]:"java.lang.Exception: Invoke failed: [[c:JasperFillManager]]->fillReport((o:JasperReport)[o:JasperReport], (i:Map)[o:HashMap], (i:Connection)[o:JDBC4Connection]). Cause: net.sf.jasperreports.engine.JRRuntimeException: No chart renderer factory specifyed for 'image_dpi' render type.

[teodord]

Hi, Juha

 

I have created the following patch entry to keep track of your contribution.

http://jasperforge.org/projects/jasperreports/tracker/view.php?id=4315

Feel free to take control of that issue and update it as you see fit.

We'll consider it for a future release.

 

Thank you,
Teodor

 

[lucianc]

zawmn83
Wrote:

I'm new to jasper report and java. Could you tell me more detail steps to apply this wrokaround? Do I need to creat a java file ImageDPIChartRendererFactory and complie? Where do I need to put output ImageDPIChartRendererFactory.class ? Do I need change jasper report properties from iReport ?

Yes, you need to compile the Java class and place the compiled class on your application's classpath.

Then you'll have to set the three JR properties that I listed.  If you're using iReport 3.6, you can do that via Tools/Options/JasperReports Properties.

Regards,

Lucian

[beigo88]

Where do you plug this class?

There is no where in iReport where I can specify a render class for my report...

Help :(

[beigo88]

Hi Lucian

I tried doing what you suggested and I am getting the following error:

 

"net.sf.jasperreports.engine.JRRuntimeException: No chart renderer factory specifyed for 'image_dpi' render type.

at net.sf.jasperreports.charts.util.ChartUtil.getChartRendererFactory(ChartUtil.java:178)...."

 

I am using iReport 3.6.1 and set the properties as you mentioned (going to Tools?option etc.). I even went as far including the property in the xml of the report like so:

<property name="net.sf.jasperreports.chart.renderer.factory.image_dpi" value="myPackageName.ImageDPIChartRendererFactory"/>

<property name="net.sf.jasperreports.chart.render.type" value="image_dpi"/>

<property name="net.sf.jasperreports.chart.dpi" value="96"/>

 

..but get the same error described above.

 

The class file is in a classes directory for my web apps and I also specify that folder using the same technique for setting the properties via iReport.

 

Any suggestions??

Thanks.

[lucianc]

The net.sf.jasperreports.chart.renderer.factory.image_dpi property needs to be set globally (in jasperreports.properties in a standard JR environment), not at report level.  Afaik iReport allows you to configure global JR properties, look in the Options dialog for that.

Regards,

Lucian

[teodord]

Hi,

A new JR configuration property named "net.sf.jasperreports.image.dpi" is now available globally and can be used to specify the DPI resolution for images, when rasterized during report export.

By default, the property has the value 72.

This has been committed to SVN and will be part of the next release.

Thank you,
Teodo

 

[kcd]

Whoops didn't see the second and third page of this topic :) It is already answered

--

In iReport Options > JasperReport Properties edit

net.sf.jasperreports.image.dpi    300

http://jasperforge.org/projects/jasperreports/tracker/view.php?id=3411

Post Edited by kcd at 12/07/2011 20:34