Read from Excel

Question

I have excel file which contain multiple sheets. Excel file was created from DataSet using below code.

if (dataset.Tables.Count == 0)
    throw new ArgumentException("DataSet needs to have at least one DataTable", "dataset");

XLWorkbook wb = new XLWorkbook();
for (int i = 0; i < dataset.Tables.Count; i++)
{
    wb.Worksheets.Add(dataset.Tables[i], dataset.Tables[i].TableName);
}
wb.SaveAs(filePath + "\\DataGridViewExport.xlsx");

Every sheet contains different data, Every sheet contain a column with DateTime value. The column location would be random, no fix cell.

When i am reading the created excel file using below code

//Open the Excel file in Read Mode using OpenXml.
            using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filePath, false))
            {
                //Read the first Sheet from Excel file.
                Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild<Sheet>();

                foreach (Sheet item in doc.WorkbookPart.Workbook.Sheets)
                {
                    MessageBox.Show(item.Id.Value);

                    //Get the Worksheet instance.
                    Worksheet worksheet = (doc.WorkbookPart.GetPartById(item.Id.Value) as WorksheetPart).Worksheet;

                    //Fetch all the rows present in the Worksheet.
                    IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Descendants<Row>();

                    //Create a new DataTable.
                    DataTable dt = new DataTable();

                    //Loop through the Worksheet rows.
                    foreach (Row row in rows)
                    {
                        //Use the first row to add columns to DataTable.
                        if (row.RowIndex.Value == 1)
                        {
                            foreach (Cell cell in row.Descendants<Cell>())
                            {
                                dt.Columns.Add(GetValue(doc, cell));
                            }
                        }
                        else
                        {
                            //Add rows to DataTable.
                            dt.Rows.Add();
                            int i = 0;
                            foreach (Cell cell in row.Descendants<Cell>())
                            {
                                dt.Rows[dt.Rows.Count - 1][i] = GetValue(doc, cell);
                                i++;
                            }
                        }
                    }
                    DataTable dataTable = dt;

                }
            }

At the time of reading the DateTime cell value it give a double type number.

for ex: The date 20-11-2018 11:53:03 is converted into 43424.4951725694.