[the.root]

Hello,

So previously I've been exporting everything to xls and was working fine, however I started needing more then the 65k rows. Trying to use xlsx, just as a simple test, and I cannot get it to work. I was apparently using the older API before with the BookModelImpl book.getReportParameterCollection() function, and it worked.. For example this is a working class :

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import com.f1j.ss.*;

public class DeptStatistics extends HttpServlet {
public void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException,
java.io.IOException {
/*********************************************************
* Tell the browser we are sending an Excel file
*********************************************************/
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition","attachment;filename=report.xls");
ServletOutputStream out = response.getOutputStream() ;
/*********************************************************
* Create a new workbook object and make it thread-safe
*********************************************************/
BookModelImpl book = new BookModelImpl();
book.getLock();
try {
/*********************************************************
* Populate the workbook from a spreadsheet report design
*********************************************************/
String pathToTemplate = "/tmp/book02.sod";
String ppid = request.getParameter("RP_propertyid");
book.read(pathToTemplate, new ReadParams());
book.getReportParameterCollection().get("RP_propertyid").setValueEx(ppid);
book.getDataSourceCollection().refresh();
/********************************************************
* Output the workbook to the output stream
********************************************************/
book.write(out, new WriteParams(book.eFileExcel97));
out.close();
}
catch(Throwable e) {
System.out.println(e.getMessage());
}
finally {
/********************************************************
* Unlock the workbook so other threads can access it
********************************************************/
book.releaseLock();
}
}
}

However, the only way I can find to export to excel xlsx (there is no xlsx/xml equivalent for book.eFileExcel97 that i could find), is to use the newer api with document.(). So I have this as a working test (not passing any parameters) :

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import com.f1j.ss.*;
import com.f1j.data.source.JDBC;
import com.f1j.data.DataQueryCollection;
import com.f1j.data.query.JdbcQuery;

public class test6 extends HttpServlet
{
  private static final long serialVersionUID = 1L;

  public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
  {
    response.setHeader("Content-Disposition", "attachment; filename=report.xlsx");
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    ServletOutputStream out = response.getOutputStream();
    Document d_doc = null;
    try{
      d_doc = new Document(null, new File("/tmp/book02.sod"), new DocumentOpenCallback());
      d_doc.getLock();
// Pass the region parameter provided by the user
      String ppid = request.getParameter("RP_propertyid");
// Output the new Excel file to the browser:
      d_doc.fileSaveCopyAs(out, DocumentType.OPEN_XML_WORKBOOK, new DocumentSaveCallback());
    }
    catch (Throwable e)    {
      System.out.println(e.getMessage());
    }
    finally    {
    out.close();
    if (d_doc != null) d_doc.releaseLock();
    }
  }
}

Now the question is how can I pass a parameter to it? I cant figure out how to use the updated getReportParameterCollection and getDataSourceCollection classes I guess.

If you couldn't tell i'm not much of a java programmer, lol. I'm sure this is a simple question for someone out there, and your help would be very appreciated.

Thanks!

[the.root]

Ahh Alright, well I guess i figured out the answer myself shortly after posting lol. Hopefully this will help someone else out some day looking for a simple class to export to xlsx w/ parameters from a sod file.

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import com.f1j.ss.*;
import com.f1j.data.source.JDBC;
import com.f1j.data.DataQueryCollection;
import com.f1j.data.query.JdbcQuery;

public class test extends HttpServlet
{
  private static final long serialVersionUID = 1L;

  public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
  {

//tell the browser were sending a xlsx file
    response.setHeader("Content-Disposition", "attachment; filename=Report.xlsx");
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    ServletOutputStream out = response.getOutputStream();

//declare document and open sod w/ a lock
    Document d_doc = null;
    try{
      d_doc = new Document(null, new File("/tmp/Book1.sod"), new DocumentOpenCallback());
      d_doc.getLock();

//set parameters
      String ppid = request.getParameter("property");

//get data source connection
      JDBC jdbc_ds = (JDBC) d_doc.getBook().getDataSourceCollection().find("source1");

// Get the query from the data source:
      DataQueryCollection dqc = jdbc_ds.getDataQueryCollection();
      JdbcQuery jdbcQuery = (JdbcQuery) dqc.find("set1");

// Set the parameter value:
      jdbcQuery.getParameterCollection().getNamedParameter("property").setValue(ppid);

//refresh the connection w/ parameters
      d_doc.getBook().getDataSourceCollection().refresh();

// Output the new Excel file to the browser:
      d_doc.fileSaveCopyAs(out, DocumentType.OPEN_XML_WORKBOOK, new DocumentSaveCallback());
    }
    catch (Throwable e)    {
      System.out.println(e.getMessage());
    }
    finally    {
    out.close();
    if (d_doc != null) d_doc.releaseLock();
    }
  }
}

One thing I've noticed is this only passes the parameters to the query/procedure/dataset. it wont pass view-time parameters that aren't used in query. IE I had a parameter that was a column header reflected the name of whatever was passed through that parameter. I haven't figured out how to get that working with the newer api yet.. IE, this used to work :

book.getReportParameterCollection().get("RP_propertyid").setValueEx(ppid);

It would actually replace the view/run-time parameter with that value, instead of just replacing it in the query, so if that parameter was referenced directly in the sheet - it'd pull it correctly.

If anyone has any suggestions i'd be interested.