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

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

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

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

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

по ходу эксплуатации программы. Как создается функционал на платформе

Это простой удобный интерфейс

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

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