ツール管理へ戻るv1 のソース
<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>ロット条件抽出ツール</title>
<style>
* { box-sizing: border-box; }
body { font-family: "Meiryo", "Yu Gothic", sans-serif; margin: 0; padding: 16px; color: #222; background: #f4f6f9; }
h1 { font-size: 20px; margin: 0 0 12px; }
h2 { font-size: 15px; margin: 0 0 8px; }
#err { background: #fdecea; color: #b71c1c; padding: 8px 12px; margin-bottom: 12px; display: none; white-space: pre-wrap; border-radius: 4px; }
.panel { border: 1px solid #ccd3dd; border-radius: 6px; padding: 12px; margin-bottom: 12px; background: #fff; }
.row { margin: 6px 0; }
.items label, .row label { display: inline-block; margin: 3px 14px 3px 0; }
button { padding: 6px 14px; margin-right: 8px; cursor: pointer; border: 1px solid #4a78c2; background: #4a78c2; color: #fff; border-radius: 4px; }
button.sub { background: #fff; color: #4a78c2; }
button.toggle.on { background: #2e7d32; border-color: #2e7d32; color: #fff; }
button.toggle.off { background: #fff; border-color: #888; color: #555; }
input[type=number] { width: 80px; padding: 3px; }
.summary { margin: 8px 0; font-weight: bold; }
.tblwrap { max-height: 440px; overflow: auto; border: 1px solid #ccd3dd; background: #fff; }
table { border-collapse: collapse; width: 100%; font-size: 12px; }
th, td { border: 1px solid #c8cfd9; padding: 4px 6px; text-align: center; white-space: nowrap; }
th { background: #e8eef7; position: sticky; top: 0; }
td.ng { color: #c62828; font-weight: bold; }
td.left { text-align: left; white-space: normal; }
.bars { margin-top: 12px; }
.bar-row { display: flex; align-items: center; margin: 3px 0; font-size: 12px; }
.bar-label { width: 80px; }
.bar-track { flex: 1; background: #e6ebf2; height: 16px; border-radius: 3px; overflow: hidden; }
.bar-fill { background: #4a78c2; height: 100%; }
.bar-val { width: 50px; text-align: right; }
.note { font-size: 12px; color: #555; }
</style>
</head>
<body>
<h1>ロット条件抽出ツール(不良率・不良項目)</h1>
<div id="err"></div>
<div class="panel">
<h2>1. ファイルを選択</h2>
<input type="file" id="file" accept=".xlsx">
<div class="note" id="fileInfo">xlsxファイルを選択してください(データはこのブラウザ内だけで処理され、外部へ送信されません)。</div>
</div>
<div id="ui" style="display:none">
<div class="panel">
<h2>2. 条件を指定</h2>
<div class="row">
<label><input type="checkbox" id="useRate" checked> 不良率の条件を使う</label>
不良率
<input type="number" id="thr" value="0.5" step="0.1" min="0"> % 以上
</div>
<div class="row">
<b>不良項目</b>(選択した項目の件数が1以上のロット)
<button class="sub" id="allOn" type="button">全選択</button>
<button class="sub" id="allOff" type="button">全解除</button>
<div class="items" id="items"></div>
</div>
<div class="row">
<b>条件の組み合わせ:</b>
<label><input type="radio" name="mode" value="AND" checked> AND(両方に当てはまる)</label>
<label><input type="radio" name="mode" value="OR"> OR(どちらかに当てはまる)</label>
</div>
<div class="row">
<b>N数0の扱い:</b>
<button id="zeroBtn" class="toggle on" type="button">N数0を省く:オン</button>
<span class="note" id="zeroInfo"></span>
</div>
<div class="row">
<button id="run" type="button">抽出する</button>
<button class="sub" id="dlCsv" type="button">抽出結果をCSV出力</button>
</div>
<div class="note">不良項目を何も選ばない場合は、不良率の条件だけで絞り込みます。両方とも使わない場合は、(N数0を省く設定を除き)全ロットを表示します。</div>
</div>
<div class="summary" id="summary"></div>
<div class="tblwrap"><table id="tbl"></table></div>
<div class="panel bars">
<h2>機械別の該当ロット数</h2>
<div id="bars"></div>
</div>
</div>
<script>
(function () {
var allRows = [];
var itemNames = [];
var result = [];
var skipZero = true;
function showErr(e, extra) {
var el = document.getElementById('err');
el.style.display = 'block';
el.textContent = 'エラーが発生しました: ' + (e && e.message ? e.message : e) + (extra ? '\n' + extra : '');
}
function clearErr() { document.getElementById('err').style.display = 'none'; }
function esc(s) {
return String(s == null ? '' : s).replace(/[&<>"]/g, function (c) {
return { '&': '&', '<': '<', '>': '>', '"': '"' }[c];
});
}
/* ---------- xlsx 読み込み(ZIP解凍 + XML解析) ---------- */
function u16(b, o) { return b[o] | (b[o + 1] << 8); }
function u32(b, o) { return (b[o] | (b[o + 1] << 8) | (b[o + 2] << 16) | (b[o + 3] << 24)) >>> 0; }
async function inflateRaw(bytes) {
var ds = new DecompressionStream('deflate-raw');
var stream = new Blob([bytes]).stream().pipeThrough(ds);
var buf = await new Response(stream).arrayBuffer();
return new Uint8Array(buf);
}
async function readZip(buf) {
var b = new Uint8Array(buf);
var eocd = -1;
for (var i = b.length - 22; i >= 0; i--) {
if (b[i] === 0x50 && b[i + 1] === 0x4b && b[i + 2] === 5 && b[i + 3] === 6) { eocd = i; break; }
}
if (eocd < 0) throw new Error('xlsxファイルの形式を認識できません。');
var count = u16(b, eocd + 10);
var cdOff = u32(b, eocd + 16);
var files = {};
var p = cdOff;
for (var n = 0; n < count; n++) {
if (u32(b, p) !== 0x02014b50) break;
var method = u16(b, p + 10);
var csize = u32(b, p + 20);
var nlen = u16(b, p + 28), elen = u16(b, p + 30), clen = u16(b, p + 32);
var lho = u32(b, p + 42);
var name = new TextDecoder().decode(b.subarray(p + 46, p + 46 + nlen));
files[name] = { method: method, csize: csize, lho: lho };
p += 46 + nlen + elen + clen;
}
async function get(name) {
var f = files[name];
if (!f) return null;
var lnl = u16(b, f.lho + 26), lel = u16(b, f.lho + 28);
var start = f.lho + 30 + lnl + lel;
var data = b.subarray(start, start + f.csize);
if (f.method === 0) return new TextDecoder().decode(data);
return new TextDecoder().decode(await inflateRaw(data));
}
return { get: get, names: Object.keys(files) };
}
function parseXml(s) { return new DOMParser().parseFromString(s, 'application/xml'); }
function colIndex(ref) {
var m = /^([A-Z]+)/.exec(ref);
var s = m ? m[1] : 'A', n = 0;
for (var i = 0; i < s.length; i++) n = n * 26 + (s.charCodeAt(i) - 64);
return n - 1;
}
async function loadWorkbook(buf) {
var zip = await readZip(buf);
var wbXml = parseXml(await zip.get('xl/workbook.xml'));
var relXml = parseXml(await zip.get('xl/_rels/workbook.xml.rels'));
var rels = {};
Array.prototype.forEach.call(relXml.getElementsByTagName('Relationship'), function (r) {
rels[r.getAttribute('Id')] = r.getAttribute('Target');
});
var shared = [];
var ssText = await zip.get('xl/sharedStrings.xml');
if (ssText) {
var ss = parseXml(ssText);
Array.prototype.forEach.call(ss.getElementsByTagName('si'), function (si) {
var t = '';
Array.prototype.forEach.call(si.getElementsByTagName('t'), function (x) { t += x.textContent; });
shared.push(t);
});
}
var sheets = [];
var sheetEls = wbXml.getElementsByTagName('sheet');
for (var i = 0; i < sheetEls.length; i++) {
var el = sheetEls[i];
var name = el.getAttribute('name');
var rid = el.getAttribute('r:id') || el.getAttributeNS('http://schemas.openxmlformats.org/officeDocument/2006/relationships', 'id');
var target = rels[rid];
if (!target) continue;
var path = target.charAt(0) === '/' ? target.slice(1) : 'xl/' + target.replace(/^\.\//, '');
var xml = await zip.get(path);
if (!xml) continue;
sheets.push({ name: name, data: parseSheetXml(xml, shared) });
}
return sheets;
}
function parseSheetXml(xml, shared) {
var doc = parseXml(xml);
var rows = [];
Array.prototype.forEach.call(doc.getElementsByTagName('row'), function (rowEl) {
var rIdx = parseInt(rowEl.getAttribute('r'), 10) - 1;
var arr = [];
Array.prototype.forEach.call(rowEl.getElementsByTagName('c'), function (c) {
var ci = colIndex(c.getAttribute('r'));
var t = c.getAttribute('t');
var vEl = c.getElementsByTagName('v')[0];
var val = '';
if (t === 'inlineStr') {
val = c.textContent;
} else if (vEl) {
var raw = vEl.textContent;
if (t === 's') val = shared[parseInt(raw, 10)] || '';
else if (t === 'str' || t === 'e') val = raw;
else if (t === 'b') val = raw === '1';
else { var num = Number(raw); val = isNaN(num) ? raw : num; }
}
arr[ci] = val;
});
rows[rIdx] = arr;
});
for (var i = 0; i < rows.length; i++) if (!rows[i]) rows[i] = [];
return rows;
}
/* ---------- データ解析 ---------- */
function norm(s) { return String(s == null ? '' : s).replace(/[\s\u3000]/g, ''); }
function dateStr(v) {
if (v === '' || v == null) return '';
if (typeof v === 'number') {
var d = new Date(Math.round((v - 25569) * 86400000));
return d.getUTCFullYear() + '/' + ('0' + (d.getUTCMonth() + 1)).slice(-2) + '/' + ('0' + d.getUTCDate()).slice(-2);
}
return String(v);
}
function findHeader(data) {
for (var r = 0; r < data.length; r++) {
var row = data[r] || [];
for (var c = 0; c < row.length; c++) {
if (norm(row[c]) === '検査日') return { r: r, c: c };
}
}
return null;
}
function findCol(head, key) {
var c;
for (c = 0; c < head.length; c++) if (norm(head[c]) === key) return c;
for (c = 0; c < head.length; c++) if (norm(head[c]).indexOf(key) >= 0) return c;
return -1;
}
function parseSheet(name, data) {
var h = findHeader(data);
if (!h) return null;
var head = data[h.r];
var cDate = findCol(head, '検査日');
var cLot = findCol(head, 'ロット№');
var cCut = findCol(head, '切落日');
var cN = findCol(head, 'N');
var cDef = findCol(head, '不良数');
var cRate = findCol(head, '不良率');
var cJudge = findCol(head, '判定');
var cContent = findCol(head, '不良内容');
var cNote = findCol(head, '備考');
if (cN < 0) cN = 3;
if (cDate < 0 || cDef < 0 || cContent < 0) return null;
var nameRow = data[h.r + 1] || [];
var items = [];
var endCol = cNote > cContent ? cNote : nameRow.length;
for (var c = cContent; c < endCol; c++) {
var nm = String(nameRow[c] == null ? '' : nameRow[c]).trim();
if (nm !== '') items.push({ col: c, name: nm });
}
var machine = name;
for (var r = 0; r < Math.min(6, data.length); r++) {
var rr = data[r] || [];
for (var cc = 0; cc < rr.length; cc++) {
if (norm(rr[cc]) === '機械№') {
for (var k = cc + 1; k <= cc + 3; k++) {
if (rr[k] !== undefined && rr[k] !== '') { machine = String(rr[k]); break; }
}
}
}
}
var out = [];
for (var i = h.r + 2; i < data.length; i++) {
var row = data[i] || [];
var d = row[cDate], N = row[cN];
if (d === undefined || d === '') continue;
var Nn = Number(N) || 0;
var defNum = Number(row[cDef]) || 0;
var rate = Number(row[cRate]);
if (row[cRate] === undefined || row[cRate] === '' || isNaN(rate)) rate = Nn > 0 ? defNum / Nn : 0;
var counts = {};
items.forEach(function (it) {
counts[it.name] = (counts[it.name] || 0) + (Number(row[it.col]) || 0);
});
out.push({
machine: machine,
date: dateStr(d),
lot: row[cLot] == null ? '' : row[cLot],
cut: dateStr(cCut >= 0 ? row[cCut] : ''),
N: Nn,
def: defNum,
rate: rate,
judge: cJudge >= 0 && row[cJudge] != null ? row[cJudge] : '',
counts: counts,
note: cNote >= 0 && row[cNote] != null ? row[cNote] : ''
});
}
return { rows: out, items: items.map(function (x) { return x.name; }) };
}
/* ---------- 画面処理 ---------- */
function buildItems() {
var box = document.getElementById('items');
box.innerHTML = '';
itemNames.forEach(function (nm) {
var l = document.createElement('label');
var cb = document.createElement('input');
cb.type = 'checkbox'; cb.className = 'itemchk'; cb.value = nm;
l.appendChild(cb);
l.appendChild(document.createTextNode(' ' + nm));
box.appendChild(l);
});
}
function selected() {
return Array.prototype.map.call(document.querySelectorAll('.itemchk:checked'), function (e) { return e.value; });
}
function updateZeroBtn() {
var btn = document.getElementById('zeroBtn');
btn.textContent = 'N数0を省く:' + (skipZero ? 'オン' : 'オフ');
btn.className = 'toggle ' + (skipZero ? 'on' : 'off');
var zeroCount = allRows.filter(function (r) { return r.N === 0; }).length;
document.getElementById('zeroInfo').textContent = 'N数0のロット: 全体で ' + zeroCount + ' 件';
}
function run() {
var thr = (Number(document.getElementById('thr').value) || 0) / 100;
var useRate = document.getElementById('useRate').checked;
var sel = selected();
var mode = document.querySelector('input[name=mode]:checked').value;
result = allRows.filter(function (r) {
if (skipZero && r.N === 0) return false;
var okRate = useRate ? r.rate >= thr - 1e-12 : null;
var okItem = sel.length > 0 ? sel.some(function (nm) { return (r.counts[nm] || 0) > 0; }) : null;
if (okRate === null && okItem === null) return true;
if (okRate === null) return okItem;
if (okItem === null) return okRate;
return mode === 'AND' ? (okRate && okItem) : (okRate || okItem);
});
updateZeroBtn();
render();
}
function render() {
var html = '<thead><tr><th>機械№</th><th>検査日</th><th>ロット№</th><th>N</th><th>不良数</th><th>不良率</th><th>判定</th>';
itemNames.forEach(function (nm) { html += '<th>' + esc(nm) + '</th>'; });
html += '<th>備考</th></tr></thead><tbody>';
result.forEach(function (r) {
html += '<tr><td>' + esc(r.machine) + '</td><td>' + esc(r.date) + '</td><td>' + esc(r.lot) + '</td><td>' + r.N +
'</td><td>' + r.def + '</td><td>' + (r.rate * 100).toFixed(2) + '%</td><td class="' +
(String(r.judge).indexOf('×') >= 0 ? 'ng' : '') + '">' + esc(r.judge) + '</td>';
itemNames.forEach(function (nm) { html += '<td>' + (r.counts[nm] || 0) + '</td>'; });
html += '<td class="left">' + esc(r.note) + '</td></tr>';
});
html += '</tbody>';
document.getElementById('tbl').innerHTML = html;
var base = skipZero ? allRows.filter(function (r) { return r.N !== 0; }).length : allRows.length;
document.getElementById('summary').textContent =
'対象ロット ' + base + ' 件中、該当 ' + result.length + ' 件' + (skipZero ? '(N数0を除外中)' : '');
var byM = {};
result.forEach(function (r) { byM[r.machine] = (byM[r.machine] || 0) + 1; });
var keys = Object.keys(byM).sort();
var max = 0;
keys.forEach(function (k) { if (byM[k] > max) max = byM[k]; });
var bars = '';
keys.forEach(function (k) {
bars += '<div class="bar-row"><div class="bar-label">' + esc(k) + '</div><div class="bar-track"><div class="bar-fill" style="width:' +
(max ? (byM[k] / max * 100) : 0) + '%"></div></div><div class="bar-val">' + byM[k] + '件</div></div>';
});
document.getElementById('bars').innerHTML = bars || '<div class="note">該当するロットはありません。</div>';
}
function csvCell(v) {
var s = String(v == null ? '' : v);
return /[",\n]/.test(s) ? '"' + s.replace(/"/g, '""') + '"' : s;
}
function downloadCsv() {
var head = ['機械№', '検査日', 'ロット№', 'N', '不良数', '不良率(%)', '判定'].concat(itemNames).concat(['備考']);
var lines = [head.map(csvCell).join(',')];
result.forEach(function (r) {
var line = [r.machine, r.date, r.lot, r.N, r.def, (r.rate * 100).toFixed(2), r.judge]
.concat(itemNames.map(function (nm) { return r.counts[nm] || 0; })).concat([r.note]);
lines.push(line.map(csvCell).join(','));
});
var blob = new Blob(['\uFEFF' + lines.join('\r\n')], { type: 'text/csv;charset=utf-8' });
var a = document.createElement('a');
a.href = URL.createObjectURL(blob);
a.download = '抽出結果.csv';
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
setTimeout(function () { URL.revokeObjectURL(a.href); }, 1000);
}
async function onFile(ev) {
clearErr();
var f = ev.target.files && ev.target.files[0];
if (!f) return;
document.getElementById('fileInfo').textContent = '読み込み中: ' + f.name;
try {
if (typeof DecompressionStream === 'undefined') {
throw new Error('お使いのブラウザは必要な機能(DecompressionStream)に対応していません。最新のChromeまたはEdgeでお試しください。');
}
var buf = await f.arrayBuffer();
var sheets = await loadWorkbook(buf);
allRows = [];
var nameSet = {}, order = [], used = 0;
sheets.forEach(function (s) {
if (s.name === 'まとめ') return;
var p = parseSheet(s.name, s.data);
if (!p) return;
used++;
allRows = allRows.concat(p.rows);
p.items.forEach(function (nm) { if (!nameSet[nm]) { nameSet[nm] = true; order.push(nm); } });
});
itemNames = order;
document.getElementById('fileInfo').textContent =
f.name + ' を読み込みました(機械シート ' + used + ' 枚、ロット ' + allRows.length + ' 件)。';
buildItems();
document.getElementById('ui').style.display = 'block';
run();
} catch (e) {
showErr(e, 'ファイルの読み込みに失敗しました。');
}
}
document.getElementById('file').addEventListener('change', onFile);
document.getElementById('run').addEventListener('click', function () { try { run(); } catch (e) { showErr(e); } });
document.getElementById('dlCsv').addEventListener('click', function () { try { downloadCsv(); } catch (e) { showErr(e); } });
document.getElementById('zeroBtn').addEventListener('click', function () {
try { skipZero = !skipZero; run(); } catch (e) { showErr(e); }
});
document.getElementById('allOn').addEventListener('click', function () {
Array.prototype.forEach.call(document.querySelectorAll('.itemchk'), function (e) { e.checked = true; });
});
document.getElementById('allOff').addEventListener('click', function () {
Array.prototype.forEach.call(document.querySelectorAll('.itemchk'), function (e) { e.checked = false; });
});
})();
</script>
</body>
</html>