Qiita Teams that are logged in
You are not logged in to any team

Community
Service
Qiita JobsQiita ZineQiita Blog
0
Help us understand the problem. What are the problem?

posted at

# [golang]Excelの列アルファベットを計算する

しかし、列を可変で出力したい場合はIndex値で扱ってほしいところです。

と、前置きが長くなりましたが今回は

Index値 -> Excelの列アルファベット

の変換処理が欲しかったので書きました。

https://docs.microsoft.com/ja-JP/office/troubleshoot/excel/convert-excel-column-numbers

# 実装コード

``````package helper

import (
"fmt"
"math"
)

func ConvertColAlphabet(col int) (string, error) {
const (
asciiAlphabetStart = 65
alphabetCount      = 26
colMin             = 1
)
if col < colMin {
return "", fmt.Errorf("argument is out of range [%d]", col)
}
var colName string
tmp := col

for tmp > 0 {
index := tmp - colMin
remaining := index / alphabetCount
charIndex := int(math.Mod(float64(index), alphabetCount))
colName = string(rune(charIndex+asciiAlphabetStart)) + colName
tmp = remaining
}

return colName, nil
}

``````

# テスト

これ本当に動くの？
と疑り深い人（自分も含む）のためにテストコードも書いておきました。
やっぱりテスト、大事ですよね。

``````package helper

import (
"testing"

"github.com/stretchr/testify/assert"
)

func TestConvertColAlphabet(t *testing.T) {

tests := []struct {
name    string
args    []int
wants   []string
wantErr error
}{
{
name:    "1桁アルファベット変換A-Z",
args:    []int{1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26},
wants:   []string{"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"},
wantErr: nil,
},
{
name:    "2桁アルファベット変換AA-AZ",
args:    []int{27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52},
wants:   []string{"AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ"},
wantErr: nil,
},
{
name:    "2桁アルファベット変換ZA-ZZ",
args:    []int{677, 678, 679, 680, 681, 682, 683, 684, 685, 686, 687, 688, 689, 690, 691, 692, 693, 694, 695, 696, 697, 698, 699, 700, 701, 702},
wants:   []string{"ZA", "ZB", "ZC", "ZD", "ZE", "ZF", "ZG", "ZH", "ZI", "ZJ", "ZK", "ZL", "ZM", "ZN", "ZO", "ZP", "ZQ", "ZR", "ZS", "ZT", "ZU", "ZV", "ZW", "ZX", "ZY", "ZZ"},
wantErr: nil,
},
{
name:    "3桁アルファベット変換AAA-AAZ",
args:    []int{703, 704, 705, 706, 707, 708, 709, 710, 711, 712, 713, 714, 715, 716, 717, 718, 719, 720, 721, 722, 723, 724, 725, 726, 727, 728},
wants:   []string{"AAA", "AAB", "AAC", "AAD", "AAE", "AAF", "AAG", "AAH", "AAI", "AAJ", "AAK", "AAL", "AAM", "AAN", "AAO", "AAP", "AAQ", "AAR", "AAS", "AAT", "AAU", "AAV", "AAW", "AAX", "AAY", "AAZ"},
wantErr: nil,
},
{
name:    "最終列XFD=16384",
args:    []int{16384},
wants:   []string{"XFD"},
wantErr: nil,
},
}

for _, tt := range tests {
t.Run(tt.name, func(t *testing.T) {
for i, arg := range tt.args {
result, err := ConvertColAlphabet(arg)
if tt.wantErr != nil {
assert.Error(t, err)
continue
}
assert.NoError(t, err)
assert.Equal(t, tt.wants[i], result)
}
})
}
}
``````
Why not register and get more from Qiita?
1. We will deliver articles that match you
By following users and tags, you can catch up information on technical fields that you are interested in as a whole
2. you can read useful information later efficiently
By "stocking" the articles you like, you can search right away
0
Help us understand the problem. What are the problem?