Extensions de types int et string pour conversion en format "colonne excel"

Version imprimable

Bonjour,

Pour ceux qui ont besoin de travailler avec une numérotation "alphabétique", voici une classe statique ainsi que deux méthodes d'extension afin de faire la conversion int => colonne Excel et string => indice de colonne Excel

Comme les noms des méthodes d'extension le montrent, ce sera certainement le plus utile à ceux qui travaillent avec Excel, mais peut-être trouverez-vous d'autres champs d'application.

J'étais initialement parti sur un changement en base 26, mais je me suis heurté à un problème de poids : dans la numérotation A, B, C... Z, AA, AB, ... ZZ, AAA... il n'y a pas de notion de "zéro". Ce n'est donc pas une base à proprement parler. Et il se produit un décallage à chaque fois qu'on devrait rencontrer un chiffre zéro (avant A, entre Z et AA, il devrait y avoir un A0, etc.)

Il est certainement possible d'améliorer l'algo en utilisant des fonctions mathématiques, en prenant en compte le décalage, mais je n'ai plus assez de neurones en état de fonctionner pour chercher.
L'utilisation d'une classe static pour stocker les valeurs allant de A à ZZZ permet d'éviter de ré-générer la liste, ce qui est déjà une bonne optimisation.

Code complet : la première classe, qui m'a servi de test, utilise les éléments des classes suivantes, qui sont celles que vous devez reprendre dans votre code pour l'utiliser.

Code:

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
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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
 
using System;
using System.Collections.Generic;
 
namespace DigitToChar
{
    class Program
    {
        static void Main(string[] args)
        {
            int n;
            string s;
 
            n = 146;
            s = "EP";
            Console.WriteLine("Le nombre {0} a pour représentation colonne Excel {1}", n, n.ToExcelColumn());
            Console.WriteLine("La colonne Excel {0} a pour indice {1}", s, s.FromExcelColumn());
            Console.WriteLine();
 
            n = 26;
            s = "Z";
            Console.WriteLine("Le nombre {0} a pour représentation colonne Excel {1}", n, n.ToExcelColumn());
            Console.WriteLine("La colonne Excel {0} a pour indice {1}", s, s.FromExcelColumn());
            Console.WriteLine();
 
            n = 27;
            s = "AA";
            Console.WriteLine("Le nombre {0} a pour représentation colonne Excel {1}", n, n.ToExcelColumn());
            Console.WriteLine("La colonne Excel {0} a pour indice {1}", s, s.FromExcelColumn());
            Console.WriteLine();
 
            n = 1;
            s = "A";
            Console.WriteLine("Le nombre {0} a pour représentation colonne Excel {1}", n, n.ToExcelColumn());
            Console.WriteLine("La colonne Excel {0} a pour indice {1}", s, s.FromExcelColumn());
            Console.WriteLine();
 
            n = 18278;
            s = "ZZZ";
            Console.WriteLine("Le nombre {0} a pour représentation colonne Excel {1}", n, n.ToExcelColumn());
            Console.WriteLine("La colonne Excel {0} a pour indice {1}", s, s.FromExcelColumn());
            Console.WriteLine();
 
            Console.WriteLine("Comptage de AP à PI");
            for (int i = "AP".FromExcelColumn(), nb = "PI".FromExcelColumn(); i <= nb; i++)
            {
                Console.Write(i.ToExcelColumn());
                Console.Write(' ');
            }
            Console.WriteLine();
 
            Console.ReadKey(true);
        }
    }
 
    public static class IntExtension
    {
        public static string ToExcelColumn(this int n)
        {
            if (n < 1)
            {
                throw new Exception("Les colonnes Excel sont numérotées à partir de 1.");
            }
            if (n > DigitsToChars.chars.Count)
            {
                throw new Exception(string.Format("Le programme ne sais pas compter au delà de {0} colonnes. Estimez-vous heureux, Excel est limité à 256 !", DigitsToChars.chars.Count));
            }
            return DigitsToChars.chars[n - 1];
        }
    }
 
    public static class StringExtension
    {
        public static int FromExcelColumn(this string s)
        {
            if (s.Length == 0 || s.Length > 3)
            {
                throw new Exception("La chaîne doit faire entre 1 et 3 caractères.");
            }
 
            return DigitsToChars.chars.IndexOf(s) + 1;
        }
    }
 
    public static class DigitsToChars
    {
        public static List<string> chars;
 
        static DigitsToChars()
        {
            chars = new List<string>();
 
            for (int i = 0; i < 26; i++)
            {
                chars.Add(string.Format("{0}", (char)(65 + i)));
            }
 
            for (int i = 0; i < 26; i++)
            {
                for (int j = 0; j < 26; j++)
                {
                    chars.Add(string.Format("{0}{1}", (char)(65 + i), (char)(65 + j)));
                }
            }
 
            for (int i = 0; i < 26; i++)
            {
                for (int j = 0; j < 26; j++)
                {
                    for (int k = 0; k < 26; k++)
                    {
                        chars.Add(string.Format("{0}{1}{2}", (char)(65 + i), (char)(65 + j), (char)(65 + k)));
                    }
                }
            }
        }
    }
}

14/03/2012, 15h00
tomlev
Citation:

Envoyé par StringBuilder

Pour ceux qui ont besoin de travailler avec une numérotation "alphabétique", voici une classe statique ainsi que deux méthodes d'extension afin de faire la conversion int => colonne Excel et string => indice de colonne Excel

Très bonne idée :ccool:
J'avais déjà essayé de faire la conversion et j'avais réalisé que c'était beaucoup moins évident que ça en avait l'air...

Citation:

J'étais initialement parti sur un changement en base 26, mais je me suis heurté à un problème de poids : dans la numérotation A, B, C... Z, AA, AB, ... ZZ, AAA... il n'y a pas de notion de "zéro". Ce n'est donc pas une base à proprement parler. Et il se produit un décallage à chaque fois qu'on devrait rencontrer un chiffre zéro (avant A, entre Z et AA, il devrait y avoir un A0, etc.)

C'est effectivement le nœud du problème ;)

Quelques remarques sur ton implémentation :
- je suis pas sûr que ce soit judicieux de faire ça sous forme de méthode d'extensions ; ça va apparaitre sur tous les int et string, alors que c'est pour un usage très spécifique, donc ça va polluer un peu l'intellisense. En plus je trouve que l'usage de FromExcelColumn n'est pas très intuitif... Perso j'aurais plutôt fait des méthodes statiques "normales"
- Citation:
  
  L'utilisation d'une classe static pour stocker les valeurs allant de A à ZZZ permet d'éviter de ré-générer la liste, ce qui est déjà une bonne optimisation.
  
  Je suis pas convaincu que ce soit vraiment une optimisation... tu vas générer 26 * 26 * 26 chaines stockées en mémoire, ce qui va prendre un peu de temps et occuper de la mémoire inutilement. En plus, le IndexOf pour retrouver la valeur numérique est en O(n), donc très inefficace. Je pense que l'algo qui fait la conversion à la demande est suffisamment rapide pour ne pas avoir besoin de mettre les données en cache (par contre il faut le trouver, l'algo en question :aie:)
- Concernant le code proprement dit :
  - La classe DigitsToChars n'a pas besoin d'être publique, donc elle ne devrait pas l'être
  - les champs publics en lecture écriture, c'est maaaal :aie:
Enfin en tous cas, dans le principe c'est une très bonne idée, même si je ne suis pas entièrement d'accord avec la façon dont tu l'as réalisé ;)

Il me semblait bien que j'avais déjà réfléchi au problème... J'ai retrouvé l'implémentation que j'avais faite :

Code:

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
27
28
29
30
31
32
33
34
35
36
37
38
public static class ExcelColumnHelper
{
    public static string ToExcelColumn(int columnNumber)
    {
        if (columnNumber <= 0)
            throw new ArgumentOutOfRangeException("columnNumber");
 
        int tmp = columnNumber;
        string excelColumn = String.Empty;
        while (tmp > 0)
        {
            int r = (tmp - 1) % 26;
            excelColumn = (char)('A' + r) + excelColumn;
            tmp = (tmp - r) / 26;
        }
 
        return excelColumn;
    }
 
    public static int FromExcelColumn(string excelColumn)
    {
        if (string.IsNullOrEmpty(excelColumn))
            throw new ArgumentException("Excel column cannot be null or empty", "excelColumn");
        excelColumn = excelColumn.ToUpperInvariant();
        int multiplier = 1;
        int columnNumber = 0;
        for (int i = excelColumn.Length - 1; i >= 0; i--)
        {
            char c = excelColumn[i];
            if (c < 'A' || c > 'Z')
                throw new ArgumentException("Excel column must only contain letters between A and Z", "excelColumn");
            int value = (int)(c - 'A' + 1) * multiplier;
            columnNumber += value;
            multiplier *= 26;
        }
        return columnNumber;
    }
}

Bonjour Tomlev.

Je pose "as this" la nouvelle implémentation.

Je découvre tes commentaires en même temps, et vu que j'ai un truc urgent à faire, j'adapterai après.

J'ai l'impression qu'une de mes méthodes n'est qu'une écriture différente de la tienne. Quant à l'autre, je pense que la mienne est beaucoup plus simple ;)

En revanche, je n'ai implémenté aucun mécanisme de sécurité et donc si on passe n'importe quoi comme chaîne en paramètre, ça fait n'importe quoi...

Code:

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
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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
 
 
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
 
namespace DigitToChar
{
    class Program
    {
        static void Main(string[] args)
        {
            Method1();
            Console.ReadKey(true);
        }
 
        public static void Method1()
        {
            Stopwatch sw = new Stopwatch();
            sw.Start();
            int n = 0;
            StringBuilder sb = new StringBuilder();
            for (int i = "A".FromExcelColumn(), nb = "ZZZ".FromExcelColumn(); i <= nb; i++)
            {
                sb.AppendFormat("{0}={1} ", i.ToExcelColumn(), i.ToExcelColumn().FromExcelColumn());
                n++;
            }
            sw.Stop();
            //Console.WriteLine(sb.ToString());
            Console.WriteLine(string.Format("Méthode 1 : {0} lignes ({1} caractères) calculées en {2} millisecondes", n, sb.Length, sw.ElapsedMilliseconds));
            Console.WriteLine();
        }
    }
 
    public static class IntExtension
    {
        public static string ToExcelColumn(this int n)
        {
            StringBuilder sb = new StringBuilder();
 
            while (n > 0)
            {
                sb.Append((char)('A' + ((n-- - 1) % 26)));
                n /= 26;
            }
 
            return sb.ToString().Reverse();
        }
    }
 
    public static class StringExtension
    {
        public static int FromExcelColumn(this string s)
        {
            int ret = 0;
            s = s.Reverse();
 
            for (int i = 0, nb = s.Length; i < nb; i++)
            {
                ret += (s[i] - 64) * (int)Math.Pow(26, i);
            }
            return ret;
        }
 
        public static string Reverse(this string s)
        {
            char[] chars = s.ToCharArray();
            Array.Reverse(chars);
            return new string(chars);
        }
    }
}

Dans un futur post, je posterai une version qui tiens compte de tes remarques (et du coup, ce sera une simple classe statique "ExcelUtils" par exemple)

Voici la classe réécrite selon tes remarques.

J'en ai profité pour mettre des commentaires et gérer un peu mieux les erreurs.
A noter que la gestion des erreurs à divisé le temps d'exécution par 10 sur ma machine 8O Je pense que le Regexp est responsable... Mais c'est tellement plus propre qu'une boucle sur la chaîne avec des 'A' <= val <= 'Z'...

Code:

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
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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
 
 
using System;
using System.Text;
using System.Text.RegularExpressions;
 
namespace DigitToChar
{
    /// <summary>
    /// Classe statique utilitaire de fonctions permettant de simplifier le travaille avec Excel
    /// </summary>
    static class ExcelUtility
    {
        /// <summary>
        /// Inverse l'ordre des caractères dans la chaîne de caractères
        /// </summary>
        /// <param name="s">Chaîne dont on veut inverse l'ordre des caractères</param>
        /// <returns>Nouvelle chaîne avec l'ordre des caractères inversé</returns>
        private static string Reverse(string s)
        {
            if (s == null) return null;
            char[] chars = s.ToCharArray();
            Array.Reverse(chars);
            return new string(chars);
        }
 
        /// <summary>
        /// Permet de déterminer pour un indice donnée, la notation "colonne Excel"
        /// </summary>
        /// <param name="i">Indice pour lequel on veut déterminer le nom de "colonne Excel"</param>
        /// <returns>Nom de "colonne Excel"</returns>
        public static string IntToExcelColumn(int i)
        {
            /*
             * Vérification de la validité du paramètre
             */
            if (i < 1)
            {
                throw new Exception("L'indice d'une colonne Excel est strictement suppérieur à 1");
            }
 
            /* 
             * Un StringBuilder est ce qu'il y a de plus rapide en ce qui concerne 
             * la manipulation de chaîne de caractères, même si dans le cas de chaînes
             * de petite taille, c'est certainement inutile
             */
            StringBuilder sb = new StringBuilder();
 
            while (i > 0)
            {
                /* 
                 * Astuce de notation. Il ne faut pas trop chercher à comprendre...
                 * Gardez juste en tête qu'il ne s'agit pas d'un simple changement de base.
                 */
                sb.Append((char)('A' + ((i-- - 1) % 26)));
                i /= 26;
            }
 
            /* 
             * On a généré la chaîne à l'envers. On doit en inverser l'ordre
             */
            return Reverse(sb.ToString());
        }
 
        /// <summary>
        /// Permet de déterminer l'indice d'un nom de "colonne Excel"
        /// </summary>
        /// <param name="s">Nom de "colonne Excel"</param>
        /// <returns>Indice de la "colonne Excel"</returns>
        public static int ExcelColumnToInt(string s)
        {
            /*
             * Vérification que le nom de la colonne ne contient que des caractères autorisés
             */
            if (new Regex("[^A-Z]").IsMatch(s))
            {
                throw new Exception("Une colonne Excel ne comporte que des caractères de A à Z");
            }
 
            /*
             * Valeur de retour
             */
            int ret = 0;
 
            /* 
             * On inverse l'ordre des caractères de la chaîne. C'est plus pratique
             * pour faire des calculs dessus
             */
            string ss = Reverse(s);
 
            for (int i = 0, nb = ss.Length; i < nb; i++)
            {
                /*
                 * Idem que pour la fonction IntToExcelColumn : il ne s'agit pas d'un
                 * simple changement de base. Les "erreurs" sont volontaire.
                 * 
                 */
                ret += (ss[i] - 64) * (int)Math.Pow(26, i);
            }
 
            /*
             * On n'a pas réussi à déterminer l'indice de la colonne.
             */
            if (ret == 0)
            {
                throw new Exception(string.Format("Impossible de déterminer l'indice de la colonne '{0}'.", s));
            }
            return ret;
        }
    }
}

Bonjour,

2-3 bricoles qui pourraient aider ;) (je remets le tout avec mes modifs)

Code:

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
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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
using System;
using System.Linq;
using System.Text;
 
namespace DigitToChar
{
    /// <summary>
    /// Classe statique utilitaire de fonctions permettant de simplifier le travaille avec Excel
    /// </summary>
    static class ExcelUtility
    {
        /* suppression de la méthode Reverse devenue inutile dans ce cadre */
 
        /// <summary>
        /// Permet de déterminer pour un indice donnée, la notation "colonne Excel"
        /// </summary>
        /// <param name="i">Indice pour lequel on veut déterminer le nom de "colonne Excel"</param>
        /// <returns>Nom de "colonne Excel"</returns>
        public static string IntToExcelColumn(this int i) /* rajout du this pour méthode d'extension */
        {
             // Vérification de la validité du paramètre
            if (i < 1)
            {
                throw new Exception("L'indice d'une colonne Excel est strictement suppérieur à 1");
            }
 
            // Un StringBuilder est ce qu'il y a de plus rapide en ce qui concerne 
            // la manipulation de chaîne de caractères, même si dans le cas de chaînes
            // de petite taille, c'est certainement inutile
            StringBuilder sb = new StringBuilder();
 
            while (i > 0)
            {
                // Astuce de notation. Il ne faut pas trop chercher à comprendre...
                // Gardez juste en tête qu'il ne s'agit pas d'un simple changement de base.
                sb.Insert(0, (char)('A' + ((i-- - 1) % 26))); /* remplacement de Append par Insert(0, ... en début donc */
                i /= 26;
            }
 
            return sb.ToString(); /* grâce à l'Insert plus besoin de reverse ici */
        }
 
        /* plus simple (et sans doute plus performant) qu'une regex
         * on pourrait carrément la mettre dans la méthode je pense) */
        private const string allowedChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
 
        /// <summary>
        /// Permet de déterminer l'indice d'un nom de "colonne Excel"
        /// </summary>
        /// <param name="s">Nom de "colonne Excel"</param>
        /// <returns>Indice de la "colonne Excel"</returns>
        public static int ExcelColumnToInt(this string s)
        {
            s = s.ToUpperInvariant(); /* des fois que la chaine passée soit en minuscules */
 
            // Vérification que le nom de la colonne ne contient que des caractères autorisés
            /* remplacement de la regex ;
             * littéralement: au moins 1 caractère dans s n'est pas contenu dans ceux autorisés (perso je trouve ça propre) */
            if (s.Any(c => !allowedChars.Contains(c)))
            {
                throw new Exception("Une colonne Excel ne comporte que des caractères de A à Z");
            }
 
            int ret = 0, currentIndex, lastIndex;
            currentIndex = lastIndex = s.Length - 1; /* initialisations multiples */
 
            while (currentIndex >= 0)
            {
                // Idem que pour la fonction IntToExcelColumn : il ne s'agit pas d'un
                // simple changement de base. Les "erreurs" sont volontaires.
                ret += (s[currentIndex] - 'A' + 1) * (int)Math.Pow(26, lastIndex - currentIndex--);
            }
 
            // On n'a pas réussi à déterminer l'indice de la colonne.
            if (ret == 0)
            {
                throw new Exception(string.Format("Impossible de déterminer l'indice de la colonne '{0}'.", s));
            }
            return ret;
        }
    }
}

Juste pour le cas ret == 0 ça arrive quand exactement (à part une chaine vide que l'on pourrait tester (String.IsNullOrWhiteSpace) en début de méthode ?

Cordialement !

15/03/2012, 17h33
StringBuilder

Les this, en fait je les avais viré, car car tomlev trouvait inutile de faire ça en extension de string/int

Sinon, en effet, j'ai été très surpris par les faibles performances de la regexp...

Quant au cas où ret == 0 c'est un peu le "UnexpectedException" avec un message "bon, ben là je prends ma retraite car je comprends rien" ;)