Reading Excel Files using c#

Question

My code for reading excel file is working properly for many of the rows but it doesnt display some contents in the file for e.g(#######) and dates for e.g (8/11/2009) but do display dates which have a 2 digit day in it for e.g(11/12/2011).

DataTable dtExcel = new DataTable();

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + HafizwalaFile + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1'";
string strSQL = "SELECT * FROM [qe$] ";
OleDbConnection excelConnection = new OleDbConnection(connectionString);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(strSQL, excelConnection);

dataAdapter.Fill(dtExcel);
   for (int i = 0; i < dtExcel.Rows.Count; i++)
   {
      for (int j = 0; j < dtExcel.Columns.Count;j++ )
      {
         System.Console.WriteLine(dtExcel.Rows[i][j]);
         count_records++;
      }
   }

Your question is a bit confusing, but to be clear, it is showing dates like '11/11/2013' but not '11/8/2013'? — Brian
– Brian, Commented Nov 13, 2013 at 17:05
Usually when you see hashtags in excel its because the column width is too narrow. Have you expanded the column? — crthompson
– crthompson, Commented Nov 13, 2013 at 17:06

محمد النعيمي · Accepted Answer · 2013-11-13 18:50:03Z

1

Before the transfer of data to the table, you have to enter the fileexcel and adjust the worksheet width of this columns are as follows

 //using Microsoft.Office.Interop.Excel;
        Microsoft.Office.Interop.Excel.Application excelapp = new Microsoft.Office.Interop.Excel.Application();
       // excelapp.Visible = true;
        string filename_Path = @"D:\exmp1.xls";
        _Workbook workbook = (_Workbook)(excelapp.Workbooks.Open(filename_Path, 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));



    excelapp .Cells .Columns .AutoFit ();
    workbook.Save();
   // workbook.Close();
    excelapp.Quit();

edited Nov 13, 2013 at 18:50

answered Nov 13, 2013 at 17:44

محمد النعيمي

5702 gold badges9 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

user1056466 Over a year ago

how to enter the file excel??? how will i access my sheet in the code...as i did above by using a query..

محمد النعيمي Over a year ago

Do not have access to the file that you are using? Do you know the name and path?

user1056466 Over a year ago

Yes setting the width solved my problem i did it manually in the file but how can i do it while programming idont know the library of worksheet...how to use worksheet.Cells.Columns.AutoFit();? what is the library for it + how to get the worksheet name ?

user1056466 Over a year ago

thanks you muhammad it did work.. i have to address another problem now after every "date" its displays 12:00:00AM do you know about this problem? Other wise thanks alot for your help.

محمد النعيمي Over a year ago

DateTime date=convert.ToDateTime(dtExcel.Rows[i][j]); string dateShort = date.ToShortDateString(); System.Console.WriteLine(dateShort ); @

|

MichaelEvanchik · Accepted Answer · 2013-11-13 17:06:50Z

0

I would suggest formatting that column of the excel into date format that forces it to pad 0's or keep it straight text. I don't think there is anything you can do on the c# side of things

answered Nov 13, 2013 at 17:06

MichaelEvanchik

1,7661 gold badge14 silver badges23 bronze badges

1 Comment

huMpty duMpty Over a year ago

Is this answer the question?

houssam · Accepted Answer · 2013-11-13 19:19:00Z

this occurred because you format a column as Date , and you entered data that is not a valid date. you can check this from C# using :
MessageBox.Show(dtExcel.Columns[0].DataType.ToString());
Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt Read this
I tried Many times and I find that the following code works fine , (Test it please) :

String cellValue = "41577.333333"; // 30/10/2013  8:00:00 AM
            int days  = 0 ;
            int hours = 0;
            string[] arr =  cellValue.Split('.');
            days = int.Parse(arr[0]);
            if(arr.Length == 2)
            {
                decimal  d_hours = decimal.Parse("0." +  arr[1]);
                hours =(int) ( d_hours  * 24  );
            }

            TimeSpan dateFromExcel = new TimeSpan(days ,hours ,0,0,0);
            DateTime resultingDate = new DateTime(1900,1,1 ,0,0,0).Add(dateFromExcel).AddDays(-2);
            MessageBox.Show(resultingDate.ToString());

try to use ACE instead of JET , some web sites mentioned this:

string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;;Data Source=" + HafizwalaFile + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1'";

Collectives™ on Stack Overflow

Reading Excel Files using c#

3 Answers 3

10 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related