fix: 导出 Excel 合并「单价」为一列，值形如 ¥X.XX/ml|g|颗|滴

Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

.gitea/workflows/test.yml

@@ -80,7 +80,7 @@ jobs:
 
           echo "=== Batch 3: Remaining tests ==="
           timeout 300 npx cypress run \
-            --spec "cypress/e2e/app-load.cy.js,cypress/e2e/account-settings.cy.js,cypress/e2e/audit-log-advanced.cy.js,cypress/e2e/batch-operations.cy.js,cypress/e2e/bug-tracker-flow.cy.js,cypress/e2e/category-modules.cy.js,cypress/e2e/notification-flow.cy.js,cypress/e2e/oil-reference.cy.js,cypress/e2e/performance.cy.js,cypress/e2e/price-display.cy.js,cypress/e2e/projects-flow.cy.js,cypress/e2e/responsive.cy.js,cypress/e2e/search-advanced.cy.js,cypress/e2e/user-management-flow.cy.js,cypress/e2e/visual-check.cy.js" \
+            --spec "cypress/e2e/app-load.cy.js,cypress/e2e/account-settings.cy.js,cypress/e2e/audit-log-advanced.cy.js,cypress/e2e/batch-operations.cy.js,cypress/e2e/bug-tracker-flow.cy.js,cypress/e2e/category-modules.cy.js,cypress/e2e/notification-flow.cy.js,cypress/e2e/oil-reference.cy.js,cypress/e2e/oil-smart-paste.cy.js,cypress/e2e/performance.cy.js,cypress/e2e/price-display.cy.js,cypress/e2e/projects-flow.cy.js,cypress/e2e/responsive.cy.js,cypress/e2e/search-advanced.cy.js,cypress/e2e/user-management-flow.cy.js,cypress/e2e/visual-check.cy.js" \
             --config "$CYPRESS_CFG" --env "ADMIN_TOKEN=$ADMIN_TOKEN"
           B3=$?
 

frontend/cypress/e2e/oil-price-export.cy.js

@@ -0,0 +1,28 @@
+describe('Oil Reference Excel Export', () => {
+  let adminToken
+
+  before(() => {
+    cy.getAdminToken().then(token => { adminToken = token })
+  })
+
+  beforeEach(() => {
+    cy.visit('/oils', {
+      onBeforeLoad(win) {
+        win.localStorage.setItem('oil_auth_token', adminToken)
+      }
+    })
+    cy.get('.oil-chip, .oils-grid', { timeout: 10000 }).should('exist')
+  })
+
+  it('shows the Excel export button for admins', () => {
+    cy.contains('button', '📥 导出Excel').should('be.visible')
+  })
+
+  it('clicking Excel export shows success toast', () => {
+    cy.window().then(win => {
+      cy.stub(win.HTMLAnchorElement.prototype, 'click').returns(undefined)
+    })
+    cy.contains('button', '📥 导出Excel').click()
+    cy.get('.toast', { timeout: 10000 }).should('contain', '导出成功')
+  })
+})

frontend/cypress/e2e/oil-reference.cy.js

@@ -16,12 +16,22 @@ describe('Oil Reference Page', () => {
   it('filters oils by search', () => {
     cy.get('.oil-chip').then($chips => {
       const initial = $chips.length
-      cy.get('input[placeholder*="搜索精油"]').type('薰衣草')
+      cy.get('input[placeholder*="搜索"]').type('薰衣草')
       cy.wait(300)
       cy.get('.oil-chip').should('have.length.lt', initial)
     })
   })
 
+  it('filters oils by english name', () => {
+    cy.get('.oil-chip').then($chips => {
+      const initial = $chips.length
+      cy.get('input[placeholder*="搜索"]').type('Lavender')
+      cy.wait(300)
+      cy.get('.oil-chip').should('have.length.lt', initial)
+      cy.get('.oil-chip').should('exist')
+    })
+  })
+
   it('toggles between bottle and drop price view', () => {
     cy.get('.oil-chip').first().invoke('text').then(textBefore => {
       cy.contains('滴价').click()

frontend/cypress/e2e/oil-smart-paste.cy.js

@@ -0,0 +1,51 @@
+describe('Oil Reference Smart Paste', () => {
+  let adminToken
+
+  before(() => {
+    cy.getAdminToken().then(token => { adminToken = token })
+  })
+
+  beforeEach(() => {
+    cy.visit('/oils', {
+      onBeforeLoad(win) {
+        win.localStorage.setItem('oil_auth_token', adminToken)
+      }
+    })
+    cy.get('.oil-chip, .oils-grid', { timeout: 10000 }).should('exist')
+  })
+
+  it('smart paste fills product form fields', () => {
+    cy.contains('button', '＋ 新增').click()
+    cy.contains('button', '🪄 智能识别').click()
+
+    const sample = [
+      '优惠顾客价:￥310PT:41',
+      '',
+      '零售价:￥465',
+      '',
+      '点数:37 规格:100毫升',
+      '',
+      '花样年华焕颜精华水 Salubelle Rejuvenating Essence',
+    ].join('\n')
+
+    cy.get('textarea').type(sample, { parseSpecialCharSequences: false, delay: 0 })
+    cy.contains('button', '识别并填入').click()
+
+    cy.get('.add-type-tab.active').should('contain', '其他')
+    cy.get('input[placeholder="产品名称"]').should('have.value', '花样年华焕颜精华水')
+    cy.get('input[placeholder="英文名"]').should('have.value', 'Salubelle Rejuvenating Essence')
+    cy.get('input[placeholder="会员价 ¥"]').should('have.value', '310')
+    cy.get('input[placeholder="零售价 ¥"]').should('have.value', '465')
+    cy.get('input[placeholder="容量"]').should('have.value', '100')
+  })
+
+  it('smart paste detects standard ml volume as essential oil', () => {
+    cy.contains('button', '＋ 新增').click()
+    cy.contains('button', '🪄 智能识别').click()
+    const sample = '会员价:￥200\n零售价:￥267\n规格:15毫升\n薰衣草测试 LavenderTest'
+    cy.get('textarea').type(sample, { parseSpecialCharSequences: false, delay: 0 })
+    cy.contains('button', '识别并填入').click()
+    cy.get('.add-type-tab.active').should('contain', '精油')
+    cy.get('input[placeholder="精油名称"]').should('have.value', '薰衣草测试')
+  })
+})

frontend/src/__tests__/oilProductPaste.test.js

@@ -0,0 +1,73 @@
+import { describe, it, expect } from 'vitest'
+import { parseOilProductPaste } from '../composables/useOilProductPaste'
+
+describe('parseOilProductPaste', () => {
+  it('returns empty shape for empty input', () => {
+    const r = parseOilProductPaste('')
+    expect(r.cn).toBe('')
+    expect(r.en).toBe('')
+    expect(r.memberPrice).toBeNull()
+    expect(r.retailPrice).toBeNull()
+  })
+
+  it('parses the 花样年华 sample as product with 100ml', () => {
+    const sample = `优惠顾客价:￥310PT:41
+
+零售价:￥465
+
+点数:37 规格:100毫升
+
+花样年华焕颜精华水 Salubelle Rejuvenating Essence`
+    const r = parseOilProductPaste(sample)
+    expect(r.type).toBe('product')
+    expect(r.memberPrice).toBe(310)
+    expect(r.retailPrice).toBe(465)
+    expect(r.productAmount).toBe(100)
+    expect(r.productUnit).toBe('ml')
+    expect(r.cn).toBe('花样年华焕颜精华水')
+    expect(r.en).toBe('Salubelle Rejuvenating Essence')
+  })
+
+  it('detects essential oil when volume is standard ml', () => {
+    const sample = `会员价:￥200\n零售价:￥267\n规格:15毫升\n薰衣草 Lavender`
+    const r = parseOilProductPaste(sample)
+    expect(r.type).toBe('oil')
+    expect(r.volume).toBe('15')
+    expect(r.cn).toBe('薰衣草')
+    expect(r.en).toBe('Lavender')
+  })
+
+  it('handles half-width colon and dollar variant', () => {
+    const r = parseOilProductPaste('优惠顾客价: ¥99\n零售价: ¥150\n规格: 5ml\n柠檬 Lemon')
+    expect(r.memberPrice).toBe(99)
+    expect(r.retailPrice).toBe(150)
+    expect(r.type).toBe('oil')
+    expect(r.volume).toBe('5')
+  })
+
+  it('parses capsule spec as product', () => {
+    const r = parseOilProductPaste('优惠顾客价:￥200\n规格:60粒\n深海鱼油 Omega')
+    expect(r.type).toBe('product')
+    expect(r.productAmount).toBe(60)
+    expect(r.productUnit).toBe('capsule')
+  })
+
+  it('parses gram spec as product', () => {
+    const r = parseOilProductPaste('优惠顾客价:￥80\n规格:120克\n洁面乳 Face Wash')
+    expect(r.productUnit).toBe('g')
+    expect(r.productAmount).toBe(120)
+  })
+
+  it('non-standard ml volume falls to product', () => {
+    const r = parseOilProductPaste('优惠顾客价:￥310\n规格:100毫升\n精华 Essence')
+    expect(r.type).toBe('product')
+    expect(r.productAmount).toBe(100)
+    expect(r.productUnit).toBe('ml')
+  })
+
+  it('name without english part keeps cn only', () => {
+    const r = parseOilProductPaste('优惠顾客价:￥50\n规格:5毫升\n某国产品')
+    expect(r.cn).toBe('某国产品')
+    expect(r.en).toBe('')
+  })
+})

frontend/src/composables/useOilProductPaste.js

@@ -0,0 +1,52 @@
+const OIL_VOLUMES = new Set(['2.5', '5', '10', '15', '115'])
+
+export function parseOilProductPaste(raw) {
+  const result = {
+    type: 'product',
+    cn: '',
+    en: '',
+    memberPrice: null,
+    retailPrice: null,
+    volume: null,
+    customDrops: null,
+    productAmount: null,
+    productUnit: null,
+  }
+  if (!raw || !raw.trim()) return result
+
+  const text = raw.replace(/[：:]/g, ':').replace(/[￥¥]/g, '')
+
+  const memberMatch = text.match(/(?:优惠顾客价|会员价|批发价)\s*:?\s*(\d+(?:\.\d+)?)/)
+  const retailMatch = text.match(/零售价\s*:?\s*(\d+(?:\.\d+)?)/)
+  const specMatch = text.match(/规格\s*:?\s*(\d+(?:\.\d+)?)\s*(毫升|ml|ML|克|g|G|颗|粒|片)/)
+
+  if (memberMatch) result.memberPrice = Number(memberMatch[1])
+  if (retailMatch) result.retailPrice = Number(retailMatch[1])
+
+  for (const line of raw.split(/\r?\n/)) {
+    const s = line.trim()
+    if (!s) continue
+    if (/优惠顾客价|会员价|零售价|点数|规格|PT\s*:|批发价/i.test(s)) continue
+    const m = s.match(/^([^A-Za-z]+?)\s+([A-Za-z].*)$/)
+    if (m) { result.cn = m[1].trim(); result.en = m[2].trim() } else { result.cn = s }
+    break
+  }
+
+  if (specMatch) {
+    const amount = specMatch[1]
+    const unitRaw = specMatch[2].toLowerCase()
+    const isMl = unitRaw === '毫升' || unitRaw === 'ml'
+    if (isMl && OIL_VOLUMES.has(String(Number(amount)))) {
+      result.type = 'oil'
+      result.volume = String(Number(amount))
+    } else {
+      result.type = 'product'
+      result.productAmount = Number(amount)
+      result.productUnit = (unitRaw === '克' || unitRaw === 'g') ? 'g'
+        : (unitRaw === '颗' || unitRaw === '粒' || unitRaw === '片') ? 'capsule'
+        : 'ml'
+    }
+  }
+
+  return result
+}

frontend/src/views/OilReference.vue

@@ -91,7 +91,7 @@
     <!-- Search + View Toggle + Add + PDF -->
     <div style="display:flex;gap:6px;align-items:center;margin-bottom:12px;flex-wrap:nowrap">
       <div class="search-box" style="flex:1;min-width:140px;margin-bottom:0">
-        <input class="search-input" v-model="searchQuery" placeholder="搜索精油名称…" style="width:100%" />
+        <input class="search-input" v-model="searchQuery" placeholder="搜索中文或英文名…" style="width:100%" />
       </div>
       <div style="display:flex;border:1.5px solid var(--border);border-radius:10px;overflow:hidden;flex-shrink:0">
         <button @click="viewMode = 'bottle'" :style="viewMode === 'bottle' ? 'background:var(--sage);color:white' : 'background:white;color:var(--text-mid)'" style="border:none;border-radius:0;font-size:12px;padding:6px 12px;cursor:pointer">每瓶价</button>
@@ -99,10 +99,10 @@
       </div>
       <!-- Desktop: text buttons -->
       <button v-if="auth.canManage" class="toolbar-btn-text" @click="showAddForm = !showAddForm">{{ showAddForm ? '收起' : '＋ 新增' }}</button>
-      <button v-if="auth.isAdmin" class="toolbar-btn-text" @click="exportPDF">📥 导出PDF</button>
+      <button v-if="auth.isAdmin" class="toolbar-btn-text" @click="exportExcel">📥 导出Excel</button>
       <!-- Mobile: emoji-only buttons -->
       <button v-if="auth.canManage" class="toolbar-btn-icon" @click="showAddForm = !showAddForm" title="新增精油">➕</button>
-      <button v-if="auth.isAdmin" class="toolbar-btn-icon" @click="exportPDF" title="导出PDF">📄</button>
+      <button v-if="auth.isAdmin" class="toolbar-btn-icon" @click="exportExcel" title="导出Excel">📄</button>
     </div>
 
     <!-- Add Oil Form (toggleable) -->
@@ -110,6 +110,14 @@
       <div class="add-type-tabs">
         <button class="add-type-tab" :class="{ active: addType === 'oil' }" @click="addType = 'oil'">精油</button>
         <button class="add-type-tab" :class="{ active: addType === 'product' }" @click="addType = 'product'">其他</button>
+        <button class="add-type-tab" :class="{ active: showSmartPaste }" @click="showSmartPaste = !showSmartPaste" style="margin-left:auto">🪄 智能识别</button>
+      </div>
+      <div v-if="showSmartPaste" class="form-row" style="flex-direction:column;align-items:stretch;gap:6px">
+        <textarea v-model="smartPasteText" rows="4" class="form-input-sm" placeholder="粘贴产品信息，例如：&#10;优惠顾客价:￥310&#10;零售价:￥465&#10;规格:100毫升&#10;花样年华焕颜精华水 Salubelle Rejuvenating Essence" style="width:100%;resize:vertical;font-family:inherit"></textarea>
+        <div style="display:flex;gap:8px">
+          <button class="btn btn-primary btn-sm" @click="runSmartPaste" :disabled="!smartPasteText.trim()">识别并填入</button>
+          <button class="btn btn-sm" @click="smartPasteText = ''">清空</button>
+        </div>
       </div>
       <!-- 新增精油 -->
       <div v-if="addType === 'oil'" class="form-row">
@@ -437,6 +445,7 @@ import { oilEn } from '../composables/useOilTranslation'
 import { getOilCard, setOilCard } from '../composables/useOilCards'
 import { showConfirm } from '../composables/useDialog'
 import { api } from '../composables/useApi'
+import { parseOilProductPaste } from '../composables/useOilProductPaste'
 
 const auth = useAuthStore()
 const oils = useOilsStore()
@@ -468,6 +477,28 @@ const activeCard = ref(null)
 
 // Add oil form
 const addType = ref('oil')
+const showSmartPaste = ref(false)
+const smartPasteText = ref('')
+
+function runSmartPaste() {
+  const raw = smartPasteText.value || ''
+  if (!raw.trim()) return
+  const parsed = parseOilProductPaste(raw)
+  if (parsed.memberPrice != null) newBottlePrice.value = parsed.memberPrice
+  if (parsed.retailPrice != null) newRetailPrice.value = parsed.retailPrice
+  if (parsed.cn) newOilName.value = parsed.cn
+  if (parsed.en) newOilEnName.value = parsed.en
+  addType.value = parsed.type
+  if (parsed.type === 'oil') {
+    if (parsed.volume) newVolume.value = parsed.volume
+    newCustomDrops.value = null
+  } else {
+    if (parsed.productAmount != null) newProductAmount.value = parsed.productAmount
+    if (parsed.productUnit) newProductUnit.value = parsed.productUnit
+  }
+  ui.showToast('已识别并填入，请检查后点添加')
+}
+
 const newOilName = ref('')
 const newOilEnName = ref('')
 const newBottlePrice = ref(null)
@@ -614,8 +645,14 @@ const filteredOilNames = computed(() => {
   if (!searchQuery.value.trim()) return oils.oilNames
   const q = searchQuery.value.trim().toLowerCase()
   return oils.oilNames.filter(n => {
-    const en = getEnglishName(n).toLowerCase()
-    return n.toLowerCase().includes(q) || en.includes(q)
+    if (n.toLowerCase().includes(q)) return true
+    const card = getOilCard(n)
+    if (card?.en && card.en.toLowerCase().includes(q)) return true
+    const meta = oils.oilsMeta[n]
+    if (meta?.enName && meta.enName.toLowerCase().includes(q)) return true
+    const fallback = oilEn(n)
+    if (fallback && fallback.toLowerCase().includes(q)) return true
+    return false
   })
 })
 
@@ -854,66 +891,38 @@ async function removeOil(name) {
   }
 }
 
-// PDF Export
-function exportPDF() {
+// Excel Export
+async function exportExcel() {
+  const XLSX = (await import('xlsx')).default || await import('xlsx')
   const today = new Date()
-  const dateStr = today.getFullYear() + String(today.getMonth()+1).padStart(2,'0') + String(today.getDate()).padStart(2,'0')
-  const title = '精油价目表' + dateStr
+  const dateStr = today.getFullYear() + '-' + String(today.getMonth()+1).padStart(2,'0') + '-' + String(today.getDate()).padStart(2,'0')
 
   const sortedNames = [...oils.oilNames].sort((a, b) => a.localeCompare(b, 'zh'))
-  let rows = ''
+  const rows = []
   for (const name of sortedNames) {
     const meta = getMeta(name)
     if (!meta) continue
     const en = getEnglishName(name)
-    const bp = meta.bottlePrice != null ? '¥' + meta.bottlePrice.toFixed(0) : '--'
-    const rp = meta.retailPrice != null ? '¥' + meta.retailPrice.toFixed(0) : '--'
     const vol = volumeLabel(meta.dropCount, name)
     const unit = oilPriceUnit(name)
-    const ppd = oils.pricePerDrop(name) ? '¥' + oils.pricePerDrop(name).toFixed(2) + '/' + unit : '--'
-    rows += `<tr>
-      <td>${name}</td>
-      <td>${en}</td>
-      <td>${bp}</td>
-      <td>${rp}</td>
-      <td>${vol}</td>
-      <td>${ppd}</td>
-    </tr>`
+    const ppdNum = oils.pricePerDrop(name)
+    rows.push({
+      '精油': name,
+      '英文名': en,
+      '会员价': meta.bottlePrice != null ? Number(meta.bottlePrice.toFixed(2)) : '',
+      '零售价': meta.retailPrice != null ? Number(meta.retailPrice.toFixed(2)) : '',
+      '容量': vol,
+      '单价': ppdNum ? `¥${ppdNum.toFixed(2)}/${unit}` : '',
+      '状态': meta.isActive === false ? '下架' : '在售',
+    })
   }
-  const html = `<!DOCTYPE html>
-<html>
-<head>
-<meta charset="utf-8">
-<title>${title}</title>
-<style>
-  body { font-family: 'PingFang SC','Hiragino Sans GB','Microsoft YaHei',sans-serif; padding: 20px; font-size: 11px; color: #333; }
-  h1 { font-size: 18px; text-align: center; margin-bottom: 16px; }
-  table { width: 100%; border-collapse: collapse; }
-  th { background: #7a9e7e; color: white; padding: 6px 8px; text-align: center; font-size: 11px; font-weight: 600; }
-  td { padding: 5px 8px; border-bottom: 1px solid #e0e0e0; text-align: center; font-size: 11px; }
-  td:first-child, th:first-child { text-align: left; font-weight: 500; }
-  td:nth-child(2), th:nth-child(2) { text-align: left; }
-  tr:nth-child(even) { background: #f9f9f9; }
-  tr:hover { background: #e8f5e9; }
-  @media print { body { padding: 10px; } h1 { font-size: 16px; } }
-</style>
-</head>
-<body>
-<h1>doTERRA 精油价目表 ${dateStr}</h1>
-<table>
-  <thead>
-    <tr><th>精油</th><th>英文名</th><th>会员价</th><th>零售价</th><th>容量</th><th>单价</th></tr>
-  </thead>
-  <tbody>${rows}</tbody>
-</table>
-<p style="text-align:center;font-size:10px;color:#aaa;margin-top:12px">共 ${sortedNames.length} 种精油 · doTERRA 配方计算器导出</p>
-</body>
-</html>`
-  const w = window.open('', '_blank')
-  w.document.write(html)
-  w.document.close()
-  w.document.title = title
-  setTimeout(() => w.print(), 500)
+
+  const ws = XLSX.utils.json_to_sheet(rows)
+  ws['!cols'] = [{ wch: 16 }, { wch: 28 }, { wch: 10 }, { wch: 10 }, { wch: 12 }, { wch: 16 }, { wch: 8 }]
+  const wb = XLSX.utils.book_new()
+  XLSX.utils.book_append_sheet(wb, ws, '精油价目表')
+  XLSX.writeFile(wb, `精油价目表${dateStr}.xlsx`)
+  ui.showToast('导出成功')
 }
 
 // ──── Save image logic (identical to RecipeDetailOverlay) ────

frontend/src/views/RecipeManager.vue

@@ -1699,7 +1699,7 @@ async function exportExcel() {
   }
 
   const today = new Date().toISOString().slice(0, 10)
-  XLSX.writeFile(wb, `精油配方${today}.xlsx`)
+  XLSX.writeFile(wb, `精油配方备份${today}.xlsx`)
   ui.showToast('导出成功')
 }