OpenXMLでExcelを操作する３

前回にも書いたがWorksheet自体にはシート名は保持しておらず、Workbookがシート名とシートIDを保持している。実際に操作したいWorksheetにたどり着くためには、Workbookから対象シート名のIDを取得して、そのシートIDのWorksheetを取得する流れとなる。

Dim filepath As String = ファイルパス
Using doc As SpreadsheetDocument = SpreadsheetDocument.Open(filepath, True)
  Dim workbookPart As WorkbookPart = doc.WorkbookPart
  ' シートIDの取得
  Dim sheetId As String = (From oxe1 As OpenXmlElement In workbookPart.Workbook.ChildElements
    Where TypeOf (oxe1) Is Sheets
    From oxe2 As OpenXmlElement In oxe1.ChildElements
    Where DirectCast(oxe2, Sheet).Name = "シート１"
    Select DirectCast(oxe2, Sheet).Id).First()
  ' WorksheetPartの取得
  Dim worksheetPart As WorksheetPart = DirectCast(workbookPart.GetPartById(sheetId),
WorksheetPart)
End Using

もっと短くてわかりやすい書き方もあるのだろうけど、クイックウォッチでバカ正直に追っていったらこんな感じになってしまった。

シェープはまだまだ出てこない。やりたいことはテキストボックスを追加したいだけなのに。

広告

OpenXMLでExcelを操作する２

OpenXMLのツールで表示されるソースコードは、指定したファイルを0から構築するコードが生成される。既存のファイルを開いて修正するためのコードはツールで生成されるコードを少し修正する必要がある。そのためにはツールで生成されるコードが何を指しているのかを確認することにした。

適当にExcelファイルを作成した。今回の目標はテキストボックスの追加なので、シートの幾つかのセルに値を入れるだけでなく、シェープも入れておいた。

そしてこのファイルをOpenXMLのツールで表示した。

ちなみにExplorerで表示されるXMLファイルは、Excelファイルの拡張子を xlsx から zip に変換して解凍した時のXMLファイルを指している。つまりOpenXMLでExcelファイルを編集したい場合は、どのXMLファイルを操作すればよいのかを把握しなければいけない。

ExcelのCOMやVBAとは直感的に少し異なる場所に値が格納されているケースが幾つかある。例えばシート名は worksheet フォルダ内の各シートのXMLファイルには保持されておらず、workbook.xml の worksheets の worksheet 要素に格納される。

またテキストボックスも sheet1.xml には入っておらず、sheet1.xml に連携する drawing1.xml に情報は格納される。一つのシェープに対して TwoCellAnchor が一つ必要のようだ。ちなみに OneCellAnchor というものもある。テキストに合わせてサイズを自動調整するかしないかで切り替わるようだ。固定なのが TwoCellAnchor で、自動調整するのが OneCellAnchor。

よってシートにテキストボックスを追加したいのであれば、まずworkbook.xml から対象のシート名のシートを探して、そのシートの drawing に TwoCellAnchor を追加すればよいことが分かった。次回へ続く。

OpenXMLでExcelを操作する１

ひょんなことからClosedXMLを触ることになった。しかしClosedXMLではシェープの操作ができないことが分かったので、シェープ操作はOpenXMLのSDKを利用することとなった。そんなわけでOpenXMLの調査まとめを記することにした。日本語ブログは少なかったし、ニッチな機能ばかり取り上げるこのサイトのネタにはちょうど良い。

今回はセル操作、シート操作などはClosedXMLを利用するので、それらの機能（普通に考えればExcel操作の主要機能）に関しては一切調べる気はない。数年前にOpenXMLでExcel出力を全部裁こうとしたら正月休みが無くなったので絶対にやめたほうがよい。シートにDBのテーブル表示させるだけだった気がするが、とにかく大変だった。ExcelからDB繋げばできるのでは？と何度も思った。

OpenXMLのSDKは以下のサイトからダウンロードできる。
http://www.microsoft.com/en-us/download/details.aspx?id=30425
要望が無いためか利用者が少ないためか僕の検索が下手なためかわからないが、日本語のダウンロードページは見つからなかった。ここでSDKのダウンロードはできるのだが、SDKとは別にTOOLと名のついているものもあるのでこれも一緒にダウンロードする。

SDKと一緒にダウンロードしたツールは選択したExcelファイルを生成するためのコードを吐き出してくれたり、ファイルのエラー情報を表示してくれたりするもの。後になってわかったのだがテキストボックスを一つ追加するだけで数百行のコードが必要になる。このツールが無いとかなりつらい。

ツールを起動したら、Document Explorerの部分にExcelファイルをドロップする。File → OpenFile → ファイル選択でも可能。すると以下のような画面になる。

次にReflect Codeを押すと指定したExcelファイルを生成するためのC#のコードが右側に表示される。０からファイルを生成したい場合はDocument Explorerのツリーの最上部のファイルを選択して表示されるコードを丸写しすればOK。Document Explorerでブックの中身を細かくした単位で絞り込むこともできる。

テキストボックスの追加方法を調べるために、内容が等しいExcelファイルを2つ準備した後で片方のファイルにテキストボックスを追加して、それぞれのコードを表示して差分を取ってやれば、テキストボックスの追加するためのコードが取得できた。めでたしめでたし、というわけにはいかないのであった。２に続く。

ASP.NETで株価チャートを作る １１

今回はツールチップとMapAreaAttributesについて取り上げる。

ツールチップをプロパティとして有するクラスは幾つかあるのだが、ここでは系列のツールチップを設定する。特に難しいものではなくツールチップとして表示したい文字列を設定するだけである。データの数値を表示させたい場合はキーワードとフォーマットがあるのでそこだけ押さえておけば問題はないだろう。日付と各値を表示するように設定したのが以下のコードだ。X軸の値は「#VALX」、Y軸の値は「#VALY」～「#VAL3」を指す。出来高にはフォーマットとして数値で小数点以下切捨て「{N0}」を設定した。

' ローソク足チャートのツールチップ
Chart1.Series("Price").ToolTip = "日付：#VALX\n高値：#VALY\n安値：#VALY2\n始値：#VALY3\n終値：#VALY4"
' 出来高チャートのツールチップ
Chart1.Series("Volume").ToolTip = "日付：#VALX\n出来高：#VALY{N0}"


具体的な数値を表示させたいのならば上記のツールチップで十分だと思うのだが、ツールチップではなく特定のSPANタグに複数の系列の値を表示させたいというケースもあるだろう。そのような時はDataPointのMapAreaAttributesプロパティを設定する。onmouseoverとonmouseoutようのスクリプトを準備しておいて、それぞれのDataPointでどのような値で呼び出すのかを設定する。これにより複数系列の値を用いることができたり、ツールチップとは別に指定した箇所に表示させたりと自由度は高まる。DataPointを操作する場合はデータのバインド後に行うこと。MapAreaAttributesを応用すればドリルダウンなどの実装も可能だと思う。

' 各種チャートの設定とデータのバインドまで終了
Dim priceCount As Integer = Chart1.Series("Price").Points.Count
For i As Integer = 0 To priceCount - 1
  Dim dpPrice As DataPoint = Chart1.Series("Price").Points(i)
  Dim dpVolume As DataPoint = Chart1.Series("Volume").Points(i)
  Dim dispValue As String = String.Empty
  dispValue += "日付：" + Date.FromOADate(dpPrice.XValue).ToString("yyyy/MM/dd") + " "
  dispValue += "終値：" + dpPrice.YValues(3).ToString("N0") + " "
  dispValue += "出来高：" + dpVolume.YValues(0).ToString("N0") + " "
  dpPrice.MapAreaAttributes = "onmouseover=""displayData('" + dispValue + "')""; onmouseout=""displayData('')"";"
Next

' 画面側に準備しておくスクリプトなど
<asp:Label ID="LblDisplay" runat="server" Text="" />
<script type="text/javascript" >
<!--
function displayData(disptext) {
  document.getElementById("<%= LblDisplay.ClientID %>").innerText = disptext;
}
// -->
</script>


予想していたより長い連載となったがひとまず終了。不足など思い出したら追記の予定。

ASP.NETで株価チャートを作る １０

今回は週足や月足について。週足や月足は日足のデータテーブルから作成することができる。専用のテーブルを保持しておいたり、事前に集計してデータテーブルを作成しておく必要はない。ただし5分足のような粒度を細かくするケースは不可能。細かくする場合はデータを自身で準備する必要がある。

基本的な流れは、日足のチャートを作成して、週足や月足に加工し、要素数の補正、欠落ポイント対応の順となる。つまり今回のコードは要素数の補正直前に入れる点に注意すること。週足や月足の加工はDataManipulatorのGroupメソッドを用いる。リファレンスはあるのだが、肝心の集計方法の記述が全然ないためここで取り上げよう。

Groupメソッドの概要はリファレンスを参考してもらうとして、具体的な設定方法は以下のようになる。ローソク足（Price）と出来高（Volume）と終値の単純移動平均（SMA）を週足に加工する。最初の引数には集計方法を設定する。ローソク足はX軸が日付となっているので週の最終日（LAST）に、Y軸には4つの値が設定されており高値、安値、始値、終値の順なので、高値は週の最大値（MAX）、安値は最小値（MIN）、始値は最初の値（FIRST）、終値は最後の値（LAST）を設定する。出来高はX軸が日付なので週の最終日（LAST）、Y軸は出来高なので合計（SUM）を設定する。終値の単純移動平均（SMA）はX軸が日付なので週の最終日（LAST）、Y軸は終値の平均なので最後の値（LAST）を設定する。次の引数には集計単位の値を設定する。今回は週足なので1週ごとだ。もし2週足にしたいのならば2を設定すればよい。次の引数が集計単位となる。週足なのでもちろん週（Weeks）を選ぶ。月足にしたいのならば月（Months）を設定すればよい。最後の引数は系列名となる。

' 日足チャートを作成する（過去記事参照）
' 週足にする
Chart1.DataManipulator.Group("X:LAST,Y:MAX,Y2:MIN,Y3:FIRST,Y4:LAST", 1, IntervalType.Weeks, "Price")
Chart1.DataManipulator.Group("X:LAST,Y:SUM", 1, IntervalType.Weeks, "Volume")
Chart1.DataManipulator.Group("X:LAST,Y:LAST", 1, IntervalType.Weeks, "SMA")
' 要素数の補正（過去記事参照）
' 欠落ポイント対応（過去記事参照）


次回はツールチップを解説する。

ASP.NETで株価チャートを作る ９

今回は移動平均収束拡散法（MACD）について。リファレンスには「移動平均収束発散法 (MACD) 式は、価格の短期移動平均および長期移動平均を比較します。 MACD は、9 日の指数移動平均で売買の勢いを示すシグナルとして使用します。」とある。いつも用いているFinancialFormulaメソッドで生成されるのはこの文章の前半部分を指す。比較用のシグナルは別途用意する必要がある。MACDの数値からシグナル分を差し引いた（※）MACD2という手法も存在するらしいがサポートしていない。
※ MACDはシグナルとの交差するポイントが重要となるため、差分を取れば値が0の地点を見ればよいというのがMACD2らしい。

まずはMACDのメインの折れ線を作成する。毎度のようにFinancialFormulaメソッドを利用する。数値パラメータとして短期移動平均を算出するための期間と長期移動平均を算出するための期間を設定する。今回は短期の期間を12日、長期の期間を26日とした。そして参照元として作成済みのローソク足の4つ目の値（Price:Y4）である終値を設定して、出力結果の名称を設定する。またMACDはオシレーター系の追加指標となるためチャートエリアと凡例を新規追加して、追加した場所に表示する。

' MACD
Chart1.Height = Unit.Pixel(Chart1.Height.Value + 200)
Chart1.ChartAreas.Add("MACDArea")
Chart1.ChartAreas("MACDArea").AlignWithChartArea = "PriceArea"
Chart1.Legends.Add("MACDLegend")
Chart1.Legends("MACDLegend").IsDockedInsideChartArea = False
Chart1.Legends("MACDLegend").DockedToChartArea = "MACDArea"
Chart1.Legends("MACDLegend").Docking = Docking.Top
Chart1.Legends("MACDLegend").Alignment = Drawing.StringAlignment.Far
Chart1.DataManipulator.FinancialFormula(FinancialFormula.MovingAverageConvergenceDivergence, "12,26", "Price:Y4", "MACD")
Chart1.Series("MACD").ChartType = SeriesChartType.Line
Chart1.Series("MACD").ChartArea = "MACDArea"
Chart1.Series("MACD").Legend = "MACDLegend"
Chart1.Series("MACD").LegendText = "MACD"


続けて比較用のシグナルの折れ線を作成する。シグナルは上記で作成したMACDの単純移動平均で作成する。単純移動平均の詳細はその３を参照してほしい。移動平均の期間は9日とした。比較用の折れ線なのでMACDのチャートエリアに描画する。最後に毎度おなじみの要素数の補正対応と欠落ポイント対応を適応する。

' MACDのシグナル
Chart1.DataManipulator.FinancialFormula(FinancialFormula.MovingAverage, "9", "MACD:Y", "Signal")
Chart1.Series("Signal").ChartType = SeriesChartType.Line
Chart1.Series("Signal").ChartArea = "MACDArea"
Chart1.Series("Signal").Legend = "MACDLegend"
Chart1.Series("Signal").LegendText = "Signal"
' 要素数の補正　（その６など参照）
' 欠落ポイント対応　（その６など参照）


MSNの株価チャートにはシグナルのほかに発散という棒グラフも表示されていたがこれが何を示しているのかは調査できなかった。いくつかのサイトを見た限りではMACDとシグナルのみのチャートが多かったのでこれだけで事足りるのだと思う。

次回は指標を離れて週足や月足の作成方法を紹介する。

ASP.NETで株価チャートを作る ８

今回はボリンジャーバンド、エンベロープについて解説する。どちらのチャートもFinancialFormulaメソッドに数値パラメータが２つ、出力されるYの値が２つのケースだ。出力されるYの値が２つの場合は、最終的にどのようなチャートで表現したいのかによってFinancialFormulaメソッドの利用方法が若干変わってくることに注意が必要だ。

まずはボリンジャーバンドから作成してみよう。FinancialFormulaメソッドの数値パラメータとして集計期間と標準偏差の係数の二つが必要となる。この二つの数値をカンマ刻みで数値パラメータとして文字列で入力する。そして参照元として作成済みのローソク足の4つ目の値（Price:Y4）である終値を設定して、出力結果の名称を設定する。出力結果の名称の設定方法が最終的に描画したいチャートの形状により変わってくることになる。リファレンスには範囲グラフもしくは2本の折れ線がよいとある。ただしリファレンスの使用例はコードに誤りがありエラーとなってしまう。そんなわけでここでは折れ線にする場合と範囲グラフにする場合のコードを載せておこう。

折れ線の場合は出力結果に２つの系列名称を付ける必要がある。リファレンスには系列一つでYとY2になっているがエラーが発生する。集計期間は5日、標準偏差の係数は2、高値の出力をBB1、低値の出力をBB2としている。バグなのか仕様なのか不明だが、出力の系列を複数にする場合は系列名称の後ろに「:Y」を付けないとダメらしい。ボリンジャーバンドはトレンド系なのでローソク足チャートと同じチャートエリアに表示する。

' ボリンジャーバンド（折れ線）
Chart1.DataManipulator.FinancialFormula(FinancialFormula.BollingerBands, "5,2", "Price:Y4", "BB1:Y,BB2:Y")
Chart1.Series("BB1").ChartType = SeriesChartType.Line
Chart1.Series("BB1").ChartArea = "PriceArea"
Chart1.Series("BB1").Legend = "PriceLegend"
Chart1.Series("BB1").LegendText = "ボリンジャーバンド（高）"
Chart1.Series("BB2").ChartType = SeriesChartType.Line
Chart1.Series("BB2").ChartArea = "PriceArea"
Chart1.Series("BB2").Legend = "PriceLegend"
Chart1.Series("BB2").LegendText = "ボリンジャーバンド（低）"
' 要素数の補正　（その６など参照）
' 欠落ポイント対応　（その６など参照）


範囲グラフの場合は出力結果の系列は１つとなる。FinancialFormulaメソッドの引数は最後の出力結果の系列名称を「BB」のみとする。このように設定すると系列BBのYには高値、Y2には安値が格納される。範囲グラフを扱う場合はチャートを塗りつぶしてしまうことに注意する必要がある。透過色で色を設定しないと後ろが見えなくなってしまう。

' ボリンジャーバンド（範囲）
Chart1.DataManipulator.FinancialFormula(FinancialFormula.BollingerBands, "5,2", "Price:Y4", "BB")
Chart1.Series("BB").ChartType = SeriesChartType.Range
Chart1.Series("BB").ChartArea = "PriceArea"
Chart1.Series("BB").Legend = "PriceLegend"
Chart1.Series("BB").LegendText = "ボリンジャーバンド"
Chart1.Series("BB").Color = Drawing.Color.FromArgb(64, Drawing.Color.Red)
Chart1.Series("BB").BorderColor = Drawing.Color.Red
' 要素数の補正　（その６など参照）
' 欠落ポイント対応　（その６など参照）


次にエンベロープを作成してみる。こちらもリファレンスの使用例通りにコードするとエラーとなる。集計期間は5日、上下シフトの割合を10%としている。折れ線の場合しか載せないが、範囲グラフの気を付ける点は上記のボリンジャーバンドと同様である。

' エンベロープ（折れ線）
Chart1.DataManipulator.FinancialFormula(FinancialFormula.Envelopes, "5,10", "Price:Y4", "ENV1:Y,ENV2:Y")
Chart1.Series("ENV1").ChartType = SeriesChartType.Line
Chart1.Series("ENV1").ChartArea = "PriceArea"
Chart1.Series("ENV1").Legend = "PriceLegend"
Chart1.Series("ENV1").LegendText = "エンベロープ（高）"
Chart1.Series("ENV2").ChartType = SeriesChartType.Line
Chart1.Series("ENV2").ChartArea = "PriceArea"
Chart1.Series("ENV2").Legend = "PriceLegend"
Chart1.Series("ENV2").LegendText = "エンベロープ（低）"
' 要素数の補正　（その６など参照）
' 欠落ポイント対応　（その６など参照）


次回は移動平均収束拡散法（MACD）を取り上げる。