Импорт-экспорт на сайте через 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) 

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
Falcon Space - функциональная веб-платформа разработки на узком стеке MS SQL/Bootstrap. Вводная по Falcon Space
Насколько полезной была статья?

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

Выгода от использования Falcon Space

В 2-3 раза экономнее и быстрее, чем заказная разработка
Более гибкая, чем коробочные решения и облачные сервисы
Используйте готовые решения и изменяйте под свои потребности
Нужна бесплатная консультация?
Получить оценку проекта
Создайте концепцию проекта на основе нашего шаблона и получите оценку проекта в виде КП.
Демо-сайт решений
Базисные решения, которые можно гибко адаптировать под себя: менять внешний вид, бизнес-логику и даже структуру базы данных.
Сайт использует Cookie. Правила конфиденциальности OK