Need to create worksheet in excel

[2006 JR Open Discussion]

By: ramesh - ramesh2410

Need to create worksheet in excel

2006-02-01 01:19

Hi Pals,

 

The data retrieved from the databse need to be shown in different worksheets of excel.if anyone is aware to create worksheet,please provide the solution.

 

Thanks for the effort.

 

 

 

 

By: ramesh - ramesh2410

RE: Need to create worksheet in excel (Urgent

2006-02-01 02:37

Using Jasper Reports i need to create the excels multiple work sheet

 

Thanks for the intrest and effort

[pbrain]

Hey Remesh! I have to do the same thing by tomorrow! Did you find a jasper report tag to specify a new sheet?

 

Pat

[manvanstaal]

So, does anyone have a answer yet. I need to do the same thing. In actual effect i need to create 4 different report in one Excel report, just using 4 different worksheets. So if anyone has some info it would be great.

[manvanstaal]

Well i have found a answer to my question. So for anyone that is interested, i am sure there is better solutions but this might help some people.

 

I created my own JRXlsAbstractExporter and JRXlsExporter.

 

1. I added 3 methods in MyJRXlsAbstractExporter.

2. I overrode the openWorkbook method in MyJRXlsExporter.

3. The rest of the code is just a pure copy.

4. To use it i have a PrintReportXls method. It will create the MyJRXlsExporter(), set the parameters. Then call the exporter.exportReportAsPage(worksheetName)

5. After you have exported all your reports you call the cleanUp method.

 

This is the methods that i added.

public abstract class MyJRXlsAbstractExporter extends JRAbstractExporter

{

....

/**

*

*/

public void exportReportAsPage(String sheetName) throws JRException

{

progressMonitor = (JRExportProgressMonitor)parameters.get(JRExporterParameter.PROGRESS_MONITOR);

 

/* */

setOffset();

 

try

{

/* */

setExportContext();

 

/* */

setInput();

 

if (!parameters.containsKey(JRExporterParameter.FILTER))

{

filter = JROriginExporterFilter.getFilter(jasperPrint.getPropertiesMap(), XLS_ORIGIN_EXPORTER_FILTER_PREFIX);

}

 

/* */

if (!isModeBatch)

{

setPageRange();

}

 

setParameters();

 

OutputStream os = (OutputStream)parameters.get(JRExporterParameter.OUTPUT_STREAM);

if (os != null)

{

exportReportAsPageToStream(os, sheetName);

}

else

{

File destFile = (File)parameters.get(JRExporterParameter.OUTPUT_FILE);

if (destFile == null)

{

String fileName = (String)parameters.get(JRExporterParameter.OUTPUT_FILE_NAME);

if (fileName != null)

{

destFile = new File(fileName);

}

else

{

throw new JRException("No output specified for the exporter.");

}

}

 

try

{

os = new FileOutputStream(destFile);

exportReportAsPageToStream(os, sheetName);

}

catch (IOException e)

{

throw new JRException("Error trying to export to file : " + destFile, e);

}

}

}

finally

{

resetExportContext();

}

}

 

protected void exportReportAsPageToStream(OutputStream os, String sheetName) throws JRException {

System.out.println("*** OutputStream ="+ os.hashCode());

 

openWorkbook(os);

 

for(reportIndex = 0; reportIndex < jasperPrintList.size(); reportIndex++)

{

jasperPrint = (JasperPrint)jasperPrintList.get(reportIndex);

defaultFont = new JRBasePrintText(jasperPrint.getDefaultStyleProvider());

 

List pages = jasperPrint.getPages();

if (pages != null && pages.size() > 0)

{

if (isModeBatch)

{

startPageIndex = 0;

endPageIndex = pages.size() - 1;

}

 

if (isOnePagePerSheet)

{

for(int pageIndex = startPageIndex; pageIndex <= endPageIndex; pageIndex++)

{

if (Thread.currentThread().isInterrupted())

{

throw new JRException("Current thread interrupted.");

}

 

JRPrintPage page = (JRPrintPage)pages.get(pageIndex);

 

if (sheetName != null)

{

createSheet(sheetName);

}

else

{

createSheet("Page");

}

 

// we need to count all sheets generated for all exported documents

sheetIndex++;

 

/* */

exportPage(page, /*xCuts*/null, /*startRow*/0);

}

}

else

{

// Create the sheet before looping.

if (sheetName != null )

{

createSheet(sheetName);

}

else

{

createSheet(jasperPrint.getName());

}

 

// we need to count all sheets generated for all exported documents

sheetIndex++;

 

/*

* Make a pass and calculate the X cuts for all pages on this sheet.

* The Y cuts can be calculated as each page is exported.

*/

CutsInfo xCuts =

JRGridLayout.calculateXCuts(

getNature(), pages, startPageIndex, endPageIndex,

jasperPrint.getPageWidth(), globalOffsetX

);

//clear the filter's internal cache that might have built up

if (filter instanceof ResetableExporterFilter)

((ResetableExporterFilter)filter).reset();

 

int startRow = 0;

 

for(int pageIndex = startPageIndex; pageIndex <= endPageIndex; pageIndex++)

{

if (Thread.currentThread().isInterrupted())

{

throw new JRException("Current thread interrupted.");

}

JRPrintPage page = (JRPrintPage)pages.get(pageIndex);

startRow = exportPage(page, xCuts, startRow);

}

 

if (isRemoveEmptySpaceBetweenColumns)

{

removeEmptyColumns(xCuts);

}

}

}

}

 

// closeWorkbook(os);

}

 

public void cleanUp(OutputStream os) throws JRException {

try

{

closeWorkbook(os);

os.flush();

}

catch (IOException e)

{

// throw new JRException("Error trying to export to file : " + destFile, e);

throw new JRException("Error trying to export to file : " + e.getMessage());

}

finally

{

if (os != null)

{

try

{

os.close();

}

catch(IOException e)

{

}

}

}

 

}

 

....

}

 

public class MyJRXlsExporter extends MyJRXlsAbstractExporter

{

....

protected void openWorkbook(OutputStream os)

{

if (workbook != null) {

return;

}

workbook = new HSSFWorkbook();

emptyCellStyle = workbook.createCellStyle();

emptyCellStyle.setFillForegroundColor((new HSSFColor.WHITE()).getIndex());

emptyCellStyle.setFillPattern(backgroundMode);

dataFormat = workbook.createDataFormat();

}

 

...

}

[Teodor Danciu]

Hi,

 

Have you tried to batch-export to XLS several reports?

You can see this in the /demo/samples/batchexport sample provided with the project.

 

It is about providing a value for the JASPER_PRINT_LIST exporter parameter instead of the usual JASPER_PRINT exporter parameter.

 

I hope this helps.

Teodor