Convert JSON to Excel in Golang: A Simple Guide with Code Example

Introduction

In today's data-driven world, converting data between different formats is a common task for developers. One such conversion is transforming JSON data into an Excel file. JSON (JavaScript Object Notation) is widely used for storing and exchanging data, while Excel files are essential for data analysis and reporting.

In this tutorial, we'll build a Go application that converts JSON data to an Excel file. We'll use the Go package tealeg/xlsx to create Excel files programmatically. This guide will help you learn how to handle JSON data, read it from a file, and write it to an Excel spreadsheet using Go.

Prerequisites

Before we dive into the code, make sure you have the following:

Go: Installed on your machine (version 1.16 or above is recommended).
tealeg/xlsx Package: A Go library for creating and writing XLSX files.

To install the tealeg/xlsx package, run:

go get github.com/tealeg/xlsx

Feel Free to add you own customizations and features.

Step-by-Step Guide

1. Create the JSON Data

First, we will create a JSON file to hold the data we want to convert to Excel. The CreateJson function takes any data in the form of a Go interface, marshals it to JSON, and writes it to a file named data.json.

package main

import (
    "encoding/json"
    "fmt"
    "io"
    "os"

    "github.com/tealeg/xlsx"
)

// CreateJson creates a JSON file from the given data
func CreateJson(data interface{}) {
    file, err := os.Create("data.json")
    if err != nil {
        fmt.Println("Error in creating file:", err)
        return
    }
    defer file.Close()

    jsonData, err := json.Marshal(data)
    if err != nil {
        fmt.Println("Error in marshaling data:", err)
        return
    }

    _, err = file.Write(jsonData)
    if err != nil {
        fmt.Println("Error in writing to file:", err)
        return
    }

    fmt.Println("JSON file created successfully")
}

2. Convert JSON to Excel

The JsonToExcel function reads data from the data.json file, converts it into an Excel file, and saves it as data.xlsx.

// JsonToExcel reads a JSON file and converts it to an Excel file
func JsonToExcel() {
    // Open the JSON file
    jsonfile, err := os.Open("data.json")
    if err != nil {
        fmt.Println("Error in opening file:", err)
        return
    }
    defer jsonfile.Close()

    // Read all data from the JSON file
    data, err := io.ReadAll(jsonfile)
    if err != nil {
        fmt.Println("Error in reading file:", err)
        return
    }

    // Unmarshal the data into a Go interface
    var result interface{}
    if err := json.Unmarshal(data, &result); err != nil {
        fmt.Println("Error in unmarshaling the data:", err)
        return
    }

    // Create a new Excel file
    file := xlsx.NewFile()
    sheet, err := file.AddSheet("Sheet1")
    if err != nil {
        fmt.Println("Error in creating Excel sheet:", err)
        return
    }

    // Process the JSON data based on its type
    switch v := result.(type) {
    case []interface{}:
        // Handle JSON array
        if len(v) > 0 {
            if obj, ok := v[0].(map[string]interface{}); ok {
                // Add headers
                headerRow := sheet.AddRow()
                for key := range obj {
                    headerRow.AddCell().Value = key
                }

                // Add data rows
                for _, item := range v {
                    row := sheet.AddRow()
                    if obj, ok := item.(map[string]interface{}); ok {
                        for _, value := range obj {
                            row.AddCell().Value = fmt.Sprintf("%v", value)
                        }
                    }
                }
            }
        }
    case map[string]interface{}:
        // Handle single JSON object
        headerRow := sheet.AddRow()
        dataRow := sheet.AddRow()
        for key, value := range v {
            headerRow.AddCell().Value = key
            dataRow.AddCell().Value = fmt.Sprintf("%v", value)
        }
    default:
        fmt.Println("Unsupported JSON structure")
        return
    }

    // Save the Excel file
    err = file.Save("data.xlsx")
    if err != nil {
        fmt.Println("Error in saving Excel file:", err)
        return
    }

    fmt.Println("Excel file successfully created with the given data")
}

3. Putting It All Together

The main function initializes example JSON data, calls the CreateJson function to generate a JSON file, and then converts that JSON file into an Excel file using JsonToExcel.

func main() {
    // Example JSON data
    data := []map[string]interface{}{
        {"name": "Sundarm", "age": 23, "city": "City A"},
        {"name": "Aman", "age": 26, "city": "City B"},
        {"name": "Dheeraj", "age": 25, "city": "City C"},
        {"name": "Avnish", "age": 21, "city": "City D"},
    }

    CreateJson(data)  // Create JSON file
    JsonToExcel()     // Convert JSON to Excel
}

Explanation of Key Steps

Creating JSON: The CreateJson function takes any data type that can be marshaled into JSON, writes it to a file, and handles any errors encountered during this process.
Reading JSON: The JsonToExcel function opens the JSON file and reads its content into memory.
Processing Data: Depending on whether the JSON represents an array of objects or a single object, the function appropriately creates headers and rows in the Excel sheet.
Writing to Excel: The processed data is written into an Excel file using the tealeg/xlsx package, and the file is saved to disk.

Running the Application

To run the code:

Save the above code into a file named main.go.
Execute the following command in your terminal:

go run main.go

This will generate a data.json file and an data.xlsx file in the same directory.

Conclusion

Converting JSON data to an Excel file in Go is straightforward and efficient using the tealeg/xlsx package. This tutorial provides a foundation for working with different data formats in Go. You can extend this example to handle more complex JSON structures or different Excel formatting needs.