A simple and light excel file reader to read a standard excel as a table faster | 一个轻量级的Excel数据读取库,用一种更`关系数据库`的方式解析Excel。

Overview

Intro | 简介

Go Report Card cover.run go GoDoc

Expect to create a reader library to read relate-db-like excel easily. Just like read a config.

This library can read all xlsx file correctly from our project now.

go get github.com/szyhf/go-excel

Example | 用例

Here is a simple example.

Assume you have a xlsx file like below:

ID NameOf Age Slice UnmarshalString
1 Andy 1 1|2 {"Foo":"Andy"}
2 Leo 2 2|3|4 {"Foo":"Leo"}
3 Ben 3 3|4|5|6 {"Foo":"Ben"}
4 Ming 4 1 {"Foo":"Ming"}
  • the first row is the title row.
  • other row is the data row.

All examples list in https://godoc.org/github.com/szyhf/go-excel#pkg-examples.

// defined a struct
type Standard struct {
	// use field name as default column name
	ID      int
	// column means to map the column name
	Name    string `xlsx:"column(NameOf)"`
	// you can map a column into more than one field
	NamePtr *string `xlsx:"column(NameOf)"`
	// omit `column` if only want to map to column name, it's equal to `column(AgeOf)`
	Age     int     `xlsx:"AgeOf"`
	// split means to split the string into slice by the `|`
	Slice   []int `xlsx:"split(|)"`
	// *Temp implement the `encoding.BinaryUnmarshaler`
	Temp    *Temp `xlsx:"column(UnmarshalString)"`
	// use '-' to ignore.
	Ignored string `xlsx:"-"`
}

// func (this Standard) GetXLSXSheetName() string {
// 	return "Some other sheet name if need"
// }

type Temp struct {
	Foo string
}

// self define a unmarshal interface to unmarshal string.
func (this *Temp) UnmarshalBinary(d []byte) error {
	return json.Unmarshal(d, this)
}

func simpleUsage() {
	// will assume the sheet name as "Standard" from the struct name.
	var stdList []Standard
	err := excel.UnmarshalXLSX("./testdata/simple.xlsx", &stdList)
	if err != nil {
		panic(err)
	}
}

func defaultUsage(){
	conn := excel.NewConnecter()
	err := conn.Open("./testdata/simple.xlsx")
	if err != nil {
		panic(err)
	}
	defer conn.Close()

	// Generate an new reader of a sheet
	// sheetNamer: if sheetNamer is string, will use sheet as sheet name.
	//             if sheetNamer is int, will i'th sheet in the workbook, be careful the hidden sheet is counted. i ∈ [1,+inf]
	//             if sheetNamer is a object implements `GetXLSXSheetName()string`, the return value will be used.
	//             otherwise, will use sheetNamer as struct and reflect for it's name.
	// 			   if sheetNamer is a slice, the type of element will be used to infer like before.
	rd, err := conn.NewReader(stdSheetName)
	if err != nil {
		panic(err)
	}
	defer rd.Close()

	for rd.Next() {
		var s Standard
		// Read a row into a struct.
		err:=rd.Read(&s)
		if err!=nil{
			panic(err)
		}
		fmt.Printf("%+v",s)
	}

	// Read all is also supported.
	// var stdList []Standard
	// err = rd.ReadAll(&stdList)
	// if err != nil {
	//   panic(err)
	//	 return
	// }
	// fmt.Printf("%+v",stdList)

	// map with string key is support, too.
	// if value is not string
	// will try to unmarshal to target type
	// but will skip if unmarshal failed.

	// var stdSliceList [][]string
	// err = rd.ReadAll(&stdSliceList)
	// if err != nil {
	//   panic(err)
	//	 return
	// }
	// fmt.Printf("%+v",stdSliceList)

	// var stdMapList []map[string]string
	// err = rd.ReadAll(&stdMapList)
	// if err != nil {
	//   panic(err)
	//	 return
	// }
	// fmt.Printf("%+v",stdMapList)

	// Using binary instead of file.
	// xlsxData, err := ioutil.ReadFile(filePath)
	// if err != nil {
	// 	log.Println(err)
	// 	return
	// }

	// conn := excel.NewConnecter()
	// err = conn.OpenBinary(xlsxData)
}

See the simple.xlsx.Standard in testdata and code in ./standard_test.go and ./standard_example_test.go for details.

While read into a []string, row of title can have duplicated titles, otherwise ErrDuplicatedTitles will be return.

Advance | 进阶用法

The advance usage can make more options.

Config | 配置

Using a config as "excel.Config":

type Config struct {
	// sheet: if sheet is string, will use sheet as sheet name.
	//        if sheet is a object implements `GetXLSXSheetName()string`, the return value will be used.
	//        otherwise, will use sheet as struct and reflect for it's name.
	// 		  if sheet is a slice, the type of element will be used to infer like before.
	Sheet interface{}
	// Use the index row as title, every row before title-row will be ignore, default is 0.
	TitleRowIndex int
	// Skip n row after title, default is 0 (not skip), empty row is not counted.
	Skip int
	// Auto prefix to sheet name.
	Prefix string
	// Auto suffix to sheet name.
	Suffix string
}

Tips:

  • Empty row will be skipped.
  • Column larger than len(TitleRow) will be skipped.
  • Only empty cell can fill with default value, if a cell can not parse into a field it will return an error.
  • Default value can be unmarshal by encoding.BinaryUnmarshaler, too.
  • If no title row privoded, the default column name in exce like 'A', 'B', 'C', 'D' ......, 'XFC', 'XFD' can be used as column name by 26-number-system.

For more details can see the code in ./test/advance_test.go and file in simple.xlsx.Advance.suffx sheet.

XLSX Tag | 标签使用

column

Map to field name in title row, by default will use the field name.

default

Set default value when no value is filled in excel cell, by default is 0 or "".

split

Split a string and convert them to a slice, it won't work if not set.

nil

Will not skip scan value in the cell equals to this 'nil value'

req

Will return error if clomun title not exist in excel.

XLSX Field Config | 字段的解析配置

Sometimes it’s a bit cumbersome to deal with escape characters (exp. #6), so implement the interface of GetXLSXFieldConfigs() map[string]FieldConfig will take a high priority than tag to provide the field config, more info to see the test file.

RoadMap | 开发计划

  • Read xlsx file and got the expect xml. √
  • Prepare the shared string xml. √
  • Get the correct sheetX.xml. √
  • Read a row of a sheet. √
  • Read a cell of a row, fix the empty cell. √
  • Fill string cell with value of shared string xml. √
  • Can set the column name row, default is the first row. √
  • Read a row into a slice. √
  • Read a row to a struct by column name. √
  • Read a row into a map with string key. √
  • Read a row into a map by primary key.

Thinking | 随想

在复杂的系统中(例如游戏)

有时候为了便于非专业人员设置一些配置

会使用Excel作为一种轻量级的关系数据库或者配置文件

毕竟对于很多非开发人员来说

配个Excel要比写json或者yaml什么简单得多

这种场景下

读取特定格式(符合关系数据库特点的表格)的数据会比各种花式写入Excel的功能更重要

毕竟从编辑上来说微软提供的Excel本身功能就非常强大了

而现在我找到的Excel库的功能都过于强大了

用起来有点浪费

于是写了这个简化库

这个库的工作参考了tealeg/xlsx的部分实现和读取逻辑。

感谢tealeg

Issues
  • Columns with header names including

    Columns with header names including "(" or ")" are not read.

    Hi, I have an Excel sheet with column names like "Patiënten (in aantallen)". These columns are not read, probably due to the "(" and ")". Can you maybe fix this? Thanks in advance! And thanks for the repository.

    Kind regards, Michiel

    opened by leapforce-nl 5
  • Is there a way to read sub column ?

    Is there a way to read sub column ?

    I got a excel file to read, and it has a columns look like this:

    Imgur

    I want to read sub column a's and b's value ( both are 0.11)

    Is there a way to do things like this ?

    opened by weeee9 5
  • Can we have a New Method in interface<Reader>?

    Can we have a New Method in interface?

    requesting a new Method in interface that can allow us to get Raw data

    // Reader to read excel
    type Reader interface {
    	// RawRow return current row data
            RawRow() ([]string, error)
    }
    
    enhancement 
    opened by wotmshuaisi 4
  • 【建议】关于指定sheetName建议带上指针类型

    【建议】关于指定sheetName建议带上指针类型

    不支持带指针的构造函数

    func (s Scopus) GetXLSXSheetName() string {
    	return "scopus_1500"
    }
    

    这样是可以的,但是带有指针是失败的,比如:下面是错误的

    func (s *Scopus) GetXLSXSheetName() string {
    	return "scopus_1500"
    }
    

    区别在于*Scopus

    enhancement 
    opened by jtyoui 0
  • can we use column name as title?

    can we use column name as title?

    can we use column names, such as A, B, AB, for some excel doesn't contain any title or the title may be updated from day to day by uploader. such as can we define structs like:

    type Demo struct {
    	ID  string `xlsx:"A"`
    	Sku string `xlsx:"B"`
    }
    

    found this: If no title row privoded, the default column name in exce like 'A', 'B', 'C', 'D' ......, 'XFC', 'XFD' can be used as column name by 26-number-system.

    what value should pass to TitleRowIndex

    opened by armenpn 1
  • Date values from xlsx file didn't convert to string properly

    Date values from xlsx file didn't convert to string properly

    default.xlsx

    above xlsx file has a date type value 01/09/2019, when i use excel.Read() function to decode the xlsx row to string slice, i got string value 43709

    is there any way that i can get data types of each cell?

    opened by wotmshuaisi 6
Releases(v1.4.3)
Owner
Back Yu
Back Yu
Pure go library for creating and processing Office Word (.docx), Excel (.xlsx) and Powerpoint (.pptx) documents

unioffice is a library for creation of Office Open XML documents (.docx, .xlsx and .pptx). Its goal is to be the most compatible and highest performan

UniDoc 3.4k Aug 8, 2022
Golang library for reading and writing Microsoft Excel™ (XLSX) files.

Excelize Introduction Excelize is a library written in pure Go providing a set of functions that allow you to write to and read from XLSX / XLSM / XLT

360 Enterprise Security Group, Endpoint Security, inc. 12.4k Aug 7, 2022
Fast and reliable way to work with Microsoft Excel™ [xlsx] files in Golang

Xlsx2Go package main import ( "github.com/plandem/xlsx" "github.com/plandem/xlsx/format/conditional" "github.com/plandem/xlsx/format/conditional/r

Andrey G. 152 Aug 10, 2022
一款 Go 语言编写的小巧、简洁、快速采集 fofa 数据导出到 Excel 表单的小工具。

fofa 一款 Go 语言编写的小巧、简洁、快速采集 fofa 数据导出到 Excel 表单的小工具。 Goroutine + retryablehttp Build git clone https://github.com/inspiringz/fofa cd fofa go build -ldf

3ND 21 Apr 15, 2022
Go Microsoft Excel Number Format Parser

NFP (Number Format Parser) Using NFP (Number Format Parser) you can get an Abstract Syntax Tree (AST) from Excel number format expression. Installatio

null 6 Jun 30, 2022
Using NFP (Number Format Parser) you can get an Abstract Syntax Tree (AST) from Excel number format expression

NFP (Number Format Parser) Using NFP (Number Format Parser) you can get an Abstract Syntax Tree (AST) from Excel number format expression. Installatio

fossabot 0 Feb 4, 2022
Cheap/fast/simple XLSX file writer for textual data

xlsxwriter Cheap/fast/simple XLSX file writer for textual data -- no fancy formatting or graphs go get github.com/mzimmerman/xlsxwriter data := [][]s

Matthew Zimmerman 0 Feb 8, 2022
Simple .docx converter implemented by Go. Convert .docx to plain text.

docc Simple ".docx" converter implemented by Go. Convert ".docx" to plain text. License MIT Features Less dependency. No need for Microsoft Office. On

tenkoh 2 Mar 30, 2022
Go (golang) library for reading and writing XLSX files.

XLSX Introduction xlsx is a library to simplify reading and writing the XML format used by recent version of Microsoft Excel in Go programs. Tutorial

Geoffrey J. Teale 5.4k Aug 7, 2022
go-eexcel implements encoding and decoding of XLSX like encoding/json

go-eexcel go-eexcel implements encoding and decoding of XLSX like encoding/json Usage func ExampleMarshal() { type st struct { Name string `eexce

sago35 0 Dec 9, 2021
🚩 TOC, zero configuration table of content generator for Markdown files, create table of contents from any Markdown file with ease.

toc toc TOC, table of content generator for Markdown files Table of Contents Table of Contents Usage Installation Packages Arch Linux Homebrew Docker

Yagiz Degirmenci 87 Jul 27, 2022
Simple-read-file - Example of how to read file in Go

simple-read-file This repository contains a simple example of how to read file i

Yessy Purnama 0 Jan 11, 2022
Bubble-table - A table component for the Bubble Tea framework

Bubble-table A table component for the Bubble Tea framework. This is currently m

Brandon Fulljames 176 Aug 9, 2022
EasyTCP is a light-weight and less painful TCP server framework written in Go (Golang) based on the standard net package.

EasyTCP is a light-weight TCP framework written in Go (Golang), built with message router. EasyTCP helps you build a TCP server easily fast and less painful.

zxl 447 Aug 16, 2022
The web framework for writing faster sites, faster

Gondola The web framework for writing faster sites, faster. Written in Go. View documentation at http://gondolaweb.com. Unless indicated otherwise at

Rainy Cape S.L. 309 Jan 24, 2022
A faster RWLock primitive in Go, 2-3 times faster than RWMutex. A Go implementation of concurrency control algorithm in paper

Go Left Right Concurrency A Go implementation of the left-right concurrency control algorithm in paper <Left-Right - A Concurrency Control Technique w

wangyi 41 Aug 5, 2022
Wrap byte read options with uniform interface for io.Reader and byte slice

nibbler Nibble chunks from Reader streams and slice in a common way Overview This is a golang module that provides an interface for treating a Reader

null 0 Dec 23, 2021
golang 在线预览word,excel,pdf,MarkDown(Online Preview Word,Excel,PPT,PDF,Image by Golang)

Go View File 在线体验地址 http://39.97.98.75:8082/view/upload (不会经常更新,保留最基本的预览功能。服务器配置较低,如果出现链接超时请等待几秒刷新重试,或者换Chrome) 目前已经完成 docker部署 (不用为运行环境烦恼) Wor

CZC 64 Jul 26, 2022
Go-sql-reader - Go utility to read the externalised sql with predefined tags

go-sql-reader go utility to read the externalised sql with predefined tags Usage

null 0 Jan 25, 2022
Hntoebook - Converts the best HN stories to .mobi format to be read using an e-reader

HN to E-Book What? This program converts the best HN stories to .mobi format to

Abishek Muthian 76 May 6, 2022
This is a simple file storage server. User can upload file, delete file and list file on the server.

Simple File Storage Server This is a simple file storage server. User can upload file, delete file and list file on the server. If you want to build a

BH_Lin 0 Jan 19, 2022
Read from standard input and output a Haags translation of the given input.

haags Read from standard input and output a Haags translation of the given input. Building make && sudo make install You may also run go build on syst

Kees van Voorthuizen 3 Sep 13, 2021
Simple, lightweight and faster response (JSON, JSONP, XML, YAML, HTML, File) rendering package for Go

Package renderer Simple, lightweight and faster response (JSON, JSONP, XML, YAML, HTML, File) rendering package for Go Installation Install the packag

Saddam H 241 Jul 3, 2022
Litestream-read-replica-demo - A demo application for running live read replication on fly.io with Litestream

Litestream Read Replica Demo A demo application for running live read replicatio

Ben Johnson 67 Aug 6, 2022
A very light-weight file sharing platform, including server and client

file-transporter A very light-weight file sharing platform, including server and client Installation git clone https://github.com/vence722/file-transp

Vence Lam 1 Jan 12, 2022
Data visualization with chart, Create CSV file, Read Write CSV file

Data visualization with chart, Create CSV file, Read Write CSV file, Read from json file and many more in single project ......

MD FAISAL PORAG 1 Jan 13, 2022
Crud - A mysql crud code generate tool from table DDL sql file

crud is a mysql crud code generate tool 中文文档 Getting Started Overview Crud is a

null 18 Aug 10, 2022
Light weight, extensible configuration management library for Go. Built in support for JSON, TOML, YAML, env, command line, file, S3 etc. Alternative to viper.

koanf (pronounced conf; a play on the Japanese Koan) is a library for reading configuration from different sources in different formats in Go applicat

Kailash Nadh 1.1k Aug 8, 2022
EasyMidi is a simple and reliable library for working with standard midi file (SMF)

EasyMidi EasyMidi is a simple and reliable library for working with standard midi file (SMF). Installing A step by step series of examples that tell y

null 39 Jun 7, 2022