Gridlines for Printing Excel from a Web Agent

Have you ever noticed that Microsoft Excel content printed from a Web Agent usually doesn’t have gridlines. You can put borders around your data by adding border=1 (or whatever) but it still doesn’t show the default gridlines.

The is the only way I have found to add gridlines is by adding some Microsoft XML settings in the header. I found the code on one of those “Expert Exchange” forums but it’s almost impossible to link to it without paying the fee.

Here is the code. I return the full header in a string. You really only need to pay attention to what is between the <head> tags.

    Public Function getHeader() As String
        getHeader=  |Content-Type:application/vnd.ms-excel| & Chr(13) & Chr(10)
        getHeader = getHeader &  |Content-Disposition: attachment; filename= | &"D" & Cstr(Right(Rnd(),4)) & |.xls| &Chr(13) & Chr(10)
        getHeader = getHeader  &   "<html xmlns:x=""urn:schemas-microsoft-com:office:excel"">"
        getHeader = getHeader  &   "<head>"
        getHeader = getHeader  &   "<!--[if gte mso 9]><xml>"
        getHeader = getHeader  &   "<x:ExcelWorkbook>"
        getHeader = getHeader  &   "<x:ExcelWorksheets>"
        getHeader = getHeader  &   "<x:ExcelWorksheet>"
        getHeader = getHeader  &   "<x:Name>"& "Export" &"</x:Name>"
        getHeader = getHeader  &   "<x:WorksheetOptions>"
        getHeader = getHeader  &   "<x:Print>"
        getHeader = getHeader  &   "<x:ValidPrinterInfo/>"
        getHeader = getHeader  &   "</x:Print>"
        getHeader = getHeader  &   "</x:WorksheetOptions>"
        getHeader = getHeader  &   "</x:ExcelWorksheet>"
        getHeader = getHeader  &   "</x:ExcelWorksheets>"
        getHeader = getHeader  &   "</x:ExcelWorkbook>"
        getHeader = getHeader  &   "</xml>"
        getHeader = getHeader  &   "<![endif]--> "
        getHeader = getHeader  &   "</head>"
    End Function

Yes… I know Julian has a great StringBuffer class. I will use it at some point.

12 February 2008 | lotus, programming | Comments

3 Responses to “Gridlines for Printing Excel from a Web Agent”

  1. 1 Michael Malewicki 13 February 2008 @ 9:50 am

    I have been creating a lot of Excel reports using XML via LotusScript. I found the easiest way to figure out an XML equivalent setting is to save as XML an Excel spreadsheet before and after changing a setting. I then compare the differences.

    Also, it looks like adding the following line might be enough for the gridlines to show:

    Similarly, to remove the gridlines, you could add a element.

  2. 2 Michael Malewicki 13 February 2008 @ 9:52 am

    Looks like the XML got stripped out:

    To show the gridlines:

    A “WorksheetOptions” element.

    To remove the gridlines:

    A “DoNotDisplayGridlines” element.

  3. 3 tom 13 February 2008 @ 10:06 am

    That’s a great idea (the saving of spreadsheets in XML to the elements).

    Thanks for the tip!

Comments:

  1.  
  2.  
  3.