#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    39
    Rep Power
    2

    Sometimes unable to "open" excel sheet from perl script


    Hello,
    Sometimes when I try to open an existing excel sheet from a perl script, it gives me a prompt where I have to select which format to be used for opening. I have tried most of them, but they give me error saying it cannot open.

    This is my script.
    Code:
    #!/usr/local/bin/perl
    
    system("libreoffice mysheet.xls");
    I don't think I have permissions to attach an image, so please check out this url, where I have uploaded the prompt - libreoffice prompt I get

    I don't always get this, but sometimes when I'm using the Spreadsheet::WriteExcel library.(I'm not certain it's related to Spreadsheet::WriteExcel though, it's fantastic, but just giving some extra details )

    Thanks.
  2. #2
  3. !~ /m$/
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    May 2004
    Location
    Reno, NV
    Posts
    4,259
    Rep Power
    1810
    I wasn't going to reply at first, because I didn't see this as a perl problem. In reading again, you might be saying that spreadsheets created with Spreadsheet::WriteExcel are not being recognized at all.

    If that's the case, you may need to show the script you are using to create spreadsheets. Take note of this in the documentation though:

    compatibility_mode()

    This method is used to improve compatibility with third party applications that read Excel files.

    $workbook->compatibility_mode();
    An Excel file is comprised of binary records that describe properties of a spreadsheet. Excel is reasonably liberal about this and, outside of a core subset, it doesn't require every possible record to be present when it reads a file. This is also true of Gnumeric and OpenOffice.Org Calc.

    Spreadsheet::WriteExcel takes advantage of this fact to omit some records in order to minimise the amount of data stored in memory and to simplify and speed up the writing of files. However, some third party applications that read Excel files often expect certain records to be present. In "compatibility mode" Spreadsheet::WriteExcel writes these records and tries to be as close to an Excel generated file as possible.

    Applications that require compatibility_mode() are Apache POI, Apple Numbers, and Quickoffice on Nokia, Palm and other devices. You should also use compatibility_mode() if your Excel file will be used as an external data source by another Excel file.

    If you encounter other situations that require compatibility_mode(), please let me know.

    It should be noted that compatibility_mode() requires additional data to be stored in memory and additional processing. This incurs a memory and speed penalty and may not be suitable for very large files (>20MB).

    You must call compatibility_mode() before calling add_worksheet().
    Although it is said Calc does not need this mode, I would test it and see if it clears your problem.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    39
    Rep Power
    2
    Originally Posted by keath
    If that's the case, you may need to show the script you are using to create spreadsheets.
    Sure, please have a look at this script.
    Code:
    #!/usr/local/bin/perl
    
    use strict;
    use Spreadsheet::WriteExcel;
    
    my $workbook  = Spreadsheet::WriteExcel->new( 'test1.xls' );
    $workbook->compatibility_mode();
    my $worksheet = $workbook->add_worksheet();
    my $bold      = $workbook->add_format( bold => 1 );
    
    # Add the worksheet data that the charts will refer to.
    my $headings = [ 'Category', 'Values 1', 'Values 2' ];
    my $data = [
        [ 'one', 'two', 'three', 'four', 'five', 'six','seven','eight','nine' ],
        [41, 249, 75, 6, 149, 29, 43 ,56 ,78],
    ];
    
    $worksheet->write( 'A1', $headings, $bold );
    $worksheet->write( 'A2', $data );
    
    #making the chart instance
    my $chart1 = $workbook->add_chart( type => 'column',embedded => 1  );
    
    # setting attributes for the chart
    $chart1->add_series
    ( 
     name   => 'myname',
     categories => '=Sheet1!$A$2:$A$9', 
     values => '=Sheet1!$B$2:$B$9',
    );
    $chart1->set_title( name => 'This is the heading of the chart' );
    $chart1->set_x_axis( name => 'This is the x axis' );
    $chart1->set_y_axis( name => 'This is the y axis' );
    
    # Insert the chart into the main worksheet.
    $worksheet->insert_chart( 'E2', $chart1 );
    
    #open up libreoffice with chart
    system("libreoffice test1.xls");
    You must call compatibility_mode() before calling add_worksheet()
    I tried this, (in the script above) Have I added that in the correct place. The prompt still keeps coming.

    Although it is said Calc does not need this mode, I would test it and see if it clears your problem.
    I couldn't follow that.

    I wasn't going to reply at first, because I didn't see this as a perl problem.
    Thanks a lot for looking into it. It might not be an error as such, maybe you could tell me which format I could use to open the excel sheet, once you're able to reproduce this.

    Thanks
  6. #4
  7. !~ /m$/
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    May 2004
    Location
    Reno, NV
    Posts
    4,259
    Rep Power
    1810
    Your script created the spreadsheet just fine, but one definite problem, and another possible source of difficulty.

    First thing is you have to explicitly close the worksheet, because if you don't do that, it may not even be saved to disc when you are trying to open it in the system command.

    That change may fix everything.

    The possible problem is in the way you call libreoffice to open the file. If it works on your command line, that may already be set up. On Mac OS X, I had to add the libreoffice directory to my path, and then I could use the following:

    Code:
    scalc -o test1.xls
    So, my changes are in red at the end of the script:

    Code:
    #!/usr/bin/env perl
    use strict;
    use warnings;
    
    use Spreadsheet::WriteExcel;
    
    my $workbook  = Spreadsheet::WriteExcel->new( 'test1.xls' );
    $workbook->compatibility_mode();
    my $worksheet = $workbook->add_worksheet();
    my $bold      = $workbook->add_format( bold => 1 );
    
    # Add the worksheet data that the charts will refer to.
    my $headings = [ 'Category', 'Values 1', 'Values 2' ];
    my $data = [
        [ 'one', 'two', 'three', 'four', 'five', 'six','seven','eight','nine' ],
        [41, 249, 75, 6, 149, 29, 43 ,56 ,78],
    ];
    
    $worksheet->write( 'A1', $headings, $bold );
    $worksheet->write( 'A2', $data );
    
    #making the chart instance
    my $chart1 = $workbook->add_chart( type => 'column',embedded => 1  );
    
    # setting attributes for the chart
    $chart1->add_series(
    	name   => 'myname',
    	categories => '=Sheet1!$A$2:$A$9', 
    	values => '=Sheet1!$B$2:$B$9',
    );
    $chart1->set_title( name => 'This is the heading of the chart' );
    $chart1->set_x_axis( name => 'This is the x axis' );
    $chart1->set_y_axis( name => 'This is the y axis' );
    
    # Insert the chart into the main worksheet.
    $worksheet->insert_chart( 'E2', $chart1 );
    $workbook->close();
    
    #open up libreoffice with chart
    system("scalc -o test1.xls");
    
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    39
    Rep Power
    2
    Originally Posted by keath
    First thing is you have to explicitly close the worksheet, because if you don't do that, it may not even be saved to disc when you are trying to open it in the system command.

    That change may fix everything.
    Thanks a lot keath, sure did

IMN logo majestic logo threadwatch logo seochat tools logo