Read Excel File + C#

[sonia.sardana]

I have excel file.I want to fill dataset from that excel file & Have to fill the database. I m able to read the excel file,but there is one problem with column whose datatype is Date.

Actual Payment Date
12/15/2009

If I have the above date, means first number is 12 --(Equal to number of months). Then in dataset data is comng correctly frm the excel file,If I have 13/15/2009 then for this value in dataset "" is there. Y so??.I want the dataset to be filled & want my dates in excel format to be dd/mm/yyyy. I just want that if the date is 13/15/2006,It will come in my dataset ,dat is filled from the excel file.

 protected void BtnSubmit_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
                //FileUpload1.SaveAs(Server.MapPath("Upload/" + FileUpload1.FileName));
            //String sConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("Upload/" + FileUpload1.FileName) + ";Extended Properties=Excel 8.0;";
                String sConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileUpload1.PostedFile.FileName  + ";Extended Properties=Excel 8.0;";
            OleDbConnection Exlcon = new OleDbConnection(sConnectionString);
            string sDate="";
            string sRowsNtInserted = "";
            int iRows = 0;
            DateTime date1;
            string sMonthName="";

            try
            {
                Exlcon.Open();
            }
            catch
            {
                ShowAlertMessage("Please select valid excel file.");
                return;
            }
            OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1$]", Exlcon);
            OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
            objAdapter1.SelectCommand = objCmdSelect;
            DataSet objDataset1 = new DataSet();
            objAdapter1.Fill(objDataset1, "XLData");

I have attached the excel file (Pic1).
In Pic2,I have attached the snapshot of dataset that is filled.

[brin351]

13/15/2010 is an invalid date, what month is the 13th? So it appears that because ado is interpreting that excel column as a date column the rows with invalid dates will not be added to the dataset. This will also be a problem if you try and insert an invalid date into database column of type datetime.

You can get ado to interpret the "actual payment date" column as type string. By default ado looks at the first 8 rows and takes it's best guess at what the data type is (limitation of ado on excel) so you would have to insert some charas into the first row of that column to get ado to see it as type string and therefore not exclude invalid dates.

Really your problem is bad data probably best to let it bad dates go null, what's their worth anyway?

[sonia.sardana]

hi brin,Sorry dats my mistake my date is 15/12/2010 its in fornat dd/mm/YYYY. To insert the data correctly into a dataset i change the column format in the foll. way--

Use this Steps to format your column:

* Select Whole Date Column in the Excel sheet

* Go to Data Menu -> Select Text to columns

* Check Delimited (This is not useful, even keep this)

* Select Next Button

* Uncheck All Checkboxes

* Select Next Button

* In Column Data Format, Select Date "DMY"

* To Apply the Format, Click Finish Button.

I just want to ask that,can we format dat column before inserting the data,because we have to send the excel file to the client.

[brin351]

The problem is excel, ado etc will interpret dates in MM/dd/yyyy so if your date is in dd/MM/yyyy and can be interpreted as MM/dd/yyyy it probably will be. It's best to store/think of your dates in MM/dd/yyyy and just format them when output. To avoid confusion you can use dd/MMM/yyyy where MMM is "short month name" and ado, DB etc recognise this eg (3/9/2010 is 3/Sept/2010) that can't be confused - again that is for output/input not storage.

OK I'm not sure on the sequece of events but you can't format a column of data within a dataset, you can format it in the query before it goes in or in say a grid control during binding. How are you creating the excel file?

Can you specify the sequence of events, there is a database, excel file creation, excel file upload?

[sonia.sardana]

I have the excel file, I m not creating the excel file thru coding.
I created the excel file & enters the date in the format dd/MM/YYYY,as excel treats the dates in the format MM/dd/YYYY,To change the format i follow the foll. steps-
Use this Steps to format your column:

* Select Whole Date Column in the Excel sheet

* Go to Data Menu -> Select Text to columns

* Check Delimited (This is not useful, even keep this)

* Select Next Button

* Uncheck All Checkboxes

* Select Next Button

* In Column Data Format, Select Date "DMY"

* To Apply the Format, Click Finish Button.

Above is working correctly for me. But client dont follow the above steps everytime the moment he did some chnages in date column. SO i want to ask that we can define the format of column in excel before entering any data,We send dat excel file to the client.

Then I have to upload the excel file data first into a dataset & then from dataset into database!!!

[brin351]

OK now I understand your difficulty. To control users entering dates into excell cells it is posible to use a "date picker", like the callendar control in .net. I don't do MS office developement so you'd have to look into the viability. If you google excel date picker you'll find refrences and also VBA macros.

If that is the type of thing you want you could post that question in the office api forum here and ask what effect it will have on different users downloading and working on your excel file.

[avrail]

hey,
try this
http://www.aspose.com/community/foru...el-export.aspx

[gep13]

I have to agree with brin, this is sounding more like a question related to Office, as opposed to ASP.Net. If you agree, then I can ask to have your question moved over to the appropriate forum.

Gary