Excel

Show allHide all

Exports or imports Microsoft Excel data.

Not intended for exporting large amounts of data.

Component modes: Export | Export with Template | Import

Component mode: Export with Template

In template mode the component takes a standard Excel (xlsx) document and inserts data from the procedure into its named cells and tables.

File modes: Download | Save | Save and download | Batch

File mode: Download

The user downloads the file.

Repeated worksheets (N/A for import mode)

By default each worksheet in an Excel template maps to one worksheet in the final document. Should you need more flexibility then you can use repeated worksheets, where a worksheet in the template maps to zero to many worksheets in the final document.

Each worksheet in the template whose name is wrapped in {} is treated as a repeated worksheet and the "Get repeated sheet" call (see below) is invoked for it. This call returns the id values and sheet names of the final sheets.

Restrictions

Repeated worksheets can not use any advanced features, including but not limited to charts, formulas and named cells.

SQL

SQL Call: Get template (mandatory)

Gets the template to use.

May modify database: No

Parameters

@Template bit
Set to '1' by Softadmin® when the component asks for a template.

Resultset: Template

Table count: repeated exactly once
Row count: exactly one row

Columns

<template> mandatory binary
The template. A xlsx or xltx document.
<template>FileName optional string
The template's name.
TemplateId optional string
The template's ID.

SQL Call: Get repeated sheet

This call is made once for each worksheet whose name is "{SheetName}"

May modify database: No

Parameters

@RepeatSheet bit
Set to 1.
@SheetName string
Name of the sheet. Does include the {} characters wrapping the name.

Resultset: Repeated sheet

Table count: repeated exactly once
Row count: zero or more rows

Columns

<SheetId> mandatory string
The id value in this column will be passed to the stored procedure when fetching data for this sheet.
SheetName mandatory string
The name the copied worksheet gets in the final workbook.

SQL Call: Get variables (mandatory)

Gets the values to insert into named cells in a worksheet.

May modify database: No

Parameters

@<DocumentId> string
The ID of the document currently being generated. (Batch mode only)
@<SheetId> string
The ID of the worksheet currently being generated (repeated sheets only).
@Sheet string
The name of the worksheet.

Resultset: Values (optional)

The values from each column will be inserted into a named cell with the same name as the column name. If no cell with the given name exists then the value is ignored.
Table count: repeated zero or more times
Row count: exactly one row

Columns

<CellName> mandatory string
Value to insert into named cell <CellName>

SQL Call: Get table (mandatory)

Gets data to insert into a named table in the template. The columns returned must match the columns in the template exactly. The number of rows need not match the template - rows will be added or deleted as necessary to adapt the document to the data.

Return immediately from the procedure to keep the data from the template instead.

Any formatting of the data (number of decimals, adding a %-sign, ...) should be handled by the Excel template document and not by the stored prodcedure!

May modify database: No

Parameters

@<DocumentId> string
The ID of the document currently being generated. (Batch mode only)
@<SheetId> string
The ID of the worksheet currently being generated (repeated sheets only).
@Sheet string
The name of the worksheet.
@Table string
Name of the table to get data for.

Resultset: Rename columns (optional)

You can use this result set to dynamically map column names in the Excel template to SQL column names. You typically use this when users can administrate their own templates.

Even if you use this table you do not need to rename all columns.
Table count: repeated zero or one time
Row count: zero or more rows

Columns

ExcelName mandatory string
The column's name in the template. Excel column names are the text in the column's table header
SqlName mandatory string
The column's name in the next table returned from this call. The values in this column must be unique -- that is, you can not assign two different ExcelName to the same SqlName.

Resultset: Table data

The data to populate the Excel table with.

Must contain one data column for each column in the template's table.

The data will replace any rows already present in the template.
Table count: repeated exactly once
Row count: one or more rows

Columns

<ColumnName> optional string
Column data.

SQL Call: Validate parameters

Allows you to validate the parameters supplied by the user before any other SQL is run in the component. This call is only made if the component has visible parameters, the SQL is a stored procedure, and 'Validate parameters' is checked.

May modify database: No

Parameters

@Force bit
Set to 1 if the last call to validate parameters used admin_force and the user clicked OK in the OK/Cancel dialog.
@ValidateParams bit
Set to 1 when this call is made.

Custom access control and logging

SQL Call: Custom access control and logging

Use this call to restrict which entries a user is allowed to view and edit, and to log which entries a user views.

Access to a menu item is normally controlled through functions and roles alone but some entities need more fine grained control. For example, a user may have access to the View Member menu item for normal members but not for members with a protected identity.

The menu items a user visits are always logged (in ADMINLogMenuItem) but for sensitive data you may need to log exactly what entries are viewed. Do the logging in this call as the common ways of viewing data (grid and InfoSQL) are not allowed to modify the database.

If you bind a scalar function instead of a stored procedure to this call then its name must end with '_GrantAccess'.

May modify database: Yes

Resultset: Access permissions

Return whether the user is allowed to visit the menu item with the current parameters.
Table count: repeated exactly once
Row count: exactly one row

Columns

GrantAccess mandatory bit
1 if the user is allowed to view the menu item, 0 if the user should not be allowed to view the menu item.

If 0 then an error will be logged as the user should not have been able to reach the menu item with the given parameters in the first place.

Examples

Single table Excel export

This is the most simple example. Get one document with one table.

ALTER PROCEDURE dbo.Booking_SearchSingleDate_Excel
	-- System specific parameter
	@Date	date = NULL,
	-- Component parameters
	@Template	bit	= 0,
	@Sheet	varchar(MAX) = NULL,
	@Table	varchar(MAX) = NULL
AS
BEGIN
	/* 
	 * This example does not use batch-mode, just returning an excel file with one page.
	 */
	 ----------------------------------------------
	 -- Anrop 1. Return template to use. --
	 ----------------------------------------------
	IF @Template = 1
	BEGIN
		SELECT
			AD.Document [TemplateDocument],
			'Dagsvy_'+ CONVERT(varchar(10), @date, 20) + '.xlsx' [TemplateDocumentFileName]
		FROM
			dbo.ADMINDocument AD
		WHERE
			AD.DocumentId = 10;
		RETURN;
	END

	------------------------------------
	-- Anrop 2. Fill separate cells. --
	------------------------------------
	IF @Sheet IS NOT NULL AND @Table IS NULL
	BEGIN
		-- No separate cells to fill
		RETURN;
	END
	-----------------------------
	-- Anrop 2. Fill table. --
	-----------------------------
	IF @Table IS NOT NULL
	BEGIN
		SELECT
			*
		INTO
			#Booking
		FROM
			dbo.Booking_Search_Definition();

		INSERT INTO #Booking
		EXEC dbo.Booking_Internal_Search
				@BookingTypeId	= NULL,	
				@HasEnlister		= NULL,
				@Status			= 7,	
				@guideid		= NULL,
				@entryid			= NULL,
				@IsDeleted		= 0,
				@SingleDate		= @Date,
				@HideStaffLed	= 0

		-- Rename the columns so that they match the kolumn names in the Excel template.
		SELECT
			B.BookingTypeName [Bokningstyp],
			B.times [Tid],
			B.Organization [Organisation],
			B.GroupName [Gruppnamn],
			B.VisitorCount [Antal],
			B.guide [Guide],
			B.Comment [Kommentar]
		FROM
			#Booking B
		ORDER BY
			B.times ASC
	END
END