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;
