I am trying to get the last cell address from the excel sheet for merging purposes based on starting address and range using the below code.

I have starting cell address like X and would like to get the end cell address using the given range. For example, starting address is X, and the range is 7, then the end cell address would be AD.

I have tried with the below approach and I am getting wrong end cell address

private static readonly char[] BaseChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray();

private static readonly Dictionary<char, int> CharValues = BaseChars
           .Select((c, i) => new { Char = c, Index = i })
           .ToDictionary(c => c.Char, c => c.Index);

public static string IntToBase(int value)
{
    int targetBase = BaseChars.Length;
    // Determine exact number of characters to use.
    char[] buffer = new char[Math.Max(
               (int)Math.Ceiling(Math.Log(value   1, targetBase)), 1)];

    var i = buffer.Length;
    do
    {
        buffer[--i] = BaseChars[value % targetBase];
        value /= targetBase;
    }
    while (value > 0);

    return new string(buffer, i, buffer.Length - i);
}

public static int BaseToInt(string number)
{
    _ = number ?? throw new ArgumentNullException(nameof(number));
    char[] chrs = number.ToCharArray();
    int m = chrs.Length - 1;
    int n = BaseChars.Length, x;
    int result = 0;
    foreach (char c in chrs)
    {
        x = CharValues[c];
        result  = x * (int)Math.Pow(n, m--);
    }
    return result;
}

public static string GetLastCellAddress(string number, int cellCount)
{
    int startVal = BaseToInt(number);
    return Enumerable.Range(startVal, cellCount).Select(i => IntToBase(i)).Last();
}

And I am using above function like as below

var environmentsLastCellAddress =  ExcelBuilderExtensions.GetLastCellAddress(startColumnAddress, spaceTypeLibraryByPropertiesCount);

The above function gives the wrong end address if I have given starting cell address like X and count is 7, and I should get the end cell address as AD instead of I am getting address as BD.

Could anyone please let me know is there anything wrong with the above code? That would be very grateful to me. Many thanks in advance!!

CodePudding user response：

I am hoping the following will work for you.

int to string reference…

Convert an Excel column number to a column name or letter:

string to int reference…

Fastest method to remove Empty rows and Columns From Excel Files using Interop

static void Main(string[] args) {
  Console.WriteLine("Start: A   0: "   GetAddedRange("A", 0));
  Console.WriteLine("Start: A   1: "   GetAddedRange("A", 1));
  Console.WriteLine("Start: H   4: "   GetAddedRange("H", 4));
  Console.WriteLine("Start: AA   26: "   GetAddedRange("AA", 26));
  Console.WriteLine("Start: BA   11: "   GetAddedRange("BA", 11));
  Console.WriteLine("Start: CAA   11: "   GetAddedRange("CAA", 11));
  Console.WriteLine("Start: GAA   11: "   GetAddedRange("GAA", 11));
  Console.WriteLine("Start: Z   11: "   GetAddedRange("Z", 11));
  Console.WriteLine("Start: Z - 10: "   GetAddedRange("Z", -10));
  Console.ReadKey();
}

private static string ColumnIndexToColumnLetter(int colIndex) {
  int div = colIndex;
  string colLetter = String.Empty;
  int mod = 0;
  while (div > 0) {
    mod = (div - 1) % 26;
    colLetter = (char)(65   mod)   colLetter;
    div = (int)((div - mod) / 26);
  }
  return colLetter;
}

private static int ParseColHeaderToIndex(string colAdress) {
  int[] digits = new int[colAdress.Length];
  for (int i = 0; i < colAdress.Length;   i) {
    digits[i] = Convert.ToInt32(colAdress[i]) - 64;
  }
  int mul = 1;
  int res = 0;
  for (int pos = digits.Length - 1; pos >= 0; --pos) {
    res  = digits[pos] * mul;
    mul *= 26;
  }
  return res;
}

private static string GetAddedRange(string startCol, int addedRange) {
  int startingCol = ParseColHeaderToIndex(startCol);
  return ColumnIndexToColumnLetter(startingCol   addedRange);
}

Also from your second to last comment… X 7 would be AE… not AD.