SpreadsheetFormatRow() and SpreadsheetFormatCell() could not align text vertical to top

[Jush1]

<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.

[-__cfSearching__-]

I could be wrong, but I do not think that feature worked in CF9.   Did you check the bug database?

[Jush1]

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!

[-__cfSearching__-]

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.

[-__cfSearching__-]

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.

[Jush1]

Yes it is change the here is the instruction for alignment:

• (ColdFusion 9) left (default), right, center, justify, general, fill, center_selection, vertical_top, vertical_bottom, vertical_center, vertical_justify
• (ColdFusion 9.0.1): left (default), right, center, justify, general, fill, and center_selection

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.

[Jush1]

Thanks, it doesn't see too many people using the function. Could not find an sample other than the documentation.

[-__cfSearching__-]

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>

[-__cfSearching__-]

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.

[Jush1]

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.

[-__cfSearching__-]

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.

[Jush1]

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.

[-__cfSearching__-]

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

[Jush1]

Yes, I did report the bug.  Probably that is the only server version it is not working. Thanks again!

[Adam Cameron.]

Just to verify cfSearching's experimental code: it works fine for me on CF 9.0.1.

--

Adam

[-__cfSearching__-]

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==-

[Jush1]

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.

[Jush1]

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.

[-__cfSearching__-]

         > 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