Быстрый и красивый импорт из Ecxel?

[Dimonishe]

Здраствуйте.

Есть задача. Выгрузить данные какой-либо таблицы в Excel, провести над ними некоторую довольно сложную обработку, и передать назад в Аксапту.

С экспортом данных все понятно. Остановился на экспорте через CSV как наиболее быстром и универсальном. Но как данные загнать назад в Axaptу?

Вариант с Мастером шаблонов Excel разочарование: работает криво и медленно.

Попробовал обработаный файл сохранять экселем в CSV и поднимать из аксапты. Все работает, но только сильно много телодвижений.

Может стоит заюзать СОМ для автоматизации вызова Аксаптовского импорта из Экселя? Но это регистрация СОМ на машине плюс дырка в безопасности.

Грузить напрямую в базу - еще больше дырка...

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

Кто-нибудь сталкивался?

Спасибо.

[slava]

Цитата:

Изначально опубликовано Dimonishe
Кто-нибудь сталкивался?

Для подобной задачи, как вы выразились, "заюзали" COM. Работает не слишком шустро, но зато позволяет вносить изменения (инфолог ошибок.. и т.д) в исходный импортируемый файл. Пользователи Щастливы

[Dimonishe]

Только что оттестил скорость передачи данных через вызова СОМ. Обнять и плакать. Скорость не то что в разы, а в сотни раз медленней чем через файл. Думаю при больших объемах данных пользователи счастливы не будут....

Наверное оптимально будет служебные операции выполнять через СОМ а данные гонять через файл...

Надо будет попробовать...

З.Ы. Вы получается для каждой таблицы делали свой VB скрипт который загонял данные? С прописыванием всех полей и т.п.?

[mazzy]

Цитата:

Изначально опубликовано Dimonishe
Наверное оптимально будет служебные операции выполнять через СОМ а данные гонять через файл...

Оптимально будет сначала поискать и почитать предыдущие обсуждения этого вопроса.
Обсуждалось уже не раз.

[slava]

Цитата:

Изначально опубликовано Dimonishe
З.Ы. Вы получается для каждой таблицы делали свой VB скрипт который загонял данные? С прописыванием всех полей и т.п.?

Нет, Вы не так поняли Данные я затягивал из Аксапты, вызывая com - Excel. Впрочем непринципиально, скорость что с одной, что с другой стороны одинакова.

[Dimonishe]

Спасибо за советы.

Поиском пользовался. И не раз. Нашел много, но все в основном касается экспорта. Внятно по поводу импорта, такого как меня интересует, мало. Возможно плохо искал.

Прочитал документ http://axapta.mazzy.ru/lib/import/ . Все здорово просто, прозрачно и замечательно. Но только, к сожалению, такое количество действий для импорта выполнять никто не согласится.

Пользователь должен только нажать в экселе кнопку типа "Отослать данные" и они "быстро и красиво ушли в аксапту". Потому как операции эти будут выполняться ежедневно, с внушительным объемом данных, людьми ценящими свое время.

[macklakov]

Цитата:

Изначально опубликовано Dimonishe
Пользователь должен только нажать в экселе кнопку типа "Отослать данные" и они "быстро и красиво ушли в аксапту".

Самые простые варианты ускорения следующие:
- передавать данные в виде массива
- передавать данные через буффер обмена
- использовать dde
- по нажатию кнопки, VB создает файл и через COM вызывает метод axapta, который этот файл подберет

[Dimonishe]

Цитата:

Изначально опубликовано macklakov

- по нажатию кнопки, VB создает файл и через COM вызывает метод axapta, который этот файл подберет

Сейчас пробую реализовать этот способ... Спасибо.

Только не совсем понятно что имеется в виду под передачей в виде массива (насколько я понимаю за один вызов можно передать максимум строку) и как автоматизировать обмен через буфер?

[macklakov]

Цитата:

Изначально опубликовано Dimonishe
Только не совсем понятно что имеется в виду под передачей в виде массива (насколько я понимаю за один вызов можно передать максимум строку)

передача строк целиком, в разы ускоряет обмен

Цитата:

Изначально опубликовано Dimonishe
и как автоматизировать обмен через буфер?

см. TextBuffer.fromClipboard

[Axapta]

А как предлагаемый вариант с Clipboardом будет у Вас работать, если я параллельно запущу две Аксапты и в обеих будут что-то импортировать? =)

[macklakov]

Цитата:

Изначально опубликовано Axapta
А как предлагаемый вариант с Clipboardом будет у Вас работать, если я параллельно запущу две Аксапты и в обеих будут что-то импортировать? =)

Глючно будет работать!

[Ace of Database]

Пример копирования через буфер обмАНА
Добавьте в класс COMExcelDocument_RU такой метод:

PHP код:

container DD_copyRangeToBuf_Lines(str _fromRange, str _toRange, int _sheetNum = 1)
{
    COM         range, range1,
                comWorkSheet;

    int         colNumber, colsNumber;
    int         rowNumber, rowsNumber;
    TextBuffer  textBuffer = new TextBuffer();
    ;

    range = this.findRange(_fromRange, _sheetNum);
    range1 = this.findRange(_toRange, _sheetNum);
    range = m_comApplication.Range(range, range1);

    range.copy();
    textBuffer.fromClipboard();
    textBuffer.setText(strrem(textBuffer.getText(), "\r"));

    return str2con_RU(textBuffer.getText(), "\n");
} 


Он возвратит Вам контейнер.
3-е поле из 5-й по порядку строки можно достать из этого контейнера примерно так:

PHP код:

      Container c;
      int               colNum, lineNum;
      anytype      value;
      ;
      colNum = 3;
      lineNum = 5;
     c =    excelDocument.DD_copyRangeToBuf_Lines("A1", "Z1555" , 1)
     value = conpeek(c, str2con_RU(conpeek(con, lineNum), "\n"),  colNum); 


[Dimonishe]

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

Смысл использовать буфер если можно использовать файл лишенный этих недостатков?

[Владимир Максимов]

Цитата:

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

Да. Так оно и есть.

Цитата:

Изначально опубликовано Dimonishe
Смысл использовать буфер если можно использовать файл лишенный этих недостатков?

Смысл один - скорость и корректность формирования отчета.

У использования файлов есть ряд недостатков по сравнению с буфером

1) Это лишний объект, который надо не забыть удалить и вообще следить за его доступностью. Например, при запуске 2 копий отчета на одной машине есть риск затереть данные одной копии или отчет будет прерван поскольку не сможет создать (удалить) этот самый файл.

2) Мало когда итоговый отчет имеет настолько примитивную структуру, что она один-в-один совпадает с файлом. Т.е. простая сетка. Это значит, что после открытия файла требуется доформатировать отчет "по месту"

3) Нет никакой возможности указать формат отображения отдельных столбцов файла. Там в качестве параметра надо указать двумерный массив, а из AXAPTA это невозможно. Это значит, что часть информации может быть потеряна из-за некорректного определения формата самим Excel. Например, 20-разрядные банковские счета будут записаны как числа с потерей точности, при конвертации в строку (последующее форматирование) восстановить потерянные цифры уже невозможно.

4) Использование файла - это всегда создание листа Excel с нуля. Нет возможности вставить полученные данные в указанный диапазон предварительно созданного шаблона Excel.

[Dimonishe]

2 Владимир.

Насчет всех 4 пунктов спорить не буду дабы не засорять. Хотя есть о чем.

Просто хочу привести способ экспорта данных который кажется довольно удобен:

1.Предварительно в Экселе создаем шаблон. С названиями, шрифтами, цветами, вензелечками и формулами. Походу определяем в каких ячейках какие данные из аксаптовских таблиц у нас будут храниться и настраиваем типы ячеек(!)

2. Готовим Аксаптой CSV файл (или файлы). Администрирование - Периодич Операции - Экспорт Импорт Данных. (Предварительно настроив группы определений)

3. Теперь, в Экселе есть чудесная возможность: Данные -> Импорт Внешних данных. Выбираем наш CSV файл в качестве источника данных, определяем какие у нас разделители, кодировка и т.п. Жмем готово и указываем куда вставлять данные. (На одном листе может быть множество таких диапазонов данных )

Теперь, эксель делает связь поля своего листа с "полем" в CSV файле.

В "свойствах диапазона данных" снимаем галку на "запрашивать имя файла при обновлении" и ставим на "обновление при открытии".

Теперь при каждом открытии файла (или принудительном ручном обновлении), Эксель будет подтягивать данные из CSV хранилища.

Все это делается и настраивается один раз.

Потом, основная работа пользователя - экспортнуть данные в CSV и открыть экселевский шаблон. Данные подтянутся автоматически. Сейчас занимаюсь автоматизацией этих двух действий

Никаких проблем с форматами данных, потерей точности или доформатированием отчета просто нет.

Как вторая часть, на VBA пишется примитивный скрипт, который измененные данные из экселя пишет в тот же самый CSV, который потом через ту же группу определений поднимается из аксапты.

Недостаток - нужно следить за расположением промежуточных файлов.

Если ко всему этому прикрутить автоматизацию по выполнению экспорта/импорта в аксапте и запуску через COM экселя/аксапты, получится простой двусторонний обмен excel-axapta требующий от пользователя минимум напрягов.

P.S. Для таблицы в 65535 записей процесс экспорта/импорта занимает менее 10 сек.

P.P.S. Кстати, никто не знает как запустить из аксапты экспорт данных Jobом и без лишних вопросов? (избежать вызова promt() )

[Владимир Максимов]

Цитата:

Изначально опубликовано Dimonishe
3. Теперь, в Экселе есть чудесная возможность: Данные -> Импорт Внешних данных. Выбираем наш CSV файл в качестве источника данных, определяем какие у нас разделители, кодировка и т.п. Жмем готово и указываем куда вставлять данные. (На одном листе может быть множество таких диапазонов данных )

Вот именно про этот пункт я и говорил! Импорт из текстового файла - это был первый, пришедший в голову способ ускорения вывода отчета.

Вы просто использовали один из вариантов. Альтернатива - это обычное открытие текстового файла (Файл - Открыть). Это метод Workbooks.OpenText() или Workbooks.Open()

Вся проблема в том, что для автоматиизации любой из этих команд необходимо в качестве одного из параметров указать формат вывода столбцов. Ну, что-то вроде: первый столбец - это числа, второй - даты, третий - текст и т.п. В зависимости от избранного способа импорта это будет разный параметр в разных командах.

НО! Во всех случаях - это будет двумерный массив !!!

Вот именно с формированием этого массива и будет затык. Никак его не сформировать из AXAPTA. Не получается Отсюда проблемы с некорректными форматами и потерей точности.

Т.е. пользователь вынужден вручную осуществлять открытие или импорт внешнего текстового файла. Нет возможности автоматизировать этот процесс.

Если найдете способ формирования этого двумерного массива форматов столбцов из AXAPTA обязательно сообщите.

[Dimonishe]

Цитата:

Изначально опубликовано Владимир Максимов

Альтернатива - это обычное открытие текстового файла (Файл - Открыть). Это метод Workbooks.OpenText() или Workbooks.Open()

Нет. Это не альтернатива. Это совсем другой способ. Я говорил о связывании предварительно отформатированных ячеек листа Excel с данными хранящимися в CSV файле.

О каких двумерных массивах может идти речь? (к слову, можно сформировать массив любой мерности из одномерного, просто указав смещение)

Цитата:

Изначально опубликовано Владимир Максимов

Т.е. пользователь вынужден вручную осуществлять открытие или импорт внешнего текстового файла. Нет возможности автоматизировать этот процесс.

Так вот. Указанная в самом первом посте задача была решена и автоматизирована следующим образом (Для обработки прайс-листа):

Пользователь имеет в своем распоряжении три кнопки:

1) В аксапте - кнопка "Выгрузить в Excel". По ее нажатию запускается следующий код :

Данный код запускает Excel, и при открытии книги, связанные ячейки автоматически обновляются данными из CSV.

PHP код:

static void ExcelStart(Args _args)
{
   sysDataExport   ex  =  new sysDataExport();
   ComExcelDocument_RU doc;
   ;

   //Экспортируем группу определений в текстовый файл
   ex.parmGroupId("PrLst");
   ex.initParmFilename("C:\\PriceList.dat");
   ex.parmFiletype(FileType::Comma);
   ex.run();

   // Открываем существующий шаблон
   doc = new ComExcelDocument_RU();
   doc.Open("C:\\PriceList.xls");
   doc.finalize();
} 


Отмечу, что экселевский шаблон должен быть предварительно сформирован разработчиком как было описано мною выше.

2) В Excel - кнопка "Импорт Из Аксапты".

Данная кнопка через COM инициирует экспорт данных Аксаптой в текстовый файл. И обновляет связи.

На кнопке макрос:

Sub ImportFromAxapta()

'Вызов аксаптовского Joba который производит экспорт
Dim Axapta As Object
Dim Args As Object
Set Axapta = CreateObject("AxaptaCOMConnector.Axapta")

Axapta.Logon "COM+", "", "", ""

Set Args = Axapta.CreateObject("args")
Args.Call "parm", "PrLst"
Axapta.CallJob "COMExport", Args
Axapta.Logoff
Set Axapta = Nothing

'Обновление данных для связанных ячеек
ActiveWorkbook.RefreshAll
  
End Sub

Аксаптовский JOB:

PHP код:

static void COMExport(Args _args)
{
      sysDataExport   ex  =  new sysDataExport();
      ;

      if(_args)
         ex.parmGroupId(_args.parm());
      ex.initParmFilename("C:\\PriceList.dat");
      ex.parmFiletype(FileType::Comma);
      ex.run();
} 


3) Excel: Кнопка - "Экспорт в Аксапту". Макрос очищает лист от форматирования и заголовков столбцов и экспортирует данные в текстовый файл. Далее, через COM запускается JOB который в аксапте инициирует операцию импорта

На кнопке макрос:

Sub ExportToAxapta()
 
'Предварительно удаляем CSV хранилище
 Dim fs
 Set fs = CreateObject("Scripting.FileSystemObject")
 fs.DeleteFile "C:\PriceList.dat"
    
'Сохраняем книгу и удаляем заголовки столбцов
 ActiveWorkbook.Save
 Rows("1:1").Select
 Range("F1").Activate
 Selection.Delete Shift:=xlUp
 
'Формируем CSV файл
 ActiveWorkbook.SaveAs Filename:= _
   "C:\PriceList.dat", _
    FileFormat:=xlCSV, CreateBackup:=False

'Возвращаемся к главному документу
 ActiveWorkbook.Close SaveChanges:=True
    Workbooks.Open Filename:= _
        "C:\\PriceList.xls"

'Вызов аксаптовского Joba который производит импорт
Dim Axapta As Object
Dim Args As Object

Set Axapta = CreateObject("AxaptaCOMConnector.Axapta")

Axapta.Logon "COM+", "", "", ""
Set Args = Axapta.CreateObject("args")
Args.Call "parm", "PrLst"

Axapta.CallJob "COMImport", Args
Axapta.Logoff
Set Axapta = Nothing

End Sub

Аксаптовский JOB

PHP код:

static void COMImport(Args _args)
{
        sysDataImport   ex  =  new sysDataImport();
        ;
       if(_args)
       ex.parmGroupId(_args.parm());
       ex.initParmFilename("C:\\PriceList.dat");
       ex.run_modified();
} 


В jobe использован модифицированый метод Run, класса SysDataImport для того, чтобы при вызове через СОМ не происходил вызов диалоговых окон для взаимодействия с пользователем при импорте.

Итого: корректный, прозрачный двусторонний обмен Axapta-Excel с минимальными задержками для практически любых шаблонов.