Standard of Excellence

Dmitry Khudorozhkov

4.53/5 (9 votes)

Mar 2, 2006

5 min read

62662

786

Learn how to integrate MS Excel functionality with your JScript/WSH scripting solutions.

Download source - 0.9 Kb

Introduction

This article (complementary to my article on MS Word automation) covers the following topics:

creating and populating an Excel document;
navigating within a Workbook and working with Workbook elements;
creating charts from existing Excel data (see also Keith Thoresz's article on inserting images);
communicating with other Office applications via the clipboard.

The main purpose of this article is to explain the basic methods of JScript-based Excel automation. It is in no way a comprehensive overview of the subject, but just a starting point in your quest. I hope it will help everyone who spent days searching for non-Visual Basic scripting samples.

This article assumes you are familiar with JScript.

General HowTo's

Startup.
- Let's start with a new Excel application instance:
```
var ExlApp = new ActiveXObject("Excel.Application");
```
  At this point, you have an empty Excel running in the background (invisible). If you wish to view the whole process, execute:
```
ExlApp.Visible = true;
```
  Beware that, in spite of the application being invisible, you'll face all the messages it generates (like "Would you like to overwrite the file?", "Do you really wish to delete a worksheet?", etc.). To suppress all warnings, thus making Excel completely silent, do the following:
```
ExlApp.DisplayAlerts = false;
```
  In this case, all actions are performed according to the default settings.
- The next step is the creation of a new workbook (in other words, a new file):
```
var WorkBook = ExlApp.Workbooks.Add();
```
  This creates a workbook with three worksheets.
- You can control the number of sheets that are created with a new workbook, through the SheetsInNewWorkbook property:
```
var ExlApp = new ActiveXObject("Excel.Application");

ExlApp.SheetsInNewWorkbook = 1; // Create only one sheet

ExlApp.Visible = true;
```
- Last step here - activate the very first sheet:
```
var Sheet = WorkBook.ActiveSheet;
```
Navigating through the sheets.
- The latest expression, by using the ActiveSheet object, brought you to, as you may have guessed, the active sheet. Eventually, you may wish to create a new sheet:
```
WorkBook.Sheets.Add()
// places new sheet before the active sheet
```
  rename a sheet:
```
WorkBook.ActiveSheet.Name = "SheetOne";
```
  activate another sheet:
```
WorkBook.Sheets("SheetTwo").Activate();
var Sheet = WorkBook.ActiveSheet;
```
  or even delete a sheet:
```
WorkBook.Sheets("SheetTwo").Delete();
```
Populating the cells.
- Conventional ("cell by cell") way of putting data into the cell is:
```
Sheet.Range("Cell-Coordinates").Value = value;
```
  For example:
```
Sheet.Range("A5").Value = "5.01.2006";
```
- Together with the Range object, you can use the Cells collection with the row/column indexes:
```
Sheet.Cells(rowIndex, columnIndex)
```
  The previous example with Range can be rewritten as follows:
```
Sheet.Cells(5, 1).Value = "5.01.2006";
```
Navigating through the cells.
Usually, you don't need to manually move the cell selection cursor to put a value in it. Nevertheless, you can make a cell an active cell, by hand:
```
Sheet.Range("A5").Activate();
```
If you need to select a range of cells (for example, to perform some clipboard operations), you should use the Select method:
```
Sheet.Range("A1:C4").Select();
```
Formatting a cell.
- No one is usually satisfied with the dumb Times New Roman; to construct an eye-candy spreadsheet, you'll need to change the visual styles of some cells. First comes the Font object:
```
// Bold text;
Sheet.Range("A1:C4").Font.Bold = true;
// Color index (from the index table) for the text;
Sheet.Range("A1:C4").Font.ColorIndex = 43;
// Font style is/isn't italic;
Sheet.Range("A1:C4").Font.Italic = false;
// Use 'Tahoma' font;
Sheet.Range("A1:C4").Font.Name = "Tahoma";
// Size (in points) of font;
Sheet.Range("A1:C4").Font.Size = 10;
// Font is/isn't struck through with a horizontal line;
Sheet.Range("A1:C4").Font.Strikethrough = false;
// Font is/isn't formatted as subscript;
Sheet.Range("A1:C4").Font.Subscript = false;
// Font is/isn't formatted as superscript;
Sheet.Range("A1:C4").Font.Superscript = false;
// Type of underline applied to the font, if any.
Sheet.Range("A1:C4").Font.Underline = XlUnderlineStyle;
```
  XlUnderlineStyle can be one of the following:
  
  Underline type name Numeric value Meaning
  
  xlUnderlineStyleNone -4142 No underline
  
  xlUnderlineStyleSingle 2 Single-line underline
  
  xlUnderlineStyleDouble -4119 Double layered underline
  
  If you don't want to render all the text with the same style, you should apply Font changes to the Characters(StartChar, Length) collection within the Range object:
```
// Bold text (for characters from 5^th to 9^th);
Sheet.Range("A1:C4").Characters(5, 5).Font.Bold = true;
// Font style is/isn't italic;
Sheet.Range("A1:C4").Characters(5, 5).Font.Italic = true;
// Use 'Verdana' font;
Sheet.Range("A1:C4").Characters(5, 5).Font.Name = "Verdana";

...  // etc.
```
  Take a note: Font and Characters objects are absolutely the same used with Word's Paragraph.
- One more thing to look at is the Interior object, which allows you to change the color and the pattern of the cell's background:
```
// Color index (from the index table) for the range/cell;
Sheet.Range("A1:C4").Interior.ColorIndex = 43;
Sheet.Range("A1:C4").Interior.Pattern = XlPattern;
```
  XlPattern can be one of the following:
  
  Pattern type name Numeric value
  
  xlPatternChecker 9
  
  xlPatternCrissCross 16
  
  xlPatternGrid 15
  
  xlPatternLightDown 13
  
  xlPatternLightUp 14
  
  xlPatternLightHorizontal 11
  
  xlPatternLightVertical 12
- The last point of interest is the Borders collection - the four borders of a range of cells. Very useful for formatting tables:
```
// Color index (from the index table) for the border;
ActiveWorksheet.Range("B2:D4").Borders.ColorIndex = 43;
ActiveWorksheet.Range("B2:D4").Borders.LineStyle = XlLineStyle;
ActiveWorksheet.Range("B2:D4").Borders.Weight = XlBorderWeight;
```
  XlLineStyle can be one of the following (style names are self-explanatory, I think):
  
  Line style name Numeric value
  
  xlContinuous 1
  
  xlDash -4115
  
  xlDot -4118
  
  xlDashDot 4
  
  xlDashDotDot 5
  
  xlSlantDashDot 13
  
  xlDouble -4119
  
  xlLineStyleNone -4142
  
  XlBorderWeight can be one of the following:
  
  Weight Numeric value
  
  xlHairline 1
  
  xlThin 2
  
  xlMedium -4138
  
  xlThick 4
  
  If you don't wish to change all the borders at a time, you can choose a single border:
```
ActiveWorksheet.Range("B2:D4").Borders(XlBordersIndex).LineStyle = 
                                                      XlLineStyle;

... // etc.
```
  XlBordersIndex can be one of the following:
  
  Weight Numeric value
  
  xlEdgeTop 8
  
  xlEdgeBottom 9
  
  xlEdgeLeft 7
  
  xlEdgeRight 10
  
  xlDiagonalDown 5
  
  xlDiagonalUp 6
  
  xlInsideHorizontal 12
  
  xlInsideVertical 11

Formatting a row/column.

You can work with entire rows and columns as if they were simple cells:

// Retrieve the row/column that contains
// the specified cell or range of cells:

// rangCol now contains the "B" column.
var rangCol = Sheet.Range("B2").EntireColumn;
// rangRow now contains the 2^nd row.
var rangRow = Sheet.Range("B2").EntireRow;

// or even:

// rangCols now contains the "B" and "C" columns.
var rangCols = Sheet.Range("B2:C4").EntireColumn;
// rangRows now contains the 2^nd,
// 3^rd and 4^th rows.
var rangRows = Sheet.Range("B2:C4").EntireRow;

// Now do what you wish:
rangCols.Font.Size = 10;

... // etc.

Building charts.

Working with charts begins with learning the ChartObjects collection. First, add a new chart object:

var chartObj = Sheet.ChartObjects.Add(chartLeftPixels, 
               chartTopPixels, chartWidthPixels, chartHeightPixels);

For example:

var rang = Sheet.Range("A1:C10");
var ch = Sheet.ChartObjects.Add(rang.Left + rang.Width, 
                                rang.Top, 350, 220);

Next, define a type of chart. Excel2000 defines ~50 types, so it's up to your artistic skills to choose the best. Search the Object Browser for the XlChartType enumeration. My favorites are:

Type name	Numeric value	Meaning
`xlBarClustered`	57	Simple horizontal bar graph
`xlBarStacked`	58	Stacked bar graph - shows the contribution of individual items into overall sum
`xlColumnClustered`	51	Histogram
`xlColumnStacked`	52	Stacked histogram
`xlPie`	5	Pie chart
`xl3DPie`	-4102	3D variant of a pie chart
`xlCylinderBarClustered`	95	Nice-looking variant of a bar graph
`xlCylinderBarStacked`	96	Nice-looking variant of a stacked bar graph

ch.Chart.ChartType = -4120;

At last, define a data source for your chart:
```
ch.Chart.SetSourceData(dataRange, where);
```
DataRange is the range of cells that contains the data for the chart. Where says where the actual chart data is (thus separating data rows and data clusters), and can be either xlColumns (numeric value: 2) or xlRows (numeric value: 1). For example:
```
var rang = Sheet.Range("A1:C10");
ch.Chart.SetSourceData(rang, 2);
```
Voila! You made it!

Working with the clipboard.
Controlling the clipboard is as easy as pressing Ctrl-C/Ctrl-V:
- If you wish to transfer text data to clipboard, use the Copy and Cut methods:
```
Sheet.Range("B2").Copy();
Sheet.Range("C2").Cut();
```
  To send a chart to the clipboard, use the CopyPicture method:
```
var ch1 = Sheet.ChartObjects.Add(ch.Left + 
              ch.Width, ch.Top, 400, 250);

...

ch1.CopyPicture();
```
- The same magic works with the PasteSpecial method:
```
Sheet.Range("C4:C5").Copy();
Sheet.Range("D4:D5").PasteSpecial();
```

Saving a workbook.

Time to put your workbook aside:

var Path = WScript.ScriptFullName;
Path = Path.substring(0, Path.lastIndexOf("\\"));

WorkBook.SaveAs(Path + "/charts.xls");

Exiting.
The easiest trick takes you out:
```
ExlApp.Quit();
```

Sample script: formatting a business report

This script creates an Excel spreadsheet, populates it, creates several charts, copies them (via clipboard) to a Word document, and, finally, saves both documents.

// Start a new instance of Microsoft Excel
var ExlApp = new ActiveXObject("Excel.Application");

// Silent-mode:
ExlApp.Visible = false;
ExlApp.DisplayAlerts = false;

var WorkBook = ExlApp.Workbooks.Add();
var Sheet = WorkBook.ActiveSheet;

Sheet.Range("A1").Value = "1.01.2006";
Sheet.Range("A2").Value = "2.01.2006";
Sheet.Range("A3").Value = "3.01.2006";
Sheet.Range("A4").Value = "4.01.2006";
Sheet.Range("A5").Value = "5.01.2006";
Sheet.Range("A6").Value = "6.01.2006";
Sheet.Range("A7").Value = "7.01.2006";
Sheet.Range("A8").Value = "8.01.2006";
Sheet.Range("A9").Value = "9.01.2006";
Sheet.Range("A10").Value = "10.01.2006";

Sheet.Range("B1").Value = 1;
Sheet.Range("B2").Value = 2;
Sheet.Range("B3").Value = 3;
Sheet.Range("B4").Value = 4;
Sheet.Range("B5").Value = 5;
Sheet.Range("B6").Value = 6;
Sheet.Range("B7").Value = 7;
Sheet.Range("B8").Value = 8;
Sheet.Range("B9").Value = 9;
Sheet.Range("B10").Value = 10;

Sheet.Range("C1").Value = 11;
Sheet.Range("C2").Value = 9.9;
Sheet.Range("C3").Value = 8.8;
Sheet.Range("C4").Value = 7.7;
Sheet.Range("C5").Value = 6.6;
Sheet.Range("C6").Value = 5.5;
Sheet.Range("C7").Value = 4.4;
Sheet.Range("C8").Value = 3.3;
Sheet.Range("C9").Value = 2.2;
Sheet.Range("C10").Value = 1.1;

var rang = Sheet.Range("A1:C10");

// First chart:
var ch = Sheet.ChartObjects.Add(rang.Left + 
         rang.Width, rang.Top, 350, 220);
ch.Chart.ChartType = -4120;
ch.Chart.SetSourceData(rang, 2);

Sheet.Range("A11").Select();

ch.CopyPicture();

// Start a new instance of Microsoft Word:
var WordApp = new ActiveXObject("Word.Application");

// Silent mode:
WordApp.Visible = false;

// Create a new Word document
WordApp.Documents.Add();

WordApp.Selection.ParagraphFormat.Alignment = 1;
WordApp.Selection.Paste();

WordApp.Selection.TypeParagraph();

// Second chart:
var ch1 = Sheet.ChartObjects.Add(ch.Left + ch.Width, 
                                 ch.Top, 400, 250);
ch1.Chart.ChartType = 95;
ch1.Chart.SetSourceData(rang, 2);

ch1.CopyPicture();

WordApp.Selection.Paste();
WordApp.Selection.TypeParagraph();

// Third chart:
var ch2 = Sheet.ChartObjects.Add(rang.Left + rang.Width, 
                                 ch.Top + ch.Height, 400, 250);
ch2.Chart.ChartType = -4100;
ch2.Chart.SetSourceData(rang, 2);

ch2.CopyPicture();

WordApp.Selection.Paste();

// Save Word document & exit:
var Path = WScript.ScriptFullName;
Path = Path.substring(0, Path.lastIndexOf("\\"));

WordApp.ActiveDocument.SaveAs(Path + "/charts.doc");
WordApp.Quit();

// Save Excel document & exit:
WorkBook.SaveAs(Path + "/charts.xls");

ExlApp.Quit();

Bonus track

For those patient enough to read till the end, here is the little bonus.

If you're scripting Word/Excel outside of the Office VBA environment, the RGB function is unavailable for you. Here are two functions, useful for performing color conversion operations. These are, actually, buried deep inside MSDN, written in VB... so here they are, the JScript versions:

  // red, green, blue - intensity of a color, value from 0 to 255.
  function RGB(red, green, blue)
  {
    return (red + (green * 256) + (blue * 65536));
  }

  // Retrieve component intensity from RGB value.
  // 
  // RGBval - value of type long to retrieve from;
  // ColorIndex - index of color to get:
  //    1 - red,
  //    2 - green,
  //    3 - blue.
  //
  function fromRGB(RGBval, ColorIndex)
  {
    // Check if Num, RGBval are valid.
    if(ColorIndex > 0 && ColorIndex < 4 && RGBval > -1 && RGBval < 16777216)
    {
      var module = 1;
      for(i = 0; i < ColorIndex - 1; i++)
        module = module * 256;

      return (Number(RGBval / module)) & 255;
    }
    else
      return 0;
  }

Now, you can use the Color property with a number of objects:

Sheet.Range("C4").Font.Color = RGB(64, 128, 192);
Sheet.Range("C5").Interior.Color = RGB(200, 150, 100);
Sheet.Range("C6").Borders.Color = RGB(100, 150, 200);

History

Date posted: March 2^nd, 2006.

Underline type name	Numeric value	Meaning
`xlUnderlineStyleNone`	-4142	No underline
`xlUnderlineStyleSingle`	2	Single-line underline
`xlUnderlineStyleDouble`	-4119	Double layered underline

Pattern type name	Numeric value
`xlPatternChecker`	9
`xlPatternCrissCross`	16
`xlPatternGrid`	15
`xlPatternLightDown`	13
`xlPatternLightUp`	14
`xlPatternLightHorizontal`	11
`xlPatternLightVertical`	12

Line style name	Numeric value
`xlContinuous`	1
`xlDash`	-4115
`xlDot`	-4118
`xlDashDot`	4
`xlDashDotDot`	5
`xlSlantDashDot`	13
`xlDouble`	-4119
`xlLineStyleNone`	-4142

Weight	Numeric value
`xlHairline`	1
`xlThin`	2
`xlMedium`	-4138
`xlThick`	4

Weight	Numeric value
`xlEdgeTop`	8
`xlEdgeBottom`	9
`xlEdgeLeft`	7
`xlEdgeRight`	10
`xlDiagonalDown`	5
`xlDiagonalUp`	6
`xlInsideHorizontal`	12
`xlInsideVertical`	11