Perl parse excel files

Change Language. Related Articles. Table of Contents. Improve Article. Save Article. Like Article. Last Updated : 11 Jul, Using write to write values in sheet. Next Perl Creating Excel Files. Recommended Articles. Article Contributed By :. Easy Normal Medium Hard Expert. Writing code in comment?

This was most commonly used to iterate over the worksheets in a workbook:. The worksheet method returns a single Worksheet object using either its name or index:.

For full documentation of the methods available via a Workbook object see Spreadsheet::ParseExcel::Workbook. A Worksheet object is obtained via the "worksheets " or "worksheet " methods. The Spreadsheet::ParseExcel::Worksheet class exposes a lot of methods but in general very few are required unless you are writing an advanced filter.

The most commonly used methods are detailed below. The others are documented in Spreadsheet::ParseExcel::Worksheet. Otherwise returns undef. Formatted in this sense refers to the numeric format of the cell value. For example a number such as might be formatted as 40,, If the cell doesn't contain a numeric format then the formatted and unformatted cell values are the same, see the unformatted method below.

In the case of a cell with formatting but no numeric or string contents the method will return the empty string ''.

These properties are generally only of interest to advanced users. Casual users can skip this section. Formatters can be passed to the parse method to deal with Unicode or Asian formatting.

Spreadsheet::ParseExcel includes 2 formatter classes. FmtDefault and FmtJapanese. It is also possible to create a user defined formatting class. Method to check the type of data in the cell. Should return Date , Numeric or Text. It is passed the following parameters:. Converts the string data in the cell into the correct encoding. Convert the original unformatted cell value into the appropriate formatted value.

For instance turn a number into a formatted date. In some cases a Spreadsheet::ParseExcel application may consume a lot of memory when processing a large Excel file and, as a result, may fail to complete.

The following explains why this can occur and how to resolve it. Spreadsheet::ParseExcel processes an Excel file in two stages. In the second stage it parses the binary stream to read workbook, worksheet and cell data which it then stores in memory. The majority of the memory usage is required for storing cell data. The reason for this is that as the Excel file is parsed and each cell is encountered a cell handling function creates a relatively large nested cell object that contains the cell value and all of the data that relates to the cell formatting.

For large files a 10MB Excel file on a MB system this overhead can cause the system to grind to a halt. However, in a lot of cases when an Excel file is being processed the only information that is required are the cell values. In these cases it is possible to avoid most of the memory overhead by specifying your own cell handling function and by telling Spreadsheet::ParseExcel not to store the parsed cell data. This is achieved by passing a cell handler function to new when creating the parse object.

Here is an example. The user specified cell handler is passed as a code reference to new along with the parameter NotSetCell which tells Spreadsheet::ParseExcel not to store the parsed cell. Effectively a two dimensional array or "matrix". Each element in the returned array represents one row in the spreadsheet.

Toggle navigation Perl Maven. Standard output, standard error and command line redirection Warning when something goes wrong What does die do? Unknown warnings category Can't use string Symbolic references in Perl Can't locate Can't locate object method " Useless use of hash element in void context Useless use of private variable in void context readline on closed filehandle in Perl Possible precedence issue with control flow operator Scalar value Formatted printing in Perl using printf and sprintf.

How to read an Excel file in Perl Spreadsheet::Read. Prev Next. Written by Gabor Szabo. If you have any comments or questions, feel free to post them on the source of this page in GitHub. Source on GitHub. Comment on this post.