Fastest way to enter Excel reference range ("C20: F22")
Let's assume this string variable is a range of cells in Excel:
string RangeReference = "C20:F22";
So, I need a list of links in RangeReference
For example:
List<string> GetAllReferencesInRange(string RangeReference) { }
In this case, we will have:
GetAllReferencesInRange(RangeReference);
//=
//C20
//D20
//E20
//F20
//C21
//D21
//E21
//F21
//C22
//D22
//E22
//F22
And if RangeReference = "AG9:AI11";
Then:
GetAllReferencesInRange(RangeReference);
//=
//AG9
//AH9
//AI9
//AG10
//AH10
//AI10
//AG11
//AH11
//AI11
So what's the fastest way to get this?
Update:
I know this question is not related to Excel at all, but I am using OpenXML SDK 2.5 to get the Excel file and items, so maybe there is an easy way in OpenXML that I don't know.
source to share
I found something, but I don't know if this is the fastest way or not.
First, I create a dictionary that stores the column name and associated number:
Dictionary<string, ulong> ColumnsInNumber = new Dictionary<string, ulong>();
string Alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
ulong SpecialNumber = 0;
foreach (char FirstChar in Alphabet)
ColumnsInNumber.Add(FirstChar.ToString(), SpecialNumber++);
foreach (char FirstChar in Alphabet)
foreach (char SecondChar in Alphabet)
ColumnsInNumber.Add(string.Format("{0}{1}", FirstChar, SecondChar), SpecialNumber++);
foreach (char FirstChar in Alphabet)
foreach (char SecondChar in Alphabet)
foreach (char ThirdChar in Alphabet)
ColumnsInNumber.Add(string.Format("{0}{1}{2}", FirstChar, SecondChar, ThirdChar), SpecialNumber++);
In Excel 2010, the last column name is "XFD", in the above we set the numbers to columns from "A" to "ZZZ"
So, we can iterate over rows and columns:
string FirstColumn = GetColumnName(GetFirstCellReference(RangeReference));
string LastColumn = GetColumnName(GetLastCellReference(RangeReference));
uint FirstRow = GetRowNumber(GetFirstCellReference(RangeReference));
uint LastRow = GetRowNumber(GetLastCellReference(RangeReference));
List<string> Result = new List<string>();
for (uint row = FirstRow; row <= LastRow; row++)
{
for (ulong column = ColumnsInNumber[FirstColumn]; column <= ColumnsInNumber[LastColumn]; column++)
{
string ColumnName = ColumnsInNumber.Where(kv => kv.Value == column).FirstOrDefault().Key;
Result.Add(string.Format("{0}{1}", ColumnName, row));
}
}
source to share
First check that it is in the correct format and then:
static readonly List<char> alphabetUpper = new List<char> { 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z' };
static public IEnumerable<string> GetRange(string arg)
{
arg = arg.ToUpperInvariant();
string[] ranges = arg.Split(':');
ulong range0 = ulong.Parse((new string((from char c in ranges[0] where char.IsDigit(c) select c).ToArray())));
ulong range1 = ulong.Parse ((new string((from char c in ranges[1] where char.IsDigit(c) select c).ToArray())));
ranges[0] = new string((from char c in ranges[0] where !char.IsDigit(c) select c).ToArray());
ranges[1] = new string((from char c in ranges[1] where !char.IsDigit(c) select c).ToArray());
IEnumerable<int> arrayindexes0 = from c in ranges[0] select alphabetUpper.IndexOf(c);
IEnumerable<int> arrayindexes1 = from c in ranges[1] select alphabetUpper.IndexOf(c);
foreach (IEnumerable<int> f in (from i in arrayindexes0 from e in arrayindexes1 select new List<int>() {i, e}))
{
foreach (int i in f)
{
foreach (ulong o in LongRange(range0, range1))
{
yield return alphabetUpper[i] + o.ToString();
}
}
}
}
static IEnumerable<BigInteger> LongRange (BigInteger first, BigInteger second)
{
BigInteger difference = second - first;
for (BigInteger i = 0; i <= difference; i++)
{
yield return first + i;
}
}
Works for single letter at the moment, I am working on making more strings.
source to share