xlsx

package module
v0.0.0-...-b4a383b Latest Latest
Warning

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

 Go to latest Published: Jan 31, 2024 License: MIT Imports: 22 Imported by: 0

README

xlsx

Travis CI Software License GoDoc Coverage Status goreport

golang mapping between xlsx and struct instances.

本库提供高层golang的struct切片和excel文件的映射，避免直接处理底层sheet/row/cells等细节。

本库底层使用unioffice，其提供了比qax-os/excelize更加友好的API。

还有另外一个比较活跃的底层实现tealeg/xlsx，官网已告知：不再维护！(No longer maintained!)

Usage documentation

Directly write excel file
package main

import "github.com/io1o/xlsx"

type memberStat struct {
	Total     int `title:"会员总数" sheet:"会员"` // sheet可选，不声明则选择首个sheet页读写
	New       int `title:"其中：新增"`
	Effective int `title:"其中：有效"`
}

func main() {
	x, _ := xlsx.New()
	defer x.Close()

	x.Write([]memberStat{
			{Total: 100, New: 50, Effective: 50},
			{Total: 200, New: 60, Effective: 140},
		})
	x.SaveToFile("testdata/test1.xlsx")
}

you will get the result excel file as the following:

image

Write excel with template file
x, _ := xlsx.New(xlsx.WithTemplate("testdata/template.xlsx"))
defer x.Close()

x.Write([]memberStat{
        {Total: 100, New: 50, Effective: 50},
        {Total: 200, New: 60, Effective: 140},
    })
x.SaveToFile("testdata/test1.xlsx")

you will get the result excel file as the following:

image

Read excel with titled row
var memberStats []memberStat

x, _ := xlsx.New(xlsx.WithExcel("testdata/test1.xlsx"))
defer x.Close()

if err := x.Read(&memberStats); err != nil {
	panic(err)
}

assert.Equal(t, []memberStat{
	{Total: 100, New: 50, Effective: 50},
	{Total: 200, New: 60, Effective: 140},
}, memberStats)
create data validation
  1. Method 1: use template sheet to list the validation datas like:

image

then declare the data validation tag dataValidation like:

type Member struct {
	Area      string `title:"区域" dataValidation:"Validation!A1:A3"`
	Total     int    `title:"=会员总数"` // use modifier = to strictly equivalent of title searching， otherwise by Containing
	New       int    `title:"其中：新增"`
	Effective int    `title:"其中：有效"`
}
  1. Method 2: directly give the list in tag dataValidation with comma-separated like:
type Member struct {
	Area      string `title:"区域" dataValidation:"A,B,C"`
	Total     int    `title:"会员总数"`
	New       int    `title:"其中：新增"`
	Effective int    `title:"其中：有效"`
}
  1. Method 3: programmatically declares and specified the key name in the tag dataValidation like:
type Member struct {
	Area      string `title:"区域" dataValidation:"areas"`
	Total     int    `title:"会员总数"`
	New       int    `title:"其中：新增"`
	Effective int    `title:"其中：有效"`
}

func demo() {
	x, _ := xlsx.New(xlsx.WithValidations(map[string][]string{
		"areas": {"A23", "B23", "C23"},
	}))
	defer x.Close()

	_ = x.Write([]memberStat23{
		{Area: "A23", Total: 100, New: 50, Effective: 50},
		{Area: "B23", Total: 200, New: 60, Effective: 140},
		{Area: "C23", Total: 300, New: 70, Effective: 240},
	})

	_ = x.SaveToFile("result.xlsx")
}
占位模板
站位模板写入

image

type RegisterTable struct {
	ContactName  string    `asPlaceholder:"true"` // 联系人
	Mobile       string    // 手机
	Landline     string    // 座机
	RegisterDate time.Time // 登记日期
	DeviceType   string    `placeholderCell:"C8"` // 类型
	Manufacturer string    // 生产厂家
	DeviceModern string    // 型号
}

func demo() {
	x, _ := xlsx.New(xlsx.WithTemplate("testdata/placeholder.xlsx"))
	defer x.Close()

	_ = x.Write(RegisterTable{
		ContactName:  "隔壁老王",
		Mobile:       "1234567890",
		Landline:     "010-1234567890",
		RegisterDate: time.Now(),
		DeviceType:   "A1",
		Manufacturer: "来弄你",
		DeviceModern: "X786",
	})

	_ = x.SaveToFile("testdata/out_placeholder.xlsx")
}

image

占位模板读取

占位符模板读取，需要两个文件：

  1. “占位符模板”excel文件
  2. “待读取数据”excel文件

然后从“占位符模板”里获取占位符的位置，用这个位置信息，去实际“待读取数据”的excel文件中提取数据。

type RegisterTable struct {
	ContactName  string    `asPlaceholder:"true"` // 联系人
	Mobile       string    // 手机
	Landline     string    // 座机
	RegisterDate time.Time // 登记日期
	DeviceType   string    `placeholderCell:"C8"` // 类型，直接从C8单元格读取
	Manufacturer string    // 生产厂家
	DeviceModern string    // 型号
}

func demo() error {
	x, err := xlsx.New(
        xlsx.WithTemplate("testdata/placeholder.xlsx"), // 1. “占位符模板”excel文件
		xlsx.WithExcel("testdata/out_placeholder.xlsx"))     // 2. “待读取数据”excel文件
	if err != nil {
		return err
	}

	defer x2.Close()

	var v RegisterTable

	err = x2.Read(&v)
	if err != nil {
		return err
	}
}

Options

  1. asPlaceholder:"true" 使用“占位符模板”excel文件，从“占位符模板”里获取占位符的位置，用这个位置信息，去实际“待读取数据”的excel文件中提取数据。
  2. ignoreEmptyRows:"false" 读取excel时，是否忽略全空行(包括空白)，默认true

Resources

  1. awesome-go microsoft-excel

    Libraries for working with Microsoft Excel.

    • qax-os/excelize at 2022-04-12
    • excelize - Golang library for reading and writing Microsoft Excel™ (XLSX) files.
    • go-excel - A simple and light reader to read a relate-db-like excel as a table.
    • goxlsxwriter - Golang bindings for libxlsxwriter for writing XLSX (Microsoft Excel) files.
    • xlsx - Library to simplify reading the XML format used by recent version of Microsoft Excel in Go programs.
    • xlsx - Fast and safe way to read/update your existing Microsoft Excel files in Go programs.

Documentation

Index

Examples

Constants

This section is empty.

Variables

View Source 
var (
	ErrFailToLocationTitleRow = errors.New("unable to location title row")
	ErrNoExcelRead            = errors.New("no excel read")
)
View Source 
var ErrUnknownExcelError = fmt.Errorf("unknown excel file format")

ErrUnknownExcelError defines the the unknown excel file format error.

Functions

func ConvertNumberToFloat64 

func ConvertNumberToFloat64(v interface{}) (float64, bool)

ConvertNumberToFloat64 converts a number value to float64. If the value is not a number, it returns 0, false.

func CopyCellStyle 

func CopyCellStyle(from, to spreadsheet.Cell)

func CopyRowStyle 

func CopyRowStyle(from, to spreadsheet.Row)

func GetCellString 

func GetCellString(c spreadsheet.Cell) string

GetCellString returns the string in a cell if it's an inline or string table string. Otherwise it returns an empty string.

func GetSharedString 

func GetSharedString(c spreadsheet.Cell, id int) (string, error)

GetSharedString retrieves a string from the shared strings table by index. nolint:goerr113

func ParseBool 

func ParseBool(v string, defaultValue bool) bool

ParseBool parses the v as a boolean when it is any of true, on, yes or 1.

func ParseJavaTimeFormat 

func ParseJavaTimeFormat(layout string) string

ParseJavaTimeFormat converts the time format in java to golang.

func RowCells 

func RowCells(r spreadsheet.Row) []spreadsheet.Cell

RowCells returns a slice of cells. The cells can be manipulated, but appending to the slice will have no effect.

Types

type MergeColsMode 

type MergeColsMode int
const (
	// DoNotMerge do not mergeTitled.
	DoNotMerge MergeColsMode = iota
	// MergeCols mergeTitled columns separately.
	// like:
	// a, b, 1
	// a, b, 2
	// c, b, 3
	// will merged to :
	// a, b, 1
	// -, -, 2
	// c, -, 3
	MergeCols
	// MergeColsAlign mergeTitled columns align left merging.
	// like:
	// a, b, 1
	// a, b, 2
	// c, b, 3
	// will merged to :
	// a, b, 1
	// -, -, 2
	// c, b, 3
	MergeColsAlign
)

type Option 

type Option struct {
	TemplateWorkbook, Workbook *spreadsheet.Workbook

	Validations map[string][]string
}

Option defines the option for the xlsx processing.

type OptionFn 

type OptionFn func(*Option)

OptionFn defines the func to change the option.

func WithExcel 

func WithExcel(excel interface{}) OptionFn

WithExcel defines the input excel file for reading. The excel can be type of any of followings: 1. a string for direct excel file name 2. a []byte for the content of excel which loaded in advance, like use packr2 to read. 3. a io.Reader.

func WithTemplate 

func WithTemplate(template interface{}) OptionFn

WithTemplate defines the template excel file for writing template. The template can be type of any of followings: 1. a string for direct template excel file name 2. a []byte for the content of template excel which loaded in advance, like use packr2 to read. 3. a io.Reader.

Example 
package main

import (
	"fmt"

	"github.com/io1o/xlsx"
)

func main() {
	x, _ := xlsx.New(xlsx.WithTemplate("testdata/template.xlsx"))
	defer x.Close()

	_ = x.Write([]memberStat{
		{Total: 100, New: 50, Effective: 50},
		{Total: 200, New: 60, Effective: 140},
	})

	err := x.SaveToFile("testdata/out_demo2.xlsx")
	fmt.Println("Write", err == nil)
}

type memberStat struct {
	Total     int `title:"会员总数" sheet:"会员"`
	New       int `title:"其中：新增"`
	Effective int `title:"其中：有效"`
}

Output:

Write true

func WithUpload 

func WithUpload(r *http.Request, filenameKey string) OptionFn

WithUpload defines the input excel file for reading.

func WithValidations 

func WithValidations(v map[string][]string) OptionFn

WithValidations defines the validations for the cells.

type PlaceholderPart 

type PlaceholderPart struct {
	Part string
	Var  string
}

PlaceholderPart is a placeholder sub Part after parsing.

type PlaceholderValue 

type PlaceholderValue struct {
	Content string

	Parts []PlaceholderPart
}

PlaceholderValue represents a placeholder value.

func ParsePlaceholder 

func ParsePlaceholder(content string) PlaceholderValue

ParsePlaceholder parses placeholders in the content.

func (*PlaceholderValue) HasPlaceholders 

func (p *PlaceholderValue) HasPlaceholders() bool

HasPlaceholders tells that the PlaceholderValue has any placeholders.

func (*PlaceholderValue) Interpolate 

func (p *PlaceholderValue) Interpolate(vars map[string]string) string

Interpolate interpolates placeholders with vars.

func (*PlaceholderValue) ParseVars 

func (p *PlaceholderValue) ParseVars(content string) (outVars map[string]string, matched bool)

ParseVars parses the vars from the content.

type Title 

type Title struct {
	Text   string
	Strict bool // Should strictly equal to Text or only matched by Containing
}

func MakeTitle 

func MakeTitle(title string) Title

func (*Title) Matches 

func (t *Title) Matches(s string) bool

type TitleField 

type TitleField struct {
	Column      string
	Title       Title
	StructField reflect.StructField
}

type WriteOption 

type WriteOption struct {
	SheetName     string
	MergeColsMode MergeColsMode
}

type WriteOptionFn 

type WriteOptionFn func(*WriteOption)

func WithMergeColsMode 

func WithMergeColsMode(v MergeColsMode) WriteOptionFn

func WithSheetName 

func WithSheetName(v string) WriteOptionFn

type Xlsx 

type Xlsx struct {
	// contains filtered or unexported fields
}

Xlsx is the structure for xlsx processing.

Example 
package main

import (
	"encoding/json"
	"fmt"
	"io/ioutil"
	"time"

	"github.com/io1o/xlsx"
)

// ReadBytes reads bytes from the file.
func ReadBytes(filename string) []byte {
	b, _ := ioutil.ReadFile(filename)
	return b
}

func main() {
	type (
		HostInfo struct {
			ServerName         string    `title:"主机名称" json:"serverName"`
			ServerHostname     string    `title:"主机hostname" json:"serverHostname"`
			ServerIP           string    `title:"主机IP" json:"serverIp"`
			ServerUserRtx      string    `json:"serverUserRtx"`
			Status             string    `json:"status"` // 状态：0正常 1删除
			InstanceID         string    `title:"实例ID" json:"instanceId"`
			Region             string    `title:"服务器可用区" json:"region"`
			CreateTime         time.Time `json:"createTime"` // 创建时间
			UpdateTime         time.Time `json:"updateTime"` // 修改时间
			ServerUserFullName string    `title:"主机负责人(rtx)" json:"serverUserFullName"`
		}
		Rsp struct {
			Status  int        `json:"status"`
			Message string     `json:"message"`
			Data    []HostInfo `json:"data"`
		}
	)

	var r Rsp

	err := json.Unmarshal(ReadBytes("testdata/hostinfos.json"), &r)
	fmt.Println("Unmarshal", err == nil)

	x, _ := xlsx.New(xlsx.WithTemplate("testdata/hostinfos_template.xlsx"))
	defer x.Close()

	err = x.Write(r.Data, xlsx.WithSheetName("FirstSheet"))
	fmt.Println("Write", err == nil)

	r.Data[0].ServerName += "第2页啦"
	_ = x.Write(r.Data, xlsx.WithSheetName("SecondSheet"))

	err = x.SaveToFile("testdata/out_hostinfos.xlsx")
	fmt.Println("SaveToFile", err == nil)
}

Output:

Unmarshal true
Write true
SaveToFile true

func New 

func New(optionFns ...OptionFn) (x *Xlsx, err error)

New creates a new instance of Xlsx.

Example 
package main

import (
	"fmt"

	"github.com/io1o/xlsx"
)

func main() {
	x, _ := xlsx.New()
	defer x.Close()

	_ = x.Write([]memberStat{
		{Total: 100, New: 50, Effective: 50},
		{Total: 200, New: 60, Effective: 140},
	})

	err := x.SaveToFile("testdata/out_demo1.xlsx")

	// See: https://golang.org/pkg/testing/#hdr-Examples
	fmt.Println("Write", err == nil)
}

type memberStat struct {
	Total     int `title:"会员总数" sheet:"会员"`
	New       int `title:"其中：新增"`
	Effective int `title:"其中：有效"`
}

Output:

Write true

func (*Xlsx) Close 

func (x *Xlsx) Close() error

Close does some cleanup like remove temporary files.

func (*Xlsx) Download 

func (x *Xlsx) Download(w http.ResponseWriter, filename string) error

Download downloads the excels file in the http response.

func (*Xlsx) Read 

func (x *Xlsx) Read(slicePtr interface{}) error

Read reads the excel rows to slice. nolint:goerr113

func (*Xlsx) Save 

func (x *Xlsx) Save(w io.Writer) error

Save writes the workbook out to a writer in the zipped xlsx format.

func (*Xlsx) SaveToFile 

func (x *Xlsx) SaveToFile(file string) error

SaveToFile writes the workbook out to a file.

func (*Xlsx) Write 

func (x *Xlsx) Write(beans interface{}, writeOptionFns ...WriteOptionFn) error

Write Writes beans to the underlying xlsx.

Source Files

View all Source files

Directories

Path Synopsis
cmd
xlsx
pkg
cast
upload

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.