0

I am working on oracle APEX.

Need to develop one application where all submitted data should store in excel. means excel should work as a database. Is it possible and how can I make it??

Parth Patel
  • 915
  • 11
  • 33
Hemanth Narayanan
  • 1
  • 1
  • 3
  • Excel is not a database. – OldProgrammer Jul 30 '19 at 13:00
  • As others said, excel is not a database, but you can keep an sql database, but export it via .csv files and maintain an excel 'database' in the background. Cant see why you would want to do it though. – TineO Jul 30 '19 at 14:50
  • You may also want to check out Interactive Grids https://docs.oracle.com/database/apex-18.1/AEEUG/what-is-interactive-grid.htm#AEEUG-GUID-DD8997BE-0F32-4591-ADB0-078DC635C10B – Scott Jul 31 '19 at 01:09
  • One of those cases that proves the customer is not always right. "We need an APEX application that stores its data in excel"... sometimes you have to dig deeper to find out what the real problem they're trying to solve is. – Jeffrey Kemp Aug 08 '19 at 14:51

2 Answers2

1

If you are asking can APEX act as the front-end to an Excel spreadsheet, the answer is No. APEX is a combination of SQL and PL/SQL that only runs in the Oracle DB. Period. If you are asking can you upload an excel spreadsheet into the Oracle DB, create a table from it and use APEX as the front-end, the answer is Yes. And you can continue to upload data from an excel spreadsheet into the table as needed. Reading the docs is a good place to start.

Export to Excel: To export Oracle database table data to excel, use a PL/SQL Package like this or read this for ideas, or this. If you want it to happen when the table is updated, then use a table trigger. Docs. Tutorial.

Also, please, when asking questions like, this, let us know what steps you took to search for the answer, before posting here and asking us to spend our free time to do the search for you,. Searches of "Upload excel data to Oracle table in APEX" "Export Oracle table data to Excel" - should have been your first try and could have reduced your questioning here.

Joe
  • 3,337
  • 1
  • 14
  • 11
0

As others have already told, I emphasize that Excel is not a database. Therefore, I can only suggest two different workarounds:

  1. Read from an Excel file through External Tables

Oracle External Tables allow you to access two-dimensional-data as if they were Database Tables. This is usually meant to be used for CSV data, but apparently, someone has written a wrapper for XLSX files: https://odieweblog.wordpress.com/2016/06/21/reading-an-excel-file-xlsx-as-an-external-table/

However ... this method doesn't seem to allow to save to the Excel-File, so you could also just convert the Excel-File to a Database Table.

  1. Export to Excel

A far more viable option would be to save the data in a normal database table and provide an Excel-Export to your users. Oracle APEX does not support this out of the box, but there is the Alexandria Library, which is written in pure PL/SQL that does allow you to generate XLSX files on the fly. Don't worry if the packages looks poorly maintenainced, I can assure you it works. There are demos here: https://github.com/mortenbra/alexandria-plsql-utils/blob/master/demos/xlsx_builder_pkg_demo.sql

Thomas Tschernich
  • 1,264
  • 15
  • 29