Splitting the records into multiple worksheets

chandantul has asked for the wisdom of the Perl Monks concerning the following question:

Hello All, I am currently using Excel::Writer::XLSX->new( 'C:/Details.xlsx' ); in order to generate a report that is more than 616337 records, I was pulling the records through API calls. my code is unable to process the record as its trying to process the data in single worksheet. I am creating pivot chart based on the one worksheet data post calculations, How i can achieve my goal to the record of 616337? What is the best suggestions as per my small code snippet below. Please let me know if you need additional code details in order provide a suggestion on the same.

## Create Excel File.

my $workbook  = Excel::Writer::XLSX->new( 'C:/Details.xlsx' );
my $format_bold = $workbook->add_format( bold => 1, size => 10, top=> 
+1, bottom=> 1 ); 
        my $format2 = $workbook->add_format(%header1);
my $worksheet = $workbook->add_worksheet('Data');
my $r = 1;
my $r2 = 1;
my $r3 = 1;
my $r4 = 1;
my $r5 = 1;
my $r7 = 1;
my $r8 = 1;
my $r10 = 1;
my $row3 = 1;
my $row4 = 1;
[download]

Comment on Splitting the records into multiple worksheets Download Code

Replies are listed 'Best First'.
Re: Splitting the records into multiple worksheets by NetWallah (Canon) on Feb 20, 2021 at 04:28 UTC
Excel is the wrong tool if you are processing > 10k rows. I would recommend a sqlite database. It has perl interfaces via the DBI and DBD::Sqlite perl modules. There are plenty of snippets to get you started - check out this cookbook. "Avoid strange women and temporary variables."	[reply]
Re^2: Splitting the records into multiple worksheets by chandantul (Scribe) on Feb 20, 2021 at 04:43 UTC
Thanks for the suggestion but Excel is the requirement as we need to generate the report with pivot chart with another sheet in the same excel file. The above solution will change complete code design as well. Do you have any other solution?	[reply]
Re^3: Splitting the records into multiple worksheets by NetWallah (Canon) on Feb 20, 2021 at 04:54 UTC
After creating the raw data table in SQLite, you could use SQL to generate your pivoted data. The SQL "GROUP BY" clause can do the pivot functions to summarize. The result can then easily export into a csv file suitable for import into Excel. "Avoid strange women and temporary variables."	[reply]
Re^4: Splitting the records into multiple worksheets by chandantul (Scribe) on Feb 20, 2021 at 05:29 UTC
Re^5: Splitting the records into multiple worksheets by Bod (Parson) on Feb 20, 2021 at 22:31 UTC
Some notes below your chosen depth have not been shown here
Re: Splitting the records into multiple worksheets by Marshall (Canon) on Feb 20, 2021 at 06:04 UTC
I am having trouble running your code. I installed Excel::Writer::XLSX on my Win10 machine. In the following code, `new( 'filename.xlsx' )` succeeds and generates a workbook, but for some reason `new ('C:/Details.xlsx' );` fails. I am not sure why that is? `use strict; use warnings; use Excel::Writer::XLSX; $\|=1; ## Create Excel File. #my $workbook = Excel::Writer::XLSX->new( 'C:/Details.xlsx' ); my $workbook = Excel::Writer::XLSX->new( 'filename.xlsx' ); print "workbook is $workbook\n"; __END__` [download] Update: It is hard for me to imagine a spreadsheet with 616,337 rows! In theory, I guess this is possible, but not a good way to proceed.	[reply] [d/l] [select]