Falcon Space. Generating Docx and Xlsx documents

Example of uploading Excel and Word documents

The component allows you to create a Word or Excel document based on a template document and insert data from a stored procedure into it.

Snippet
<a href="#" class="as-doc" data-code="code" data-itemid="123" title="Generating an Act document"><i class="fa fa-file-text"></i></a><i class="fa fa-file-text">
<!--When you click on the link, a document is generated and a download link is issued.--></i>

A document element Must be created in the database (section Uploading documents) with elements: 

• Code - is part of the name of the stored data extraction procedure, as well as the name of the document template. 
• Name-participates in forming the name of the output file
• Type-Word or Excel
• Roles - which roles have access to the document. 

Next, create a stored procedure named doc_{code}_getData, which returns datasets (multiple select).

ALTER PROCEDURE [dbo].[doc_testWord_getData]
@username nvarchar(64),
@itemID int,
@urlParameters CRUDFilterParameter READONLY
AS
BEGIN
select 1 Result, '' msg, '8000' TableWidth, '2500,1000,3500' ColWidths, '999999'TableHeaderBackColor, 'Act - {g}' FileName

select 'IE Fedorov AI' customer, '241' docNum, 'November 10, 2019.' date,
'Agreement #3 dated November 01, 2019.' parentDoc,
'550 (five hundred and fifty) rubles' propis,
'Individual entrepreneur' customerPost,
'Alexey Fedorov' customerFIO,
'2019' year

select 'wall Painting' as 'Work', 12 as 'Meters', 250 as 'Cost'
union
select 'laying laminate', 20, 300

END

Preparing the Export file template. It is located at /uploads/doc-templates/{code}. docx (or xlsx). 
IMPORTANT. Template files should be in docx and xlsx (not doc and xls).

Description of the SELECT 1 parameters:
1. TableWidth (for word only) - the width of tables in a Word document (by default, 9600).
2. ColWidths (word only) - comma-separated column widths of the table (by default, the column width in the table is 2000)
3. TableHeaderBackColor (word only) - color of the table header (by default, f9f9f9). 
4. FileName - name of the output file (using Latin letters!). it can contain additional pseudo-parameters: {g} - inserting 4 guid characters, {date} - inserting a date. If the name is omitted, it will be {code}-{g}. xlsx
5. ShowTableHeader (only for Excel) - if 1, the table shown is added along with the header. 

In the template, use the following fields to insert data:

• the

  {table2} - to insert the entire second table (second select).

• {table2. title} - insert the value of the title column from 1 line of the second set (select). 

Пример Xlsx: 

Note

1. For the Docx template, any codes should only be inserted via the clipboard from Notepad (and not edited in Word). This is due to the fact that Word stores XML structures in the form of P, run, and text tags. For tables ({table1}) - only one element must be in the row (without additional text). If the template doesn't work somewhere, try inserting the full text with parameters directly from Notepad
2. When generating documents, the log of who created the files with a link to the generated file is saved. 
3. The generated files are located at/uploads/doc/{code} + the miniGuid suffix is added to the name.
4. If you need to check the rights to the generated document in more detail, you can do this directly in the GetData stored procedure based on username and itemID.