This post is about how to Transforming .xlsx file

A local architecture firm contacted me about a small software automation project.

One of their project management applications, Deltec was exporting data to a spreadsheet.

Elsewhere in the organization, a copy of that spreadsheet was being altered and manually entered into a spreadsheet used for managing project spend, projections, and staffing.

Problem

When the source data is updated, the downstream reporting artifacts also need to be updated.

Solution

First, we must understand the issue, and then take one or more steps to improve this situation.

Option 1 would be to update Deltec to provide the report directly. But given this isn't available at the moment, we'll move to Option 2.

Option 2 is to use the source spreadsheet and generate an automated artifact that can be used for analysis.

The trick will be to limit or prevent how much "state" can be stored in that analytical view.

Our goal, is to allow for a full-refresh. But the more notes and comments and customizations users have, the more difficult it is to refresh everything at once.

Read the source xlsx data into a web app using an Elixir Phoenix web form. Read the data in that spreadsheet. Extract from it the structured data the Deltec report writer provided. Re-cast that data into a spreadsheet with a different sorting and format.