Solved

Create Spreadsheet from Coldfusion with XML

Posted on 2012-04-11
7
733 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

This article is very specific and is only intended to help if you are installing Dreamweaver 8 in a Windows 7 environment with Office 2007 installed.   I'm not sure why Microsoft tends to release OS' that should not be released but they do.  Windows…
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 …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

821 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