int TryGetValue(IXLCell cell)
{
try {
return((int)cell.GetDouble());
} catch {
return(0);
}
}
public IDictionary <ExcelDataType, object> ReadExcelNamedRange(IXLWorkbook workbook, string namedRange)
{
IXLNamedRange range = workbook.NamedRange(namedRange);
if (range == null)
{
throw new ClosedXMLReadException($"There is no named range called {namedRange} in this workbook");
}
int count = range.Ranges.First().Cells().Count();
//if solo then don't return collection.
if (count.Equals(1))
{
IXLCell cell = range.Ranges.First().Cells().First();
switch (cell.DataType)
{
case XLDataType.DateTime:
return(new Dictionary <ExcelDataType, object>
{
{ ExcelDataType.Numeric, cell.GetDateTime().ToOADate() }
});
case XLDataType.Number:
return(new Dictionary <ExcelDataType, object>
{
{ ExcelDataType.Numeric, cell.GetDouble() }
});
case XLDataType.Text:
string textValue = GetSingleCellTextValue(cell);
KeyValuePair <ExcelDataType, object> parsed = ParseString(textValue);
switch (parsed.Key)
{
case ExcelDataType.Numeric:
return(new Dictionary <ExcelDataType, object>
{
{ ExcelDataType.Numeric, (double)parsed.Value }
});
case ExcelDataType.Text:
string[,] array = new string[1, 1];
array[0, 0] = textValue;
return(new Dictionary <ExcelDataType, object>
{
{ ExcelDataType.Text, array }
});
default:
throw new NotImplementedException("I haven't implemented formulas yet");
}
}
}
IXLTable table = range.Ranges.First().AsTable();
return(ReadTable(table));
}
public IDictionary <ExcelDataType, object> ReadTable(IXLTable table)
{
int rows = table.RowCount();
int columns = table.ColumnCount();
var numericData = new double?[rows, columns];
var textData = new string[rows, columns];
var formula = new string[rows, columns];
for (int i = 1; i <= rows; i++)
{
for (int j = 1; j <= columns; j++)
{
IXLCell cell = table.Cell(i, j);
switch (cell.DataType)
{
case XLDataType.Number:
numericData[i - 1, j - 1] = cell.GetDouble();
break;
case XLDataType.Text:
{
string textValue = GetSingleCellTextValue(cell);
KeyValuePair <ExcelDataType, object> parsed = ParseString(textValue);
switch (parsed.Key)
{
case ExcelDataType.Numeric:
numericData[i - 1, j - 1] = (double)parsed.Value;
break;
case ExcelDataType.Text:
textData[i - 1, j - 1] = textValue;
break;
default:
throw new NotImplementedException("I haven't implemented formulas yet");
}
break;
}
}
if (cell.HasFormula)
{
formula[i - 1, j - 1] = cell.FormulaA1;
}
}
}
return(new Dictionary <ExcelDataType, object>
{
{ ExcelDataType.Numeric, numericData },
{ ExcelDataType.Formulae, formula },
{ ExcelDataType.Text, textData }
});
}
public void AccessRichTextTest1()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
IXLCell cell = ws.Cell(1, 1);
cell.RichText.AddText("12");
cell.DataType = XLCellValues.Number;
Assert.AreEqual(12.0, cell.GetDouble());
IXLRichText richText = cell.RichText;
Assert.AreEqual("12", richText.ToString());
richText.AddText("34");
Assert.AreEqual("1234", cell.GetString());
Assert.AreEqual(XLCellValues.Number, cell.DataType);
Assert.AreEqual(1234.0, cell.GetDouble());
}
public static dynamic GetValue(this IXLCell cell, Type dataType)
{
if (dataType.Equals(typeof(string)))
{
return(cell.GetValue <string>());
}
else if (dataType.Equals(typeof(int)))
{
return(cell.GetValue <int>());
}
else if (dataType.Equals(typeof(uint)))
{
return(cell.GetValue <uint>());
}
else if (dataType.Equals(typeof(DateTime)))
{
return(DateTime.FromOADate(cell.GetDouble()));
}
else if (dataType.Equals(typeof(short)))
{
return(cell.GetValue <short>());
}
else if (dataType.Equals(typeof(ushort)))
{
return(cell.GetValue <ushort>());
}
else if (dataType.Equals(typeof(float)))
{
return(cell.GetValue <float>());
}
else if (dataType.Equals(typeof(double)))
{
return(cell.GetValue <double>());
}
else if (dataType.Equals(typeof(long)))
{
return(cell.GetValue <long>());
}
else if (dataType.Equals(typeof(ulong)))
{
return(cell.GetValue <ulong>());
}
else
{
throw new TypeNotSupportedException();
}
}