KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Empty second sheet on excel bigdataexport #1515

Open Andrew Borell opened this topic on on Jul 6, 2020 - 5 comments

Andrew Borell commented on Jul 6, 2020

I have some big data I need to run reports on. In a year span the table could be 400,000 rows and about 85 columns of data computed in a stored procedure that can generate tons of different views for financial reporting. I have no problem exporting the data. First sheet looks good but the second sheet is blank.

Here is what I am working with in dev at the moment. What am I missing?

 protected function setup()
    {
		$params = array();

		$params[':pdate1'] = $this->params["pdate1"];
		$params[':pdate2'] = $this->params["pdate2"];
		$params[':pdate3'] = $this->params["pdate3"];
			
		$this->src('report')
			->query( "exec sp_getFinancialData @pdate1 = :pdate1,@pdate2 = :pdate2,@pdate3 = :pdate3"	)
			->params($params)
			->saveTo($trip_list);
			
			$trip_list->pipe($this->dataStore("trip_list"));
			
			$trip_list->pipe(new Pivot(array(
					"dimensions" => array(
						"column" => "expected, allowed",
						"row" => "orgCode"
					),
					"aggregates"=>array(
						"sum" => "charge"
					)
				)))
			->saveTo($trip_list_pivot);
			
			$trip_list_pivot->pipe($this->dataStore("trip_list_pivot"));
			;
    }    

I just want to list the org name on the left with some agg data in the columns across row grouped by the organization.

Andrew Borell commented on Jul 6, 2020

scratch this reply. Still need help with empty pivot for other reasons.

David Winterburn commented on Jul 7, 2020

Hi Andrew,

BigSpreadsheetExportable only works for text and table widgets and not pivot table or chart. The reason is that it uses an excel stream writer to output each row one by one while pivot table or chart needs cell merging/chart formatting which the excel stream writer can not do. For this reason if you want to export pivot table or chart to excel, ExcelExportable is the only option now.

In the future we will do research to see if it's possible to merge multiple excel files into one so users could use both ExcelExportable and BigSpreadsheetExportable in one file.

Andrew Borell commented on Jul 7, 2020

Old excel formats only support 65536 rows and I suspect ExcelExportable is probably 97 format wheras the big file export is true xslx ( just a guess ). Instead of using pivot can I apply a template to the second sheet or something?

Andrew Borell commented on Jul 7, 2020

In retrospect I think the best solution will be to pivot the data right in a copy of the original procedure. This is very easy to do with the built-in group_concat function in mysql and probably a lot faster. Also as far as koolreport is concerned BigSpreadsheetExportable is much faster than the ExcelExportable. I dont mind splitting up the data into two separate spreadsheets and adjusting the names to identify them respectively as the transaction data or pivot data, so I guess I will consider this resolved.

David Winterburn commented on Jul 8, 2020

Hi Andrew,

In fact both ExcelExportable and BigSpreadsheetExportable use modern excel format. The difference between them is the way excel file is created.

In the case of the former, an object representing the whole excel file is created. Thus, it can add complex excel objects to its sheets at the cost of performance in both file size and processing time.

In the later case only a stream writer is created so big spreadsheet could output huge files row by row (no need to save previous rows in memory) at the cost of complexity (no cell merging or chart).

Let us know if you have any more difficulty with excel export. Thanks!

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
help needed
solved

Excel