はじめに
現在、Microsoft Excelを利用したデータの管理を行っているケースが、まだまだ多いのではないでしょうか。
大小を問わずまだまだ多くの企業では、日々の仕事の煩雑に追われ、慣れ親しんだExcelを利用したデータの入力および管理から抜けられずにいるのではないでしょうか。
そうして日々蓄積されたデータには、会社営業の行く先を決める大事な情報がたくさん入っていると思われますが、複数のExcelファイルに分かれたデータからそのような大事な情報を読み出すには、それなりの労力と時間を費やす必要があると思われます。
一般的には、データは社内ネットワーク上に配置されたデータベースサーバーに集積され共有されることで、社内の各部署からそれぞれに必要なデータが常に閲覧可能になり、迅速な意思決定の助けになればいいですよね。
今回は、Sharperlightを利用して、それを実現する第一歩を見ていきたいと思います。
ここに、このようなExcelで作成された売上台帳があるとします。このサンプルを利用して実際に作業してみます。
データベースの準備
Excelで作成された売上台帳のデータを、Microsoft SQL Serverデータベースに保存する準備を行います。
空データベース
MS SQL Server Management Studioを利用して、データ保存用のデータベースを新規作成します。
テーブル
作成したデータベースに、新しいテーブルを準備します。
Excelの売上台帳を注意深く読んでみます。
どうやらヘッダーとその詳細に分かれそうですね。
伝票番号306で見てみると、緑の列がヘッダー情報で、赤の列がその明細のようですね。
では、ヘッダーテーブルと明細テーブルを作成します。
Excelシート上の列名を利用します。
--売上ヘッダーテーブル
USE [〇〇会社]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SalesHeader](
[Row_ID] [int] IDENTITY(1,1) NOT NULL,
[伝票番号] [int] NOT NULL,
[伝票単位数] [int] NOT NULL,
[売上区分] [nvarchar](100) NOT NULL,
[年月日] [date] NOT NULL,
[担当営業員] [int] NOT NULL,
[店舗支店コード] [nvarchar](50) NOT NULL,
[顧客コード] [nvarchar](50) NOT NULL,
[顧客名] [nvarchar](200) NOT NULL,
[顧客TEL] [nvarchar](50) NOT NULL,
[メールアドレス] [nvarchar](200) NOT NULL,
CONSTRAINT [PK_dbo_SalesHeader_Row_ID] PRIMARY KEY CLUSTERED
(
[Row_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--売上明細テーブル
USE [〇〇会社]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SalesDetail](
[Row_ID] [int] IDENTITY(1,1) NOT NULL,
[伝票番号] [int] NOT NULL,
[明細番号] [int] NOT NULL,
[商品コード] [nvarchar](50) NOT NULL,
[数量] [int] NOT NULL,
[単位] [nvarchar](20) NOT NULL,
[単価] [int] NOT NULL,
[金額] [int] NOT NULL,
[分類] [nvarchar](10) NOT NULL,
[品番] [nvarchar](100) NOT NULL,
CONSTRAINT [PK_dbo_SalesDetail_Row_ID] PRIMARY KEY CLUSTERED
(
[Row_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
これで、売上データの入れ物は準備できました。
Sharperlight データモデルの作成
ここでは、Sharperlightを使用するための準備をします。
Sharperlightとデータベースの会話を成立させるためのコネクター: データモデルを作成します。
データモデルの作成はSharperlightスタジオで行います。Sharperlightアプリケーションメニューからスタジオを起動します。
接続
Sharperlightスタジオとデータベースを接続し、会話に必要なデータベーススキーマを読み込みます。
メニューバーからNewアイコンをクリックします。
データモデル（ここではプロダクトと呼ぶ）に必要な情報を入力します。OKで先に進みます。
データベースの接続を確立します。
先ほど作成したデータベースの接続情報を入力します。接続テストボタンで接続を確認します。上手く接続ができたらOKボタンで先に進みます。
スキーマ読込
スキーマを読み込みますかという確認メッセージが表示されるので、Yesボタンで先に進みます。
スキーマを読み込むためのダイアログが表示されます。Get Tablesボタンで読み込みを開始します。
スキーマ情報が表示されたらOKボタンで読み込みます。
スキーマが読み込まれました。
テーブル定義の編集
既定ではこのデータモデルと対象データベース間の会話は読み込み専用となっています。
今回は、Excelファイルのデータをデータベースに書き込みたいので、テーブルの設定を書き込み可にします。
両テーブルのAllow Table WritebackオプションをTrueにします。テーブルのアイコンが赤くなります。
データモデルの保存とSharperlightアプリケーション用の接続
メニューバーの保存アイコンでデータモデルを保存します。
注意を促すメッセージが表示されるので、OKで先に進みます。
既定の保存場所が指定されるので、SAVEボタンで保存します。
Sharperlightアプリケーション用の接続設定を促すメッセージが表示されるので、Yesボタンで接続設定画面に移動します。
Sharperlightクライアントセットアップが開きます。このアプリケーションでは、インストールされている各データモデルとその対象データベースの接続を管理します。
今回作成したデータモデルの対象データベースへの接続情報を入力し、接続テストボタンで接続を確認後、OKボタンで先に進みます。
以上でSharperlightデータモデルの作成は一応完了。Sharperlightスタジオを閉じます。
データの書込み
では作成したデータモデルとその接続を利用してデータの書き込みを行います。
MS Excelを起動し、Sharperlightアドインにログインします。
そして売上台帳を開きます。
売上台帳がExcel上でのテーブル化がされていない場合は、テーブル化しておきます。
Sharperlight Writeback(書き戻し)の定義
データの書き込みには、SharperlightのWriteback(書き戻し)機能を利用します。
Sharperlightリボンの書き戻しアイコンをクリックして書き戻しダイアログを開きます。
製品の選択
製品（データモデル）を指定します。検索ボタンで一覧から選択します。
テーブルの選択
検索ボタンでをクリックすると、選択した製品（データモデル）内で書き込み可なテーブルの一覧が表示されるので、その中から書き込み対象となるテーブルを選びます。
フィールドタブに書き込み対象のフィールド一覧が表示されます。
書き戻し名の記入
この書き戻し定義を保存する場合の保存名となります。
フィールドのマッピング
Excelシート上の列とデータベーステーブルの列をデータモデルを介して結びつけます。
フィールドタブ上のどのフィールドでもよいので選択します。その後Excelシート上のテーブルヘッダーをダブルクリックします。マッピング機能が起動し、注意を促すメッセージが表示されます。Yesボタンで確定します。
Excelシート上の列名とデータモデルに定義された列名が同じ場合、この機能でマッピングが作成されます。
店舗支店コードと顧客 Telフィールドのマッピングが完了していません。これは列名の不一致によるものです。手動でマッピングを行います。
フィールドタブ上の店舗支店コードを選択した後、Excelシート上の店舗支店コード列のヘッダーをダブルクリックします。先ほどと同じメッセージが表示されるので、今度はNoボタンを押します。
このようにマッピングが設定されました。
同じように顧客 Telフィールドにも手動でマッピングを作成します。
このようにマッピングが完了しました。
ドライバ列の指定
Excelシートからデータを読む場合、どの列を基準にするかを指定します。
伝票番号列をドライバとします。
Writeback(書き戻し)の定義の検証
定義が完了したので動作の検証を行います。検証ボタンをクリックします。このようなメッセージが表示されれば正常です。
Writeback(書き戻し)の定義の保存
ダイアログを閉じると保存を促すメッセージが表示されるので、Yesボタンで保存します。上記で記入した書き戻し名でこのシート内に保存されます。
Sharperlight Writeback(書き戻し)の実行
では早速実行してみます。Sharperlightリボンの書き戻しアイコンで書き戻しダイアログを再度開き、書き戻し名検索ボタンで保存されている書き戻し定義一覧を表示します。読み出したい定義売上ヘッダーを選択します。
定義が読み込まれたら実行ボタンをクリックします。
このようなログが表示されれば、実行は正常に行われたということです。
データベーステーブルを確認してみます。
おやおや！書き戻しは正常に行われているのに、ヘッダー情報が重複してしまっています。
調整の必要がありますね。
ユニークキーの設定
データモデル側でユニークキーの設定を行います。
Sharperlightスタジオで〇〇会社営業データデータモデルを開きます。
テーブルタブのSalesHeaderテーブルの定義を開きます。
伝票番号フィールドのIs UniqueオプションをTrueにします。
データモデルを保存します。変更を有効にするためExcelアドインからログオフし、再度ログオンします。
Truncate tableコマンドでデータベーステーブルを空っぽにして、再度書き戻しを実行します。
書き戻しは正常終了しました。
データベーステーブル側では、重複は解決されたようです。
正確に確認してみましょう。
SELECT 伝票番号, COUNT(伝票番号)
FROM [〇〇会社].[dbo].[SalesHeader]
GROUP BY 伝票番号
HAVING COUNT(伝票番号) > 1
重複はありません。
同じような手順でSalesDetailテーブルもデータの書き戻しを行います。
SalesDetailテーブルでも同じように重複問題が発生すると思われるので、データモデルのテーブル定義を更新してから書き戻しを実行します。
SalesDetailテーブルでは、伝票番号、明細番号および商品コードでユニークキーを形成します。
このように設定しデータモデルを保存します。
あとはSalesHeaderテーブルの時と同じように書き戻し定義を作成し実行します。
Excel売上台帳を書き戻し専用シートにする
ここまでで基本となる書き戻し定義の作成が完了し、Excel売上台帳上のデータがデータベースに書き戻されたので、そのデータを元にExcel売上台帳を書き戻し専用シートに改良します。
以下の手順の繰り返しで、売上データの入力管理が行えるようにします。
- クエリを利用してデータベースとExcel売上台帳のデータの同期をとる。
- 新規データをExcel売上台帳に入力する。
- Excel売上台帳の書き戻し定義を利用して、データベースを更新する。
改良する前にExcel売上台帳のバックアップをとっておきます。
クエリの定義
クエリを作成する前にデータモデルのSalesDetailテーブル定義にテーブルJOINを追加しておきます。クエリを作成する場合、何かと便利なのでJOINを作成しておきます。
これは、実際のデータベースに作成するわけではなく、Sharperlightのデータモデル上に作成されるJOINです。
伝票番号フィールドを選択し、右クリックメニューからNew Joinをクリックします。
JOIN作成支援ダイアログが表示されるので、SalesHeaderテーブルへのJOINを作成します。
テーブルJOINが作成されました。データモデルを保存します。
Excel売上台帳に戻り、データモデルへの変更を有効にするためにSharperlightからログオフし、再度ログオンします。
行を最上段に追加します。セルA1を選択後、Sharperlightリボンからテーブルアイコンをクリックしクエリビルダを開きます。
クエリビルダでは下図のような設定を行います。
出力アイテムに設定したフィールドの説明をExcel売上台帳のテーブルヘッダー名に合わせておくといいでしょう。
クエリ名の入力を促すダイアログが表示されるので、クエリ名を入力しOKを押します。
Sharperlightテーブル式はセルA１に設定され、クエリが即実行されますが、結果は既存のExcelテーブルに邪魔されて表示されません。
シート上の既存のテーブルを削除します。既存のテーブルが削除されると直ぐSharperlightテーブル式が再度実行され、新たなテーブルが作成されます。
書き戻し定義の更新
クエリによってテーブルが再作成されたので、はじめに作成した書き戻し定義を見直します。
保存してある売上ヘッダー書き戻し定義を開き、すべてのマッピング情報を消去します。
定義上の伝票番号を選択しExcelテーブルのヘッダーをダブルクリックします。新しいマッピングが作成されます。
閉じるボタンで保存します。
同じように売上明細書き戻し定義もマッピングを更新します。
クエリとボタンの配置
クエリ、二つの書き戻し処理がボタンで行えるようにします。
Excelシート上部に更に3行追加します。ここにボタンを配置します。
Sharperlightリボンより、ボタン->再計算->mdRecalcActiveWorksheetを選択します。
クエリを実行するボタンがこのように追加されます。
セルA4にはクエリ式が定義されているので、行4は隠しておきます。
次に書き戻し定義を実行するボタンを追加します。
Sharperlightリボンより、ボタン->書き戻し->mdWriteBackを選択します。
書き戻し定義一覧が表示されるので、このボタンに結び付ける定義を選択すると書き戻しボタンが作成されます。
最終的には、書き戻しボタンが二つできるのでキャプションを編集します。
こうすることで毎回書き戻し定義を呼び出して、書き戻し定義ダイアログ上の実行ボタンで実行する手間が省けます。
書き戻しログをシート上に表示する
最後に、書き戻しを実行した際に開かれるログ情報ダイアログが邪魔なので、ログ情報はシート上の指定の場所に表示するよう変更をかけます。
各書き戻し定義を開き、処理オプションタブに移動します。
履歴表示用のセルオプションにセル参照を指定します。
指定したシート上のセル属性を調整し、ログ情報が上手く表示されるようにしておきます。
以上で売上台帳を使用した書き戻しが可能になりました。
クエリでExcel売上台帳とデータベースの同期をとった後、新しい売上レコードを売上台帳に追加します。
次に書き戻し:売上ヘッダーボタンで新しいレコードのヘッダー情報を先にデータベースに書き戻します。最後に書き戻し:売上明細ボタンで新しいレコードの明細情報をデータベースに書き戻します。
とりあえずこのような手順で日々の売上データをデータベースに書き戻すことができるようになりました。
レポートの作成
以上でExcel売上台帳上のデータがデータベースに移行できました。
あとはSharperlightを使用してデータの可視化をしてみます。
レポート
もちろんSharperlight Excelアドインを利用して、Excel上にレポートを作成することも可能ですが、SharperlightパブリッシャーでWebレポートを作成し、Sharperlightサービスを利用して社内で共有することも可能です。また各種のセキュリティ設定を行った上で、外部に公開することさえ可能になります。
Sharperlightパブリッシャを起動します。新規ボタンで新しいレポートの作成を開始します。
Webレポートのダイアログが開いたら、コード、グループ、タイトル等を記入し、クエリを編集ボタンでクエリの作成を開始します。
モードには概要レポート、製品には〇〇会社営業データ、テーブルにはSales Detailを指定します。
選択領域に選択可能なフィールド一覧が表示されるので、それらを利用してフィルターや出力アイテムを設定します。
売上区分フィールドと年月日フィールドにフィルターを設定してみました。年月日フィルターの値 DATE -2m は、Sharperlight特有の記号で、現在の年月日から2か月前(マイナス 2 month)という意味を表します。また年月日フィルターには、年/月オプションを設定し、年月単位でフィルターをかけるようにしました。
つまりクエリを実行する日が2023年5月の場合、生成されるWHERE句はこのようになります。
CAST(YEAR(J002.[年月日]) AS varchar(20)) + '/' + RIGHT('0' + CAST(MONTH(J002.[年月日]) AS varchar(20)),2) BETWEEN N'2023/03' AND N'2023/03'
では、OKボタンでクエリを保存し、WEBレポートもOKボタンで保存します。
Sharperlightサービスを起動し、早速作成したばかりのレポートをブラウザで開いてみましょう。以下のURLでレポートを表示できます。
http://localhost/DemoRest/Report/?query=QiitaSample.ExcelToDatabase
もう少し編集してみます。
レポートのタイトルが左側に表示されるように編集します。一般タブの配置オプションで変更可能です。
プロンプト（フィルター）を縦に揃えて（2行に渡って）表示します。オプションタブ、プロンプトオプションの1行の項目属性を1にします。1行に1フィルターのみ表示という意味で、今回はフィルターが2つあるので2行表示になるという事です。
では変更を保存して、ブラウザ上のレポートをF5で更新！どうでしょうか！？
更に、伝票番号でグループ化してみます。
クエリを開いて、出力アイテムの伝票番号フィールドのオプションを編集します。
オプションダイアログの並べ替えとグループ化タブで伝票番号を指定し、グループ化オプション 基本を設定します。
では変更を保存して、ブラウザ上のレポートをF5で更新！どうかな！？
グラフ
売上伝票と返品伝票の推移をグラフで表示してみます。
新しいレポートを作成します。コード、グループおよびタイトル等を記入します。
クエリを編集ボタンでクエリの作成を開始します。
先に作成したレポート同様、モード、製品、テーブル、いくつかのフィルターを設定し、出力アイテムも指定します。
今回は、フィルターに売上区分を売上用と返品用の二つ準備しました。これはこれから説明する出力アイテムに使用されます。
出力アイテムに先ず売上日を設定。更に伝票番号を二つ設定します。ひとつは、売上伝票をカウントするもの、もうひとつは、返品伝票をカウントするものです。それぞれの説明を、売上伝票と返品伝票と書き換えます。
売上伝票をカウントする方のオプションでは、集計属性にカウント、出力項目にフィルターを適用属性では、先ほどの売上区分：売上フィルターを選択します。
返品伝票をカウントする方のオプションでは、集計属性にカウント、出力項目にフィルターを適用属性では、先ほどの売上区分：返品フィルターを選択します。
この設定でこのようなSQL文が生成されます。
,COUNT( CASE WHEN (J002.[売上区分]=N'売上'
) THEN J001.[伝票番号] ELSE NULL END )
,COUNT( CASE WHEN (J002.[売上区分]=N'返品'
) THEN J001.[伝票番号] ELSE NULL END )
ではプレビュー機能でクエリの確認。上手くデータが返ってきました。
クエリを保存して、レポートダイアログに戻ります。
オプションタブで既定の出力形式をChartに、更にプロンプトを無効にします。
グラフタブでは、グラフの種類をArea、サイズ：幅を1300、タイトルの回転-度をマイナス45度、そしてカスタムタイトルを伝票数とします。
さあ、レポートを保存してブラウザで表示してみます。
http://localhost/DemoRest/Report/?query=QiitaSample.ExcelToDatabase2
このようなグラフが表示されました。
ダッシュボードページ
Sharperlightダッシュボードページを使用して、先に作成した二つのレポートをタイル表示してみようと思います。
アプリケーションメニューからダッシュボードページを起動します。
新規ボタンで新しいダッシュボードページの作成を開始します。
ダッシュボードページ定義ダイアログが表示されます。
ダッシュボードページの詳細
一般タブでは、このダッシュボードページの情報を記入します。コード、グループおよびタイトルは最低限記入します。
タイルの定義
タイルタブでは、実際にタイルを定義していきます。
既定でタイルが４つ設定されていますが、今回は２つでいいので、削除アイコンで２つ削除します。
先ず一つめのタイルですが、タイトルには売上伝票一覧、コンテンツURLには、一番先に作成した通常レポートを指定します。右隣りの検索ボタンで検索することができます。
オプションのタイトルを隠す属性をオンにします。
後はレイアウトの編集エリアで大きさと位置を調整します。
二つめのタイルも同じようにします。タイトルを売上伝票と返品伝票の推移、コンテンツURLには、先に作成したグラフレポートを指定します。指定したコンテンツURLの最後尾にグラフの幅を指定するオプション &width=1000を以下のように指定します
/Report?query=QiitaSample.ExcelToDatabase2&rtype=chart&ctype=area&width=1000
オプションのタイトルを隠す属性もオンにします。
最後にレイアウトの編集エリアで大きさと位置を調整します。
ダッシュボードページの検証と編集
ビューボタンでダッシュボードページをブラウザに表示します。
最初はこのように一部が上手く表示されていない不完全なタイルが表示されると思われます。
コンテンツが正しく見えるように、タイルの角を引っ張ってサイズを調整したり、タイル自体をドラッグして位置合わせを行います。
調整が終ったら、いづれかのタイルの右上隅に表示されるギアアイコンをクリックして、オプションページを開きます。
オプションページが開いたら、保存ボタンで調整したサイズと位置を保存します。
さて、どうかな？いい感じでした。
あとがき
長～い記事になってしまいましたが、この記事から何かひとつでも参考になるものが見つかって、あなたのお役に立てたらいいなと思います。