Formatting WEB Excel Layouts

[Former Member]

Hi All,

I would like to change the font size in the header rows in an excel based web layout. I have checked "Publish to web" and my column widths and freeze panes has remained but not my font size. Has anyone been able to do this???

Mike

[Former Member · Accepted solution]

Hi Michael,

We are on 3.5 BW with SP 11. But I don't think thats where your problem lies.

Are there any run time error messages?

What happens when you open your web interface?

Make sure the text component (with the javascript) is placed after the layout component in the web builder.

Also, try and insert some alerts to see if the code is running and can pick up a cell value. Insert the following into the code just before the sheet is re-protected (change the cell(1,1) to something you know will pick up a excel cell with a value in it:

 
var cell = layout_sheet.cells(1,1);
alert(cell.value);

This way you should be able to see if the javascript is correctly opening the layout and finding excel cells.

Good luck,

Rael

[Former Member]

Hi All,

Success!!! I finally got this to work. I went into the source code for the page and copied how it was selecting the sheet. This did the trick. Below is the code I ended up using:

<script language="JavaScript" type="text/javascript">

<!--

var layout_id = "LAYOUT4";

layout_sheet = document.all( layout_id );

var classIds = ["CLSID:0002E510-0000-0000-C000-000000000046", // 2000

"CLSID:0002E559-0000-0000-C000-000000000046", // 2003

"CLSID:0002E551-0000-0000-C000-000000000046"]; // XP(2002)

// if Excel OWC NOT available => try OCX from another known office version

var defClassId = layout_sheet.classid;

for (i = 0; !layout_sheet.ActiveSheet && i < classIds.length; i++) {

if (classIds<i> == defClassId) continue; // skip default

// remove the irrelevant object

layout_sheet.removeNode();

// create new object & set size attributes

layout_sheet = document.createElement("OBJECT");

layout_sheet.style.height = "0";

layout_sheet.style.width = "0";

// append to dom and activate OCX

var layout_div = document.all( layout_id + "-div" );

layout_sheet = layout_div.appendChild(layout_sheet);

layout_sheet.classid = classIds<i>;

layout_sheet.id = layout_id;

} // loop over OCX classes

if (layout_sheet.ActiveSheet) { // Excel OWC available

// send the office version to the backend

document.all(layout_id + "-class").value = layout_sheet.classid;

layout_sheet.ActiveSheet.Protection.Enabled = false;

range = layout_sheet.Range(layout_sheet.Cells(6 ,1 ),layout_sheet.Cells(6 ,70 ));//

range.Font.Size =8 ;

layout_sheet.ActiveSheet.Protection.Enabled = true;

}

// -->

[Former Member]

Hi Rael,

Still no luck. I have even changed the name of my layout to LAYOUT4 so i can cut and paste your code exactly as it is above.

What version / support pack are you on???

I am on a 3.5 BW with SP 10.

Mike.

[Former Member]

Hi all,

I just found all the availables properties on the microsoft site :

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/owcvba11/html/ocobjWindow_HV0308161...

So if you want to use all the availables controls, they are given there.

For example, to hide the lines and colums headers, the scrollbar and the workbooks Tab, add to the code :

layout_sheet.DisplayVerticalScrollBar = false;

layout_sheet.DisplayWorkbookTabs = false;

layout_sheet.DisplayColumnHeadings = false;

layout_sheet.DisplayRowHeadings = false;

Hope this will help ...

[Former Member]

Hi Mike,

I just tried the code again and it worked fine. Try again with the following:

<script language="JavaScript" type="text/javascript">

  var layout_id = "LAYOUT4";
  layout_sheet = getBpsExcel( layout_id );
  if (layout_sheet && layout_sheet.ActiveSheet) { 
 
// OPEN Excel OWC FOR CHANGE
    layout_sheet.ActiveSheet.Protection.Enabled = false;
 
// A RANGE OF CELLS IS DECLERED (ROW,COL) TO (ROW,COL) IN YOUR EXCEL SHEET. 
 
    range = layout_sheet.Range(layout_sheet.Cells(10,1),layout_sheet.Cells(10,3));                                                          

 //CHANGE THE FONT SIZE HERE
    range.Font.Size  = 15 ;
 
//PROTECT THE SHEET AGAIN
    layout_sheet.ActiveSheet.Protection.Enabled = true;
  }

</script>

Good luck,

Rael

[Former Member]

Rael,

Thanks so much for the code. It looks like exactly what I need. I can also think of a ton of other uses for this. However, I cannot get it to work. I copied it directly to my web intface and put it directly below my layout. I only changed where you put LAYOUT_NAME to be the component name of my layout from the wib. I increased the range to be (1,1) to (100,100) to avoid any possible issue there. Any other tips??? What can I be missing?

Thanks again,

Mike.

[Former Member]

Hi,

If you are in BW 3.5, you can only change the header sizes using the 'styles'.

See note 830251.

If you are before 3.5, you can use stylesheets.

On top of you web interface customizing transaction, you will find a button to import the standard style sheet and change it.

If you are on 3.5, this stylesheet functionality does no longer work, it is replaced with styles.

Please award points if this helped.

Best Regards,

Filip Ledoux

[Former Member]

Hi Michael,

Place the following in the HTML=true text component(insert the name of your layout into the LAYOUT_NAME field:

<script language="JavaScript" type="text/javascript">
<!--
  var layout_id = "LAYOUT_NAME";
  layout_sheet = getBpsExcel( layout_id );
  if (layout_sheet && layout_sheet.ActiveSheet) { 

// OPEN Excel OWC FOR CHANGE
    layout_sheet.ActiveSheet.Protection.Enabled = false;

// A RANGE OF CELLS IS DECLERED (ROW,COL) TO (ROW,COL) IN YOUR EXCEL SHEET. 

    range = layout_sheet.Range(layout_sheet.Cells(1 ,1 
),layout_sheet.Cells(3 ,3 ));

//CHANGE THE FONT SIZE HERE
    range.Font.Size  =9 ;
 
//PROTECT THE SHEET AGAIN
    layout_sheet.ActiveSheet.Protection.Enabled = true;
  }
-->
</script>

You could also hit an individual cell using statements such as:

layout_sheet.Cells(1,1).value = 'ANY_TEXT';

Make sure you place your text component after your layout item in the WIB.

Good luck,

Rael

[andrea_previati]

Hi Michael

You can manipulate font size and so on in the third screen of layout builder. In Excel choose FORMAT --> Style > Modify> Font

After this you can drag and drop your layout with font modified in the WEB INTERFACE BUILDER

Hope it helps

Ciao

Andr

[Former Member]

Hi,

Unfortunately, that is exactly my problem. The formatting done in the layout builder does not appear in the web. Some of my formattting does. Column widths, freeze panes for example. But no font size.

Mike.