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:=rangexlFile.GetDefinedName(){log.Printf("Name: %s Refer: %s Scope: %s",name.Name,name.RefersTo,name.Scope)ifname.Name!="_xlnm.Print_Area"{continue}splittedRefs:=strings.Split(name.RefersTo,"!")// The name set for the shape does not have a sheet nameiflen(splittedRefs)>1{// $B$1:$J$53splittedAddresses:=strings.Split(splittedRefs[1],":")iflen(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: 77Column: 10Get NumFmt
I can get cell values like below.
sample.xlsm
xlsWriter.go
...targetSheet:=xlFile.GetSheetName(1)fori:=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.55Cell Add: D5 Value: 0Cell Add: D6 Value: 0.1Cell Add: D7 Value: 10To match them, I should get their number format.
In excelize, I only can get their Style IDs from cells.
xlsWriter.go
...packagemainimport("bytes""fmt""log""net/http""regexp""strconv""strings""github.com/xuri/excelize/v2")funcSaveFileFromPath(filePathstring,saveFilePathstring)error{xlFile,err:=excelize.OpenFile(filePath)iferr!=nil{fmt.Println(err)returnerr}deferfunc(){// Close the spreadsheet.iferr:=xlFile.Close();err!=nil{fmt.Println(err)}}()rex:=regexp.MustCompile("[0-9]+.[0-9]+")targetSheet:=xlFile.GetSheetName(1)fori:=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 formatnumberFormatCode:=getNumberFormatCode(xlFile,targetSheet,add)log.Println(numberFormatCode)iflen(numberFormatCode)>0{// Get format text for SprintffmtText:=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)}}...returnerr}...// Get number format codefuncgetNumberFormatCode(xlFile*excelize.File,sheetNamestring,addressstring)string{styleID,_:=xlFile.GetCellStyle(sheetName,address)// Get Number Format ID by Style IDnumFmtID:=xlFile.Styles.CellXfs.Xf[styleID].NumFmtIDfor_,numFmt:=rangexlFile.Styles.NumFmts.NumFmt{ifnumFmt.NumFmtID==*numFmtID{returnnumFmt.FormatCode}}return""}// Get number format for fmt.SprintffuncgetFormat(numFmtCodestring,rex*regexp.Regexp)string{fmtNumbers:=rex.FindString(numFmtCode)splitted:=strings.Split(fmtNumbers,".")iflen(splitted)<=1{returnfmtNumbers}result:="%.[ZERO_LENGTH]f"returnstrings.Replace(result,"[ZERO_LENGTH]",strconv.Itoa(len(splitted[1])),-1)}Result
Cell Add: D4 Value: 0.550.00_);[Red]\(0.00\)Cell Add: D5 Value: 0 fmt: %.2f formatted: 0.000.0000Cell Add: D6 Value: 0.1 fmt: %.4f formatted: 0.10000.0Cell Add: D7 Value: 0.05 fmt: %.1f formatted: 0.1Top comments(0)
Subscribe
For further actions, you may consider blocking this person and/orreporting abuse
