如何在策略中读写Excel文件

The sample code below shows how to read from Excel file and write to it while a strategy is running. It reades two sma lengths from the SampleBook.xlsx file in the OnStrategyStart method and writes the last bar’s close price to the Excel file in the OnBar method.

本示例以下代码显示了当策略运行时如何从Excel文件读取并存盘。示例从SampleBook.xlsx文件的OnStrategyStart中读取了2个SMA时间周期，并在OnBar中把最新的bar收盘价编写进Excel文件。

  1. using System;
  2. using System.Drawing;
  3. using OpenQuant.API;
  4. using OpenQuant.API.Indicators;
  5. using Interop.Excel;
  6. public class MyStrategy : Strategy
  7. {
  8.    SMA sma1;
  9.    SMA sma2;
  10.    Workbook workbook;
  11.    Worksheet worksheet;
  12.    public override void OnStrategyStart()
  13.    {
  14.       // open Excel file  打开Excel文件
  15.       Application excel = new Application();
  16.   workbook = excel.Workbooks.Open(@"C:\Users\s.b\Documents\SampleBook.xlsx", 0, false,
  17. 5, “”, “”, true, Interop.Excel.XlPlatform.xlWindows, “\t”, false, false, 0, true, null,
  18. null);
  19.   Sheets sheets = workbook.Worksheets;
  20.   worksheet = (Worksheet)sheets.get_Item(1);
  22.   // read A1 and A2 values 读取A1和A2值
  23.   double length1 = (double)worksheet.get_Range("A1", "A1").Value2;
  24.   double length2 = (double)worksheet.get_Range("A2", "A2").Value2;
  26.   //
  27.   sma1 = new SMA(Bars, (int)length1);
  28.   sma2 = new SMA(Bars, (int)length2);
  29.    }
  30.    public override void OnBar(Bar bar)
  31.    {
  32.       worksheet.get_Range(“B1”, “B1”).Value2 = bar.Close;
  33.    }
  34.    public override void OnStrategyStop()
  35.    {
  36.       // save and close the workbook  保存并关闭工作簿（Excel文件）
  37.       workbook.Save();
  38.       workbook.Close(true, null, null);
  39.    }
  40. }
To build the sample you should add a reference to the Interop.Excel.dll, using the following steps:

为了建立示例你应该增加一个Interop.Excel.dll文件的引用，使用以下方法：

  • locate the Interop.Excel.dll file at “[Installation Directory]\Framework\bin” folder

  • 打开[Installation Directory]\Framework\bin文件夹，找到Interop.Excel.dll文件

  • copy it to the “[Installation Directory]\bin” folder

  • 把它复制到 [Installation Directory]\bin文件夹

  • from the main menu go to Tools -> Options, in the opened Options dialog click “Projects and Solutions” node and select “Build” node. Click “Add” button, choose “.NET…” option and choose the Interop.Excel.dll file.

  • 从主菜单找到Tools -> Options，在打开的Options对话框中点击Projects and Solutions节点选择Build节点。单击Add按钮，选择“.NET…”选项再选择Interop.Excel.dll 文件。