Create functionality to download item data to custom Excel file

Well sometimes users have a requirement to download data into excel file on a single action in Maximo application , for example item and related storeroom data  to excel file, formatted in certain way. I believe that implementing something like this might be interesting, so I decided to write down small step by step tutorial that will explain how this can be achieved in Maximo. First of all let’s see how excel file should look like:

  1. First sheet represents basic item data:

excel1

  1. Second sheet represents inventory information for that item (tab Storerooms in Item application)

excel2

 

We can start first by extending the application bean of the application and defining new method


public class ItemAppBeanExt extends ItemAppBean{
private String url;
private String folder;
private String template;

@Override
 protected void initialize() throws MXException, RemoteException {

   url = MXServer.getMXServer().getConfig().getProperty("custom.anjadev.downloadurl);
   folder = MXServer.getMXServer().getConfig().getProperty("custom.anjadev.tempfolder");
   template = MXServer.getMXServer().getConfig().getProperty("custom.anjadev.template");
   super.initialize();
}

public int EXPITM() throws RemoteException, MXException {
   MboRemote itemMbo = getMbo();
   String file = getMXSession().getUserName()+MXServer.getMXServer().getDate().getSeconds();
   clientSession.getRequest().getSession().setAttribute(("FILENAME", file);
   //generate excel file
   try{
      generateExcelFile(itemMbo, file);
   }catch(IOException e){
      Throw new MxApplicationException(“system”, “error”);
   }
   String options = "width=700,height=450,status=yes,resizable=yes,top=100,left=200,dependent=yes,alwaysRaised=no,modal=yes";

   //opens up new window pointing to specified url
   this.clientSession.getCurrentApp().openURL(url, true, null, options);
   return EVENT_HANDLED;
}

private void generateExcelFile(MboRemote item, String file) throws RemoteException, MXException, IOException {
   FileInputStream fis = null;
   FileOutputStream fos = null;
   try{
      fis = new FileInputStream(template);
      fos = new FileOutputStream(folder + file +".xls");

      XSSFWorkbook wb=new XSSFWorkbook(fis);
      Sheet itemSheet = wb.getSheetAt(0); 

      Row row = itemSheet.getRow(0);
      Cell cell = row.createCell(1);
      cell.setCellValue(item.getString("ITEMNUM"));
      row = itemSheet.getRow(1);
      cell = row.createCell(1);
      cell.setCellValue(item.getString("DESCRIPTION"));
      row = itemSheet.getRow(2);
      cell = row.createCell(1);
      cell.setCellValue(item.getString("STATUS"));

      MboSetEnumeration mse = new MboSetEnumeration(item.getMboSet("INVENTORY"));
      Sheet inventorySheet = wb.getSheetAt(1);
      int i =0;
      while(mse.hasMoreElements()){
         MboRemote inventory = mse.nextMbo();
         row = inventorySheet.createRow(i++);
         cell = row.createCell(0);
         cell.setCellValue(inventory.getString("LOCATION"));
         cell = row.createCell(1);
         cell.setCellValue(inventory.getString("BINNUM"));
         cell = row.createCell(2);
         cell.setCellValue(inventory.getString("STATUS"));
         cell = row.createCell(3);
         cell.setCellValue(inventory.getDouble("INVCOST.AVGCOST"));
         cell = row.createCell(4);
      }
      wb.write(fos);
   }catch (Exception e) {
      throw new MXApplicationException("expexc", "error_on_excel_export")
   }finally{
      if(fis!=null){
         fis.close();
      }
      if(fos!=null){
         fos.close();
      }
    }
  }
}

Now let’s analyze this code above.

In initialize method of application bean we are initializing couple of variables from Maximo system properties (Usually it is nice to have them as system properties so that you can change them easily without restarting application):

  • url – string value that will point to servlet that will be used  serve generated excel file (i.e. http://localhost/maximo/donloadexc)
  • folder – path on the system (i.e. d:/temp) where code above will generate excel file with populated data
  • template – string value of “template” excel file that is described at the beginning of this article (i.e. c:/template/template.xls). System will read this template and populate data and then write new file in folder

 

Now in method EXPITM, first we are fetching item mbo that we will use to export data into excel file, then generating file name of the new excel file that system will create for user and also that name we are putting in the session (use of putting this value in session we will see later when we develop servlet to download the file that system will create). Next step is to generate file by calling method generateExcelFile and finally  last few lines are enabling  system to open  on user side new pop-up window that will route user to the file for download.

If you take a look at the code for method generateExcelFile you will see that code is using POI library for generating excel files (POI library is part of standard Maximo and more details on POI library can  be found on: APACHE POI). It is just worth mentioning over there we are taking first excel worksheet and populating it with information from the item itself and for second worksheet we are taking INVENOTY data set to iterate and populate excel file lines with storeroom data.

This is first part of the solution and now it is time to implement a servlet that will handle downloading generated file that we have just created in above bean. So let’s start with creating the servlet (this class is part of maximouiweb module in Maximo – to be placed in $MAXIMO_HOME/maximo/applications/maximo/maximouiweb/webmodule/WEB-INF/classes folder):


public class DownloadExcelServlet extends HttpServlet{

   private static final int BUFSIZE = 4096;

   @Override
   protected void doGet(HttpServletRequest req, HttpServletResponse resp)
      throws ServletException, IOException {

   @Override
   protected void doPost(HttpServletRequest req, HttpServletResponse resp)
      throws ServletException, IOException {

      File file = null;
      DataInputStream in=null;
      ServletOutputStream outStream = null;
      try{
         //check if user has session
         MXSession mxSession = (MXSession) req.getSession().getAttribute("MXSession");
         if (mxSession == null) {
            return;
         }else if(!mxSession.isConnected()){
           return;
         }

         String tempFolder = MXServer.getMXServer().getConfig().getProperty("custom.anjadev.tempfolder");
         String fileName = (String)req.getSession().getAttribute("FILENAME");
         //check if file from attribute consists of numbers only - we do not want some security issue to arise 🙂
         Pattern p = Pattern.compile("[0-9]{5,50}");
         Matcher m = p.matcher(fileName);
         if(!m.matches()){
           return;
         }

         file = new File(tempFolder + fileName + extension);
         //get output stream to write file
         outStream = resp.getOutputStream();
         ServletContext context = getServletConfig().getServletContext();
         String mimetype = context.getMimeType(file.getName());
         // sets response content type
         if (mimetype == null) {
            mimetype = "application/octet-stream";
         }
         resp.setContentType(mimetype);
         resp.setContentLength((int)file.length());
         // set HTTP header
         resp.setHeader("Content-Disposition", "attachment; filename=\"" + fileName+ extension + "\"");

         byte[] byteBuffer = new byte[BUFSIZE];
         in = new DataInputStream(new FileInputStream(file));
         int length = 0;
         // reads the file's bytes and writes them to the response stream
         while ((in != null) && ((length = in.read(byteBuffer)) != -1)){
            outStream.write(byteBuffer,0,length);
         }
         in.close();
         outStream.close();

     }catch (Exception e) {

     }finally{
        if(in!=null){
           in.close();
        }
        if(file!=null){
            file.delete();
        }
       if(outStream!=null){
          outStream.close();
       }
    }
}

Looking at this servlet, you can see that it is doing simple task, taking file name from the session (same attribute that we have set up in code of ItemAppExtBean – method EXPITM) and reading that file from the location specified as a system property in Maximo. Rest of the code is setting up header attributes and writing file to the output stream of response object of servlet. Finally at the end servlet just closes the streams and deletes file after it is sent.

Now when we have all our classes finalized, we just need to register servlet in web.xml of a maximouiweb module and to configure application to call method we have implemented in application bean. First let’s see how registerng bean into web.xml looks like, first register servlet:

<pre class="codeBox"><servlet>
    <servlet-name>downloadExcelServ</servlet-name>
    <servlet-class>com.anjadev.servlet.DownloadExcelServlet</servlet-class>
</servlet>

Thank map servlet to proper url pattern:

<pre class="codeBox"><servlet-mapping>
    <servlet-name>downloadExcelServ</servlet-name>
    <url-pattern>/ui/downloadexcl</url-pattern>
</servlet-mapping>

Now to configure application Item to be able to call this method. First we should change application bean on the application in Application Designer, then in same application create  signature option and create new option in Select Action Menu. What is important is that signature option has a same name as method we defined in the application bean so that Maximo framework can call it. At the end final step is that you grant this signature option to proper security group in Maximo.

Well I hope you have found this article interesting and I shall try to come up with more of them. Have a nice day …

 

Posted in IBM Maximo Tagged with: , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe for Newsletter

Categories