Need to generate excel having more than 70,000 records

[gube]

Hi Monks,

I am generating an excel file based on the data from my table. The record exceeded more than 70,000. But, when i create excel using Spreadsheet::WriteExcel i cannot able to create records more than 65,536. Since my client using latest windows excel he is expecting all the records having more than 70,000. Can any one please throw me some modules or ideas to proceed ?

Please see below sample code:

use Spreadsheet::WriteExcel;
# Create a new Excel workbook
my ($col, $row);
$col = $row = 0;
my $workbook = Spreadsheet::WriteExcel->new('perl1.xls');
# Add a worksheet
my  $worksheet = $workbook->add_worksheet("test");
for (0..70000) {
    $worksheet->write($_, $col, "Testing");
}
$workbook->close();

[tilly]

The format produced by Spreadsheet::WriteExcel is the one used by older versions of Excel. As you've found, it has the limitations of the older version of Excel. Therefore you need to use a newer format.

The simplest option is csv. If this will not work (formatting is the usual reason why it won't) then you can step things up by creating an HTML file with a single table. Excel will load this without difficulty and you can do some formatting, but you will not have access to more advanced features of Excel. If you want that, Excel now saves files internally as XML in a zipped file. Nothing in principle stops you from creating the same exact XML.

If you wish to go this direction I would advise creating an Excel spreadsheet that looks like what you want it to look like and has all of the features you want. Unzip it. Then open it up and see if you can find where the stuff that is important for the data is. Then dynamically write that part, leave alone anything you can, and zip it up again. Then pray and try opening your file in Excel.

If you're really brave you could go to http://technet.microsoft.com/en-us/library/cc179190.aspx and try to figure out the format from the documentation that Microsoft provides.

[holli]

Try generating the file in the Excel XML-Format.

[Ish]

Can you not just produce a CSV file for them to import? Just a thought if the current module does not support > 64k rows.

[whakka]

Excel is not the way to go for lots of data - no module will give you what you want since the limiting factor is built into Excel itself. I would follow the advice of Ish and output .csv which can be read in many types of software and inform your client what he/she wants isn't possible. Either that or split the data into multiple worksheets.

Here's Microsoft's outline of Excel's specification and limits.

Update: It appears I should have read the fine print, sorry for the dated information.

[ELISHEVA]

The limits for Excel 2007 are 1,048,576 rows by 16,384 columns. For further information see, Excel 2007 Specifications and Limits.

Best, beth

[tilly]

Excel 2007 no longer has those limits. (Well it has limits, but they are much more generous ones.)

[Mr. Muskrat]

The OP didn't specify which version of Excel would need to parse the file so I think that you have provided a perfectly acceptable answer.

[GrandFather]

OP: "Since my client using latest windows excel he is expecting all the records having more than 70,000.".

[Gangabass]

The only way i can think is to use Win32::OLE.

[dragonchild]

use Excel::Template;

my $template = Excel::Template->new(
    RENDERER => Excel::Template->RENDER_XML,
);

or Excel::Template->RENDER_BIG. Either way, E::T handles all that stuff for you.

[runrig]

I don't think that will work. Excel::Template says those options use Spreadsheet::WriteExcelXML or Spreadsheet::WriteExcel::Big as backends, and those modules still say they have a row limit of 65536. Spreadsheet::WriteExcel::Big lets you create files > 7MB, but still has the 65536 rows limit.

Update: The XML "template" of Excel::Template is a mini-DSL that the author uses to produce different kinds of files using the same code, and has nothing to do with the XML in Excel.

[tilly]

I had not known about this module. Now that I know about it, it is clearly the right way to go.

[jurple]

Could you convince them to accept the data spread over multiple worksheets in the same workbook? That would be a very quick fix...

[sundialsvc4]

A client who's got 70,000 rows in a spreadsheet needs to learn about Microsoft Excel lists. Especially when used in conjunction with Microsoft SharePoint (which is a thin wrapper over SQL databases and file-synchronization technology), this represents a much more intelligent way to manage this data.

And if you don't want to go that way, well, the client undoubtedly has Microsoft Access, and it is easy to define a spreadsheet list which links directly and invisibly to that database. They won't have to change their data, formatting or basic work-flow.

(Eeeee-yuck! I've just caught myself recommending Microsoft, and I didn't say one thing about Perl!)   ;-)

[roboticus]

gube:

If you need only limited control of formatting, you could emit the data as an HTML document and load it into Excel. I've had good success with that in the past, with the ability to control much (but not all) of the formatting.

...roboticus

[VMat]

I like imrags' suggestion. Win32::OLE is cool. You can do pretty much anything you do with Visual Basic inside Excel.

The drawback is that you need Excel installed and running (i.e., you have to open your document to start working on it). If you have the newer version that supports the number of records you want, you're good to go.

VMat

[gube]

Thanks Ish. But, they need excel since for some formatting purpose.

Gubs

[Ish]

Hi, I'm sure they will use Excel, but there is any reason why you couldn't produce a CSV file and let them import it into Excel? CSV files are the most frequently used 'glue' between things like databases and spreadsheets (and ordinary files of course). A sort of common denominator as it were.

[tilly]

There is absolutely a good reason and you were just told it. CSV is the wrong answer if you want any control of formatting. And there are a lot of valid reasons to care about formatting, from keeping Excel from breaking things by trying to convert things to dates to producing something that looks nice because that is what the client was promised.

[derby]

Ish is correct. If the report is just plain tabular data then just create a CSV file and slap a '.xls' extension on the end of it. When you go to open it with Excel, Excel will do the right thing.

[imrags]

Use Win32::OLE
a good tutorial can be found at Tutorial