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

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 Ключевые настройки:

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

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

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

Процедура экспорта 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

Выходная модель совпадает с настройками параметров в Тип экспорта.

Эти же поля будут в Excel файле.

Процедура импорта 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

На входе - одна модель строки из excel - задается на основе параметров типа

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

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

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

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

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

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

Falcon Space - функциальная веб-платформа разработки на узком стеке MS SQL/Bootstrap. Вводная по Falcon Space

SQL-инструмент для создания личных кабинетов на сайте

Суть подхода и история создания Falcon Space

Платформа Falcon Space

Это снижение стоимости владения

за счет меньшего количества людей для поддержки

Это быстрое внесение изменений

по ходу эксплуатации программы

Это современный интерфейс

полная адаптация под мобильные устройства

Веб-приложения на MS SQL. Партнерская программа для разработчиков и веб-студий

Вы можете разрабатывать самостоятельно или сотрудничать с нами в плане веб-разработки на платформе Falcon Space, используя только SQL и HTML.
Смотреть примеры с кодом SQL
Документация по платформе

Google поиск по нашей документации