-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathindex.html
More file actions
352 lines (289 loc) · 15.5 KB
/
Copy pathindex.html
File metadata and controls
352 lines (289 loc) · 15.5 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
```html
<!DOCTYPE html>
<html lang="zh-TW">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Excel 製品數量統計工具</title>
<script src="https://cdn.tailwindcss.com"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
<style>
::-webkit-scrollbar { width: 8px; height: 8px; }
::-webkit-scrollbar-track { background: #f1f1f1; border-radius: 4px; }
::-webkit-scrollbar-thumb { background: #cbd5e1; border-radius: 4px; }
::-webkit-scrollbar-thumb:hover { background: #94a3b8; }
.loading-overlay {
display: none;
position: absolute;
inset: 0;
background: rgba(255, 255, 255, 0.8);
justify-content: center;
align-items: center;
z-index: 10;
backdrop-filter: blur(2px);
}
</style>
</head>
<body class="bg-slate-50 min-h-screen text-slate-800 p-4 md:p-8">
<div class="max-w-4xl mx-auto bg-white rounded-2xl shadow-sm border border-slate-200 overflow-hidden relative">
<div id="loading" class="loading-overlay rounded-2xl">
<div class="flex flex-col items-center">
<svg class="animate-spin -ml-1 mr-3 h-8 w-8 text-indigo-600 mb-2" xmlns="http://www.w3.org/2000/svg" fill="none" viewBox="0 0 24 24">
<circle class="opacity-25" cx="12" cy="12" r="10" stroke="currentColor" stroke-width="4"></circle>
<path class="opacity-75" fill="currentColor" d="M4 12a8 8 0 018-8V0C5.373 0 0 5.373 0 12h4zm2 5.291A7.962 7.962 0 014 12H0c0 3.042 1.135 5.824 3 7.938l3-2.647z"></path>
</svg>
<span class="text-sm font-medium text-slate-600">處理中...</span>
</div>
</div>
<div class="bg-emerald-600 p-6 text-white">
<h1 class="text-2xl font-bold flex items-center gap-2">
<svg xmlns="http://www.w3.org/2000/svg" class="h-7 w-7" fill="none" viewBox="0 0 24 24" stroke="currentColor">
<path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M9 17v-2m3 2v-4m3 4v-6m2 10H7a2 2 0 01-2-2V5a2 2 0 012-2h5.586a1 1 0 01.707.293l5.414 5.414a1 1 0 01.293.707V19a2 2 0 01-2 2z" />
</svg>
Excel 製品數量自動統計工具
</h1>
<p class="mt-2 text-emerald-100 opacity-90 text-sm">
邏輯：換行拆分出每一項後，<b>沒有標註 `*` 算作 1 個；有標註 `*N` 則計算為 N 個</b>。
</p>
</div>
<div class="p-6 border-b border-slate-100 bg-slate-50/50">
<div class="flex flex-col md:flex-row gap-4 items-end">
<div class="flex-1 w-full">
<label class="block text-sm font-medium text-slate-700 mb-2">1. 選擇包含您訂單資料的 Excel 檔案</label>
<input type="file" id="excel-file" accept=".xlsx, .xls, .csv" class="block w-full text-sm text-slate-500 file:mr-4 file:py-2.5 file:px-4 file:rounded-lg file:border-0 file:text-sm file:font-semibold file:bg-emerald-50 file:text-emerald-700 hover:file:bg-emerald-100 transition-all cursor-pointer border border-slate-200 rounded-lg bg-white box-border"/>
</div>
<div id="sheet-selector-container" class="hidden w-full md:w-1/3">
<label class="block text-sm font-medium text-slate-700 mb-2">2. 選擇工作表</label>
<select id="sheet-select" class="block w-full rounded-lg border-slate-300 border bg-white px-4 py-2.5 text-sm focus:border-emerald-500 focus:ring-emerald-500 shadow-sm outline-none transition-all">
</select>
</div>
</div>
<div id="error-message" class="hidden mt-4 p-3 bg-red-50 text-red-700 text-sm rounded-lg border border-red-100"></div>
</div>
<div class="p-0">
<div id="empty-state" class="p-12 text-center text-slate-400">
<svg xmlns="http://www.w3.org/2000/svg" class="h-16 w-16 mx-auto mb-4 text-slate-300" fill="none" viewBox="0 0 24 24" stroke="currentColor">
<path stroke-linecap="round" stroke-linejoin="round" stroke-width="1.5" d="M9 12h6m-6 4h6m2 5H7a2 2 0 01-2-2V5a2 2 0 012-2h5.586a1 1 0 01.707.293l5.414 5.414a1 1 0 01.293.707V19a2 2 0 01-2 2z" />
</svg>
<p>請於上方上傳 Excel (.xlsx) 檔案</p>
</div>
<div id="results-container" class="hidden">
<div class="flex flex-col sm:flex-row justify-between items-center p-4 bg-slate-50 border-b border-slate-200 gap-4">
<div class="text-sm font-medium text-slate-700" id="summary-text"></div>
<button id="download-btn" class="w-full sm:w-auto flex justify-center items-center gap-2 bg-emerald-600 hover:bg-emerald-700 text-white px-4 py-2 rounded-lg text-sm font-medium transition-all shadow-sm">
<svg xmlns="http://www.w3.org/2000/svg" class="h-4 w-4" fill="none" viewBox="0 0 24 24" stroke="currentColor">
<path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M4 16v1a3 3 0 003 3h10a3 3 0 003-3v-1m-4-4l-4 4m0 0l-4-4m4 4V4" />
</svg>
匯出 Excel (.xlsx) 檔案
</button>
</div>
<div class="overflow-x-auto max-h-[500px]">
<table class="w-full text-left border-collapse">
<thead class="bg-white sticky top-0 shadow-sm z-0">
<tr>
<th class="py-3 px-6 text-sm font-semibold text-slate-600 border-b border-slate-200 w-16 text-center">排名</th>
<th class="py-3 px-6 text-sm font-semibold text-slate-600 border-b border-slate-200">製品名稱</th>
<th class="py-3 px-6 text-sm font-semibold text-slate-600 border-b border-slate-200 w-32 text-right">總數量</th>
</tr>
</thead>
<tbody id="results-body" class="divide-y divide-slate-100">
</tbody>
</table>
</div>
</div>
</div>
</div>
<script>
let currentResultData = [];
let currentWorkbook = null;
document.getElementById('excel-file').addEventListener('change', handleFileUpload);
document.getElementById('sheet-select').addEventListener('change', processSelectedSheet);
document.getElementById('download-btn').addEventListener('click', downloadExcel);
function showError(msg) {
const errorDiv = document.getElementById('error-message');
errorDiv.textContent = msg;
errorDiv.classList.remove('hidden');
document.getElementById('results-container').classList.add('hidden');
document.getElementById('empty-state').classList.remove('hidden');
hideLoading();
}
function hideError() {
document.getElementById('error-message').classList.add('hidden');
}
function showLoading() {
document.getElementById('loading').style.display = 'flex';
}
function hideLoading() {
document.getElementById('loading').style.display = 'none';
}
function handleFileUpload(e) {
hideError();
const file = e.target.files[0];
if (!file) return;
showLoading();
const reader = new FileReader();
reader.onload = function(e) {
try {
const data = new Uint8Array(e.target.result);
currentWorkbook = XLSX.read(data, {type: 'array'});
const sheetSelect = document.getElementById('sheet-select');
sheetSelect.innerHTML = '';
currentWorkbook.SheetNames.forEach((sheetName) => {
const option = document.createElement('option');
option.value = sheetName;
option.textContent = sheetName;
sheetSelect.appendChild(option);
});
document.getElementById('sheet-selector-container').classList.remove('hidden');
processSelectedSheet();
} catch (err) {
showError(`檔案解析失敗: ${err.message}`);
}
};
reader.onerror = function() {
showError("讀取檔案時發生錯誤。");
};
reader.readAsArrayBuffer(file);
}
function processSelectedSheet() {
if (!currentWorkbook) return;
showLoading();
setTimeout(() => {
try {
const sheetName = document.getElementById('sheet-select').value;
const worksheet = currentWorkbook.Sheets[sheetName];
const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 1, defval: "" });
processData(jsonData);
} catch (err) {
showError(`處理資料時發生錯誤: ${err.message}`);
} finally {
hideLoading();
}
}, 50);
}
function processData(data) {
if (!data || data.length === 0) {
showError("工作表為空或無法解析。");
return;
}
let headerRowIndex = -1;
let productColIndex = -1;
for (let i = 0; i < Math.min(20, data.length); i++) {
const row = data[i];
if (!row) continue;
const colIdx = row.findIndex(cell => {
if (typeof cell !== 'string') return false;
const cleanCell = cell.replace(/[\r\n\s]/g, '');
return cleanCell.includes('制品种类') || cleanCell.includes('製品種類');
});
if (colIdx !== -1) {
headerRowIndex = i;
productColIndex = colIdx;
break;
}
}
if (headerRowIndex === -1 || productColIndex === -1) {
showError("找不到名為「制品种类」的欄位。");
return;
}
const counts = {};
for (let i = headerRowIndex + 1; i < data.length; i++) {
const row = data[i];
if (!row || row.length <= productColIndex) continue;
let productsStr = row[productColIndex];
if (productsStr === undefined || productsStr === null || productsStr === '') continue;
if (typeof productsStr !== 'string') {
productsStr = String(productsStr);
}
// 換行拆分：將同一儲存格內的文字，透過換行符號拆成陣列
const items = productsStr.split(/\r?\n/);
for (let itemStr of items) {
itemStr = itemStr.trim();
if (!itemStr) continue;
let name = itemStr;
let qty = 1; // 預設：沒標 * 就是 1 個
// 檢查這「一行」商品字串最後有沒有 * 和數字
// (.*?) 抓取商品名稱
// \s* 允許 * 前後有空格
// [\*＊] 支援半形或全形星號
// (\d+)$ 抓取最後的數字作為數量
const match = itemStr.match(/^(.*?)\s*[\*＊]\s*(\d+)$/);
if (match) {
name = match[1].trim(); // 去掉星號前面的名稱
qty = parseInt(match[2], 10); // 星號後面的數字變成數量
}
if (counts[name]) {
counts[name] += qty;
} else {
counts[name] = qty;
}
}
}
currentResultData = Object.entries(counts).sort((a, b) => {
if (b[1] !== a[1]) {
return b[1] - a[1];
}
return a[0].localeCompare(b[0]);
});
if (currentResultData.length === 0) {
showError("未找到商品資料。");
return;
}
renderTable(currentResultData);
}
function renderTable(sortedData) {
document.getElementById('empty-state').classList.add('hidden');
document.getElementById('results-container').classList.remove('hidden');
const tbody = document.getElementById('results-body');
tbody.innerHTML = '';
let totalItems = 0;
let totalQuantity = 0;
sortedData.forEach(([name, qty], index) => {
totalItems++;
totalQuantity += qty;
const tr = document.createElement('tr');
tr.className = "hover:bg-emerald-50/50 transition-colors";
let rankStyle = "text-slate-500 font-medium";
if (index === 0) rankStyle = "text-amber-500 font-bold bg-amber-50 rounded-lg px-2 py-1";
else if (index === 1) rankStyle = "text-slate-400 font-bold bg-slate-100 rounded-lg px-2 py-1";
else if (index === 2) rankStyle = "text-amber-700 font-bold bg-amber-50 rounded-lg px-2 py-1 opacity-80";
tr.innerHTML = `
<td class="py-3 px-6 border-b border-slate-100 text-center">
<span class="${rankStyle}">${index + 1}</span>
</td>
<td class="py-3 px-6 border-b border-slate-100 text-slate-700 text-sm break-words">${name}</td>
<td class="py-3 px-6 border-b border-slate-100 text-right font-semibold text-emerald-600">${qty}</td>
`;
tbody.appendChild(tr);
});
document.getElementById('summary-text').innerHTML =
`共計 <strong>${totalItems}</strong> 款製品，總數量 <strong>${totalQuantity}</strong> 件`;
}
function downloadExcel() {
if (!currentResultData || currentResultData.length === 0) return;
showLoading();
setTimeout(() => {
try {
const exportData = [["製品名稱", "總數量"]];
currentResultData.forEach(([name, qty]) => {
exportData.push([name, qty]);
});
const wb = XLSX.utils.book_new();
const ws = XLSX.utils.aoa_to_sheet(exportData);
ws['!cols'] = [
{ wch: 50 },
{ wch: 15 }
];
XLSX.utils.book_append_sheet(wb, ws, "數量統計結果");
XLSX.writeFile(wb, "製品數量統計結果.xlsx");
} catch (err) {
alert("匯出失敗: " + err.message);
} finally {
hideLoading();
}
}, 50);
}
</script>
</body>
</html>
```