Introduction
Importing Excel files that contain embedded images poses a unique challenge. Unlike plain data, images in Excel files are not stored in individual cells—they are embedded as separate binary objects within the file’s internal ZIP structure. This means that conventional Excel import tools do not detect or extract these images, making it necessary to use specialized techniques like ZIP extraction and binary BLOB processing to access and associate these images with corresponding data rows. Oracle APEX, combined with PL/SQL, offers the flexibility to handle this task effectively.
Oracle Application Express (APEX) provides powerful tools to handle complex file imports, including Excel files embedded with images. This guide demonstrates a simplified, step-by-step approach using PL/SQL to extract and store data along with images from an Excel file into your APEX application.
Review the Excel-Data structure
Before uploading, open the Excel file and verify the data structure.
-
Ensure that each row represents a complete record, and that any images are associated clearly with their corresponding rows.
- Every row should have a corresponding image.
-
Important: Do not overlay images on top of cells. Instead, embed them properly within the cell area, aligned by row. This will help with accurate mapping to row IDs during the import process.
- Here is a sample file (
sample_excel_with_images), which is also available on the demo application.
Create or review pages, process & DB-Objects
DB-Objects
In this example we will handle the data as bellow:
- Import the data into an APEX_COLLECTION
- Use a virtual tables Views formed by a query based on the collection data.
CREATE OR REPLACE VIEW v_excel_import
AS
SELECT
seq_id id,
c001 item_no,
c002 item_descr,
blob001 image_blob,
dbms_lob.getlength(blob001) display_image,
c003 image_name,
c004 content_type,
current_timestamp last_update_ts
FROM
apex_collections
WHERE
collection_name = 'EXCEL_IMPORT_COLLECTION'
ORDER BY
1;
Create an APEX Page for File Upload
Create a New Page in Your APEX Application
Use this page to upload your file and process the data.
We recommend using the Upload Wizard for a guided, step-by-step process to handle the data upload and processing efficiently.
Process Excel Data and Images
You can extract, process, and store data and images using the following code.
This code should be defined as a Process at the Processing Point, executed after the user clicks the “Process Data” button.
The process performs the following steps:
- Retrieve the uploaded Excel file.
- Extract images from the Excel file using the
APEX_ZIPOracle package, which supports zipping and unzipping operations. - Extract the remaining data from the file.
- Handle file extensions and associate each image with its corresponding data by referencing the Excel row number minus one, ensuring visual confirmation of correct image-data pairing.
- Insert the extracted data and images into the APEX_COLLECTION.
DECLARE
v_file_blob BLOB;
v_zip_files apex_zip.t_files;
TYPE t_blob_table IS
TABLE OF BLOB INDEX BY VARCHAR2( 200 );
TYPE t_ext_list IS
TABLE OF VARCHAR2( 10 );
v_image_blobs t_blob_table;
v_file_name VARCHAR2( 400 ) :=:p1055_file;
BEGIN
-- Get uploaded Excel file
SELECT blob_content INTO
v_file_blob
FROM apex_application_temp_files WHERE name = v_file_name;
-- Extract embedded images
v_zip_files := apex_zip.get_files(
p_zipped_blob => v_file_blob
);
-- Load image blobs
FOR i IN 1..v_zip_files.count LOOP
IF
v_zip_files( i ) LIKE 'xl/media/%'
THEN
DECLARE
v_filename VARCHAR2( 200 ) :=
lower( regexp_substr( v_zip_files( i ), '[^/]+$' ) );
BEGIN
v_image_blobs( v_filename ) :=
apex_zip.get_file_content(
p_zipped_blob => v_file_blob,
p_file_name => v_zip_files( i )
);
END;
END IF;
END LOOP;
-- Create or clear APEX collection
apex_collection.create_or_truncate_collection(
p_collection_name => 'EXCEL_IMPORT_COLLECTION'
);
-- Parse Excel rows
FOR rec IN (
SELECT
line_number,
col001 AS item_no,
col002 AS item_descr
FROM
TABLE ( apex_data_parser.parse(
p_content => v_file_blob,
p_file_name => v_file_name
) )
WHERE
line_number > 1
ORDER BY line_number ) LOOP
DECLARE
v_image_blob BLOB := NULL;
v_image_name VARCHAR2( 200 ) := NULL;
v_content_type VARCHAR2( 50 ) := NULL;
v_exts t_ext_list := t_ext_list(
'jpeg',
'jpg',
'png',
'gif',
'bmp'
);
BEGIN
-- Match image by extension and use PNG as default extension
FOR i IN 1..v_exts.count LOOP
IF
v_image_blobs.EXISTS( 'image'
|| ( rec.line_number - 1 )
|| '.'
|| v_exts( i ) )
THEN
v_image_blob := v_image_blobs( 'image'
|| ( rec.line_number - 1 )
|| '.'
|| v_exts( i ) );
v_image_name := 'image'
|| ( rec.line_number - 1 )
|| '.png';
v_content_type := 'image/png';
EXIT;
END IF;
END LOOP;
-- Add row to collection
apex_collection.add_member(
p_collection_name => 'EXCEL_IMPORT_COLLECTION',
p_c001 => rec.item_no,
p_c002 => rec.item_descr,
p_c003 => v_image_name,
p_c004 => v_content_type,
p_blob001 => v_image_blob
);
END;
END LOOP;
END;
Run and Verify
-
Execute the process by uploading an Excel file.
-
Verify the imported data and images in your target table.
Display Images in Reports
Use APEX Interactive Reports or Cards to clearly present your data along with the associated images.
-
To download images, use the “Download BLOB” column type.
-
To display images directly within the Interactive Report, use the “Display Image” column type.
Ensure that the images are of an appropriate and displayable size for optimal rendering.
SELECT
id,
item_no,
item_descr,
display_image,
image_name,
content_type,
last_update_ts
FROM
v_excel_import;
Now, you can visualize your data.
Conclusion
Following these concise steps, you’ll efficiently import Excel files with images, making data management straightforward within your Oracle APEX application.
Add a Comment