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: 

{step1}

{table1.total}

C1

D1

A2

B2

C2

D2

A3

{table2}

C3

D3

A4

B4

C4

D4

A5

B5

C5

D5

A6

B6

{table1}

D6

A7

B7

C7

D7

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.
Falcon Space is a functional web development platform on a narrow stack MS SQL/Bootstrap. Falcon Space Gettting started
{sp-shortDemostandLinks}

SQL-tool for creating personal accounts on the site

The essence of the approach and the history of the creation of Falcon Space
Web platform for creating personal accounts

Falcon Space Platform

This is a reduction in the cost of ownership

at the expense of fewer people to support

This is a quick change

while using the program

This is a modern interface

full adaptation for mobile devices

MS SQL web applications. Affiliate program for developers and web studios

You can develop on your own or collaborate with us on Falcon Space web development using only SQL and HTML.
See examples with SQL code
Platform documentation
Working on MS SQL Server
Note