0
Answer

Reading Excel file using C#

Manoj Bist

Manoj Bist

18y
1.9k
1

I am reading a excel file into Datatable using C# and displaying it in GridView control ,
rest everything is fine but the date field date appears as a double value, it looses its date formatting.

How to resolve this problem............

here is a sample code

app = new Excel.ApplicationClass();
String filename = "D:\\Test.xls";

            Excel.WorkbookClass workbook = (Excel.WorkbookClass)app.Workbooks.Open(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            Excel.Sheets excelSheets = workbook.Worksheets;
            Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(1);

            Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range("A1", "c29");

            DataTable dt = new DataTable("Expense");
            dt.Columns.Add("Date", Type.GetType("System.String"));
            dt.Columns.Add("Item", Type.GetType("System.String"));
            dt.Columns.Add("Price", Type.GetType("System.String"));


            for (int i = 2; i <= excelCell.Rows.Count; i++)
            {
                DataRow dr = dt.NewRow();

                dr["Date"] = ((Excel.Range)excelCell.Cells[i, 1]).Value2.ToString();
                dr["Item"] = ((Excel.Range)excelCell.Cells[i, 2]).Value2.ToString();
                dr["Price"] = ((Excel.Range)excelCell.Cells[i, 3]).Value2.ToString();

                dt.Rows.Add(dr);
           
            }
            gv.DataSource = dt;