Skip to content

Instantly share code, notes, and snippets.

@yattom
Created June 28, 2021 02:41
Show Gist options
  • Save yattom/d0f8fe79bd0b0a6673eb7c190a1feb05 to your computer and use it in GitHub Desktop.
Save yattom/d0f8fe79bd0b0a6673eb7c190a1feb05 to your computer and use it in GitHub Desktop.
Excelのカラムの表記をインデックスを元に求める(0->A, 25->Z, 26->AAなど。インデックスは0始まり(Excelでは1始まり))
function ColumnKey($idx)
{
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$book = $excel.Workbooks.Add()
$sheet = $book.Sheets(1)
$cell = $sheet.Cells(1, 1)
$col = $idx + 1
$cell.Formula = "=ADDRESS(1,$($col),4)"
$text = $cell.Text
$_ = $book.Close($false)
$_ = $excel.Quit()
$_ = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
Remove-Variable excel
return $text.Substring(0, $text.Length - 1)
}
ColumnKey(0) | echo
ColumnKey(1) | echo
ColumnKey(25) | echo
ColumnKey(26) | echo
ColumnKey(27) | echo
ColumnKey(72) | echo
import pytest
@pytest.mark.parametrize('num,col',
[
(0, 'A'),
(26, 'AA'),
(72, 'BU'),
(701, 'ZZ'),
(702, 'AAA'),
(1377, 'AZZ'),
(1378, 'BAA'),
(2054, 'CAA'),
(16383, 'XFD'),
])
def test_column_key(num, col):
assert column_key(num) == col
def test_column_key_max():
with pytest.raises(ValueError):
column_key(16384)
def column_key(num):
if num > 16383:
raise ValueError("maximum index of row is 16383")
digits = 'ABCDEFGHIJKLMNOPQRSTUVEXYZ'
base = len(digits)
converted = digits[num % base]
num = num // base
while num > 0:
converted = digits[num % base - 1] + converted
num = (num - 1) // base
return converted
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment