Solved

ColdFusion How do I set the cell padding when creating an excel spreadsheet?

Posted on 2012-12-30
17
840 Views
Last Modified: 2013-01-01
How do I increase the cell padding (space between the text and the cell) for the whole excel document?
0
Comment
Question by:DJPr0
[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
  • 6
  • 4
  • 4
  • +1
17 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 38731848
Increase the width of the column, if it's right justified add padding-right: style
0
 

Author Comment

by:DJPr0
ID: 38732319
Can you give me an example?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 38732567
The example depends on how you are coding it; can you post part of your code so I can see what you're doing and then I can help adjust it..
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 29

Expert Comment

by:Pravin Asar
ID: 38732624
In excel, you can set the indent attribute for a cell.

e.g.

Range("A1").IndentLevel = 5

With coldfusion you can set the display attributes for a cell/rows/columns

e.g.

cellFormat = StructNew();
cellFormat.font = "Arial";
cellFormat.fontsize = "18";
cellFormat.color = "black";
cellFormat.alignment = "left";
cellFormat.verticalalignment = "vertical_top"
cellFormat.textwrap = "true";
cellFormat.fgcolor = "white";
cellFormat.indent=5;


SpreadsheetFormatcell( sheetObj, cellFormat, rowNum, colNum );
0
 

Author Comment

by:DJPr0
ID: 38732707
What property creates space above and below the text in a cell?
Is there one property to create space all around the text in a cell?

Here is my existing code:

<cfif structKeyExists(FORM, "export") AND structKeyExists(VARIABLES, "Results")>
      <cfset sObj = SpreadsheetNew(#discipline1#)>
   
    <!--- add query parmas in 1st row and center align --->
    <cfset columnsInQuery = listLen(results.columnList)>
    <cfset SpreadsheetMergeCells(sObj, 1, 1, 1, columnsInQuery)>
    <cfset SpreadSheetSetCellValue(sObj, querySummary, 1, 1)>
    <cfset SpreadsheetFormatRow(sObj, {alignment="center"}, 1)>

    <!--- add record count in 2nd row and center align --->
    <cfset SpreadsheetMergeCells(sObj, 2, 2, 1, columnsInQuery)>
    <cfset SpreadSheetSetCellValue(sObj, recordsSummary, 2, 1)>
    <cfset SpreadsheetFormatRow(sObj, {alignment="center"}, 2)>

        <!--- Create header row --->
      <cfset SpreadsheetAddRow(sObj, "EQ,lian,S,Sp,Pro,Num,D,Description,QTY,QTY2 ,UI,SN,Con,Date")>
      <cfset SpreadsheetFormatRows(sObj, {bold=TRUE,fgcolor="grey_25_percent",fontsize="12", alignment="center"}, "1-3")>
    <cfset SpreadsheetFormatRow(sObj, {topborder="medium"}, 3)>
      <cfset SpreadsheetAddRows(sObj, Results)>
      <cfset SpreadsheetFormatColumns(sObj, {fontsize="12",bottomborder="medium",leftborder="medium",rightborder="medium"},"1-14")>
   
   
     <cfset saveToFile = "C:\orders#createUUID()#.xls">
     <cfspreadsheet action="write" name="sObj" filename="#saveToFile#" overwrite="true">

     <cfheader name="Content-Disposition" value="inline; filename=Orders.xls">
     <cfcontent type="application/excel" file="#saveToFile#" deletefile="yes">
     <!---- we're finished so exit --->
     <cfabort />
</cfif>
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 250 total points
ID: 38732918
Oh, you're using cfspreadsheet - I haven't used that yet.

But there is a height function to set the row height:

http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSD5DCEA07-5C77-4c79-BCF5-AD48D265243C.html
0
 

Author Comment

by:DJPr0
ID: 38732963
How can I use SpreadsheetSetRowHeight for all rows?

The number of rows vary according to query results.
0
 
LVL 29

Expert Comment

by:Pravin Asar
ID: 38733016
excel vba : Range("A").RowHeight  = 30;

Coldfusion Function:

SpreadsheetSetRowHeight

You can also try

rowFormat.rowheight
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38733054
> How can I use SpreadsheetSetRowHeight for all rows?

(No points)

As gdemaria mentioned, there's SpreadsheetSetRowHeight. But it doesn't accept a range. You must call it in a loop.

    <cfset maxRow = Results.recordCount +  (number of header rows)>
    <cfloop from="1" to="#maxRow#" index="rowNum">
         <cfset SpreadsheetSetRowHeight(sObj, rowNum, (height you want))>
     </cfloop>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38733458
One more thing. I'm pretty sure Excel doesn't have the concept of cell padding like in html tables. AFAIK the best you can get is either change the row height (plus center align) like gdemaria suggested OR add indentation like pravinasar suggested. There is no setting to add padding on all sides.
0
 

Author Comment

by:DJPr0
ID: 38734880
Thanks _agx_ the loop worked great!

I'm having a problem with text wrapping and column width.

I want to text wrap the  third column header:
QTY REC to
QTY
REC

I tried using:
 <cfset SpreadsheetFormatCell(sObj, {textwrap=true}, 2, 3)>
 <cfset SpreadSheetSetColumnWidth(sObj, 3, 3)>

Without any effect.
0
 
LVL 29

Expert Comment

by:Pravin Asar
ID: 38735025
You can have textwrap

cellFormat.textwrap = "true";

But this would ensure you have break at particular location.
You can force line breaks to appear in cell contents by

value= "Line 1" & chr(10) & "Line 2"
0
 

Author Comment

by:DJPr0
ID: 38735077
How do I utilize/format this line in my spreadsheet code?

value= "Line 1" & chr(10) & "Line 2"

<cfset value= "Line 1" & chr(10) & "Line 2"?

Open in new window

0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 250 total points
ID: 38735135
For a single cell value? Just combine your code w/ pravinasar's. If you want to wrap all cells in the column use SpreadsheetFormatColumn


<cfset SpreadSheetSetColumnWidth(sObj, 3, 3)>
<cfset SpreadsheetFormatCell(sObj, {textwrap=true}, 2, 3)>
<cfset cellValue = "This"& chr(10) & "value should"& chr(10) &"wrap">
<cfset SpreadsheetSetCellValue(sObj, cellValue, 2, 3)>
0
 

Author Closing Comment

by:DJPr0
ID: 38735313
Big THANKS to everyone!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38735383
@DJPro - I think you accidentally omitted pravinasar's response as part of the answer. He mentioned using chr(10) to force a new line. (I just combined both of your answers into a working example.) If you want to reallocate, just use the "Request Attention" link in the original question and the mods can re-open it for you.  Cheers!
0
 
LVL 29

Expert Comment

by:Pravin Asar
ID: 38735470
It is weird

I put quite a bit of effort in explaining with cf functions

And I get zero

Have a nice year
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Coldfusion subreport - passing parameters 2 91
<cffile cannot delete a file 4 72
Using a variable with dateAdd 18 81
Coldfusion remove square brackets from string 4 164
Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
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.

732 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