Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery

Spreadsheet::WriteExcel and displaying a specific column in split_panes

by magamo (Initiate)
on Jul 17, 2012 at 16:14 UTC ( #982275=perlquestion: print w/ replies, xml ) Need Help??
magamo has asked for the wisdom of the Perl Monks concerning the following question:

I'm trying to use a combination of Spreadsheet::ParseExcel::SaveParser and Spreadsheet::WriteExcel to modify an existing spreadsheet. I have all my modification routines so far working perfectly, including keeping color settings from the original sheet. However one thing that the original sheet has that I'd like to at least attempt to preserve in a meaningful manner is split panes.

After calling the SaveAs method, I turn around and reopen a WriteExcel worksheet method, and have it define the split panes, and found sizing that works for what I want, and have it set the right hand scroll area to a default that is meaningful for my purposes. What I cannot seem to figure out is how to set the left side of the split pane to by default display a specific column.

Right now it simply displays column A, starting at whatever row I have specified later. I'd like to be able to define that (column S for example, though ultimately I'd like to select the column programatically using routines I've already developed)

$swe_wbook=$wbook->SaveAs("$ssheet"); $worksheet = $swe_wbook->sheets($wsheetnum); $worksheet->split_panes(12.75,10,1,$namedcol);

$wsheetnum is the previously discovered index number of the worksheet we're working on, obviously. $namedcol is the column number of the first column to be displayed in the right hand side. I'd like $namedcol -1 to be the column being displayed to the left of the split. If there is further information I can provide, I'll do my best.

Comment on Spreadsheet::WriteExcel and displaying a specific column in split_panes
Download Code
Re: Spreadsheet::WriteExcel and displaying a specific column in split_panes
by jmcnamara (Monsignor) on Jul 17, 2012 at 23:54 UTC

    Unfortunately, there isn't anything in the API to allow that degree of control over the row/column location in panes (apart from the bottom right pane).


      I was afraid of that. Irksome, considering the fact that Excel allows for it when saving a file

Re: Spreadsheet::WriteExcel and displaying a specific column in split_panes
by strat (Canon) on Jul 18, 2012 at 12:07 UTC


    maybe ExcelPerl will help you (or at least some of the codes inside). It is a kind of sed/awk-like program (or like perl -ane) that lets you change values and formats of an existing excel file on the fly...

    It isn't based on the SpreadSheet::*Excel modules but on Win32::OLE... so it will only run under Win and require an installed Excel version...

    You can get it from

    Best regards,
    perl -e "s>>*F>e=>y)\*martinF)stronat)=>print,print v8."

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://982275]
Front-paged by Arunbear
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (8)
As of 2014-07-31 00:03 GMT
Find Nodes?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:

    Results (241 votes), past polls