Генерация документов Docx и Xlsx. Выгрузка Excel, Word по шаблону
Компонент Выгрузка документов позволяет создать некий шаблон Excel или Word, вставить в него параметры вида {tableN.colName} и затем по кнопке генерировать файлы из шаблона.
Выполняется некий запрос (возвращающий несколько SELECT), эти данные передаются в обработчик, который подставит их в файл-шаблон и выдаст ссылку на новый файл пользователю.
Т.е. задача разработчика заключается в следующем:
- подготовить SQL запрос извлечения данных,
- подготовить шаблон файла с параметрами, соответствующими данным из запроса.
Пример выгрузки документов Excel и Word
Компонент позволяет сформировать документ Word или Excel на основе документа-шаблона со вставкой в него данных из хранимой процедуры.
Сниппет
<a href="#" class="as-doc" data-code="code" data-itemid="123" title="Генерация документа Акта"><i class="fa fa-file-text"></i>
</a>
Должен быть создан элемент Документа в БД (раздел Выгрузка документов) с элементами:
- Код - является частью названия хранимой процедуры извлечения данных, а также названием шаблона документа.
- Название - участвует в формировании названия выходного файла
- Тип - Word или Excel
- Роли - какие роли имеют доступ к документу.
Далее создаем хранимую процедуру с именем doc_{code}_getData, которая возвращает наборы данных (несколько 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, 'Акт-{g}' FileName
select 'ИП Федоров А.И.' customer, '241' docNum, '10 ноября 2019г.' date,
'Договор №3 от 01 ноября 2019г.' parentDoc,
'550 (пятьсот пятьдесят) рублей' propis,
'Индивидуальный предприниматель' customerPost,
'Федоров Алексей Иванович' customerFIO,
'2019' year
select 'Покраска стен' as 'Работы', 12 as 'Метры', 250 as 'Стоимость'
union
select 'Укладка ламината',20, 300
END
ВАЖНО. Файлы шаблонов должны быть именно в docx и xlsx (а не doc и xls).
- @username - текущий пользователь
- @itemID - переданный в сниппете data-itemID
- @urlParameters - параметры из URL
- @parameters (ExtendedDictionaryParameter) - необязательный параметр, в нем могут передаваться дополнительные переменные (используется во внешнем действии генерации документов).
На выходе:
SELECT 1:
1. TableWidth (только для word) - ширина таблиц в документе Word (по умолчанию 9600).
4. TableHeaderBackColor (только для word) - цвет шапки таблицы (по умолчанию f9f9f9).
5. FileName - название выходного файла (с использованием латинских букв!). может содержать дополнительные псевдопараметры: {g} - вставка 4 символов guid, {date} - вставка даты. Если имя не указано, то оно будет таким {code}-{g}.xlsx
6. ShowTableHeader (только для Excel) - если 1, то показываемая таблица добавляется вместе с заголовком.
В шаблоне используем следующие поля для вставки данных:
-
{table2} - вставить целиком вторую таблицу (table2 соответствует третьему select. Первый SELECT служебный. поэтому нумерация начинается со второго селекта по данным).
-
{table2.title} - вставить значение колонки title из 1 строки второго набора (SELECT 2). (также можно {table1.title.text}).
- {table2.code1.img} - вывести картинку.
- {table2.code1.ul} и {table2.code1.ol} - вывести ненумерованный и нумерованный список (при этом в поле SQL значения будут перечислены через ||).
- Если используются картинки в шаблоне, то могут быть проблемы с открытием файла (Невозможно открыть файл) в старых версиях Word (2003, 2007).
- Если данных по таблице нет - то она не будет выводиться и шаблон {tableN} заменится на пустую строку.
Пример Xlsx: S
{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 |
Примечание
- Для шаблона Docx любые коды вставлять только через буфер обмена из блокнота (и не редактировать в Word). Это связано с особенностью хранения в Word структуры XML в виде P, run и text тегов. Для таблиц ({table1}) - обязательно только один элемент должен быть в строке (без дополнительного текста). Если где-то не срабатывает шаблон - попробуйте вставить полный кусок текста с параметрами прямо из блокнота
- При генерации документов сохраняется лог, кто, когда создавал файлы со ссылкой на сформированный файл (процедура [as_doc_writeLog] @itemCode nvarchar(64), @username nvarchar(64), @url nvarchar(256), @itemID nvarchar(max) = '', @parameters ExtendedDictionaryParameter).
- Генерируемые файлы находятся по адресу /uploads/doc/{code} + в название добавляется суффикс miniGuid.
- Если необходимо проверить более детально права на формируемый документ - это можно сделать прямо в хранимой процедуре GetData на основе username и itemID.
- Также вы можете генерировать документы через Внешнее действие generatedoc (при этом дополнительные данные передаются в GetData процедуру через параметр ExtendedDictionaryParameter @parameters). См. подробнее про Внешние действия.
- Выходные данные из SELECT лучше сразу приводить к строке в явном виде и обрабатывайте ситуацию с null (через isnull).
Как вставлять картинки в генерируемый Excel
Для этого параметры указываем с префиксом img_
Пример:
select '{"path": "/uploads/5.jpg", "name":"Name11", "height": 50, "width": 200,
"barcode":{ "value": "123", fontSize: 45,
font: "", backColor: "white", "width": 200, "height": 50 } }' as img_col1
В файле Excel указываем {table1.img_col1}.
В этом месте выведется указанная картинка (либо сгенерированный штрихкод).
Параметры:
- barcode - настройка для генерации штрихкода (в этом случае path можно оставить пустым). Его параметры:
- value - что кодируем в штрихкоде
- fontSize - размер выводимого штрихкода
- font - название спецшрифта для вывода штрихкода, например font39.
- backColor - какой будет фон у штрихкода (white, yellow и т.д.)
- width, height - размеры картинки штрихкода.
- barcode2 - альтернативный вариант генерации (через дополнительную библиотеку, а не шрифт на сервере). Его параметры:
- value - что кодируем в штрихкоде
- pureCode - bit. Если 0, то с кодом будет внизу выводиться сама закодированная строка. Если 1 - то выводится только сам штрихкод.
- width, height - размеры картинки штрихкода.
- format - формат генерации штрихкода (по умолчанию CODE_128).
- margin - отступ для кода, по умолчанию 10.
- gs1Format - bit, если 1, то будет значение декодировано по GS1 стандарт (по умолчанию false).
- height, width - размер картинки (например 200, 300)
- name - наименование объекта в Excel
- path - путь к файлу
- scale - указывает масштаб вывода картинки (например 0.5)
Возможные варианты barcode2.format:
AZTEC, Aztec 2D barcode format.
CODABAR, CODABAR 1D format.
CODE_128, Code 128 1D format.
CODE_39, Code 39 1D format.
CODE_93, Code 93 1D format.
DATA_MATRIX, Data Matrix 2D barcode format.
EAN_13, EAN-13 1D format.
EAN_8, EAN-8 1D format.
ITF, ITF (Interleaved Two of Five) 1D format.
MAXICODE, MaxiCode 2D barcode format.
PDF_417, PDF417 format.
QR_CODE, QR Code 2D barcode format.
RSS_14, RSS 14
RSS_EXPANDED, RSS EXPANDED
UPC_A, UPC-A 1D format.
UPC_E, UPC-E 1D format.
UPC_EAN_EXTENSION, UPC/EAN extension format.
Примечание:
- Для параметра barcode должен быть установлен (открыть файл ttf и кнопка Установить) шрифт font39 на сервере или другой аналогичный шрифт для вывода штрихкодов. Шрифт есть в папке /content/fonts. После установки шрифта нужно перезагрузить IIS на сервере.
- Параметр barcode2 используйте, когда нет возможности установить шрифт на сервере (например, это win хостинг).
Проблема. Иногда "съедаются" пробелы в документе рядом с параметрами.
В случае подобной проблемы добавляйте к параметру специальный код пробела.
select name + char(0x000000A0) name
Другие специальные коды можно посмотреть здесь - https://www.compart.com/en/unicode/U+00A0
Как сделать генерацию документа через некую форму
Иногда требуется дать пользователю возможность внести некие параметры в форме и на основе этого сгенерировать документ (например, docx).
В этом случае мы можем использоать внешнее действие generatedoc в SaveItem формы.
1. Создаем форму, добавляем в нее поля и в SaveItem вызываем внешнее действие.
CREATE PROCEDURE [dbo].[fm_generateDoc_saveItem]
@username nvarchar(256),
@itemID int,
@parameters ExtendedDictionaryParameter READONLY
AS
BEGIN
-- сохраняем форму (добавление/обновление некой сущности)
declare @psum nvarchar(max)
select @psum = Value2 from @parameters where [key]='sum'
declare @pdate nvarchar(max)
select @pdate = Value2 from @parameters where [key]='date'
....
-- SELECT 1 (Result, Msg, SuccessUrl, HideFormAfterSubmit, RefreshContainer)
select 1 Result, ' ' Msg, '' SuccessUrl, 1 HideFormAfterSubmit, '' RefreshContainer
-- SELECT 2 Вызов внешнего действия
select 'generatedoc' type, 'stageInvoice' code, cast(@itemID as nvarchar) itemID, '{
"companyName" : "'+replace(isnull(@companyName, ''), '"', '')+'",
"num" : "'+cast(isnull(@num, 0) as nvarchar)+'",
"date" : "'+isnull(@pdate, '')+'",
"sum" : "'+cast(isnull(@psum, '') as nvarchar)+'",
"text" : "'+isnull(@ptext, '')+'",
"docID": '+cast(@docID as nvarchar)+'
}' parameters,
'generatedoc_uploadGD' spCallback, -- если нужно что то сохранить допом после генерации (например ссылку на созданный файл)
cast(@docID as nvarchar) docID
END
2. Далее реализуем генерацию документа (в /doctemplates)
CREATE PROCEDURE [dbo].[doc_stageInvoice_getData]
@username nvarchar(64),
@itemID int,
@urlParameters CRUDFilterParameter READONLY ,
@parameters ExtendedDictionaryParameter READONLY
AS
begin
declare @companyName nvarchar(max)=''
select @companyName = Value2 from @parameters where [Key]='companyName'
declare @num nvarchar(max)=''
select @num = Value2 from @parameters where [Key]='num'
declare @date nvarchar(max)=''
select @date = Value2 from @parameters where [Key]='date'
declare @parentDocName nvarchar(max)=''
select @parentDocName = Value2 from @parameters where [Key]='parentDocName'
declare @sum int
select @sum = try_cast(Value2 as int) from @parameters where [Key]='sum'
declare @text nvarchar(max)=''
select @text = Value2 from @parameters where [Key]='text'
declare @docID int
select @docID = try_cast(Value2 as int) from @parameters where [Key]='docID'
--SELECT 1 Мета данные
select 1 Result, '' msg, '9400' TableWidth, '800,4000,1000,1200,1200,1200' ColWidths, 'f5f5f5' TableHeaderBackColor,
'Счет ' + [dbo].[str_processURL] ( cast(isnull(@num, 00) as nvarchar) +' - ' + (select top 1 name from rudenas2x.dbo.tt_projects where estimateID = stage.estimateID)+' ' + name + ' {g}') FileName
-- SELECT 2
select
'Индивидуальный предприниматель' p1,
'XXXX' p2,
'yyyyy' p3,
'zzzzzz' p4,
'cccccccc' p5,
'44444' p6,
'5555555' p7,
'АО "ТИНЬКОФФ БАНК"' p8,
'777777' p9,
isnull(@num, 00) p10, -- '23 от 10 июня 2019 года
isnull(@companyName,'') p11, --'ООО Матильда'
cast(@sum as nvarchar) p12, -- 550
cast(@sum as nvarchar) p13, -- 550
'1' p14,
cast(@sum as nvarchar) + ' (' + [dbo].[as_numPropis] (@sum, 1) + ') рублей' p15, -- '550 (пятьсот пятьдесят) рублей'
'Индивидуальный предприниматель' p16,
'' p17,
isnull(@date, 'no date') p18,
'' p19
-- SELECT 3 СМЕТА
select '1' '№',
isnull(@text, '') 'Наименование услуги',
'услуга' 'Ед. изм.',
'1' 'Кол-во',
cast(@sum as nvarchar) 'Цена, руб.',
cast(@sum as nvarchar) 'Сумма, руб.'
end
В эту процедуру передаются параметры из внешнего действия в коллекцию @parameters.
3. На странице, где используется форма, нам необходимо вывести кастом сообщение о результате операции через JS коллбек.
var f1 = function(data){
var el = JSON.parse(data.additionalData)[0].items;
console.log("xxxxxxxxxxxxx", el);
$.each(el, function(i,item){
console.log("asdasd ",item)
if(item.key=="url"){
var url = item.value2;
if(url) as.sys.bootstrapAlert("Скачать файл", {type:"success", delay:0});
}
});
};
as.formcallbacks["generateDocument_saveItem"] = f1;
4. И если нужно сохранить ссылку на файл где-то, то реализуем SQL коллбек для внешнего действия:
CREATE PROCEDURE [dbo].[outer_generatedoc_uploadGD]
@parameters ExtendedDictionaryParameter readonly,
@data nvarchar(128) = '',
@username nvarchar(128) = ''
AS
BEGIN
-- SELECT 1 ReesultModel
select 1 Result, '' Msg
declare @path nvarchar(256)
select @path = Value2 from @parameters where lower([key]) = 'url'
declare @docID nvarchar(256)
select @docID = Value2 from @parameters where lower([key]) = 'docid'
update documents set exportDocLink = @path where id = @docID
END
Пробема Excel - искажает вывод больших числе
Для этого указываем выводимый параметр со спец пробелом (чтобы Excel не пытался это понимать как число):
-- SELECT 1
select
char(0x000000A0) + '1234567890123' p1,
Дополнительные материалы
- Импорт-экспорт на сайте через Excel
- Таблица. стилизация Excel при выгрузке таблицы в MS Excel
- Создание выгрузки документа в Excel через форму
- Как docx файл конвертировать в pdf
Google поиск по нашей документации
- Руководства
- Основа Falcon Space
- Основные компоненты
- Возможности
- Коммуникация с пользователем
- Дизайн, стилизация, юзабилити
- Лендинги
- Универсальный API
- Примеры интеграций
- Каталоги
- Навигация
- Документы Импорт-экспорт на сайте через Excel Генерация документов Docx и Xlsx. Выгрузка Excel, Word по шаблону Таблица. Стилизация Excel при выгрузке таблицы в MS Excel Проверка орфографии в тексте через Яндекс Speller Как создать выгрузку документа в Excel через форму с последующей загрузкой на Google Disk и скачиваем в формате PDF Интеграция с Google Drive API Как docx файл конвертировать в pdf (LibreOffice, powershell)
- Дополнительные компоненты
- Продвижение, SEO
- Системные моменты
- Системное администрирование
- HOWTO
- Таблицы
- Формы
- Загрузка файлов, картинок
- Работа с SQL
- HOWTO JS
- HOWTO Верстка
- Решение проблем
Выгода от использования Falcon Space
В 2-3 раза экономнее и быстрее, чем заказная разработка
Более гибкая, чем коробочные решения и облачные сервисы
Используйте готовые решения и изменяйте под свои потребности
- Шаг 1. Создать концепт проекта
- Шаг 2. Получить оценку бюджета (КП)
- Шаг 3. Заключить договор
- Шаг 4. Создать совместно техническое задание
- Шаг 5. Поэтапная реализация проекта