[Go] Get values by excelize
Tags: go, excelize
Published: 2023-05-28 17:14:43

## Intro
In this time, I will try getting cell values from an Excel file.

* [[Go] Try excelize](https://dev.to/masanori_msl/go-try-excelize-31c0)

## Get page sizes
Because it seems there is probably no way to get the page size directly, I will try other ways.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w3d8v4of3i0z9psn743j.png)

### Get a print area
If the file is set a print area, I can get it by "GetDefinedName".

#### xlsWriter.go
```go
...
	for _, name := range xlFile.GetDefinedName() {
		log.Printf("Name: %s Refer: %s Scope: %s", name.Name, name.RefersTo, name.Scope)
		if name.Name != "_xlnm.Print_Area" {
			continue
		}
		splittedRefs := strings.Split(name.RefersTo, "!")
		// The name set for the shape does not have a sheet name
		if len(splittedRefs) > 1 {
			// $B$1:$J$53
			splittedAddresses := strings.Split(splittedRefs[1], ":")
			if len(splittedAddresses) > 1 {
				log.Printf("From: %s To: %s", splittedAddresses[0], splittedAddresses[1])
			}
		}
	}
...
```

#### Results
```
...
Name: _xlnm.Print_Area Refer: 個人用月次収支!$B$1:$J$53 Scope: 個人用月 次収支
From: $B$1 To: $J$53
...
```

### Get last columns and rows
#### xlsWriter.go
```go
...
	rows, _ := xlFile.GetRows(xlFile.GetSheetName(1))
	log.Printf("Row: %d", len(rows))

	columns, _ := xlFile.GetCols(xlFile.GetSheetName(1))
	log.Printf("Column: %d", len(columns))
...
```

#### Results
```
Row: 77
Column: 10
```

## Get NumFmt
I can get cell values like below.

#### sample.xlsm

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/729lfjqnq0csu9g5u4al.png)

#### xlsWriter.go
```go
...
	targetSheet := xlFile.GetSheetName(1)
	for i := 4; i <= 7; i++ {
		add := fmt.Sprintf("D%d", i)
		value, _ := xlFile.GetCellValue(targetSheet, add)
		log.Printf("Cell Add: %s Value: %s", add, value)
	}
...
```

But those results are different from the display on the Excel file.

```
Cell Add: D4 Value: 0.55
Cell Add: D5 Value: 0
Cell Add: D6 Value: 0.1
Cell Add: D7 Value: 10
```

To match them, I should get their number format.
In excelize, I only can get their Style IDs from cells.


#### xlsWriter.go
```go
...
package main

import (
	"bytes"
	"fmt"
	"log"
	"net/http"
	"regexp"
	"strconv"
	"strings"

	"github.com/xuri/excelize/v2"
)

func SaveFileFromPath(filePath string, saveFilePath string) error {
	xlFile, err := excelize.OpenFile(filePath)
	if err != nil {
		fmt.Println(err)
		return err
	}
	defer func() {
		// Close the spreadsheet.
		if err := xlFile.Close(); err != nil {
			fmt.Println(err)
		}
	}()
	rex := regexp.MustCompile("[0-9]+.[0-9]+")
	targetSheet := xlFile.GetSheetName(1)
	for i := 4; i <= 7; i++ {
		add := fmt.Sprintf("D%d", i)
		value, _ := xlFile.GetCellValue(targetSheet, add)
		// Get format code like "0.00" if the target cell is set a number format
		numberFormatCode := getNumberFormatCode(xlFile, targetSheet, add)
		log.Println(numberFormatCode)

		if len(numberFormatCode) > 0 {
			// Get format text for Sprintf
			fmtText := getFormat(numberFormatCode, rex)
			floatValue, _ := strconv.ParseFloat(value, 64)
			log.Printf("Cell Add: %s Value: %s fmt: %s formatted: %s", add, value, fmtText, fmt.Sprintf(fmtText, floatValue))

		} else {
			log.Printf("Cell Add: %s Value: %s", add, value)
		}
	}
...
	return err
}
...
// Get number format code
func getNumberFormatCode(xlFile *excelize.File, sheetName string, address string) string {
	styleID, _ := xlFile.GetCellStyle(sheetName, address)
	// Get Number Format ID by Style ID
	numFmtID := xlFile.Styles.CellXfs.Xf[styleID].NumFmtID
	for _, numFmt := range xlFile.Styles.NumFmts.NumFmt {
		if numFmt.NumFmtID == *numFmtID {
			return numFmt.FormatCode
		}
	}
	return ""
}
// Get number format for fmt.Sprintf
func getFormat(numFmtCode string, rex *regexp.Regexp) string {
	fmtNumbers := rex.FindString(numFmtCode)
	splitted := strings.Split(fmtNumbers, ".")
	if len(splitted) <= 1 {
		return fmtNumbers
	}
	result := "%.[ZERO_LENGTH]f"
	return strings.Replace(result, "[ZERO_LENGTH]", strconv.Itoa(len(splitted[1])), -1)
}
```

#### Result
```
Cell Add: D4 Value: 0.55
0.00_);[Red]\(0.00\)
Cell Add: D5 Value: 0 fmt: %.2f formatted: 0.00
0.0000
Cell Add: D6 Value: 0.1 fmt: %.4f formatted: 0.1000
0.0
Cell Add: D7 Value: 0.05 fmt: %.1f formatted: 0.1
```

* [Style - Excelize Document](https://xuri.me/excelize/en/style.html)
* [golang excelize how to copy the cell and it's format - StackOverflow](https://stackoverflow.com/questions/71309324/golang-excelize-how-to-copy-the-cell-and-its-format)