private void AppendToRow(ExcelCell data, Row row, ref Cell old)
{
Cell newCell = new Cell();
row.InsertAfter(newCell, old);
if (data.Value == null)
{
newCell.CellValue = new CellValue("");
newCell.DataType = new EnumValue <CellValues>(CellValues.String);
old = newCell;
return;
}
try
{
newCell.CellValue = new CellValue(data.Value.ToString(System.Globalization.CultureInfo.InvariantCulture.NumberFormat));
}
catch
{
newCell.CellValue = new CellValue("");
}
if (data.ValueType == typeof(DateTime))
{
newCell.CellValue = ToExcelDate(data.Value);
newCell.DataType = new EnumValue <CellValues>(CellValues.String);
}
else if (data.ValueType == typeof(string))
{
newCell.DataType = new EnumValue <CellValues>(CellValues.String);
}
else if (data.ValueType == typeof(int) || data.ValueType == typeof(long) || data.ValueType == typeof(float) || data.ValueType == typeof(double) || data.ValueType == typeof(decimal))
{
newCell.DataType = new EnumValue <CellValues>(CellValues.Number);
}
else if (data.GetType() == typeof(DateTime))
{
newCell.DataType = new EnumValue <CellValues>(CellValues.Date);
}
old = newCell;
}
public List<ExcelRow> ReadRows()
{
List<ExcelRow> tableData = new List<ExcelRow>();
foreach (var dataWithMeta in SheetDataWithMeta)
foreach(Row row in dataWithMeta.Item1.ChildElements)
{
//List<string> rowData = new List<string>();
ExcelRow rowData = new ExcelRow();
foreach (Cell c in row.ChildElements)
{
if (c == null || c.CellValue == null || string.IsNullOrWhiteSpace(c.CellValue.Text))
continue;
var styles = dataWithMeta.Item3;
//Dates from excel: http://blogs.msdn.com/b/eric_carter/archive/2004/08/14/214713.aspx
ExcelCell val = null;
CellFormat toFindNumbFormat = null;
if(c.StyleIndex != null && styles != null)
toFindNumbFormat = styles.CellFormats.ToArray()[c.StyleIndex.Value] as CellFormat;
bool isDate = false;
if (toFindNumbFormat != null && toFindNumbFormat.NumberFormatId.HasValue && toFindNumbFormat.NumberFormatId.Value != 0)
{
var index = toFindNumbFormat.NumberFormatId.Value;
if (index >= 163)
{
NumberingFormat format = styles.NumberingFormats.First(x => x is NumberingFormat && ((NumberingFormat)x).NumberFormatId == toFindNumbFormat.NumberFormatId.Value) as NumberingFormat;
if (format != null && format.FormatCode.HasValue && VerifyDateFormatCode(format.FormatCode.Value))
isDate = true;
}
else if ((index >= 14 && index <= 22) || (index >= 45 && index <= 47))
isDate = true;
}
double doubleVal;
long intVal;
if (c.CellValue == null)
continue;
if (c.DataType != null && c.DataType == CellValues.SharedString)
val = new ExcelCell(dataWithMeta.Item2.SharedStringTable.ElementAt(int.Parse(c.CellValue.Text)).InnerText);
else if (isDate)
val = new ExcelCell(GetDateFromExcelDate(c.CellValue.Text));
else if (c.DataType != null && c.DataType == CellValues.Date)
val = new ExcelCell(DateTime.Parse(c.CellValue.Text));
else if (long.TryParse(c.CellValue.Text, System.Globalization.NumberStyles.Integer, System.Globalization.CultureInfo.InvariantCulture.NumberFormat, out intVal))
val = new ExcelCell(intVal);
else if (double.TryParse(c.CellValue.Text, System.Globalization.NumberStyles.Any, System.Globalization.CultureInfo.InvariantCulture.NumberFormat, out doubleVal))
val = new ExcelCell(doubleVal);
else
val = new ExcelCell(c.CellValue.Text);
rowData.Add(val);
}
tableData.Add(rowData);
}
return tableData;
}
public List <ExcelRow> ReadRows()
{
List <ExcelRow> tableData = new List <ExcelRow>();
foreach (var dataWithMeta in SheetDataWithMeta)
{
foreach (Row row in dataWithMeta.Item1.ChildElements)
{
//List<string> rowData = new List<string>();
ExcelRow rowData = new ExcelRow();
foreach (Cell c in row.ChildElements)
{
if (c == null || c.CellValue == null || string.IsNullOrWhiteSpace(c.CellValue.Text))
{
continue;
}
var styles = dataWithMeta.Item3;
//Dates from excel: http://blogs.msdn.com/b/eric_carter/archive/2004/08/14/214713.aspx
ExcelCell val = null;
CellFormat toFindNumbFormat = null;
if (c.StyleIndex != null && styles != null)
{
toFindNumbFormat = styles.CellFormats.ToArray()[c.StyleIndex.Value] as CellFormat;
}
bool isDate = false;
if (toFindNumbFormat != null && toFindNumbFormat.NumberFormatId.HasValue && toFindNumbFormat.NumberFormatId.Value != 0)
{
var index = toFindNumbFormat.NumberFormatId.Value;
if (index >= 163)
{
NumberingFormat format = styles.NumberingFormats.First(x => x is NumberingFormat && ((NumberingFormat)x).NumberFormatId == toFindNumbFormat.NumberFormatId.Value) as NumberingFormat;
if (format != null && format.FormatCode.HasValue && VerifyDateFormatCode(format.FormatCode.Value))
{
isDate = true;
}
}
else if ((index >= 14 && index <= 22) || (index >= 45 && index <= 47))
{
isDate = true;
}
}
double doubleVal;
long intVal;
if (c.CellValue == null)
{
continue;
}
if (c.DataType != null && c.DataType == CellValues.SharedString)
{
val = new ExcelCell(dataWithMeta.Item2.SharedStringTable.ElementAt(int.Parse(c.CellValue.Text)).InnerText);
}
else if (isDate)
{
val = new ExcelCell(GetDateFromExcelDate(c.CellValue.Text));
}
else if (c.DataType != null && c.DataType == CellValues.Date)
{
val = new ExcelCell(DateTime.Parse(c.CellValue.Text));
}
else if (long.TryParse(c.CellValue.Text, System.Globalization.NumberStyles.Integer, System.Globalization.CultureInfo.InvariantCulture.NumberFormat, out intVal))
{
val = new ExcelCell(intVal);
}
else if (double.TryParse(c.CellValue.Text, System.Globalization.NumberStyles.Any, System.Globalization.CultureInfo.InvariantCulture.NumberFormat, out doubleVal))
{
val = new ExcelCell(doubleVal);
}
else
{
val = new ExcelCell(c.CellValue.Text);
}
rowData.Add(val);
}
tableData.Add(rowData);
}
}
return(tableData);
}