Click here to Skip to main content
14,387,739 members
Rate this:
Please Sign up or sign in to vote.
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:

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
Updated 8-Feb-16 7:13am
v2
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
Rate this:
Please Sign up or sign in to vote.

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.
   
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.
Rate this:
Please Sign up or sign in to vote.

Solution 2

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.
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100