How to select cells or range in excel and export the selected cell data to sql using asp.net

Question

0.00/5 (No votes)

See more:

I want to import data from Excel using range or cell, like data from row '9' to column 'G'...all the data within these rows and columns will be Exported from excel to sql data table, and this i want to achieve through asp.net.

it is like using X axis and Y axis .

i have code to import the whole file

What I have tried:

C#

string csvPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
        FileUpload1.SaveAs(csvPath);

        DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[3] { new DataColumn("KPI", typeof(string)),
            new DataColumn("KPIPN", typeof(string)),
        new DataColumn("KPIPV", typeof(string))});
        string csvData = File.ReadAllText(csvPath);
        foreach (string row in csvData.Split('\n'))
        {
            if (!string.IsNullOrEmpty(row))
            {
                dt.Rows.Add();
                int i = 0;
                foreach (string cell in row.Split(','))
                {
                    dt.Rows[dt.Rows.Count - 1][i] = cell;
                    i++;
                }
 using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(con))
                {
                    sqlbulkcopy.DestinationTableName = "dbo.csv";

                    sqlbulkcopy.WriteToServer(dt);
                    con.Close();
                }

Posted 8-Feb-16 0:31am

Member 12313733

Updated 8-Feb-16 6:13am

Patrice T

v2

Add a Solution

Comments

Sinisa Hajnal 8-Feb-16 8:28am

And what happens when you run the code you have? What is the problem? You loop through the rows until you get to row 9 then split and use only columns from G onward...Sorry, not enough details to help you. Your code need minor modification for that. Just think about it.

Member 12313733 8-Feb-16 22:42pm

i want all the selection of ranges to happen dynamically through asp.net

2 solutions

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)

Sangani Viral · Answer 1 · 2016-02-08T04:59:00

Solution 1

I would recommend you to use the OfficeOpenXML library provided by Microsoft and it will make your life easier. 
Steps:
1) Open the workbook.
2) Select the worksheet.
3) Give the range of cells. (sheet.Cells(fromrow,fromcol,torow,tocol))
4) GetCellValue method for the selected range. Now you have all the values for the given range.
5) Use your logic to export it to SQL.

Posted 8-Feb-16 4:59am

Sangani Viral

Comments

Member 12313733 8-Feb-16 22:43pm

sangani viral
have to use asp.net to form an interface between sql and excel.

Sangani Viral 8-Feb-16 23:51pm

what is your asp.net page doing? Does it have a button that does the interfacing? Or how are you planning to use it. Be clear in your requirements.

Member 12313733 9-Feb-16 1:42am

yes i am using standard control "fileupload" to browse and a button to import from csv to sql, i am looking for a mechanism whatever it is ,it code be providing range textbox like, textbox1 will take input from row side like in excel 6 and a textbox2 will take column side like as in excel column G ....and then through button click that row could be imported from that csv or excel file to database that has been assigned for that purpose.

Sangani Viral · Answer 2 · 2016-02-09T05:52:00

Still you could use the same steps.
For code you can look up yourself on google.
1) Read the file from the fileUpload control.
2) You have two text boxes where TextBox1 is the row and TextBox2 is the column. Read these values in variables in the code behind. 
3) Access worksheet using OpenXML code.
4) Follow the steps mentioned in the comment earlier.
5) refer to EEPlus documentation on codeplex.
6) Google for code please.