Automation: быстрый старт или «А ну-ка, Excel, пиши за меня сам!»

Шпаргалка для быстрого написания скриптов для автоматической обработки документов MS OFFICE
auto_Excel

Сама работа скрипта может быть и не быстрой, но чаще важнее сам результат, да и объёмы далеко не всегда такие большие. Зато Automation позволяет написать скрипт практически на любом языке. Здесь я выдам заготовки для JavaScript и IronPython, но, надеюсь, в комментариях найдётся место и для других языков (например, на PowerShell).

Чуть-чуть лирики

Для работы через automation нужен следующий минимум:

  • MS Office (если есть документы, то он, видимо, есть/будет установлен)
  • Интерпретатор выбранного языка (для JS, VBS и PowerShell этот пункт не актуален)
  • Ещё раз MS Office для подглядывание за генерацией кода в VBA (в записываемых макросах)

Ещё раз повторюсь. Цель — быстро написать скрипт без сложных требований (производительность, встраивание, работа без ms office и т.п.).

Microsoft показала «умный» Excel — «формулы» можно задавать на «обычном» языке

Собственно, код

Имхо, самый разумный вариант — это JScript или VBScript (точнее Windows script host), т.к. интерпретатор уже идёт с операционной системой, и можно раздавать скрипт друзьям/коллегам, не требуя от них никаких дополнительных действий — перетащил xls(x)-файл на скрипт в explorer’е и работа пошла:

var excel = WScript.CreateObject("Excel.Application"); var fileName = WScript.Arguments.Item(0);  try { 	main(); } catch(e) { 	// Чтобы процесс не завис в памяти 	delete excel; 	throw e; }  function main() { 	//excel.Visible = true; // Иногда хочется поспать перед экраном, где что-то происходит, но так работает дольше 	//var book  = excel.Workbooks.Add();	// Новая книга (создать) 	var book  = excel.Workbooks.Open(fileName);	// Старая книга 	var sheet = book.Worksheets.Item(1);	// (в VBA нумерация с единицы)   	// Что-то делаем 	for (var i = 1; i <= 10; i++) { // нумерация ячеек с единицы 		sheet.Range("A" + i).Value = "hello_" + i; 	}   	// Сохраняем результат 	book.Close(true); // сохранить 	//book.Close(true, newFileName); // сохранить как... 	WScript.Sleep(2000); // не помню, зачем это :) } 

Главный недостаток — сложно отлаживаться (только что убил полчаса на цикл for — нумерация с 0 выдавала ошибку о пропущенной точке с запятой).

Но если цель — написать быстро, а JS/VBS известны гораздо хуже родного и знакомого питона, то (IronPython):

# coding=utf-8 # IronPython 2.6 # hint: http://www.ironpython.info/index.php/Interacting_with_Excel import clr, os clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c') from Microsoft.Office.Interop import Excel  ex = Excel.ApplicationClass()    #ex.Visible = True ex.DisplayAlerts = False     def main(): 	ws = workbook.Worksheets[1] 	# rowsCount = ws.UsedRange.Rows.Count  	# Делаем что-то полезное: 	for i in range(1,11): 		ws.Range("A{0}".format(i)).Value = 'hello_{0}'.format(i)  # Точка входа с контролем ошибок workbook = ex.Workbooks.Open( os.path.realpath( 'test.xlsx' ) ) try: 	main() finally: 	workbook.Save() 	workbook.Close() 

 

Пишем свой VLOOKUP для того, чтобы не зависеть от стандартного (Excel functions)

Немного полезных фактов

Самая главная подсказка — редактор макросов в Excel. Записываем действие, которое хотим выполнить (View -> Macros -> Record macro…, имя значения не имеет), открываем его код (View -> Macros -> View macros…) и делаем по образу и подобию. Сама запись макроса — просто набор действий, которые хочется автоматизировать. Например, вставить или изменить какое-то значение, поменять цвет/шрифт и т.п. Запись макроса лучше производить в отдельном документе, во избежание.

Для лучшей ориентации в мире объектов объектном мире Excel — система классов (тоже самое, но для Office 2010).

Небольшой бонус: константы для цветовых индексов.