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

hello, I am trying to read a .xlsx file, but the values of the cells are blank. I am doing this on windows. I have basically copied the synopsis from cpan, but I am not using iconv. I coudn't get TEXT::ICONV to work. The text in my files is "normal" ascii. This is how I am using Spreadsheet::XLSX my $excel = Spreadsheet::XLSX->new($filename); Is this ok? Should I try harder to get ICONV to work ? thanks, leaf

Replies are listed 'Best First'.
Re: reading xlsx files
by sierpinski (Chaplain) on Jul 29, 2010 at 18:07 UTC
    Please post your entire code, and use <code> tags so it's formatted for easier reading. Something that we can drop into a file on our systems and try to execute.
[reply]
Re: reading xlsx files
by dasgar (Priest) on Jul 29, 2010 at 18:33 UTC

    Since you're on Windows and it sounds like you have Excel installed, I'd suggest spending some time learning how to use Win32::OLE to directly control Excel via OLE. Once you get the hang of it, data retrieval from cells as well as writing data/formulas to cells become very easy. You'll want to use the OLE browser that comes with the Win32::OLE module. Also, you can record a macros and then look at it's source code to help figure out how to do more complicated tasks, such as graph creation/manipulation. Of course, both methods are giving you information in VB, which you'll need to convert to Perl. Also, you'll want to be doing some error trapping to help avoid creating orphaned Excel processes.

    After doing a quick search on "win32 ole excel", there appears to be a number of perlmonks.org nodes covering using Win32::OLE to control Excel. That might be a good start with learning about that.

[reply]
Re: reading xlsx files
by earthfriendlyleaf (Initiate) on Jul 31, 2010 at 13:01 UTC
    hi, Thanks for responding. I have posted the code for reference. I tried this on my computer at home and it works. I have Strawberry Perl at home and Active State on my machine at work. I'll try it with Strawberry Perl at work next week. Thank you for you suggestion about OLE. I will definitely look into this. I am finding perl very useful and fun. But I don't have Excel installed on my computer at work. So I think for now I need to stick with Spreadsheet::XLSX. thanks! 
    
 
 

 use Spreadsheet::XLSX;
 
 my $excel = Spreadsheet::XLSX -> new ('file.xlsx');
 
 foreach my $sheet (@{$excel -> {Worksheet}}) {
 
        printf("Sheet: %s\n", $sheet->{Name});
        
        $sheet -> {MaxRow} ||= $sheet -> {MinRow};
        
         foreach my $row ($sheet -> {MinRow} .. $sheet -> {MaxRow}) {
         
                $sheet -> {MaxCol} ||= $sheet -> {MinCol};
                
                foreach my $col ($sheet -> {MinCol} ..  $sheet -> {Max
+Col}) {
                
                        my $cell = $sheet -> {Cells} [$row] [$col];
 
                        if ($cell) {
                            printf("( %s , %s ) => %s\n", $row, $col, 
+$cell -> {Val});
                        }
 
                }
 
        }
 
 }

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom