public string GetText(int row, Column column)
{
if (row < 1 || column < 1) throw new ArgumentException();
return Globals.ThisAddIn.Application.get_Range(string.Format("{0}{1}", column, row)).Text;
}
public void SetText(int row, Column column, object value)
{
if (row < 1 || column < 1) throw new ArgumentException();
Globals.ThisAddIn.Application.get_Range(string.Format("{0}{1}", column, row)).set_Value(XlRangeValueDataType.xlRangeValueDefault, value);
}
public static IList<Sequence> GetSeqenceData(this ISpreadsheet app)
{
string startText = null;
int startRow = 1;
Column startColumn = new Column(1);
//Search for the start of the table in the worksheet
while (true)
{
if (startRow > 100)
{
throw new InvalidOperationException("Could not find the start of the table within 100 cells of the start of the worksheet");
}
startText = app.GetText(startRow, startColumn);
if (string.IsNullOrWhiteSpace(startText))
{
startRow++;
}
else
{
break;
}
}
int numRows = 0;
string nucleotide = null;
//Search for the bottom of the table
while (true)
{
nucleotide = app.GetText(startRow + numRows + 1, startColumn);
if (string.IsNullOrWhiteSpace(nucleotide))
{
break;
}
else
{
numRows++;
}
}
List<Sequence> sequences = new List<Sequence>();
Column endColumn = 1;
string label = null;
//Search for the right edge of the table
while (true)
{
label = app.GetText(startRow, endColumn);
if (!string.IsNullOrWhiteSpace(label))
{
sequences.Add(new Sequence { Label = label });
endColumn++;
}
else
{
break;
}
}
//Parse the genetic sequence data
for (int j = 0; j < sequences.Count; j++)
{
Sequence sequence = sequences[j];
var strings = Enumerable.Range(0, numRows)
.Select(i => app.GetText(startRow + i + 1, startColumn + j));
if (strings.Any(s => string.IsNullOrWhiteSpace(s)))
{
throw new InvalidOperationException("The DNA sequence table is not even in length. Check input and try again.");
}
var chars = strings.Select(s => s.ToUpper().First());
sequence.Data = ParseNucleotides(chars.ToList());
}
return sequences;
}