view release on metacpan or  search on metacpan

lib/RapidApp/Module/Grid/Role/ExcelExport.pm  view on Meta::CPAN

my $xlsx_mime= 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
my %formats= map { $_->{mime} => $_ } (
	{ mime => 'text/csv',                  file_ext => '.csv',  renderer => 'export_render_csv' },
	{ mime => 'text/tab-separated-values', file_ext => '.tsv',  renderer => 'export_render_tsv' },
	{ mime => 'application/json',          file_ext => '.json', renderer => 'export_render_json' },
	{ mime => $xlsx_mime,                  file_ext => '.xlsx', renderer => 'export_render_excel' },
);
sub export_to_file {
	my $self = shift;
	my $params = $self->c->req->params;
	
	# Determine output format, defaulting to CSV
	my $export_format= $formats{$params->{export_format}} || $formats{'text/csv'};
	
	# Determine file name, defaulting to 'export', and apply the default file extension.
	my $export_filename = $params->{export_filename} || 'export';
  
  # New: append the current date/time to the export filename:
  my $dt = DateTime->now( time_zone => 'local' );
  $export_filename .= join('','-',$dt->ymd('-'),'_',$dt->hms(''));
  
	$export_filename .= $export_format->{file_ext}
		unless substr($export_filename,-length($export_format->{file_ext})) eq $export_format->{file_ext};

	# Clean up params so that AppGrid doesn't get confused
	delete $params->{export_filename};
	delete $params->{export_format};
	
	# Get the list of desired columns from the query parameters.
	# If not specified, we use all defined columns.
	my $columns= ($params->{columns})
		? $self->json->decode($params->{columns})
		: $self->column_order;
	
	# filter out columns that we can't use, and also build the column definitions for ExcelTableWriter
	my @colDefs = ();
	foreach my $col (@$columns) {
		my $field = $self->get_column($col) or die "column $col does not exist in columns hash";
		
		# New: If render_column is defined, use it instead of name
		my $colname = $field->render_column ? $field->render_column : $field->name;
		
		next if ($field->name eq 'icon');
		next if $field->no_column;
		next unless (defined $field->header and defined $field->name);
		push @colDefs, {
			name => $colname,
			label => $field->header
		};
	}
	
	# Restrict columns to the set we chose to keep.
	# Note that the previous ref is a constant, and would be bad if we modified it.
	$columns= [ map { $_->{name} } @colDefs ];

	# override the columns that DataStore is fetching
	#$self->c->req->params->{columns}= $self->json->encode($columns);
	my $data = $self->DataStore->read({%$params, columns => $columns, ignore_page_size => 1});

	# TODO: We just read all rows into memory, and now we're building the file in memory as well.
	# We would do well to replace this with a db-cursor-to-tempfile streaming design
	
	my $dlData = '';
	open my $fd, '>', \$dlData;
	
	my $method= $export_format->{renderer};
	$self->$method({ %$params, col_defs => \@colDefs }, $data, $fd);
	
	close $fd;

	$self->render_as_json(0);
	
	my $h= $self->c->res->headers;
	
	# Excel 97-2003 format (XLS)
	#$h->content_type('application/vnd.ms-excel');
	
	# Generic Spreadsheet format
	#$h->content_type('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	
	# Excel XLSX format
	#$h->content_type('application/vnd.ms-excel.12');
	$h->content_type($export_format->{mime});
	
	# Make it a file download:
	$h->header('Content-disposition' => "attachment; filename=\"$export_filename\"");

	$h->content_length(do { use bytes; length($dlData) });
	$h->last_modified(time);
	$h->expires(time());
	$h->header('Pragma' => 'no-cache');
	$h->header('Cache-Control' => 'no-cache');
	
	return $dlData;
}

sub export_render_excel {
	my ($self, $params, $data, $fd)= @_;
	
	my $xls = Excel::Writer::XLSX->new($fd);
	
	# -- Excel/Writer/XLSX-specific: (slashes used instead of :: to protect from find/replace)
	$xls->set_optimization();
	# --
	
	$xls->set_properties(
		title    => 'Exported RapidApp AppGrid Module: ' . ref($self),
	);
	my $ws = $xls->add_worksheet;
	my $tw = RapidApp::Spreadsheet::ExcelTableWriter->new(
		wbook	=> $xls,
		wsheet	=> $ws,
		columns	=> $params->{col_defs},
		ignoreUnknownRowKeys => 1,
	);
	
	#########################################
	$tw->writeRow($_) for (@{$data->{rows}});
	#########################################
	
	#### Column Summaries ####