I currently am writing a system that includes reading in excel files. I want to read in this excel file and potentially spew it out into a csv. However, the issue im running into is that it is keeping the format that excel uses. For example i have a long number that in excel displays as 3.9151+E15, and it reads it in like this. When i highlight the cell in excel it shows the real number '3915100000026840'. This is the number i want to receive. It also adds a timestamp to dates which i do not want. It adds 00:00 00:00:000 or something similar to 17/05/2018, which is all i want. So basically, i want to retrieve the real text values from this excel spreadsheet.
The code i have at the minute is
public static DataTable READExcel(string path)
{
Microsoft.Office.Interop.Excel.Application objXL = null;
Microsoft.Office.Interop.Excel.Workbook objWB = null;
objXL = new Microsoft.Office.Interop.Excel.Application();
objWB = objXL.Workbooks.Open(path);
Microsoft.Office.Interop.Excel.Worksheet objSHT = objWB.Worksheets[1];
int rows = objSHT.UsedRange.Rows.Count;
int cols = objSHT.UsedRange.Columns.Count;
DataTable dt = new DataTable();
int noofrow = 1;
for (int c = 1; c <= cols; c++)
{
string colname = objSHT.Cells[1, c].Value.ToString();
dt.Columns.Add(colname);
noofrow = 2;
}
for (int r = noofrow; r <= rows; r++)
{
DataRow dr = dt.NewRow();
for (int c = 1; c <= cols; c++)
{
dr[c - 1] = objSHT.Cells[r, c].Value.ToString();
}
dt.Rows.Add(dr);
}
objWB.Close();
objXL.Quit();
return dt;
}
(Also another question which is slightly related but slightly not related, i had a csv to start which had the value '0003915100000026845'. When i turned this csv into a excel file it changed it to the value i reference above. Will excel have remembered these leading zeros anywhere or not?)
