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