none
Get Values/Formatting/Range from XSLT XML file RRS feed

  • Question

  • Hi,

    I create an xslt xml file by extracting data from an Excel workbook. For example:

    <?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>
        <Style ss:ID="s16">
          <Borders>
            <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2" />
            <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2" />
            <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="2" />
          </Borders>
        </Style>
        <Style ss:ID="s17">
          <Borders>
            <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2" />
            <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2" />
          </Borders>
        </Style>
        <Style ss:ID="s18">
          <Borders>
            <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2" />
            <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2" />
            <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2" />
          </Borders>
          <Interior ss:Color="#16365C" ss:Pattern="Solid" />
        </Style>
      </Styles>
      <Worksheet ss:Name="First Worksheet">
        <Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="9" ss:DefaultRowHeight="15">
          <Column ss:Index="3" ss:Width="54" />
          <Row ss:Height="15.75" />
          <Row>
            <Cell ss:Index="3" ss:StyleID="s16">
              <Data ss:Type="Number">12345</Data>
            </Cell>
          </Row>
          <Row>
            <Cell ss:Index="3" ss:StyleID="s17" />
          </Row>
          <Row>
            <Cell ss:Index="3" ss:StyleID="s17">
              <Data ss:Type="String">Some Text</Data>
            </Cell>
          </Row>
          <Row>
            <Cell ss:Index="3" ss:StyleID="s17" />
          </Row>
          <Row ss:Height="15.75">
            <Cell ss:Index="3" ss:StyleID="s18" />
          </Row>
        </Table>
      </Worksheet>
    </Workbook>

    Now I want to use this to create for example an Excel.Range object containing all information (worksheet name is not needed), with borders, cell colours, values etc to copy into a new worksheet in a new or existing workbook. Or another way to copy and paste all this information. I would rather not open de xml file in Excel and then get the range.

    I'm using c#. Is this possible? Thanks in advance


    • Edited by MindCraft Friday, November 1, 2013 12:18 AM
    Friday, November 1, 2013 12:15 AM

Answers

All replies

  • What do you mean by "copy and paste all this information into a new worksheet"?

    Do you mean copy every data element into a cell and set the style base on the style element?

    If it's true, it is possible.

    Firstly, read xml file:

    http://support.microsoft.com/kb/307548

    Secondly, using Object model to insert specified xml elements into cells

    Thirdly, set the style of every cell based on xml Attributes.

    For creating a new Excel sheet:

    http://msdn.microsoft.com/en-us/library/ms173186(v=vs.80).aspx

    Friday, November 1, 2013 6:36 AM
  • Thanks for your reply!

    Yes, this is what I mean. I'm sorry if I choose to explain it in a certain way, but English is not my main language. I want all the data (values, numbers, strings) and styles (formatting, borders, colours) to be inserted exactly how it's in the xml, in a new OR existing worksheet at a certain range (this does not have to be the same range as in the xml file).

    I do know how to create a new worksheet. I'm using c# and VSTO to manipulate data and styles on worksheets. But my knowledge of xml spreadsheets is practically none. Thank you for the link how to read the xml file, this helps a lot to start reading the attributes. But, how do I use Object model to insert the elements into the cells, and set the styles? Do you have some links or information how I can achieve this?

    Friday, November 1, 2013 7:49 AM
  • Ok let's put it this way. The one thing that I'm not sure to handle is the ss:ID prefix. for example

    <Style ss:ID="s16">

    This should hold the information which range (cells) it is, but I cannot seem to figure out how. Anyone?

    Saturday, November 2, 2013 6:02 AM
  • What's the meaning of s16?

    Does it apply to Range("s16") in a workbook?

    Monday, November 4, 2013 1:44 AM