Excel Pajak

Employee tax data extraction and Excel report generation tool for Indonesian payroll processing.

Overview

.NET 10.0 console application that extracts employee tax data from PostgreSQL multi-schema databases and generates Excel reports using NPOI library.

Prerequisites

• .NET 10.0 SDK or later
• PostgreSQL database
• Valid connection string in configuration

Project Structure

excel_pajak/
├── Models/               # Data models (EmployeeInfo, EmployeePayrollInfo)
├── Services/             # Static services (DatabaseService, ExcelService)
├── Examples/             # Code examples
├── appsettings.json      # Application configuration
└── excel_pajak.csproj    # Main project

excel_pajak_test/         # MSTest unit tests

Configuration

Edit excel_pajak/appsettings.json to configure:

{
  "ConnectionStrings": {
    "DefaultConnection": "Host=localhost;Database=payroll;Username=your_user;Password=your_password"
  },
  "ExcelSettings": {
    "TemplatePath": "simulasi_perhitungan_ak.xlsx",
    "OutputDirectory": "Output"
  }
}

Building

# Build entire solution
dotnet build

# Build specific project
dotnet build excel_pajak
dotnet build excel_pajak_test

Running

# Run with default settings
dotnet run --project excel_pajak

# Run with command-line arguments
dotnet run --project excel_pajak -- --schema=company_2024 --year=2024

# Run specific schema and year
dotnet run --project excel_pajak -- --schema=company_2025 --year=2025

Command-Line Arguments

Argument	Description	Required
--schema	Database schema name	Yes
--year	Tax year (4 digits)	Yes
--help	Show help information	No

Testing

# Run all tests
dotnet test

# Run specific test class
dotnet test --filter "FullyQualifiedName~DatabaseServiceTests"

# Run specific test method
dotnet test --filter "FullyQualifiedName~DatabaseServiceTests.ExecuteScalar_NullConnectionString_ThrowsArgumentException"

# Run with detailed output
dotnet test --logger "console;verbosity=detailed"

Workflow

1. Configuration Phase

• Load connection string from appsettings.json
• Validate schema name and year parameters
• Initialize DatabaseService with connection string

2. Data Extraction Phase

• Connect to PostgreSQL database
• Execute query to extract employee tax data
• Query uses json_agg(row_to_json(t)) for JSON aggregation
• Placeholder replacement: {schema} in query template

3. Excel Generation Phase

• Load template Excel file (simulasi_perhitungan_ak.xlsx)
• Write employee data to specific cells
• Optionally refresh formulas
• Save to Output directory with naming convention: {EmployeeNumber}_{Year}.xlsx

4. Output Phase

• Generate Excel files in Output/ directory
• Each file named after employee number and year
• Example: AA001_2025.xlsx

Key Services

DatabaseService

• Static service for PostgreSQL operations
• Synchronous database operations using Npgsql
• Query execution with schema placeholders

ExcelService

• Excel file manipulation using NPOI
• Template loading and data writing
• Formula refresh support
• File overwrite control

ConfigurationService

• Manages application settings
• Loads configuration from appsettings.json

Architecture

• Pattern: Static service classes with Initialize()
• Database: Synchronous operations (no async)
• Serialization: System.Text.Json with snake_case
• Validation: Regex-based schema/year validation

Anti-Patterns Avoided

• No async database operations (synchronous by design)
• No connection pooling in services
• Static state requires explicit Initialize() before use

Troubleshooting

Connection Issues

• Verify PostgreSQL server is running
• Check connection string in appsettings.json
• Ensure database user has appropriate permissions

Schema Not Found

• Verify schema name exists in database
• Schema names should match pattern: ^[a-zA-Z0-9_-]+$

Excel Output Issues

• Ensure Output directory exists
• Check write permissions
• Verify template file exists

License

Internal use only.