Удобное внедрение языка программирования "Python" в файл Excel на основе портативной сборки Python, Настроенный файл Excel для удобного вызова скриптов Python из Excel
Vladimir Chebykin, Странная позиция у вас... БМВ, как и я до этого, просто хочет ясности, т.к. кроме абсолютно шаблонных, стереотипных ярлыков, автор ничего не сказал, что в первую очередь говорит о том, что он дальше чем "я увидел на сайте, что так можно и нашел вариант, как НЕ хранить скрипт отдельно" не продвинулся. И вы тоже. Пока только безосновательные разговоры, а это больше про бабушку у подъезда. Никто не говорит, что автор сделал что-то плохое, просто как можно говорить о том, как это здорово работает без каких-либо тестов и сравнений. И сравнивать нужно, РАЗУМЕЕТСЯ, с VBA. Про библу bedvit'а я сказал просто, как следующий этап сравнения производительности, до которого очень маловероятно дойдёт дело
Вы с автором тут непонятно что выгораживаете, хотя никто и не нападает Вы говорите, что автомобиль хорош, хотя за рулём ни разу не сидели - это же абсурд Сделайте всё по инструкции, напишите простой код (только не hello world", а по работе с Excel) и покажите - вот это будет разговор, а пока пустая болтовня ни о чём
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Сергей, добрый день. Питон вне моих интересов, но спасибо за предложенное интересное решение с подробным описанием и ссылками. Встречал запросы на эту тему на форумах без внятных ответов. По моему, любые альтернативные варианты автоматизации Excel найдут чью-то заинтересованность, насмотря на встроенные возможности (VBA, XLL, JS и т,.п.) Удачи Вам в развитии решения!
Ещё раз подчеркну, что за само исследование и описание возможности - спасибо Хотелось бы, конечно конкретики и сравнении, буду рад посмотреть, если будут
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Спасибо большое за поддержку, всем кто поддержал! В своей благодарности я специально не буду указывать имена тех кто поддержал. Чтобы никого не обидеть, ведь, справедливости ради, я получал слова поддержки и от своих критиков (скорее, их правильно называть скептиками).
Цитата
ZVI написал: По моему, любые альтернативные варианты автоматизации Excel найдут чью-то заинтересованность, несмотря на встроенные возможности (VBA, XLL, JS и т,.п.)
ZVI, добрый день! А разве JavaScript встроен в Excel или вы путаете с Google Sheets? (Ответьте, пожалуйста, что он туда встроен - обрадуйте меня!)
ZVI, Владимир приветствую! Правильно ли я понимаю: Надстройка Office - это веб-приложение. Но с добавлением фрамеворка может работать и с локальными версиями, начиная с Excel 2016? Как это работает?
bedvit, Добрый день, Виталий. Да, правильно понимаете. Как работает? - Медленно и достаточно ограниченно. Еще и асинхронно, но какие-то методы обхода проблем асинронности там принимались. Защита кода тоже была условной, когда я это смотрел (несколько лет тому назад). Изначально там, например, не было быстрого копирования значенией из массива в ячейки, как это встроено в VBA, но по просьбе MVPs была добавлена какая-то неполная альтернитива. Отмечу, что со стороны MS была достаточно опратичная реакция на запросы и замечания MVPs, отвечал и дорабатывл framework автор идеи. Но меня все это интересовало поверхностно из-за изрядно ограниченной функциональности и скорости кода (в 2 и более раза медленнее, чем VBA). Если вопрос о том, каким именно образом устроен механизм реализации JS API внутри Excel и framework-а для взаимодействия с локальной версией Excel, то такая информация мне не попадалась, да и незачем MS ее раскрывать - достаточно описания API. Вроде в курилке давно была тема о том, что появился JS API.
Благодарю, очень полезная информация (как всегда).
Цитата
ZVI написал: Вроде в курилке давно была тема о том, что появился JS API.
Признаюсь, пропустил. Про веб-версию читал. Меня интересует тема развития Excel, направление развития, возможности предоставляемые разработчиками (официальными+не официальными). К сожалению, здесь для меня доступно только материалы в свободном доступе, а этого недостаточно. Мне представлялась картина что-то, вроде этой (порядок возникновения - не точный) Excel-4.0-Macro-Functions -> VBA -> C API -> Com-Add-In -> VSTO -> JS API ->? Добавте, что пропустил. Что из этого поддерживается. Насколько я знаю, пока все, но что развивается, какое направление?
Функционал C API в основном базируется на Excel-4.0, что давно уже как бы забыто, вот все функции(579) и методы (805), которые определены в заголовке файла xlcall.h
Кажется много, но не покрывает огромной части СОМ-модели Excel (которую, к примеру может использовать VBA). Но вместе, с тем начиная с Excel 2013 добавлены асинхронные функции (использовал - интересный вариант для некоторых задумок). Возможно JS API, это обертка этих функций? Если С API живо, почему так мало методов (по сравнению с СОМ)? Возможно есть не документируемые С API, которые используются внутренними разработчиками и дают более широкий набор действий по сравнению с текущими. Что из этого является базовыми API (какая модель), или наиболее близкие к движку Excel? По тестам при обращении к листу, СОМ-модель Excel медленее С АРI (получить, присвоить значение около 30%)
у меня к вам предложение – поучаствуйте в тестировании.
Я подготовлю экземпляр Excel cо скриптом Python, а кто-то из вас подготовит его аналог с кодом VBA. Мы сравним результат по производительности и по удобству. Мои условия:
Скрытый текст
Победителем признается решение, набравшее наибольшее количество баллов. Так как мне скорость написания кода одинаково важна со скоростью исполнения кода: баллы определяются преимуществом во времени исполнения кода и в количестве строк кода. Пример начисления баллов: Шаг 1. Ваш код исполняется за 20 секунд, код сопреника за 40: Вам 0,67 баллов, расчет - 40/(20+40). Сопернику - 0,33 балла, расчет – 20 / (20+40). Шаг 2.Ваш код 77 строк, код сопреника 50: Вам 0,39 баллов, расчет - 50/(77+50). Сопернику - 0,61 балл, расчет – 77 / (77+50). Шаг 3. Определение победителя суммой баллов шаг 1 + шаг 2: Ваш код 1,06 (0,67 + 0,39) балла, соперник 0,94 балла (0,33 + 0,61).
Длина строки кода - в традициях лучшей практики читаемости, без злоупотреблений длиной.
Задание для теста: Имеется папка "sales_data" с подпапками с файлами Excel в формате ".xlsx" и ".xls". Файлов много (пусть от 50 до 500, можно будет сделать несколько заездов).
Задача - собрать данные с единственного листа, вывести результат в новой книге Excel, результат представляет из себя агрегированные данные по образцу: 1. Строки отсортированы по возрастанию дат. 2. Даты - последний день в месяце, агрегируют в одну строку все данные за месяц. 3. Столбцы отсортированы по возрастанию итоговых сумм по столбцам. 4. Имеется итоги в правой части таблицы. 5. Умная таблица не обязательна (у меня будет).
главное, чтобы запрос поддерживал перебор всех вложенных подпапок Думаю, что автор просто не думал про сравнение с ним, так как это битва кодов всё-таки (я ведь тоже никогда с PQ не сравниваю). Возьмёшься или тёзку моего позовёшь?
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Лично мне, PQ как штатное средство экселя, будет интересно оттестировать и в одной упряжке с VBA и в соло и как угодно. Но PQ разве покажет высокие скорости исполнения? Было бы интересно и чистый VBA глянуть. Кстати, у меня есть еще вопрос, который давно меня интересовал - на каких языках, кроме VBA, работают встроенные приблуды в Excel? Я знаю DAX для PP, и язык "М" идет с PQ, а какие языки у Excel для пользователей есть еще? SQL?
PooHkrd написал: А потому что автор сразу подложил медведю свинью
На самом деле подкладывать свинюку я не думал - я даже не знал про сложности с .xls у PQ. Но умение работать с разными форматами действительно специально включил в тест.
Я вообще личностно воспринимать ни победу, ни поражение в тестах Python не стану. Я тут ни за что не топлю, ничего не отстаиваю, ничего не теряю, если хотите. Моя награда такая же как и у вас - знание реального положения дела с языками в Excel и общение. Естественно, я мотивирован отстоять Python, но у меня нет иных душевных вложений в мои ожидания, кроме интереса. Предлагать заведомо выигрышные методы сравнения не стану, к счастью, они мне не известны. Разве что оценка по количеству строк кода - она увеличивает шансы Python. Но мне кажется это объективное требование, ведь Python определенно отжертвовал производительностью ради лаконичности, так как специально разрабатывался под лаконичный код. Это нужно как-то проиллюстрировать тем, кто присматривается к языку. А такие, как я понял, в эту тему заглядывают.
Option Explicit
Option Private Module
'====================================================================================================
Sub CollectExcel()
Dim WB As Workbook
Dim x, arr, arrFile() As String, arrAll(), arrCol(), arrCol_1(), arrCol_2()
Dim strContr$, f&, AC&
Dim tx$, t!, i&, r&, nR&
Set WB = ActiveWorkbook: tx = WB.Path
If Not FolderChoose(tx) Then Exit Sub Else t = Timer
If Not GetPaths(tx, arrFile) Then Exit Sub
ReDim arrAll(1 To 1000000, 1 To 4)
strContr = "transaction_date"
arrCol_1 = Array(2, 3, 6)
arrCol_2 = Array(2, 4, 7)
Application.ScreenUpdating = False
AC = Application.Calculation: Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Application.EnableEvents = False
For f = 1 To UBound(arrFile, 1)
Application.StatusBar = "Get data: file " & f & " out of " & UBound(arrFile): DoEvents
Workbooks.Open Filename:=arrFile(f), UpdateLinks:=False, ReadOnly:=False, IgnoreReadOnlyRecommended:=True, CorruptLoad:=xlRepairFile
arr = ActiveSheet.UsedRange.Value2: ActiveWorkbook.Close False
If arr(1, 3) = strContr Then arrCol = arrCol_1: GoTo cyc
If arr(1, 4) = strContr Then arrCol = arrCol_2: GoTo cyc
Err.Raise xlErrNA
cyc: For r = 2 To UBound(arr, 1)
nR = nR + 1: arrAll(nR, 1) = arrFile(f)
For i = 0 To UBound(arrCol)
arrAll(nR, i + 2) = arr(r, arrCol(i))
Next i
Next r
Next f
Application.StatusBar = "Creating table …"
Worksheets.Add After:=ActiveSheet
Cells(1, 1).Resize(1, 4).Value2 = Array("PATH", "Store", "Date", "Value")
Cells(2, 1).Resize(nR, UBound(arrAll, 2)).Value2 = arrAll
Application.StatusBar = False
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.Calculation = AC
Application.ScreenUpdating = True
MsgBox "Files have been processed: " & Format$(UBound(arrFile, 1), "#,##0") & vbLf & "Rows download: " & Format$(nR, "#,##0"), vbInformation, Format$(Timer - t, "0.00 sec")
End Sub
'====================================================================================================
'====================================================================================================
Private Function FolderChoose(ByRef tmpDefPath) As Boolean
Dim PS$
PS = Application.PathSeparator
If Not Right$(tmpDefPath, 1) = PS Then tmpDefPath = tmpDefPath & PS
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Choose the FOLDER"
.ButtonName = "Folder choose"
.Filters.Clear
.InitialFileName = tmpDefPath
.InitialView = msoFileDialogViewDetails
If .Show = 0 Then Exit Function
PS = .SelectedItems(1)
If Len(PS) < 4 Then MsgBox "Can't search in Drive!" & vbLf & "Please, choose the FOLDER!", vbCritical, "FolderChoose": Exit Function
tmpDefPath = PS
End With
FolderChoose = True
End Function
'====================================================================================================
Private Function GetPaths(FolderPath$, arrStr() As String) As Boolean
Dim FSO As New FileSystemObject, n&
ReDim arrStr(1 To 100000)
RecurSearch FSO, arrStr, n, FolderPath
If n = 0 Then Exit Function
ReDim Preserve arrStr(1 To n)
GetPaths = True
End Function
'----------------------------------------------------------------------------------------------------
Private Sub RecurSearch(FSO As FileSystemObject, arrS() As String, n&, FP$)
Dim curFol, iFile, sFol, tx$
Set curFol = FSO.GetFolder(FP)
If curFol.Files.Count Then
For Each iFile In curFol.Files
tx = iFile.Path
If Not tx Like "*~*" And tx Like "*.xls*" Then n = n + 1: arrS(n) = tx
Next iFile
End If
For Each sFol In curFol.SubFolders
RecurSearch FSO, arrS, n, sFol.Path
Next sFol
Set iFile = Nothing: Set curFol = Nothing
End Sub
'====================================================================================================
Оговорки
Выводятся только сами строки из нужных полей, агрегация и преобразования дат в последний день месяца не происходит, но зато сохранены пути к файлам-источникам Причина: я никогда не произвожу подобную агрегацию при сборе, т.к. с этим прекрасно справляется штатная сводная (см. скрин) и при этом не теряется связь, что откуда и сколько пришло. Время на подобную агрегацию не должно занять больше 30% от общего времени сбора, так что можете ещё пару секунд докинуть к неполным 7ми Если пайтон покажет примерно такое же время, то сделаю вариант "вчистую" - с агрегацией и преобразованием дат
Об оценке
С методологией оценки категорически не согласен: • пайтон, как вы и говорили, славится готовыми библиотеками, а это значит, что вместо того, чтобы писать код самому, программист обращается к готовым процедурам и функциям - в таком случае, справедливо будет считать строки только основной процедуры (48 строк от "Sub" до "End Sub") и не считать вспомогательные функции • неужели для вас и правда двукратный прирост в скорости и двукратный прирост в теле кода - это одно и тоже??? Лично я готов ОДИН раз написать функцию/процедуру хоть на сотни строк, чтобы потом просто её вызывать и экономить И время кодирования, И время работы макросов Предложить иной способ сравнения не готов, достаточно просто предоставить ваш вариант кода и время выполнения (просто 2 числа без сложения и сравнивания суммы) - я дам свою оценку и обосную, также, как и остальные • сбор данных из других файлов, это, как видно, конечно, не является проблемой для VBA, однако мне гораздо более интересны тесты типа "закрасить ячейки по условию", найти значение в диапазоне, ВПР и так далее — внутренняя кухня, то есть
PooHkrd, привет, тёзка! Ну преобразуй в "*.xlsx" и сделай свой вариант - мне интересно, да и другим, думаю, тоже
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Не, тогда теряется чистота эксперимента, если мы начинаем преобразовывать, то тогда уж можно и xlsx преобразовать в csv, а из текста PQ читает быстрее всего. Я же не преобразую его силами самого PQ.
не соглашусь - на данный момент использование xls ввиду отсутствия у пользователя версии Excel выше 2003 является абсурдом. В таком случае, ни о каком Power Query не может быть и речи. В большинстве случаев файл имеет формат xls просто потому что "у нас так принято" или файл просто очень древний и копировался миллионы раз с бородатых лет вместо перенесения данных в новый чистый файл ИМХО тестируй на 12ти файлах из папки NEW и да прибудет с тобой сила
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous написал: В большинстве случаев файл имеет формат xls просто потому что "у нас так принято" или файл просто очень древний и копировался миллионы раз с бородатых лет вместо перенесения данных в новый чистый файл
или программа экспортирует в него по умолчанию, даже если есть экспорт в xlsx что касается кода, то возможно не открывая файл, а беря данные через ADO будет шустрее. но тут мы на количество строк попадем :-)
Jack Famous, ок. Благодарю за конструктивное участие в теме! Сегодня вечером сяду делать свой вариант.
Цитата
Jack Famous написал: справедливо будет считать строки только основной процедуры (48 строк от "Sub" до "End Sub") и не считать вспомогательные функции
Хорошо.
Цитата
Jack Famous написал: достаточно просто предоставить ваш вариант кода и время выполнения (просто 2 числа без сложения и сравнивания суммы) - я дам свою оценку и обосную, также, как и остальные
Так не пойдет. Тесты провести в пределах одного компьютера. Файлы разместить в пределах одного диска. Только тогда сравнивать.
БМВ: не открывая файл, а беря данные через ADO будет шустрее
не для теста, а для себя даже - где посмотреть примеры? У меня тысячи файлов иногда собираются и скорость мне очень важна (ну скорость мне всегда важна, если уж честно). Можно ли при таком подходе заполнять диапазоны объединённых ячеек данными из первой, как при работе с листом?
Цитата
Сергей: Тесты провести в пределах одного компьютера. Файлы разместить в пределах одного диска
тогда вы у себя, пожалуйста Мой макрос предоставит возможность выбрать папку для поиска всех файлов *.xls* в ней, так что у вас всё получится Жду результатов
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous написал: не соглашусь - на данный момент использование xls ввиду отсутствия у пользователя версии Excel выше 2003 является абсурдом.
xlsb очень распространенный формат для инструментов на формулах, и современный к тому же. Но и с ним у PQ те же самые проблемы. Короче уже мульен раз писал, проблема не в формате, а в том пути который выбрали кодеры PQ для создания коннектора подключающегося к книгам экселя - он был ошибочным. А по тестированию, найду время на неделе, поучаствую, преобразовывать ничего не буду, буду лепить как есть. Если считает с ошибкой, значит так тому и быть. Но при любых раскладах бинарные форматы + PQ это вселенский тормоз.
PooHkrd, потестируй на 12ти xlsx-файлах — это самый чистый результат будет. Ошибок нет и преобразовывать не надо
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous написал: не для теста, а для себя даже - где посмотреть примеры?
так отсюда пляши Eсли использовать UNION, то можно в рекорд сете получить сразу таблицу консолидированную Мне лень писать процедуру фомирующую тупой запрос для Pivot
from pathlib import Path
import pandas as pd
import xlwings as xw
import time
# Сбор в папке sales_data, которая по умолчанию размещена в текущем каталоге:
this_dir = Path(__file__).resolve().parent
parts = []
for path in (this_dir / "sales_data").rglob("[!~$]*.xls*"):
part = pd.read_excel(path)
parts.append(part)
df = pd.concat(parts)
# Название столбцов - города. Агрегируются значения с общей датой:
pivot = pd.pivot_table(df, index="transaction_date", columns="store", values="amount", aggfunc="sum")
# Агрегирование по месяцам. Сортировка столбцов по итоговому значению. Cтолбец "Total".
summary = pivot.resample("M").sum()
summary = summary.loc[:, summary.sum().sort_values().index]
summary.loc[:, "Total"] = summary.sum(axis=1)
# Вывод в Excel таблицы и времени окончания работы:
xw.view(summary)
xw.Book.caller().sheets[0]['B14'].value = time.strftime("%H:%M:%S", time.localtime())