Job Costing #11 – Loading the Excel Files

This post is part of a series on an ongoing project. Today I’m going to explore calling the legacy code from my Kotlin Spring controller. This will focus on calling the code that performs the analysis of the three input files and produces the output that will be sent back to the frontend.

getTimeSheetData()

The first step in analyzing the files is to load the Excel files via the Apache POI library. The existing code has three extremely similar functions. The function for loading the time report spreadsheet is shown below, but the other two functions are extremely similar.

   public static final TimeSheetData getTimeSheetData( Component parent, File file, Warnings warnings ) throws Exception
   {
      XSSFWorkbook workbook = openWorkbook( parent, file, "Time Sheet" );

      if( workbook == null )
         return null;
      else
         return TimeSheetData.compile( workbook, Context.MATH_CONTEXT );
   }

The problem here is the ‘parent’ parameter. That is a Component class which is Java Swing, so I’ll need to remove it from the code. Peeking into the openWorkbook() function, the ‘parent’ parameter is used to create a dialog box to prompt the user for a password, in the event that the spreadsheet is password protected.

openWorkbook()

In the new app, the user is going to type the password in the page before any of this code is called. So I’ll need to rework this code to take the password as a parameter that is passed in. Let’s take a look at the existing openWorkbook() function.

   public static final XSSFWorkbook openWorkbook( Component parent, File file, String name ) throws Exception
   {
      try
      {
         NPOIFSFileSystem fs = new NPOIFSFileSystem( file );
         EncryptionInfo ei = new EncryptionInfo( fs );
         Decryptor d = Decryptor.getInstance( ei );

         try
         {
            d.verifyPassword( promptUserForPassword( parent, name ) );
            return new XSSFWorkbook( d.getDataStream( fs ) );
         }
         catch( java.security.InvalidKeyException e )
         {
            JOptionPane.showMessageDialog( parent, "You have not entered the correct password.", "Incorrect Password", JOptionPane.ERROR_MESSAGE );
         }
      }
      catch( OfficeXmlFileException e )
      {
         OPCPackage pkg = OPCPackage.open( file );
         return new XSSFWorkbook( pkg );
      }
   }

The code defaults to trying to load the spreadsheet as an encrypted file. In the case that it is not encrypted, an OfficeXmlFileException is thrown, which the code catches and then opens the spreadsheet without password protection. If the spreadsheet is encrypted, then the code flows in to the promptUserForPassword() function, which uses the ‘parent’ parameter.

The above code would allow any (or none) of the three spreadsheets to be password protected, however that’s a feature that isn’t required for this project. A project assumption is that only the employee cost spreadsheet will be encrypted.

New openWorkbook()

So I’m going to rework this code to provide two functions. One that opens a spreadsheet without password protection, and a different function that takes a password and opens an encrypted spreadsheet. I think this simplifies the code and keeps it more self-explanatory. Here’s what I end up with.

   public static final XSSFWorkbook openWorkbook( File file ) throws Exception
   {
      return new XSSFWorkbook( OPCPackage.open( file ) );
   }
   
   public static final XSSFWorkbook openEncryptedWorkbook( File file, String password ) throws Exception
   {
      NPOIFSFileSystem fs = new NPOIFSFileSystem( file );
      EncryptionInfo ei = new EncryptionInfo( fs );
      Decryptor d = Decryptor.getInstance( ei );

      d.verifyPassword( password );
      return new XSSFWorkbook( d.getDataStream( fs ) );
   }

The first function simply opens an unencrypted spreadsheet in one line. The second function, openEncryptedWorkbook(), takes the password as a parameter and uses it to open the spreadsheet.

I was also able to eliminate the ‘name’ parameter as it was only used to construct an Exception message. The openEncryptedWorkbook() function will throw an exception if the password is wrong, but this code will leave the creation of any user display message to the calling code.

Controller/Legacy Bridge

On that note, let’s turn to the calling code. I’ve created a new Kotlin file, named ‘controllerLegacyBridge.kt’ which is a bridge between the REST controller code and the legacy code from the Java Swing app. It’s job is to hide the details of the legacy code from the controller and vice versa.

It defines a function, analyzeTime(), that takes the data from the REST request and calls the legacy code to load the three Excel files. For now it still returns the test response, but I’ll change that shortly.

fun analyzeTime(files: AnalyzeFiles, params: AnalyzeParams?, logger: Logger): Response {
    return try {
        tryToAnalyzeTime(files, params)
    } catch(e: java.security.InvalidKeyException) {
        logAndReturnErrorDueToException("Invalid password", e, logger)
    }
}

private fun tryToAnalyzeTime(files: AnalyzeFiles, params: AnalyzeParams?): Response {
    val warnings = Warnings()

    val projectSheet = TimeTracking.getProjectSheet(getFile(files.projectSheet), warnings)
    val employeeCost = TimeTracking.getCostSheet(getFile(files.employeeCost), warnings, files.employeeCostPassword)
    val timeReport = TimeTracking.getTimeSheetData(getFile(files.timeReport), warnings)

    return testAnalyzeTime(files, params).toResponse()
}

And this means that the controller code is now very simple. Let’s take a quick look at it below.

    @RequestMapping("/jobcost/analyze")
    @CrossOrigin(origins = ["http://localhost:3000"])
    fun handleAnalyze(@RequestBody req: AnalyzeRequest): Response {
        logger.log(Level.INFO, "GET /jobcost/analyze\n\tfiles = ${req.files}\n\tparams = ${req.params}")

        return analyzeTime(req.files, req.params, logger)
    }

That’s pretty simple for a Spring REST controller!

Creating a (Partially) Real Response

Next, I’ll focus on building a real response to the analyze data request, instead of using the test one that the current code produces. Let’s go back to tryToAnalyzeTime(). Most of the changes here are specific to the core business logic of the legacy Swing app, so the specifics aren’t that important.

private fun tryToAnalyzeTime(files: AnalyzeFiles, params: AnalyzeParams?): Response {
    val warnings = Warnings()

    val projectSheet = TimeTracking.getProjectSheet(getFile(files.projectSheet))
    val employeeCost = TimeTracking.getCostSheet(getFile(files.employeeCost), files.employeeCostPassword)
    val timeReport = TimeTracking.getTimeSheetData(getFile(files.timeReport))
    val context = createContext(timeReport, params)

    val analyzedData = TimeTracking.analyzeData(timeReport, employeeCost, projectSheet, context, warnings)
    val costMap = CostMap.create(projectSheet, analyzedData, context, warnings)

    return AnalyzeResponse(
        createParams(timeReport, context, params?.journalEntryDate),
        testOutputText(),
        testOutputFile(files)
    ).toResponse()
}

The interesting part is the return value. Instead of returning a test analyze response, I return an AnalyzeResponse object that is partially real data and partially test data. The ‘params’ portion is real data, and the two output portions are test data. The text output is a larger topic than I have room for in today’s post, so I’ll wrap up today’s post with the params.

private fun createParams(timeReport: TimeSheetData, context: Context, journalEntryDate: String?): AnalyzeParams {
    return AnalyzeParams(
            timeReport.earliestDate.toJson(),
            timeReport.latestDate.toJson(),
            context.targetHours.toInt(),
            context.lowHours.toInt(),
            context.highHours.toInt(),
            journalEntryDate ?: timeReport.latestDate.toJson()
    )
}

There’s nothing too complicated going on here, simply getting the parameter values from the spreadsheet data and constructing the object. The fun part here is the extension function I’ve defined on the standard Java Calendar class. Extension functions are a neat feature in Kotlin that allows you to write new functions on existing classes that you can’t otherwise modify. While they aren’t necessary here, I think it keeps the code clean and the intention clear. Let’s take a quick look at the extension function toJson().

private val dateFormatter = SimpleDateFormat("yyyy-MM-dd")

private fun Calendar.toJson(): String {
    return dateFormatter.format(this.time)
}

What’s Next?

Next up, I’ll turn to returning valid output in the response. This will involve changes to the Warnings class to generate HTML output. Until next time!