65.9K
CodeProject 正在变化。 阅读更多。
Home

卓越标准

Dmitry Khudorozhkov
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.53/5 (9投票s)

2006年3月2日

5分钟阅读

viewsIcon

63540

downloadIcon

786

了解如何将 MS Excel 功能集成到您的 JScript/WSH 脚本解决方案中。

引言

本文（作为我关于 MS Word 自动化的文章的补充）涵盖了以下主题：

  • 创建和填充 Excel 文档；
  • 在工作簿中导航并处理工作簿元素；
  • 从现有 Excel 数据创建图表（另请参阅Keith Thoresz 的文章，关于插入图片）；
  • 通过剪贴板与其他 Office 应用程序通信。

本文的主要目的是解释基于 JScript 的 Excel 自动化的基本方法。它绝不是该主题的全面概述，而仅仅是您探索之旅的一个起点。我希望它能帮助所有花费数天时间寻找非 Visual Basic 脚本示例的人。

本文假定您熟悉 JScript。

通用操作指南

  • 启动。
    • 让我们从一个新的 Excel 应用程序实例开始。
      var ExlApp = new ActiveXObject("Excel.Application");

      此时，您会有一个空的 Excel 在后台运行（不可见）。如果您想查看整个过程，请执行：

      ExlApp.Visible = true;

      请注意，尽管应用程序是不可见的，但您会遇到它生成的所有消息（例如“是否要覆盖文件？”、“您真的要删除工作表吗？”等）。要抑制所有警告，从而使 Excel 完全静默，请执行以下操作：

      ExlApp.DisplayAlerts = false;

      在这种情况下，所有操作都按照默认设置执行。

    • 下一步是创建一个新的工作簿（换句话说，一个新文件）。
      var WorkBook = ExlApp.Workbooks.Add();

      这将创建一个包含三个工作表的工作簿。

    • 您可以通过 `SheetsInNewWorkbook` 属性控制新工作簿创建时包含的工作表数量。
      var ExlApp = new ActiveXObject("Excel.Application");

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

ExlApp.Visible = true;
    • 此处的最后一步是激活第一个工作表。
      var Sheet = WorkBook.ActiveSheet;
  • 在工作表之间导航。
    • 正如您可能已经猜到的，最后一个表达式通过使用 `ActiveSheet` 对象将您带到了活动工作表。最终，您可能希望创建一个新工作表。
      WorkBook.Sheets.Add()
// places new sheet before the active sheet

      重命名工作表。

      WorkBook.ActiveSheet.Name = "SheetOne";

      激活另一个工作表。

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

      甚至删除一个工作表。

      WorkBook.Sheets("SheetTwo").Delete();
  • 填充单元格。
    • 向单元格中填入数据的传统（“逐个单元格”）方式是：
      Sheet.Range("Cell-Coordinates").Value = value;

      例如

      Sheet.Range("A5").Value = "5.01.2006";
    • 与 `Range` 对象一起，您可以使用带有行/列索引的 `Cells` 集合。
      Sheet.Cells(rowIndex, columnIndex)

      上面的 `Range` 示例可以重写如下：

      Sheet.Cells(5, 1).Value = "5.01.2006";
  • 在单元格之间导航。

    通常，您不需要手动移动单元格选择光标来为其设置值。尽管如此，您仍然可以通过手动将一个单元格设为活动单元格。

    Sheet.Range("A5").Activate();

    如果您需要选择一个单元格范围（例如，为了执行某些剪贴板操作），您应该使用 `Select` 方法。

    Sheet.Range("A1:C4").Select();
  • 格式化单元格。
    • 没有人会满足于普通的 *Times New Roman*；要创建一个赏心悦目的电子表格，您需要更改某些单元格的视觉样式。首先是 `Font` 对象。
      // 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` 可以是以下之一：

      下划线类型名称 数值 含义
      xlUnderlineStyleNone -4142 无下划线
      xlUnderlineStyleSingle 2 单线加下划线
      xlUnderlineStyleDouble -4119 双层下划线

      如果您不想用相同的样式渲染所有文本，您应该将 `Font` 更改应用于 `Range` 对象内的 `Characters(StartChar, Length)` 集合。

      // Bold text (for characters from 5th to 9th);
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.

      请注意：`Font` 和 `Characters` 对象与 Word 中用于 `Paragraph` 的对象完全相同。

    • 还有一点需要关注的是 `Interior` 对象，它允许您更改单元格背景的颜色和图案。
      // 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` 可以是以下之一：

      图案类型名称 数值
      xlPatternChecker 9
      xlPatternCrissCross 16
      xlPatternGrid 15
      xlPatternLightDown 13
      xlPatternLightUp 14
      xlPatternLightHorizontal 11
      xlPatternLightVertical 12
    • 最后感兴趣的点是 `Borders` 集合——单元格范围的四条边框。非常适合格式化表格。

      // 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` 可以是以下之一（我认为样式名称不言自明）：

      线型名称 数值
      xlContinuous 1
      xlDash -4115
      xlDot -4118
      xlDashDot 4
      xlDashDotDot 5
      xlSlantDashDot 13
      xlDouble -4119
      xlLineStyleNone -4142

      `XlBorderWeight` 可以是以下之一：

      权重 数值
      xlHairline 1
      xlThin 2
      xlMedium -4138
      xlThick 4

      如果您不想一次性更改所有边框，您可以选择单个边框。

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

... // etc.

      `XlBordersIndex` 可以是以下之一：

      权重 数值
      xlEdgeTop 8
      xlEdgeBottom 9
      xlEdgeLeft 7
      xlEdgeRight 10
      xlDiagonalDown 5
      xlDiagonalUp 6
      xlInsideHorizontal 12
      xlInsideVertical 11
  • 格式化行/列。

    您可以像处理简单单元格一样处理整个行和列。

    // 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 2nd 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 2nd,
// 3rd and 4th rows.
var rangRows = Sheet.Range("B2:C4").EntireRow;

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

... // etc.
  • 构建图表。

    • 处理图表始于学习 `ChartObjects` 集合。首先，添加一个新的图表对象。
      var chartObj = Sheet.ChartObjects.Add(chartLeftPixels, 
               chartTopPixels, chartWidthPixels, chartHeightPixels);

      例如

      var rang = Sheet.Range("A1:C10");
var ch = Sheet.ChartObjects.Add(rang.Left + rang.Width, 
                                rang.Top, 350, 220);
    • 接下来，定义图表类型。Excel 2000 定义了大约 50 种类型，所以选择最好的取决于您的艺术才能。在对象浏览器中搜索 `XlChartType` 枚举。我最喜欢的有：
      类型名称 数值 含义
      xlBarClustered 57 简单的水平条形图
      xlBarStacked 58 堆积条形图 - 显示个体项目对总和的贡献
      xlColumnClustered 51 直方图
      xlColumnStacked 52 堆积直方图
      xlPie 5 饼图
      xl3DPie -4102 饼图的 3D 变体
      xlCylinderBarClustered 95 条形图的精美变体
      xlCylinderBarStacked 96 堆积条形图的精美变体
      ch.Chart.ChartType = -4120;
    • 最后，定义图表的数据源。
      ch.Chart.SetSourceData(dataRange, where);

      `DataRange` 是包含图表数据的单元格范围。`Where` 指示实际图表数据的位置（从而区分数据行和数据簇），可以是 `xlColumns`（数值：2）或 `xlRows`（数值：1）。例如：

      var rang = Sheet.Range("A1:C10");
ch.Chart.SetSourceData(rang, 2);

      搞定！您做到了！

  • 处理剪贴板。

    控制剪贴板就像按 *Ctrl-C* / *Ctrl-V* 一样简单。

    • 如果您希望将*文本*数据传输到剪贴板，请使用 `Copy` 和 `Cut` 方法。
      Sheet.Range("B2").Copy();
Sheet.Range("C2").Cut();

      要将图表发送到剪贴板，请使用 `CopyPicture` 方法。

      var ch1 = Sheet.ChartObjects.Add(ch.Left + 
              ch.Width, ch.Top, 400, 250);

...

ch1.CopyPicture();
    • 相同的魔法也适用于 `PasteSpecial` 方法。
      Sheet.Range("C4:C5").Copy();
Sheet.Range("D4:D5").PasteSpecial();
  • 保存工作簿。

    是时候将您的工作簿放在一边了。

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

WorkBook.SaveAs(Path + "/charts.xls");
  • 退出。

    最简单的技巧让您退出。

    ExlApp.Quit();

示例脚本：格式化业务报告

此脚本创建一个 Excel 电子表格，填充它，创建几个图表，通过剪贴板将它们复制到 Word 文档，最后保存这两个文档。

// 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();

奖励环节

对于耐心读到最后的人，这里有一个小奖励。

如果您正在 Office VBA 环境之外编写 Word/Excel 脚本，则 `RGB` 函数对您不可用。这里有两个函数，可用于执行颜色转换操作。它们实际上深埋在 MSDN 中，用 VB 编写……所以，它们是 JScript 版本。

  // 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;
  }

现在，您可以使用 `Color` 属性处理多个对象。

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);

历史

  • 发布日期：2006 年 3 月 2 日。
© . All rights reserved.