如何在Excel中选择单元格或范围，并使用asp.net将选定的单元格数据导出到sql [英] How to select cells or range in excel and export the selected cell data to sql using asp.net

问题描述

我想使用范围或单元格从Excel导入数据，例如从行'9'到列'G'的数据...这些行和列中的所有数据都将从excel导出到sql数据表，这个我想通过asp.net实现。



就像使用X轴和Y轴一样。



i有代码导入整个文件



我尝试过：

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();
                }

推荐答案

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.

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.

这篇关于如何在Excel中选择单元格或范围，并使用asp.net将选定的单元格数据导出到sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！