RE: Spreadsheet manipulation - clearing/deleting cell contents
> So it seems the culprit is creating a spreasheet from a > query with SpreadsheetAddRows(). > Rows created by QueryAddRow() although empty, are not > 'blank' in Excel's opinion. Yep. It seems to set the cell values to "", rather than the special BLANK cell type (unfortunately). ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334090 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Spreadsheet manipulation - clearing/deleting cell contents
So it seems the culprit is creating a spreasheet from a query with
SpreadsheetAddRows().
Rows created by QueryAddRow() although empty, are not 'blank' in Excel's
opinion.
In this case, I needed some blank spreadsheet rows to inject long overflowing
strings, inbetween the rows of source query data.
I would have used SpreadsheetAddRows() to loop through the whole thing, except
that some fields need to contain comma-delimited strings - adding unwanted
columns in the process. Another workaround in this case would be to find a way
to escape the commas.
-Original Message-
From: Leigh [mailto:cfsearch...@yahoo.com]
Sent: Friday, May 28, 2010 10:03 AM
To: cf-talk
Subject: RE: Spreadsheet manipulation - clearing/deleting cell contents
If you want A1 to overflow into B1, then do not set anything for B1. For
example, notice how this code sets a value for A1 and C1, but skips B1? As long
as you do not touch the adjacent cell (B1), it will be considered blank. So any
text to the left of it will overflow into that cell.
cfSheet = SpreadsheetNew("foo");
SpreadsheetSetCellValue(cfSheet, 'this text should overflow', 1, 1);
SpreadsheetSetCellValue(cfSheet, 'short text', 1, 3);
saveToFile = ExpandPath("test-blank-cell.xls");
SpreadsheetWrite(cfsheet, saveToFile, true);
WriteOutput("Saved file: "& saveToFile &"");
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334087
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Spreadsheet manipulation - clearing/deleting cell contents
If you want A1 to overflow into B1, then do not set anything for B1. For
example, notice how this code sets a value for A1 and C1, but skips B1? As long
as you do not touch the adjacent cell (B1), it will be considered blank. So any
text to the left of it will overflow into that cell.
cfSheet = SpreadsheetNew("foo");
SpreadsheetSetCellValue(cfSheet, 'this text should overflow', 1, 1);
SpreadsheetSetCellValue(cfSheet, 'short text', 1, 3);
saveToFile = ExpandPath("test-blank-cell.xls");
SpreadsheetWrite(cfsheet, saveToFile, true);
WriteOutput("Saved file: "& saveToFile &"");
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334085
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Spreadsheet manipulation - clearing/deleting cell contents
I may not be following you on this Leigh; if I put a long string in A1 using SpreadsheetSetCellValue and nothing in B1, A1 is still clipped...? -Original Message- From: Leigh [mailto:cfsearch...@yahoo.com] Sent: Friday, May 28, 2010 2:32 AM To: cf-talk Subject: RE: Spreadsheet manipulation - clearing/deleting cell contents Just a follow up. Azadi pointed out you can get around the problem by setting the _non-blank_ cell values individually, with SpreadsheetSetCellValue(). That has the benefit of being documented at least. Still giving serious thought to the bug/enhancement request though .. -Leigh ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334083 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Spreadsheet manipulation - clearing/deleting cell contents
Just a follow up. Azadi pointed out you can get around the problem by setting the _non-blank_ cell values individually, with SpreadsheetSetCellValue(). That has the benefit of being documented at least. Still giving serious thought to the bug/enhancement request though .. -Leigh ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334080 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Spreadsheet manipulation - clearing/deleting cell contents
> Hopefully it doesn't break to soon if ever, That is always a risk with undocumented code. If you chose to live dangerously ;), you might want to isolate the code to a single location. So it is easier to fix when it breaks. Personally, I would be to keep looking for something "documented". If you find it really is not possible, consider submitting a bug report/feature request in the ACF bug database. I would vote for it. -Leigh ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334068 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Spreadsheet manipulation - clearing/deleting cell contents
Yes, thanks Leigh that is great cheat to get into production with!
Hopefully it doesn't break to soon if ever,
--
Jon
-Original Message-
From: Leigh [mailto:cfsearch...@yahoo.com]
Sent: Wednesday, May 26, 2010 9:02 PM
To: cf-talk
Subject: Re: Spreadsheet manipulation - clearing/deleting cell contents
I am not sure if the Spreadsheet functions support the concept of a "blank
cell". You could cheat and use the underlying POI functions. But I too am
curious if anyone knows a supported method of doing this.
// ...
variables.sheet = variables.spObj.getWorkBook().getSheetAt( 0 );
for (i=2;i LTE qryTarget.Recordcount; i=i+1) {
SpreadsheetSetCellValue(variables.spObj, 'overflowoverflow
overflowoverflow overflowoverflow overflowoverflow ', i, 1 );
// data starts at row 1. so physical and logical rows are the same
variables.row = variables.sheet.getRow( i-1 );
variables.cell = variables.row.getCell( 1 );
variables.cell.setCellType( variables.cell.CELL_TYPE_BLANK );
//
}
-Leigh
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334054
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Spreadsheet manipulation - clearing/deleting cell contents
I am not sure if the Spreadsheet functions support the concept of a "blank
cell". You could cheat and use the underlying POI functions. But I too am
curious if anyone knows a supported method of doing this.
// ...
variables.sheet = variables.spObj.getWorkBook().getSheetAt( 0 );
for (i=2;i LTE qryTarget.Recordcount; i=i+1) {
SpreadsheetSetCellValue(variables.spObj, 'overflowoverflow
overflowoverflow overflowoverflow overflowoverflow ', i, 1 );
// data starts at row 1. so physical and logical rows are the same
variables.row = variables.sheet.getRow( i-1 );
variables.cell = variables.row.getCell( 1 );
variables.cell.setCellType( variables.cell.CELL_TYPE_BLANK );
//
}
-Leigh
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334028
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
