[勉強会資料] Excel + SharePoint + Power Platform によるローン返済シミュレーションアプリ開発

LogicFlow-ja online #3 Excel + S h arePoint + P o wer P l atform に よる ローン返済シミュレーション アプリ開発 日時 : 2020/08/29 平野 愛 | Ai HIRANO

自己紹介 平野 愛 (旧姓: 山崎) 前々職のグローバルナレッジ ネットワーク(現トレノケート)株式会社にて マイクロソフト認定トレーナーとしてコンテンツ開発や研修実施を主に行う • • • • Windows NTおよびActive Directory ドメインなどのシステム管理 .NET Framework を中心としたアプリケーション開発 Skype for Business Server の前身製品のシステム管理 SharePoint, Outlook, OneNote, InfoPath などの情報共有基 盤など その後、株式会社イルミネート・ジャパン社にて SharePoint の教育ビジネスの立ち上げに携わる。 2008年4月に独立。 2004年に日本で初めてのSharePoint分野での Microsoft MVP を受賞し、以降現在まで連続受賞 主な過去の登壇 • • • • • • De:code 2019 ブレイクアウト セッション スピーカ Microsoft MSTEP 講師 Microsoft TechEd 講師 Microsoft 主催の SharePoint の無償セミナー Japan SharePoint User Conference 講師 Microsoft Tech Summit Japan 2017 基調講演 講師 [Blog] http://shanqiai.weblogs.jp/ @ai_yamasaki オフィスアイ株式会社 2008年4月設立。Microsoft SharePoint および Office 365 に対応できる SE や開発者などの 人材育成と、設計、構築、運用、利活用に関しての関するコ ンサルティング サービスを提供。 特に教育に関しては、日本の現場に即したオリジナル コンテンツを独自開発し、 少人数制のオープンコース(複数組織から参加できる集合研修)の実施を定 期的に実施しているほか、1社向けのカスタマイズ研修なども提供。 [Official Site] http://www.office-i-corp.jp/ 書籍執筆 (共著を含む) その他の執筆活動 • • • • • • • • マイクロソフト社の SharePoint 関連のホワイト ペー パー・評価ガイド • • ひと目でわかる Office 365 導入・運用管理編 ひと目でわかる SharePoint Server 2016 ひと目でわかる SharePoint Server 2013 ひと目でわかる SharePoint Server 2010 VSTO と SharePoint Server 2007 による開発技術 ひと目でわかる SharePoint Server 2007 ひと目でわかる Microsoft Windows Server 2003 ネットワーク 設定・管理術 実践 Active Directory 逆引きリファレンス 2 Windows Server 2003 完全技術解説

• http://www.office-i-corp.jp/
• http://shanqiai.weblogs.jp/

今回のアプリの主役は E xcel ! Workbook ©2020 Ai HIRANO 3

今回のアプリの主役は E xcel ! 多くの現状、、、 Workbook レイアウトの簡易さから 印刷時の見た目など重視した利用が多い データの再利用性を 重視 ©2020 Ai HIRANO データの 再利用性 印刷したときの 見た目 4

SharePoint O nline と Excel ド キ ュ メント ラ イ ブ ラリで E x ce l フ ァ イ ルを共有、管理 フ ァ イ ルビューアー W e b パ ー ツ 埋 め 込 み We bパーツ ©2020 Ai HIRANO 5

Demo S HAREPOINT 標準機能と EXC EL ©2020 Ai HIRANO 6

SharePoint と E xcel の関係 Office SharePoint Server 2007 Excel Services レポーティング機能がメイン SharePoint Server 2010～ 技術的な変遷を 見ていきましょう! Excel Services Office Web Apps (Excel Online) 編集が可能になった SharePoint Online Excel Online Microsoft Graph (Excel REST API) ©2020 Ai HIRANO 7

[参考] E xcel Services って どんなのだったの? ◼ Excel で作成したワークブックを ◦ Web ブラウザ上で表示。フィルタなどの対話操作ができる ◦ ワークブック内の計算処理はサーバー側で処理される ◦ Excel Web Service の使用により外部アプリケーションから値を設定または取得したり、 ワークブック内の計算ロジックを利用したりできる ◼ モバイル端末サポート ◦ iOS, Android でも利用可能 「SharePoint Server Enterprise サイト コレクション機能」 のアクティブ化が必要 ©2020 Ai HIRANO 8

◼ Excel Services を構成する 3 つのコンポーネント – Excel Calculation Services (ECS) • Excel Services のエンジン – Excel Web Access (EWA) Web パーツ • ECS の情報を HTML にレンダリング – Excel Web Service API • Excel Services に外部からアクセスするための API SQL Server (コンテンツ データベース) Excel Services アプリケーション サーバー フロントエンド Web サーバー Excel Calculation Services Excel Web Access UDFs Excel Web Service Web ブラウザ Web フォルダ ファイル共有 MOSS 2007 カスタム アプリケーション 9

◼ 利点 – 一切コーディングすることなく、Excel シート内にある ビジネスロジック(計算式)に対して動的に値を渡し結果を 表示できるようになる ◼ 実装方法 – パラメータとして値を設定したいセルに名前を定義し、 Excel Services 発行時にパラメータを追加した状態で 発行する CRIE Illuminate .INC 10

◼ Excel Web Service API – 既定で 用意されている Web サービス – カスタム アプリケーション内から Excel Services に公開されている Excel データにアクセスで きる ◼ Excel Web Service API を利用できる 主なアプリケーション – .NET Framework ベースの Windows アプリケーション – ASP.NET Web アプリケーション • SharePoint Web パーツを含む – Office クライアント アプリケーション (VSTO の利用など) – JavaScript コード ◼ Excel Web Service API を利用するには – Web 参照を追加 • http://<サーバ名>/_vti_bin/ExcelService.asmx • http://<サーバ名>/<サイト>/_vti_bin/ExcelService.asmx

• http://&lt;

//コーディング例
ExcelService xlSrv =new ExcelService();
Status[] status;
string sessionId;
xlSrv.Credentials=System.Net.CredentialCache.DefaultCreadentials;
try{
sessionId=xlSrv.Openworkbook(@”http://moss2007/DocLib/Book1.xlsx”,Empty
.String,Empty.String,out status);
}

◼ 主なメソッド
–
–
–
–
–
–
–
–

OpenWorkbook: 新しいセッション内で Excel ワークブックを読み込む
CloseWorkbook : セッションを閉じる
CalculateA1,Calculate,CalculateWorkbook : 計算を行う
GetRangeA1,GetRange : 範囲の値を取得
GetCellA1,GetCell : セルの値を取得
SetRangeA1,SetRange : 範囲に値を設定
SharPoint
SetCellA1,SetCell : セルに値を設定
Online では
GetWorkbook : ワークブックを取得。戻り値はバイト配列
廃止!

◼ 詳細

– http://msdn2.microsoft.com/ja-jp/library/ms572330.aspx
12

• http://msdn2.microsoft.com/ja-jp/library/ms572330.aspx

Po wer Automate と E xcel E x c e l O n l i n e ( B u si n e s ) コ ネ ク ターを利用することでワークブックの参照や更新などが可能 ワークブックを更新せず、ロジックだけ 再利用するというシナリオの実現は難しい ©2020 Ai HIRANO 13

O f fice Scripts v. s. Excel REST API (M SGraph) Office Scripts Excel REST API ファイルの参照 ファイルの更新 ファイルの更新 関数およびデータのみの再利用 ©2020 Ai HIRANO 14

E xcel – M icrosoft Grap h https://docs.microsoft.com/en-us/graph/api/resources/excel?view=graph-rest-1.0 15

• https://docs.microsoft.com/en-us/graph/api/resources/excel?view=graph-rest-1.0

©2020 Ai HIRANO 16

取得しておくべき情報 以 下 の 情報を M i c ro s o ft G r a p h エ ク ス プ ロ ー ラ ー で 取 得 し て お く と よ い • サイト ID • リ ス ト ID • ア イ テ ム ID ( フ ァ イルのアイテム ID ) • フ ァ イ ルの Dri v eIt e m I D • リ ス ト の D r i v eID ©2020 Ai HIRANO 17

サイト IDの取得 G E T h t t ps ://g raph . mic ro so f t .c o m/v1. 0/si t e s/ o f fi c e il e arn i n g. sh are po i n t. c om: /si t es /T rai n in g - P o w e rAu t omat e -Adv an c ed ©2020 Ai HIRANO 18

©2020 Ai HIRANO 19

リストIDの取得 GE T h t t ps ://g raph . mic ro so f t .c o m/v1. 0/si t e s/ o f fi c e il e arn i n g. sh are po i n t. c om, e aa1b7f 4 -c 7 d1- 4 0 c a-ab 07-465b 15bb8bf a, 71d5 4204 -f 303-4 aac -8745-7 76e 48a3a d80/ l is t s ©2020 Ai HIRANO 20

©2020 Ai HIRANO 21

アイテムIDの取得 G E T h t t ps ://g raph . mic ro so f t .c o m/v1. 0/si t e s/ o f fi c e il e arn i n g. sh are po i n t. c om, e aa1b7f 4 -c 7 d1- 4 0 c a-ab 07-465b 15bb8bf a, 71d5 4204 -f 303-4 aac -8745-7 76e 48a3a d80/ l is t s/ 870 46e b2-e 33f 4 2 7 5 -b29b- 3af d1e 2 6e bf3 /it e m s? e x pan d=f ie l ds ©2020 Ai HIRANO 22

©2020 Ai HIRANO 23

DirveItem G E T h t t ps ://g raph . mic ro so f t .c o m/v1. 0/si t e s/ o f fi c e il e arn i n g. sh are po i n t. c om, e aa1b7f 4 -c 7 d1- 4 0 c a-ab 07-465b 15bb8bf a, 71d5 4204 -f 303-4 aac -8745-7 76e 48a3a d80/ l is t s/ 870 46e b2-e 33f 4 2 7 5 -b29b- 3af d1e 2 6e bf3 /it e ms/ 1/ d ri ve It e m ©2020 Ai HIRANO 24

©2020 Ai HIRANO 25

DriveIDの取得 h t t ps ://g raph . mic ro so f t .c o m/v1. 0/si t e s/ o f fi c e il e arn i n g. sh ar e poi n t .c o m,e aa1b 7f4 -c7 d1- 40c a- a b 0 7 -465b15 bb8bf a, 71d542 04- f 303-4aac -8 745- 776e 4 8a3ad8 0 /dri v es ©2020 Ai HIRANO 26

DriveID でファイルを取得する h t t ps ://g raph . mic ro so f t .c o m/v1. 0/si t e s/ o f fi c e il e arn i n g. sh ar e poi n t .c o ,e aa1b 7f4 - c7 d1- 40c a- a b 0 7 -465b15 bb8bf a, 71d542 04- f 303-4aac -8 7457 7 6 e 48 a3ad80 /dri ve s / b!9Le h 6t H H yk C rB0Z bFbu L - g R C1X E D 86x K h 0V 3bk ij rY C y bgS H P- N 1 Q rK b Ov 0e Ju vz /it e ms/01 OF6T M E SGFK O BV YX T YJDJ G56N L I ZT5X X J ©2020 Ai HIRANO 27

参考 : E xcel - PM T (ペイメント) 関数 ロ ー ン 返済、積立貯金の払込額を求める関数 パ ラ メ ーター R a t e ( 利 率 )…利率 N p e r ( 期 間 )…返 済 期 間 P v ( 現 在 価値) … 借 入 額 [ 将 来 価値] …借入を完済する場合は 0 [ 支 払 期 日 ] … 返 済 が 期 末 の 場 合 は 省 略 可 能 (0) 、期首の場合は 1 https://support.microsoft.com/ja-jp/office/pmt-関数-0214da64-9a63-4996-bc20214433fa6441?ui=ja-jp&rs=ja-jp&ad=jp ©2020 Ai HIRANO 28

• https://support.microsoft.com/ja-jp/office/pmt-

関数を呼び出そう P O S T h t t ps: / / gr aph .m i c ro so f t. c om / v 1. 0/ si t es / o ff i ce i l ea rn in g .s h are po i nt . co , ea a1b7f 4- c 7d1 - 4 0 c a-ab 07-465b 15bb8bf a, 71d5 4204 -f 303-4 aac -87457 7 6 e 48 a3ad80 /dri ve s /b!9Le h 6t H H yk C rB0Z bFbu L - g R C1X E D 86x K h 0V 3bk ij rY C y bgS H P- N 1 Q rK b Ov 0e Ju vz /it e ms/01 OF6T M ESGFK O BV YX T YJDJ G56N L IZT5X X J/ wo rk bo o k/f u n c ti o n s/pmt ©2020 Ai HIRANO 29

Average P O S T … / w o rk bo o k /fu n c ti o n s/av e rag e ©2020 Ai HIRANO 30

E xcel 内の関数の使い方に関する公式ドキュメント h t t ps ://do c s. mic ro so f t .c o m/e n -u s / gr aph / e xc e l - u se - f un c t io n s ©2020 Ai HIRANO 31

• https://docs.microsoft.com/en-us/graph/excel-use-functions

Azure AD 側 Power Automate から の Graph API 呼 び出しのおさらい • アプリの登録を行いクライアントID,ク ライアントシークレットを生成し、テナ ントIDとともにこれらの情報を入手す る • 登録したアプリに必要なアクセス許可を 付与する Power Automate 側 • HTTP要求コネクターを使って呼び出す ©2020 Ai HIRANO 32

[参考] アプリのアクセス許可 ©2020 Ai HIRANO 33

SharePoint O nline + M icrosoft Grap h + Po wer Plat form シ ス テ ム構成図 ドキュメント ライブラリ Microsoft Graph経由 Word Online コネクター ＋ PDF化してメール送信 ©2020 Ai HIRANO 34

Demo ローン返済のシミュレーション ©2020 Ai HIRANO 35

M icrosoft Grap h – Excel REST API の呼び出し ©2020 Ai HIRANO 36

PM T 関数の呼び出し部分 div(div(variables('rate'), 100),12) mul(variables('nper'), 12) int(concat('',variables('pv'))) ©2020 Ai HIRANO 37

W o rd ファイルと PDF ファイルの生成 Wo r d On line コ ネ ク ターを利用 ©2020 Ai HIRANO 38

W o rd ファイルの生成 テ ン プ レートとして利用する W o r d フ ァ イ ルを事前に用意しておく 必 要 が ある ©2020 Ai HIRANO 39

W o rd ファイルの準備 ©2020 Ai HIRANO 40

PDF ファイルを生成し、メールに添付する ©2020 Ai HIRANO 41

E x c e l は デ ー タの再利用性を考えよう! まとめ S h a re Po i n t ( O n e Dri v e f o r B u s i n e ss ) + M i c ro so f t 3 6 5 A p p s + P o w e r P l a t f or m の 組 み 合わせ方を い ろ い ろと研究してみよう プ レ ミ アムコネクターにも挑戦してみてください。 ©2020 Ai HIRANO 42