Импорт-экспорт в базу данных через Excel

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

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

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

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

Примечание: 

Использовать можно только формат 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

Данная процедура выполняется для КАЖДОЙ строки файла Excel отдельно.

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) 

• Msg - вывод сообщения (если пробел - то не будет выводиться окно). 
• Result - результат операции импорта 
• Type - задает цвет сообщения (success, primary, danger, warning и т.д.)
• Actions - JSON для выполнения действий на клиенте. 

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

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

Проблемы при загрузке файла Excel. Что делать?

1. Убедитесь, что у вас файл создан из шаблона, а не с нуля сделанный файл

2. Загрузке подлежит только XLSX файлы (а не XLS). 

3. Попробуйте очистить формат файла

4. Попробуйте готовить файл в Google Disk и делать выгрузку в XLSX. 

5. Пробуйте сначала загрузить файл из 1-3 строк. 

Дополнительные материалы

1. Выгрузка документа в Excel
2. Стилизация Excel при выгрузке таблицы в MS Excel
3. Генерация документов в Docx и Xlsx