Create Spreadsheet from Coldfusion with XML

Good Day All!!! I was hoping for some assistance in using Coldfusion to create an Excel spreadsheet with multiple worksheets. In theory, I think I'm close....as I can get the multiple work books, and I can load a query, and get the results corrct (I think). I can even set the column widths..so I'm good with all of that. What I'm having difficulty with is using styles to set things like color, font, ect. I keep getting an error when I try to use the styles....but if I remove the style, the sheets seem to be o.k. I guess I have a formatting issue somewhere, but don't know enough about this and xml to go any further. Can anyone help me, or point me in the right direction?? Thanks Alot!!

<cfheader name="content-disposition" value="inline;filename=ExcelWithMultipleWorksheets.xls">
<cfcontent type="application/msexcel">
<?xml version="1.0"?>
<Workbook
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:ss="urn:schemas-micfosoft-com:office:spreadsheet">

<!-- The Styles tag and sub-tags hold the formattiong information for the spreadsheet.  Think of them like CSS -->      
      <Styles>        
           <Style ss:ID="s25">
                  <Alignment ss:Horizontal="Center" ss:Vertical="Bottom" ss:WrapText="1"/>
                  <Borders>
                        <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
                        <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                        <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
                        <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>                        
                  </Borders>
                  <Font x:Family="Swiss" ss:Size="6" ss:Bold="1"/>
                  <Interior ss:Color="#CCFFFF" ss:Pattern="Solid"/>                  
             </Style>        

      </Styles>

    <Worksheet ss:Name="Worksheet 1">
        <Table>
       
        <!-- The Column tag sets some properties for eache collumn -->
            <Column ss:Index="1" ss:Width="250"/>
            <Column ss:Index="2" ss:Width="30"/>
            <Column ss:Index="3" ss:Width="30"/>
            <Column ss:Index="4" ss:Width="25"/>
            <Column ss:Index="5" ss:Width="40"/>
            <Column ss:Index="6" ss:Width="40"/>
            <Column ss:Index="7" ss:Width="40"/>
            <Column ss:Index="8" ss:Width="33"/>
            <Column ss:Index="9" ss:Width="34"/>
            <Column ss:Index="10" ss:Width="35"/>
            <Column ss:Index="11" ss:Width="25"/>
            <Column ss:Index="12" ss:Width="30"/>
            <Column ss:Index="13" ss:Width="55"/>
            <Column ss:Index="14" ss:Width="40"/>
            <Column ss:Index="15" ss:Width="90"/>
            <Column ss:Index="16" ss:Width="40"/>
            <Column ss:Index="17" ss:Width="37"/>
            <Column ss:Index="18" ss:Width="60" ss:AutoFitWidth="1"/>
            <Column ss:Index="19" ss:Width="60" ss:AutoFitWidth="1"/>
            <Column ss:Index="20" ss:Width="60" ss:AutoFitWidth="1"/>
            <Column ss:Index="21" ss:Width="37"/>
            <Column ss:Index="22" ss:Width="37"/>
            <Column ss:Index="23" ss:Width="80"/>
            <Column ss:Index="24" ss:Width="45"/>
       
            <Row>
                  <!-- The Cell tag starts each Cell.
                            The StyleID attribute associates a style with this field.
                        The Data tag holds the actual data for the cell.-->
                  <Cell ss:StyleID="s25"><Data ss:Type="String">ID</Data></Cell>
                <Cell ss:StyleID="s25"><Data ss:Type="String">MIPR Type</Data></Cell>
                <Cell ss:StyleID="s25"><Data ss:Type="String">Requiring Activity</Data></Cell>
                <Cell ss:StyleID="s25"><Data ss:Type="String">Sub Activity</Data></Cell>
                <Cell ss:StyleID="s25"><Data ss:Type="String">Acq. Center</Data></Cell>
                <Cell ss:StyleID="s25"><Data ss:Type="String">Requiring Activity POC</Data></Cell>
                <Cell ss:StyleID="s25"><Data ss:Type="String">Contracting Officer</Data></Cell>
                <Cell ss:StyleID="s25"><Data ss:Type="String">Contractor</Data></Cell>
                <Cell ss:StyleID="s25"><Data ss:Type="String">Solicitation Number</Data></Cell>
                <Cell ss:StyleID="s25"><Data ss:Type="String">Contract Number</Data></Cell>
                <Cell ss:StyleID="s25"><Data ss:Type="String">Type of Funds</Data></Cell>
                <Cell ss:StyleID="s25"><Data ss:Type="String">JONO</Data></Cell>
                <Cell ss:StyleID="s25"><Data ss:Type="String">Commit Ref. Number</Data></Cell>
                <Cell ss:StyleID="s25"><Data ss:Type="String">Date of MIPR</Data></Cell>
                <Cell ss:StyleID="s25"><Data ss:Type="String">Nomenclature</Data></Cell>
                <Cell ss:StyleID="s25"><Data ss:Type="String">Customer</Data></Cell>
                <Cell ss:StyleID="s25"><Data ss:Type="String">Type of Contractual Action</Data></Cell>
                <Cell ss:StyleID="s25"><Data ss:Type="String">Estimated Value</Data></Cell>
                <Cell ss:StyleID="s25"><Data ss:Type="String">Obligations</Data></Cell>
                <Cell ss:StyleID="s25"><Data ss:Type="String">Commits</Data></Cell>
                <Cell ss:StyleID="s25"><Data ss:Type="String">Projected Award Date</Data></Cell>
                <Cell ss:StyleID="s25"><Data ss:Type="String">Current Revised Projected Award Date</Data></Cell>
                <Cell ss:StyleID="s25"><Data ss:Type="String">Program Remarks</Data></Cell>
                <Cell ss:StyleID="s25"><Data ss:Type="String">Budget Remarks</Data></Cell>
               
            </Row>
        </Table>
    </Worksheet>
   
    <Worksheet ss:Name="Worksheet 2">
        <Table>
            <Row>
                <Cell ss:Index="1"><Data ss:Type="String">Test Data 2</Data></Cell>
            </Row>
        </Table>
    </Worksheet>
   
</Workbook>
diecasthftAsked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
I keep getting an error that says something about no mappings...

Hm.. what exactly does it say? Are you doing this all locally in ms Excel? Because I see no reason exporting/importing on a single computer wouldn't work.

The error message I get is actually pretty lame

Figures, what kinds of styles are you trying to add? Maybe I can dummy up a simple example.

Edit: Maybe you're missing some headers/ns declarations. Here's a simple example with 3 styled cells that works in Office 2003.

<?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">
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel" />
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s21">
   <Font/>
   <Interior ss:Color="#FF0000" ss:Pattern="Solid"/>
  </Style>
  <Style ss:ID="s23">
   <Font x:Family="Swiss" ss:Color="#0000FF" ss:Bold="1"/>
  </Style>
  <Style ss:ID="s25">
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
     ss:Color="#00FF00"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
     ss:Color="#00FF00"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
     ss:Color="#00FF00"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
     ss:Color="#00FF00"/>
   </Borders>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="3" x:FullColumns="1"
   x:FullRows="1">
   <Column ss:AutoFitWidth="0" ss:Width="87.75"/>
   <Row>
    <Cell ss:StyleID="s21"/>
   </Row>
   <Row>
    <Cell ss:StyleID="s23"><Data ss:Type="String">Blue Bold</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s25"><Data ss:Type="String">Green box border</Data></Cell>
   </Row>
  </Table>
 </Worksheet>
 <x:ExcelWorkbook/>
</Workbook>

Open in new window

0
 
_agx_Commented:
I keep getting an error when I try to use the styles

What's a very simple example that cause an error - and what's the message you're getting?

I always find the xml stuff complicated. So I usually create a simple one cell example in excel and export it as xml. Then I can see what the xml should look like.. and duplicate it.
0
 
diecasthftAuthor Commented:
I read about that, but I haven't been able to take a spreadhseet and export to xml...I keep getting an error that says something about no mappings...

The error message I get is actually pretty lame. It tells me that there is an error in the TABLE, or the WORKBOOK, or STYLE but doesn't give me much more to go on. It even tells me to go to a folder to view an error log file, but when I go to that folder, there's nothing there.
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
_agx_Commented:
Any luck with the updated example?
0
 
diecasthftAuthor Commented:
Sorry...I've been away, and just now getting back to this...let me take a look. Thanks Alot!!
0
 
Jason C. LevineNo oneCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
All Courses

From novice to tech pro — start learning today.