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