how to import data of excel document to database table using window application?

Question

0.00/5 (No votes)

See more:

WinForms

Any one please help me how i import data from excel document to a table present in data base using winform.????

Posted 24-Apr-13 23:27pm

ashu_dhiman

Add a Solution

2 solutions

Solution 2

Hi ashu_dhiman

1.If you want to import all columns to sql database use sqlbulkcopy and map your source and destination columns.
2.Secondly if you want selected values to import go for Excel Interop classes.

with Excel Interop classes you can do something like this

public void parsefunction(string filepath)
    {
        Microsoft.Office.Interop.Excel.Application Exlapp;
        Microsoft.Office.Interop.Excel.Workbook Exlwkbk;
        Microsoft.Office.Interop.Excel.Worksheet Exlwksht;
        Microsoft.Office.Interop.Excel.Range ExlRng;
        Exlapp = new Microsoft.Office.Interop.Excel.ApplicationClass();
        //opening Excel file
        Exlwkbk = Exlapp.Workbooks.Open(@filepath,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);
        Exlwksht = (Microsoft.Office.Interop.Excel.Worksheet)Exlwkbk.Sheets.get_Item(1);
        ExlRng = Exlwksht.UsedRange;
        ExlRng = Exlwksht.get_Range("A2","L2");//this is starting and ending index in Excel column..
         
        string a3;//declare some variables
        string b3;
        string c3;
        string d3;
        string e3;
        string f3;
        string g3;
        string h3;
        string i3;
        string j3;
        string k3;
       a3 = Exlwksht.get_Range("A3",Type.Missing).Value.ToString();//assign Index values to those variables you may also go for both the things(declaring and assigning) together.
       b3 = Exlwksht.get_Range("B3",Type.Missing).Value.ToString();
       c3= Exlwksht.get_Range("C3",Type.Missing).Value.ToString();
       d3 = Exlwksht.get_Range("D3",Type.Missing).Value.ToString();
       e3 = Exlwksht.get_Range("E3",Type.Missing).Value.ToString();
       f3 = Exlwksht.get_Range("F3",Type.Missing).Value.ToString();
       g3 = Exlwksht.get_Range("G3",Type.Missing).Value.ToString();
       h3 = Exlwksht.get_Range("H3",Type.Missing).Value.ToString();
       i3 = Exlwksht.get_Range("I3",Type.Missing).Value.ToString();
       j3 = Exlwksht.get_Range("J3",Type.Missing).Value.ToString();
       k3 = Exlwksht.get_Range("K3",Type.Missing).Value.ToString();
       SqlHelper.ExecuteNonQuery(ConfigurationManager.AppSettings["connstring"], "insertetartdata",a3,b3, c3, d3, e3,f3,g3,h3,i3,j3,k3 //now insert data into sql server using a sprocedure..
      //look I am using a different function(which i have not shown here) here for reading the path of Excel file's location,you may go for a hardcoded location i.e specify the path here itself on the page..

Hope this may help you ..

Posted 25-Apr-13 2:46am

Rawat.Manoj

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

nitin bhoyate · Accepted Answer · 2013-04-24T23:55:00

Steps
1) Create Datagridview having same comumns as Database.
2) Read Excel File and Add Rows and Columns data to Datagridview
3) Give Save button and Onclick of save button insert data from the gridview into DB by reading each row.
(to inser you can insert one row at one time using for loop OR you can use Union all to insert all rows at a time)

Here are few references for above steps:

Read Excel file Data : http://stackoverflow.com/questions/15828/reading-excel-files-from-c-sharp[^]