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(); } }