Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Trouble converting XML to Excel

Status
Not open for further replies.
nwruiz

nwruiz

Programmer
Jun 22, 2005
60
US
Hello,

I am trying to convert some XML to Excel using the following XSLT:

Code: 
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
	xmlns="urn:schemas-microsoft-com:office:spreadsheet"
	xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform"[/URL]
		xmlns:msxsl="urn:schemas-microsoft-com:xslt"
		xmlns:user="urn:my-scripts"
		xmlns:o="urn:schemas-microsoft-com:office:office"
		xmlns:x="urn:schemas-microsoft-com:office:excel"
		xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" >
		
	<xsl:template match="/">
	<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="[URL unfurl="true"]http://www.w3.org/TR/REC-html40"[/URL] >
		
		<Styles>
			<Style ss:ID="Default" ss:Name="Normal">
				<Alignment ss:Vertical="Bottom"/>
				<Borders/>
				<Font/>
				<Interior/>
				<NumberFormat/>
				<Protection/>
			</Style>
			<Style ss:ID="HeaderText">
				<Font ss:Bold="1"/>
				<Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
			</Style>
			<Style ss:ID="HeaderBody">
				<Font ss:Bold="1"/>
				<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
			</Style>
			<Style ss:ID="BodyText">
				<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
			</Style>
			<Style ss:ID="BodyDate">
				<Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
			</Style>
			<Style ss:ID="BodyNum">
				<Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
			</Style>
		</Styles>
		
		<Worksheet>
			<xsl:attribute name="ss:Name">Detail</xsl:attribute>
			<Table ss:ExpandedColumnCount="12" ss:ExpandedRowCount="100" x:FullRows="1" x:FullColumns="1">
				<!-- Header -->
				<Row>
					<Cell ss:StyleID="HeaderText"><Data ss:Type="String">EDC Number:</Data></Cell>
					<Cell ss:StyleID="BodyText"><Data ss:Type="String">
						<xsl:value-of select="/IpbRoot/IndexPricingBreakdown/EdcAccount"/>
					</Data></Cell>
				</Row>
				<Row>
					<Cell ss:StyleID="HeaderText"><Data ss:Type="String">Bill Account Number:</Data></Cell>
					<Cell ss:StyleID="BodyText"><Data ss:Type="String">
						<xsl:value-of select="/IpbRoot/IndexPricingBreakdown/KyBaLeadZeros"/>
					</Data></Cell>
				</Row>
				<Row>
					<Cell ss:StyleID="HeaderText"><Data ss:Type="String">Enrollment Number:</Data></Cell>
					<Cell ss:StyleID="BodyText"><Data ss:Type="String">
						<xsl:value-of select="/IpbRoot/IndexPricingBreakdown/KyEnroll"/>
					</Data></Cell>
				</Row>
				<Row>
					<Cell ss:StyleID="HeaderText"><Data ss:Type="String">Bill Date:</Data></Cell>
					<Cell ss:StyleID="BodyText"><Data ss:Type="String">
						<xsl:value-of select="/IpbRoot/IndexPricingBreakdown/PostDate"/>
					</Data></Cell>
				</Row>
				<!-- Skip 2 rows -->
				<Row></Row>
				<Row></Row>
				<!-- Body header -->
				<Row>
					<Cell ss:StyleID="HeaderBody"><Data ss:Type="String">SR SEQ No</Data></Cell>
					<Cell ss:StyleID="HeaderBody"><Data ss:Type="String">Date</Data></Cell>
					<Cell ss:StyleID="HeaderBody"><Data ss:Type="String">Time</Data></Cell>
					<Cell ss:StyleID="HeaderBody"><Data ss:Type="String">Metered KWH</Data></Cell>
					<Cell ss:StyleID="HeaderBody"><Data ss:Type="String">Actual KWH</Data></Cell>
					<Cell ss:StyleID="HeaderBody"><Data ss:Type="String">Peak</Data></Cell>
					<Cell ss:StyleID="HeaderBody"><Data ss:Type="String">Index Price</Data></Cell>
					<Cell ss:StyleID="HeaderBody"><Data ss:Type="String">Price Adder</Data></Cell>
					<Cell ss:StyleID="HeaderBody"><Data ss:Type="String">Pricing Zone</Data></Cell>
					<Cell ss:StyleID="HeaderBody"><Data ss:Type="String">Index Only Energy Charge</Data></Cell>
					<Cell ss:StyleID="HeaderBody"><Data ss:Type="String">Index+Adder Energy Charge</Data></Cell>
					<Cell ss:StyleID="HeaderBody"><Data ss:Type="String">Post Date</Data></Cell>
				</Row>
			</Table>
		</Worksheet>
	</Workbook>
	</xsl:template>		
</xsl:stylesheet>

And here is a DTD that gives you the general idea of my XML document's format:
Code: 
<!-- Ipb.dtd -->
<!-- DTD document for Index Pricing Breakdown XML files -->

<!ELEMENT IpbRoot ( IndexPricingBreakdown )>

<!ELEMENT IndexPricingBreakdown ( TranPtr, KySource, EdcAccount, PostDate, KyRowSeqTrans, PtrHistBilling, SrSeqNo, 
	KyEnroll, KyBaLeadZeros, ClientAcctNo, DateMin, DateMax, AggregatedValues, Details )>
<!ATTLIST IndexPricingBreakdown extracted-from CDATA (Access | Oracle) "Oracle">
<!ATTLIST IndexPricingBreakdown value-count CDATA #REQUIRED>
<!ATTLIST IndexPricingBreakdown interval-length CDATA #REQUIRED>

<!-- Columns in str_idxpricngbrkdn_hdr -->
<!ELEMENT TranPtr ( #PCDATA )>
<!ELEMENT KySource ( #PCDATA )>
<!ELEMENT EdcAccount (#PCDATA)>
<!ELEMENT PostDate ( #PCDATA )>
<!ELEMENT KyRowSeqTrans ( #PCDATA )>
<!ELEMENT PtrHistBilling ( #PCDATA )>
<!ELEMENT SrSeqNo ( #PCDATA )>
<!ELEMENT KyEnroll ( #PCDATA )>
<!ELEMENT KyBaLeadZeros ( #PCDATA )>
<!ELEMENT ClientAcctNo ( #PCDATA )>
<!ELEMENT DateMin ( #PCDATA )>
<!ELEMENT DateMax ( #PCDATA )>

<!ELEMENT AggregatedValues ( MeteredKwhSum, ActualKwhSum, IndexPriceAvg, IndexOnlySum, IndexPlusAdderSum )
<!ELEMENT MeteredKwhSum ( #PCDATA )>
<!ELEMENT ActualKwhSum ( #PCDATA )>
<!ELEMENT IndexPriceAvg ( #PCDATA )>
<!ELEMENT IndexOnlySum ( #PCDATA )>
<!ELEMENT IndexPlusAdderSum ( #PCDATA )>

<!-- Details -->
<!ELEMENT Details ( Interval+ )>

<!-- IntervalDetails -->
<!ELEMENT Interval ( MeteredKwh, ActualKwh, Peak, IndexPrice, PriceAdder, PricingZone, IndexOnlyCharge,
	IndexPlusAdderCharge ) >
<!ATTLIST Interval dt CDATA #REQUIRED>
<!ATTLIST Interval hhmm CDATA #REQUIRED>
<!ELEMENT MeteredKwh ( #PCDATA )>
<!ELEMENT ActualKwh ( #PCDATA )>
<!ELEMENT Peak ( #PCDATA)>
<!ELEMENT IndexPrice ( #PCDATA )>
<!ELEMENT PriceAdder ( #PCDATA )>
<!ELEMENT PricingZone ( #PCDATA )>
<!ELEMENT IndexOnlyCharge ( #PCDATA )>
<!ELEMENT IndexPlusAdderCharge ( #PCDATA )>

My real problem is occurring within my XSLT: all <Cell> tags are being written to R1C1 (or A1) in my Excel spreadsheet, instead of printing to different cells. I can't figure out what I'm missing in my code. Thanks in advance for your help.

Nick Ruiz
Associate Integrator
PPLSolutions IT Billing and Transactions
 
Sort by date Sort by votes
This is the output of the XSL Transformation:

Code: 
<Workbook xmlns:user="urn:my-scripts" xmlns:msxsl="urn:schemas-microsoft-com:xslt" 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="[URL unfurl="true"]http://www.w3.org/TR/REC-html40">[/URL]
  <Styles>
    <Style ss:ID="Default" ss:Name="Normal">
      <Alignment ss:Vertical="Bottom" />
      <Borders />
      <Font />
      <Interior />
      <NumberFormat />
      <Protection />
    </Style>
    <Style ss:ID="s21">
      <Font ss:Bold="1" />
      <Alignment ss:Horizontal="Right" ss:Vertical="Bottom" />
    </Style>
    <Style ss:ID="s22">
      <Font ss:Bold="1" />
      <Alignment ss:Horizontal="Center" ss:Vertical="Bottom" />
    </Style>
    <Style ss:ID="s23">
      <Alignment ss:Horizontal="Center" ss:Vertical="Bottom" />
    </Style>
    <Style ss:ID="s24">
      <Alignment ss:Horizontal="Right" ss:Vertical="Bottom" />
    </Style>
    <Style ss:ID="s25">
      <Alignment ss:Horizontal="Right" ss:Vertical="Bottom" />
    </Style>
  </Styles>
  <Worksheet ss:Name="Detail">
    <Table ss:ExpandedColumnCount="12" ss:ExpandedRowCount="100" x:FullRows="1" x:FullColumns="1">
      <Row>
        <Cell ss:StyleID="s21">
          <Data ss:Type="String">EDC Number:</Data>
        </Cell>
        <Cell ss:StyleID="s23">
          <Data ss:Type="String">068231499986</Data>
        </Cell>
      </Row>
      <Row>
        <Cell ss:StyleID="s21">
          <Data ss:Type="String">Bill Account Number:</Data>
        </Cell>
        <Cell ss:StyleID="s23">
          <Data ss:Type="String">9842312000</Data>
        </Cell>
      </Row>
      <Row>
        <Cell ss:StyleID="s21">
          <Data ss:Type="String">Enrollment Number:</Data>
        </Cell>
        <Cell ss:StyleID="s23">
          <Data ss:Type="Number">1190533</Data>
        </Cell>
      </Row>
      <Row>
        <Cell ss:StyleID="s21">
          <Data ss:Type="String">Bill Date:</Data>
        </Cell>
        <Cell ss:StyleID="s23">
          <Data ss:Type="String">07/10/2006</Data>
        </Cell>
      </Row>
      <Row ss:Index="6">
        <Cell ss:StyleID="s22">
          <Data ss:Type="String">SR SEQ No</Data>
        </Cell>
        <Cell ss:StyleID="s22">
          <Data ss:Type="String">Date</Data>
        </Cell>
        <Cell ss:StyleID="s22">
          <Data ss:Type="String">Time</Data>
        </Cell>
        <Cell ss:StyleID="s22">
          <Data ss:Type="String">Metered KWH</Data>
        </Cell>
        <Cell ss:StyleID="s22">
          <Data ss:Type="String">Actual KWH</Data>
        </Cell>
        <Cell ss:StyleID="s22">
          <Data ss:Type="String">Peak</Data>
        </Cell>
        <Cell ss:StyleID="s22">
          <Data ss:Type="String">Index Price</Data>
        </Cell>
        <Cell ss:StyleID="s22">
          <Data ss:Type="String">Price Adder</Data>
        </Cell>
        <Cell ss:StyleID="s22">
          <Data ss:Type="String">Pricing Zone</Data>
        </Cell>
        <Cell ss:StyleID="s22">
          <Data ss:Type="String">Index Only Energy Charge</Data>
        </Cell>
        <Cell ss:StyleID="s22">
          <Data ss:Type="String">Index+Adder Energy Charge</Data>
        </Cell>
        <Cell ss:StyleID="s22">
          <Data ss:Type="String">Post Date</Data>
        </Cell>
      </Row>
    </Table>
  </Worksheet>
</Workbook>
If you save this to a .xls extension, you have what I am dealing with.

I noticed that when exporting a normal Excel spreadsheet to XML, the top of the document has the following:
Code: 
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
However, I am not sure how to write that in without throwing an exception (in VB.NET). Does anyone have any ideas as to why my export is incorrect?

Nick Ruiz
Associate Integrator
PPLSolutions IT Billing and Transactions
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

smak1
  • Locked
  • Question
add structure to XML
Replies
0
Views
690
smak1
smak1
Bijan Borazjani
  • Locked
  • Question
XML to PDF
Replies
0
Views
786
Bijan Borazjani
Bijan Borazjani
cdr_greg
  • Locked
  • Question
Display XML Line Breaks in Microsoft Word Content Controls
Replies
0
Views
1K
cdr_greg
cdr_greg
P
  • Locked
  • Question
How to re-use a recursive template
Replies
1
Views
778
pdrapeau
P
198709km
  • Locked
  • Question
Grouping XML key value using XSLT
Replies
1
Views
744
atlopes
atlopes

Part and Inventory Search

Sponsor

Back
Top