Skip to main content

Common Code Patterns

Frequently used code patterns for NetSuite report development.

1. Loading and Processing NetSuite Data​

Basic Data Loading​

import json
import pandas as pd

# Load from JSON file
with open('test_data/aging_data.json', 'r', encoding='utf-8') as f:
    aging_data = json.load(f)

# Convert to DataFrame
df = pd.DataFrame(aging_data)

# Clean up
if 'rownumber' in df.columns:
    df = df.drop(columns=['rownumber'])
df = df.fillna(0)

Loading from Pyodide​

from js import array_aging
import json
import pandas as pd

aging_data = json.loads(array_aging)
df = pd.DataFrame(aging_data).fillna(0)

2. Separating Data by Type​

AR/AP Split​

# Split by account type
ar_df = df[df['accttype'] == 'AcctRec'].drop(columns=['accttype'])
ap_df = df[df['accttype'] == 'AcctPay'].drop(columns=['accttype'])

print(f"AR records: {len(ar_df)}")
print(f"AP records: {len(ap_df)}")

Currency Split​

currencies = df['currency'].unique().tolist()
print(f"Currencies: {currencies}")

for curr in currencies:
    curr_df = df[df['currency'] == curr]
    print(f"{curr}: {len(curr_df)} records")

3. Calculating Totals and Overdue​

Overdue Calculation​

# Calculate overdue (sum of all aging buckets except current)
df['FC Overdue'] = (df['foreignamount_h30'] +
                    df['foreignamount_h60'] +
                    df['foreignamount_h60_above'])

df['Rp Overdue'] = (df['amount_h30'] +
                    df['amount_h60'] +
                    df['amount_h60_above'])

Total Calculation​

# Calculate total (current + all overdue)
df['FC Total'] = (df['foreignamount_current'] +
                  df['foreignamount_h30'] +
                  df['foreignamount_h60'] +
                  df['foreignamount_h60_above'])

df['Rp Total'] = (df['amount_current'] +
                  df['amount_h30'] +
                  df['amount_h60'] +
                  df['amount_h60_above'])

4. Building Report with Subtotals​

Per-Currency Subtotals​

final_df = pd.DataFrame()
empty_row = pd.DataFrame("", index=range(1), columns=df.columns)

for curr in currencies:
    # Get data for this currency
    temp_curr_df = df[df['currency'] == curr].sort_values(by=['Rp Total'])

    # Create subtotal row
    temp_total_df = pd.DataFrame(columns=temp_curr_df.columns)
    temp_total_df.loc[f"Subtotal {curr}"] = temp_curr_df.sum(numeric_only=True)

    # Combine: empty row + data + empty row + subtotal
    final_df = pd.concat([final_df, empty_row, temp_curr_df, empty_row, temp_total_df])

# Reset index for clean output
final_df = final_df.reset_index(drop=True)

Grand Total​

# Add grand total at the end
grand_total = pd.DataFrame(columns=df.columns)
grand_total.loc['Grand Total'] = df.sum(numeric_only=True)
grand_total['name'] = 'GRAND TOTAL'

final_df = pd.concat([final_df, empty_row, grand_total])

5. Excel Report Structure​

Standard Report Layout​

import io
import pandas as pd

bio = io.BytesIO()

with pd.ExcelWriter(bio, engine='xlsxwriter') as writer:
    workbook = writer.book
    worksheet = workbook.add_worksheet('Report')

    # Row layout:
    # 0-2: Title area (company name, report name, date)
    # 3: Empty
    # 4-5: Column headers
    # 6+: Data

    # Define formats
    title_fmt = workbook.add_format({
        'bold': True, 'font_size': 14, 'align': 'center'
    })
    header_fmt = workbook.add_format({
        'bold': True, 'bg_color': '#4472C4', 'font_color': 'white',
        'align': 'center', 'border': 1
    })
    data_fmt = workbook.add_format({'border': 1})
    number_fmt = workbook.add_format({'border': 1, 'num_format': '#,##0'})

    # Write title (rows 0-2)
    worksheet.merge_range(0, 0, 0, 10, 'PT. COMPANY NAME', title_fmt)
    worksheet.merge_range(1, 0, 1, 10, 'Aging Report', title_fmt)
    worksheet.merge_range(2, 0, 2, 10, f'As of {asofdate}', title_fmt)

    # Write headers (rows 4-5)
    # ... header writing code ...

    # Write data starting at row 6
    data_start_row = 6
    for idx, row in final_df.iterrows():
        excel_row = data_start_row + idx
        # Write each cell...

Column Width Configuration​

# Set column widths for common report layout
worksheet.set_column('A:A', 30)   # Customer/Vendor name
worksheet.set_column('B:B', 10)   # Currency
worksheet.set_column('C:N', 15)   # Numeric columns

6. Date Formatting​

Parsing Date Strings​

from datetime import datetime

# Parse date from string
asof_date = datetime.strptime(asofdate, '%d/%m/%Y')

# Format for display
formatted_date = asof_date.strftime('%d %B %Y')  # "15 December 2024"

Date in Report Title​

# Common date formats for reports
date_short = asof_date.strftime('%d/%m/%Y')      # "15/12/2024"
date_long = asof_date.strftime('%d %B %Y')       # "15 December 2024"
date_month = asof_date.strftime('%B %Y')         # "December 2024"

7. Currency Formatting​

Indonesian Rupiah Display​

# Format number as Indonesian Rupiah
def format_idr(value):
    return f"Rp {value:,.0f}".replace(",", ".")

# Example
amount = 1500000
print(format_idr(amount))  # "Rp 1.500.000"

Multiple Currency Display​

def format_currency(value, currency):
    if currency == 'IDR':
        return f"Rp {value:,.0f}"
    elif currency == 'USD':
        return f"$ {value:,.2f}"
    elif currency == 'SGD':
        return f"S$ {value:,.2f}"
    else:
        return f"{currency} {value:,.2f}"

8. Row Position Tracking​

Dynamic Row Management​

# Track current row position
current_row = 6  # Start after headers

# Write data and track position
for idx, row in df.iterrows():
    worksheet.write(current_row, 0, row['name'], data_fmt)
    worksheet.write(current_row, 1, row['amount'], number_fmt)
    current_row += 1

# Add subtotal after data
worksheet.write(current_row, 0, 'Subtotal', subtotal_fmt)
worksheet.write(current_row, 1, df['amount'].sum(), subtotal_fmt)
current_row += 1

# Record row ranges for later reference
ar_row_start = 6
ar_row_end = current_row - 1

9. Conditional Cell Formatting​

Format Based on Value​

# Apply different format based on value
for idx, row in df.iterrows():
    excel_row = data_start + idx
    value = row['amount']

    if value > 1000000:
        fmt = high_value_fmt
    elif value < 0:
        fmt = negative_fmt
    else:
        fmt = normal_fmt

    worksheet.write(excel_row, col, value, fmt)

Subtotal Row Detection​

# Check if row is a subtotal row
for idx, row in final_df.iterrows():
    is_subtotal = 'Subtotal' in str(row.get('name', ''))
    is_grandtotal = 'Grand Total' in str(row.get('name', ''))

    if is_grandtotal:
        row_fmt = grandtotal_fmt
    elif is_subtotal:
        row_fmt = subtotal_fmt
    else:
        row_fmt = data_fmt

10. Memory-Efficient Processing​

Process Large Datasets​

def process_in_batches(data, batch_size=1000):
    """Process large datasets in batches"""
    results = []

    for i in range(0, len(data), batch_size):
        batch = data[i:i + batch_size]
        df_batch = pd.DataFrame(batch)
        # Process batch...
        results.append(df_batch)

    return pd.concat(results, ignore_index=True)

Cleanup After Processing​

import gc

# After creating Excel file
del df, final_df, ar_df, ap_df
gc.collect()

11. Error Handling​

Safe Column Access​

# Check column exists before using
if 'rownumber' in df.columns:
    df = df.drop(columns=['rownumber'])

# Get column with default
def safe_get(df, column, default=0):
    return df[column] if column in df.columns else default

Try-Catch Pattern​

try:
    df = pd.DataFrame(data)
    result = process_data(df)
except KeyError as e:
    print(f"Missing column: {e}")
    raise
except Exception as e:
    print(f"Processing error: {e}")
    raise

12. Quick Reference​

Data Loading​

df = pd.DataFrame(json.loads(data_str)).fillna(0)

Split by Type​

ar_df = df[df['type'] == 'AR']
ap_df = df[df['type'] == 'AP']

Calculate Totals​

df['total'] = df['col1'] + df['col2'] + df['col3']

Group and Subtotal​

grouped = df.groupby('currency').sum(numeric_only=True)

Write to Excel​

worksheet.write(row, col, value, format)

Track Position​

current_row += 1