Импорт-экспорт на сайте через Excel

Компонент позволяет настроить импорт/экспорт практически для любых данных, обрабатываемых в системе, например, для товарного каталога.

Как сделать импорт и экспорт данных через Excel

Как происходит работа с компонентом с точки зрения пользователя:

Выгружаем необходимые данные в Excel (либо выгружается только шаблон файла)
Редактируем их в Excel.
Импортируем данные обратно. Файл должен строго соблюдать начальный формат.

Примечание:

Использовать можно только формат xlsx (не xls).

ВАЖНО! Не используйте данный компонент в модальном окне (связано с особенностями загрузки файла через модальное окно).

Как настроить компонент импорта через Excel

Для настройки компонента необходимо выполнить следующее:

1. На странице вставляем сниппет

<div class="as-exportImport" data-code="trace" data-itemid="123" data-title="Название" data-desc="Подзаголовок" data-exportbtn="Экспортировать" data-exporttemplatebtn="Шаблон" data-importbtn="Импортировать" data-disableimport="1">
</div>

Параметры сниппета разметки компонента:

code - задает код элемента (для него мы будем настраивать хранимые процедуры)
desc - описание-подсказка
disableExport - если 1, то кнопка Экспорта скрывается.
disableImport - если 1, то кнопки Шаблона и Импорта скрываются (также обязательно в настройках в ХП settings также запретить импорт).
exportBtn - кнопка Экспорт
exportTemplateBtn - кнопка Выгрузить шаблон Excel
importBtn - кнопка Импорт
itemID - некий внешний ID (например категории каталога).
title - заголовок компонента

2. В разделе Экспорт/Импорт создаем новый элемент с кодом code (поле название - информативное, нигде не используется).

3. Реализуем процедуру settings

4. Реализуем процедуру экспорта export

5. Реализуем процедуру импорта import

Процедура экспорта exportImport_{code}_settings

CREATE PROCEDURE [dbo].[exportImport_example_settings]
	@username nvarchar(64),
	@itemID nvarchar(32),
	@parameters ExtendedDictionaryParameter READONLY
AS
BEGIN
	-- SELECT 1
	select '' Msg, 1 Result,
		'List name' ExcelListName,
		'{code}-{date}-{g}.xlsx' ExportFileName,    -- use {g} {code} {date}
		'' ImportDirectory,  -- "/uploads/importExcel/"
		'{title} ({name})' HeaderColName,  -- col header in export excel
		0 DisableImport,
		1 LogImport,   -- log to table as_exportImportLog, as_exportImportLogItems
	   'admin'	Roles


	-- SELECT 2 Cols
	select * from (
	select 'ID' name, 'Номер' title, '1' value, 20 width, 1 ord
	union
	select 'Name' name, 'Название' title, '2' value, 50 width, 2 ord
	union
	select 'Desc' name, 'Описание' title, '3' value, 100 width, 3 ord
	) t1
	order by ord

END

На входе:

@username - текущий пользователь
@itemID - itemID из сниппета
@parameters - здесь передаются вспомогательные параметры (например userGuid, langID).

На выходе выдаем настройки экспорта/импорта:

SELECT 1 Ключевые настройки:

DisableImport - запрещает делать импорт по данному коду (т.е. можно настроить, например, что одним пользователям можно делать, а другим нельзя)
ExcelListName - наименование листа в Excel
ExportFileName - имя экспортируемого файла (можно использовать переменные {g} {code} {date})
HeaderColName - наименование колонок (можно использовать переменные {title} {name})
HideImportDetails - если 1, то по результатам импорта не показывает построчный лог, что загрузилось, а что нет.
HideImportSummary - если 1, то по результатам импорта не показывается саммари по загрузке (сколько ошибок и сколько загружено)
ImportDirectory - куда будут складываться файлы импорта при загрузке
LogImport - логировать или нет в базу результат выполнения операций импорта по строкам.
Msg, Result - результат операции
Roles - список ролей через запятую, которые имеют доступ к компоненту

SELECT 2 Настройки колонок Excel

name - код колонки для идентификации в процедуре импорта
ord - порядковый номер столбца
title - название колонки
value - значение по умолчанию - используется как выгрузка примера в шаблоне Excel1
width - ширина столбца в excel

Процедура экспорта exportImport_{code}_export

CREATE procedure [dbo].[exportImport_trace_export]
 @page int = 1,         --на входе page и pageSize что бы ограничить
 @pageSize int = 5,      --набор выходных данных
 @username nvarchar(128)=''
as
begin
     -- SELECT 1
    select id ID, header Name, text [Desc] from as_trace
    order by  id desc
    OFFSET @PageSize * (@Page - 1) ROWS
    FETCH NEXT @PageSize ROWS ONLY;

    -- SELECT 2 Внешние действия
end

На входе:

@page - номер страницы выгрузки данных (указывает пользователь при вызове экспорта).
@pageSize - сколько выдавать элементов на одной "странице".
@username - текущий пользователь.
@itemID - параметр переданный из разметки сниппета (data-itemID).

На выходе:

SELECT 1 - поля выгружаемые в Excel в соответствии с моделью, заданной в процедуре settings в SELECT2

SELECT 2 - вызов внешних действий

Процедура импорта exportImport_{code}_import

CREATE procedure [dbo].[exportImport_trace_import]
  @ID int,
  @Name nvarchar(512),
  @Desc nvarchar(max),
  @username nvarchar(128)=''
as
begin
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRY
    BEGIN TRAN
        if(@id>0)begin
        update as_trace set header = @Name, text = @Desc where id = @ID
        select 'Обновлено ID=' + cast(@id as nvarchar) Msg, 1 Result
        end
        if(@id<0)begin
        delete from as_trace where id = -@ID
        select 'Удалено ID=' + cast(@id as nvarchar) Msg, 1 Result
        end
        if(@id=0)begin
        insert into as_trace (header, text) values(@Name, @Desc)
        select 'Добавлено ID=' + cast(SCOPE_IDENTITY() as nvarchar) Msg, 1 Result
        end

        -- SELECT 2 Внешние действия
    COMMIT
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK
    DECLARE @Err varchar(512)
    SET @Err= ERROR_MESSAGE()
    RAISERROR (@Err, 16, 1)
    select @Err Msg, 0 Result
END CATCH

end

На входе:

параметры, определяемые из настроек в SELECT 2 процедуры Settings (т.е. те поля, которые мы загружаем из Excel)
@username - текущий пользователь
@itemID - параметр из разметки сниппета (data-itemID)

На выходе:

SELECT 1 Result, '' Msg модель результата операции загрузки по 1 строке файла.

Соглашение по операциям:

ID = 0 добавление,
ID > 0 обновление записи ID
ID < 0 удаление записи -ID

Выходная модель:

SELECT 1 ResultModel(Msg, Result)
SELECT 2 Внешние действия

Информация о результате операции выдается в выходной лог по импорту, а также может сохраняться в таблицы лога.

Обновление 06.06.2020. Добавлен параметр @username в процедуры export и import.

Процедура SQL коллбека после выполнения импорта exportImport_{code}_afterImport

CREATE procedure [dbo].[exportImport_langtexts_afterImport]
	@username nvarchar(64),
	@code nvarchar(64),
	@itemID nvarchar(64),
	@filePath nvarchar(256),
	@result bit,
	@items ExtendedDictionaryParameter readonly
as
begin
   -- SELECT 1 
   select '' Msg, 1 Result

   -- SELECT 2 Outer actions

end

На входе:

@username - текущий пользователь,
@code - код компонента импорта,
@itemID - itemID в компоненте импорта,
@filePath - путь к файлу,
@result - резултат обработки файла,
@items - все результаты обработки строк файла (Key,Value2),

Процедура выполняется сразу после обработки всех строк файла и выдает следующие SELECT:

1. SELECT 1 Результат операции (Msg и Result)

2. SELECT 2 Вызов внешних действий.

На входе имеем текущего пользователя параметры data-code и data-itemID от сниппета элемента импорта, путь к файлу загрузки, результат операции загрузки и все загруженные элементы в коллекции items (полная аналогия с процедурой логирования результатов загрузки).