Overview
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.
Defining a Remote Excel File Dataset
Preparing an Excel File
Create an Excel file named test.xlsx, as shown in the following figure.
Creating a JSP File
Note:
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:
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.