Solved

Create Spreadsheet from Coldfusion with XML

Posted on 2012-04-11
7
705 Views
Last Modified: 2012-12-27
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>
0
Comment
Question by:diecasthft
  • 3
  • 2
7 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 37834521
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
 

Author Comment

by:diecasthft
ID: 37834567
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
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 37834618
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 52

Expert Comment

by:_agx_
ID: 37839801
Any luck with the updated example?
0
 

Author Comment

by:diecasthft
ID: 37866433
Sorry...I've been away, and just now getting back to this...let me take a look. Thanks Alot!!
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 38723224
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I still run into .cgi files every now and then. In some instances, I actually prefer the simplicity of a .cgi script to other options. Since I use DreamWeaver extensively, what I needed was a way to open .cgi scripts in Dreamweaver. And I wanted to …
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now