exampe1 (tealeg/xlsx)

exampe1.go

/*
	Execution sequence:
	1) main
	2) writeExcel
	3) writeWheet
	4) iteration write row
*/

package main

import (
	"io"
	"log"
	"os"
	"strings"
	"time"

	"github.com/tealeg/xlsx/v2"
)

var (
	// this is a block of preset styles. It's best to put it in a separate file like "CustomStyles.go"
	defaultFont      = xlsx.Font{Size: 11, Bold: false, Name: "Calibri"}
	defaultAlignment = xlsx.Alignment{WrapText: true, Horizontal: "left", Vertical: "center"}
	defaultBorder    = xlsx.Border{Left: "thin", Right: "thin", Top: "thin", Bottom: "thin"}

	emptyStyle = xlsx.NewStyle()
	titleStyle = &xlsx.Style{
		Font:      xlsx.Font{Size: 14, Bold: true, Name: "Calibri"},
		ApplyFont: true}
	headerStyle = &xlsx.Style{
		Font:           xlsx.Font{Size: 11, Bold: true, Name: "Calibri"},
		ApplyFont:      true,
		Fill:           xlsx.Fill{PatternType: "solid", FgColor: "FFFFFF", BgColor: "00000000"},
		ApplyFill:      true,
		Border:         defaultBorder,
		ApplyBorder:    true,
		Alignment:      xlsx.Alignment{WrapText: true, Horizontal: "center", Vertical: "center"},
		ApplyAlignment: true}
	defaultStyle = &xlsx.Style{
		Font:           defaultFont,
		ApplyFont:      true,
		Border:         defaultBorder,
		ApplyBorder:    true,
		Alignment:      defaultAlignment,
		ApplyAlignment: true}
	redStyle = &xlsx.Style{
		Font:           defaultFont,
		ApplyFont:      true,
		Fill:           xlsx.Fill{PatternType: "solid", FgColor: "DA9694", BgColor: "00000000"},
		ApplyFill:      true,
		Border:         defaultBorder,
		ApplyBorder:    true,
		Alignment:      defaultAlignment,
		ApplyAlignment: true}
	orangeStyle = &xlsx.Style{
		Font:           defaultFont,
		ApplyFont:      true,
		Fill:           xlsx.Fill{PatternType: "solid", FgColor: "FFBC40", BgColor: "00000000"},
		ApplyFill:      true,
		Border:         defaultBorder,
		ApplyBorder:    true,
		Alignment:      defaultAlignment,
		ApplyAlignment: true}
	hyperlinkStyle = &xlsx.Style{
		Font:           xlsx.Font{Size: defaultFont.Size, Bold: defaultFont.Bold, Name: defaultFont.Name, Color: "0070C0"},
		ApplyFont:      true,
		Fill:           xlsx.Fill{PatternType: "solid", FgColor: "FFBC40", BgColor: "00000000"},
		ApplyFill:      true,
		Alignment:      defaultAlignment,
		ApplyAlignment: true}
	datetimeOptions = xlsx.DateTimeOptions{Location: time.UTC, ExcelTimeFormat: `dd.mm.yyy`}
)

const (
	formatDate     = "02.01.2006"
	formatDateTime = "02.01.2006 15:04:05"
)

// example of an entity structure
type user struct {
	Name        string
	Age         uint64
	Citizenship string
	Languages   []string
	BirthDate   time.Time
}

// simple wrapper for errors
func iferr(err error) {
	if err != nil {
		log.Fatalln(err)
	}
}

// most often, our business logic intersects with excel in the form of visualization.
// If you have the opportunity to put something in your calculations in advance, it is better to do it.
// When creating cells, we often have to allocate sections in some way.
// I believe that the logic of stylization is better embedded in the method of the entity that we need to visualize.
func (u *user) createRow(row *xlsx.Row) error {
	row.AddCell() // empty cell for serial number (this is an synthetic example for next)
	row.AddCell().SetString(u.Name)
	{
		// example of business logic
		ageRow := row.AddCell()
		ageRow.SetInt64(int64(u.Age))
		if u.Age <= 20 {
			ageRow.SetStyle(orangeStyle)
		}
	}
	{
		// example of business logic
		bdCell := row.AddCell()
		bdCell.SetDateWithOptions(u.BirthDate, datetimeOptions)
		if u.BirthDate.Day()%2 != 0 {
			bdCell.SetStyle(redStyle)
		}
	}
	row.AddCell().SetString(u.Citizenship)
	{
		// you can specifically handle data visualization in your cell just inside the method of your business entity
		row.AddCell().SetString(strings.Join(u.Languages, "\n"))
	}
	for i := range row.Cells {
		if *emptyStyle != *row.Cells[i].GetStyle() {
			// if the cell already has a style then skip it (row 34)
			continue
		}
		row.Cells[i].SetStyle(defaultStyle)
	}
	return nil
}

// Most likely, you have to create multiple sheets in a single file.
// Wrap your business logic in a separate method with a file binding.
func writeWheet(f *xlsx.File, users []user) error {
	sheet, err := f.AddSheet("users")
	iferr(err)
	{
		// setup for headers
		headers := []string{"#", "Name", "Age", "BirthDate", "Citizenship", "Languages"}
		headersRow := sheet.AddRow()
		headersRow.WriteSlice(&headers, len(headers))
		for _, c := range headersRow.Cells {
			c.SetStyle(headerStyle)
		}
	}
	// WRITE SHETT ROWS
	for i := range users {
		// This is a synthetic example.
		// You can add these strings if for some reason they can't be accessed inside your entity method
		row := sheet.AddRow()
		iferr(users[i].createRow(row))
		row.Cells[0].SetInt(i + 1)
	}
	{
		// setup visual convenience
		sheet.SetColWidth(1, 1, 5.0)
		sheet.SetColWidth(2, 2, 20.0)
		sheet.SetColWidth(3, 3, 30.0)
		sheet.SetColWidth(4, 5, 20.0)
		sheet.SetColWidth(6, 6, 25.0)
		sheet.SetColWidth(7, 7, 13.0)
	}
	return nil
}

// Most likely, you will need to send your file over http, for example.
// It is best (IMHO) to perceive working with this library through its Write method
// (logically it is similar to the WriteTo interface, but it does not return the number of bytes).
// NOTE: if for some reason you need to convert to io.Reader, then use io.Pipe()
func writeExcel(w io.Writer, data []user) error {
	file := xlsx.NewFile()
	if err := writeWheet(file, data); err != nil {
		return err
	}
	return file.Write(w)
}

func main() {
	// example fixtures
	var users = []user{
		{Name: "Max", Age: 13, Citizenship: "USA", BirthDate: time.Date(2007, 3, 15, 0, 0, 0, 0, time.Local), Languages: []string{"Eng", "Fr"}},
		{Name: "Alex", Age: 32, Citizenship: "RF", BirthDate: time.Date(1988, 5, 23, 0, 0, 0, 0, time.Local), Languages: []string{"Ru"}},
		{Name: "Martin", Age: 28, Citizenship: "UA", BirthDate: time.Date(1998, 1, 30, 0, 0, 0, 0, time.Local), Languages: []string{"Eng", "Ru"}},
		{Name: "Mary", Age: 34, Citizenship: "FR", BirthDate: time.Date(1996, 2, 17, 0, 0, 0, 0, time.Local), Languages: []string{"De", "Fr"}},
		{Name: "Bob", Age: 19, Citizenship: "DE", BirthDate: time.Date(2001, 8, 28, 0, 0, 0, 0, time.Local), Languages: []string{"Eng"}},
	}
	// save option
	// you can similarly use any interface that implements io.Writer:
	// http.ResponseWriter, *bytes.Buffer, os.Stdout, io.Pipe(), etc.
	w, err := os.Create("example1.xlsx")
	iferr(err)
	defer w.Close()
	iferr(writeExcel(w, users))
}