CODEWITHSUNDEEP
c#.netexcel
Lucian
Lucian

Reputation: 894

Invalid data when using ExcelLibrary in .NET


I need to read the DateTime from a cell from a xls file, but the cell contents is invalid, instead of returning the contents as a date or string it returns a number. The code to read the file:

using System.IO;
using ExcelLibrary.SpreadSheet;

namespace XLSX_reader
{
   class Program
   {
       private static ExcelDocument XlsDocument { get; set; }
       static void Main(string[] args)
       {
            var inputStream = new     MemoryStream(File.ReadAllBytes("d:\\Work\\Flexportal_Other\\Importers\\11-2016_Uitzendrapportage_SRS_Personeel_85873112442611460665848.xls"));
        XlsDocument = new ExcelDocument(inputStream, null);
        Cell cell1 = XlsDocument.GetCell(6, 4);
        XlsDocument.GetCell(5, 7);
        var date = ExcelDocument.ParseDateTime(cell1);
    }
  }
}

ExcellDocument class:

namespace XLSX_reader
{
    public class ExcelDocument
    {
        public ExcelDocument(MemoryStream stream, string sheetName = null)
        {
            Workbook = LoadWorkbook(stream);
            if (Workbook == null)
            {
                throw new InvalidOperationException("err");
             }

             Worksheet = LoadWorksheet(sheetName);
        }
     public Worksheet LoadWorksheet(string sheetName = null)
    {
        if (Workbook == null)
        {
            throw new InvalidOperationException(
                "##(Load workbook before loading worksheets)(Open eerst een werkboek alvorens een werkblad te openen)##");
        }

        Worksheet = sheetName != null
            ? Workbook.Worksheets.FirstOrDefault(ws => ws.SheetType == SheetType.Worksheet && ws.Name == sheetName)
            : Workbook.Worksheets.FirstOrDefault(ws => ws.SheetType == SheetType.Worksheet);

        if (Worksheet == null)
        {
            throw new InvalidOperationException("##noWorksheetLoaded##");
        }

        RowIndexMin = Worksheet.Cells.FirstRowIndex;
        RowIndexMax = Worksheet.Cells.LastRowIndex;
        _rowIndex = RowIndexMin - 1;

        return Worksheet;
    }
    public Cell GetCell(int row, int column)
    {
        return Worksheet.Cells[row, column];
    }
  }
 }

The xls looks like this: enter image description here

And the reading of the cell looks like this: enter image description here

So the outcome is 42443, not a date or anything. Do you have any idea why this happens and how can I fix it? Thanks

Upvotes: 1

Views: 1074

Answers (1)

lgaud
lgaud

Reputation: 2479

Excel stores dates as numbers, specifically, the number of days since Jan 1 1900. Microsoft article

Excel stores all dates as integers and all times as decimal fractions. With this system, Excel can add, subtract, or compare dates and times just like any other numbers, and all dates are manipulated by using this system.

In this system, the serial number 1 represents 1/1/1900 12:00:00 a.m. Times are stored as decimal numbers between .0 and .99999, where .0 is 00:00:00 and .99999 is 23:59:59. The date integers and time decimal fractions can be combined to create numbers that have a decimal and an integer portion. For example, the number 32331.06 represents the date and time 7/7/1988 1:26:24 a.m.

Upvotes: 1

Related Questions