Excel Datasources II

Yesterday I introduced the Excel APIs for reading binary Excel file and generating XML output for reporting consumption ... of course you could take that XML and load it into transactional tables et voila you have a data entry method via Excel, you could even build a macro in Excel to call the java API to allow the user to enter data through Excel and push a button to submit the data ... I feel another blog entry coming on :o)


Today thou, its reporting and how we can use the Excel API with the BIP Server and create an 'Excel Datasource'. At this point I should mention that the data templates will, at some point in the furture incorporate the Excel APIs so you will not need to do the following but its not that tough to do if you can not wait.


To serve or not ?


Those of you that know the BIP Enterprise server know that it can use an HTTP data source i.e. it can contact a datasource via a URL or it can access a static file from the file system. You have choices, either set up a daemon service that looks for updated excel files in a specific directory and then calls a java class to convert the xsl to xml that the BIP Server can then use as a datasource or ... and this is what Im about to show you. Build a servlet that accepts a filename, looks for that file in a directory, converts the contents to XML and then streams the result back to the server in real time. What to do? Well, its going to come down to how much data is in your Excel file and how quickly the servlet can serve up the data ... experiment until you're happy.


We're serving ...


OK, we decided to use a servlet, here's the cut down version with the pertinent parts, with comments inline:


public class xlsdata extends HttpServlet {
    private static final String CONTENT_TYPE = "text/xml; charset=windows-1252";

    public void init(ServletConfig config) throws ServletException {
        super.init(config);
    }

    public void doGet(HttpServletRequest request,
                      HttpServletResponse response) throws ServletException, IOException
{response.setContentType(CONTENT_TYPE);
        ServletContext context = request.getSession().getServletContext();
        // Remember the API needs a temporary directory to play in
        final Object TMPDIR = context.getAttribute("javax.servlet.context.tempdir");
        System.out.println(TMPDIR.toString());
        // The URL will have a paramter on it called xlsfile containing the name of
// the Excel file minus the extension
        String xlsFile = "/"+ request.getParameter("xlsfile");
       // The excel files will reside in a working directory called 'excel' on the server
        String WrkDir = context.getRealPath("excel");
        // Call the API to convert the xls to xml
        Excel2Data xls2Data = new Excel2Data();
        xls2Data.setOutputType(Excel2Data.OUTPUT_TYPE_XML);
        xls2Data.setConfig("system-temp-dir",TMPDIR.toString());
        try
        {
            xls2Data.loadExcel(WrkDir+xlsFile+".xls");
        // Write the xml to the file system - you could equally stream it straight back
            xls2Data.generate(WrkDir+xlsFile+".xml");            
        }
        catch (XDOException e)
        {
            e.printStackTrace();
        }
        catch (IOException exc)
        {
           exc.printStackTrace();
        }
        System.out.println("File processed");
        PrintWriter out = response.getWriter();
       // Read the XML and stream it back to the caller
        InputStream xmlFile = new FileInputStream(WrkDir+xlsFile+".xml");
        try{
            int ch;
            while ( (ch = xmlFile.read()) != -1 ) {
                 out.print( (char)ch );
            }
         } catch (Exception e) {
                 System.out.println("Could not find output file");
                 e.printStackTrace();
         } finally {
                 if( xmlFile != null ) {
                   xmlFile.close();
                 }
         } 
        out.close();
    }
}


The full source code is here.

Hooking Up


Now we need to hook up the servlet to a report in the BIP Server

ExcelDS1:


Its an HTTP datasource and we created the parameter to hold the Excel file name. Doing it this way we can push any Excel we wish to the servlet and get XML back. We created a hidden parameter to hold the Excel file name:

ExcelDS2:

Notice the default value is set to "Customers", the name of the Excel file to be processed. Once the data is set up its a case of building a template to format the output and we're done. We now have an Excel file as a data source that can be updated by users and snapshot reports taken from it at will. So we can take an Excel datasource:

ExcelDS3:

and convert it into a report.

ExcelDS4:

Now there are plugins to Excel that can do this for you of course, but how about if you used the Excel data to supplement a SQL based data source, thats where the power of the APIs becomes really useful. Happy Code comes from Happy Coders ... Got Code?

Comments:

how can build a macro in Excel to call the java API to allow the user to enter data through Excel and push a button to submit the data ... I feel another blog entry coming on :o) can u explain to me how to call servlet through Excel sheet macro

Posted by Rajiv Sahu on August 20, 2008 at 06:22 PM MDT #

Great share :)

Posted by t shirt tryck on August 18, 2010 at 05:18 AM MDT #

Excellent job.

Posted by TX on September 23, 2010 at 01:46 AM MDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today