none
using Range.Setvalue with xlRangeValueXMLSpreadsheet does not set column & row widths or heights

    Question

  • Hi Office experts,

     

    I have a question regarding the use of xlRangeValueXMLSpreadsheet.

    I use it to extract the sheet in a XML format like so:

     

    xmlDef = sheet.UsedRange.get_Value(Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueXMLSpreadsheet).ToString();

    I get the sheet information in XML format and when I save the XML as a file to test it when I open it with Excel, it is 100% correct.

    when I use

    sheet.Range[X,Y].set_Value(Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueXMLSpreadsheet, xmlDef);

    it formats the sheet correctly but without setting the columns' width or the row's height.

    Any ideas?

    When I look at the XML I can see that it does export this correctly, here is an example XML:

    ----------------------------------------------------------------------------------------------------------------------------

    <?xml version="1.0"?>
    <?mso-application progid="Excel.Sheet"?>
    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
     xmlns:o="urn:schemas-microsoft-com:office:office"
     xmlns:x="urn:schemas-microsoft-com:office:excel"
     xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
     xmlns:html="http://www.w3.org/TR/REC-html40">
     <Styles>
      <Style ss:ID="Default" ss:Name="Normal">
       <Alignment ss:Vertical="Bottom"/>
       <Borders/>
       <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
       <Interior/>
       <NumberFormat/>
       <Protection/>
      </Style>
     </Styles>
     <Names>
      <NamedRange ss:Name="_cf5baf26_bf2e_4002_af50_bc3b41862249"
       ss:RefersTo="=heightWidth!R10C3"/>
      <NamedRange ss:Name="_d6dfe1b0_1d0f_4361_a03e_bb13903345f5"
       ss:RefersTo="=heightWidth!R10C4:R10C9"/>
     </Names>
     <Worksheet ss:Name="heightWidth">
      <Table ss:ExpandedColumnCount="9" ss:ExpandedRowCount="10"
       ss:DefaultRowHeight="15">
       <Column ss:Index="9" ss:AutoFitWidth="0" ss:Width="269.25"/>
       <Row>
        <Cell><Data ss:Type="String"> </Data></Cell>
       </Row>
       <Row ss:Index="10" ss:AutoFitHeight="0" ss:Height="254.25">
        <Cell ss:Index="3"><Data ss:Type="String"> </Data><NamedCell
          ss:Name="_cf5baf26_bf2e_4002_af50_bc3b41862249"/></Cell>
        <Cell ss:MergeAcross="5"><Data ss:Type="String"> </Data><NamedCell
          ss:Name="_d6dfe1b0_1d0f_4361_a03e_bb13903345f5"/></Cell>
       </Row>
      </Table>
     </Worksheet>
    </Workbook>

    --------------------------------------------------------------------------------------------------------------------------------------

     

    any help would be greatly appreciated,

    Regards

     

     

     

     

     

     

     

     

    • Moved by Cindy Meister MVPMVP Thursday, February 10, 2011 6:39 PM Excel-specific question (From:Visual Studio Tools for Office)
    Thursday, February 10, 2011 2:03 PM

All replies

  • Hi Leon,

    Thank you for posting and we are glad to help with you.

    After reading your post, I tested on my computer. As a result, I reproduced your senario, however, I think it is by design. In the MSDN article: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlrangevaluedatatype.aspx , it says that xlRangeValueXMLSpreadsheet can Returns the values, formatting, formulas and names of the specified Range object in the XML Spreadsheet format. It can't resize the column width or row height. If you want to set the row height or column with, you should do like this:

          rng.RowHeight = ws.get_Range("B1").RowHeight;
          rng.ColumnWidth = ws.get_Range("B1").ColumnWidth;
    
    
    

    I hope it can help you and feel free to follow up after you tried.

    Best Regards,

     


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by Bruce Song Friday, February 25, 2011 2:45 AM
    Tuesday, February 15, 2011 8:02 AM
  • Hi Bruce,

    Thanks so much for the reply.

    Yes, I did see that article, think I was in denial ;-)

    Any idea why this was done like this? If you open the XML up with excel, it does work though. But not through code.

    Okay, I'll use a regEx to find the settings out of the XML and set the widths and heights manually.

    Thanks for looking into this,

     

    Regards,

    Leon.

     

    Thursday, February 17, 2011 8:07 AM