Импорт-экспорт в базу данных через 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>

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

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

На входе: 

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

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

SELECT 2 Настройки колонок 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

На входе: 

На выходе:

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 1 Result, '' Msg модель результата операции загрузки по 1 строке файла. 

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

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

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

Обновление 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

На входе: 

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

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

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
Страница-источник на сайте falconspace.ru