Intro
In this time, I will try getting cell values from an Excel file.
Get page sizes
Because it seems there is probably no way to get the page size directly, I will try other ways.
Get a print area
If the file is set a print area, I can get it by "GetDefinedName".
xlsWriter.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
...
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
xlsWriter.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
...
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
Top comments (0)