xlsrpt

package module
v0.9.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Jan 22, 2021 License: MIT Imports: 10 Imported by: 0

README

made-with-Go GoReportCard

xlsrpt

Excel Reports Generator Package

Overview

Easy & Flexible Excel Report Generator Package for go.

Usage
For detailed documentation and examples please see https://godoc.org/github.com/moisoto/xlsrpt

There are four ways to generate a report. Each of the following functions can be used depending your needs:

  • ExcelFromDB()

    • Allows generation of report with minimal effort. Uses reflect to infer column types.

  • ExcelReport()

    • Allows single sheet report generation. You must define a struct with fields for each column.
    • A function that loads data into a map of such struct must be implemented.

  • ExcelMultiSheetFromDB()

    • Allows multiple sheets report generation with minimal effort. Uses reflect to infer column types.

  • ExcelMultiSheet()

    • Allows multiple sheets report generation.
    • You must define a struct for each sheet, each one with fields for each column to be added.
    • One or several functions that load data into a map of such struct(s) must be implemented.
Features
  • Generate simple Excel Report by providing *sql.DB object and a Query String
  • Generate more flexible, more complex Excel Report by providing a map of structures where each struct element is a record.
  • Struct Based generation can specify column names, columns to be summarized, allows specific order using unique columns.
Dependencies

This package currently depends on tealeg's xlsx v1.0.5 package. xlsrpt uses go modules, no need to worry if you tealeg/xlsx in your programs along with this package.

Project State

xlsrpt is currently in beta, all main functionality is completed and working. Pending work before first release:

  • Unit testing code
  • Code cleanup / verification
  • Commented code cleanup

Documentation

Overview

Package xlsrpt allows easy generation of Excel Reports. Report is generated from a *sql.DB datasource.

Index

Examples

Constants

This section is empty.

Variables

View Source 
var (
	// LogBench can be used to log benchmark information of report creation (unimplemented).
	LogBench bool

	// UntouchStrings can be used to leave strings untouched.
	UntouchStrings bool

	// UntouchCols can be used to set columns that must not be formatted.
	UntouchCols []string
)

Library behavior configuration variables.

View Source 
var Debug bool

Debug can be used to print debug information about Excel File generation when set to <true>.

View Source 
var Vervose bool

Vervose can be used to print information about Excel File generation when set to <true>.

Functions

func ExcelFromDB 

func ExcelFromDB(rp RepParams, db *sql.DB) error

ExcelFromDB can be used when the selected columns are not known. Uses reflect to infer data type directly from DB.

Example 
repParams := xlsrpt.RepParams{
	RepTitle:   "Customer Report",
	Query:      "SELECT CreationDate, FirstName, LastName, CustomerNumber, Balance FROM Customer;",
	NoTitleRow: true,
	AutoFilter: true}

// Open Connection to Database
database, err := dbConnect()
if err != nil {
	panic(err.Error())
}

// Just call ExcelFromDB and pass the parameters and the *sql.DB pointer
xlsrpt.ExcelFromDB(repParams, database)

func ExcelMultiSheet 

func ExcelMultiSheet(filePath string, reports []MultiSheetRep) error

ExcelMultiSheet generates a Report with Multiple Sheets using a datamap that should be loaded by your implementation of LoadRows() function.

Example 
// Open Connection to Database
database, err := dbConnect()
if err != nil {
	panic(err.Error())
}

// repExampleMap and LoadRows() implementation for repExampleMap is located on ReportData_test.go
// Here we use the same struct (repExampleMap) for simplification since example columns are the same on both sheets
// Normally you will have several structs and several implementations of LoadRows() for each struct
var rptDataMap1 = make(repExampleMap)
var rptDataMap2 = make(repExampleMap)

var rptData1 xlsrpt.ReportData = rptDataMap1
var rptData2 xlsrpt.ReportData = rptDataMap2

repParams := []xlsrpt.MultiSheetRep{
	{
		Params: xlsrpt.RepParams{
			RepTitle: "All Accounts",
			RepCols: []xlsrpt.RepColumns{
				{Title: "Date Created", SumFlag: false},
				{Title: "First Name", SumFlag: false},
				{Title: "Last Name", SumFlag: false},
				{Title: "Customer Number", SumFlag: false},
				{Title: "Customer Balance", SumFlag: true}},
			Query:      "SELECT CreationDate, FirstName, LastName, CustomerNumber, Balance FROM Customer;",
			AutoFilter: true},
		Data: rptData1,
		DB:   database},
	{
		Params: xlsrpt.RepParams{
			RepTitle: "VIP Accounts",
			RepSheet: "VIPs", // You can specify a sheet name, otherwise RepTitle will be used as the sheet name
			RepCols: []xlsrpt.RepColumns{
				{Title: "Date Created", SumFlag: false},
				{Title: "First Name", SumFlag: false},
				{Title: "Last Name", SumFlag: false},
				{Title: "Customer Number", SumFlag: false},
				{Title: "Customer Balance", SumFlag: true}},
			Query:      "SELECT CreationDate, FirstName, LastName, CustomerNumber, Balance FROM Customer WHERE vip=1;",
			AutoFilter: true},
		Data: rptData2,
		DB:   database}}

// Just call ExcelMultiSheet and pass the file name and report parameters.
xlsrpt.ExcelMultiSheet("Customer Report.xlsx", repParams)

func ExcelMultiSheetFromDB 

func ExcelMultiSheetFromDB(filePath string, reports []MultiSheetRep) error

ExcelMultiSheetFromDB generates a Report with Multiple Sheets. Uses reflect to infer data type directly from DB.

Example 
// Open Connection to Database
database, err := dbConnect()
if err != nil {
	panic(err.Error())
}

repParams := []xlsrpt.MultiSheetRep{
	{
		Params: xlsrpt.RepParams{
			RepTitle:   "All Customers",
			Query:      "SELECT CreationDate, FirstName, LastName, CustomerNumber, Balance FROM Customer;",
			NoTitleRow: true,
			AutoFilter: true},
		DB: database},
	{
		Params: xlsrpt.RepParams{
			RepTitle:   "VIP Customers",
			Query:      "SELECT CreationDate, FirstName, LastName, CustomerNumber, Balance FROM Customer WHERE vip=1;",
			NoTitleRow: false,
			AutoFilter: true},
		DB: database}}

// Just call ExcelMultiSheetFromDB and pass the file name and report parameters.
xlsrpt.ExcelMultiSheetFromDB("Customer Report.xlsx", repParams)

func ExcelReport 

func ExcelReport(rp RepParams, rptData ReportData, db *sql.DB) error

ExcelReport generates excel report using a datamap that should be loaded by your implementation of LoadRows() function

Example 
repParams := xlsrpt.RepParams{
	RepTitle: "Customer Report",
	RepCols: []xlsrpt.RepColumns{
		{Title: "Date Created", SumFlag: false},
		{Title: "First Name", SumFlag: false},
		{Title: "Last Name", SumFlag: false},
		{Title: "Customer Number", SumFlag: false},
		{Title: "Customer Balance", SumFlag: true}},
	Query: "SELECT CreationDate, FirstName, LastName, CustomerNumber, Balance FROM Customer;"}

// Open Connection to Database
database, err := dbConnect()
if err != nil {
	panic(err.Error())
}

// repExampleMap and LoadRows() implementation for repExampleMap is located on ReportData_test.go
var rptDataMap = make(repExampleMap)
var rptData xlsrpt.ReportData = rptDataMap

// Call ExcelReport with the report parameters, dataMap and database pointer.
// Check ReportData_test.go to see how to implement the LoadRows() function.
xlsrpt.ExcelReport(repParams, rptData, database)

Types

type CellCurrency 

type CellCurrency float32

CellCurrency - Currency Cell Type.

type CellDate 

type CellDate time.Time

CellDate - Date Cell Type.

type CellDecimal 

type CellDecimal float64

CellDecimal - Decimal Cell Type (Number with commas and 2 decimal places).

type CellInt 

type CellInt int

CellInt - Integer Cell Type.

type CellNumeric 

type CellNumeric float64

CellNumeric - Numeric Cell Type (Number with no format).

type CellPercent 

type CellPercent float64

CellPercent - Percent Cell Type.

type CellStr 

type CellStr string

CellStr - String Cell Type.

type MultiSheetRep 

type MultiSheetRep struct {
	Params RepParams
	Data   ReportData
	DB     *sql.DB
}

MultiSheetRep type is used for multiple sheets reports.

type RepColumns 

type RepColumns struct {
	Title   string
	SumFlag bool
}

RepColumns - Report Columns Definition.

type RepParams 

type RepParams struct {
	RepTitle   string
	RepSheet   string
	RepCols    []RepColumns
	Query      string
	FilePath   string
	AltBg      bool
	AutoFilter bool
	NoTitleRow bool
}

RepParams - Parameters for Report Generation.

type ReportData 

type ReportData interface {
	LoadRows(rows *sql.Rows) error
}

ReportData defines LoadRows() function that must be implemented

Implement this by creating a struct with the fields you want to use as columns in the report. Then create a map with items of the structure type (map key can be any type).

Note that this is only needed when using ExcelReport() or ExcelMultiSheet() functions.

Example 
package main

import (
	"database/sql"
	"fmt"
	"strconv"

	"github.com/moisoto/xlsrpt"
)

type repExampleData struct {
	DateCreated   xlsrpt.CellStr
	FirstName     xlsrpt.CellStr
	LastName      xlsrpt.CellStr
	AccountNumber xlsrpt.CellInt
	Balance       xlsrpt.CellCurrency
}

type repExampleMap map[string]repExampleData

func main() {
	fmt.Println("Example of Implementation of ReportData interface.")
}

// LoadRows implements LoadRows function using type repExampleMap
func (dataMap repExampleMap) LoadRows(rows *sql.Rows) error {
	mapIndex := 0
	for rows.Next() {
		mapIndex++
		var d repExampleData
		err := rows.Scan(
			&d.DateCreated, &d.FirstName, &d.LastName, &d.AccountNumber, &d.Balance)
		if err != nil {
			fmt.Println(err.Error())
			rows.Close()
			return nil
		}
		dataMap[strconv.Itoa(mapIndex)] = d
	}
	return nil
}

Output:

Example of Implementation of ReportData interface.

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.