ExcelDocumentType: Create Hyperlinks between Worksheets

This post  introduces a new feature in the ExcelDocTypeUtils PL/SQL package that allows a developer to create hyper linked cell data that points from one worksheet to another.   The feature is implemented by a  new function called createWorksheetLink.  The function takes two parameters: the cell data, and the name of the target worksheet.  The function is called from the dynamic SQL statement that is passed as part of a worksheet data object (ExcelDocTypeUtils.T_WORKSHEET_DATA).

The following code sample demonstrates how this feature can be used to generate a summary worksheet containing a hyper linked list of department names. Each hyper linked department name points to a worksheet containing employee data for that particular department.    The department related worksheets each contain a hyper link back to the summary worksheet that contains the department listings. The data for this demo comes from Oracle HR demo schema.

CREATE OR REPLACE PROCEDURE ExcelHyperlinkHR
IS

– This cursor will be used to generate the individual Department worksheets.
CURSOR crsrDepatmentWS IS
SELECT
department_id,
SUBSTR(UPPER(DEPARTMENT_NAME),1,20) sheet_name
FROM
hr.departments dp
WHERE
EXISTS (select ‘x’ from HR.employees where DEPARTMENT_ID = dp.department_id)
ORDER BY
dp.department_name;

– Base SQL statement for loading each departmental worksheet
v_employee_base_sql VARCHAR2(500) := ‘SELECT last_name||”, ”||first_name,email,phone_number,to_char(hire_date,”MM/DD/YYYY”) ‘||
‘FROM hr.employees ‘||
‘WHERE department_id = :id ‘||
‘ORDER BY last_name,first_name’;

– SQL statement based on v_employee_base_sql, but with the :id replaced with department_id.
v_employee_dept_sql VARCHAR2(500) := NULL;

– SQL statement used to generate hyperlinked worksheet listing all departments containing employees
v_department_sql    VARCHAR2(500) := ‘SELECT ExcelDocTypeUtils.createWorksheetLink(UPPER(department_name),department_name) ‘||
‘FROM hr.departments dp ‘||
‘WHERE EXISTS (select ”x” from HR.employees where DEPARTMENT_ID = dp.department_id) ‘||
‘ORDER BY DP.DEPARTMENT_NAME ‘;

excelReport         ExcelDocumentType := ExcelDocumentType();
documentArray       ExcelDocumentLine := ExcelDocumentLine();

v_worksheet_rec     ExcelDocTypeUtils.T_WORKSHEET_DATA := NULL;
v_worksheet_array   ExcelDocTypeUtils.WORKSHEET_TABLE  := ExcelDocTypeUtils.WORKSHEET_TABLE();

v_sheet_title       ExcelDocTypeUtils.T_SHEET_TITLE := NULL;

– Objects for Defining Document Styles (Optional)

v_style_def         ExcelDocTypeUtils.T_STYLE_DEF := NULL;
v_style_array       ExcelDocTypeUtils.STYLE_LIST  := ExcelDocTypeUtils.STYLE_LIST();

BEGIN

– Add Styles

v_style_def.p_style_id     := ‘HyperlinkStyle’;
v_style_def.p_text_color   := ‘Blue’;

ExcelDocTypeUtils.addStyleType(v_style_array,v_style_def);

v_style_def := NULL;
v_style_def.p_style_id          := ‘SheetTitleStyle’;
v_style_def.p_align_horizontal  := ‘Center’;
v_style_def.p_bold              := ‘Y’;
v_style_def.p_text_color        := ‘Black’;

ExcelDocTypeUtils.addStyleType(v_style_array,v_style_def);

v_style_def := NULL;
v_style_def.p_style_id          := ‘SheetTitleStyleLink’;
v_style_def.p_align_horizontal  := ‘Center’;
v_style_def.p_bold              := ‘Y’;
v_style_def.p_text_color        := ‘Blue’;

ExcelDocTypeUtils.addStyleType(v_style_array,v_style_def);

– Define Sheet Title
v_sheet_title.title      := ‘Employee Listing By Department’;
v_sheet_title.cell_span  := ’4′;
v_sheet_title.style      := ‘SheetTitleStyle’;

v_worksheet_rec.query             := v_department_sql;
v_worksheet_rec.worksheet_name    := ‘Departments’;
v_worksheet_rec.title             := v_sheet_title;
v_worksheet_rec.col_count         := 1;
v_worksheet_rec.col_width_list    := ’40′;
v_worksheet_rec.col_header_list   := ‘Department’;
v_worksheet_rec.col_datatype_list := ‘String’;
v_worksheet_rec.col_style_list    := ‘HyperlinkStyle’;

ExcelDocTypeUtils.addWorksheetType(v_worksheet_array,v_worksheet_rec);

– Construct Department worksheets

FOR data_rec IN crsrDepatmentWS LOOP

v_worksheet_rec      := NULL;
v_employee_dept_sql  := REPLACE(v_employee_base_sql,’:id’,data_rec.department_id);

v_sheet_title.title      := ExcelDocTypeUtils.createWorksheetLink(‘Departments’,’Back to Department Listing’);
v_sheet_title.cell_span  := ’3′;
v_sheet_title.style      := ‘SheetTitleStyleLink’;

v_worksheet_rec.query             := v_employee_dept_sql;
v_worksheet_rec.worksheet_name    := data_rec.sheet_name;
v_worksheet_rec.title             := v_sheet_title;
v_worksheet_rec.col_count         := 4;
v_worksheet_rec.col_width_list    := ’50,20,20,15′;
v_worksheet_rec.col_header_list   := ‘Name,Email,Phone Number,Hire Date’;
v_worksheet_rec.col_datatype_list := ‘String,String,String,String’;

ExcelDocTypeUtils.addWorksheetType(v_worksheet_array,v_worksheet_rec);

END LOOP;

excelReport := ExcelDocTypeUtils.createExcelDocument(v_worksheet_array,v_style_array);

excelReport.displayDocument;

END;

This video clip demonstrates the output from the source code.

Download the code for the procedure here:ExcelHyperlinkHR.sql.
(Note … you must have the latest version of the ExcelDocumentType and ExcelDocTypeUtils)

Comments are closed.