#1
  1. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,497
    Rep Power
    594

    Set Background Color Using Spreadsheet::SaveParser


    I have a script that is working fine but I need to add one more touch. Given a certain cell content, I need to make the background color of that cell yellow. I have spent hours trying to use various things none of which work. Can someone help?
    Code:
    my $format=$sheet->get_cell($i,$j)->get_format();
    $format->set_color('yellow');
    All I get is a null pointer error on the 2nd line.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2010
    Posts
    1
    Rep Power
    0
    What perl module are you using?
    Have you looked at the perldoc for the module you are using?
  4. #3
  5. 'fie' on me, allege-dly
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2003
    Location
    in da kitchen ...
    Posts
    12,889
    Rep Power
    6444
    Maybe you need to pass a hex (RGB) value ?
    --Ax
    without exception, there is no rule ...
    Handmade Irish Jewellery
    Targeted Advertising Cookie Optout (TACO) extension for Firefox
    The great thing about Object Oriented code is that it can make small, simple problems look like large, complex ones


    09 F9 11 02
    9D 74 E3 5B
    D8 41 56 C5
    63 56 88 C0
    Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems.
    -- Jamie Zawinski
    Detavil - the devil is in the detail, allegedly, and I use the term advisedly, allegedly ... oh, no, wait I did ...
    BIT COINS ANYONE
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,497
    Rep Power
    594
    Thanks for the replies. I'm using Spreadsheet::SaveParser as the subject field says. I don't think a hex RGB is the issue because it is $format that is null.

    In my research I have hit a catch-22 which means I am likely missing something. I was under the impression that Spreadsheet::SaveParser was a module that combined Spreadsheet::ParseExcel and Spreadsheet:WriteExcel. However, the implication is that get_format() is not defined in SaveParser (why I don't get an error on that is another question) nor are any of the set_xxx methods. So that means I need to use ParseExcel and WriteExcel instead. The catch-22 is that I need to use SaveParser to modify an existing spreadsheet but need WriteExcel to set colors but it can only create new spreadsheets which means I have to use SaveParser, ad nauseum. This seems like a giant hole in the functionality of these modules which seems unlikely. More likely is something I am not understanding. I guess I can use a brute force method by using WriteExcel to create a new spreadsheet from the old, then delete the old and rename the new but that should be unnecessary.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2012
    Posts
    835
    Rep Power
    496
    Originally Posted by gw1500se
    I guess I can use a brute force method by using WriteExcel to create a new spreadsheet from the old, then delete the old and rename the new but that should be unnecessary.
    I think that is probably what you have to do.

    What I am saying here may be outdated, but last time I used the Excel Perl modules, it was not possible to read from a workbook et write to the same workbook at the same time. In other woird, you could not edit an existing book.

    As far as I know, the reason for it is that Microsoft is protecting its spreadsheets with CRCs or similar verification codes or keys and that the functions used for that is proprietary information. So that if you were to edit an existing Excel file and change its content, Excel would not be able to read it again.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,497
    Rep Power
    594
    Apparently that is not completely true any more. I am indeed able to use SaveParser to edit an existing spreadsheet. As I said originally, I have it mostly working. The piece that is missing is setting cell formats.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.

IMN logo majestic logo threadwatch logo seochat tools logo