質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.06%

PowerShell で XML の 要素 の 追加 をしたい

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 3,101

score 172

前提・実現したいこと

Excelの大量のセルの複雑な書式設定を素早く行うために
XML経由で書式設定を行いたいです。

スタイルを追加してから対象のセルに追加したスタイルIDを設定すれば
出来そうなことは分かったので
元のスタイルをコピーして書き換えて追加して
もともとデフォルトのスタイルが割り当てられていたセルには"StyleID"の項目がないので
"StyleID"が無いセルはその項目を追加したいのですが
AppendChildを使えばいいらしいところまでは分かったのですが使い方がよくわかりませんでした。

上記の
配列になっている項目の追加方法と
新しいプロパティ? の追加方法と

元の項目をコピーして流用? することが可能
ならばそちらも

教えて頂きたいです。
お願い致します。

サンプルソースコード

try
{
    $App = New-Object -ComObject "Excel.Application"
    $App.Visible = $true
    $BK = $App.Workbooks.Add()
    $Sh = $Bk.ActiveSheet

    $App.DisplayAlerts = $false
    $Sh.Range("A1:C4").Formula() = "=Row()*Column()"
    $Sh.Range("A2:C2").merge()
    $Sh.Range("A2:C2").Interior.Color = [int]"0xCCCCCC"

    $XML = $Sh.Range("A1:C4").Value(11) -as "XML"


        <#
            ここでXMLをいじって表示形式を変更したい

            そのために....
                $XML.Workbook.Styles に新しいスタイルを追加したい

                $XML.Workbook.Worksheet.Table.Row.Cellに"StyleID"の項目が無ければ追加したい
        #>

    $XML.PreserveWhiteSpace = $true
    $NewXmlText = $XML.OuterXml
    Write-Host $NewXmlText
    $Sh.Range("A1:C4").Value(11) = $NewXmlText
    # コンソールに"Enter"入力されるまで待機
    $null = Read-Host "Enter で終了"
}
catch [DivideByZeroException]
{
    Write-Host "エラー"
}
finally
{
    Write-Host "クリーンアップしています..."
    $BK.Close($false)
    $App.Quit()

    $Null = [System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($Sh)
    $Null = [System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($BK)
    $Null = [System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($App)
    [System.GC]::Collect()
}

試したこと

AppendChildを使えばいいらしいところまでは分かったのですが
使い方がよくわかりませんでした。

補足情報(FW/ツールのバージョンなど)

Win10

蛇足( - 追記 - )

セルの結合サイズが違う場所同士でのNumberFormatや値のコピペなど
自由なコピペをしたくてそのために試行錯誤しています。

XML 以外でも下記条件を全て満たせれば XMLの要素の追加 以外の回答でもありがたいです。

-- 最終必須条件 --
既に開いているブック間のコピペ
別のプロセス間のコピペ
同じブックを複数 読取専用で開いていても 可

ペースト時に、ペースト方法選択切替

  1. NumberFormat
  2. セルの塗りつぶし色とパターン
  3. 値(Value)の 書換 or 追記(貼り付け先の値の 先頭 or 末尾)
  4. FontSize
  5. FontColor

等 の組み合わせ

コピー内容を編集してからのペースト

ショートカットキー での コピペ
保存されていないブック( 一度も保存されていない, 最終保存から変更されている ) でのコピペ
異なる結合範囲間でのコピペ(結合されたセルは1つのセルとして扱う コピー範囲 よりも 貼付範囲の 方が大きければ コピー内容をループ)
複数Areaのコピペ
異なるArea.Count間 のコピペ
コピー 又は、ペースト 最中のExcel操作
上記の条件は可能な組み合わせならば複数同時に発生することあり

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

check解決した方法

0

たこはち様 の「へのかっぱ」日記をみてXMLには要素と属性という概念があることを知りました。

あとはそれぞれの操作方法を調べることで無事 理解できました。

$XMLの中身は質問文のプログラムで取得した内容とします。

属性の作成
$Attribute = $XML.CreateAttribute("名前")

要素の作成
$Element1 = $XML.CreateElement("名前")
$Element2 = $XML.CreateElement("名前")

属性の追加
$Element1.Attributes.Append($Attribute)

要素の追加
Element1.AppendChild($Element2)

要素のコピー
$Element3 = $XML.Workbook.Styles.Style[1].Clone()

属性のコピー
未検証、要素と同様にClone()が存在するところまで確認


あとはいろいろ試しながらやっていきたいと思います。

タイトル記載の問題は解決できたので
この問題はクローズと致します。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

時間を測定したわけではありませんが、
COM経由で弄るのであれば、XMLを編集するより、ExcelのCOM API経由で操作した方がより簡単で堅牢ではないでしょうか?

以下、今開いているブックに対してExcelのCOM API経由で新しいスタイルの作成、適用をするサンプルです。

[scriptblock]$releaseCom = {
    param([ref]$com)
    if ($null -eq $com.Value -or 
        -not [System.Runtime.InteropServices.Marshal]::IsComObject($com.Value)) {
        return
    }
    [System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($com.Value) > $null
    $com.Value = $null
}

try {
    [__ComObject]$appXl = [System.Runtime.InteropServices.Marshal]::GetActiveObject('Excel.Application')
    [__ComObject]$wb = $appXl.ActiveWorkbook

    # 適用するスタイルを作成
    [__ComObject]$applyStyle = $wb.Styles.Add('PSTest')
    $applyStyle.Interior.Color = 0xCCCCCC
    # 適用する書式のチェックを外す場合は $applyStyle.Include~ を $false にする

    [__ComObject]$ws = $wb.ActiveSheet
    [__ComObject]$rng = $ws.Range('A1')
    # 作成したスタイルを適用
    $rng.Style = $applyStyle

} finally {
    $releaseCom.Invoke([ref]$rng)
    $releaseCom.Invoke([ref]$ws)
    $releaseCom.Invoke([ref]$applyStyle)
    $releaseCom.Invoke([ref]$wb)
    $releaseCom.Invoke([ref]$appXl)
    [GC]::Collect()
    [GC]::WaitForPendingFinalizers()
    [GC]::Collect()
}

もっとも、ExcelのCOM APIを使うならメインの処理はVBAで作成した方がより早いはずですけれど…

Private Sub SampleApplyStyle()
    Dim appXl As Excel.Application
    Set appXl = Excel.Application

    Dim wb As Excel.Workbook
    Set wb = appXl.ActiveWorkbook

    Dim applyStyle As Excel.Style
    Set applyStyle = wb.Styles.Add("VBAtest")
    applyStyle.Interior.Color = &HCCCCCC

    Dim ws As Excel.Worksheet
    Set ws = wb.ActiveSheet
    Dim rng As Excel.Range
    Set rng = ws.Range("A2")
    rng.Style = applyStyle
End Sub

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/12/27 10:12

    回答有難うございます。
    少し試してみたいと思います

    セルの結合サイズが違う場所同士でのNumberFormatや値のコピペを
    したくてそのために試行錯誤しています。
    最初VBAで値のみ対応させたものを利用していたのですが
    貼り付けの速度が足りず待ちの間何もできなくなってしまい困ったので
    現在、非同期でVBSを呼び出して 並列で実行できるようにしています。
    ですが今度は貼り付け作業が重なってくるとExcelの応答性がおちてセルの選択すらラグがひどすぎて
    結局貼り付け待ちをしなければならないような状態になっています。

    XMLなら取得や設定にセルを回すのに比べて時間が掛からず XMLの編集中もExcelに負荷がかからないので
    いけるのではないかと思ったのですが....

    あとはExcelプロセスが違っているとVBAからだとわたしには厳しかったり
    Excel VBAからだとフォームが隠れてしまったり
    そのVBAを入れているブックを非表示にするとなぜかブックが入っていない空のウィンドウが出てしまい
    閉じるボタンを押すと同じプロセスのブックが巻き込まれて閉じてしまうので閉じれなかったり...
    VBAも触るので後からコピペ用のVBAブックを開いたときにEnableEventsがFalseになっていて
    OpenBookイベントにショートカットキーの割り当てを入れているのですがそれが走らなかったり....


    それらの問題が解決できるのであればもともとVBAでやろうとしていたのでVBAでもいいのですが
    いろいろ考えてもいい案が出ませんでした....

    他に手段はあるのでしょうか?

    キャンセル

  • 2018/12/27 18:23

    # 情報のコピペの速度について
    単純な書式込みのコピーをしたとき
    XML文字列での取得・設定と、`sourceRange.Copy(destRange)`では`Range.Copy`の方が倍近く早い結果となった事がありました。

    誰でも読める形式であるXMLの変換・解釈に時間がかかるため、内部処理で完結するCOM操作の方が早いのだと思われます。

    そのため、ExcelファイルそのものをOffice Open XML(OOXML)として編集するのでなければ、COM操作の方が高速だと考えています。

    ---

    > 貼り付け作業が重なってくるとExcelの応答性がおちて~
    のあたりから、手動操作用のExcelと自動処理用のExcelでインスタンスが分けられていないように感じます(インスタンスが別なら、端末負荷以外の影響は無いはず、という考えから)。

    まずは、自分の手動作業用のExcelを予め立ち上げておき(ダブルクリックでファイルを開いたとき、先に起動していたインスタンスで開かれるため)、
    その上で、新規のExcelのインスタンスを生成し、その中で処理用のマクロの入ったブックを開いて処理させてはどうでしょうか(マクロ自身は同じインスタンス内のものを使って処理する)。

    キャンセル

  • 2018/12/28 08:44 編集

    すみませんよくわかりませんでした。
    「sourceRange.Copy(destRange)」とはRange("A1:C20").Copy 等のことでいいでしょうか?

    「新規のExcelのインスタンスを生成し、その中で処理用のマクロの入ったブックを開いて処理させてはどうでしょうか(マクロ自身は同じインスタンス内のものを使って処理する)。」の「マクロの入ったブック」のマクロ と 「マクロ自身は同じインスタンス内」のマクロは別物でしょうか?
    新規インスタンスでということは別のプロセスでということでいいでしょうか?

    あとセルの結合範囲が貼り付け元と貼り付け先で違うのと
    値とNumberFormatのみコピーしたかったりするのですが
    上記の方法は可能なのでしょうか?

    -- 追記 --
    質問文の 蛇足( - 追記 - ) に最終的に達成したい条件を載せました。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.06%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る