none
XLSX - which sheetView is used? RRS feed

  • Question

  • ISO/IEC 29500-1

    18.3.1.89 - sheetViews does not describe which view is used. I have a worksheet where sheet1.xml has:

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
    	<sheetPr>
    		<pageSetUpPr fitToPage="1"/>
    	</sheetPr>
    	<dimension ref="A1:O58"/>
    	<sheetViews>
    		<sheetView tabSelected="1" zoomScale="85" workbookViewId="0">
    			<pane xSplit="1" ySplit="7" topLeftCell="P42" activePane="bottomRight" state="frozen"/>
    			<selection pane="topRight" activeCell="B1" sqref="B1"/>
    			<selection pane="bottomLeft" activeCell="A8" sqref="A8"/>
    			<selection pane="bottomRight" activeCell="R59" sqref="R59"/>
    		</sheetView>
    		<sheetView zoomScale="80" workbookViewId="1">
    			<pane xSplit="1" ySplit="7" topLeftCell="F8" activePane="bottomRight" state="frozen"/>
    			<selection pane="topRight" activeCell="B1" sqref="B1"/>
    			<selection pane="bottomLeft" activeCell="A8" sqref="A8"/>
    			<selection pane="bottomRight" activeCell="A30" sqref="A30:H46"/>
    		</sheetView>
    	</sheetViews>
    

    The first sheetView with tabSelected="1" has a topLeftCell (in pane) of P42. The second one has F8. But when I open the file in Excel, it is displaying with F8 as the top left cell.

    So how do I know that it is using that second sheetView as the default? From trying a couple of different things, it seems like it's the last one that is used????

    thanks - dave


    Who will win The International Collegiate Programming Championships?

    Saturday, September 1, 2012 12:06 AM

Answers

  • Hi Dave,

    You have two questions.  To answer to the first one, in my previous posting I quoted:
    ISO/IEC 29500-1:2008 §18.3.1.87 sheetView
    workbookViewId: Zero-based index of this workbook view, pointing to a workbookView element in the bookViews collection.
    The workbookView element is a child of the bookViews and can be found in the workbook.xml part.  Using your sample file, Tropic_test.xlsx, \xl\workbook.xml, the relevant section (the comments were added by me):
      <bookViews>
        <workbookView visibility="hidden" xWindow="9795" yWindow="-15" windowWidth="3315" windowHeight="7200" tabRatio="603"/> <!-- being the first has index “0” -->
        <workbookView xWindow="-15" yWindow="-15" windowWidth="15420" windowHeight="4140" activeTab="1"/><!-- being the second has index “1” -->
      </bookViews>
    As if the <bookViews> element contains a one dimensional array, the first workbookView has index “0”, the second “1”, etc.

    In your second question you want to see some change after unhiding sheet1. Whether a <sheet state=”hidden”> is set does not affect which cell is displayed in the top left corner of the active pane. In my posting, September 19, 2012 5:51 PM, I explained why you see F8 in the top left corner of the active pane.

    I might have misunderstood your questions.  Please send an email to dochelp at Microsoft dot com, referencing this thread’s URL and my name so that we can discuss this offline.

    Thanks, Vilmos

    • Marked as answer by DavidThi808 Wednesday, September 26, 2012 3:12 PM
    Friday, September 21, 2012 8:37 PM
  • Hi Dave,

    You are right, there is nothing which controls how many workbook can be visible. The visible workbooks reside in their windows. In your sample spreadsheet there are two <workbookView>s; after unhiding the first one we might have this view of the resulting windows:

    Thanks, Vilmos

    • Marked as answer by DavidThi808 Wednesday, September 26, 2012 3:32 PM
    Tuesday, September 25, 2012 8:21 PM

All replies

  • Hi Dave,

    Thank you for your question. A member of the protocol documentation team will respond to you soon.

    Regards,
    Vilmos Foltenyi - MSFT

    Saturday, September 1, 2012 8:32 AM
  • Hi Dave,

    I am the engineer who will be working with you on this issue. I am currently researching the problem and will provide you with an update soon.

    Regards,
    Vilmos Foltenyi - MSFT

    Sunday, September 2, 2012 8:29 PM
  • Hi Vilmos;

    Any luck getting an answer yet?

    thanks - dave


    Who will win The International Collegiate Programming Championships?

    Tuesday, September 11, 2012 3:02 PM
  • Hi Dave,

    I am still looking into this issue. I hope to have more information for you soon. Your patience is greatly appreciated.

    Thanks, Vilmos

    Wednesday, September 12, 2012 7:02 PM
  • Hi Dave,

    Could you post or send the whole file? If you send, please send it to  “dochelp (at) microsoft (dot) com”  and indicate that it is for me.
    I would like to see the other parts, too. E.g. § 18.3.1.87 tabSelected says “In case of a conflict, the Start Part
    setting wins and sets the active sheet tab”.
    Please describe how the spreadsheet was generated, e.g. Excel (which version) and this and this was manually modified.
    Please make the illustrating spreadsheet minimal and without confidential information.

    Thanks, Vilmos

    Monday, September 17, 2012 7:20 PM
  • Hi Dave,

    I got the file Tropic_test.xlsx. You wrote:
    This file was created in Excel 2010. We’re using our program to read it – we did not change the file in any way after Excel saved it.
    You have to have another workbook open in Excel when you open the file I sent you. Otherwise Excel silently does nothing. No idea why.

    I was unable to reproduce your observation; the file opens normally regardless whether another workbook is opened or not. When it opens workbook:1 is hidden, which correspond to workbook.xml part setting; I was able to Unhide it without any problem.
    Also I can switch between sheet1 and sheet2, see the calculations, e.g. on sheet2  E44 = +E33-E42.

    In your original posting you wrote: In sheet1.xml 'The first sheetView with tabSelected="1" has a topLeftCell (in pane) of P42. The second one has F8. But when I open the file in Excel, it is displaying with F8 as the top left cell.'
    When the file opened only Topic_test.xlsx:2 can be seen and F8 is the top left cell of the bottom right pane, which correspond to
        <sheetView zoomScale="80" workbookViewId="1">
          <pane xSplit="1" ySplit="7" topLeftCell="F8" activePane="bottomRight" state="frozen"/>
    in sheet1.xml, see picture and ISO/IEC 29500:2008 - 1 section 18.3.1.66.

    After unhiding Topic_test.xlsx:1 and extending it so the panes can be visible P42 is the top left cell of the bottom right pane, which correspond to
        <sheetView tabSelected="1" zoomScale="85" workbookViewId="0">
          <pane xSplit="1" ySplit="7" topLeftCell="P42" activePane="bottomRight" state="frozen"/>
    in sheet1.xml, see picture.

    Basically I cannot reproduce your problems, the sent Tropic_test.xlsx spreadsheet file works for me.

    Thanks, Vilmos

    Wednesday, September 19, 2012 5:51 PM
  • Hi Vilmos;

    Thank you for your answer. What I don't understand is how it decides which sheetView to use for that worksheet. How does unhiding the other worksheet impact which view to use in this worksheet?

    thanks - dave


    Who will win The International Collegiate Programming Championships?

    Wednesday, September 19, 2012 7:13 PM
  • Hi Dave,

    Whether a <workbookView visibility="hidden"> or a <sheet state="hidden"> are set do nothing to determine which <sheetView> is used. All the views of one sheet are collected in one part, e.g. in your sample file, Tropic_test.xlsx, \xl\worksheets\sheet2.xml. The workbookViewId attribute of the sheetView tag shows to which workbookView they belong, see ISO/IEC 29500-1:2008 §18.3.1.87 sheetView
    workbookViewId: Zero-based index of this workbook view, pointing to a workbookView element in the bookViews collection.

    Here's the relevant part from your sheet2.xml:
      <sheetViews>
        <sheetView zoomScale="75" workbookViewId="0">
          <pane xSplit="1" ySplit="3" topLeftCell="B4" activePane="bottomRight" state="frozen"/>
          <selection pane="topRight" activeCell="B1" sqref="B1"/>
          <selection pane="bottomLeft" activeCell="A4" sqref="A4"/>
          <selection pane="bottomRight" activeCell="AG18" sqref="AG18"/>
        </sheetView>
        <sheetView zoomScale="85" workbookViewId="1">
          <pane xSplit="1" ySplit="5" topLeftCell="B35" activePane="bottomRight" state="frozen"/>
          <selection pane="topRight" activeCell="B1" sqref="B1"/>
          <selection pane="bottomLeft" activeCell="A6" sqref="A6"/>
          <selection pane="bottomRight" activeCell="A35" sqref="A35"/>
        </sheetView>
      </sheetViews>

    Thanks, Vilmos

    Thursday, September 20, 2012 4:58 PM
  • Hi Vilmos;

    There's clearly something I'm not understanding on this. What is it that determines if it uses the view for workbookViewId=0 vs workbookViewId=1? Can you tell me what specific setting in the XLSX file identifies which is used?

    On thing that might be an issue - I'm not seeing a change when I click Unhide in the View tab - sheet 1 still has F8 as the upper left corner of the unfrozen part.

    thanks - dave


    Who will win The International Collegiate Programming Championships?

    Thursday, September 20, 2012 8:13 PM
  • Hi Dave,

    You have two questions.  To answer to the first one, in my previous posting I quoted:
    ISO/IEC 29500-1:2008 §18.3.1.87 sheetView
    workbookViewId: Zero-based index of this workbook view, pointing to a workbookView element in the bookViews collection.
    The workbookView element is a child of the bookViews and can be found in the workbook.xml part.  Using your sample file, Tropic_test.xlsx, \xl\workbook.xml, the relevant section (the comments were added by me):
      <bookViews>
        <workbookView visibility="hidden" xWindow="9795" yWindow="-15" windowWidth="3315" windowHeight="7200" tabRatio="603"/> <!-- being the first has index “0” -->
        <workbookView xWindow="-15" yWindow="-15" windowWidth="15420" windowHeight="4140" activeTab="1"/><!-- being the second has index “1” -->
      </bookViews>
    As if the <bookViews> element contains a one dimensional array, the first workbookView has index “0”, the second “1”, etc.

    In your second question you want to see some change after unhiding sheet1. Whether a <sheet state=”hidden”> is set does not affect which cell is displayed in the top left corner of the active pane. In my posting, September 19, 2012 5:51 PM, I explained why you see F8 in the top left corner of the active pane.

    I might have misunderstood your questions.  Please send an email to dochelp at Microsoft dot com, referencing this thread’s URL and my name so that we can discuss this offline.

    Thanks, Vilmos

    • Marked as answer by DavidThi808 Wednesday, September 26, 2012 3:12 PM
    Friday, September 21, 2012 8:37 PM
  • Vilmos - thank you. That clicked for me.

    I think what I missed was I didn't think the view setting in workbook would set it for all sheets. Makes sense now that I see it, but that didn't click for me before.

    Thanks for sticking with this to explain it for me.

    thanks - dave


    Who will win The International Collegiate Programming Championships?

    Monday, September 24, 2012 8:53 PM
  • ps - One final question - what if multiple workbookView elements are not hidden. Reading 18.2.30 there is nothing in it that says only one can be visible.

    ??? - thanks - dave


    Who will win The International Collegiate Programming Championships?

    Monday, September 24, 2012 8:56 PM
  • Hi Dave,

    You are right, there is nothing which controls how many workbook can be visible. The visible workbooks reside in their windows. In your sample spreadsheet there are two <workbookView>s; after unhiding the first one we might have this view of the resulting windows:

    Thanks, Vilmos

    • Marked as answer by DavidThi808 Wednesday, September 26, 2012 3:32 PM
    Tuesday, September 25, 2012 8:21 PM