データベースとテーブルの作成

データベースとテーブルの作成は以下のようにします。

-- データベースの作成
CREATE DATABASE mydb;

-- テーブルの作成
CREATE TABLE spreadsheet_cells (
  id INT AUTO_INCREMENT PRIMARY KEY,
  file_id INT NOT NULL,
  row_index INT NOT NULL,
  col_index INT NOT NULL,
  value VARCHAR(255)
);

-- 1行
CREATE TABLE spreadsheet_cells (id INT AUTO_INCREMENT PRIMARY KEY, file_id INT NOT NULL, row_index INT NOT NULL, col_index INT NOT NULL, value VARCHAR(255));

file_id：CSVやシートを区別するID
row_index：行番号
col_index：列番号
value：セルの値（文字列でも数値でも入る）

コード

アプリのスケルトンはExpress Generatorで作成します。

app.jsのコードを以下のように変更します。

app.js

app.use(express.urlencoded({ extended: true }));

データベースに登録するビューを以下のようにします。

myapp/views/db/create.ejs

<!DOCTYPE html>
<html lang="ja">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <link rel="stylesheet" href="/stylesheets/chart.css">
  <script src="/javascripts/chart.js"></script>
  <title>Document</title>
</head>
<body>
  <h1>DB CREATE PAGE</h1>
  <form id="sheetForm" action="/db/create" method="post">
  <table id="sheetTable">
<tbody>
  <tr>
    <td><input type="text" name="rows[0][0]"></td>
    <td><input type="text" name="rows[0][1]"></td>
  </tr>
  <tr>
    <td><input type="text" name="rows[1][0]"></td>
    <td><input type="text" name="rows[1][1]"></td>
  </tr>
</tbody>
  </table>

  <div style="margin-top:10px;">
<button type="button" id="addRowAbove">行を上に追加</button>
<button type="button" id="addRowBelow">行を下に追加</button>
<button type="button" id="addColLeft">列を左に追加</button>
<button type="button" id="addColRight">列を右に追加</button>
<button type="button" id="delRow">行削除</button>
<button type="button" id="delCol">列削除</button>
  </div>

  <input type="hidden" name="file_id" value="1">
  <input type="submit">
</form>
</body>
</html>

データベースに登録されたデータを表示するには、以下のようにします。

myapp/views/db/view.ejs

<!DOCTYPE html>
<html>
<head>
  <title>スプレッドシート表示</title>
  <style>
table { border-collapse: collapse; }
td { border: 1px solid #ccc; padding: 5px; width: 80px; text-align: center; }
  </style>
</head>
<body>
  <h1>ファイルID <%= fileId %> の内容</h1>
  <table>
<tbody>
  <% sheet.forEach(function(row) { %>
    <tr>
      <% row.forEach(function(cell) { %>
        <td><%= cell %></td>
      <% }) %>
    </tr>
  <% }) %>
</tbody>
  </table>
</body>
</html>

ルーティングは以下のようにします。

myapp/routes/db.js

var express = require('express');
var router = express.Router();
const mysql = require('mysql2/promise');

const conf = {
  host: "localhost",
  user: "root",
  password: "password",
  database: "mydb",
  namedPlaceholders: true,
  waitForConnections: true,
  connectionLimit: 100,
  queueLimit: 0,
  maxIdle: 10,
  charset: 'utf8mb4'
}

const pool = mysql.createPool(conf);

router.get('/create', function(req, res, next) {
  res.render('db/create');
});

router.post('/create', async function(req, res, next) {
  const fileId = req.body.file_id; // ファイルID
  const rows = req.body.rows;      // [[1,2,3,4], [5,6,7,8]]
  const values = [];
  rows.forEach((row, rowIndex) => {
row.forEach((cell, colIndex) => {
  values.push([fileId, rowIndex, colIndex, cell || null]);
});
  });

  try {
const conn = await pool.getConnection();
await conn.query("DELETE FROM spreadsheet_cells WHERE file_id = ?", [fileId]);

const sql = `INSERT INTO spreadsheet_cells (file_id, row_index, col_index, value) VALUES ?`;
await conn.query(sql, [values]);
conn.release();

res.render('db/create');
  } catch (err) {
console.error(err);
res.status(500).send("エラーが発生しました");
  }
})

router.get("/view/:fileId", async (req, res) => {
  const fileId = req.params.fileId;

  const conn = await pool.getConnection();
  const [rows] = await conn.query(
"SELECT row_index, col_index, value FROM spreadsheet_cells WHERE file_id = ? ORDER BY row_index, col_index",
[fileId]
  );
  conn.release();

  // セルを2次元配列に変換
  let sheet = [];
  rows.forEach(cell => {
if (!sheet[cell.row_index]) {
  sheet[cell.row_index] = [];
}
sheet[cell.row_index][cell.col_index] = cell.value || "";
  });

  res.render("db/view", { sheet, fileId });
});

module.exports = router;