Remote Excel Dataset

Sometimes you may need to call Excel datasets on other servers.

In this case, you can call Excel files through JSP, access the JSP through a URL, and call Excel files on other servers as datasets.

Preparing an Excel File

Create an Excel file named test.xlsx, as shown in the following figure.

Creating a JSP File

Note:

Use English for the JSP file name. Otherwise, the connection will fail.

Create a JSP file named download.jsp. The code is shown as follows.

<%@ page contentType="application/vnd.ms-excel" language="java" import="java.io.*" pageEncoding="UTF-8"%><%    response.reset();  <%Optional.%>     response.setContentType("application/x-download");        <%Set an id parameter and store the needed file with the id as the file name like test.xlsx.;%>String fileid=request.getParameter("id");<%Set field to an empty string if you do not set an id parameter, allowing the system only to read the file test.xlsx.%>if(fileid==null) fileid="";<%Enter the actual file storage path (whether a relative path or an absoluate path in the disk of the server, like c:/test.xlsx) and the file name.;%>    String path = request.getRealPath("/")+"test"+fileid+".xlsx";   <%String path = "c:/a.xlsx";  %>  <%(Optional) Enter the downloaded file name.%>  String downloadname = "abc";    response.addHeader("Content-Disposition","attachment;filename=" + downloadname);     <%Define the input/output stream to allow the system to read files.%>   java.io.OutputStream outStream = null;    java.io.FileInputStream in = null;    try    {    outStream = response.getOutputStream();    in = new FileInputStream(path);    byte[] b = new byte[1024];    int i = 0;     while((i = in.read(b)) > 0)    {    outStream.write(b, 0, i);    }  <%Output the buffer forcibly.%>outStream.flush();  <%Update the out object.%>out.clear();  out = pageContext.pushBody(); }    catch(Exception e)    {    System.out.println("Error!");    e.printStackTrace();    }    finally    <%Release files.%>  {    if(in != null)    {    in.close();    in = null;    }   }  %>

Place the Excel file and JSP in the project folder like apptest under Tomcat, as shown in the following figure.

Note:

 JSP files are supported only on servers such as Tomcat and WebLogic.

Creating a File Dataset

Create a file dataset in the designer, set File Type to Excel, select URL, and enter the access URL of the file download.jsp, like http://www.finereporthelp.com:8181/apptest/download.jsp.

Select Contain Column Heading in First Row as needed and click Preview, as shown in the following figure.

Effect Display

After completion, a dataset named File1 is added to Template Dataset, as shown in the following figure.

A remote Excel dataset is created and can be used normally.

Notes

If you need to process multiple Excel files through the file download.jsp, you can use the id parameter to process different Excel files corresponding to different IDs in JSP.

You can obtain the dataset of the file test1.xlsx through the URL http://www.finereporthelp.com:8181/apptest/download.jsp?id=1 (provided that this Excel file exists, otherwise it fails), obtain the dataset of the file test2.xlsx through the URL http://www.finereporthelp.com:8181/apptest/download.jsp?id=2, and so on.