xlsx

TDTP ↔ XLSX Converter

Bidirectional converter between TDTP packets and Excel XLSX files.

What you get immediately:

• export from a database into Excel for analysis
• import from Excel into a database with no programming
• people work in the Excel they already know
• ideal for business users

Features

• ✅ TDTP → XLSX: Convert database exports to Excel
• ✅ XLSX → TDTP: Convert Excel files to database imports
• ✅ Type Preservation: INTEGER, REAL, DECIMAL, TEXT, BOOLEAN, DATE, DATETIME, etc.
• ✅ Formatted Headers: Show field names with types and keys
• ✅ Auto-formatting: Numbers, dates, booleans formatted correctly
• ✅ Primary Keys: Marked with * in headers
• ✅ Simple API: Just 2 functions

Installation

go get github.com/queuebridge/tdtp/pkg/xlsx

Quick Start

Export Database → Excel

import "github.com/queuebridge/tdtp/pkg/xlsx"

// Export from database to XLSX
packet, _ := adapter.ExportTable(ctx, "orders")
err := xlsx.ToXLSX(packet, "orders.xlsx", "Orders")

Import Excel → Database

// Import from XLSX to database
packet, err := xlsx.FromXLSX("orders.xlsx", "Orders")
adapter.ImportPacket(ctx, packet, adapters.StrategyReplace)

API Reference

ToXLSX

Convert TDTP packet to Excel file.

func ToXLSX(pkt *packet.DataPacket, filePath string, sheetName string) error

Parameters:

• pkt - TDTP data packet
• filePath - Output Excel file path
• sheetName - Sheet name (uses table name if empty)

Returns: error if conversion fails

Example:

packet := packet.NewDataPacket(packet.TypeReference, "customers")
packet.Schema = packet.Schema{
    Fields: []packet.Field{
        {Name: "id", Type: "INTEGER", Key: true},
        {Name: "name", Type: "TEXT"},
        {Name: "email", Type: "TEXT"},
        {Name: "balance", Type: "DECIMAL"},
    },
}
packet.Data = packet.Data{
    Rows: []packet.Row{
        {Value: "1|John Doe|john@example.com|1500.50"},
        {Value: "2|Jane Smith|jane@example.com|2750.25"},
    },
}

err := xlsx.ToXLSX(packet, "customers.xlsx", "Customers")

Excel Output:

| id (INTEGER) * | name (TEXT) | email (TEXT)        | balance (DECIMAL) |
|----------------|-------------|---------------------|-------------------|
| 1              | John Doe    | john@example.com    | 1500.50           |
| 2              | Jane Smith  | jane@example.com    | 2750.25           |

FromXLSX

Convert Excel file to TDTP packet.

func FromXLSX(filePath string, sheetName string) (*packet.DataPacket, error)

Parameters:

• filePath - Input Excel file path
• sheetName - Sheet name (uses first sheet if empty)

Returns:

• *packet.DataPacket - TDTP packet
• error if conversion fails

Expected Excel Format:

• First row: Headers with format field_name (TYPE) or field_name (TYPE) * for keys
• Following rows: Data

Example:

packet, err := xlsx.FromXLSX("customers.xlsx", "Customers")
if err != nil {
    log.Fatal(err)
}

// Use with database adapter
adapter.ImportPacket(ctx, packet, adapters.StrategyReplace)

Use Cases

1. Database Reports to Excel

// Export database table to Excel for analysis
packets, _ := pgAdapter.ExportTable(ctx, "sales_report")
for i, pkt := range packets {
    filename := fmt.Sprintf("sales_report_part_%d.xlsx", i+1)
    xlsx.ToXLSX(pkt, filename, "Sales")
}

// Business analysts can now:
// - Create pivot tables
// - Apply filters
// - Add charts
// - Share with stakeholders

2. Bulk Data Loading from Excel

// Business users prepare data in Excel
// Load to database

packet, _ := xlsx.FromXLSX("new_products.xlsx", "Products")
err := mysqlAdapter.ImportPacket(ctx, packet, adapters.StrategyReplace)

// Benefits:
// - No SQL knowledge required
// - Data validation in Excel
// - Version control via file
// - Easy collaboration

3. Database Migration via Excel

// Export from source database
srcPackets, _ := oracleAdapter.ExportTable(ctx, "customers")
xlsx.ToXLSX(srcPackets[0], "customers_migration.xlsx", "Customers")

// Manual data cleanup/transformation in Excel
// - Fix invalid emails
// - Normalize phone numbers
// - Remove duplicates
// - Add missing data

// Import to target database
packet, _ := xlsx.FromXLSX("customers_migration.xlsx", "Customers")
postgresAdapter.ImportPacket(ctx, packet, adapters.StrategyReplace)

4. Master Data Management

// Export current master data
packets, _ := adapter.ExportTable(ctx, "products")
xlsx.ToXLSX(packets[0], "products_master.xlsx", "Products")

// Business team updates in Excel:
// - Add new products
// - Update prices
// - Modify descriptions
// - Set stock levels

// Reimport updated data
packet, _ := xlsx.FromXLSX("products_master_updated.xlsx", "Products")
adapter.ImportPacket(ctx, packet, adapters.StrategyReplace)

5. RabbitMQ + MSSQL Integration

// Export from MSSQL
mssqlAdapter, _ := mssql.NewAdapter()
packets, _ := mssqlAdapter.ExportTable(ctx, "orders")

// Save to Excel for review
xlsx.ToXLSX(packets[0], "orders_for_review.xlsx", "Orders")

// After review, send to RabbitMQ
packet, _ := xlsx.FromXLSX("orders_approved.xlsx", "Orders")

// Convert to TDTP XML and publish
for _, row := range packet.Data.Rows {
    rabbitMQ.Publish(ctx, packet)
}

Type Mapping

Excel File Format

Header Row

Headers show field information:

• Format: field_name (TYPE)
• Primary keys: field_name (TYPE) *

Example:

order_id (INTEGER) * | customer (TEXT) | total (DECIMAL) | shipped (BOOLEAN)

Data Rows

Standard Excel data format:

• Numbers without quotes
• Booleans as TRUE/FALSE
• Dates formatted
• Text as-is

Styling

• Header row: Bold white text on blue background
• Numbers: Right-aligned with appropriate decimals
• Dates: Date format applied
• Text: Left-aligned

Performance

Tested with:

• ✅ 1,000 rows: < 100ms export, < 50ms import
• ✅ 10,000 rows: < 500ms export, < 200ms import
• ✅ 100,000 rows: < 5s export, < 2s import

Limitations

• Maximum Excel row limit: 1,048,576 rows
• For larger datasets, export multiple files or use database directly
• BLOB fields are stored as text (base64)
• Complex data types (arrays, JSON) are stored as text

Best Practices

1. Use descriptive sheet names: Match table names for clarity
2. Include primary keys: Mark with * for identification
3. Validate data in Excel: Before importing to database
4. Version control: Keep Excel files in version control
5. Document changes: Add comments in Excel for audit trail
6. Test imports: Use staging environment first
7. Backup data: Before bulk imports

Error Handling

// Always check errors
packet, err := xlsx.FromXLSX("data.xlsx", "Sheet1")
if err != nil {
    log.Printf("Failed to import: %v", err)
    // Handle error:
    // - Check file exists
    // - Check file format
    // - Check sheet name
    // - Check header format
    return
}

// Validate packet before database import
if len(packet.Data.Rows) == 0 {
    log.Println("Warning: No data rows in file")
    return
}

// Import with error handling
err = adapter.ImportPacket(ctx, packet, adapters.StrategyReplace)
if err != nil {
    log.Printf("Database import failed: %v", err)
    // Handle error:
    // - Check database connection
    // - Check table exists
    // - Check data types match
    // - Check constraints
    return
}

Examples

See examples/04-tdtp-xlsx for complete working examples.

Contributing

Contributions welcome! Areas for improvement:

• Support for Excel formulas
• Cell validation rules
• Conditional formatting
• Multiple sheets per file
• Template support
• Macro support

License

MIT License - see LICENSE file for details