Войти | Регистрация | Забыли пароль?
Сервисы поддержки I-поддержка Форумы Примеры Новости База знаний Видеоролики Документация
Сервисы поддержки / Форумы / RSL / RS-6 и Excel - ускорить вывод данных

RS-6 и Excel - ускорить вывод данных

5 (2)
Открыть все сообщения Скрыть все сообщения
  • Развернуть RS-6 и Excel - ускорить вывод данных ( Ulan  09.01.2012 08:12 )
    3(1)
    Добрый день.
    Поделитесь опытом, можно ли как то ускорить вывод больших объемов данных в эксель из RS-6?
    Встречал на форуме, что можно заполнить какой-нибудь массив, а потом уже этот массив указать как значение для вывода в эксель. Но у меня что-то этот способ не работает, во весь указанный диапазон ячеек выдает одно и то же значение, а именно первый элемент массива.
    Убирал обновление экрана в процессе заполнения листа (ExcelApplication.ScreenUpdating = false), но в общем это не помогает при вставке данных, не содержащих формулы.
    Как можно делать вывод не в каждую ячейку, а в диапазон ячеек, хотя бы построчно?
    >> Ответить
    • Развернуть Можно просто в текстовый файл вывести ( Ltybc  09.01.2012 11:46 )
      0(0)
      Вроде xml и потом экселем открыть.
      А если в формате 2007, то вывести в xml, подложить стили и заархивировать и никакой эксель не нужен.
      >> Ответить
      • Развернуть В Windows Reports вроде был способ вывода через csv ( krupennikov  09.01.2012 18:22 )
        0(0)
        Почитайте документацию, там этот способ описан, и вроде это наиболее быстрый способ вывода больших объемов данных. Я сам так не пробовал, я просто без Windows Reports формировал сам csv, и потом открывал этот файл экселем, работало очень быстро.
        >> Ответить
    • Развернуть Посмотри в доке про TArray свойство кажись MarshallBy (-) ( григ  11.01.2012 07:43 )
      2(1)
      Not specified
      >> Ответить
    • Развернуть чем все закончилось? ( developer512  27.02.2012 17:22 )
      0(0)
      тоже сталкиваюсь с такой проблемой. выборка и вывод отрабатывает быстро, но выгрузка в ексель занимает больше половины времени. чем у Вас все закончилось?
      >> Ответить
      • Развернуть Пока ничем, времени совсем нет. ( Ulan  28.02.2012 07:17 )
        0(0)
        Хотя надо бы этим делом снова занятся.
        >> Ответить
        • Развернуть работает! ( developer512  28.02.2012 15:00 )
          0(0)
          оптимизировал выгрузку через csv файл.
          на формирование csv в цикле уходит около 40 сек (порядка 30000 записей, некоторые из них расчитываются в цикле, всего 65 колонок), вставка их в заранее подготовленый шаблон около 2 сек.
          и того не более 45 сек вся выгрузка в ексель! :)
          (при заполнении в цикле или через vbs файл, на выгрузку в ексель уходило порядка 2х часов!!!)

          последовательность такая:
          1) перенаправляем весь вывод в csv файл в папке txtfiles
          2) заполняем его с разделителем (я взял точку с запятой). пример:
          1;2;3;4;5;
          2;3;4;5;6;
          и тд.
          3) копируем себе на локаль этот csv и шаблон excel
          4) открываем excel, устанавливаем курсор в ячейку с которой пойдет заполнение
          5) делаем импорт внешних данных
          cv = xl.querytables.add("TEXT;" + local_dir + csvfile, xl.range("A8")); //<-Заполнение начинаем с ячейки А8
cv.name = csvfile;
cv.fieldnames = True;
cv.rownumbers = False;
cv.filladjacentformulas = false;
cv.preserveformatting = false;
cv.refreshonfileopen = false;
cv.refreshstyle = 1;
cv.savepassword = false;
cv.savedata = true;
cv.adjustcolumnwidth = false; // <- запрещаем раздвигать колонки по размеру данных
cv.refreshperiod = 0;
cv.textfilepromptonrefresh = false;
cv.textfileplatform = 1251; // <- кодировка csv файла
cv.textfilestartrow = 1;
cv.textfileparsetype = 1;
cv.textfiletextqualifier = 1; // <- разделитель - точка с запятой
cv.textfileconsecutivedelimiter = false;
cv.textfiletabdelimiter = false;
cv.textfilesemicolondelimiter = true;
cv.textfilecommadelimiter = false;
cv.textfilespacedelimiter = false;
cv.textfiletrailingminusnumbers = true;
cv.refresh(false); // <- выполняем импорт
          6) отображаем excel
          >> Ответить
          • Развернуть Спасибо ( Ulan  29.02.2012 14:22 )
            0(0)
            Опробуем такой вариант.

            На мой взгляд задание всей этой кучи параметров не есть обязательно. Отформатировать как нужно можно уже и самой VBA, много времени это уже не займет.
            >> Ответить
    • Развернуть На мой взглят проще всего сделать html, любой эксел его берет как родной ( LeonL  05.07.2012 09:05 )
      0(0)
      Вот пример:
      SetOutput("..\\TxtFile\\"+bp+".html",FALSE);
      println("<META HTTP-EQUIV=\"Content-Type\" Content=\"text/html; Charset=CP866\">");
      println("<table border=\"1\" width=\"100%\">");
      println("<thead style=\"display:table-header-group\">");
      println(" <tr>");
      i=0;
      while (i< fc )
      println(" <td>"+ FldComment( b, i ) + "</td>");
      i=i+1;
      end;
      println(" </tr>");
      println("</thead>");

      println("<tbody>");
      while (next(b))
      println(" <tr>");
      i=0;
      while (i< fc )
      println(" <td>"+ b.rec[i] + "</td>");
      i=i+1;
      end;
      println(" </tr>");
      end;
      println("</tbody>");

      println("</table>");
      >> Ответить
      • Развернуть Пробовал такой вариант тоже. ( Ulan  05.07.2012 10:03 )
        0(0)
        Но тут есть некоторые сложности с форматированием и не строгим выводом колонок. Пробовал бороться с этим путем хранения данных в некотором классе, а затем формирование такого файла на основании данных класса. Но как то громоздко и неудобно получилось.

        Использую метод через CSV, на мой взгляд гораздо удобней. Единственные минусы - вывод например лицевых счетов, которые воспринимаются как числа, например "124555412266544855456", борюсь префиксом "'". А также невозможность вывода данных в одной ячейке в несколько строк, с этим мирюсь. Если кто знает способ, подскажите.

        Кстати разродился вот таким файлом библиотечкой с примером в конце, может кому будет полезно: 

        /* ************************************************************************** */
import rslx, rcw, rsexts;
/* ************************************************************************** */
private var ServTempDir      = "..\\txtfile\\";                        // временная директория на сервере
private var TermTempDir      = "c:\\Temp\\";                           // временная директория на терминале
private var TermTempFileName = "TmpExcel" + StrSubSt(String(Time), ":", "") + "." + UserNumber;

var ExcelSeparator = ","; // разделитель дробной части
/* ************************************************************************** */
/* Этакий счетчик */
private var MySequenceCount = 0;
macro XLS_MySequence()
  return MySequenceCount = MySequenceCount + 1;
end;
/* ************************************************************************** */
macro XLS_NVL(pVal1, pVal2)
  if ((ValType(pVal1) == V_UNDEF) or (ValType(pVal1) == 26) or (pVal1 == ""))
    return pVal2;
  end;
  return pVal1;
end;
/* ************************************************************************** */
macro XLS_GetNumb(pStr)   /* преобразует строку типа "AB" в соответсвуюещее число */
  var lCharStr = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
  var i = 1, lLen = StrLen(pStr), lChar, lNum, lResNum = 0;
  pStr = StrUpr(pStr);
  while (i <= lLen)
    lChar = SubStr(pStr, i, 1);
    lNum  = Index(lCharStr, lChar);
    lResNum = lResNum + lNum * Pow(26, lLen - i);
    i = i + 1;
  end;
  return lResNum;
end;
/* ************************************************************************** */
/* сам класс */
class TExcelCSV()
  /* ---------- */
  var Rec, RecCount = 0;
  var cCount = XLS_MySequence();         // тут меняем нумерацию, чтобы добиться уникальности, если у нас одновременно больше чем один CSV-файл
  var cTermF = TermTempDir + cCount + TermTempFileName;
  var cServF = ServTempDir + cCount + TermTempFileName;
  /* ---------- */
  Rec = TArray();
  /* ---------- */
  macro Init()
    file fExcel() txt write;
    if (not Open(fExcel, cServF))
      MsgBox("Не удалось открыть файл");
      Exit();
    end;
  end;
  /* ---------- */
  macro PutRec()               // сброс содержимого записи в CSV-файл и очистка записи
    file fExcel() txt append;
    if (not Open(fExcel, cServF))
      MsgBox("Не удалось открыть файл");
      Exit();
    end;

    var i = 0, lStr = "";
    while (i < Rec.size)
      lStr = lStr + StrSubSt(StrSubSt(String(XLS_NVL(Rec[i], "")), "\t", "   "), "\n", "; ") + "\t";  // заменяем табуляцию на 3 пробела, а Enter на "; "
      i = i + 1;
    end;

    fExcel.Str = lStr;
    if (Insert(fExcel))
      RecCount = RecCount + 1;
    else
      MsgBox("Ошибка записи в CSV-файл");
    end;
    Close(fExcel);
    Rec = TArray();
  end;
  /* ---------- */
  macro PutRecEmpty(pNum)
    if (pNum == null)
      pNum = 1;
    end;
    Rec = TArray();
    var i = 0;
    while (i < pNum)
      PutRec();
      i = i + 1;
    end;
  end;
  /* ---------- */
  macro SetCellValue(pCol, pValue)
    var lType = ValType(pValue);
    if ((lType == V_DOUBLE) or (lType == V_MONEY))
      pValue = StrSubSt(StrSubSt(String(XLS_NVL(pValue, "") : 50 : 10), ".", ExcelSeparator), ",", ExcelSeparator);
    elif ((lType == V_DATE) or (lType == V_DTTM))
      pValue = Trim(SubStr(String(lType)));
    end;

    if (ValType(pCol) == V_INTEGER)
      Rec[pCol - 1] = pValue;
    elif (ValType(pCol) == V_STRING)
      Rec[XLS_GetNumb(pCol) - 1] = pValue;
    end;
  end;
  /* ---------- */
  macro ImportCSV(pExcelSheet, pCellStr, pHideActionFlag)  // импорт ранее заполненых данных в эксель
    if (RecCount <= 0)
      DelFile(cServF); // удаляем файл на сервере
      return;
    end;
    if (not pHideActionFlag)
      BegAction(1, "Импорт данных");
    end;
    if (not CopyFile(cServF, "$" + cTermF))
      MsgBox("Ошибка!!! Не удалось скопировать файл на терминал");
      return;
    end;
    DelFile(cServF); // удаляем файл на сервере

    var lTmp = pExcelSheet.QueryTables.add("TEXT;" + cTermF, pExcelSheet.Range(pCellStr)); // Заполнение начинаем с ячейки pCellStr
    lTmp.name                         = TermTempFileName;
    lTmp.fieldnames                   = true;
    lTmp.rownumbers                   = false;
    lTmp.filladjacentformulas         = false;
    lTmp.preserveformatting           = true;   // сохраняем исходное форматирование
    lTmp.refreshonfileopen            = false;
    lTmp.refreshstyle                 = 1;
    lTmp.savepassword                 = false;
    lTmp.savedata                     = true;
    lTmp.adjustcolumnwidth            = false;  // запрещаем раздвигать колонки по размеру данных
    lTmp.RefreshPeriod                = 0;
    lTmp.textfilepromptonrefresh      = false;
    lTmp.textfileplatform             = 866;    // кодировка csv файла (866 - Кириллица DOS)
    lTmp.textfilestartrow             = 1;
    lTmp.textfileparsetype            = 1;
    lTmp.textfiletextqualifier        = 1;
    lTmp.textfileconsecutivedelimiter = false;
    lTmp.textfiletabdelimiter         = true;   // разделитель табуляция
    lTmp.textfilesemicolondelimiter   = false;
    lTmp.textfilecommadelimiter       = false;
    lTmp.textfilespacedelimiter       = false;
    lTmp.textfiletrailingminusnumbers = true;
    lTmp.Refresh(false);                        // выполняем импорт
    RemoveFile("$" + cTermF);                   // удаляем файл на терминале
    if (not pHideActionFlag)
      EndAction();
    end;
  end;
  /* ---------- */
  Init();
  /* ---------- */
end;
/* ************************************************************************** */
var ExcelApplication, ExSheets = TArray();
macro Ex_Init()
  if (isStandAlone())
    ExcelApplication = ActiveX("Excel.Application", null, true);
  else
    ExcelApplication = CreateObject("rsax", "TRsAxServer", "LoansAxServer", isStandalone()).CreateComObject("Excel.Application");
  end;
  ExcelApplication.Workbooks.Add;

  var i = 1;
  while (i <= ExcelApplication.Sheets.Count)
    ExSheets[i] = ExcelApplication.Sheets(i);
    i = i + 1;
  end;
end;
/* ************************************************************************** */









/* ************************************************************************** */
// Пример использования:  Выводим 10 колонок для 10000 записей на двух листах

var CSV1 = TExcelCSV();
var CSV2 = TExcelCSV();
var CountMax = 10000;

var i = 0, j;
InitProgress(CountMax);
while (i < CountMax)
  // для первого листа
  j = 1;
  while (j <= 10)
    CSV1.SetCellValue(j, "Лист1, ячейка " + i + ":" + j);
    j = j + 1;
  end;
  // для второго листа
  CSV2.SetCellValue("A", "Лист2, колонка А: " + i);
  CSV2.SetCellValue("B", "Лист2, колонка B: " + i);
  CSV2.SetCellValue("C", "Лист2, колонка C: " + i);

  CSV1.PutRec();  // !!! Сбрасываем содержимое буфера в CSV-файл (!!! не забывать это делать, а то ничего не выйдет)
  CSV2.PutRec();  // !!! Сбрасываем содержимое буфера в CSV-файл (!!! не забывать это делать, а то ничего не выйдет)

  UseProgress(i = i + 1);
end;
RemProgress();

Ex_Init();

CSV1.ImportCSV(ExSheets[1], "A10");  // делаем импорт на первом листе начиная с ячейки А10
CSV2.ImportCSV(ExSheets[2], "A5");   // делаем импорт на втором листе начиная с ячейки А5

ExcelApplication.Visible = true;

        >> Ответить
        • Развернуть Можно обойтись без CSV и HTML ( SKucherov  05.07.2012 15:32 )
          0(0)
          Используются SQL запросы... написал примерчик для двухзвенки и для трехзвенки...

          В двухзвенке, вполне можно протестировать этот пример, только PVSW-SQL должен быть настроен.
          в Трехзвенке данный пример не заработает, поскольку нужна настройка MS SQL - шлюза, это уже отдельная тема. Если кто заинтересуется, расскажу.

          В данном примере выводится 65000 (ограничения EXCEL на кол-во строк) записей, в двухзвенке за 11 сек. в трех-звенке за 8 сек.

          Код непосредственного вывода данных довольно простой, ключевая функция в екселе CopyFromRecordset (штатная), выводит массивами в любую табличку... ексель (задаем левый верхний угол и набор RECORDSET) столбцы можно предварительно отформатировать по вкусу....


          Запросы SQL можно усложнять, и всю подготовку данных делать на SQL.
          Если использовать MS SQL - шлюз, то преодолеваются все ограничения языка TSQL в PVSW. Т.е можно использовать хранимые процедуры, пошагоую обработку, временные таблицы для хранения промежуточных результатов и т.д., скорость обработки уже как в MS-SQL, а не как в Pervasive. (т.е раз в 10 быстрее :) )



          Всем удачи.

          import rcw;

var DBRS ,RST, EXCEL;

//это текст запроса... можно усложнять...
var sql="select top 65000 date_carry,real_payer,real_receiver,\"sum\",ground from arhdoc_dbt where result_carry<>23 order by date_carry";

//sql="select 1";// можно использовать для отладки

if (IsStandAlone())               // двухзвенная архитектура
    Excel  = ActiveX("Excel.Application",NULL,false); 
    DBRS = ActiveX( "ADODB.Connection");
    RST = ActiveX("ADODB.Recordset");

     //подключаемся к первасив SQL, задайте свои имя сервера, имя БД пароль и имя пользователя, (обычно делаем для одного виртуального)
    DBRS.ConnectionString =
       "DRIVER={Pervasive ODBC Client Interface};ServerName=xxxxxxxxx;ServerDSN=xxxxxxx;"+
	"UID=xxxxx;PWD=xxxx;TranslationDLL=w32btxlt.dll;";
else
    Excel = CreateObject ("rsax","TRsAxServer","RsAxServer",false).CreateComObject("Excel.Application");
    DBRS = CreateObject ("rsax","TRsAxServer","RsAxServer",false).CreateComObject("ADODB.Connection");
    RST = CreateObject ("rsax","TRsAxServer","RsAxServer",false).CreateComObject("ADODB.recordset");

    // Подключаемся к MS SQL шлюзу, в трехзвенке  // подключаться к PVSW - это значит устанавливать всем пользователям PVSW-клиент.  а этого совсем не хочетя 
    DBRS.ConnectionString =
	"DRIVER=SQL Server;SERVER=сервер_со_шлюзом;database=база_со_шлюзом;";

    //	преобразовываем запрос под шлюз... указываем PVSW сервер и имя БД
    sql="exec dbo.rs @server='xxxxxxxxx', @dsn='xxxxxx', @sql='"+sql+"'";

end;



    DBRS.open();
    RST.Open(sql , DBRS, 2, 1);


    // можно проверить работоспособность RECORDSET, что он заполнился...
    // msgbox(rst.fields(0).value);exit;


  excel.workbooks.add;
  excel.range("B2").CopyFromRecordset(rst);
  excel.visible=true;

          >> Ответить
          • Развернуть MS SQL - шлюз ( Nataliya  10.07.2012 08:44 )
            0(0)
            Расскажите,пожалуйста, поподробнее что такое MS SQL - шлюз и как он работает.
            >> Ответить
            • Развернуть Шлюз ( SKucherov  10.07.2012 10:17 )
              2(1)
              1. Организуем доступ к базам RSbank через Pervasive SQL.

              2. На сервере MS SQL выделяем отдельный DATABASE для целей настоящего шлюза. Организуем ограниченный доступ по чтению для пользователей RSBANK. Тут можно разные способы использовать, например через сетевую идентификацию.

              3. На сервере с MS SQL устанавливаем Pervasive Client. И добиваемся работоспособности запросов с MS SQL сервера к PSQL. Тестовый запрос:
               
select * FROM 
OPENROWSET('MSDASQL',
'DRIVER={Pervasive ODBC Interface};ServerName=xxxxxx;ServerDSN=xxx;UID=xxxx;PWD=xxxxx;TranslationDLL=w64btxlt.dll;',
'select "curdate" from curdate_dbt')


              Подставляем в этот запрос ваши названия PSQL сервера, DSN и данные авторизации на PSQL сервере...

              4. Делаем хранимую процедуру для универсального доступа к таблицам RS через MS SQL. Приведу текст процедуры из примера про Excel.

               

CREATE procedure [dbo].[RS](@server nvarchar(20),@DSN nvarchar(20), @sql nvarchar(3500)) as 
begin
declare @zSql nvarchar(4000)
set @sql=replace(@sql,'''','''''')
set @zSql='SELECT * FROM 
OPENROWSET(''MSDASQL'',
''DRIVER={Pervasive ODBC Interface};ServerName='+@server+';ServerDSN='+@DSN+';UID=xxxxx;PWD=xxxxx;TranslationDLL=w64btxlt.dll;'',
'''+@sql+''')'

exec(@zSql)
end


              Данная процедура позволяет работать с несколькими серверами PSQL одновременно, с разными копиями и версиями RSBANK.

              Вот шлюз и готов. Теперь на рабочих станциях пользователей RSBANK не нужно ставить никаких дополнительных драйверов, клиентов Pervasive иметь возможность доступа к базам RS через MS SQL со всей его мощью и возможностями.



              P.S. Кстати не пробовал сам, но не вижу противопоказаний установить такой же шлюз на ORACLE. Это для тех банков у которых MS SQL не куплен, а ORACLE используется для каких то задач.



              >> Ответить
    • Развернуть Есть сверхбыстый способ вывода больших объемов ( Hammer  16.01.2021 14:29 )
      0(0)
      Существенное замедление происходит из-за огромного количества обращений к ячейкам Excel.
      Если требуется выгрузить большой объем - можно сделать так.
      Подготовить шаблон xlsm с VBA макросом

      Sub LoadCSV(Filename As String, delimiter As String, FirstRow As Integer)
          Dim sFiles As String
          Dim s As String
          Dim d As String
          Dim r As Double
          Dim StartRow As Integer
          
          StartRow = FirstRow
          s = ""
          r = 0
          If (Len(Filename) > 3) Then
              Open Filename For Input As #1
              Do While Not EOF(1)
                  Line Input #1, s
                  If InStr(1, s, delimiter) > 0 Then
                          t = Split(s, delimiter)
                          r = r + 1
                          For i = 0 To UBound(t)
                              Cells(StartRow + r, i + 1) = t(i)
                          Next i
                  End If
              Loop
              Close #1
          End If
      End Sub


      Потом макросом RSL
      - подготовить файл данных в TXT с разделителем
      - открыть подготовленный шаблон Excel и запустить в нем макрос VBA с параметрами,
      например exApp.Run("LoadCSV", FileNameTxt, delimiter, 6);
      //имя запускаемой подпрограммы VBA, имя файла, разделитель, с какой строки загружать.
      Ну и далее можно активировать просмотр документа Excel.






      >> Ответить