Remote Excel Dataset

  • Last update:December 04, 2023
  • 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.

     1.png

    Creating a JSP File

    iconNote:
    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.

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

     2.png

    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.

     3.png

    Effect Display

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

    4.png

    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.


    Attachment List


    Theme: Data Preparation
    • Helpful
    • Not helpful
    • Only read

    滑鼠選中內容,快速回饋問題

    滑鼠選中存在疑惑的內容,即可快速回饋問題,我們將會跟進處理。

    不再提示

    10s後關閉

    Get
    Help
    Online Support
    Professional technical support is provided to quickly help you solve problems.
    Online support is available from 9:00-12:00 and 13:30-17:30 on weekdays.
    Page Feedback
    You can provide suggestions and feedback for the current web page.
    Pre-Sales Consultation
    Business Consultation
    Business: international@fanruan.com
    Support: support@fanruan.com
    Page Feedback
    *Problem Type
    Cannot be empty
    Problem Description
    0/1000
    Cannot be empty

    Submitted successfully

    Network busy