エクセルの列名と列番号を変換するには

エクセルはセルを「A1」とか「AB10」のように英字の列と数字の行番号で指定するのだけど(A1スタイルと云うのかな)、たまに列番号で指定したいときもある。




ということで、列番号を渡すと列名を返すメソッドを作ってみる。このメソッドがあると、5列目を”E”列のように変換できるので、列番号で指定できるようになる。

  1.         /// <summary>
  2.         /// 列番号からエクセルの列名を得る (例 5 → "E")
  3.         /// </summary>
  4.         /// <param name="c"></param>
  5.         /// <returns></returns>
  6.         static public string GetCellA1(int c)
  7.         {
  8.             string alpha = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
  9.             string s = "";
  10.  
  11.             for (; c > 0; c = (c - 1) / 26 )
  12.             {
  13.                 int n = (c - 1) % 26;
  14.                 s =  alpha.Substring(n, 1) + s;
  15.             }
  16.             return s;
  17.         }


この種のメソッドを作るときに間違いやすいのが、1で始まるデータであるところ。上のコードでいえばfor文の3番目の式「c = (c - 1) / 26」を「c /= 26」と書いてしまったりする。そうすると、26が渡されたときにループをもう1回まわってしまって、"AA"に変換されてしまう。正しくは"Z"です。

上のメソッドをテストするのが次のコード。

  1.         [TestMethod()]
  2.         public void GetCellA1Test()
  3.         {
  4.             Assert.AreEqual(ExcelLib.Util.GetCellA1(1), "A");
  5.             Assert.AreEqual(ExcelLib.Util.GetCellA1(26), "Z");
  6.             Assert.AreEqual(ExcelLib.Util.GetCellA1(27), "AA");
  7.             Assert.AreEqual(ExcelLib.Util.GetCellA1(702), "ZZ");
  8.             Assert.AreEqual(ExcelLib.Util.GetCellA1(703), "AAA");
  9.  
  10.             for (int i = 1; i <= 26; i++)
  11.             {
  12.                 string s = Convert.ToChar(Convert.ToInt32('A') + i - 1).ToString();
  13.                 Assert.AreEqual(ExcelLib.Util.GetCellA1(i), s);
  14.             }
  15.         }

前の投稿で文字コードのことを調べていたのは、このテストでアルファベット26文字分のテストをループを回して済ませたかったからなのでした。



逆に”A”を渡すと1を返すメソッドも作ってみる。

  1.         /// <summary>
  2.         /// エクセルの列名から列番号を得る(例:"A"→1)
  3.         /// </summary>
  4.         /// <param name="col"></param>
  5.         /// <returns></returns>
  6.         static public int getExcel列番号(string col)
  7.         {
  8.             string abc = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
  9.             int m = 0;
  10.  
  11.             for (int i = 0; i < col.Length; i++)
  12.             {
  13.                 string c = col.Substring(i, 1);
  14.                 int n = abc.IndexOf(c) + 1;
  15.                 m += n * (int)Math.Pow(26, col.Length - i - 1);
  16.             }
  17.  
  18.             return m;
  19.         }

作った時期が違うので、メソッド名も引数もローカル変数名もバラバラですな、なかなか恥ずかしい。

そして単体テストがこちら。

  1.         [TestMethod]
  2.         public void Test_getExcel列番号()
  3.         {
  4.             Assert.AreEqual(mmExcel.getExcel列番号("A"), 1);
  5.             Assert.AreEqual(mmExcel.getExcel列番号("B"), 2);
  6.             Assert.AreEqual(mmExcel.getExcel列番号("C"), 3);
  7.             Assert.AreEqual(mmExcel.getExcel列番号("AA"), 27);
  8.             Assert.AreEqual(mmExcel.getExcel列番号("AAA"), 703);  // 26 * 27 + 1 = 26 * (26 + 1) + 1
  9.  
  10.             int c = Convert.ToInt32('A');
  11.  
  12.             for (int i = 0; i < 26; i++)
  13.             {
  14.                 string s = Convert.ToChar(c + i).ToString();
  15.                 Assert.AreEqual(mmExcel.getExcel列番号(s), i + 1);
  16.             }
  17.         }

こうしてテストで確認できるようにしてあると、確かに安心。

コメント

このブログの人気の投稿

varchar をデータ型 numeric に変換中に、算術オーバーフロー エラーが発生しました。