Exports or imports Microsoft Excel data.
Not intended for exporting large amounts of data.
In template mode the component takes a standard Excel (xlsx) document and inserts data from the procedure into its named cells and tables.
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.
Repeated worksheets can not use any advanced features, including but not limited to charts, formulas and named cells.
Gets the template to use.
This call is made once for each worksheet whose name is "{SheetName}"
Gets the values to insert into named cells in a worksheet.
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!
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.
Allows up to 10000 rows/table.
Allows you to validate the SQL parameters before any other SQL is run in the component. This call is only made if the SQL is a stored procedure and Validate parameters is checked.
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'.
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