Copy link to clipboard
Copied
<cfscript>
theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "courses.xls";
theSheet = SpreadsheetNew("CourseData");
SpreadsheetAddRows(theSheet,courses);
format1=StructNew()
format1.alignment="vertical_top";
SpreadsheetFormatRow(theSheet,format1,"5");
//SpreadsheetFormatCell(theSheet,format1,5,1)
</cfscript>
I copied the example from the function example, but it does not work on my CF9.0 Server. Neither format row or format cell works.
Does anyone have any idea why it is not working and how could I make the text in the cell align to top?
Thanks.
Copy link to clipboard
Copied
I could be wrong, but I do not think that feature worked in CF9. Did you check the bug database?
Copy link to clipboard
Copied
it is not in the bug specifically as the two function mentioned here. The documentation indicates it should be working with CF9.
Thanks for your reply!
Copy link to clipboard
Copied
Yep, I know the documentation says it should work. But I just seem to recall something about that particular setting not working. Again, I am not 100% certain. But seeing as how you are having problems too it does lend some credence to the theory. Let me look around and see if I can find anything to substantiate or disprove it.
Copy link to clipboard
Copied
Also, so as not to overlook the obvious - are you sure you are using the correct syntax for your CF version? I believe it was changed in 9.0.1.
Copy link to clipboard
Copied
Yes it is change the here is the instruction for alignment:
the example for 9.01 is:
Any of the following: vertical_top, vertical_bottom, vertical_center, vertical_justify
For example,
<cfscript>SpreadsheetFormatCellRange(theSheet,{verticalalignment="VERTICAL_TOP"}, 3,4,30,10);</cfscript>
Here is my code:
format1=StructNew();
format1.font="serif";/* */
format1.fontsize="9";
format1.color="dark_yellow";
format1.alignment="vertical_top";
//format1.verticalalignment="VERTICAL_TOP";
//format2.textwrap="true";
for (x = 2; x <= #q.recordcount#+1; x++) {
SpreadSheetFormatRow(theSheet,format1,#x#); //not working to v-align top
}
font and color all working only valignment is not working.
Copy link to clipboard
Copied
Thanks, it doesn't see too many people using the function. Could not find an sample other than the documentation.
Copy link to clipboard
Copied
I cannot access a CF9 install right now, but this works with CF10. Assuming you are running 9.0.1, can you try it? What results do you get?
<cfscript>
// note: "verticalalignment" property was added in 9.0.1
sheet = SpreadsheetNew();
spreadsheetSetCellValue(sheet, "AAA", 1, 1);
spreadsheetSetRowHeight(sheet, 1, 25);
spreadsheetFormatCell(sheet,{verticalalignment="vertical_top"},1,1);
spreadSheetWrite(sheet, "c:/path/to/test.xls", true);
</cfscript>
Copy link to clipboard
Copied
Oops.. posts collided.
Just to make sure we are on the same page, what is your exact version - CF 9.0.1? If so try the code above, just for grins. Because if it does not work under 9.0.1, then that strongly suggests a bug.
Copy link to clipboard
Copied
The server is cf 9.0, I tried either code for 9.0 or 9.0.1, for both spreadsheetFormatCell and spreadsheetFormatRow, neither is working, I tried your code, as well as changed the wording on either side of the equal sign, NO luck, I guess the valignment is just not working for this version coldfusion server.
Copy link to clipboard
Copied
Unfortunately it is looking that way. You could do it yourself by digging into the POI internals. However, that might be more manual than you want just to change the alignment.
Copy link to clipboard
Copied
Another thing I did, I also set the column height and width, if I don't set the height and width, there will be not such issue or need to set v-alignment, but the column just expend to very wide if the content is long text,
Thanks you so much for spending time on this, appreciate your time.
Copy link to clipboard
Copied
You are welcome. That sounds like a simpler solution.
BTW, if you have time you might consider submitting a bug report too. Whether or not they choose to fix it in version 9, just having it documented is helpful to others that run into the same problem in the future.
-Leigh
Copy link to clipboard
Copied
Yes, I did report the bug. Probably that is the only server version it is not working. Thanks again!
Copy link to clipboard
Copied
Just to verify cfSearching's experimental code: it works fine for me on CF 9.0.1.
--
Adam
Copy link to clipboard
Copied
Just to verify cfSearching's experimental code: it works fine for me on CF 9.0.1.
--
Adam
Interesting. I tried the old syntax under 9,0,0,251028 and it does not work for me. Let me try updating to 9.0.1 and see if I get the same results.
@Jush - Since your results are different than Adam's are you sure you tested my code with 9.0.1?
Update:
I did some additional experimenting with 9.0.0 and I am pretty sure the feature is broken in that version. Based on the behvavior, I think they mistakenly used POI's horizontal alignment function for both properties. Notice if you use "vertical_bottom" or "vertical_justify" CF actually changes the horizontal alignment, not vertical.
<cfscript>
sheet = SpreadsheetNew();
spreadsheetSetCellValue(sheet, "Foo", 1, 1);
spreadsheetSetCellValue(sheet, "Bar", 2, 1);
// this produces horizontal CENTER alignment
spreadsheetFormatCell(sheet, {alignment="vertical_bottom"}, 1, 1);
// produces horizontal RIGHT alignment
spreadsheetFormatCell(sheet, {alignment="vertical_justify"}, 2, 1);
// must set these properties last otherwise they do not stick! ...
spreadsheetSetColumnWidth(sheet, 1, 40);
spreadsheetSetRowHeight(sheet, 1, 60);
spreadsheetSetRowHeight(sheet, 2, 60);
spreadSheetWrite(sheet, "c:/path/to/test.xls", true);
</cfscript>
Message was edited by: -==cfSearching==-
Copy link to clipboard
Copied
I don't have CF 9.0.1 server so I am not able to test on that version; I tested on 9,0,0,251028, it did not work, on CF 10, it worked fine.
Copy link to clipboard
Copied
The bug exists on CF server version 9,0,0,251028, that is the server I have, CF 9.0.1 has more updates on the function, it could very possible it works. for It also works for CF10. Thanks.
Copy link to clipboard
Copied
> The bug exists on CF server version 9,0,0,251028
Agreed. That is the version I used for testing. (I misread your earlier comment and thought you had tested with 9.0.1. But on second read, I realize you just meant the newer syntax. Sorry, my bad. Mystery solved
-Leigh