Posts tagged excel
Read an Excel xlsx with PL/SQL
6At the OTN SQL and PLSQL forum I promised to publish some code I use for a project I’ still working on. This code allows you to select the content from an Excel document (more…)
Valuable Java, JavaScript and ADF resources
0In the past few days, I have been working hard on several projects – both ADF and (plain) Java based, with quite a bit of JavaScript involved as well. The three main functional challenges:
- support keyboard (function key based) navigation in rich ADF Web pages (in addition to mouse based actions)
- support online and inline editing of customized (per context) resource bundle entries
- create a stand-alone viewer that allows users to inspect images (jpeg, tiff), PDF documents and Word and Excel documents
While working on these requirements, I have used – through Google, my main tool – a large number of very valuable resources on the internet. From the Oracle Technology Network Forums (OTN) to StackOverflow (http://stackoverflow.com), from personal blogs to corporate white papers as well as formal documentation – I have picked an incredible number of brains in a very condensed period of time.
I did not stop to leave notes of gratitude on all the sites I have come across. So I thought – for my own future reference as well as to pay some hommage to all these sites and individuals that helped me and to provide some insight in what challenges I faced and how I addressed them – to write More >
Leveraging APEX in XE 11gR2 to rapidly load Excel data into a database table
1Whilst preparing for the Thursday Thunder session at the last day of the ODTUG Kaleidoscope 2011 conference in Long Beach, where an all star team of moderators and developers will present a live application development session with key components from Fusion Middleware, I came across a very useful feature of APEX, readily available in my local light weight Oracle XE Database 11gR2.
The Thursday Thunder session will create a portal for participants of a technology conference just like ODTUG Kaleidoscope itself. It will also create a back office application for the conference’s organizing team and it will model and implement a key business process addressing the submission, acceptance and delivery of presentations at the conference. This session has three moderators – Chris Muir, Lonneke Dikmans and Duncan Mills – and a development team of five – Edwin Biemond, Peter Ebell, Ronald van Luttikhuizen, Luc Bors and Steven Davelaar. In order to have a case that is as realistic as possible, I requested the ODTUG organizers for the actual data of this year’s conference – in terms of presenters, sessions and the planning of them all.
They were very helpful and almost instantly provided me More >
Create an Excel-file with PL/SQL
78For this project I took an Apex-plugin I have written, (IR) Report to Excel (xlsx), and turned it into a PL/SQL package. With this package it’s very easy to create an Excel 2007 file with only a few lines of PL/SQL code.
begin as_xlsx.query2sheet( 'select * from dual' ); as_xlsx.save( 'MY_DIR', 'my.xlsx' ); end;The main purpose for this package is getting data from the database into an Excel file, so I deliberate did not include some Excel functionality, such as formulas, into the package. Excel itself is a far better tool for such things.
Anton
The source code for the package: as_xlsx P.S. I have added the possibility to add Comments and MergedCells to the Excel-file P.S.2 And bold/italic fonts P.S.3 Fixed issue with timezones with a regionname P.S.4 Fixed issue with XML-escaping from text P.S.5 Fixed NLS-issue with column width P.S.6 Added p_rgb to get_font P.S.7 Fixed bug in add_string P.S.8 Fixed set_autofilter (only one autofilter per sheet, added _xlnm._FilterDatabase) Added list_validation = drop-down
ADF 11g : Import From Excel Into an ADF Table
12ADF 11g provides an “export to excel” feature that allows you to take data from an ADF table into an excel sheet. But what about the other way around. Sometimes you need to work with excel sheets or comma separated values, and load the content of such a file into a database table. You could use the external table feature of the Oracle Database for that, or you just use any tool to load the csv contents into a database table. The use case I have is that the data in the csv needs to be validated and changed before it is committed into the database. In this post I show you how to load the csv contents into an ADF table component so you can edit the data before committing (more…)
OOW Sneak preview of Database Excel-erator – on the ADF Desktop integration
One of the sessions I attended yesterday at OOW was by Juan Camilo Ruiz, Product Manager for Oracle Development Tools. His talk was titled ‘Introduction to Oracle ADF Desktop Integration; An Office front-end for ADF Applications.’
He showed how we will be able to use Excel as the client for ADF applications. Instead of having users go into a Swing Desktop client or the ADF Faces Web interface, they can use the tool they are perhaps most familiar with to review, analyze and manipulate the data. Excel is a client to the ADF Model and Business Services through which it downloads the data and uploads the data when the user submits the changes.
Note that the Excel worksheet can taken off-line and the user can work on the data – potentially huge sets of it – in disconnected mode (on the air plane for example where despite all the cloud computing initiatives connectivity still is a scare commodity) only to have it synchronized when it becomes connected again. It is a bit like mobile devices that can work in disconnected mode and synchronize once a connection is available again – but it is Excel behaving in that way. From a developer’s point of view, developing the Excel client for ADF More >
Recent Comments