469,342 Members | 6,455 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

How to Import and Export Excel XLSX by Go Language

xuri
1



Excelize is a library written in pure Go and providing a set of functions that allow you to write to and read from XLSX files. Support reads and writes XLSX file generated by Microsoft Excel™ 2007 and later. Support save file without losing original charts of XLSX. This library needs Go version 1.10 or later. The full API docs can be seen using go's built-in documentation tool, or online at godoc.org and docs reference.

GitHub: github.com/360EntSecGroup-Skylar/excelize

Basic Usage

Installation

Expand|Select|Wrap|Line Numbers
  1. go get github.com/360EntSecGroup-Skylar/excelize
Create XLSX file

Here is a minimal example usage that will create XLSX file.

Expand|Select|Wrap|Line Numbers
  1. package main
  2.  
  3. import "github.com/360EntSecGroup-Skylar/excelize"
  4.  
  5. func main() {
  6.     f := excelize.NewFile()
  7.     // Create a new sheet.
  8.     index := f.NewSheet("Sheet2")
  9.     // Set value of a cell.
  10.     f.SetCellValue("Sheet2", "A2", "Hello world.")
  11.     f.SetCellValue("Sheet1", "B2", 100)
  12.     // Set active sheet of the workbook.
  13.     f.SetActiveSheet(index)
  14.     // Save xlsx file by the given path.
  15.     if err := f.SaveAs("Book1.xlsx"); err != nil {
  16.         println(err.Error())
  17.     }
  18. }
Reading XLSX file

The following constitutes the bare to read a XLSX document.

Expand|Select|Wrap|Line Numbers
  1. package main
  2.  
  3. import "github.com/360EntSecGroup-Skylar/excelize"
  4.  
  5. func main() {
  6.     f, err := excelize.OpenFile("Book1.xlsx")
  7.     if err != nil {
  8.         println(err.Error())
  9.         return
  10.     }
  11.     // Get value from cell by given worksheet name and axis.
  12.     cell, err := f.GetCellValue("Sheet1", "B2")
  13.     if err != nil {
  14.         println(err.Error())
  15.         return
  16.     }
  17.     println(cell)
  18.     // Get all the rows in the Sheet1.
  19.     rows, err := f.GetRows("Sheet1")
  20.     for _, row := range rows {
  21.         for _, colCell := range row {
  22.             print(colCell, "\t")
  23.         }
  24.         println()
  25.     }
  26. }
Add chart to XLSX file

With Excelize chart generation and management is as easy as a few lines of code. You can build charts based off data in your worksheet or generate charts without any data in your worksheet at all.



Expand|Select|Wrap|Line Numbers
  1. package main
  2.  
  3. import "github.com/360EntSecGroup-Skylar/excelize"
  4.  
  5. func main() {
  6.     categories := map[string]string{"A2": "Small", "A3": "Normal", "A4": "Large", "B1": "Apple", "C1": "Orange", "D1": "Pear"}
  7.     values := map[string]int{"B2": 2, "C2": 3, "D2": 3, "B3": 5, "C3": 2, "D3": 4, "B4": 6, "C4": 7, "D4": 8}
  8.     f := excelize.NewFile()
  9.     for k, v := range categories {
  10.         f.SetCellValue("Sheet1", k, v)
  11.     }
  12.     for k, v := range values {
  13.         f.SetCellValue("Sheet1", k, v)
  14.     }
  15.     if err := f.AddChart("Sheet1", "E1", `{"type":"col3DClustered","series":[{"name":"Sheet1!$A$2","categories":"Sheet1!$B$1:$D$1","values":"Sheet1!$B$2:$D$2"},{"name":"Sheet1!$A$3","categories":"Sheet1!$B$1:$D$1","values":"Sheet1!$B$3:$D$3"},{"name":"Sheet1!$A$4","categories":"Sheet1!$B$1:$D$1","values":"Sheet1!$B$4:$D$4"}],"title":{"name":"Fruit 3D Clustered Column Chart"}}`); err != nil {
  16.         println(err.Error())
  17.         return
  18.     }
  19.     // Save xlsx file by the given path.
  20.     if err := f.SaveAs("Book1.xlsx"); err != nil {
  21.         println(err.Error())
  22.     }
  23. }
Add picture to XLSX file

Expand|Select|Wrap|Line Numbers
  1. package main
  2.  
  3. import (
  4.     _ "image/gif"
  5.     _ "image/jpeg"
  6.     _ "image/png"
  7.  
  8.     "github.com/360EntSecGroup-Skylar/excelize"
  9. )
  10.  
  11. func main() {
  12.     f, err := excelize.OpenFile("Book1.xlsx")
  13.     if err != nil {
  14.         println(err.Error())
  15.         return
  16.     }
  17.     // Insert a picture.
  18.     if err := f.AddPicture("Sheet1", "A2", "image.png", ""); err != nil {
  19.         println(err.Error())
  20.     }
  21.     // Insert a picture to worksheet with scaling.
  22.     if err := f.AddPicture("Sheet1", "D2", "image.jpg", `{"x_scale": 0.5, "y_scale": 0.5}`); err != nil {
  23.         println(err.Error())
  24.     }
  25.     // Insert a picture offset in the cell with printing support.
  26.     if err := f.AddPicture("Sheet1", "H2", "image.gif", `{"x_offset": 15, "y_offset": 10, "print_obj": true, "lock_aspect_ratio": false, "locked": false}`); err != nil {
  27.         println(err.Error())
  28.     }
  29.     // Save the xlsx file with the origin path.
  30.     if err = f.Save(); err != nil {
  31.         println(err.Error())
  32.     }
  33. }
Dec 31 '19 #1
0 2335

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Damien | last post: by
reply views Thread by Yvonne | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by haryvincent176 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.