Click here to Skip to main content
15,868,340 members
Articles / Programming Languages / JScript .NET
Article

Standard of Excellence

Rate me:
Please Sign up or sign in to vote.
4.53/5 (10 votes)
2 Mar 20065 min read 61.7K   784   41   2
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:
      JavaScript
      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:

      JavaScript
      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:

      JavaScript
      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):
      JavaScript
      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:
      JavaScript
      var ExlApp = new ActiveXObject("Excel.Application");
      
      ExlApp.SheetsInNewWorkbook = 1; // Create only one sheet
      
      ExlApp.Visible = true;
    • Last step here - activate the very first sheet:
      JavaScript
      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:
      JavaScript
      WorkBook.Sheets.Add()
      // places new sheet before the active sheet

      rename a sheet:

      JavaScript
      WorkBook.ActiveSheet.Name = "SheetOne";

      activate another sheet:

      JavaScript
      WorkBook.Sheets("SheetTwo").Activate();
      var Sheet = WorkBook.ActiveSheet;

      or even delete a sheet:

      JavaScript
      WorkBook.Sheets("SheetTwo").Delete();
  • Populating the cells.
    • Conventional ("cell by cell") way of putting data into the cell is:
      JavaScript
      Sheet.Range("Cell-Coordinates").Value = value;

      For example:

      JavaScript
      Sheet.Range("A5").Value = "5.01.2006";
    • Together with the Range object, you can use the Cells collection with the row/column indexes:
      JavaScript
      Sheet.Cells(rowIndex, columnIndex)

      The previous example with Range can be rewritten as follows:

      JavaScript
      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:

    JavaScript
    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:

    JavaScript
    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:
      JavaScript
      // 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 nameNumeric valueMeaning
      xlUnderlineStyleNone-4142No underline
      xlUnderlineStyleSingle2Single-line underline
      xlUnderlineStyleDouble-4119Double 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:

      JavaScript
      // 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:
      JavaScript
      // 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 nameNumeric value
      xlPatternChecker9
      xlPatternCrissCross16
      xlPatternGrid15
      xlPatternLightDown13
      xlPatternLightUp14
      xlPatternLightHorizontal11
      xlPatternLightVertical12
    • The last point of interest is the Borders collection - the four borders of a range of cells. Very useful for formatting tables:

      JavaScript
      // 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 nameNumeric value
      xlContinuous1
      xlDash-4115
      xlDot-4118
      xlDashDot4
      xlDashDotDot5
      xlSlantDashDot13
      xlDouble-4119
      xlLineStyleNone-4142

      XlBorderWeight can be one of the following:

      WeightNumeric value
      xlHairline1
      xlThin2
      xlMedium-4138
      xlThick4

      If you don't wish to change all the borders at a time, you can choose a single border:

      JavaScript
      ActiveWorksheet.Range("B2:D4").Borders(XlBordersIndex).LineStyle = 
                                                            XlLineStyle;
      
      ... // etc.

      XlBordersIndex can be one of the following:

      WeightNumeric value
      xlEdgeTop8
      xlEdgeBottom9
      xlEdgeLeft7
      xlEdgeRight10
      xlDiagonalDown5
      xlDiagonalUp6
      xlInsideHorizontal12
      xlInsideVertical11
  • Formatting a row/column.

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

    JavaScript
    // 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:
      JavaScript
      var chartObj = Sheet.ChartObjects.Add(chartLeftPixels, 
                     chartTopPixels, chartWidthPixels, chartHeightPixels);

      For example:

      JavaScript
      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 nameNumeric valueMeaning
      xlBarClustered57Simple horizontal bar graph
      xlBarStacked58Stacked bar graph - shows the contribution of individual items into overall sum
      xlColumnClustered51Histogram
      xlColumnStacked52Stacked histogram
      xlPie5Pie chart
      xl3DPie-41023D variant of a pie chart
      xlCylinderBarClustered95Nice-looking variant of a bar graph
      xlCylinderBarStacked96Nice-looking variant of a stacked bar graph
      JavaScript
      ch.Chart.ChartType = -4120;
    • At last, define a data source for your chart:
      JavaScript
      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:

      JavaScript
      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:
      JavaScript
      Sheet.Range("B2").Copy();
      Sheet.Range("C2").Cut();

      To send a chart to the clipboard, use the CopyPicture method:

      JavaScript
      var ch1 = Sheet.ChartObjects.Add(ch.Left + 
                    ch.Width, ch.Top, 400, 250);
      
      ...
      
      ch1.CopyPicture();
    • The same magic works with the PasteSpecial method:
      JavaScript
      Sheet.Range("C4:C5").Copy();
      Sheet.Range("D4:D5").PasteSpecial();
  • Saving a workbook.

    Time to put your workbook aside:

    JavaScript
    var Path = WScript.ScriptFullName;
    Path = Path.substring(0, Path.lastIndexOf("\\"));
    
    WorkBook.SaveAs(Path + "/charts.xls");
  • Exiting.

    The easiest trick takes you out:

    JavaScript
    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.

JavaScript
// 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:

JavaScript
// 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:

JavaScript
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


Written By
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 Pin
Kulasekaran, Nivetha12-Oct-12 3:14
Kulasekaran, Nivetha12-Oct-12 3:14 
Questionhow to avoid multiple Excel processes Pin
i_a_z4-Sep-06 3:19
i_a_z4-Sep-06 3:19 

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

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