LoginSignup
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@KickDrop2020

Javascriptでエクセルを読み込んでグラフ化する

Posted at

本記事のねらい

Javascriptを使ってエクセルを読み込んでグラフ化する、までの情報はGoogleで検索すれば方法が書かれた記事をいくつ見つけることができました。しかし、グラフ化してY軸の範囲を変更すればグラフが瞬時に切り替わる、というコードについて触れている記事は見つかりませんでした。そのためこの記事で１例をアップすることにします

コードのポイント

よくあるコードの書き方

main.js
document.getElementById('fileInput').addEventListener('change', handleFile);

function handleFile(event) {
    const file = event.target.files[0];
    const reader = new FileReader();
    
    reader.onload = function (e) {
        const data = new Uint8Array(e.target.result);
        const workbook = XLSX.read(data, { type: 'array' });
    
        // "XXX"という名前のシートを取得
        const sheetName = "XXX";
        const sheet = workbook.Sheets[sheetName];

        //＊＊＊＊＊＊＊データ処理＊＊＊＊＊＊＊
    
        // 棒グラフを表示
        displayChart(a1Count, b3Count);
    };

reader.readAsArrayBuffer(file);
}

このコードで確かにエクセルファイルを読み込むことはできるが、読み込んだ後Y軸の値を変えることはできない。
なので以下２点を修正する。

  • 「～.addEventListener('change', handleFile)」「function handleFile(event)～」を使わない
  • 「myChart.destroy()」をコードに追加する
main.js
const result = [];
let fileInput = document.getElementById('fileInput');
let fileReader = new FileReader();

fileInput.onchange = () => {
    let file = fileInput.files[0];
    fileReader.readAsArrayBuffer(file);
};

fileReader.onload = () => {
    const data = new Uint8Array(fileReader.result);
    const workbook = XLSX.read(data, { type: 'array' });

     //＊＊＊＊＊＊＊データ処理＊＊＊＊＊＊＊

    if (myChart) {
        myChart.destroy();
    }
    // 棒グラフを表示
    displayBarChart(KeyCount, keycodes, 0, 40);
}

これでエクセルファイルを読み込んだ後に、Y軸の範囲を変更すると瞬時にグラフが切り替わります。

実際に試しに作成したコードを公開しておきます

test.html
<!DOCTYPE html>
<html lang="ja">
    <head>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>自動集計アプリ</title>
        <link rel="stylesheet" href="style.css">
    </head>
    <body>
        <header>
            <div class="logo">
                <h1>自動集計ツール</h1>
            </div>
        </header>
        <div class="container">
            <p>エクセルの「XXX」「YYY」「ZZZ」シートそれぞれに"A-1","A-2","A-3","B-1","B-2","B-3","B-4","B-5"の文字がいくつあるかを算出します</p>
            <div class="container01">
                <label>表示するファイルを選択してください</label>
                <div class="container02">
                    <input type="file" id="fileInput" accept=".xlsx, .xls"/>
                </div>
            </div>
            <div class="container01">
                <label>表示するY軸の範囲を選択してください</label>
                <div>
                    <input type="number" id="Ymin" value="0" step="1">
                    〜
                    <input type="number" id="Ymax" value="40" step="1">
                    <button type="button" id="display">表示</button>
                </div>
            </div>
            
            <div class="canvas-container">
                <canvas id="myChart1"></canvas>
            </div>
            <div class="canvas-container">
                <canvas id="myChart2"></canvas>
            </div>
            <div class="canvas-container">
                <canvas id="myChart3"></canvas>
            </div>
            
        </div>
    
        <script src="https://cdn.jsdelivr.net/npm/xlsx@0.18.5/dist/xlsx.full.min.js"></script>

        <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
        <script src="script.js"></script>
    </body>
</html>
style.css
.logo {
    background-color: blue;
    color: #fff;
    text-shadow: #000 2px 2px 0.2rem;
    height: 3.5rem;
}

body {
    background-color: skyblue;
    color: blue;
    font-family: 'Arial', sans-serif;
    text-align: center;
}

.container {
    margin-top: 50px;
}
.container01 {
    color: black;
}
.container02 {
    border-color: black;
}

canvas {
    background-color: #fff;
    border: ridge #ccc;
    border-width: 6px;
}

input[type="file"] {
    margin-bottom: 20px;
    color: blue;
    font-size: 20px;
}

.canvas-container {
    margin: 0rem auto;
    position: relative;
    width:  65rem;
    height: 33rem;
    overflow: hidden;
}
script.js
const ctx1 = document.getElementById('myChart1').getContext('2d');
let myChart1 = new Chart(ctx1);
const ctx2 = document.getElementById('myChart2').getContext('2d');
let myChart2 = new Chart(ctx2);
const ctx3 = document.getElementById('myChart3').getContext('2d');
let myChart3 = new Chart(ctx3);

const result = [];
let fileInput = document.getElementById('fileInput');
let fileReader = new FileReader();

fileInput.onchange = () => {
    let file = fileInput.files[0];
    fileReader.readAsArrayBuffer(file);
};

fileReader.onload = () => {
    const data = new Uint8Array(fileReader.result);
    const workbook = XLSX.read(data, { type: 'array' });
    console.log(data);
    console.log(workbook);
    const transpose = a => a[0].map((_, c) => a.map(r => r[c]));

    // "XXX"という名前のシートを取得
    const sheetNames = ["XXX","YYY","ZZZ"];
    const keycodes = ["A-1","A-2","A-3","B-1","B-2","B-3","B-4","B-5"];
    let KeyCount = Array(keycodes.length).fill(0);
    let KeyCount_Bysheet= new Array(sheetNames.length); //要素数5の配列(array)を作成
    for(let y = 0; y < sheetNames.length; y++) {
        KeyCount_Bysheet[y] = new Array(keycodes.length).fill(0); //配列(array)の各要素に対して、要素数5の配列を作成し、0で初期化
    }

    for (let j = 0; j<sheetNames.length; j++) {
        const sheet = workbook.Sheets[sheetNames[j]];
        if (!sheet) {
            console.warn(`Sheet ${sheetNames[j]} is not found in the workbook.`);
            continue; // シートが存在しない場合は次のループに進む
        }
        const tmp_range = sheet['!ref'];

        // セルの範囲 B2～K6 を取得し、その中から"A-1"と"B-3"をカウントする
        const range = XLSX.utils.decode_range(tmp_range);

        for (let R = range.s.r; R <= range.e.r; ++R) {
            for (let C = range.s.c; C <= range.e.c; ++C) {
                const cellAddress = { c: C, r: R };
                const cellRef = XLSX.utils.encode_cell(cellAddress);
                const cellValue = sheet[cellRef] ? sheet[cellRef].v : null;

                for (let i = 0; i<keycodes.length; i++) {
                    if (cellValue === keycodes[i]) {
                        KeyCount[i]++;
                        KeyCount_Bysheet[j][i]++;
                        break;
                    }
                }
            }
        }

    }
    if (myChart1) {
        myChart1.destroy();
        myChart2.destroy();
        myChart3.destroy();
    }
    // 棒グラフを表示
    displayBarChart(KeyCount, keycodes, 0, 40);
    console.log(KeyCount_Bysheet);
    displayStackChart1(KeyCount_Bysheet, keycodes, sheetNames, 0, 40);
    const KeyCount_Bysheet_t = transpose(KeyCount_Bysheet);
    displayStackChart2(KeyCount_Bysheet_t, keycodes, sheetNames, 0, 40);
}

// 表示期間を指定した時の処理
let displayclick = document.getElementById('display');

displayclick.onclick = () => {
    const data = new Uint8Array(fileReader.result);
    const workbook = XLSX.read(data, { type: 'array' });
    const transpose = a => a[0].map((_, c) => a.map(r => r[c]));

    // "XXX"という名前のシートを取得
    const sheetNames = ["XXX","YYY","ZZZ"];
    const keycodes = ["A-1","A-2","A-3","B-1","B-2","B-3","B-4","B-5"];
    let KeyCount = Array(keycodes.length).fill(0);
    let KeyCount_Bysheet= new Array(sheetNames.length); //要素数5の配列(array)を作成
    for(let y = 0; y < sheetNames.length; y++) {
        KeyCount_Bysheet[y] = new Array(keycodes.length).fill(0); //配列(array)の各要素に対して、要素数5の配列を作成し、0で初期化
    }

    for (let j = 0; j<sheetNames.length; j++) {
        const sheet = workbook.Sheets[sheetNames[j]];
        if (!sheet) {
            console.warn(`Sheet ${sheetNames[j]} is not found in the workbook.`);
            continue; // シートが存在しない場合は次のループに進む
        }
        const tmp_range = sheet['!ref'];

        // セルの範囲 B2～K6 を取得し、その中から"A-1"と"B-3"をカウントする
        const range = XLSX.utils.decode_range(tmp_range);

        for (let R = range.s.r; R <= range.e.r; ++R) {
            for (let C = range.s.c; C <= range.e.c; ++C) {
                const cellAddress = { c: C, r: R };
                const cellRef = XLSX.utils.encode_cell(cellAddress);
                const cellValue = sheet[cellRef] ? sheet[cellRef].v : null;

                for (let i = 0; i<keycodes.length; i++) {
                    if (cellValue === keycodes[i]) {
                        KeyCount[i]++;
                        KeyCount_Bysheet[j][i]++;
                        break;
                    }
                }
            }
        }

    }
    let Ymin = Math.trunc(document.getElementById("Ymin").value);
    let Ymax = Math.trunc(document.getElementById("Ymax").value);
    console.log(Ymin);
    console.log(Ymax);
    if (myChart1) {
        myChart1.destroy();
        myChart2.destroy();
        myChart3.destroy();
    }
    // 棒グラフを表示
    displayBarChart(KeyCount, keycodes, Ymin, Ymax);
    console.log(KeyCount_Bysheet);
    displayStackChart1(KeyCount_Bysheet, keycodes, sheetNames, Ymin, Ymax);
    const KeyCount_Bysheet_t = transpose(KeyCount_Bysheet);
    displayStackChart2(KeyCount_Bysheet_t, keycodes, sheetNames, Ymin, Ymax);

}


// Chart.jsを使って棒グラフを描画
function displayBarChart(KeyCount, keycodes, Ymin, Ymax) {
    myChart1 = new Chart(ctx1, {
        type: 'bar',
        data: {
            labels: keycodes,
            datasets: [{
                label: '合計',
                data: KeyCount,
                borderWidth: 2
            }]
        },
        options: {
            plugins: {
                title: {
                    display: true,
                    text: 'コード別合計',
                    font: {
                        size: 18,
                    }
                },
                legend: {
                    position: 'left'
                }
            },
            y: {
                min: Ymin, //Y軸の最小値
                max: Ymax, //Y軸の最大値
            },
            scales: {
                x: {
                    ticks: {
                        color: "blue",
                        font: {
                            size: 18
                        }
                    }
                },
                y: {
                    ticks: {
                        font: {
                            size: 18
                        },
                        stepSize: Math.trunc(5)
                    },
                    title: {
                        display: true,
                        text: '頻度（回数）',//Y軸のﾗﾍﾞﾙ
                        font: {
                            size: 18
                        }
                    },
                    beginAtZero: true,
                }
            }
        },
    });
}

function displayStackChart1(KeyCount_Bysheet, keycodes, sheetNames, Ymin, Ymax) {
    myChart2 = new Chart(ctx2, {
        type: 'bar',
        data: {
            labels: keycodes,
            datasets: [
                {
                    label: sheetNames[0],
                    data: KeyCount_Bysheet[0],
                    borderWidth: 2
                },
                {
                    label: sheetNames[1],
                    data: KeyCount_Bysheet[1],
                    borderWidth: 2
                },
                {
                    label: sheetNames[2],
                    data: KeyCount_Bysheet[2],
                    borderWidth: 2
                }
            ]
        },
        options: {
            plugins: {
                datalabels: {
                    font: {
                        size: 14
                    }
                },
                title: {
                    display: true,
                    text: 'コード別内訳',
                    font: {
                        size: 18,
                    }
                },
                legend: {
                    position: 'left'
                }
            },
            y: {
                min: Ymin, //Y軸の最小値
                max: Ymax, //Y軸の最大値
            },
            scales: {
                x: {
                    ticks: {
                        color: "blue",
                        font: {
                            size: 18
                        }
                    },
                    stacked: true,
                },
                y: {
                    ticks: {
                        font: {
                            size: 18
                        },
                        stepSize: Math.trunc(5)
                    },
                    title: {
                        display: true,
                        text: '頻度（回数）',//Y軸のﾗﾍﾞﾙ
                        font: {
                            size: 18
                        }
                    },
                    stacked: true,
                    beginAtZero: true
                }
            }
        }
    });
}

function displayStackChart2(KeyCount_Bysheet, keycodes, sheetNames, Ymin, Ymax) {
    myChart3 = new Chart(ctx3, {
        type: 'bar',
        data: {
            labels: sheetNames,
            datasets: [
                {
                    label: keycodes[0],
                    data: KeyCount_Bysheet[0],
                    borderWidth: 2
                },
                {
                    label: keycodes[1],
                    data: KeyCount_Bysheet[1],
                    borderWidth: 2
                },
                {
                    label: keycodes[2],
                    data: KeyCount_Bysheet[2],
                    borderWidth: 2
                },
                {
                    label: keycodes[3],
                    data: KeyCount_Bysheet[3],
                    borderWidth: 2
                },
                {
                    label: keycodes[4],
                    data: KeyCount_Bysheet[4],
                    borderWidth: 2
                },
                {
                    label: keycodes[5],
                    data: KeyCount_Bysheet[5],
                    borderWidth: 2
                },
                {
                    label: keycodes[6],
                    data: KeyCount_Bysheet[6],
                    borderWidth: 2
                },
                {
                    label: keycodes[7],
                    data: KeyCount_Bysheet[7],
                    borderWidth: 2
                }
            ]
        },
        options: {
            plugins: {
                title: {
                    display: true,
                    text: 'シート別内訳',
                    font: {
                        size: 18,
                    }
                },
                legend: {
                    position: 'left'
                }
            },
            y: {
                min: Ymin, //Y軸の最小値
                max: Ymax, //Y軸の最大値
            },
            scales: {
                x: {
                    ticks: {
                        color: "blue",
                        font: {
                            size: 18
                        }
                    },
                    stacked: true,
                },
                y: {
                    ticks: {
                        font: {
                            size: 18
                        },
                        stepSize: Math.trunc(5)
                    },
                    title: {
                        display: true,
                        text: '頻度（回数）',//Y軸のﾗﾍﾞﾙ
                        font: {
                            size: 18
                        }
                    },
                    stacked: true,
                    beginAtZero: true
                }
            }
        }
    });
}

参考までに、GitHubのリンクを載せておきます。
GitHub - 自動計算アプリ

2
1
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?