ReportServer Forum

Patryx

Member

Registered: 2019-03-25

Report Export - Is possible to export dynamic reports to XLS or XLSX?

Hi,
I need to have possibility to export reports in EXCEL in two formats: XLS and XLSX (user can choose which one).
I know that I can choose globally which format is always exported via built-in button: Excel (configuration in "excelexport.cf)" but such permanent setting doesn't resolve my problem.
Should I create Report Executor? Any hints?

What's more, is possible to export dynamic list report to Word automatically?

I would be grateful if you give me some hints.

Patryx

Member

Registered: 2019-03-25

Re: Report Export - Is possible to export dynamic reports to XLS or XLSX?

Hi,
I think I resolved this problem by adding output generator hook based on code from build-in net.datenwerke.rs.base.service.reportengines.table.output.generator.XLSOutputGenerator.java.
Maybe sb has the same problem so I pase my code of such a script:

/* Script with XLS Excel97 output generator class to register such an output generator as a hook */
 
import java.io.ByteArrayOutputStream;
import java.io.IOException;

import net.datenwerke.rs.base.service.reportengines.table.hooks.TableOutputGeneratorProviderHook;
import net.datenwerke.rs.base.service.reportengines.table.hooks.adapter.TableOutputGeneratorProviderHookAdapter;
import net.datenwerke.rs.base.service.reportengines.table.output.generator.TableOutputGenerator;
import net.datenwerke.rs.base.service.reportengines.table.output.generator.RSTableOutputGenerator;

import net.datenwerke.rs.base.service.reportengines.locale.ReportEnginesMessages;

import net.datenwerke.rs.base.service.reportengines.table.output.object.CompiledXLSTableReport;
import net.datenwerke.rs.base.service.reportengines.table.output.object.RSTableModel;
import net.datenwerke.rs.base.service.reportengines.table.utils.RSTableToXLS;
import net.datenwerke.rs.core.service.reportmanager.ReportExecutorService;
import net.datenwerke.rs.core.service.reportmanager.engine.CompiledReport;
import net.datenwerke.rs.core.service.reportmanager.exceptions.ReportExecutorRuntimeException;
import net.datenwerke.rs.utils.localization.LocalizationServiceImpl;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.google.inject.Inject;
 
 
public class XLSExcel97OutputGenerator extends RSTableOutputGenerator{
	
	public static final String HOOK_NAME = "XLS_EXCEL97_OUTPUT_GENERATOR";
		
	private final ReportEnginesMessages messages = LocalizationServiceImpl.getMessages(ReportEnginesMessages.class);
	
	private final RSTableToXLS rsTableToXLS;
		
	@Inject
	public XLSExcel97OutputGenerator(
		RSTableToXLS rsTableToXLS
		) {
		super();
		
		/* store objects */
		this.rsTableToXLS = rsTableToXLS;
	}

	@Override
	public String[] getFormats() {
	    String[] formats = ["EXCEL97"];
        return formats;
	}
	
	private String getExportFormat(){
		return "xls";
	}
	
	@Override
	public CompiledReport getTableObject() {
		boolean stream = null != os;
		if(! stream)
			os = new ByteArrayOutputStream();
		
		try {
		
			/* export to excel */
			Workbook wb = new HSSFWorkbook();

			Sheet sheet = wb.createSheet(messages.xlsOutputGeneratorSheetName());
			
			/* export */
			rsTableToXLS.exportToExcel((RSTableModel) super.getTableObject(), wb, sheet, false, originalReport);
			
			/* finalize workbook */
			wb.write(os);
			
		} catch (Exception e) {
			ReportExecutorRuntimeException rere = new ReportExecutorRuntimeException(e.getMessage());
			rere.initCause(e);
			throw rere;
		} finally{
			try {
				os.close();
			} catch (IOException e) {
				throw new ReportExecutorRuntimeException(e);
			}
		}
		
		/* create report object */
		return new CompiledXLSTableReport(stream ? null : ((ByteArrayOutputStream)os).toByteArray());
	}

	@Override
	public CompiledReport getFormatInfo() {
		return new CompiledXLSTableReport(null);
	}
		
	@Override
    boolean supportsStreaming(){
       return false;
    }
	
}

final XLSExcel97OutputGenerator xlsGenerator = GLOBALS.injector.getInstance(XLSExcel97OutputGenerator.class);

/* specify provider */
def provider = [
	provideGenerators : { ->
		return [xlsGenerator]
	}
] as TableOutputGeneratorProviderHookAdapter

/* plugin hook */
GLOBALS.services.callbackRegistry.attachHook(XLSExcel97OutputGenerator.HOOK_NAME, TableOutputGeneratorProviderHook.class, provider)

I have a question to Report Server team:
Why is there an error for report with empty results (I want to export report with no rows) if I don't override method supportsStreaming() to return false instead of true?

Regards,
Patryk

Last edited by Patryx (2019-07-09 15:44:32)

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Report Export - Is possible to export dynamic reports to XLS or XLSX?

Hi Patryx,

supportsStreaming has to be set to false for the old excel format. So your script sets it correctly, thanks for sharing.

Regards,
Eduardo