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

Log in to Qiita Team
Community
OrganizationEventAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
0
Help us understand the problem. What are the problem?

posted at

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

皆さんもBtoB系のWebサービスを作っている時に「golangで何かしらの帳票をExcelで出力したい」みたいな要望もあると思いますが、そんな時に大体の人が使用するgolang主要なLibraryであるqax-os/excelizeは列情報をありがた迷惑にもA-Zの文字列で扱ってくれています。

列情報が固定で変更の余地がほぼない!という状況ならまあソースにアルファベットベタ書きでも問題ないかもしれません(実際にはそんな事はままないと思いますが…)
しかし、列を可変で出力したい場合はIndex値で扱ってほしいところです。

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

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

の変換処理が欲しかったので書きました。
列アルファベットの算出ロジックはMicrosoftの公式サイトにVBのコードですが載っています。
今回はそれをgolang用に書き直しています。
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?