FORUM PLAN UPDATE
Date revision: This forum will remain open to new posts and responses until December 1, 2018. (After that date, you will still be able to view and search the forum.) Also, we're taking a second look at the best place to host future conversation. For now, keep using this forum, and stay tuned for more news.


Jul 27, 2016, 3:59 AM
2 Posts

MS Excel web export

  • Category: Domino Designer
  • Platform: Windows
  • Release: 9.0.1
  • Role: Developer
  • Tags: Excel Export
  • Replies: 3

Hi, 
Microsoft have just released a patch/fix to MS Excel that basically prevents web exports from opening. 


http://www.infoworld.com/article/3098898/microsoft-windows/excel-refusing-to-open-files-blame-the-kb-3115322-3115262-security-updates.html


This has caused no end of problems for us and a suite of some of our older modules. The code we're using is your bog standard way of exported from a website to Excel via a lotusscript agent called with a URL along the lines of "nameOfExportAgent?OpenAgent". Using print statements we perform the following....


    Print |Content-Type:application/vnd.ms-excel |
    Print |Content-Disposition:Attachment; filename="Report.xls"|


...and then build a table, looping around your data until complete, close the table off and the excel file is handled according to your browser of choice. It may download automatically or request you save it. Either way, when you attempt to open the file it fails with a blank screen.


The only workarounds we have found so far are as follows:

  • Open the file up in notepad, re-save it and close it. It will then open correctly in Excel as the untrusted signature is removed.
  • Using Windows - Control Panel - Internet Options - Security tab - trusted Sites - add the domain of the site that stores the code. This works for most browsers except Internet Explorer. 
  • remove the Security patch - Not really an option.

Has anyone else encountered this issue and can you advise any other workarounds? We're hoping for an option that doesn't require re-writing several pieces of exporting functionality.

Thanks

Jul 27, 2016, 10:30 AM
2 Posts
Solution...

8 hrs on from when I posted this, I've found a solution so if anyone else encounters the problem the solution is "YES" - you will need to re-write your agent(s) however it wasn't as bad as I thought. I've used spreadsheetML to export the data. Its slightly different to the raw html I was outputting but if your code is structured well enough you can use a find replace to replace your html columns with spreadsheetML cells etc... 

Print |Content-Type:application/vnd.ms-excel|
        Print |Content-Disposition:Attachment; filename=Report.xls| 
        Print |<?xml version="1.0" encoding="UTF-8"?>|
        
        Print |<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:o="urn:schemas-microsoft-com:office:office">
        <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
        <WindowHeight>10680</WindowHeight>
        <WindowWidth>19740</WindowWidth>
        <WindowTopX>-6015</WindowTopX>
        <WindowTopY>315</WindowTopY>
        <TabRatio>721</TabRatio>
        <ProtectStructure>False</ProtectStructure>
        <ProtectWindows>False</ProtectWindows>
        </ExcelWorkbook>|
        
 Print |<Styles>
            <Style ss:ID="normalCell">
                <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
                <Font ss:Color="#000000" ss:FontName="Arial" ss:Size="9" x:Family="Swiss"/>
            </Style>
            <Style ss:ID="headerCell"  ss:Parent="normalCell">
                <Font ss:Bold="1" ss:Color="#000000" ss:FontName="Arial" ss:Size="9" x:Family="Swiss"/>
            </Style>
        </Styles>|

Print |<Worksheet ss:Name="| + worksheetName + |">|

Print |<Table>| + _ 
            |<Column ss:Width="| + CStr(colWidth) + |"></Column>|

For x = 0 To UBound(dataStr,1)

       Print |<Row>
        For y = 0 To UBound(dataStr)
            Print |<Cell | + headerClass + |>| + _ 
            |<Data ss:Type="String">| + dataStr(y)+ |</Data>| + _ 
            |</Cell>|
        Next

      Print |</Row>|
    Next

Print |</Table>|

Print |</Worksheet>|

Print |</Workbook>|

 

Hope this helps

Aug 3, 2016, 1:06 PM
360 Posts
Wow. Thanks for this, Neil.

Maybe I'll use it as an example "How Microsoft upgrades require a 15x expansion in lines of code."

It's odd how, when Microsoft comes up with a nice enhancement, then they make it inexplicably complicated.

Aug 4, 2016, 9:57 AM
91 Posts
Probably would have been easier in XPages
You could create an XPage that uses the POI4XPages package from OpenNTF that generates the Excel file for you from a view.
https://poi4xpages.openntf.org/

Howard

FORUM PLAN UPDATE
Date revision: This forum will remain open to new posts and responses until December 1, 2018. (After that date, you will still be able to view and search the forum.) Also, we're taking a second look at the best place to host future conversation. For now, keep using this forum, and stay tuned for more news.