Click here to Skip to main content
15,916,463 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have searched for so many posts that explain static manner some of the posts are not clearly some of the posts are not supporting for latest excel files so many problems.Please help me as soon as possible. Thanks for advance.

What I have tried:

Excel Sheet Not Imported into database using C# WPF (MVVM). Please help with Example.
Posted
Updated 12-Apr-16 2:38am
Comments
Richard MacCutchan 9-Apr-16 10:50am    
Seriously? No one is going to be able to provide a complete example to such a question. Please read the FAQ and post a proper detailed question. Alternatively break your problem into its constituent parts and research each one in turn.

Quote:
How to import excel file into datagrid and after that save in SQL server database using WPF MVVM?

You are asking 3 different unrelated questions.
Even your question look like a wobbly solution to another problem.
And the way you ask it, it looks like you have searched a full blowup solution that you can simply copy/paste. It don't work like that.
You have to search a solution for each question and then you are the one that make the glue between the solutions.

Learn to use Google.
 
Share this answer
 
Comments
Kaku Pandit 29-Apr-16 0:17am    
I solved this problem i'm little bit confused using MVVM then i asked. :D
It is Possible, though you need is to follow several steps,they are as following

Step 1 : Get the filepath using OpenFileDialog
C#
var fd = new OpenFileDialog();
            fd.Multiselect = false;
            fd.Title = "Upload Excel File";
            fd.ValidateNames = true;
            fd.DefaultExt = "xls";
            fd.Filter = "Excel Files (*.xls)|*.xls";
            fd.FilterIndex = 1;

string fp = string.Empty;
fp = fd.FileName;


Step 2 : Make A query for eg:
C#
select [column1],[column2],[column3],[column4],[column5] from [Sheet1$]


here "Sheet1$" is sheet1 from your excel.

Step 3: Store This Data in a DataReader, through which you can display it in both grid as well as store it in database.
here i have used OleDbDataReader.

C#
string connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0';";
OleDbConnection con = new System.Data.OleDb.OleDbConnection(connection);
            OleDbCommand cmd = new OleDbCommand(query, con);
            con.Open();

            var dr = cmd.ExecuteReader();

            try
            {
                var bulkCopy = new SqlBulkCopy(_connectionString);
                bulkCopy.DestinationTableName = "ExcelData";
                bulkCopy.WriteToServer(dr);
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }


here
"_connectionString" is the string in which you store your Data path
"ExcelData" is the name of table to store the data.

Note: 1)Your table should contain exact same columns as in the excel also the data type of the column in excel & table should be same.. you can view the type of data of excel column from "dr" while debugging.

2)Also You should have Microsoft office Access database engine 2007 installed in your machine. if not you may get it from this link
Download 2007 Office System Driver: Data Connectivity Components from Official Microsoft Download Center[^]

3) check the version of excel too i have 97-2003 excel so i have used "Excel 8.0"

now for uploading the data Of this OleDbDataReader, just follow this link

How to Populate a DataGridView Control using OleDbDataReader[^]
 
Share this answer
 
v2
Comments
Kaku Pandit 29-Apr-16 0:18am    
Thanks.. (y)
i have same use in 2007 excel.
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'"

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900