javascriptでXLSXをプレーンテキストに
javascriptでDOCX,ODTをプレーンテキストに と同様 ExcelのXLSXフォーマットの解析と
javascriptでのプレーンテキスト化に挑戦した
動作環境
- DOMParserが使えるブラウザ
- JSZip ( https://stuk.github.io/jszip/ )
XLSXの構造調査
XLSXを展開すると以下のようなファイルツリーになっている
filename.xlsx
|- /docProps
|- /xl
| |- /theme
| |- /worksheets
| | |- sheet1.xml
| | |- sheet2.xml
| | +- sheet3.xml
| |
| |- /_rels
| |- workbook.xml
| |- sharedStrings.xml
| +- styles.xml
|
|- /_rels
+- [Content_Types].xml
DOCXと違い1つのXMLファイルからでは情報が不足している。
例えば、Sheet1のテーブルはsheet1.xmlにあるが、出現する文字列はsharedStrings.xmlにまとめられている。
sheet1.xmlにはsharedStrings.xmlの何番目の文字列かを示す数字だけが入っている。
また、セルの書式はstyles.xmlにまとめられているので、セルの値をどう表示するかを決めるためにはstyles.xmlが必要である。
他にも色、ボーダー、フォントなどもstylesにあるがプレーンテキスト化には関係ないので今回は言及しない。
sheet1.xmlの構造
sheet1.xml(sheet2,sheet3も同様)は以下のような構造だった
xl/worksheets/sheet1.xml
<worksheet>
<cols>...</cols>
<sheetData>
<row r="1">
<c r="A1"><v>1</v></c>
<c r="B1" s="2"><v>2</v></c>
<c r="C1" s="2" t="s"><v>3</v></c>
<c r="D1" s="3"><v>0.37152777777777773</v></c>
...
</row>
</sheetData>
</worksheet>
-
row要素が行、c要素がセルの単位になる - セルの中の
v要素内がセルの値である - 行の
r属性で行番号が、セルのr属性で列の位置を示すリファレンスが指定されている - セルの
t属性によってセルのデータ型が決定する。デフォルトは数値のようだ-
t属性の値としては s(SharedString), inlineStr, b(Boolean), d(Date), str(string) などがある
-
- セルの
s属性でスタイルが決定する。これもデフォルトは数値のようである
sharedStrings.xmlの構造
sheetN.xml内のセルのデータ型が s(SharedString) だった場合はsharedStrings.xmlから文字列データを取得する必要がある
xl/sharedStrings.xml
<sst>
<si><t>str1</t></si>
<si><t>str2</t></si>
<si><t>3番目の文字列</t></si>
...
</sst>
例えば、<c t="s"><v>2</v></c> というセルはエクセル上では 3番目の文字列 という表記になる
styles.xmlの構造
xl/styles.xml
<styleSheet>
<fonts>...</fonts>
<fills>...</fills>
<borders>...</borders>
<cellStyleXfs>...</cellStyleXfs>
<cellXfs>
<xf numFmtId="0" fontId="0" ...></xf>
<xf numFmtId="0" fontId="2" ...></xf>
<xf numFmtId="0" fontId="0" ...></xf>
<xf numFmtId="20" fontId="0" ...></xf>
...
</cellXfs>
<cellStyles>...</cellStyles>
<tableStyles>...</tableStyles>
</styleSheet>
例えばセルに <c s="3"><v>0.5</v></c> のようなスタイルの指定があった場合、エクセル上では12:00と表示される。
なぜならば、スタイルの3番目<xf numFmtId="20" ...はセルの値をh:mm形式で表示するため。
numFmtId(Number Format)とフォーマットコードの関係は以下(ECMA-376 §18.8.30より)
| ID | formatCode |
|---|---|
| 0 | General |
| 1 | 0 |
| 2 | 0.00 |
| 3 | # |
| 4 | # |
| 9 | 0% |
| 10 | 0.00% |
| 11 | 0.00E+00 |
| 12 | # ?/? |
| 13 | # ??/?? |
| 14 | mm-dd-yy |
| 15 | d-mmm-yy |
| 16 | d-mmm |
| 17 | mmm-yy |
| 18 | h:mm AM/PM |
| 19 | h ss AM/PM |
| 20 | h:mm |
| 21 | hss |
| 22 | m/d/yy h:mm |
| 37 | #,##0 ;(#,##0) |
| 38 | #,##0 ;[Red](#,##0) |
| 39 | #,##0.00;(#,##0.00) |
| 40 | #,##0.00;[Red](#,##0.00) |
| 45 | mm:ss |
| 46 | [h]ss |
| 47 | mmss.0 |
| 48 | ##0.0E+0 |
| 49 | @ |
抜けている27～36,50～58は国別に違うフォーマットになる。
| ID | formatCode(ja-JP) |
|---|---|
| 27 | [$-411]ge.m.d |
| 28 | [$-411]ggge"年"m"月"d"日" |
| 29 | [$-411]ggge"年"m"月"d"日" |
| 30 | m/d/yy |
| 31 | yyyy"年"m"月"d"日" |
| 32 | h"時"mm"分" |
| 33 | h"時"mm"分"ss"秒" |
| 34 | yyyy"年"m"月" |
| 35 | m"月"d"日" |
| 36 | [$-411]ge.m.d |
| 50 | [$-411]ge.m.d |
| 51 | [$-411]ggge"年"m"月"d"日" |
| 52 | yyyy"年"m"月" |
| 53 | m"月"d"日" |
| 54 | [$-411]ggge"年"m"月"d"日" |
| 55 | yyyy"年"m"月" |
| 56 | m"月"d"日" |
| 57 | [$-411]ge.m.d |
| 58 | [$-411]ggge"年"m"月"d"日" |
今回はこの辺りはまじめに実装しない。日付かそれ以外かの区別程度にしておく。
数値から日付・時刻の変換
書式指定で日付や時刻表示される場合、セルの値(数値)を日付に変換する必要がある。
このとき、セルの値が0だと1900-01-01 00:00:00になり、1だとその翌日になる。
jsのDateは1970年開始のミリ秒単位なので変換は容易。
70年分の日数を引いて246060*1000倍すればよい。
ただ、セルの値が0で1904-01-01 00:00:00になることがあるようだ。
この場合はxl/workbook.xml内のworkbookPr要素のdate1904属性が1になっている。
xl/workbook.xml
<workbook>
<workbookPr date1904="1"/>
<sheets>...</sheets>
</workbook>
xlsx2txtの実装
以上を踏まえて実装する
"use strict";
var xlsx2txt,cell2txt,a1ref;
cell2txt = function(cell, sst, styles) {
var val, t, s, fmtId;
val = cell.getElementsByTagName('v')[0];
val = val ? val.textContent : null;
if (val === null) {return null;}
t = cell.getAttribute('t');
s = cell.getAttribute('s')-0;
if (t === 's') {return sst[val];}
if (s && styles[s]) {
fmtId = styles[s].numFmtId;
if (fmtId >= 14 && fmtId < 37 || fmtId > 44 && fmtId < 48 || fmtId > 49 && fmtId < 59) {
val = new Date((val - 25567) * 86400000);
}
}
return val;
};
a1ref = function(a1) {
var m, r, c, i, p;
if (!(m = a1.toUpperCase().match(/^([A-Z]+)([0-9]+)$/))) {
return null;
}
r = m[2]-1;
for(c=0,i=m[1].length-1,p=1; i>=0; p*=26,i--) {
c += (m[1].charCodeAt(i)-64) * p;
}
return [r, c-1];
};
xlsx2txt = function(file, callback) {
var fr = new FileReader();
fr.onload = function() {
var zip,xml,dom,sst,styles,data,rows,cells,pos,txt,i,j;
zip = new JSZip(fr.result);
dom = new DOMParser();
xml = {
styles: dom.parseFromString(zip.file('xl/styles.xml').asText(), 'application/xml'),
sst: dom.parseFromString(zip.file('xl/sharedStrings.xml').asText(), 'application/xml'),
sheet1: dom.parseFromString(zip.file('xl/worksheets/sheet1.xml').asText(), 'application/xml')
};
txt = "";
sst = {list:xml.sst.getElementsByTagName('si')};
for(i=0; i<sst.list.length; i++) {
sst[i] = sst.list[i].textContent;
}
styles = {list:xml.styles.getElementsByTagName('cellXfs')[0].childNodes};
for(i=0; i<styles.list.length; i++) {
styles[i] = {numFmtId: styles.list[i].getAttribute('numFmtId')-0};
}
data = [];
rows = xml.sheet1.getElementsByTagName('row');
for(i=0; i<rows.length; i++) {
cells = rows[i].getElementsByTagName('c');
for(j=0; j<cells.length; j++) {
pos = a1ref(cells[j].getAttribute('r'));
if (!data[pos[0]]) {data[pos[0]] = [];}
data[pos[0]][pos[1]] = cell2txt(cells[j], sst, styles);
}
}
for(i=0; i<data.length; i++) {
for(j=0; j<data[i].length; j++) {
txt += data[i][j] ? ('"' + (data[i][j]).toString().replace(/"/g,'""') + '"') : "";
if (j<data[i].length-1) {txt += ",";}
}
txt += "\n";
}
callback(txt);
};
fr.readAsArrayBuffer(file);
};
//FileオブジェクトはDrag and Dropやinput[type='file']などで作成。以下の例はinputタグ
document.getElementById('inputFile').onchange = function(e) {
var file = e.target.files[0];
if (file && file.name.match(/\.xlsx$/i)) {
xlsx2txt(file, function(txt) {
console.info(txt);
});
}
};
簡単のためいろいろと省略した
- 出力されるのはSheet1だけ
- 日付・時刻の書式はまともに反映していない
- 日付が1904年開始だったら間違っている
考察
Excelの仕様はやはり複雑だったが、ごくシンプルなXLSXファイルだったらjsでも読めることがわかった。