RE: Spreadsheet manipulation - clearing/deleting cell contents

2010-05-28 Thread Leigh

> 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

2010-05-28 Thread Jon Sala

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

2010-05-28 Thread Leigh

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

2010-05-28 Thread Jon Sala

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

2010-05-28 Thread Leigh

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

2010-05-27 Thread Leigh

> 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

2010-05-27 Thread Jon Sala

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

2010-05-26 Thread Leigh

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