This forum is closed to new posts and responses. Individual names altered for privacy purposes. The information contained in this website is provided for informational purposes only and should not be construed as a forum for customer support requests. Any customer support requests should be directed to the official HCL customer support channels below:

HCL Software Customer Support Portal for U.S. Federal Government clients
HCL Software Customer Support Portal



Apr 18, 2011, 3:53 PM
5 Posts

Using Template with Excel Export

  • Category: Other
  • Platform: All
  • Release: 8.5.2
  • Role:
  • Tags:
  • Replies: 3
Hello all,
 
I have the following code that takes a view and exports it to excel.
What I want to know...is there a way to open an Excel Template and then export the data from Lotus Notes there?
 
Here is the code:

var exCon = facesContext.getExternalContext();
      var writer = facesContext.getResponseWriter();
      var response = exCon.getResponse();
      var projects:NotesView = database.getView('ExcelReportChargeBackReportbySession')
      var viewNav:NotesViewNavigator = projects.createViewNav();
      var viewEnt:NotesViewEntry = viewNav.getFirst();
      var output:string = "";
      while (viewEnt != null)
      {
       output += "<tr>";
       for (var x in viewEnt.getColumnValues())
       {
            output += "<td>" + x + "</td>"
       }
           output += "</tr>";  
           viewEnt = viewNav.getNext(viewEnt);
       }
      response.setContentType("application/vnd.ms-excel");
      response.setHeader("Content-disposition", "attachment; filename=ChargeBackReportbySession.xls")
      response.setHeader("Cache-Control", "no-cache");
      writer.write("<table>");
      writer.write("<thead><tr>");
      writer.write("<td><b>Course Name</b></td>");
      writer.write("<td><b>Session ID</b></td>");
      writer.write("<td><b>Course Title</b></td>");
      writer.write("<td><b>Employee Last Name</b></td>");
      writer.write("<td><b>Employee First Name</b></td>");
      writer.write("<td><b>Employee WIN ID</b></td>");
      writer.write("<td><b>Employee Position Title</b></td>");
      writer.write("<td><b>Employee Department Name</b></td>");
      writer.write("<td><b>Employee Region</b></td>");
      writer.write("</tr></thead>");
      writer.write(output);
      writer.write("</table>");
      writer.endDocument();
Apr 19, 2011, 9:41 PM
57 Posts
Re: Using Template with Excel Export
 


we can generate excel from domino in this way
 
domino data ==> XML ==> XMLSS (with XSTL template that you mention) 
Apr 21, 2011, 10:25 AM
57 Posts
Re: Using Template with Excel Export
 
I could not find a good template which fited my need on google when I wrote that, since all examples is for conversion from xmlss to xml , not the other way around, I learned some of the syntax to write my own, you can take a look at mine

String xslString = "<?xml version=\"1.0\" encoding=\"utf-8\"?>"
                + "<xsl:stylesheet version=\"1.0\" xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:xsl=\"http://www.w3.org/1999/XSL/Transform\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">"
                + "<xsl:template match=\"/\"><xsl:processing-instruction name=\"mso-application\">progid=\"Excel.Sheet\"</xsl:processing-instruction><Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:html=\"http://www.w3.org/TR/REC-html40\"><xsl:for-each select=\"root\"><xsl:apply-templates/></xsl:for-each></Workbook></xsl:template><xsl:template match=\"root/*\">"
                + "<Worksheet><xsl:attribute name=\"ss:Name\"><xsl:value-of select=\"local-name()\"/></xsl:attribute><Table x:FullColumns=\"1\" x:FullRows=\"1\"><Row><xsl:for-each select=\"*[position() = 2]/*\"><Cell><Data ss:Type=\"String\"><xsl:value-of select=\"local-name()\"/></Data></Cell></xsl:for-each></Row><xsl:for-each select=\"./*\"><Row><xsl:for-each select=\"*\"><Cell><Data ss:Type=\"String\"><xsl:value-of select=\".\"/></Data></Cell></xsl:for-each></Row></xsl:for-each>"
                + "</Table></Worksheet></xsl:template></xsl:stylesheet>";
 
 
some of the words in the template are my nodes names in XML, 
 
-------XML source-----------
<?xml version="1.0" encoding="UTF-8"?><root>
  <Atlas1>
    <row>
      <Subject/>
      <Title>titleA</Title>
      <ContentType>contentA</ContentType>
    </row>
    <row>
      <Subject>subjectB</Subject>
      <Title>titleB</Title>
      <ContentType>contentB</ContentType>
    </row>
    <row>
      <Subject>subjectC</Subject>
      <Title>titleC</Title>
      <ContentType>contentC</ContentType>
    </row>
  </Atlas1>
  <Atlas2>
    <row>
      <Subject/>
      <Title>titleA</Title>
      <ContentType>contentA</ContentType>
    </row>
    <row>
      <Subject>subjectB</Subject>
      <Title>titleB</Title>
      <ContentType>contentB</ContentType>
    </row>
    <row>
      <Subject>subjectC</Subject>
      <Title>titleC</Title>
      <ContentType>contentC</ContentType>
    </row>
  </Atlas2>
  <Atlas3>
    <row>
      <Subject/>
      <Title>titleA</Title>
      <ContentType>contentA</ContentType>
    </row>
    <row>
      <Subject>subjectB</Subject>
      <Title>titleB</Title>
      <ContentType>contentB</ContentType>
    </row>
    <row>
      <Subject>subjectC</Subject>
      <Title>titleC</Title>
      <ContentType>contentC</ContentType>
    </row>
  </Atlas3>
</root>

This forum is closed to new posts and responses. Individual names altered for privacy purposes. The information contained in this website is provided for informational purposes only and should not be construed as a forum for customer support requests. Any customer support requests should be directed to the official HCL customer support channels below:

HCL Software Customer Support Portal for U.S. Federal Government clients
HCL Software Customer Support Portal