Solved

Create Spreadsheet from Coldfusion with XML

Posted on 2012-04-11
7
747 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
REGEX HELP 11 62
decryping the string data from visa checkout 5 46
Please Explain What Reading a Zip file Back In Means 11 79
SQL Server for XML PATH giving wrong results. 6 61
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

759 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