Automation: quick start or “Come on, Excel, write for me!”

A small topic-cheat sheet for quickly writing scripts for automatic processing of ms office documents. And also to help overcome the clean slate syndrome.

As correctly noted in the recent topic , the script itself may not be fast, but the result itself is often more important, and the volumes are not always so large. But Automation allows you to write a script in almost any language. Here, I will issue the templates for JavaScript and IronPython, but I hope there will be a place in the comments for other languages ​​(for example, on PowerShell).

Little lyrics

To work through automation you need the following minimum:

• MS Office (if there are documents, then it seems to be / will be installed)
• Interpreter of the selected language (for JS, VBS and PowerShell this item is not relevant)
• Once again, MS Office for peeping at the generation of code in VBA (in recordable macros)

Once again. The goal is to quickly write a script without complex requirements (performance, embedding, work without ms office, etc.).
')

Actually, the code

IMHO, the most reasonable option is JScript or VBScript (more precisely, the Windows script host ), since the interpreter is already running with the operating system, and you can distribute the script to friends / colleagues, without requiring any additional actions from them - drag the xls (x) file to the script in the explorer and the work went:

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); //  ,   :) } 

The main drawback is that it is difficult to debug (I just killed half an hour in the for loop - the numbering from 0 produced an error about a missing semicolon).

But if the goal is to write quickly, and JS / VBS are known much worse than the familiar and familiar python, then (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() 

Some useful facts.

The most important hint is the macro editor in Excel. We write down the action we want to perform ( View -> Macros -> Record macro ... , the name does not matter), open its code ( View -> Macros -> View macros ... ) and do it in the same way. The macro recording itself is just a set of actions that you want to automate. For example, insert or change some value, change color / font, etc. Macro recording is best done in a separate document, to avoid.

For better orientation in the world of objects, the object world of Excel is a class system (the same for Office 2010 ).

Small bonus: constants for color indexes .

PS Somewhere I had code to work with Automation from C ++, but that's another story :)