#Creating Excel Templates

Learn how to create custom Excel templates that can transform any Excel structure into LHDN-compliant invoices.

#What is an Excel Template?

An Excel Template is a configuration that tells InvoisX how to read and interpret your Excel files. It maps your Excel columns to the required LHDN invoice fields and defines rules for data transformation.

#Creating Your First Template

#Step 1: Upload Your Excel File

1. Navigate to Data Import > Excel Templates
2. Click the Create Template button
3. Upload a sample Excel file that represents your typical invoice format
4. Enter a descriptive name and description for your template

#Step 2: Configure Import Settings

After uploading, you'll see the Configure Template page with several key sections:

#Template Information

• Name: Your template identifier
• Description: Notes about this template's purpose
• Import Mode: How your Excel data is structured
• Validation Mode: Strict or relaxed validation rules
• Status: Active/Inactive toggle

#Statistics Dashboard

The top dashboard shows your template's current status:

• Total Sheets: Number of Excel sheets detected
• Total Columns: All columns found in your Excel file
• Mapped Columns: Columns successfully mapped to invoice fields
• Mapping Progress: Percentage of completion with visual progress bar

#Import Modes

Choose the import mode that matches your Excel structure:

#Standard (Header + Lines)

• Best for: Excel files with separate header and line item sheets
• Structure: One sheet for invoice header data, another for line items
• Example: "Invoice_Headers" sheet + "Invoice_Lines" sheet

#Mixed (Single Sheet)

• Best for: All invoice data in one sheet
• Structure: Each row contains both header and line item information
• Example: One sheet with columns for invoice details and item details

#Lines Only

• Best for: Excel files containing only line item data
• Structure: Header information will be auto-generated or set via defaults
• Example: Product catalog or item listing imports

#Template Configuration Options

#Import Mode Settings

• Standard: Select when you have separate sheets for headers and lines
• Mixed: Choose for single-sheet files with all data
• Lines Only: Use for item-only imports

#Validation Mode

• Strict: Enforces all LHDN validation rules (recommended for production)
• Relaxed: More lenient validation (useful for testing)

#Template Status

• Active: Template is available for imports
• Inactive: Template is saved but not usable for imports

#Next Steps

Once your template is created:

1. Map your columns to LHDN invoice fields
2. Set up code mappings for value transformation
3. Configure default values for automation
4. Test your template with sample data

#Tips for Success

#Excel File Preparation

• Use consistent column headers across your Excel files
• Ensure data types are consistent (dates as dates, numbers as numbers)
• Remove empty rows and columns that aren't needed

#Naming Conventions

• Use descriptive template names (e.g., "Sales_Invoice_Q1_2024")
• Include the source system in the description
• Mention any specific formatting requirements

#Testing Strategy

• Start with a small sample file (5-10 invoices)
• Test all mapping scenarios before full production use
• Verify calculations and totals after import

#Common Use Cases

#Accounting System Export

For Excel exports from accounting systems like QuickBooks or Xero:

• Usually requires Standard mode (separate header/lines)
• Focus on mapping amounts and tax calculations
• Set up code mappings for chart of accounts

#Sales Report Import

For sales reports or order exports:

• Often works best with Mixed mode
• Requires customer and product code mappings
• May need conditional defaults for missing data

#Inventory-Based Invoicing

For product catalog or inventory-based invoicing:

• Use Lines Only mode
• Set fixed defaults for common header values
• Focus on product code and pricing mappings