2

I'm making a cell top align, after a merge cell by using spreadsheet functions. What I found on google and CFML Reference Adobe ColdFusion 10 was only left (default), right, center, justify, general, fill, and center_selection. Is there any other way or idea that can make it "top align" after merge cell? Here is my code :

<cfscript> 
    theSheet = SpreadsheetNew("Order Details 1");
    SpreadsheetAddRow(theSheet, "NO, ,VENDOR, PART NUMBER, PART NAME, PSI, LEAD TIME,MONTH, YEAR, ,N-5, N-4, N-3, 
    N-2, N-1, N, N+1, N+2, N+3, N+4, PACKING MONTH, PRODUCTION MONTH ",5,1); 
    myFormat2=StructNew();
    myFormat2.bold=false;
    myFormat2=StructNew();
    myFormat2.bold=false;
    myFormat2.alignment="vertical_top";
    SpreadsheetFormatRow(theSheet,myFormat2,6);
    SpreadsheetMergeCells(theSheet,6,25,2,2);
    SpreadsheetMergeCells(theSheet,6,25,3,3);
    SpreadsheetMergeCells(theSheet,6,25,4,4);
    SpreadsheetMergeCells(theSheet,6,25,5,5);
    SpreadsheetMergeCells(theSheet,6,25,7,7);
    SpreadsheetMergeCells(theSheet,26,45,2,2);
    SpreadsheetMergeCells(theSheet,26,45,3,3);
    SpreadsheetMergeCells(theSheet,26,45,4,4);
    SpreadsheetMergeCells(theSheet,26,45,5,5);
    SpreadsheetMergeCells(theSheet,26,45,7,7);
    SpreadsheetAddRows(theSheet,getROW);
</cfscript>
SOS
  • 6,430
  • 2
  • 11
  • 29
SySyBy
  • 827
  • 1
  • 7
  • 11

2 Answers2

3

You can use verticalalignment instead/along with alignment for aligning to top. You can use vertical_top, vertical_bottom, vertical_center, vertical_justifyFor for different alignments.

For more information go here.

SpreadsheetFormatCellRange(theSheet,{verticalalignment="VERTICAL_TOP"}, 3,4,30,10);
rrk
  • 15,677
  • 4
  • 29
  • 45
  • Have tried it but it does not work and it gives a blank space on top of the cell. – SySyBy Jan 02 '19 at 15:27
  • Are you sure you used `verticalalignment` and not `alignment`? – rrk Jan 02 '19 at 15:58
  • Any difference when you use this? `spreadsheetFormatCell(theSheet, {verticalalignment="VERTICAL_TOP"}, 6, 3)` – rrk Jan 02 '19 at 16:13
  • Yes im used verticalalignment like this SpreadsheetFormatCellRange (theSheet, {verticalalignment="VERTICAL_TOP"}, 6, 2, 25, 2); but its not work.when I used spreadsheetFormatCell(theSheet, {verticalalignment="VERTICAL_TOP"}, 6, 3) it move to other merge cell below and its still bottom align. – SySyBy Jan 02 '19 at 16:29
  • What's your exact version? Seems to work on trycf.com (10,0,16,293499) to top align column B https://trycf.com/gist/f42d6c97b71ee02123216c23abe4915c/acf?theme=monokai – SOS Jan 03 '19 at 00:18
  • @Ageax Im using ColdFusion Server Enterprise 10,0,23,302580. When I use this code SpreadsheetFormatCellRange (theSheet, {verticalalignment="VERTICAL_TOP"}, 6, 2, 25, 2); , yes it will make the column B top align but the data inside row 6 will move to row 26 not start at row 6. – SySyBy Jan 03 '19 at 01:19
  • 2
    @RRK I try again using SpreadsheetFormatCellRange (theSheet, {verticalalignment="VERTICAL_TOP"}, 6, 2, 25, 2); and its work!! This is because before this i write SpreadsheetFormatCellRange before SpreadsheetAddRows(theSheet,getROW);.It should be write like this: SpreadsheetAddRows(theSheet,getROW); SpreadsheetFormatCellRange (theSheet,{verticalalignment="VERTICAL_TOP"}, 6, 2, 25, 2);. Thank you. – SySyBy Jan 04 '19 at 00:21
  • 1
    Yes, that is something you need to keep in mind. I am not sure why but formatting gets overwritten when we add data to a cell. – rrk Jan 04 '19 at 06:26
  • 1
    Good point about the different settings. `alignment` should really be called "horizontalAlignment". – SOS Jan 04 '19 at 19:10
0

RRK answer should work but if you are looking for another option there is a plug-in that I have been using for a long time because of the limitations and frustrations of cfspreadsheet.

https://github.com/cfsimplicity/lucee-spreadsheet

I know its is really for Lucee (which I like better than Adobes version) but it does work in Adobe's CF2016 if you end up upgrading which you should be planning since ColdFusion 11 is hitting it's end of life this year.

https://helpx.adobe.com/support/programs/eol-matrix.html

Lance
  • 3,193
  • 2
  • 32
  • 49