使用ExcelDataReader v2.1从CLS中读取XLSX中的数据并写入SQL Server [英] Reading Data from XLSX in C# using ExcelDataReader v2.1 and writing to SQL Server

问题描述

我需要在C＃中编写一个控制台应用程序，从XLSX读取数据并使用ExcelDataReader v2.1库写入SQL服务器。



我需要读取数据在单元格D17，D18，D19和SQL中的购买列。

读取单元格K17，K18，K19并在SQL中删除/销售列

这是SQL表查询和数据库名称是test1

I need to write a console application in C# reading data from XLSX and writing to SQL server using ExcelDataReader v2.1 Library .

I need to read the data in Cell D17,D18,D19 and drop to buying column in SQL.
And read Cell K17,K18,K19 and drop to /selling column in SQL
This is the SQL table query and database name is test1

SELECT TOP 1000 [id]
      ,[code]
      ,[buying]
      ,[selling]
  FROM [test1].[dbo].[Currency]

这是我试过的代码，但它没有使用ExcelDataReader v2.1。我需要帮助才能使用ExcelDataReader v2.1

This is a code i tried but it is not using ExcelDataReader v2.1. i need help to do it with ExcelDataReader v2.1

using System;
using System.IO;
using Bytescout.Spreadsheet;
using System.Data.SqlClient;

namespace ExportToSQLServer
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
               
                string connectionString = "Data Source=192.168.1.215;Initial Catalog=RECIPES2;Persist Security Info=True;User ID=sa;Password=***********";

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();

                    
                    ExecuteQueryWithoutResult(connection, "USE test1");

                    //Load XLS document
                    using (Spreadsheet document = new Spreadsheet())
                    {
                        document.LoadFromFile("SimpleReport.xls");
                        Worksheet worksheet = document.Workbook.Worksheets[0];

                        for (int row = 0; row <= worksheet.UsedRangeRowMax; row++)
                        {
                            String insertCommand = string.Format("INSERT XlsTest VALUES('{0}','{1}')",
                           worksheet.Cell(row, 0).Value, worksheet.Cell(row, 1).Value);
                            ExecuteQueryWithoutResult(connection, insertCommand);
                        }
                    }

                    // Check the data successfully exported
                    using (SqlCommand command = new SqlCommand("SELECT * from XlsTest", connection))
                    {
                        SqlDataReader reader = command.ExecuteReader();

                        if (reader != null)
                        {
                            Console.WriteLine();
                            Console.WriteLine("Exported XLS data:");
                            Console.WriteLine();

                            while (reader.Read())
                            {
                                Console.WriteLine(String.Format("{0}  |  {1}", reader[0], reader[1]));
                            }
                        }
                    }

                    Console.WriteLine();
                    Console.WriteLine("Press any key.");
                    Console.ReadKey();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
                Console.ReadKey();
            }
        }

        static void ExecuteQueryWithoutResult(SqlConnection connection, string query)
        {
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                command.ExecuteNonQuery();
            }
        }
    }
}

请帮助

推荐答案

使用列名从Excel中读取数据并将其导入asp.net中的数据表并将记录插入SQL表中。

使用单独的数据表进行买卖以避免冲突，如果您是专家也使用相同的数据表。



请参阅此处以加载Excel中的Excel数据表。



从Excel加载GridView [ ^ ]



这里我做的是，从excel加载gridview你只需发送数据而不在gridview中加载它。

而不是usinf SQL bulkcopy获取数据表中的每条记录并插入记录

Read the data from the Excel using column name and import it to a datatable in asp.net and insert the records to the SQL table.
Use separate datatable for buying and selling to avoid collisions, if you are expert use the same datatable too.

Refer this to load the Excel in the datatable.

Load GridView from Excel[^]

Here what i did is, loaded the gridview from excel u just send the data without loading it in gridview.
Instead of usinf SQL bulkcopy get each records in datatable and insert the records

这篇关于使用ExcelDataReader v2.1从CLS中读取XLSX中的数据并写入SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！