Posted at

[SharePoint2010]Excelのデータを連携して、ページに反映する仕掛けを作りました。


作った理由

実務をするオペレータの人は、Excelで集計表を作ります。

この集計表(随時更新される)の内容を、SharePointのページに簡単に反映したいというご希望がありました。

更新するたびに、Excelの内容をページの表に手打ちで写して入力していくのは超めんどくさい……。

ExcelServicesが使えれば、もしくは365でモダンサイトとかだったらそれで終わるような話なのですが、SharePoint2010オンプレで、しかも安いほうのライセンスなので、そういうのは使えないのです。

なければ作るしか……。


集計表のイメージ

2016年より2017年のほうが良い数字なら緑、悪い数字なら黄色で表示しています。

無題.png


手順


ExcelからCSVでデータを出力

私は、表の右側の使っていないエリアに、SUBSTITUTEとかCONCATENATEとかでセルの内容をCSVテキストの形にして、それをマクロでファイル出力しています。

もし日本語が入ってるデータなら、出力ファイル形式はUTF-8Nで。じゃないと化けるので。

Excelは普通にテキストファイルを出力するとShift-JISで出ます。UTF-8はBOM付きで出ます。


2017年データ testdata2017.txt

"0.269","56","2"

"0.320","103","1"
"0.280","52","2"
"0.190","19","1"


2016年データ testdata2016.txt

"0.216","36","3"

"0.292","175","3"
"0.226","28","0"
"0.254","116","6"


出力したCSVファイルをSharepointの指定ライブラリにアップロード

更新するときは、ファイルを同名で上書きアップロードする運用にしています。


ページでCSVを読む


ページのテーブル

<div>

<table border="1" cellspacing="2" cellpadding="2">
<thead>
<tr>
<th>氏名</th>
<th></th>
<th>打率</th>
<th>安打</th>
<th>本塁打</th>
</tr>
</thead>
<tbody>
<tr align="right">
<td rowspan="2">藤井</td>
<td>2017年</td>
<td id="K001"></td>
<td id="K002"></td>
<td id="K003"></td>
</tr>
<tr align="right">
<td>2016年</td>
<td id="K001m"></td>
<td id="K002m"></td>
<td id="K003m"></td>
</tr>
<tr align="right">
<td rowspan="2">大島</td>
<td>2017年</td>
<td id="K101"></td>
<td id="K102"></td>
<td id="K103"></td>
</tr>
<tr align="right">
<td>2016年</td>
<td id="K101m"></td>
<td id="K102m"></td>
<td id="K103m"></td>
</tr>
<tr align="right">
<td rowspan="2">亀澤</td>
<td>2017年</td>
<td id="K201"></td>
<td id="K202"></td>
<td id="K203"></td>
</tr>
<tr align="right">
<td>2016年</td>
<td id="K201m"></td>
<td id="K202m"></td>
<td id="K203m"></td>
</tr>
<tr align="right">
<td rowspan="2">堂上</td>
<td>2017年</td>
<td id="K301"></td>
<td id="K302"></td>
<td id="K303"></td>
</tr>
<tr align="right">
<td>2016年</td>
<td id="K301m"></td>
<td id="K302m"></td>
<td id="K303m"></td>
</tr>
</tbody>
</table>
</div>


CSVを読んでテーブルに展開する仕掛け

csvのカンマで分割して二次元配列に格納して、それをテーブルに転記しています。

あと、数字を見比べて緑と黄色に塗るのもやっています。

    var mlist = ["01", "02", "03"];

//ファイルからデータを受け取って整形して返す
var trimDataFromText = function(data) {
var trimedDatas = [];
var zdatas = data.split(/\r\n|\r|\n/);
for (var i = 0; i < zdatas.length; i++) {
var _tmp = [];
var zdatat = zdatas[i].split(',');
for (var j = 0; j < zdatat.length; j++) {
_tmp.push(zdatat[j].replace(/^\"|\"$/g, ''))
}
trimedDatas.push(_tmp);
}
return trimedDatas;
}

var z2017s;
var z2016s;

//2017ここから
var ajax1 = $.ajax({
url: '/(csvファイルを置いたライブラリ)/testdata2017.txt',
success: function(data) {
var zdatas = trimDataFromText(data);
z2017s = zdatas;
for (var i = 0; i < zdatas.length; i++) {
var zdata = zdatas[i];
for (var j = 0; j < mlist.length; j++) {
$("#K" + i + mlist[j]).html(zdata[j]);
}
}
}
});

//2016ここから
var ajax2 = $.ajax({
url: '/(csvファイルを置いたライブラリ)/testdata2016.txt',
success: function(data) {
var zdatas = trimDataFromText(data);
z2016s = zdatas;
for (var i = 0; i < zdatas.length; i++) {
var zdata = zdatas[i];
for (var j = 0; j < mlist.length; j++) {
$("#K" + i + mlist[j] + "m").html(zdata[j]);
}
}
}
});

//2017>=2016なら緑、2017<2016なら黄色
function CheckMokuhyo() {

var jikan;
var jikanm;

for (var i = 0; i < 4; i++) {
var z2017 = z2017s[i];
var z2016 = z2016s[i];
for (var j = 0; j < mlist.length; j++) {
if (+z2017[j] > +z2016[j]) {
$("#K" + i + mlist[j]).css("background-color", "lightgreen");
} else {
$("#K" + i + mlist[j]).css("background-color", "yellow");
}
}
}
}

$.when(ajax1, ajax2).done(CheckMokuhyo);


残された問題

集計表の形が変わったら(たとえば集計項目が増えるとか)Excelの表と、CSVにするところと、SharePointのtableと、あとJavaScriptで転記しているところと、直さないといけないのです。直すところいっぱい……。

なので表の形自体はそんなに変わらなくて、中のデータだけ頻繁に変わるようなものをこれでやっています。

表の形が頻繁に変わるようなのはどうしたらいいのでしょうか。値の入ってるtableタグをExcelで出力して、それを読むとかでしょうか……。