Add function to setPrintRange

[topialla]

Is your feature request related to a problem? Please describe. I would love to set the print range of a sheet programmatically, as this has often to be set manually.

Describe the solution you'd like Add a function setPrintRange similar to createNamedRegion(). This will give a lot of value also to pageSetup().

Setting it to "none" would remove the print area. The function should also detect a set print area, so another range can be added, and a switch to either add to or replace an existing print area would likely also be required (not for me, but for the general usage, I guess).

-->I described a possible solution to the end of this post.

Describe alternatives you've considered None really, except for setting this manually.

Additional context I found out, that setting a print range creates a named range in workbook.xml called "_xlnm.Print_Area"

    <sheets>
        <sheet name="Sheet 1"
               sheetId="1"
               r:id="rId1"/>
    </sheets>
    <definedNames>
        <definedName name="_xlnm.Print_Area"
                     localSheetId="0">Tabelle1!$D$4:$F$14</definedName> <!-- area where I set the print range when playing around/testing -->
    </definedNames>

So I tried to create a respective Area, (which works partially) NB: the pageSetup was done, since I was unsure, if this initialises something to help with printing (it seems not to).

library(openxlsx)
wb <- createWorkbook()
addWorksheet(wb, "Sheet 1")
writeData(wb, sheet = 1, x = iris, startCol = 1, startRow = 1)

createNamedRegion(
  wb = wb,
  sheet = "Sheet 1",
  name = "_xlnm.Print_Area",
  rows = 1:10,
  cols = 1:3
)
pageSetup(wb, sheet = 1, orientation = "landscape", scale = 50)

saveWorkbook(wb,"testprintrange.xlsx",overwrite = T)

I wrote "partially" as the generic range name for the print range is found by Excel 2016, I only have a german version available, sorry, for that.

I then went on further, noting that saving a file in excel changes several things to the xml etc, that are not initially set by saveWorkbook, so

• I first saved the file as it was written out by saveWorkbook
• and then added the print range manually by selecting the range

I noted the following changes invoked by Excel 2016.

[Content_Types].xml

testprintrange/[Content_Types].xml is ameliorated with

<Default Extension="bin" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.printerSettings"/>

workbook.xml

testprintrange/xl/workbook.xml already contained

    <definedNames>

        <definedName name="_xlnm.Print_Area">'Sheet 1'!$A$1:$C$10</definedName>
    </definedNames>

but is ameliorated by setting the print range manually with

    <definedNames>
+       <definedName name="_xlnm.Print_Area" localSheetId="0">'Sheet 1'!$A$1:$C$10</definedName> <!-- this is added -->
        <definedName name="_xlnm.Print_Area">'Sheet 1'!$A$1:$C$10</definedName>
    </definedNames>

sheet1.xml

testprintrange/xl/worksheets/sheet1.xml is also changed slightly:

original file

<pageSetup paperSize="9" scale="50" fitToWidth="0" fitToHeight="0" orientation="landscape" horizontalDpi="300" verticalDpi="300"/>

after setting the print range

    <pageSetup paperSize="9" scale="50" fitToWidth="0" fitToHeight="0" orientation="landscape" horizontalDpi="300" verticalDpi="300" r:id="rId1"/>

So a reference seems to be added to the page setup.

Solution

Upon further playing around, I found out, that adding localSheetId="0" to the <definedNames> in workbook.xml is sufficient to make it work. (see above my remarks regarding workbook.xml).

So I would propose a function that calls createNamedRegion() and sets the name to "_xlnm.Print_Area", but also localSheetId="0" (the number is the sheet index seems to be the value of sheetId –1

[github-actions[bot]]

This issue is stale because it has been open 365 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[github-actions[bot]]

This issue was closed because it has been stalled for 7 days with no activity.