Fast and reliable way to work with Microsoft Excel™ [xlsx] files in Golang

Overview

Xlsx2Go

Build Status Code Coverage Go Report Card GoDoc License FOSSA Status Donate

package main

import (
	"github.com/plandem/xlsx"
	"github.com/plandem/xlsx/format/conditional"
	"github.com/plandem/xlsx/format/conditional/rule"
	"github.com/plandem/xlsx/format/styles"
)

func main() {
	xl := xlsx.New()
	defer xl.Close()

	//create a new sheet
	sheet := xl.AddSheet("The first sheet")

	//access by ref
	cell := sheet.CellByRef("A2")

	//set value
	cell.SetValue("Easy Peasy")

	//set cool styles
	cell.SetStyles(styles.New(
		styles.Font.Bold,
		styles.Font.Color("#ff0000"),
		styles.Fill.Type(styles.PatternTypeSolid),
		styles.Fill.Color("#ffff00"),
		styles.Border.Color("#009000"),
		styles.Border.Type(styles.BorderStyleMedium),
	))

	//add comment
	cell.SetComment("No Comment!")

	//add hyperlink
	sheet.CellByRef("A4").SetValueWithHyperlink("wikipedia", "http://google.com")

	//merge cells
	sheet.RangeByRef("A6:A7").Merge()
	sheet.CellByRef("A6").SetValue("merged cell")

	//iterating
	for iRow := 1; iRow < 7; iRow++ {
		//access by indexes
		cell := sheet.Cell(1, iRow)
		cell.SetValue(iRow)
	}

	//add conditional formatting
	sheet.AddConditional(conditional.New(
		conditional.AddRule(
			rule.Value.Between(1, 3, styles.New(
				styles.Font.Bold,
				styles.Font.Color("#ff0000"),
			)),
		),
		conditional.AddRule(
			rule.IconSet.Type(rule.IconSetType3Arrows),
		),
	), "B2:B7")

	xl.SaveAs("./foo.xlsx")
}

Documentation

Roadmap

  • sheet: copy
  • sheet: custom filters
  • sheet: streaming
  • merged cells: merge/split for ranges, cols, rows
  • hyperlinks: for cells, ranges, cols, rows
  • range: copy
  • row: copy
  • col: copy
  • cell: comments
  • cell: formulas
  • cell: typed getter/setter for values
  • other: conditional formatting
  • other: rich texts
  • other: drawing
  • other: more optimization
  • other: more tests

Contribution

  • To prevent mess, sources have strict separation of markup and functionality. Document that describes OOXML is quite huge (about 6K pages), but the same time - functionality is not.
  • All markup resides inside of 'ml' folders, only marshal/unmarshal is allowed here, no any functionality.
  • Not every 'ml object' has related 'functional object' and vice versa.
  • If you want some functionality, then wrap 'ml object' and do what you want.

OOXML edition

XML is compliant with part 1 of the 5th edition of the ECMA-376 Standard for Office Open XML

License

FOSSA Status

Issues
  • SetHyperlink in the second sheet causing file format errors.

    SetHyperlink in the second sheet causing file format errors.

    demo.xlsx

    SetHyperlink in the first sheet works fine, but not fine in the second sheet.

    Open with MS Office 2016:

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    	<logFileName>error208200_01.xml</logFileName>
    	<summary>在文件“C:\msys64\home\ofunc_7kwo01e\go\src\test\xlsx\demo.xlsx”中检测到错误</summary>
    	<additionalInfo>
    		<info>Excel 已完成文件级验证和修复。此工作簿的某些部分可能已被修复或丢弃。</info>
    	</additionalInfo>
    	<repairedParts>
    		<repairedPart>已修复的部件: 部件 /xl/worksheets/sheet2.xml。</repairedPart>
    	</repairedParts>
    </recoveryLog>
    
    bug 
    opened by ofunc 17
  • ColIterator will cause some conditional format information lost.

    ColIterator will cause some conditional format information lost.

    package main
    
    import (
    	"log"
    
    	"github.com/plandem/xlsx"
    )
    
    func main() {
    	xl, err := xlsx.Open("test.xlsx")
    	if err != nil {
    		log.Fatal(err)
    	}
    	defer xl.Close()
    
    	// NOTE If uncomment the code, some conditional format information will lost.
    	for cols := xl.Sheet(0).Cols(); cols.HasNext(); {
    		cols.Next()
    	}
    
    	//	xl.Save() ERROR: Access is denied.
    	xl.SaveAs("out.xlsx")
    }
    

    test.xlsx out.xlsx

    enhancement 
    opened by ofunc 14
  • sheet corrupted after save

    sheet corrupted after save

    While working on https://github.com/plandem/xlsx/issues/37, I tried to open a truncated version of an example spreadsheet in "not streaming" mode to make my modifications.

    Here is the original sheet:

    mfi101119holdingslist_100_noissuer.xlsx

    The goal is to lookup matches for the "issuer" column in a database of previous entries and select the best match.

    To this end I iterate though the sheet using row = rows.Next() collect some fields to use in a database query, then use

    cell := match.Row.Cell(mi.IColIssuer)
    // everything output by this diagnostic is reasonble
    fmt.Println("I", match.Row.Bounds(), mi.IColIssuer, match.Issuer)
    cell.SetText(match.Issuer)
    

    to write the data (match.Issuer is of type string). Finally I save the data; it doesn't seem to be documented whether to call save before or after sheet.Close() so I tried both ways:

    sheet.Close()
    xl.SaveAs("./foo.xlsx") // also tried this first
    xl.Close()
    

    The resulting file is corrupt: foo.xlsx

    opened by shaunc 8
  • ColIterator will cause the columns hidden.

    ColIterator will cause the columns hidden.

    for cols := sheet.Cols(); cols.HasNext(); {
    	cols.Next()
    }
    xl.Save()
    

    The columns in the saved file is hidden.

    opened by ofunc 7
  • Read files from google sheets

    Read files from google sheets

    If I export a file from Google Sheets, only the first column is read in this file.

    opened by dimkaone 6
  • Border

    Border

    Thanks for the work you've done. Tell me how to work with borders and center alignment.

    opened by veliev 6
  • How do I get the StyleID of cell, row and column?

    How do I get the StyleID of cell, row and column?

    In this way, I can set the same style for the new cells, rows and columns as the specified cells, rows and columns.

    opened by ofunc 6
  • InsertRow (for sheetReadWrite)

    InsertRow (for sheetReadWrite)

    Hi,

    Given the following example: https://play.golang.org/p/G6QwfVo8CFb which instantiates a new doc, with a blank new sheet, my expectation when invoking InsertRow twice with index 0 was the first two rows being populated with the text.

    It appears that the cell pointers are still somehow pegged to the old row: the text appears on the 3rd row.

    Please advise if the approach is incorrect, and another approach is necessary when creating documents from scratch (maybe an example in the readme).

    Cheers, silviu

    opened by silviucm 5
  • cannot using in project with govendor

    cannot using in project with govendor

    when my project using govendor to manage golang packages, xlsx2go package cannot compile correctly, the "go:linkname" compile have error, the error belows: (can you remove the go:linkname in the package?)

    liushun.lucien/own_study/vendor/github.com/plandem/xlsx/format/conditional.(*Info).Validate: relocation target github.com/plandem/xlsx/format/conditional/rule.fromRule not defined liushun.lucien/own_study/vendor/github.com/plandem/xlsx/format/conditional.AddRule.func1: relocation target github.com/plandem/xlsx/format/conditional/rule.fromRule not defined liushun.lucien/own_study/vendor/github.com/plandem/xlsx.(*conditionals).Add: relocation target github.com/plandem/xlsx/format/conditional.from not defined liushun.lucien/own_study/vendor/github.com/plandem/xlsx.(*hyperlinks).Add: relocation target github.com/plandem/xlsx/types/hyperlink.from not defined liushun.lucien/own_study/vendor/github.com/plandem/xlsx.toRichText: relocation target github.com/plandem/xlsx/format/styles.toRichFont not defined liushun.lucien/own_study/vendor/github.com/plandem/xlsx.(*styleSheet).addDiffStyle: relocation target github.com/plandem/xlsx/format/styles.from not defined liushun.lucien/own_study/vendor/github.com/plandem/xlsx.(*styleSheet).addStyle: relocation target github.com/plandem/xlsx/format/styles.from not defined

    opened by LucienLS 5
  • Stream write

    Stream write

    Hi!

    Have u got any examples of stream writing? Or this lib has only stream read mode

    enhancement help wanted 
    opened by trueone 5
  • {header:1} facing issue

    {header:1} facing issue

    Argument of type '{ header: number; }' is not assignable to parameter of type 'Sheet2CSVOpts'. Object literal may only specify known properties, and 'header' does not exist in type 'Sheet2CSVOpts'.

    const data = XLSX.utils.sheet_to_csv(ws,{ header: 1 });
    
    opened by appasaheb4 1
  • Implementing built in date formatting

    Implementing built in date formatting

    Currently, the number.Format method doesn't take into consideration the formatting code. This diff implements formatting for dates based on the code. The implementation doesn't look at the cell data type because in cases of date it's not being populated by Excel, so it's always empty.

    opened by demontiejr 1
  • Reading data formatted as date always returns numbers

    Reading data formatted as date always returns numbers

    Hi,

    I'm trying to use the library to process very large files and they contain date columns. When reading the values, I'm always getting numbers (the underlying numeric value for the date). Since I don't know in advance what's the data type for a particular column, I'd like to be able to identify it from the formatting.

    I tried using cell.Type() == types.CellTypeDate() to decide whether I could explicitly get the value parsed to Time (with the Date function) but it's always returning false. I then tried to get the style ID and use the style sheet to get the info and check whether it's a date, but then I saw that this method is not implemented. https://github.com/plandem/xlsx/blob/4538b54c4a21aba8c7661e299444f60090bfcf43/style_sheet.go#L214-L227

    Is there a way I can identify dates right now? If not, can you point me to how I can solve this issue so I can send a pull request?

    Thanks, Demontiê

    opened by demontiejr 8
  • Set column width when using streaming

    Set column width when using streaming

    Hi, I am trying to writing file with a lot of rows (from 200k to 1m) using streaming mode.

    Writing cell with style is ok but when I start set width for column, it return panic with message not supported.

    I have read the docs and can't find it. Is there anyway I can keep using stream writing and set column width?

    Thank you.

    p/s: is there an option to freeze top row or first column?

    opened by duzgxbe 1
  • index out of range [17] with length 17

    index out of range [17] with length 17

    17 is the original number of columns the xlsx file has.

    xl, err := xlsx.Open('my_file.xslx)
    ...
    
    sheet := xl.Sheet(0)
    ...
    
    colCount,_ := sheet.Dimension()
    fmt.Println(colCount) // 17
    ...
    
    sheet.InsertCol(17).Cell(0)
    xl.Save()
    fmt.Println(sheet.Dimension()) // 18, 132
    ...
    
    for rows := sheet.Rows(); rows.HasNext(); {
    		iRow, row := rows.Next()
                    row.Cell(17).SetValue("testing123") // panic: runtime error: index out of range [17] with length 17
    }
    

    in sheet_readwrite.go I have tested the following:

    func (s *sheetReadWrite) Cell(colIndex, rowIndex int) *Cell {
    	s.expandIfRequired(colIndex, rowIndex)
    
    	colIndex, rowIndex, _ = s.mergedCells.Resolve(colIndex, rowIndex)
    	fmt.Println("LENGTH:", len(s.ml.SheetData[rowIndex].Cells)) // prints 17 inconsistently
    	data := s.ml.SheetData[rowIndex].Cells[colIndex]
    
    	//if there is no any data for this cell, then create it
    	if data == nil {
    		data = &ml.Cell{
    			Ref: types.CellRefFromIndexes(colIndex, rowIndex),
    		}
    
    		s.ml.SheetData[rowIndex].Cells[colIndex] = data
    	}
    
    	return &Cell{ml: data, sheet: s.sheetInfo}
    }
    
    opened by ecarter202 0
  • Results of related formulas are not updated.

    Results of related formulas are not updated.

    Hello! Modify an existing xlsx file and save it. However, the calculation results of related formulas are not updated. There is a simple solution: clear calcId in xl/workbook.xml. It is recommended to provide an method of Spreadsheet for doing this.

    opened by ofunc 0
  • Defined names support

    Defined names support

    How about supporting specific names (so-called named ranges)?

    enhancement 
    opened by nsemikov 1
  • Question

    Question "sed"-like editing support

    I would like to update very large xlsx files in stream mode, setting information in given rows of a sheet as I iterate over it.

    I tried updating a stream using the current version. Cell.SetText() panics with:

        not supported in read-only mode
        /Users/shauncutts/src/other/pkg/mod/github.com/plandem/[email protected]/cell.go:130
    

    In fact, Cell.SetInlineText() succeeds; however, the resulting text is not present.

    Would this be hard to add?

    Update One possibility would be to add an additional sheet in streaming write mode, copy over everything while making modifications, then close and delete the original sheet, and rename the new sheet. (sigh). But looking around I don't see any facilities for copying data from one sheet to another. Would that be a promising approach?

    opened by shaunc 14
  • Update Guide, help wanted

    Update Guide, help wanted

    It would be cool if someone would help to actualize the guide. Library has decent number of features, but not everything is documented (there are API documentation, tests and examples).

    E.g. conditional formatting, as well as, styles are fully implemented, but not documented yet.

    help wanted 
    opened by plandem 0
  • Chart support

    Chart support

    Will you add some chart support?

    enhancement drawingML 
    opened by nsemikov 1
Releases(v1.0.4)
Owner
Andrey G.
Andrey G.
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.1k Dec 6, 2021
A simple and light excel file reader to read a standard excel as a table faster | 一个轻量级的Excel数据读取库,用一种更`关系数据库`的方式解析Excel。

Intro | 简介 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 correct

Back Yu 130 Nov 22, 2021
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.2k Nov 26, 2021
Golang bindings for libxlsxwriter for writing XLSX files

goxlsxwriter provides Go bindings for the libxlsxwriter C library. Install goxlsxwriter requires the libxslxwriter library to be installe

Frank Terragna 17 Nov 12, 2021
Golang bindings for libxlsxwriter for writing XLSX files

goxlsxwriter goxlsxwriter provides Go bindings for the libxlsxwriter C library. Install goxlsxwriter requires the libxslxwriter library to be installe

Frank Terragna 730 May 30, 2021
A simple excel engine without ui to parse .csv files.

A simple excel engine without ui to parse .csv files.

Akmal Hossain 1 Nov 4, 2021
A Go native tabular data extraction package. Currently supports .xls, .xlsx, .csv, .tsv formats.

grate A Go native tabular data extraction package. Currently supports .xls, .xlsx, .csv, .tsv formats. Why? Grate focuses on speed and stability first

Jeremy Jay 98 Nov 20, 2021
一款 Go 语言编写的小巧、简洁、快速采集 fofa 数据导出到 Excel 表单的小工具。

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

3ND 13 Nov 15, 2021
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. 10.1k Nov 27, 2021
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. 10.1k Dec 2, 2021
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. 10.2k Dec 6, 2021
Command-line tool to load csv and excel (xlsx) files and run sql commands

csv-sql supports loading and saving results as CSV and XLSX files with data processing with SQLite compatible sql commands including joins.

Dhamith Hewamullage 24 Sep 30, 2021
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.1k Dec 6, 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 31 Nov 30, 2021
A simple and light excel file reader to read a standard excel as a table faster | 一个轻量级的Excel数据读取库,用一种更`关系数据库`的方式解析Excel。

Intro | 简介 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 correct

Back Yu 130 Nov 22, 2021
Minutes is a CLI tool for synchronizing work logs between multiple time trackers, invoicing, and bookkeeping software to make entrepreneurs' daily work easier.

Minutes is a CLI tool for synchronizing work logs between multiple time trackers, invoicing, and bookkeeping software to make entrepreneurs' daily work easier.

Gábor Boros 10 Nov 8, 2021
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.2k Dec 6, 2021
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.2k Nov 26, 2021
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. Current s

Geoffrey J. Teale 5.2k Dec 4, 2021
Golang bindings for libxlsxwriter for writing XLSX files

goxlsxwriter provides Go bindings for the libxlsxwriter C library. Install goxlsxwriter requires the libxslxwriter library to be installe

Frank Terragna 17 Nov 12, 2021
Golang bindings for libxlsxwriter for writing XLSX files

goxlsxwriter goxlsxwriter provides Go bindings for the libxlsxwriter C library. Install goxlsxwriter requires the libxslxwriter library to be installe

Frank Terragna 730 May 30, 2021
Declare AMQP entities like queues, producers, and consumers in a declarative way. Can be used to work with RabbitMQ.

About This package provides an ability to encapsulate creation and configuration of RabbitMQ([AMQP])(https://www.amqp.org) entities like queues, excha

Alex 69 Oct 5, 2021
Learning about containers and how they work by creating them the hard way

Containers the hard way: Gocker: A mini Docker written in Go It is a set of Linux's operating system primitives that provide the illusion of a contain

Shuveb Hussain 1.3k Nov 17, 2021
Fast and Reliable Golang Web Framework

Gramework The Good Framework Gramework long-term testing stand metrics screenshot made with Gramework Stats Dashboard and metrics middleware What is i

null 365 Nov 2, 2021
Fast and Reliable Golang Web Framework

Gramework The Good Framework Gramework long-term testing stand metrics screenshot made with Gramework Stats Dashboard and metrics middleware What is i

null 365 Nov 2, 2021
go-jsonc provides a way to work with commented json by converting it to plain json.

JSON with comments for GO Decodes a "commented json" to "json". Provided, the input must be a valid jsonc document. Supports io.Reader With this, we c

Akshay Bharambe 8 Dec 4, 2020
A simple excel engine without ui to parse .csv files.

A simple excel engine without ui to parse .csv files.

Akmal Hossain 1 Nov 4, 2021
A small, fast, reliable pastemyst API wrapper written in Golang

A small, fast, reliable pastemyst API wrapper written in Golang. Official pastemyst API docs found here.

null 11 Nov 18, 2021
Split multiple Kubernetes files into smaller files with ease. Split multi-YAML files into individual files.

Split multiple Kubernetes files into smaller files with ease. Split multi-YAML files into individual files.

Patrick D'appollonio 76 Dec 3, 2021