im@sparqlを触ってみる その2 - PowerShellのデータ取得を関数化する

imihito.hatenablog.jp

上記の記事でPowerShellから情報を取得することには成功したが、そのままでは使いにくいため、関数に切り出してみた。

動作環境

Windows10 Pro 64bit上のWindows PowerShellのみで確認。

> $PSVersionTable

Name                           Value                                                  
----                           -----                                                  
PSVersion                      5.1.17134.165
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.17134.165
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1

コード(psm1)

毎回クエリを投げるのは負荷的に避けたかったため、簡単なキャッシュ機構を付けてみた。

ただ、関数だけでは状態を保持しにくいため、モジュール(psm1)として、モジュール内の変数にキャッシュを保持させる構成としている。

# 結果のキャッシュ
[Collections.Generic.Dictionary[string,psobject[]]]$jsonCache =
    New-Object -TypeName 'Collections.Generic.Dictionary[string,psobject[]]'

function Request-Imasparql {
    [CmdletBinding()]Param (
        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [string]$QueryString
    )
    Set-StrictMode -Version Latest
    $ErrorActionPreference = 'Stop'

    # 改行及びインデント削除
    [string]$trimQuery = 
        [regex]::Replace( $QueryString , ' *[\r\n]{1,2} *' , [string]::Empty )

    # キャッシュにあれば情報取得&リターン
    [psobject[]]$jsons = @()
    if ( $jsonCache.TryGetValue( $trimQuery , [ref]$jsons ) ) {
        Write-Information 'use cache'
        Write-Information ('query = ' + $trimQuery)
        return $jsons
    }
    
    
    Write-Information 'access'
    [Net.WebClient]$wc = 
        New-Object -TypeName Net.WebClient -Property @{
            BaseAddress = 'https://sparql.crssnky.xyz/spql/imas/query'
            Encoding    = [Text.UTF8Encoding]$false
        }

    # クエリの設定
    $wc.QueryString.Set( 'output', 'json' ) # 出力形式
    [string]$escapedQuery = [uri]::EscapeDataString( $trimQuery )
    $wc.QueryString.Set( 'query', $escapedQuery )
    Write-Information ('escaped query = ' + $escapedQuery)

    [string]$jsonTxt = $wc.DownloadString( [string]::Empty )

    [psobject]$data = ConvertFrom-Json -InputObject $jsonTxt
    $jsons = $data.results.bindings
    $jsonCache.Add( $trimQuery , $jsons )
    
    return $jsons
}

Export-ModuleMember -Function Request-Imasparql

サンプル

上記モジュールを使って前回の記事と同じ処理を書くと以下のようになる。

Import-Module -Name '上記コードを○○.psm1として保存して、保存したファイルのフルパス'

# https://sparql.crssnky.xyz/imas/ 内の「千早のセリフテキストを取得」のクエリ
[string]$query = @'
PREFIX schema: <http://schema.org/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX imas: <https://sparql.crssnky.xyz/imasrdf/URIs/imas-schema.ttl#>

SELECT *
WHERE {
  ?s rdf:type imas:ScriptText;
     imas:Source ?source;
     schema:text ?text.
  ?source schema:name ?name;
     filter(regex(str(?name),"千早"))
}order by ?text
'@

# 情報取得
[psobject[]]$resultJson = 
    Request-Imasparql -QueryString $query -InformationAction Continue

# 必要な情報をコンソール出力
$resultJson | 
    select -Property @(
        @{N="Name";E={$_.name.value}}, 
        @{N="Text";E={$_.text.value}}
    ) |
    Write-Output

im@sparqlを触ってみる その1 - PowerShellでデータを取得する

その2

はじめに

自分のプログラミング以外の趣味関係のファンサイトに、「im@sparql」というサイトがある。

こちらはWEB上にデータベースがあり、クエリ文字列を付加してGETすれば情報が取れるというものらしい。 (自分も良く分かっていないので詳細は以下なども参照)

京都肉上げ croMisa

自分自身、各種基本知識・理解が不足しているため、「とりあえず動かしてみる」を目標にPowerShellから情報を取得するコードを作ってみた。

取得する情報

今回はim@sparqlの「千早のセリフテキストを取得」のクエリをそのまま使用して、情報を取得する。

コード

# ベースのURL
[string]$baseUrl = 'https://sparql.crssnky.xyz/spql/imas/query?query='

# https://sparql.crssnky.xyz/imas/ 内の「千早のセリフテキストを取得」のクエリ
# 一行に納めるため、SplitしてからJoinしている
[string]$query = [string]::Join( '' , @'
PREFIX schema: <http://schema.org/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX imas: <https://sparql.crssnky.xyz/imasrdf/URIs/imas-schema.ttl#>

SELECT *
WHERE {
  ?s rdf:type imas:ScriptText;
     imas:Source ?source;
     schema:text ?text.
  ?source schema:name ?name;
     filter(regex(str(?name),"千早"))
}order by ?text
'@.Split("`r`n") )

# クエリ文字列をエスケープ
# また、そのままだとXMLで結果が返されるため '&output=json' を付与する
[uri]$openUri = [uri]($baseUrl + [uri]::EscapeDataString( $query ) + '&output=json')

# 情報取得
$result = Invoke-WebRequest -Method Get -Uri $openUri

if ( $result.StatusCode -ne 200 ) { throw }

# $result.Content はByte配列なので、文字列(JSON)に変換し
# JSON文字列をPSObjectへ変換
$json = 
    [Text.Encoding]::UTF8.GetString( $result.Content ) |
    ConvertFrom-Json

# 必要な情報を出力
$json.results.bindings |
    select -Property @(
        @{N="Name";E={$_.name.value}}, 
        @{N="Text";E={$_.text.value}}
    )

コンソール出力

Name Text                                                       
---- ----                                                       
如月千早 …!                                                         
如月千早 …………。                                                      
如月千早 …いえ、もうすぐ本番ですし、そこで体力を使い果たしてしまったら問題ですよ。                      
(中略)         
如月千早 静香…あなたならいつか、できる時がくるわ。アイドルを続けていれば、必ず。…必ずよ。 

ハマったところ

結果がXML

JavaScriptXMLHttpRequestやCOMのMSXML2.XMLHTTPを使用した場合、responseTextとしてJSON文字列が取得できた。

しかし、.NET Framework系のメソッドを使用した場合はXMLで結果が返却された。

詳しい人からGET時のURLにパラメータを追加することで結果を制御できるとの話があり、試すとうまくいった('&output=json'の付与)。

文字化け

XMLで結果が返却されたときの話だが、エンコードが違うのか文字化けをしてしまった。

エンコードをUTF8に設定することで文字化けせずにXMLを取得することができた。

Worksheets.Item()がObject型を返す理由の妄想

結論

シートモジュールのせい。

(18/07/07 追記

引数にインデックス・名前の配列を渡すと、Excel.Sheets型で返されるため、純粋に「返すオブジェクトの種類が固定ではない」せいとなります。

Dim s As Object
Set s = Worksheets.Item(Array("Sheet1", 2))

Debug.Print TypeName(s) '-> Sheets

以下は以前の記事の内容になります。

結論への経緯

ワークシートはWorksheet型ではない

Excelのワークシートは、厳密に言うとWorksheet型ではありません(ワークシート以外にもシートの種類はありますが、それらについても同様です)。

では何か?と言うとWorksheet型をベースに拡張された、各シート固有の型となります。 これらの型は一つとして同じ物はありません。

そのため、Worksheet型として扱うとWorksheetとしての共通機能は使用できますが、各シート固有の機能は使用できません。

補足:各シート固有の部分

  • シート上のActiveXコントロール(オブジェクト名のプロパティになります)
  • シートモジュール内で定義された広域変数(プロパティ相当のものになります)
  • シートモジュール内で定義された広域プロシージャ

シートの参照方法

ワークシートを参照する方法には以下の2種類があります。

  1. Sheet1などのシートオブジェクトを直接参照
  2. Worksheets.Item()による名前・番号を指定した参照

1の場合、Worksheetよりも具体的な型で取得できるため、各シート固有の機能を使用できます。 ただし、プロジェクト外部から使用したい場合は参照設定が必要になります。

2の場合、Worksheets.Item()の返り値がObject型であるため、正しい名前を指定すれば各シート固有の機能を使用できます。 プロジェクト外部からでも、名前などが正しければ使用できます。

仮に返り値がWorksheet型の場合、一度Object型や各シート固有の型にキャストしないと各シート固有の機能を使用できません。

Worksheets.Item()がWorksheet型だと問題になる例

実際にそれぞれのシートが別の機能を持っているならば「Sheet1などのシートオブジェクトを直接参照」で処理をすれば問題はありません。

しかし「同じ形式のシートが複数枚存在し、それぞれに対して同じ処理をしたい」といったことがあった場合、 返り値がWorksheet型だと何らかの型変換が必要になってしまいます。

'm番目からn番目のシート上にある、
'`CommandButton1`という名前のActiveXコントロールのキャプションを表示する
For i = m To n
    
    '`Worksheets.Item()`がObject型の場合
    Debug.Print Worksheets.Item(i).CommandButton1.Caption '直接参照でOK
    
    '`Worksheets.Item()`がWorksheet型の場合
    Dim tmp As Object
    Set tmp = Worksheets.Item(i) '要キャスト
    Debug.Print tmp.CommandButton1.Caption
    
Next i

このあたりを良い感じに処理できるようにするために、Sheets型という汎用的な型でWorksheetsを表しているのではないかと思います。

画像ファイルを名前の日付で分類(DateTime.TryParseExact の使い方メモ)

DateTime.TryParseExact メソッド (System)

が結構便利そうだったので使い方の確認がてらPowerShellでタイトルの処理を作ってみた。

対象のファイル群

Dropboxの自動アップロードによってスマホからPCに同期された画像ファイルに対して処理を行う。

画像ファイルの名前は自動で2018-03-23 12.34.56.jpgといった形式となるため、その名前を日付に変換して分類を行う。

コード

<#
.Synopsis
Dropboxでアップロードされた画像ファイルを日付で分類する
yyMMのフォルダを作成して、その中に移動する
#>

[string]$rootPath = 
    # カレントディレクトリ以下のファイルを対象にする場合
    $PWD.ProviderPath
    # PS1として保存して、その保存先フォルダ内を対象にする場合
    #[IO.Path]::GetDirectoryName($MyInvocation.MyCommand.Definition)

# Dropboxで自動アップロードされたファイルは以下のような名前になる
# e.g. 2018年3月23日12時34分56秒に撮影した画像の場合
# 2018-03-23 12.34.56.jpg
[string]$dateFormat = 'yyyy-MM-dd HH.mm.ss'

# DateTime.TryParseExact の引数指定が面倒だったため、スクリプトブロックに格納
[scriptblock]$tryParse = {
    param([string]$dateString, [ref]$outDate)
    return [datetime]::TryParseExact(
            $dateString, 
            $dateFormat,
            [Globalization.DateTimeFormatInfo]::CurrentInfo,
            [System.Globalization.DateTimeStyles]::None,
            $outDate
        )
}

# パースした結果受け取り用変数
[datetime]$parsedDate = [datetime]::MinValue

# $rootPath 内のファイルに対して操作
Get-ChildItem -LiteralPath $rootPath |
    # 名前を日付に変換できるものだけにフィルター
    ?{$tryParse.Invoke(
        # $_ には [System.IO.FileSystemInfo] が入るはず
        [IO.Path]::GetFileNameWithoutExtension($_.Name),
        [ref]$parsedDate)
    } |

    # 取得した日付を書式設定した値でグループ化(PowerShellのパイプラインの動作上、$parsedDateはちゃんと反映される)
    Group-Object -Property {$parsedDate.ToString('yyMM')} | 

    # 各グループに対して処理
    %{
        # 出力先のフォルダ作成
        # [IO.Directory]::CreateDirectory は冪等性のある処理っぽいのですでに存在していてもOK
        [string]$destDir = [IO.Path]::Combine($rootPath, $_.Name)
        [IO.Directory]::CreateDirectory($destDir) > $null

        # 各ファイルを移動
        $_.Group | 
        %{
            [string]$moveToPath = [IO.Path]::Combine($destDir,$_.Name)
            Write-Host ('{0} => {1}' -f $_.Name, [IO.Path]::GetFileName($destDir))
            $_.MoveTo($moveToPath)
        }
    }

Excelの選択しているセルの行・列に色を付ける(書式を設定する)

Twitterで面白そうなネタを見つけたのでやってみる。

はまさんのツイート: "アクティブセルの行全体に色を付ける方法です。横に長い表の場合は、便利です。 https://t.co/wJr1nNaxxX… "

アクティブセルのある行・列を目立たせる:エクセルマクロ・Excel VBAの使い方-イベントプロシージャ

リンク先の方法は非常にシンプルで良いのですが、直前に触った一つのセルしか対象になりません。

複数セル選択に対応できないかと、いじくり回していたら何とかなったので備忘録として残します。

コード

'Worksheet Module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    '条件付き書式で使う数式(常にTrue)
    Const FC_ID = "=ISTEXT(""ID1"")"
    
    '条件付き書式を適用する範囲
    Dim crossRng As Excel.Range
    Set crossRng = Excel.Union(Target.EntireRow, Target.EntireColumn)
    
    '条件付き書式を探す
    Dim fc As Excel.FormatCondition
    If tryGetFmtCond(Me, FC_ID, fc) Then
        
        '条件付き書式の範囲を変更(元に戻すの履歴は消えない)
        Call fc.ModifyAppliesToRange(crossRng)
        
    Else
        
        '見つからなかったので新規作成
        Set fc = crossRng.FormatConditions.Add(xlExpression, Formula1:=FC_ID)
        With fc.Interior
            .Pattern = XlPattern.xlPatternGray25
            .PatternColor = vbYellow
        End With 'fc.Interior
        
    End If
    
End Sub


'`ws`から`condFormula`の数式の条件付き書式を探す。
'見つかったらTrueおよび`oFmtCond`に見つかった条件付き書式を返す。
Private Function tryGetFmtCond( _
    ws As Excel.Worksheet, _
    condFormula As String, _
    ByRef oFmtCond As Excel.FormatCondition) As Boolean
    
    Dim fc As Excel.FormatCondition
    For Each fc In ws.Cells.FormatConditions
        Select Case True
            Case fc.Type <> XlFormatConditionType.xlExpression, _
                 fc.Formula1 <> condFormula
                'Next
                
            Case Else
                Set oFmtCond = fc
                Let tryGetFmtCond = True
                Exit Function
                
        End Select
    Next fc
    
End Function

動作イメージ

やっていること

  1. 常にTRUEの条件付き書式を作成する(常にその範囲に書式が設定される)
  2. 条件付き書式の適用範囲を、選択セルに応じて動的に変更する

余談

FormatConditionModifyAppliesToRangeなど一部のメソッドは、実行しても「元に戻す」の履歴は消えないようです。

問題点

条件付き書式を自己生成するため、止める手段がありません。

アドイン化&クラスモジュール化して、インスタンス・破棄で制御する形にすれば良いですが……

メモ:VBAからDiscordにメッセージを送信する

完全に見様見真似のメモ。

ほぼこちらの内容をVBAにしただけ。

アプリケーションからDiscordのチャンネルにメッセージを送る - Qiita

webhookのURLを取得

この辺から取得する。

設定 > テーマ > 詳細設定 >開発者モード のチェックが必要かも

f:id:imihito:20180127232758p:plain

当然ながら、自分がサーバー権限持ってないと取得できない。

ただ、簡単にサーバーは建てられるので、試すだけだけなら非常に楽。

メッセージ送信

MSXML2.XMLHTTPオブジェクトを作ってPOSTするだけ。

失敗した場合は、sendした後にresponseTextにエラーメッセージが入る。

また、openの第三引数varAsyncにTrueを指定しないと、sendで強制停止させられてエラーになる。

Sub SendDiscordMsgSample()
    
    Dim msg As String
    msg = "Hello Discord!!"
    
    Const WEBHOOK_URL = ' 上記で取得したURL文字列
    Dim xhr As Object 'As MSXML2.XMLHTTP60
    Set xhr = VBA.CreateObject("MSXML2.XMLHTTP")
    
    With xhr
        .open "POST", WEBHOOK_URL, True
        .setRequestHeader "Content-Type", "application/json"
        .send "{""content"":""" & msg & """}"
    End With 'xhr
    
End Sub

結果

f:id:imihito:20180127233503p:plain

参考

公式。ちゃんと理解していない。

Discord

メモ:PowerShellのAdd-MemberでCOMオブジェクトを拡張する

PowerShellではAdd-Memberコマンドレットを使って、任意のオブジェクトを拡張することが出来る。

この「任意のオブジェクト」にCOMオブジェクトも含まれていたため、動作確認も含めてメモ。

(Add-MemberそのものについてはAdd-Member を極める - 鷲ノ巣が詳しい)

やること

Scripting.Dictionary に TryGetValueという名前のメソッドを追加する。

VBAで書くと以下のようなイメージ

Private dic As Scripting.Dictionary
'...
Function TryGetValue(Key As Variant, ByRef Value As Variant) As Boolean
    Dim isExist As Boolean
    isExist = dic.Exists(Key)
    
    If isExist Then
        Value = dic.Item(Key) 'TODO:Value / Object
    End If
    
    Let TryGetValue = isExist
End Function

PowerShellのコード

# 拡張したCOMオブジェクトを取得する
       # Scripting.Dictionaryをインスタンスして次へ流す
$dic = New-Object -ComObject Scripting.Dictionary |
        # TryGetValue という名前のメソッドを追加
        Add-Member -Name TryGetValue -MemberType ScriptMethod -Value {
            # 引数の設定
            param ([object]$Key, [ref]$Value)

            [bool]$exist? = $this.Exists($Key)
            if ($exist?) {
                $Value.Value = $this.Item($Key)
            }
            Write-Output $exist?
        } -PassThru # 拡張されたオブジェクトを出力

# 準備
$dic.Add('dog', '犬')
$dic.Add('cat', '猫')

# 通常の参照
Write-Host 通常: $dic.Item('dog') # => 通常: 犬

# TryGetValueの動作確認
foreach ($k in @('dog', 'cat','mouse')) {
    $val = ''
    if ($dic.TryGetValue($k, [ref]$val)) {
        Write-Host $k は存在する 値: $val
    } else {
        Write-Host $k は存在しない
    }
}
<#
dog は存在する 値: 犬
cat は存在する 値: 猫
mouse は存在しない
#>