Julia for Excel users

[technocrat]

Use your own spreadsheet instead of the demo, and if you do, the fake data will be bypassed

# ==============================================================================
# Julia Starter Template for Excel Users
# Mission: Load a XLSX, clean it, and run a "Pivot Table" summary.
# script and spreadsheet
==============================================================================

using CSV
using DataFrames 
using Statistics
using XLSX

# 1. LOAD DATA 
# In Excel: File > Open
# In Julia: We read the file into a 'DataFrame' (think of it as a virtual sheet)
file_path = "sales.xlsx" 

# Note: Check if file exists before loading
if isfile(file_path)
    df = DataFrame(XLSX.readtable(file_path, 1))
    println("Successfully loaded $(nrow(df)) rows.")
else
    # Create dummy data for testing if no file is present
    df = DataFrame(
        Region = ["East", "West", "East", "North", "West"],
        Sales = [1200.50, 850.00, 2100.00, 400.25, 1500.75],
        Category = ["Tech", "Office", "Tech", "Furniture", "Office"]
    )
    println("Using sample data for demonstration...")
end

# 2. THE "CALCULATED COLUMN"
# adjust to your vareiable names
# In Excel: =B2 * 0.08 (Tax Calculation)
# In Julia: Use the '.' for element-wise math (applying to every row at once)
df.Tax = df.Sales .* 0.08

# 3. FILTERING
# In Excel: Data > Filter > "Tech"
# In Julia: Create a subset
tech_sales = subset(df, :Category => x -> x .== "Tech")

# 4. THE "PIVOT TABLE"
# In Excel: Insert > Pivot Table (Rows: Region, Values: Sum of Sales)
# In Julia: GroupBy and Combine
pivot_summary = combine(groupby(df, :Region), 
    :Sales => sum => :Total_Sales,
    :Sales => mean => :Average_Ticket
)

# 5. EXPORT
# In Excel: File > Save As
CSV.write("julia_report_output.csv", pivot_summary)

println("--- Summary Report ---")
show(pivot_summary)

which will output the program and result

Julia> # ==============================================================================
       # Julia Starter Template for Excel Users
       # Mission: Load a CSV, clean it, and run a "Pivot Table" summary.
       # ==============================================================================

       using CSV

julia> using DataFrames

julia> using Statistics

julia> using XLSX

julia> # 1. LOAD DATA 
       # In Excel: File > Open
       # In Julia: We read the file into a 'DataFrame' (think of it as a virtual sheet)
       file_path = "sales.xlsx"
"sales.xlsx"

julia> # Note: Check if file exists before loading
       if isfile(file_path)
           df = DataFrame(XLSX.readtable(file_path, 1))
           println("Successfully loaded $(nrow(df)) rows.")
       else
           # Create dummy data for testing if no file is present
           df = DataFrame(
               Region = ["East", "West", "East", "North", "West"],
               Sales = [1200.50, 850.00, 2100.00, 400.25, 1500.75],
               Category = ["Tech", "Office", "Tech", "Furniture", "Office"]
           )
           println("Using sample data for demonstration...")
       end
Successfully loaded 5 rows.

julia> # 2. THE "CALCULATED COLUMN"
       # adjust to your vareiable names
       # In Excel: =B2 * 0.08 (Tax Calculation)
       # In Julia: Use the '.' for element-wise math (applying to every row at once)
       df.Tax = df.Sales .* 0.08
5-element Vector{Float64}:
 100.0
  68.0
 168.0
  32.02
 120.06

julia> # 3. FILTERING
       # In Excel: Data > Filter > "Tech"
       # In Julia: Create a subset
       tech_sales = subset(df, :Category => x -> x .== "Tech")
2×4 DataFrame
 Row │ Region  Sales  Category  Tax     
     │ Any     Any    Any       Float64 
─────┼──────────────────────────────────
   1 │ East    1250   Tech        100.0
   2 │ East    2100   Tech        168.0

julia> # 4. THE "PIVOT TABLE"
       # In Excel: Insert > Pivot Table (Rows: Region, Values: Sum of Sales)
       # In Julia: GroupBy and Combine
       pivot_summary = combine(groupby(df, :Region), 
           :Sales => sum => :Total_Sales,
           :Sales => mean => :Average_Ticket
       )
3×3 DataFrame
 Row │ Region  Total_Sales  Average_Ticket 
     │ Any     Float64      Float64        
─────┼─────────────────────────────────────
   1 │ East        3350.0          1675.0
   2 │ West        2350.75         1175.38
   3 │ North        400.25          400.25

julia> # 5. EXPORT
       # In Excel: File > Save As
       CSV.write("julia_report_output.csv", pivot_summary)
"julia_report_output.csv"

julia> println("--- Summary Report ---")
--- Summary Report ---

julia> show(pivot_summary)
3×3 DataFrame
 Row │ Region  Total_Sales  Average_Ticket 
     │ Any     Float64      Float64        
─────┼─────────────────────────────────────
   1 │ East        3350.0          1675.0
   2 │ West        2350.75         1175.38
   3 │ North        400.25          400.25
julia> ```

(Thanks to @vzion for pointing out errors in the original.)

[vzion]

Nice, but you should update your template as it doesn’t contain the same code as in the output and doesn’t work as is (you don’t load the CSV package you are using at the end and you try to load a csv file with the XLSX package…).

And for Excel users, I tend to show them the DataFramesMeta package syntax first, like in the pumas.ai DataFrame tutorial, with a pinch of @chain @rsubset @rtransform and @by macros. I think it makes the code less intimidating than the vanillia DataFrames syntax.

[TimG]

The pumas.ai tutorial (which I hadn’t seen before now) is a little out of date with respect to reading and writing Excel files using XLSX.jl. It seems still to use the pre v0.8 style. The current version of XLSX.jl is 0.10.4.

There was a query here recently that seems to have been (mis)informed by this.

[vzion]

About the XLSX package, I really hope you’ll be added as a main contributor to the package soon, @TimG. All your work there deserves to be merge in a new release as it adds many formating features we’re waiting for a few years now !
And I guess a fork to a new XLSX2 package would be a bit sad…

[technocrat]

Thanks for the catch @vzion. That’ll learn me always to do this in Pluto in a fresh environment.