Payroll Spreadsheet

Build a multi-sheet payroll workbook with employee data, computed totals, and summary formulas.

The Code

package main

import (
	"fmt"
	"log"

	"github.com/jpoz/werkbook"
)

func main() {
	wb := werkbook.New(werkbook.FirstSheet("Payroll"))
	sheet := wb.Sheet("Payroll")

	// Headers
	headers := []string{"Name", "Department", "Hours", "Rate", "Gross Pay"}
	for i, h := range headers {
		col := werkbook.ColumnNumberToName(i + 1)
		sheet.SetValue(col+"1", h)
	}

	// Style the header row
	sheet.SetRangeStyle("A1:E1", &werkbook.Style{
		Font: &werkbook.Font{Bold: true, Size: 11, Color: "FFFFFF"},
		Fill: &werkbook.Fill{Color: "2B579A"},
		Alignment: &werkbook.Alignment{
			Horizontal: werkbook.HAlignCenter,
		},
	})

	// Employee data
	employees := []struct {
		name string
		dept string
		hrs  float64
		rate float64
	}{
		{"Alice Chen", "Engineering", 80, 65.00},
		{"Bob Martinez", "Engineering", 76, 60.00},
		{"Carol Davis", "Design", 80, 55.00},
		{"Dan Wilson", "Marketing", 72, 50.00},
		{"Eve Johnson", "Design", 80, 58.00},
	}

	for i, emp := range employees {
		row := i + 2
		r := fmt.Sprint(row)
		sheet.SetValue("A"+r, emp.name)
		sheet.SetValue("B"+r, emp.dept)
		sheet.SetValue("C"+r, emp.hrs)
		sheet.SetValue("D"+r, emp.rate)
		// Gross pay = hours * rate
		sheet.SetFormula("E"+r, fmt.Sprintf("C%d*D%d", row, row))
	}

	// Format currency columns
	for row := 2; row <= 6; row++ {
		r := fmt.Sprint(row)
		sheet.SetStyle("D"+r, &werkbook.Style{NumFmt: "$#,##0.00"})
		sheet.SetStyle("E"+r, &werkbook.Style{NumFmt: "$#,##0.00"})
	}

	// Summary section
	summaryRow := len(employees) + 3
	sr := fmt.Sprint(summaryRow)

	sheet.SetValue("A"+sr, "Total Employees")
	sheet.SetFormula("B"+sr, "COUNTA(A2:A6)")

	sheet.SetValue("A"+fmt.Sprint(summaryRow+1), "Total Gross Pay")
	sheet.SetFormula("B"+fmt.Sprint(summaryRow+1), "SUM(E2:E6)")
	sheet.SetStyle("B"+fmt.Sprint(summaryRow+1), &werkbook.Style{
		NumFmt: "$#,##0.00",
		Font:   &werkbook.Font{Bold: true},
	})

	sheet.SetValue("A"+fmt.Sprint(summaryRow+2), "Average Gross Pay")
	sheet.SetFormula("B"+fmt.Sprint(summaryRow+2), "AVERAGE(E2:E6)")
	sheet.SetStyle("B"+fmt.Sprint(summaryRow+2), &werkbook.Style{
		NumFmt: "$#,##0.00",
	})

	// Column widths
	sheet.SetColumnWidth("A", 18)
	sheet.SetColumnWidth("B", 14)
	sheet.SetColumnWidth("E", 14)

	// Add an Inventory sheet
	inv, _ := wb.NewSheet("Inventory")

	invHeaders := []string{"Item", "Quantity", "Unit Cost", "Total"}
	for i, h := range invHeaders {
		col := werkbook.ColumnNumberToName(i + 1)
		inv.SetValue(col+"1", h)
	}

	items := []struct {
		name string
		qty  int
		cost float64
	}{
		{"Laptops", 10, 1200.00},
		{"Monitors", 15, 450.00},
		{"Keyboards", 25, 75.00},
	}

	for i, item := range items {
		row := i + 2
		r := fmt.Sprint(row)
		inv.SetValue("A"+r, item.name)
		inv.SetValue("B"+r, item.qty)
		inv.SetValue("C"+r, item.cost)
		inv.SetFormula("D"+r, fmt.Sprintf("B%d*C%d", row, row))
	}

	inv.SetValue("A5", "Grand Total")
	inv.SetFormula("D5", "SUM(D2:D4)")

	if err := wb.SaveAs("payroll.xlsx"); err != nil {
		log.Fatal(err)
	}
}

What It Produces

Payroll sheet:

Inventory sheet:

Key Concepts Used

• SetFormula — C*D for computed totals, SUM and AVERAGE for aggregation
• SetRangeStyle — styling an entire header row at once
• NumFmt — formatting currency with $#,##0.00
• Multiple sheets — payroll data and inventory in the same workbook
• COUNTA — counting non-empty cells for employee count