nestoru

First of all look into http://community.jaspersoft.com/jasperreports-library/issues/2363#comment-710646 as apparently Jasper Reports provided the solution I suggested years ago. If that does not work then I assume XLS macros do still load correctly (for example following http://thinkinginsoftware.blogspot.com/2011/11/excel-jasper-reports-with-macros.html). Similar code should be available for XLSX. I would create a java standalone program (containing main method) that would load the original xlsm, add some text in A1 and then save it with POI could help in understanding if there is an issue with Apache POI. We should manually open the saved file and expect it still opens the message box just like the original xlsm. If it does not then we need to open a bug with Apache POI project. If it does then we add that information here and potentially create a bug ticket for Jasper Reports API. In addition posting the question to stack overflow just as you did over a year ago (http://stackoverflow.com/questions/28593223/apache-poi-opcpackage-unable-to-save-jasper-report-generated-xlsx) could help as well. Best regards, - Nestor

Hello, We cannot make custom palette to work. From iReport Designer version 5.6.0 we can set a custom backcolor which will correctly export the excel cell with expected backgroiund color: <reportElement mode="Opaque" x="0" y="26" width="250" height="26" forecolor="#FFFFFF" backcolor="#5D87A1" uuid="67e64e41-d7ac-4a1a-aba1-d44eb862c564"/>[/code]It is expected that we can set jasperreports.properties: net.sf.jasperreports.export.xls.create.custom.palette=true[/code]or as a property in JRXML <property name="net.sf.jasperreports.export.xls.create.custom.palette" value="true"/>[/code]To get the exact color from a jasperreports exporter however after using the jrxml from jasperreports 5.6.0 jar it will not respect the custom color and instead break with a NullPointerException:Caused by: java.lang.NullPointerExceptionat net.sf.jasperreports.engine.export.JRXlsExporter.getWorkbookColor(JRXlsExporter.java[/code]Going back to version 4.7.1 it will not break but it will not change the color either. Moving forward to jasperreports version 6.0.0 will also result in NullPointerException. Apparently this works correctly for Jasper Server ( https://community.jaspersoft.com/wiki/discrepancy-report-xls-output-cell-background-color-using-jasperreports-server-web-services ) so the question is if anybody has managed to use custom colors from the jasperreports library and how. Thanks in advance, - Nestor

Hi guys, iReport uses POI library which has support for setting the worksheet tab background color via http://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFSheet.html#setTabColor(int). Is Jasper reports / iReport supporting this feature? Thanks! - Nestor

Hello, We faced this issue *only* in Windows after an installation + settings import. As I explained in http://thinkinginsoftware.blogspot.com/2012/10/ireport-attribute-uuid-is-not-allowed.html you can go around the issue if you ignore importing settings (or if you work from OSX or Linux you should not see the issue at least in our case going from 4.1.3 to 4.7.1. I hope it helps somebody out there. Cheers, -Nestor

Thanks Sanda. Best, -Nestor

Glad to hear about the work in progress. So just to clarify myself can we expect macro support then for both JRXlsExporter and JRXlsxExporter in the near future? Thanks for this great project! -Nestor

Hi, I have created ticket http://jasperforge.org/projects/jasperreports/tracker/view.php?id=5553 We are lucky enough in our current scenario to have no bidirectional formula references so we are pushing the sheet containing the formula at the end of the workboo and then using a macro that runs automatically once the book is loaded. You can find more information about this trick and a proposal to add macro support in http://jasperforge.org/plugins/mantis/view.php?id=2363 Best, -Nestor

Hi Teodor, I have to do something similar now for JRXlsxExporter because apparently JRXlExporter has a bug ( http://jasperforge.org/plugins/espforum/view.php?group_id=102&forumid=103&topicid=93881 ) which is kind of a road block for us. So I dug into the code and I could not find a way to supply a template (InputStream) in openWorkbook(OutputStream os). I came up with something like the below but when I supply an xls or xslm as the inputstream the resulting xlsx file is always corrupt. Can you please shed some light so we can switch our project to XSLX when we need cross sheet formula capabilities? Thanks for your help on this, -Nestor package com.nestorurquiza.utils; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.util.ArrayList; import java.util.HashMap; import net.sf.jasperreports.engine.JRException; import net.sf.jasperreports.engine.JRPrintElementIndex; import net.sf.jasperreports.engine.export.ooxml.JRXlsxExporter; import net.sf.jasperreports.engine.export.ooxml.XlsxContentTypesHelper; import net.sf.jasperreports.engine.export.ooxml.XlsxRelsHelper; import net.sf.jasperreports.engine.export.ooxml.XlsxStyleHelper; import net.sf.jasperreports.engine.export.ooxml.XlsxWorkbookHelper; import net.sf.jasperreports.engine.export.ooxml.XlsxZip; import net.sf.jasperreports.engine.export.zip.ExportZipEntry; import org.apache.commons.io.IOUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; public class CustomJRXlsxExporter extends JRXlsxExporter { private static final Logger log = LoggerFactory.getLogger(CustomJRXlsxExporter.class); private InputStream is; public InputStream getIs() { return is; } public void setIs(InputStream is) { this.is = is; } @Override protected void openWorkbook(OutputStream os) throws JRException { if(is != null) { try { rendererToImagePathMap = new HashMap(); imagesToProcess = new ArrayList(); xlsxZip = new XlsxZip(); //Fill workbook entry with the supplied data? ExportZipEntry workbookEntry = xlsxZip.getWorkbookEntry(); //There is no method to supply an existing input stream as a template //OutputStream outputStream = new ByteArrayOutputStream(); //IOUtils.copy(is, outputStream); //workbookEntry.writeData(outputStream); IOUtils.copy(is, workbookEntry.getOutputStream()); wbHelper = new XlsxWorkbookHelper(workbookEntry.getWriter()); wbHelper.exportHeader(); relsHelper = new XlsxRelsHelper(xlsxZip.getRelsEntry().getWriter()); relsHelper.exportHeader(); ctHelper = new XlsxContentTypesHelper(xlsxZip.getContentTypesEntry().getWriter()); ctHelper.exportHeader(); styleHelper = new XlsxStyleHelper( xlsxZip.getStylesEntry().getWriter(), fontMap, getExporterKey(), isWhitePageBackground, isIgnoreCellBorder, isIgnoreCellBackground, isFontSizeFixEnabled ); return; } catch (IOException e) { log.error("Creating a new Workbook when I was supposed to use an existing one.", e); } } super.openWorkbook(os); } }

I can confirm this issue does not happen when we use JRXlsxExporter. The JRXlsExporter does fail so this is I think a bug. Any feedback? Should I go ahead and feel a bug fix request? Thanks, -Nestor

Hi, When running with "Excel 2007 (XLSX) Preview" the cell does get what we expect: =mySheet2!A1 However when we pick "XLS Preview" we get: =#REF!A1 You can find more information including source code/test case in http://thinkinginsoftware.blogspot.com/2011/11/jasper-reports-excel-cross-sheet.html Any help on this issue is highly appreciated. Thanks,-Nestor

At this moment my knowledge of Jasper internals is limited but perhaps this suggestion will be enough? We are using POI (JRXlsExporter) and what I did for now was just to extend the current Exporter just to place an initial WorkBook as starting point from which Jasper builds the report. The code is really simple but I agree integrating everything with iReport is the way to go. A custom setting in iReport should allow to CREATE_FROM_TEMPLATE_PATH as gnelson proposed and other flags like IGNORE_SHEETS_IN_TEMPLATE could be useful as well. Here is the code for POI which we have it working for including a zoom 75% for the workbook via a macro as explained in http://thinkinginsoftware.blogspot.com/2011/11/excel-jasper-report-with-default-zoom.html : package com.nestorurquiza.utils; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import net.sf.jasperreports.engine.export.JRXlsExporter; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.slf4j.Logger; import org.slf4j.LoggerFactory; public class CustomJRXlsExporter extends JRXlsExporter { private static final Logger log = LoggerFactory.getLogger(CustomJRXlsExporter.class); private InputStream is; public InputStream getIs() { return is; } public void setIs(InputStream is) { this.is = is; } @Override protected void openWorkbook(OutputStream os) { if(is != null) { try { workbook = new HSSFWorkbook(is); emptyCellStyle = workbook.createCellStyle(); emptyCellStyle.setFillForegroundColor((new HSSFColor.WHITE()).getIndex()); emptyCellStyle.setFillPattern(backgroundMode); dataFormat = workbook.createDataFormat(); createHelper = workbook.getCreationHelper(); return; } catch (IOException e) { log.error("Creating a new Workbook when I was supposed to use an existing one.", e); } } super.openWorkbook(os); } }

I have documented what I did with JRXlsExporter in http://thinkinginsoftware.blogspot.com/2011/11/excel-jasper-reports-with-macros.html At least using macros we can get some formattinf capabilities iReport/Jasper Reports might be missing or even those impossible to implement outside of the VBA World. Here is an example to zoom an Excel Jasper Report http://thinkinginsoftware.blogspot.com/2011/11/excel-jasper-report-with-default-zoom.html So I do think the patch suggested by gnelson should be implemented. That way from iReport we could have a hint so the front end report designer can get the formatting they want using an Excel template without the need to wait for an external API implementation.

Hi, Is there any plan to include this patch? It looks to me like something the whole community will benefit from. As of now the option would be a custom implementation that could break with new releases of Jasper Reports API. Thanks! -Nestor

Here is how to solve this issue: http://jasperforge.org/plugins/espforum/view.php?group_id=102&forumid=103&topicid=32273

Hi, Sorry to resucitate an old thread but the suggested link does not work. I am wondering how did you resolve this issue? Excel formatting allows for more options than Jasper as far as I can tell. Thanks in advance, -Nestor

Here is what I did to solve this issue. Hopefully will help others. Thanks, -Nestor

Hi Danda, I am wondering how did you solve the issue. I see you were on the right track as I believe the table component is the way to go however it is not working for me. Thanks in advance, -Nestor