private List<string> rowToList(Row r)
{
string[] rowAsStringArray = new string[numOfColumns];
// create a new cell
Cell c = new Cell();
for (int i = 0; i < r.ChildElements.Count(); i++)
{
// get current cell at i
c = r.Elements<Cell>().ElementAt(i);
string value = "";
if (c != null)
{
if (c.CellValue != null)
{
// if cell reference in range of the area
int cellReferencAsInterger = getColumnNumber(getColumnName(c.CellReference));
int start = getColumnNumber(this.areaOfData.StartColumn);
int end = getColumnNumber(this.areaOfData.EndColumn);
if (cellReferencAsInterger >= start && cellReferencAsInterger <= end)
{
// if Value a text
if (c.DataType != null && c.DataType.HasValue && c.DataType.Value == CellValues.SharedString)
{
int sharedStringIndex = int.Parse(c.CellValue.Text, CultureInfo.InvariantCulture);
SharedStringItem sharedStringItem = sharedStrings[sharedStringIndex];
value = sharedStringItem.InnerText;
}
// not a text
else if (c.StyleIndex != null && c.StyleIndex.HasValue)
{
uint styleIndex = c.StyleIndex.Value;
CellFormat cellFormat = stylesheet.CellFormats.ChildElements[(int)styleIndex] as CellFormat;
if (cellFormat.ApplyNumberFormat != null && cellFormat.ApplyNumberFormat.HasValue && cellFormat.ApplyNumberFormat.Value && cellFormat.NumberFormatId != null && cellFormat.NumberFormatId.HasValue)
{
uint numberFormatId = cellFormat.NumberFormatId.Value;
// Number format 14-22 and 45-47 are built-in date and/or time formats
if ((numberFormatId >= 14 && numberFormatId <= 22) || (numberFormatId >= 45 && numberFormatId <= 47))
{
DateTime dateTime = DateTime.FromOADate(double.Parse(c.CellValue.Text, CultureInfo.InvariantCulture));
value = dateTime.ToString();
}
else
{
if (stylesheet.NumberingFormats != null && stylesheet.NumberingFormats.Any(numFormat => ((NumberingFormat)numFormat).NumberFormatId.Value == numberFormatId))
{
NumberingFormat numberFormat = stylesheet.NumberingFormats.First(numFormat => ((NumberingFormat)numFormat).NumberFormatId.Value == numberFormatId) as NumberingFormat;
if (numberFormat != null && numberFormat.FormatCode != null && numberFormat.FormatCode.HasValue)
{
string formatCode = numberFormat.FormatCode.Value;
if ((formatCode.Contains("h") && formatCode.Contains("m")) || (formatCode.Contains("m") && formatCode.Contains("d")))
{
DateTime dateTime = DateTime.FromOADate(double.Parse(c.CellValue.Text, CultureInfo.InvariantCulture));
value = dateTime.ToString();
}
else
{
value = c.CellValue.Text;
}
}
else
{
value = c.CellValue.Text;
}
}
else
{
value = c.CellValue.Text;
}
}
}
else
{
value = c.CellValue.Text;
}
}
// define index based on cell refernce - offset
int index = cellReferencAsInterger - offset - 1;
rowAsStringArray[index] = value;
}
}//end if cell value
}//end if cell null
}//for
return rowAsStringArray.ToList();
}
