Import from Excel to datagrid using c#.-- URGENT.

Posted on 2008-06-13
Medium Priority
Last Modified: 2010-04-21

    I have a datagrid with about 10 columns in it.Now i want to import the values in excel file to datagrid.

scenario :

1-----There are only 5 columns in excel.So i want to match the column names for excel and datagrid for populating the same column values in datagrid.
2-----I have a session value session["RiskID"]. I want to check this value against all the values in first column"RiskId" in excel. If any one value in the column dosen't match, then it shud not import to datagrid.Instead it shud prompt a message to user.
3-----I have a column called "SI" in excel. I want to check all the values in this column. If any one value in the column has "0", then it shud not import to datagrid.Instead it shud prompt a message to user.
4-----If any values in excel has values like " Microsoft's " with an apostaphe, it shud not be a problem in importing the values to datagrid.

Please help me on this. Thanks. Its very Urgent
Question by:srk1982

Accepted Solution

ron10023 earned 750 total points
ID: 21776730
Hi srk1982,

First of all, you should never use signs like apostaphe in excel because the structure of an excel file is basically xml and we all know that xml doesn't handle those signs well.

After that said, just go to www.connectionstrings.com and take a connection string for excel. This way you can use the excel file just like a database and use an sql query for all your needs.

Hope it helped

LVL 14

Assisted Solution

by:Dustin Hopkins
Dustin Hopkins earned 750 total points
ID: 21776945
Ok lets take a swing...
1---Just set your datagrid not to load the columns automatically, instead add them as bound columns and set the datafield to the column name in excel.
2---not completely sure what you mean by not loading, you may want to change your select statement to filter your results or you can loop throught he datagrid and change/edit text.
4---There shouldn't be a problem with apostophes, though it is not prefered usage in excel documents

'Code to query excel
Dim sExcelFileName As String = "~\current.xls"
        Dim sWorkbook As String = "[DSRO$]"
        Dim sExcelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath(sExcelFileName) & ";Extended Properties=""Excel 8.0;HDR=YES;"""
        Dim OleDbConn As OleDbConnection = New OleDbConnection(sExcelConnectionString)
        Dim OleDbCmd As OleDbCommand = New OleDbCommand(("SELECT * FROM " & sWorkbook), OleDbConn)
        Dim dr As OleDbDataReader = OleDbCmd.ExecuteReader()
        dg.DataSource = dr

Open in new window


Author Closing Comment

ID: 31466847
Your post helped me to do the task much faster.Anyway i did this in c#, not VB.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
If you are looking for an automated solution for backup single or multiple Office 365 user mailboxes to Outlook data file, then you can use Kernel Office 365 Backup & Restore tool. Go through the video to check out the steps to backup single or mult…
When you have multiple client accounts to manage, it often feels like there aren’t enough hours in the day. With too many applications to juggle, you can’t focus on your clients, much less your growing to-do list. But that doesn’t have to be the cas…

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question