Avatar of DJPr0
DJPr0
 asked on

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

How do I increase the cell padding (space between the text and the cell) for the whole excel document?
ColdFusion Language

Avatar of undefined
Last Comment
Pravin Asar

8/22/2022 - Mon
gdemaria

Increase the width of the column, if it's right justified add padding-right: style
DJPr0

ASKER
Can you give me an example?
gdemaria

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..
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Pravin Asar

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 );
DJPr0

ASKER
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>
ASKER CERTIFIED SOLUTION
gdemaria

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
DJPr0

ASKER
How can I use SpreadsheetSetRowHeight for all rows?

The number of rows vary according to query results.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Pravin Asar

excel vba : Range("A").RowHeight  = 30;

Coldfusion Function:

SpreadsheetSetRowHeight

You can also try

rowFormat.rowheight
_agx_

> 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>
_agx_

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
DJPr0

ASKER
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.
Pravin Asar

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"
DJPr0

ASKER
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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
_agx_

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
DJPr0

ASKER
Big THANKS to everyone!
_agx_

@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!
Pravin Asar

It is weird

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

And I get zero

Have a nice year
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy