README
¶
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 ¶
- Variables
- func ExcelFromDB(rp RepParams, db *sql.DB) error
- func ExcelMultiSheet(filePath string, reports []MultiSheetRep) error
- func ExcelMultiSheetFromDB(filePath string, reports []MultiSheetRep) error
- func ExcelReport(rp RepParams, rptData ReportData, db *sql.DB) error
- type CellCurrency
- type CellDate
- type CellDecimal
- type CellInt
- type CellNumeric
- type CellPercent
- type CellStr
- type MultiSheetRep
- type RepColumns
- type RepParams
- type ReportData
Examples ¶
Constants ¶
This section is empty.
Variables ¶
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.
var Debug bool
Debug can be used to print debug information about Excel File generation when set to <true>.
var Vervose bool
Vervose can be used to print information about Excel File generation when set to <true>.
Functions ¶
func ExcelFromDB ¶
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)
Output:
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)
Output:
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)
Output:
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)
Output:
Types ¶
type CellDecimal ¶
type CellDecimal float64
CellDecimal - Decimal Cell Type (Number with commas and 2 decimal places).
type CellNumeric ¶
type CellNumeric float64
CellNumeric - Numeric Cell Type (Number with no format).
type MultiSheetRep ¶
type MultiSheetRep struct {
Params RepParams
Data ReportData
DB *sql.DB
}
MultiSheetRep type is used for multiple sheets reports.
type RepColumns ¶
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 ¶
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