Oracle has a rapid development environment, Apex, that is very useful in replacing all those shared Excel files and Access databases running wild in your organisation. With it you can create modern HTML5 applications to replace all that business logic. To give you a better understanding of how it works and how you can use it within your organisation I’ve put together this series of posts where will take an Excel spreadsheet and create a usable application.
Our scenario is that the business has been using a spreadsheet to manage the sales for each of the regions. We’d like to modernise this app for a number of reasons:
- We want to be able to use the data in other applications. Whilst it is locked up in an Excel document this is impossible
- Since it’s a shared Excel document its not always clear if you have the latest version. This probably isn’t a major issue with our data set as its only changed quarterly, but a more complex Excel document will run into these problems
- The business wants to add more capability into the spreadsheet and wants IT’s help on this. By making it into a proper application this will be a lot easier to manage
The first thing we need to do is go into our Apex workspace (we’ve assumed that IT has already set up a workspace for us, there are plenty of tutorials online that cover this) and upload our spreadsheet data. To do that we go to SQL Workshop->Utilities->Data Workshop and then click on the ‘Spreadsheet’ data link in the ‘Data Load’ section.
On the resulting screen we want to create a new table for our data and then copy and paste it in. We could also upload the file after we converted into csv.
Let’s copy the data from our sales spreadsheet, as you can see it’s pretty rudimentary. Make sure to include the column headings, but not the title row (we don’t need that as it’s not part of the data)
Paste this data into the ‘Data’ text box. It should look like this:
After clicking ‘Next’ we are presented with all our columns and some of the data that will be uploaded. You can generally leave all this as default as Apex is smart enough to work out what type of column you are uploading. In this scenario change the ‘Upload’ to ‘No’ for the ‘Total’ column. We don’t want to upload that column as it’s just the sum of the Hardware, Software and Services columns. We can add that in automatically later.
The import will proceed. It will then show the outcome of the import and any failures will be listed on that screen. Click the ‘Sales’ hyperlink and navigate to the ‘Data’ tab. You will see all your data has been loaded.
With that, we’ve got all our data into the database. At this point anyone with access to this schema will now be able to use our data in other systems. But we still have to create an application to allow our business users to modify the data. We could edit the data directly on this screen, but it’s not exactly business-friendly.
In the next post, we will show how we create an application in Apex to provide an interface to managing this data.