Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.
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?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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..
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.verticalalignme
cellFormat.textwrap = "true";
cellFormat.fgcolor = "white";
cellFormat.indent=5;
SpreadsheetFormatcell( sheetObj, cellFormat, rowNum, colNum );
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.verticalalignme
cellFormat.textwrap = "true";
cellFormat.fgcolor = "white";
cellFormat.indent=5;
SpreadsheetFormatcell( sheetObj, cellFormat, rowNum, colNum );
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,
<cfset sObj = SpreadsheetNew(#discipline
<!--- add query parmas in 1st row and center align --->
<cfset columnsInQuery = listLen(results.columnList
<cfset SpreadsheetMergeCells(sObj
<cfset SpreadSheetSetCellValue(sO
<cfset SpreadsheetFormatRow(sObj,
<!--- add record count in 2nd row and center align --->
<cfset SpreadsheetMergeCells(sObj
<cfset SpreadSheetSetCellValue(sO
<cfset SpreadsheetFormatRow(sObj,
<!--- Create header row --->
<cfset SpreadsheetAddRow(sObj, "EQ,lian,S,Sp,Pro,Num,D,De
<cfset SpreadsheetFormatRows(sObj
<cfset SpreadsheetFormatRow(sObj,
<cfset SpreadsheetAddRows(sObj, Results)>
<cfset SpreadsheetFormatColumns(s
<cfset saveToFile = "C:\orders#createUUID()#.x
<cfspreadsheet action="write" name="sObj" filename="#saveToFile#" overwrite="true">
<cfheader name="Content-Disposition"
<cfcontent type="application/excel" file="#saveToFile#" deletefile="yes">
<!---- we're finished so exit --->
<cfabort />
</cfif>
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,
<cfset sObj = SpreadsheetNew(#discipline
<!--- add query parmas in 1st row and center align --->
<cfset columnsInQuery = listLen(results.columnList
<cfset SpreadsheetMergeCells(sObj
<cfset SpreadSheetSetCellValue(sO
<cfset SpreadsheetFormatRow(sObj,
<!--- add record count in 2nd row and center align --->
<cfset SpreadsheetMergeCells(sObj
<cfset SpreadSheetSetCellValue(sO
<cfset SpreadsheetFormatRow(sObj,
<!--- Create header row --->
<cfset SpreadsheetAddRow(sObj, "EQ,lian,S,Sp,Pro,Num,D,De
<cfset SpreadsheetFormatRows(sObj
<cfset SpreadsheetFormatRow(sObj,
<cfset SpreadsheetAddRows(sObj, Results)>
<cfset SpreadsheetFormatColumns(s
<cfset saveToFile = "C:\orders#createUUID()#.x
<cfspreadsheet action="write" name="sObj" filename="#saveToFile#" overwrite="true">
<cfheader name="Content-Disposition"
<cfcontent type="application/excel" file="#saveToFile#" deletefile="yes">
<!---- we're finished so exit --->
<cfabort />
</cfif>
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
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
How can I use SpreadsheetSetRowHeight for all rows?
The number of rows vary according to query results.
The number of rows vary according to query results.
excel vba : Range("A").RowHeight = 30;
Coldfusion Function:
SpreadsheetSetRowHeight
You can also try
rowFormat.rowheight
Coldfusion Function:
SpreadsheetSetRowHeight
You can also try
rowFormat.rowheight
> 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(sO
</cfloop>
(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(sO
</cfloop>
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.
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
<cfset SpreadSheetSetColumnWidth(
Without any effect.
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
<cfset SpreadSheetSetColumnWidth(
Without any effect.
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"
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"
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"?
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(
<cfset SpreadsheetFormatCell(sObj
<cfset cellValue = "This"& chr(10) & "value should"& chr(10) &"wrap">
<cfset SpreadsheetSetCellValue(sO
<cfset SpreadSheetSetColumnWidth(
<cfset SpreadsheetFormatCell(sObj
<cfset cellValue = "This"& chr(10) & "value should"& chr(10) &"wrap">
<cfset SpreadsheetSetCellValue(sO
@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!
Premium Content
You need an Expert Office subscription to comment.Start Free Trial