ExcelのCSV問題の対策補助ツール「jsCsvViewer」を作ってみた

Posted at 2024-09-17

はじめに

ここ最近、はてなブックマーク-人気エントリーにてCSVファイルに関する記事がエントリーされていました。

ExcelのCSVファイルの「0落ち」等の自動変換は昔から問題があり、皆が関心を持っていることが分かります。

検証.csv

前0,12桁以上,日付1,番地,括弧数値,日付2,日付3,正常
00001,123456789012,2024-9-17,1-2-3,(1),"2024-9-17",2024/9,1
00002,1234567890123,2024-8-31,2-3-4,(2000),"2024-8-31",2024/8,2

Excel 2019 for Mac では下図のように変換されて表示される。

Excelの自動データ変換の抑制機能

Microsoft 365のアップデートにより下記の自動データ変換は抑制されます。また買い切り版だと今年後半に発売予定のExcel 2024に搭載されるはず。
先頭のゼロを勝手に消すな～「Excel」のお節介な自動データ変換を抑制する機能が一般提供開始

先頭のゼロを削除して数値に変換する
ロング数値の最初の 15 桁を保持し、科学的記数法で表示する
文字“E”を囲む数字を科学的記数法に基づく数値に変換する
連続する文字と数字を日付に変換する

しかしながら次の3点については、まだ対処できていません。他もあるはず

24:00以上の時間数の形式が意図せず変換される
日付「〇〇/〇〇」が「〇〇月〇〇日」の形式に変換される
カッコつきの数「例 (1)」がマイナスの数に変換される

jsCsvViewerの作成

今回作成した「jsCsvViewer」の画面

CSVファイルを編集したいためにExcelを開きたいわけではなく単に中身をちょっとだけ確認したい、その際に出来れば見やすく表形式で見たいわけです。
CSV専用エディタや秀丸エディタのCSVモードやサクラエディタのCSVを見やすく設定などありますが、セキュリティが厳しい会社などはツールのインストール自体の許可が難しかったりします。

そこで、HTML5 + JavaScript + CSS のみで作成すればブラウザを開くだけで使え、かつWeb標準機能のみなので OS 関係なく使用することができます。

ライブラリは外部サーバーから読み込んでいますが、CSVファイルの読み込み処理はクライアント側のJavaScriptのみで行われているため、CSVファイルの内容が外部サーバーに送信されることはありません。

CSVファイルの編集はExcelやテキストエディタに任せ、jsCsvViewerはその名の通り表示専用ツールとして機能します。保存機能は後述するように限定的なもので、あくまで補助的な機能とお考えください。

使用ライブラリ

ライブラリ	内容	ライセンス
Jspreadsheet CE v4	JavaScript で Excel 風スプレッドシートを実現するライブラリ	MIT
jsuites	Jspreadsheetに付随するライブラリ	MIT
papaparse	JavaScript用の高速なCSVパーサー	MIT

筆者はHandsontableの記事をよく書いていますが、Handsontableの最新バージョンは14.5でMITライセンス版は6.2.2と差が開いてしまったため、今回は初期ごろのHandsontableをフォークしたJspreadsheet(旧名 jExcel)を数式やツールバーを標準搭載していることやCE版でもメンテナンスを継続していることもあり採用しました。

papaparseライブラリ側で区切り文字の自動検出や改行と引用符を正しく処理してくれます。

ソースコード

<!DOCTYPE html>
<html lang="ja">
<head>
  <meta charset="UTF-8">
  <title>jsCsvViewer Ver.1.0.0</title>
  <style type="text/css">
    #drop {
      position: absolute;
      top: 10; left: 10; width: 98%; height: 98%;
      z-index: -1;
    }
    #fileName {
      padding-left: 10px;
    }
    #grid {
      padding-top: 10px;
    }
    .jexcel thead tr td {
        text-align: center !important;
    }
  </style>
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/jspreadsheet-ce/dist/jspreadsheet.min.css" media="screen">
  <link rel="stylesheet" href="https://jsuites.net/v4/jsuites.css" type="text/css" />
  <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" type="text/css" />
</head>
<body>
  <div id="drop" ondragover="onDragOver(event)">
    CSVファイルをドラッグ&ドロップするかファイルの選択をしてください。<br>
    <input type="file" id="fileBox" accept=".csv, .tsv, .txt" style="display:none">
    <button id="fileSelect" type="button">ファイルを選択</button><span id="fileName"></span><br>
    <span style="flex:1;">文字コード
      <select id="encode-type">
        <option value="shift-jis">shift-jis</option>
        <option value="utf-8">utf-8</option>
      </select>
    </span>
    <input type="checkbox" id="isHeader" name="header" /><label for="header">ヘッダー</label>
    <button type="button" onClick="reload()">再読み込み</button>
    <button type="button" onClick="copyAllToClipboard()" >全選択コピー</button>
    <br>
    <div id="grid"></div>
  </div>
<script src="https://cdn.jsdelivr.net/npm/jspreadsheet-ce/dist/index.min.js"></script>
  <script src="https://jsuites.net/v4/jsuites.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/papaparse@5.4.1/papaparse.min.js"></script>
  <script type="text/javascript">
    let spreadsheet;  // jSpreadsheetのインスタンス変数
    let savePath;     // CSVパス退避変数

    var emptyData = Array.from({ length: 10 }, () => Array(10).fill(''));
    createGrid(emptyData, true);

    const fileSelect = document.getElementById("fileSelect");
    const fileBox = document.getElementById("fileBox");
    fileBox.addEventListener("change", inputChange);
    fileSelect.addEventListener("click", () => { fileBox.click(); }, false);

    if (window.File) {
      document.getElementById("drop").addEventListener("drop", onDrop, false);
    } else {
      window.alert("本ブラウザではFile APIが使えません");
    }

    // ファイル選択のファイルのプロパティ情報読み取り処理
    function inputChange(event) {
      let files = event.target.files;
      readCSV(files[0]);
    }

    // Drop領域にドロップした際のファイルのプロパティ情報読み取り処理
    function onDrop(event) {
      let files = event.dataTransfer.files;
      readCSV(files[0]);
    }

    // ドラッグオーバー処理
    function onDragOver(event){ 
      event.preventDefault(); 
    }

    // 再読み込みボタンのクリック処理
    function reload() {
      if (savePath)
        readCSV(savePath);
    }

    // 全選択コピーボタンのクリック処理
    function copyAllToClipboard() {
      spreadsheet.selectAll();
      spreadsheet.copy();
    }

    // CSVファイルの読み取り処理
    function readCSV(csvpath){
       jspreadsheet.destroy(document.getElementById('grid'));

      let element = document.getElementById("fileName");
      element.innerText = csvpath.name;

      let disp = document.getElementById("disp");
      let encode_type = document.getElementById("encode-type").value;

      let f = csvpath;
      savePath = f
      let reader = new FileReader();
      reader.onerror = function () {
        disp.innerHTML = "読み取り時にエラーが発生しました。";
      }

      if (f.type.match('text.*') || f.name.match(/csv$/m)) {
        // ファイル読取が完了した際に呼ばれる処理
        reader.onload = function () {
          // FileReaderが取得したテキストをそのままdivタグに出力
          let isHeader = document.getElementById("isHeader").checked;
          Papa.parse(reader.result, {
            header: isHeader,
            complete: function(results) {
              createGrid(results.data);
            }
          });
        }
        // readAsTextメソッドでファイルの内容を取得
        reader.readAsText(f, encode_type);
      }

      event.preventDefault();
    }

    // グリッド生成処理
    function createGrid(data, isNew=false) {
      let isHeader = document.getElementById("isHeader").checked;
      let grid = document.getElementById('grid');
      const text = {
        insertANewRowBefore: '上に行を挿入',
        insertANewRowAfter: '下に行を挿入',
        deleteSelectedRows: '選択行の削除',
        copy: 'コピー',
        paste: '貼り付け'
      };

      // 保存ファイル名 jsCsv_yyyyMMddHHmm.csv
      let baseName = 'jsCsv';
      let dt = new Date(Date.now() + 3600000 * 9).toISOString().replace(/[^0-9]/g, '').slice(0, -5);
      if (!isNew) {
        let filename = savePath.name.replace(/^.*[\\\/]/, '');
        baseName = filename.split('.').slice(0, -1).join('.');
      }

      spreadsheet = new jspreadsheet(grid, {
        data: data,
        text: text,
        colHeaders: isHeader ? Object.keys(data[0]) : [],
        defaultColWidth: 100,    // デフォルトの幅を設定
        defaultColAlign: 'left', // デフォルトの配置を左寄せに設定
        allowManualInsertColumn: isNew,
        allowManualInsertRow: isNew,
        wordWrap: true,
        csvFileName: baseName + '_' + dt, 
        toolbar: [
          { type: 'i', content: 'undo', onclick: () => spreadsheet.undo(), tooltip: '元に戻す' },
          { type: 'i', content: 'redo', onclick: () => spreadsheet.redo(), tooltip: 'やり直し' },
          { type: 'i', content: 'save', onclick: () => spreadsheet.download(), tooltip: '保存' },
          { type: 'i', content: 'format_align_left', k: 'text-align', v: 'left', tooltip: '左寄せ' },
          { type: 'i', content:'format_align_center', k:'text-align', v:'center', tooltip: '中央寄せ' },
          { type: 'i', content: 'format_align_right', k: 'text-align', v: 'right', tooltip: '右寄せ' },
          { type: 'i', content: 'format_bold', k: 'font-weight', v: 'bold', tooltip: '太字' },
          { type: 'color', content: 'format_color_text', k: 'color', tooltip: '文字色' },
          { type: 'color', content: 'format_color_fill', k: 'background-color', tooltip: '背景色' },
          { type: 'i', content: 'add', onclick: () => addOrRemoveColumn(true), tooltip: '列を追加'},
          { type: 'i', content: 'remove', onclick: () => addOrRemoveColumn(false), tooltip: '列を削除' },
        ],
        // 読み込み完了後のイベントリスナー
        onload: (event, instance) => { 
          // A1にフォーカス
          instance.updateSelectionFromCoords(0, 0);
          // 末尾空行を削除
          if (!isNew) removeEmptyLastRow(instance); 
        },
      });
    }

    // 列追加/削除処理
    function addOrRemoveColumn(isAdd) {
      let selectedColumns = spreadsheet.getSelectedColumns();
      if (selectedColumns.length > 0) {
        if (isAdd)
          spreadsheet.insertColumn(1, selectedColumns[selectedColumns.length - 1] + 1);
        else
          spreadsheet.deleteColumn(selectedColumns[0]);
      }
    }

    // 最終行が空行なら削除する関数
    function removeEmptyLastRow(instance) {
        let data = instance.getData();
        let lastRowIndex = data.length - 1;
        let lastRow = data[lastRowIndex];
        let isEmptyRow = lastRow.every(function(cell) {
            return cell === '';
        });
        if (isEmptyRow)
          instance.deleteRow(lastRowIndex);
    }
</script>
</body>
</html>

使用方法

Webサイト

オンライン上で使用する場合、下記サイトにアクセスして下さい。

jsCsvViewer.htmlの作成

Qiitaから取得

1.メモ帳などのテキストエディタを開く
2.ソースコードの部分をコピーして貼り付ける
3.ファイル名「jsCsvViewer.html」をUTF-8形式で保存

GitHubから取得

GitHubからダウンロードできます。

画面説明

jsCsvViewer.htmlを開くと次図が表示されます。

画面上部

CSVファイルをドラッグ＆ドロップするか「ファイルを選択」ボタンをクリックするとファイル選択ダイアログが表示される
読込み完了時に「ファイルを選択」ボタンの右側にファイル名が表示される
文字コードには「shift-jis」と「utf-8」が選択できる。※UTF-8 BOM付きは自動判断される
「ヘッダー」チェックボックスはオフなら1行目にヘッダーを表示、オンの場合は列ヘッダーに表示します。※Excel貼り付けた際にヘッダーまでコピーされるように初期値はオフにしています
「再読み込み」ボタンは同じファイルを読み込み直します。文字コードや「ヘッダー」チェックボックスの変更後などに使用する
「全選択コピー」ボタンは表全体をクリップボードにコピーします

Excelへの貼り付けについて
「全選択コピー」ボタンによりクリップボードにコピーされますが、それをそのままExcelへ貼り付けても自動変換されてしまいます。
Excelへ貼り付けする前に、Excelのコーナーヘッドで全選択して書式を「文字列」にした上で貼り付けを行なってください。
その後に指定列を他の書式に変換したい場合、次記事を参考にしてください。
【Excel】文字列で入力された数字を「標準」や「数値」に直したい

Excel 2019 for Mac にて表全体の書式を「文字列」にして貼り付け

アイコン

「元に戻す」ボタンは編集した内容を戻します
「やり直し」ボタンは元に戻した内容を取り消します
「保存」ボタンはカンマ区切りで表示内容を保存します
「右寄せ」ボタンは指定セルや選択範囲を右寄せにします
「中央寄せ」ボタンは指定セルや選択範囲を中央寄せにします
「左寄せ」ボタンは指定セルや選択範囲を左寄せにします
「太字」ボタンは指定セルや選択範囲を太文字にします
「文字色」ボタンは指定セルや選択範囲の文字をカラーピッカーの選択色にします
「背景色」ボタンは指定セルや選択範囲の背景をカラーピッカーの選択色にします
「列追加」ボタンは指定セルの右側に列を追加します
「列削除」ボタンは指定セルの列を削除します

コンテキストメニュー

セルの右クリックメニューとなります。日本語化したところのみ説明します。

「上に行を挿入」メニューは指定セルの上側に空行を追加します
「下に行を挿入」メニューは指定セルの下側に空行を追加します
「選択行の削除」メニューは指定セルの行を削除します
「コピー」メニューは指定セルや選択範囲をクリップボードにコピーします
「貼り付け」メニューは指定セルにクリップボードから貼り付けします

クリアー処理

ブラウザのChrome「このページを再読み込みします」またはEdge「最新の情報に更新」を使用してください。

検索機能

ブラウザの検索(Ctrl+F)を使用してください。

ソート機能

列ヘッダーをダブルクリックすると数値のみでも「文字列」扱いでソートされます。
ソートする場合は、ヘッダーチェックボックスをオンにしておかないとヘッダーもソート対象になってしまいます。

Excelだと列幅を自動調整する際にダブルクリックしますが、この機能はないためダブルクリックするとソートされてしまいます。

数式機能

Jspreadsheetライブラリには標準で数式機能が備わっています。

金額は単価$\times$数量で=C2*D2や=C3*D3、合計は=SUM(E2:E3)のようにセルに埋め込み計算させることが可能です。
使用可能な関数一覧(formulajs ライブラリに依存)

保存機能

Jspreadsheetライブラリの保存機能をそのまま使用しています。
読込みのファイル形式を維持できない(例タブ区切りでもカンマ区切りになるなど)ため、限定的な機能になります。

読込みのファイル名(新規 jsCsv)_年月日時分.csvでダウンロードフォルダに保存されます。
ファイル形式は、UTF-8 BOM付きカンマ区切りの改行CRLFとなります。

読み込みサイズ

サイズが大きい場合はエラーになり読み込めません。
郵便局のCSVの全県データ「utf_ken_all.csv」18.3MiB(124390行)はメモリ不足エラーになりましたが、北海道データ「01HOKKAI.csv」860KiB(8201行)は問題なく読み込めました。

カスタム

jsCsvViewerで修正して欲しいところがあれば、ソースコードもそんなに長くないので修正してみてください。

デフォルトの幅

初期の幅を広げたい場合、下記の値を変更してみて下さい。

defaultColWidth: 100,    // デフォルトの幅を設定

ヘッダーチェックボックスの初期値

ヘッダーチェックボックスを最初からオンにしたい場合、checked属性を追加して下さい。

<input type="checkbox" id="isHeader" name="header" checked />

Jspreadsheetの変更

ChatGPTなどの生成AIで「Jspreadsheetで何々したい」と要望をかくとコードを提示してくれます。但しバージョンの違いによって動作しないコードが生成されます。

最後に

今回は枯れた技術を使ったツールではありますが、世の中的には大きな貢献ができるのではないかと期待しています。

ExcelでWebDriverを使用しないでWebスクレイピングさせる紹介記事を書いています。良かったら読んでみてください。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up