介绍
此时，我将尝试从Excel文件中获取单元格值。
获取页面尺寸
因为似乎可能无法直接获取页面大小，所以我会尝试其他方法。
获得打印区域
如果将文件设置为打印区域，我可以通过“ 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])
}
}
}
...
结果
...
Name: _xlnm.Print_Area Refer: 個人用月次収支!$B$1:$J$53 Scope: 個人用月 次収支
From: $B$1 To: $J$53
...
获取最后一列和行
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))
...
结果
Row: 77
Column: 10
获取numfmt
i可以获得下面的单元格值。
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)
}
...
但是这些结果与Excel文件上的显示不同。
Cell Add: D4 Value: 0.55
Cell Add: D5 Value: 0
Cell Add: D6 Value: 0.1
Cell Add: D7 Value: 10
要匹配它们，我应该得到它们的数字格式。
在Excelize中，我只能从单元格中获取它们的样式ID。
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)
}
结果
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