Click here to Skip to main content
Click here to Skip to main content

Standard of Excellence

, 2 Mar 2006
Rate this:
Please Sign up or sign in to vote.
Learn how to integrate MS Excel functionality with your JScript/WSH scripting solutions.

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 <A href="http://msdn2.microsoft.com/en-us/library/microsoft.office.interop.excel.font.colorindex.aspx">index table</A>) 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<SUP>th</SUP> to 9<SUP>th</SUP>);
      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 <A href="http://msdn2.microsoft.com/en-us/library/microsoft.office.interop.excel.interior.colorindex.aspx">index table</A>) 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 <A href="http://msdn2.microsoft.com/en-us/library/microsoft.office.interop.excel.border.colorindex.aspx">index table</A>) 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<SUP>nd</SUP> 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<SUP>nd</SUP>,
    // 3<SUP>rd</SUP> and 4<SUP>th</SUP> 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 2nd, 2006.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Dmitry Khudorozhkov
Software Developer Freelance software engineer
Russian Federation Russian Federation
Dmitry Khudorozhkov began programming (and gaming) with his ZX Spectrum in 1989. Having seen and used all IBM PCs from early XT to the latest x64 machines, now Dmitry is a freelance programmer, living in Moscow, Russia. He is a graduate of the Moscow State Institute of Electronics and Mathematics (Applied Mathematics).
 
He is proficient in:
 
- C/C++ - more that 9 years of experience. Pure Win32 API/MFC desktop programming, networking (BSD/Win sockets), databases (primarily SQLite), OpenGL;
 
- JavaScript - more that 6 years of experience. Client-side components, AJAX, jQuery installation and customization;
 
- Firefox extensions (immediatelly ready for addons.mozilla.org reviewing) and Greasemonkey scripts. As an example of extensions Dmitry made you can search for FoxyPrices or WhatBird Winged Toolbar;
 
- XML and it's applications (last 2 years): XSLT (+ XPath), XSD, SVG, VML;
 
- ASP.NET/C# (webservices mostly);
 
Also familiar with (= entry level):
 
- PHP;
 
- HTML/CSS slicing.
 
Trying to learn:
 
- Ruby/Ruby-on-Rails;
 
- Czech language.
 
If you wish to express your opinion, ask a question or report a bug, feel free to e-mail:dmitrykhudorozhkov@yahoo.com. Job offers are warmly welcome.
 
If you wish to donate - and, by doing so, support further development - you can send Dmitry a bonus through the Rentacoder.com service (registration is free, Paypal is supported). Russian users can donate to the Yandex.Money account 41001132298694.
 
-

Comments and Discussions

 
QuestionNot working PinmemberKulasekaran, Nivetha12-Oct-12 4:14 
Questionhow to avoid multiple Excel processes Pinmemberi_a_z4-Sep-06 4:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141220.1 | Last Updated 2 Mar 2006
Article Copyright 2006 by Dmitry Khudorozhkov
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid