CODEWITHSUNDEEP
excelgo
mahbh2001
mahbh2001

Reputation: 101

golang - how to list excel spreadsheet column names?

golang - how to list excel column names ?? enter image description here

As shown in image , the exel columns are listed as A B C ... AA AB AC .... There is no sample available on how to list this in go lang. This is needed when parsing excel spreadsheet to get range of spreadsheet tabs to get cells values

Upvotes: 1

Views: 2106

Answers (3)

MSH
MSH

Reputation: 425

Here is one with an error handler. this is modified from excelize note that this is not as fast as @rocka2q answer.

package main

import (
    "errors"
    "fmt"
)

func main() {

    for cm := 1; cm < 100; cm++ {
        cmName, _ := ColumnNumberToName(cm)
        fmt.Println(cmName)
    }

}

func ColumnNumberToName(num int) (string, error) {
    if num < 1 {
        return "", fmt.Errorf("incorrect column number %d", num)
    }
    if num > 16384 {
        return "", errors.New("column number exceeds maximum limit")
    }
    var col string
    for num > 0 {
        col = string(rune((num-1)%26+65)) + col
        num = (num - 1) / 26
    }
    return col, nil
}

or you can directly use the excelize function:


package main

import (
    "fmt"
    "github.com/xuri/excelize/v2"
)

func main() {

    for cm := 1; cm < 100; cm++ {
        cmName, _ := excelize.ColumnNumberToName(cm)
        fmt.Println(cmName)
    }

}

Upvotes: 2

rocka2q
rocka2q

Reputation: 2804

Here is an efficient solution.

func getColumnName(col int) string {
    name := make([]byte, 0, 3) // max 16,384 columns (2022)
    const aLen = 'Z' - 'A' + 1 // alphabet length
    for ; col > 0; col /= aLen + 1 {
        name = append(name, byte('A'+(col-1)%aLen))
    }
    for i, j := 0, len(name)-1; i < j; i, j = i+1, j-1 {
        name[i], name[j] = name[j], name[i]
    }
    return string(name)
}

https://go.dev/play/p/upjBQeE1x8I

The OP (@mahbh2001) posted an answer to his own question: https://stackoverflow.com/a/71350368

Here are the results of getColumnName benchmarks for columns 1 through 99:

rocka2q:
BenchmarkA2Q-4  758865  1498 ns/op   146 B/op   73 allocs/op

OP (mahbh2001):
BenchmarkOP-4   122714  9515 ns/op  1376 B/op  341 allocs/op

The OP's code is significantly slower.

Upvotes: 3

mahbh2001
mahbh2001

Reputation: 101

I tried to search for handy function to get excel column names but was not able to get in golang , so created one , hope this helps other to save time

package main

import "fmt"

func reverse(s string) string {
    runes := []rune(s)
    for i, j := 0, len(runes)-1; i < j; i, j = i+1, j-1 {
        runes[i], runes[j] = runes[j], runes[i]
    }
    return string(runes)
}

// Function to print Excel column
// name for a given column number
//
func getColumnName(columnNumber int) string {

    // To store result (Excel column name)
    var columnName = ""
    for ok := true; ok; ok = columnNumber > 0 {

        // Find remainder
        rem := columnNumber % 26

        // If remainder is 0, then a
        // 'Z' must be there in output
        if rem == 0 {
            columnName += "Z"
            columnNumber = (columnNumber / 26) - 1
        } else // If remainder is non-zero
        {
            columnName += string((rem - 1) + int('A'))
            columnNumber = columnNumber / 26
        }
    }

    // Reverse the string
    columnName = reverse(columnName)
    return columnName

}

func main() {
    for cm := 1; cm < 100; cm++ {
        fmt.Println(getColumnName(cm))
    }
}

Upvotes: 2

Related Questions