Handsontable Integration

MStore Dashboard menggunakan Handsontable untuk fitur spreadsheet yang memungkinkan editing data seperti Excel dengan copy-paste, validation, dan custom cell types.

Installation

Handsontable sudah terinstall:

{
  "dependencies": {
    "@handsontable/vue3": "^16.2.0",
    "handsontable": "^16.2.0"
  }
}

CSS styles dimuat di nuxt.config.ts:

export default defineNuxtConfig({
  css: [
    'handsontable/styles/handsontable.css',
    'handsontable/styles/ht-theme-main.css',
    'handsontable/styles/ht-theme-horizon.css',
    'handsontable/styles/ht-theme-classic.css'
  ]
})

Basic Usage

<template>
  <HotTable
    :data="tableData"
    :columns="columns"
    :colHeaders="colHeaders"
    :rowHeaders="true"
    :height="400"
    :licenseKey="'non-commercial-and-evaluation'"
  />
</template>

<script setup lang="ts">
import { HotTable } from '@handsontable/vue3'
import { registerAllModules } from 'handsontable/registry'

// Register all Handsontable modules
registerAllModules()

const tableData = ref([
  { name: 'Product A', sku: 'SKU001', price: 100000, stock: 50 },
  { name: 'Product B', sku: 'SKU002', price: 200000, stock: 30 },
])

const columns = [
  { data: 'name', type: 'text' },
  { data: 'sku', type: 'text' },
  { data: 'price', type: 'numeric', numericFormat: { pattern: '0,0' } },
  { data: 'stock', type: 'numeric' },
]

const colHeaders = ['Product Name', 'SKU', 'Price', 'Stock']
</script>

Column Types

Text Column

{
  data: 'name',
  type: 'text',
  width: 200,
}

Numeric Column

{
  data: 'price',
  type: 'numeric',
  numericFormat: {
    pattern: '0,0.00',
    culture: 'id-ID'
  },
  width: 120,
}

{
  data: 'category',
  type: 'dropdown',
  source: ['Electronics', 'Clothing', 'Food', 'Beverages'],
  width: 150,
}

Autocomplete Column

{
  data: 'supplier',
  type: 'autocomplete',
  source: supplierNames,
  strict: true,
  allowInvalid: false,
  width: 180,
}

Checkbox Column

{
  data: 'isActive',
  type: 'checkbox',
  checkedTemplate: true,
  uncheckedTemplate: false,
  width: 80,
}

Date Column

{
  data: 'expiryDate',
  type: 'date',
  dateFormat: 'YYYY-MM-DD',
  correctFormat: true,
  width: 120,
}

Read-Only Column

{
  data: 'id',
  type: 'text',
  readOnly: true,
  width: 100,
}

Advanced Configuration

Full Example with All Features

<template>
  <div class="spreadsheet-container">
    <HotTable
      ref="hotTableRef"
      :data="tableData"
      :columns="columns"
      :colHeaders="colHeaders"
      :rowHeaders="true"
      :minRows="10"
      :minSpareRows="3"
      :height="500"
      :width="'100%'"
      :stretchH="'all'"
      :contextMenu="contextMenuConfig"
      :copyPaste="true"
      :filters="true"
      :dropdownMenu="true"
      :manualColumnResize="true"
      :manualRowResize="true"
      :undo="true"
      :search="true"
      :comments="true"
      :licenseKey="'non-commercial-and-evaluation'"
      @afterChange="handleAfterChange"
      @afterSelection="handleAfterSelection"
      @beforeValidate="handleBeforeValidate"
    />
  </div>
</template>

<script setup lang="ts">
import { HotTable } from '@handsontable/vue3'
import { registerAllModules } from 'handsontable/registry'
import type { CellChange } from 'handsontable/common'

registerAllModules()

const hotTableRef = ref<InstanceType<typeof HotTable> | null>(null)

const tableData = ref([
  { id: 1, name: 'Laptop', sku: 'ELEC-001', price: 15000000, stock: 10, category: 'Electronics', isActive: true },
  { id: 2, name: 'T-Shirt', sku: 'CLTH-001', price: 150000, stock: 100, category: 'Clothing', isActive: true },
])

const columns = [
  { data: 'id', type: 'numeric', readOnly: true, width: 60 },
  { data: 'name', type: 'text', width: 200 },
  { data: 'sku', type: 'text', width: 120 },
  {
    data: 'price',
    type: 'numeric',
    numericFormat: { pattern: '0,0', culture: 'id-ID' },
    width: 150,
  },
  { data: 'stock', type: 'numeric', width: 80 },
  {
    data: 'category',
    type: 'dropdown',
    source: ['Electronics', 'Clothing', 'Food', 'Beverages'],
    width: 150,
  },
  { data: 'isActive', type: 'checkbox', width: 80 },
]

const colHeaders = ['ID', 'Name', 'SKU', 'Price', 'Stock', 'Category', 'Active']

const contextMenuConfig = {
  items: {
    'row_above': { name: 'Insert row above' },
    'row_below': { name: 'Insert row below' },
    'separator1': '---------',
    'remove_row': { name: 'Remove row' },
    'separator2': '---------',
    'undo': { name: 'Undo' },
    'redo': { name: 'Redo' },
    'separator3': '---------',
    'copy': { name: 'Copy' },
    'cut': { name: 'Cut' },
  }
}

// Event handlers
const handleAfterChange = (changes: CellChange[] | null, source: string) => {
  if (!changes || source === 'loadData') return

  changes.forEach(([row, prop, oldValue, newValue]) => {
    console.log(`Row ${row}, Column ${prop}: ${oldValue} → ${newValue}`)
  })

  // Emit changes to parent
  emit('dataChange', tableData.value)
}

const handleAfterSelection = (
  row: number,
  column: number,
  row2: number,
  column2: number
) => {
  console.log(`Selected: (${row}, ${column}) to (${row2}, ${column2})`)
}

const handleBeforeValidate = (value: any, row: number, prop: string) => {
  // Custom validation logic
  if (prop === 'price' && value < 0) {
    return false
  }
  return true
}

const emit = defineEmits<{
  dataChange: [data: typeof tableData.value]
}>()
</script>

<style scoped>
.spreadsheet-container {
  border: 1px solid #e5e7eb;
  border-radius: 0.5rem;
  overflow: hidden;
}
</style>

Cell Validation

Built-in Validators

const columns = [
  // Required field
  {
    data: 'name',
    type: 'text',
    validator: (value, callback) => {
      callback(value !== null && value !== '')
    },
  },

  // Number range
  {
    data: 'price',
    type: 'numeric',
    validator: (value, callback) => {
      callback(value >= 0 && value <= 100000000)
    },
  },

  // Regex pattern
  {
    data: 'sku',
    type: 'text',
    validator: /^[A-Z]{3,4}-\d{3,4}$/,
  },
]

Custom Validator

const emailValidator = (value: string, callback: (valid: boolean) => void) => {
  const emailPattern = /^[^\s@]+@[^\s@]+\.[^\s@]+$/
  callback(emailPattern.test(value))
}

const columns = [
  {
    data: 'email',
    type: 'text',
    validator: emailValidator,
    allowInvalid: false, // Prevent invalid values
  },
]

Server-Side Validation

const handleAfterChange = async (changes: CellChange[] | null, source: string) => {
  if (!changes || source === 'loadData') return

  // Collect all changes
  const changedRows = changes.map(([row]) => tableData.value[row])

  try {
    const result = await $fetch('/api/v1/inventory/validate', {
      method: 'POST',
      body: { items: changedRows },
    })

    if (!result.valid) {
      // Show validation errors
      toast.add({
        title: 'Validation Error',
        description: result.errors.join(', '),
        color: 'error',
      })

      // Highlight invalid cells
      result.invalidCells.forEach(({ row, col }) => {
        hotTableRef.value?.hotInstance?.setCellMeta(row, col, 'valid', false)
      })
      hotTableRef.value?.hotInstance?.render()
    }
  } catch (error) {
    console.error('Validation failed:', error)
  }
}

Custom Cell Renderers

import Handsontable from 'handsontable'

// Currency renderer
const currencyRenderer = (
  instance: Handsontable,
  td: HTMLTableCellElement,
  row: number,
  col: number,
  prop: string | number,
  value: any,
  cellProperties: Handsontable.CellProperties
) => {
  Handsontable.renderers.NumericRenderer.apply(this, arguments as any)

  if (value !== null && value !== undefined) {
    td.textContent = new Intl.NumberFormat('id-ID', {
      style: 'currency',
      currency: 'IDR',
    }).format(value)
  }
}

// Status badge renderer
const statusRenderer = (
  instance: Handsontable,
  td: HTMLTableCellElement,
  row: number,
  col: number,
  prop: string | number,
  value: any
) => {
  td.innerHTML = ''

  const badge = document.createElement('span')
  badge.className = value === 'active'
    ? 'px-2 py-1 text-xs rounded-full bg-green-100 text-green-800'
    : 'px-2 py-1 text-xs rounded-full bg-red-100 text-red-800'
  badge.textContent = value

  td.appendChild(badge)
}

// Register renderers
Handsontable.renderers.registerRenderer('currency', currencyRenderer)
Handsontable.renderers.registerRenderer('status', statusRenderer)

// Use in columns
const columns = [
  { data: 'price', renderer: 'currency' },
  { data: 'status', renderer: 'status' },
]

Copy-Paste from Excel

Handsontable mendukung copy-paste langsung dari Excel:

<template>
  <HotTable
    :data="tableData"
    :columns="columns"
    :copyPaste="copyPasteConfig"
  />
</template>

<script setup lang="ts">
const copyPasteConfig = {
  columnsLimit: 100,
  rowsLimit: 1000,
  pasteMode: 'overwrite', // or 'shift_down', 'shift_right'
  uiContainer: document.body,
}
</script>

Composable for Spreadsheet

// composables/useSpreadsheet.ts
import { HotTable } from '@handsontable/vue3'
import type Handsontable from 'handsontable'

export const useSpreadsheet = <T extends Record<string, any>>(
  initialData: T[] = []
) => {
  const hotRef = ref<InstanceType<typeof HotTable> | null>(null)
  const data = ref<T[]>(initialData)
  const selectedRows = ref<number[]>([])

  // Get Handsontable instance
  const getInstance = (): Handsontable | null => {
    return hotRef.value?.hotInstance ?? null
  }

  // Get all data
  const getData = (): T[] => {
    const instance = getInstance()
    if (!instance) return data.value
    return instance.getSourceData() as T[]
  }

  // Set data
  const setData = (newData: T[]) => {
    data.value = newData
    getInstance()?.loadData(newData)
  }

  // Add row
  const addRow = (rowData?: Partial<T>) => {
    const instance = getInstance()
    if (!instance) return

    const newRow = rowData || ({} as T)
    instance.alter('insert_row_below', instance.countRows() - 1)
    const lastRow = instance.countRows() - 1

    Object.entries(newRow).forEach(([key, value]) => {
      const colIndex = instance.propToCol(key)
      if (colIndex !== -1) {
        instance.setDataAtCell(lastRow, colIndex, value)
      }
    })
  }

  // Remove selected rows
  const removeSelectedRows = () => {
    const instance = getInstance()
    if (!instance || selectedRows.value.length === 0) return

    // Sort descending to remove from bottom first
    const sorted = [...selectedRows.value].sort((a, b) => b - a)
    sorted.forEach(row => {
      instance.alter('remove_row', row)
    })

    selectedRows.value = []
  }

  // Validate all
  const validateAll = async (): Promise<boolean> => {
    const instance = getInstance()
    if (!instance) return false

    return new Promise(resolve => {
      instance.validateCells(valid => {
        resolve(valid)
      })
    })
  }

  // Export to array
  const exportData = (): T[] => {
    return getData().filter(row =>
      Object.values(row).some(v => v !== null && v !== '' && v !== undefined)
    )
  }

  return {
    hotRef,
    data,
    selectedRows,
    getInstance,
    getData,
    setData,
    addRow,
    removeSelectedRows,
    validateAll,
    exportData,
  }
}

Usage with Composable

<template>
  <div class="space-y-4">
    <div class="flex gap-2">
      <UButton @click="addRow()">Add Row</UButton>
      <UButton
        color="error"
        :disabled="selectedRows.length === 0"
        @click="removeSelectedRows"
      >
        Remove Selected
      </UButton>
      <UButton @click="handleSave">Save</UButton>
    </div>

    <HotTable
      ref="hotRef"
      :data="data"
      :columns="columns"
      :colHeaders="colHeaders"
      @afterSelectionEnd="handleSelection"
    />
  </div>
</template>

<script setup lang="ts">
import { useSpreadsheet } from '~/composables/useSpreadsheet'

interface Product {
  name: string
  sku: string
  price: number
}

const {
  hotRef,
  data,
  selectedRows,
  addRow,
  removeSelectedRows,
  validateAll,
  exportData,
} = useSpreadsheet<Product>([])

const handleSelection = (
  row: number,
  column: number,
  row2: number,
  column2: number
) => {
  const rows: number[] = []
  for (let i = Math.min(row, row2); i <= Math.max(row, row2); i++) {
    rows.push(i)
  }
  selectedRows.value = rows
}

const handleSave = async () => {
  const isValid = await validateAll()
  if (!isValid) {
    toast.add({ title: 'Please fix validation errors', color: 'error' })
    return
  }

  const products = exportData()
  await saveProducts(products)
}
</script>

Best Practices

License Key

Untuk production, gunakan license key yang valid:

<HotTable licenseKey="your-license-key" />

Performance

Gunakan minSpareRows untuk auto-add rows
Limit data untuk large datasets
Use virtual rendering (default)
Avoid unnecessary re-renders

Validation

Validate on client-side first
Confirm with server-side validation
Show clear error messages
Highlight invalid cells

Next Steps

i18n Localization

Multi-language support

Onboarding Feature

Spreadsheet in onboarding wizard

UI Framework

​Handsontable Integration

​Installation

​Basic Usage

​Column Types

​Text Column

​Numeric Column

​Dropdown Column

​Autocomplete Column

​Checkbox Column

​Date Column

​Read-Only Column

​Advanced Configuration

​Full Example with All Features

​Cell Validation

​Built-in Validators

​Custom Validator

​Server-Side Validation

​Custom Cell Renderers

​Copy-Paste from Excel

​Composable for Spreadsheet

​Usage with Composable

​Best Practices

​Next Steps