Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save agriyakhetarpal/56b75bccdf8588f2c1d098fbe6079a97 to your computer and use it in GitHub Desktop.
Save agriyakhetarpal/56b75bccdf8588f2c1d098fbe6079a97 to your computer and use it in GitHub Desktop.
Comparison of both xl_col_to_name functions
Display the source blob
Display the rendered blob
Raw
{
"nbformat": 4,
"nbformat_minor": 0,
"metadata": {
"colab": {
"name": "Comparison of both xl_col_to_name functions",
"provenance": [],
"collapsed_sections": [],
"include_colab_link": true
},
"kernelspec": {
"name": "python3",
"display_name": "Python 3"
},
"language_info": {
"name": "python"
}
},
"cells": [
{
"cell_type": "markdown",
"metadata": {
"id": "view-in-github",
"colab_type": "text"
},
"source": [
"<a href=\"https://colab.research.google.com/gist/agriyakhetarpal/56b75bccdf8588f2c1d098fbe6079a97/comparison-of-both-xl_col_to_name-functions.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
]
},
{
"cell_type": "code",
"metadata": {
"id": "eTq0z2cgIJGM"
},
"source": [
"# Updated xl_col_to_name function (my code)\n",
"\n",
"def xl_col_to_name(col, col_abs=False):\n",
" \"\"\"\n",
" Convert a zero indexed column cell reference to a string.\n",
"\n",
" Args:\n",
" col: The cell column. Int.\n",
" col_abs: Optional flag to make the column absolute. Bool.\n",
"\n",
" Returns:\n",
" Column style string.\n",
"\n",
" \"\"\"\n",
" alphabets = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'\n",
" col_num = col\n",
" if col_num < 0:\n",
" warn(\"Col number %d must be >= 0\" % col_num)\n",
" return None\n",
" \n",
" col_abs = '$' if col_abs else ''\n",
"\n",
" # Return the alphabet if the index is less than 26\n",
" if col_num < 26:\n",
" return col_abs + alphabets[col_num]\n",
"\n",
" # If index is greater than or equal to 26\n",
" else:\n",
" # Store the quotient and the remainder in a tuple via the divmod() method\n",
" index = divmod(col_num, 26)\n",
"\n",
" # If remainder is 0\n",
" if index[1] == 0:\n",
" # If quotient equals 1, return the alphabet \n",
" if index[0] == 1:\n",
" return col_abs + alphabets[index[0] - 1] + alphabets[index[1]]\n",
" else:\n",
" # If the quotient > 1 then use recursive relation\n",
" return col_abs + xl_col_to_name(index[0] - 1) + alphabets[index[1]]\n",
" \n",
" # If remainder is not 0 \n",
" else:\n",
" return col_abs + xl_col_to_name(index[0] - 1) + alphabets[index[1]]"
],
"execution_count": 1,
"outputs": []
},
{
"cell_type": "code",
"source": [
"%timeit -r 5 -n 100 [xl_col_to_name(i) for i in range(10000)]"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "CwO-xh1UsJ7c",
"outputId": "9fe61a58-047d-4bd3-c180-6dcbd1143ca0"
},
"execution_count": 2,
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": [
"100 loops, best of 5: 12.4 ms per loop\n"
]
}
]
},
{
"cell_type": "code",
"source": [
"!pip install xlsxwriter\n",
"from xlsxwriter.utility import xl_col_to_name\n",
"\n",
"%timeit -r 5 -n 100 [xl_col_to_name(i) for i in range(10000)]"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "zrAUoSUBZzrP",
"outputId": "7f8058af-848f-45ea-c9ff-084e391e6586"
},
"execution_count": 3,
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": [
"Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
"Collecting xlsxwriter\n",
" Downloading XlsxWriter-3.0.3-py3-none-any.whl (149 kB)\n",
"\u001b[K |████████████████████████████████| 149 kB 31.1 MB/s \n",
"\u001b[?25hInstalling collected packages: xlsxwriter\n",
"Successfully installed xlsxwriter-3.0.3\n",
"100 loops, best of 5: 17 ms per loop\n"
]
}
]
}
]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment