AndyF, спасибо за тесты. Интересно, работают ли другие XLL в этой конфигурации. Нужно найти еще какую либо и протестировать Складывается впечатление, что это проблема на стороне Excel. 2007 этот первая версия с увеличенным количеством строк и новым типом данных в C API Excel - XLOPER12. Я использую этот тип. Возможно это как -то взаимосвязано.
AndyF написал: Excel x32, как они будут работать на х64
если нет WinApi, то ничего не изменится. Может есть возможность в другой версии Excel. 2007 не очень удачный с меню и с его настройкой, как по мне. Опять же х64 может использовать оперативки больше чем 2 Гбайт. Я из-за этого и перешел на х64.
testuser написал: в vba получить указатель разименовать, поработать с данными, и отпустить указатель, как буд-то ни чего не был, а менеджер памти Excel пусть делает что хочет. В общем на VBa все это не проблемма
Теперь все прояснилось, благодарю.
Цитата
testuser написал: нельзя ли ее привести к какому-то такому виду?
Уверен, что после указателей, вам не составит труда справится и с этой проблемой!
testuser написал: MS почему-то нет по ней информации
Скрытый текст
Код
/*
** XL 12 Basic Datatypes
**/
typedef INT32 BOOL; /* Boolean */
typedef WCHAR XCHAR; /* Wide Character */
typedef INT32 RW; /* XL 12 Row */
typedef INT32 COL; /* XL 12 Column */
typedef DWORD_PTR IDSHEET; /* XL12 Sheet ID */
/*
** XLREF structure
**
** Describes a single rectangular reference.
*/
typedef struct xlref
{
WORD rwFirst;
WORD rwLast;
BYTE colFirst;
BYTE colLast;
} XLREF, *LPXLREF;
/*
** XLMREF structure
**
** Describes multiple rectangular references.
** This is a variable size structure, default
** size is 1 reference.
*/
typedef struct xlmref
{
WORD count;
XLREF reftbl[1]; /* actually reftbl[count] */
} XLMREF, *LPXLMREF;
/*
** XLREF12 structure
**
** Describes a single XL 12 rectangular reference.
*/
typedef struct xlref12
{
RW rwFirst;
RW rwLast;
COL colFirst;
COL colLast;
} XLREF12, *LPXLREF12;
/*
** XLMREF12 structure
**
** Describes multiple rectangular XL 12 references.
** This is a variable size structure, default
** size is 1 reference.
*/
typedef struct xlmref12
{
WORD count; //WORD
XLREF12 reftbl[1]; /* actually reftbl[count] */
} XLMREF12, *LPXLMREF12;
/*
** FP structure
**
** Describes FP structure.
*/
typedef struct _FP
{
unsigned short int rows;
unsigned short int columns;
double array[1]; /* Actually, array[rows][columns] */
} FP;
/*
** FP12 structure
**
** Describes FP structure capable of handling the big grid.
*/
typedef struct _FP12
{
INT32 rows;
INT32 columns;
double array[1]; /* Actually, array[rows][columns] */
} FP12;
/*
** XLOPER structure
**
** Excel's fundamental data type: can hold data
** of any type. Use "R" as the argument type in the
** REGISTER function.
**/
typedef struct xloper
{
union
{
double num; /* xltypeNum */
LPSTR str; /* xltypeStr */
#ifdef __cplusplus
WORD xbool; /* xltypeBool */
#else
WORD bool; /* xltypeBool */
#endif
WORD err; /* xltypeErr */
short int w; /* xltypeInt */
struct
{
WORD count; /* always = 1 */
XLREF ref;
} sref; /* xltypeSRef */
struct
{
XLMREF *lpmref;
IDSHEET idSheet;
} mref; /* xltypeRef */
struct
{
struct xloper *lparray;
WORD rows;
WORD columns;
} array; /* xltypeMulti */
struct
{
union
{
short int level; /* xlflowRestart */
short int tbctrl; /* xlflowPause */
IDSHEET idSheet; /* xlflowGoto */
} valflow;
WORD rw; /* xlflowGoto */
BYTE col; /* xlflowGoto */
BYTE xlflow;
} flow; /* xltypeFlow */
struct
{
union
{
BYTE *lpbData; /* data passed to XL */
HANDLE hdata; /* data returned from XL */
} h;
long cbData;
} bigdata; /* xltypeBigData */
} val;
WORD xltype;
} XLOPER, *LPXLOPER;
/*
** XLOPER12 structure
**
** Excel 12's fundamental data type: can hold data
** of any type. Use "U" as the argument type in the
** REGISTER function.
**/
typedef struct xloper12
{
union
{
double num; /* xltypeNum */
XCHAR *str; /* xltypeStr */
BOOL xbool; /* xltypeBool */
int err; /* xltypeErr */
int w;
struct
{
WORD count; /* always = 1 */
XLREF12 ref;
} sref; /* xltypeSRef */
struct
{
XLMREF12 *lpmref;
IDSHEET idSheet;
} mref; /* xltypeRef */
struct
{
struct xloper12 *lparray;
RW rows;
COL columns;
} array; /* xltypeMulti */
struct
{
union
{
int level; /* xlflowRestart */
int tbctrl; /* xlflowPause */
IDSHEET idSheet; /* xlflowGoto */
} valflow;
RW rw; /* xlflowGoto */
COL col; /* xlflowGoto */
BYTE xlflow;
} flow; /* xltypeFlow */
struct
{
union
{
BYTE *lpbData; /* data passed to XL */
HANDLE hdata; /* data returned from XL */
} h;
long cbData;
} bigdata; /* xltypeBigData */
} val;
DWORD xltype;
} XLOPER12, *LPXLOPER12;
/*
** XLOPER and XLOPER12 data types
**
** Used for xltype field of XLOPER and XLOPER12 structures
*/
#define xltypeNum 0x0001
#define xltypeStr 0x0002
#define xltypeBool 0x0004
#define xltypeRef 0x0008
#define xltypeErr 0x0010
#define xltypeFlow 0x0020
#define xltypeMulti 0x0040
#define xltypeMissing 0x0080
#define xltypeNil 0x0100
#define xltypeSRef 0x0400
#define xltypeInt 0x0800
#define xlbitXLFree 0x1000
#define xlbitDLLFree 0x4000
#define xltypeBigData (xltypeStr | xltypeInt)
/*
** Error codes
**
** Used for val.err field of XLOPER and XLOPER12 structures
** when constructing error XLOPERs and XLOPER12s
*/
#define xlerrNull 0
#define xlerrDiv0 7
#define xlerrValue 15
#define xlerrRef 23
#define xlerrName 29
#define xlerrNum 36
#define xlerrNA 42
#define xlerrGettingData 43
Цитата
testuser написал: освобождение указателя на массив, но это не проблема.
уверен, что вы не до конца понимаете с чем столкнетесь. Зачем вам это? Как вы с этими данными будете работать? Не будет там никакого safearrey, это СОМ, не путайте.
testuser написал: передавать/получать данные непосредственно по указателю массива типа XLOPER12??
передавать куда, и получать откуда? XLOPER12 это Си-шная структура, кто будет управлять памятью, когда я вам передам указатель на массив? Кто будет освобождать память занятую элементами массива? Каковы границы жизни указателя на массив и данных в нем (связан с первыми вопросами)?
Что отфильтровать? При чем здесь умная таблица? Если ты про свое, то я говорю про обычную выгрузку и про копи-паст. Все они вставляют в скрытый диапазон. А про то, о чем ты говоришь - я без кода не понимаю.
это пустой XLOPER12, в VBA не смотрел какой это тип. Надо проверить, можете потестировать, возможно Empty.
testuser, да похож на Variant, только немного попроще. Данный инструмент XLLcmdE12xlSet() по скорости обгоняет .Value2 на 15% XLOPER12 используется в Excel C API, так же как Variant в VBA. Собственно это основной тип (структура) данных. Для VBA, Excel трансформирует XLOPER12 в Variant и наоборот. В XLL я делаю это сам (ранее руками, теперь написал свой класс XloperX, даже в новостях от 7.04.24 отметил.)
Функция/Метод XLLcmdE12xlSet(XCHAR * param, LPXLOPER12 param2, LPXLOPER12 pxReference, LPXLOPER12 pxValue) param - командная строка XLL API - см. "XLL API": 1й параметр - Режим диалога для комманд меню XLL - см. "Диалог" param2 – зарезервирован
pxReference - Прямоугольная ссылка (адрес), описывающая целевую ячейку или ячейки. Адрес должен описывать смежные ячейки.
pxValue - Значение или значения, помещаемые в ячейку или ячейки.
Аргумент pxValue pxValue может быть значением или массивом. Если это значение, этим значением заполняется весь диапазон назначения. Если это массив, элементы массива помещаются в соответствующие расположения в прямоугольнике.
Если для четвертого аргумента используется горизонтальный массив, он дублируется вниз, чтобы заполнить весь прямоугольник. Если используется вертикальный массив, он дублируется вправо для заполнения всего прямоугольника. Если вы используете прямоугольный массив, и он слишком мал для прямоугольного диапазона, в который вы хотите его поместить, этот диапазон заполняется исходными данными размером с массив, а остальной диапазон заполняется #Н/Д.
Если целевой диапазон меньше исходного массива, значения копируются до пределов целевого диапазона, а лишние данные из массива игнорируются.
Чтобы очистить элемент прямоугольника назначения, используйте элемент массива типа Empty в исходном массиве. Чтобы очистить весь прямоугольник назначения, опустите четвертый аргумент.
Ограничения Невозможно отменить xlSet . Кроме того, он удаляет все сведения об отмене, которые могли быть доступны ранее. XlSet может помещать в ячейки только константы, а не формулы. Максимальная длина помещаемой строки 8190 символов.
Возвращает код ошибки: 0 - команда выполнена успешно и Error 2036, #NUM!, #ЧИСЛО! - если ошибка.
Пример использования:
Код
Option Explicit
Sub TestXLLcmdE12xlSet()
Dim i, testSize As Long: testSize = 2
Dim arrOrValue: ReDim arrOrValue(1 To testSize, 1 To 1)
For i = 1 To testSize
arrOrValue(i, 1) = Str(i) 'MAX количество символов для ячейки: 8190
Next
'Варианты использования:
'1.Выводим массив или значение по указанному диапазону на лист Excel
Debug.Print Application.RUN("XLLcmdE12xlSet", "", 0, Range("a1:a2").Address, arrOrValue)
'2. Помещаем в диапазон значение "1"
Debug.Print Application.RUN("XLLcmdE12xlSet", "", 0, Range("b1:b2").Address, "1")
'3. Выводим массив по указанному диапазону на лист Excel, первый элемент = Empty
arrOrValue(1, 1) = Empty 'первый элемент Empty
Debug.Print Application.RUN("XLLcmdE12xlSet", "", 0, Range("c1:c2").Address, arrOrValue)
'4 Очищаем заданный диапазон от данных
[d1:d5] = 2 'заполняем данными (2) диапазон для теста
Debug.Print Application.RUN("XLLcmdE12xlSet", "", 0, Range("d1:d2").Address) ' очищаем в заданном диапазоне
End Sub
XLL это обычная надстройка, ее не нужно запускать все время. Ее нужно поместить в папку для надстроек и подключить (поставить галку в надстройках). Если файл открывать из любого другого места, без размещения в надстройках, он будет открыватся как обычный файл, и при закрытии Excel, нужно будет открывать ещё раз. Еще есть файл установки, он делает все сам (размещает в парке надстроек - надстройку) и подключает её (ставиь галку в надстройках). Выполняется один раз.
Когда перестала работать формула, появлялась ли какая ошибка?
При каких действиях слетела надстройка? Можно поставить заново и начисто (удалив, в папке надстроек файлыBedvitXLL.dll и BedvitCOM.dll и BedvitXLL.bin). Но хотелось бы понять причину, по которой перестало работать.
PooHkrd написал: Смотрите здесь . Экспериментируйте на копии!
Цитата
Gerzzog написал: Сработало на модели где немного строк, а вот на большой модели макрос выбивает ошибку "An error occurred - Method Open of object _Recordset failed"
Была задача автоматизировать процесс в одну кнопку. DAX Studio не справился, потому как я не умею его готовить.
Посему пришлось использовать решение выложенное выше с апгрейдом: 1.Выгружаются данные любого размера (у меня 2,5 млн строк, 60 столбцов, в основном текст) 2.Спец.символы экранируются, в данном случае кавычки 3.Числа сохраняются без кавычек, что правильно распознается SQL-сервером 4.Дата сохраняется в формате, который понимает SQL: "yyyy-mm-dd hh:mm:ss" 5.Отключен Timeout, который вызывал отключение запроса на больших временных отрезках (больших данных): cmd.CommandTimeout = 0 ' 6.Поменял фильтр CSV-файлов для нового Excel, кто хочет может допилить до "правильного" кода не привязанного к фильтру, а привязанного к CSV: OFD.FilterIndex = 21 7.Оставил всеми любимый GoTo - на функционал не влияет, кто хочет подпилить - прошу. 8.Файл выгружается в кодировке Windows-1251, не знаю, может ли VBA выгружать в другой, мне было не нужно, можно тоже подпилить при желании.
Да, забыл... По скорости выгрузки +- паритет с DAX Studio (у меня в тестах быстрее, чем DAX Studio), Плюс NULL-значения не выгружает (;;), что в SQL и распознается как NULL. DAX Studio выгружает как пустую строку(;"";), что, я считаю, некорректно, т.к. в SQL попадает как пустая строка. Плюс DAX Studio дату оформляет в виде строки с кавычками так, что SQL не распознает как дату. Плюс DAX Studio проставляет в число системный разделитель дробной части, т.е. "," (запятая). В инструменте всегда "." (точка), что корректно загружается в SQL.
Из особенностей инструмента: числовые значения если число дробное и меньше нуля, ведущий нуль до разделителя дробной части не проставляется, пример: число 0,002365 отображается как .002365, так отрабатывает Str(), такие числа нормально распознаются, проблем не было. Иногда число отображается в Экспоненциальном виде 2.12344E+21, по той же причине. В моем проекте с этим тоже проблем не было.
Код
'Экспорт в CSV из модели данных PowerQuery
'06.02.2024
'Для работы нужно активировать библиотеки (Tools -> Preferences...):
'-Microsoft Scripting Runtime
'-Microsoft ActiveX Data Objects 6.1 Library
'-Microsoft ActiveX Data Objects Recordset 6.0 Library
Option Explicit
Public FSO As New FileSystemObject
Sub main()
Dim ModelList As String
Dim OFD As FileDialog
Dim i As Integer
Dim ModelNum As Integer
Dim tmp As String
Dim t
ModelList = "Доступные в данной книге модели:" + Chr(10) + Chr(13) + Chr(13)
If ThisWorkbook.Model.ModelTables.Count <> 0 Then
For i = 1 To ThisWorkbook.Model.ModelTables.Count
ModelList = ModelList & i & ". " & ThisWorkbook.Model.ModelTables.Item(i).Name & Chr(10) & Chr(13)
Next i
Else
ModelList = ModelList & " Нет доступных моделей"
End If
ModelNameInput:
ModelNum = 0
tmp = InputBox(ModelList, "Введите номер модели")
If IsNumeric(tmp) Then
If CInt(tmp) > ThisWorkbook.Model.ModelTables.Count Or CInt(tmp) <= 0 Then
MsgBox "Incorrect Model num", vbOKOnly
GoTo ModelNameInput
Else
ModelNum = CInt(tmp)
End If
Else
If tmp <> "" Then
MsgBox "Incorrect Model num", vbOKOnly
GoTo ModelNameInput
Else
Exit Sub
End If
End If
Set OFD = Application.FileDialog(msoFileDialogSaveAs)
OFD.Title = "Выберите путь и имя файла"
OFD.ButtonName = "Сохранить"
OFD.FilterIndex = 21
OFD.InitialFileName = "export.csv"
OFD.InitialView = msoFileDialogViewLargeIcons
t = Timer
If OFD.Show <> 0 Then
Call ExportToCsv(ThisWorkbook.Model.ModelTables.Item(ModelNum).Name, OFD.SelectedItems.Item(1), True)
Else
End If
Set OFD = Nothing
MsgBox "Готово! Время, сек.: " & (Timer - t), vbInformation
End Sub
Public Sub ExportToCsv(QueryName As String, exportPath As String, ShowColumnNames As Boolean)
Dim wbTarget As Workbook
' Dim ws As Worksheet
Dim rs As Object
Dim cmd As New ADODB.Command
Dim fileCSV As TextStream
Dim i As Long, x, max_iter, iter, sn, row, col, strCSV As String
Dim batch_size: batch_size = 1000 ' Размер одного блока '1000
Application.StatusBar = "(" & Now() & ") Экспорт данных в CSV..."
DoEvents
Set wbTarget = ThisWorkbook
' Загрузка модели
wbTarget.Model.Initialize
'Send query to the model
Set cmd.ActiveConnection = wbTarget.Model.DataModelConnection.ModelConnection.ADOConnection
cmd.CommandTimeout = 0 ' Отключение прерывания подключения после определенного времени
cmd.CommandText = "EVALUATE '" & QueryName & "'" '
Set rs = CreateObject("ADODB.Recordset")
rs.Open cmd
Set fileCSV = FSO.CreateTextFile(exportPath, True)
If ShowColumnNames Then
For i = 0 To rs.Fields.Count - 1
strCSV = strCSV & IIf(i > 0, ";", "") & VarToCSV(Mid(rs.Fields(i).Name, InStr(1, rs.Fields(i).Name, "[") + 1, Len(rs.Fields(i).Name) - InStr(1, rs.Fields(i).Name, "[") - 1))
Next i
fileCSV.Write strCSV & vbNewLine
End If
max_iter = rs.RecordCount \ batch_size + IIf(rs.RecordCount Mod batch_size, 1, 0)
For iter = 1 To max_iter
sn = rs.GetRows(batch_size)
ReDim c(0 To UBound(sn))
ReDim b(0 To UBound(sn, 2))
For row = 0 To UBound(sn, 2)
For col = 0 To UBound(sn)
c(col) = VarToCSV(sn(col, row))
Next
b(row) = Join(c, ";") 'строка
Next
strCSV = Join(b, vbNewLine) & vbNewLine 'строки
fileCSV.Write strCSV
Next iter
fileCSV.Close
rs.Close
Set fileCSV = Nothing
Set rs = Nothing
Set cmd = Nothing
Application.StatusBar = False
End Sub
Function VarToCSV(ByVal v) As String
If VarType(v) = vbDate Then
If v = Int(v) Then
VarToCSV = FORMAT(v, "yyyy-mm-dd")
Else
VarToCSV = FORMAT(v, "yyyy-mm-dd hh:mm:ss")
End If
VarToCSV = """" & VarToCSV & """"
ElseIf VarType(v) = vbString Then
VarToCSV = """" & Replace(v, """", """""") & """"
ElseIf VarType(v) = vbEmpty Then
VarToCSV = "" '"NULL"
ElseIf VarType(v) = vbNull Then
VarToCSV = "" '"NULL"
Else
VarToCSV = Trim(Str(v))
End If
End Function
+XLLcmdRangeStore() - добавлена возможность использования разных локализаций и разной разрядности Excel при шифровании, дешифровки и сохранении формул/значений (см. видеосправку ниже). +Добавлена обработка ошибок на листе Excel при шифровании, дешифровки и сохранении формул/значений.
Как сохраняются текстовые форматы в функциях форматирования в разных локализациях Win + Excel, как сам Excel с этим справляется пока вопрос открытый.
Андрей VG, Андрей привет! Подскажи, а существует ли в DAX Studio.возможность автоматизировать процесс выгрузки данных в CSV. К примеру жмакнув где-то кнопку?
Senaki, к сожалению, единственного тестера в той теме забанили (не за взлом - я давал офф. разрешение, как разработчик на "стресс-тестирование", а за несоблюдение других правил). Сам с интересом почитаю, если такая информация появятся.
Бахтиёр, а) этот инструмент позволяет зашифровать нужный диапазон данных (формул) и пользователь может так же на них ссылатся, когда нужно - можно этот диапазон расшифровать и сделать пересчет модели. б) формулу можно пересчитать и зашифровать, при следующем пересчёте - можно сделать это еще раз. В динамике получать результат из зашифрованных формул нельзя.
+Для работы нужна надстройка, ее можео просто открыть или установить в папку надстроек. Работает как обычная надстройка под правами пользователя.
+Поддерживаются все символы юникода, какие сможет отобразить Excel - это вопрос шрифтов.
Новая редакция BedvitXLL64v5.0b (beta) Добавленный функционал: +для каждой команды есть всплывающие подсказки +хранить и шифровать можно значения (value2) или формулы (formula). Формула может содержать максимум 8 тыс. символов +добавлены разные алгоритмы шифрования +тип данных и алгоритм шифрования запоминается при зашифровки, и их нужно указывать только при шифровании. При расшифровки нужен только 1 пароль (повтор пароля нужен только для шифрования, для расшифровки достаточно одного-первого) +шифровать можно на листе, шифровать можно сохраняемые диапазоны и шифровать можно и файл пользовательских сохранений. +сохранять диапазон в списке диапазонов можно в зашифрованном виде или не в зашифрованном (в незашифрованном эффективнее сжимаются сохранения т.к. обрабатываются все дубликаты во всех сохранённых диапазонах, а не частично в отдельном диапазоне), но можно шифровать сам файл пользовательских настроек - эффективность сжатия сохраняется. +есть возможность автоматически определять границы диапазона, выделяя весь лист или целый столбец или целую строку, см. usedrange +добавлена возможность включать пользовательские сообщения и сообщения в статус-баре Excel +ЭКСПЕРЕМЕНТАЛЬНО: добавлено автоматическое распознавание разных блоков зашифрованных диапазонов, с разными алгоритмами и их расшифровка разом (см. видео).