Главная

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

2012-03-13 в 8:22, admin, рубрики: automation, Excel, interop, ms office, vba, метки: automation, Excel, interop, ms office, vba

Небольшой топик-шпаргалка для быстрого написания скриптов для автоматической обработки документов ms office'а. А так же для помощи в преодолении синдрома чистого листа.

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

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

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

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

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

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

Имхо, самый разумный вариант — это 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()