Excel to Modern App in Apex Part 1

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:

  1. We want to be able to use the data in other applications. Whilst it is locked up in an Excel document this is impossible
  2. 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
  3. 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.

1.Data Workshop

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.

2.Load Data Screen 1

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)

3. Copy data

Paste this data into the ‘Data’ text box. It should look like this:

4. Paste Data

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.

5. Review data
On the next screen just choose ‘Load Data’. This screen deals with how your data is identified. Since we didn’t have an identity column the system will add one in for us.
6. Load Data

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.

7. Data in Table

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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s