Use EPPlus to translate the location of the Excel Cell

Question

I am new to using EPPlus.

I want to adress a cell, relative to a starting point, what is the best way to achieve this?

E.g:

var startLocation = "C5";
var newLocation1 = TLoc(startLocation, 2, 3); //E8
var newLocation2 = TLoc(startLocation, 0, 1); //C6

For now I wrote this method myself, because I am unfamiliar with EPPlus functionality:

/// <summary>
/// Use EPPlus to translate the Location.
/// </summary>
/// <param name="startLocation"></param>
/// <param name="columns"></param>
/// <param name="rows"></param>
/// <returns></returns>
private string TLoc(string startLocation, int columns, int rows)
{
    using (ExcelPackage tmpPack = new ExcelPackage())
    {
        var tmpWs = tmpPack.Workbook.Worksheets.Add("temp");
        var startCell = tmpWs.Cells[startLocation];

        int startColumn = startCell.Start.Column;
        int startRow = startCell.Start.Row;

        int tColumn = startColumn + columns;
        int tRow = startRow + rows;

        ExcelAddress translatedAddress = new ExcelAddress(tRow, tColumn, tRow, tColumn);

        return translatedAddress.ToString();
    }
}

Please show me an example or link me to documentation. Thank you!

To be more specific: examples of using EPPlus to achieve this functionality.

I like to use proven technology when available instead of risking the introduction of new bugs.

Answer 1

For an EPPlus specific solution, the only way I can think of that is "shortest" is something like this:

//Prints "E8"
Console.WriteLine(ExcelCellBase.TranslateFromR1C1(ExcelCellBase.TranslateToR1C1("C5", -3, -2), 0, 0)); 

//Prints "C8"
Console.WriteLine(ExcelCellBase.TranslateFromR1C1(ExcelCellBase.TranslateToR1C1("C5", -1, 0), 0, 0)); 

Not the cleanest but since they makers of it didnt expose the translate function that backs those two methods we cant use it directly unfortunately.

Answer 2

I hope this help you something , i am not clear about your question. But i hope your looking for something like this.

Here is Solution for Converting the Integer Row to Interger Column to Excel address.

Its you need create Address before and after Data return to Excel worksheet

 public class ExcelAddress
    {
        public readonly string LabelCell;
        public readonly string ColumnAddress;
        public readonly int ColumnAddressNumber;
        public readonly int RowAddressNumber;
        public readonly string RowAddress;
        public readonly string CellAddress; // this will give you address

        public ExcelAddress(int colNum, int rowNum, string Label)
        {
            ColumnAddressNumber = colNum;
            ColumnAddress = ExcelCellAddressConvertor.FromIntegerIndexToColumnLetter(ColumnAddressNumber);
            RowAddressNumber = rowNum;
            RowAddress = RowAddressNumber.ToString();
            LabelCell = Label;
            CellAddress = ColumnAddress + RowAddress;
        }
    }

and Here is Converter Class for address mapping

using System;
using System.Text.RegularExpressions;

namespace BLABLA
{
    public static class ExcelCellAddressConvertor
    {
        public static string FromIntegerIndexToColumnLetter(int intCol)
        {
            var intFirstLetter = ((intCol) / 676) + 64;
            var intSecondLetter = ((intCol % 676) / 26) + 64;
            var intThirdLetter = (intCol % 26) + 65;

            var firstLetter = (intFirstLetter > 64)
                ? (char)intFirstLetter : ' ';
            var secondLetter = (intSecondLetter > 64)
                ? (char)intSecondLetter : ' ';
            var thirdLetter = (char)intThirdLetter;

            return string.Concat(firstLetter, secondLetter,
                thirdLetter).Trim();
        }

        public static int ConvertColumnNameToNumber(string columnName)
        {
            var alpha = new Regex("^[A-Z]+$");
            if (!alpha.IsMatch(columnName)) throw new ArgumentException();

            char[] colLetters = columnName.ToCharArray();
            Array.Reverse(colLetters);

            var convertedValue = 0;
            for (int i = 0; i < colLetters.Length; i++)
            {
                char letter = colLetters[i];
                // ASCII 'A' = 65
                int current = i == 0 ? letter - 65 : letter - 64;
                convertedValue += current * (int)Math.Pow(26, i);
            }

            return convertedValue;
        }
    }
}

EEPLUS GOOD API for Excel automation,

But still simple and clear one

i would recommended you http://spreadsheetlight.com/