使用c＃将Ms-excel作为数据库 [英] Ms-excel as database using c#

问题描述

大家好，



对于这个编码工作来说相对来说是非常新的，目前正在开发ASP.NET项目。我的问题是：



我有两个Excel文件

1。 Data_file_area.xls

2. Data_file_capacity.xls



和第三个excel文件

< b> 3。 Data_operations_functions.xls

包含从这两个excel文件派生的函数和操作。



现在我已经在asp.net中开发了一个前端表单，我可以定期输入新数据。它在这两个Excel文件中保存/更新，现在我需要访问第三个excel文件及其在Gridview中的所有功能/操作。这些功能必须根据两个excel文件自动更新，并在Gridview（实时）中显示，因为数据会定期更新。



有可能吗？



您的回答非常有用。



更新：



例如：

Table1包含以下列和数据。

ID |名称|目标

11 | Abcd | 123

12 | xyzx | 135



表2包含以下列和数据。

ID |候选人| Roll_Number

11 | asdeff | 12345

11 | dfrsf | 12346

11 | dfesf | 12347

12 | erfgv | 12348

12 | dsfdv | 12349

12 | dasfr | 12350

12 | dsadsa | 12351



注意：我已经采用了示例名称​​。



表3应存储来自table1&的数据应用函数后的table2。

名称|候选人数|目标

Abcd | 3 | 123

xyzx | 4 | 135



注意：在第三个表中，候选人数栏应分别从表2中计算。



我需要一个SQL语句来生成表3中的上述数据。



谢谢

Saiekrishna



我尝试过：



无法将Excel文件链接到前端。

Hello All,

Am relatively very new to this coding stuff and currently working on the ASP.NET project. My question is:

I have a two Excel files
1. Data_file_area.xls
2. Data_file_capacity.xls

and a third excel file
3. Data_operations_functions.xls
which contains functions and operation which is derived from those two excel files.

Now i have developed a front end form in asp.net where i can enter new data regularly & it is saved/updated in those two Excel files, now i need to access the third excel file with all its functions/operations in a Gridview. And the functions has to be updated automatically based on the two excel files and display it in Gridview(Realtime) as the data is updated regularly.

Is it possible?

Your answers will be very helpful.

Update:

For example:
Table1 contains following column and data.
ID | Name | Target
11 | Abcd | 123
12 | xyzx | 135

Table2 contains following columns and data.
ID | Candidate | Roll_Number
11 | asdeff | 12345
11 | dfrsf | 12346
11 | dfesf | 12347
12 | erfgv | 12348
12 | dsfdv | 12349
12 | dasfr | 12350
12 | dsadsa | 12351

Note: I have taken example names.

Table3 should store data from table1 & table2 after applying functions.
Name | Number of Candidate | Target
Abcd | 3 | 123
xyzx | 4 | 135

Note: In the third table the "Number of candidate" column should be COUNTED from table2, respectively.

I need a SQL statements which generates above data in table3.

Thanks
Saiekrishna

What I have tried:

Am unable to link Excel file to the front end.

推荐答案

查看Sinisa Hajnal的评论。



他们提供了一些数据库优于excel的原因。还有几个，尤其是您必须在服务器上安装Excel驱动程序才能访问数据。这可能是一个昂贵的开销，只是不需要。



其次，任何功能和操作不应该以这种方式在你的数据库 - 你应该放这些在后面的代码中，或作为实际数据库中的存储过程/用户定义函数。



使用Access将是使用Excel向前迈出的一步，但即便如此来自它自己的问题。



考虑使用众多可用的免费数据库系统之一，例如免费数据库|免费SQL Server数据库|数据库管理解决方案 [ ^ ]

See the comment from Sinisa Hajnal.

They have provided a few reasons why a database is better than excel. There are several more, not least the fact that you would have to have Excel drivers installed on your server in order to access the data. That can be an expensive overhead that is just not required.

Secondly, any functions and operations should not be in your "database" in this way - you should be putting these in the code behind, or as Stored Procedures / User Defined Functions on your actual Database.

Using Access would be a step forward from using Excel, but even that will come with it's own issues.

Consider using one of the many free database systems that are available e.g. Free Database | Free SQL Server Database | Database Management Solution[^]

除了 CHill60 <的解决方案1之外/ a> [ ^ ]。 ..



由于Caroline和Sinisa提到使用Excel文件作为数据库可能会提出几个问题，因为这些文件不是真正的数据库。因此，如果您计划存储多个记录，则不应使用Excel文件。对于大数据存储，每个数据库都比Excel文件更好！但是......



如果您想将Excel文件作为数据库使用，则必须使用OleDb [ ^ ]，以便能够提供 CRUD [ ^ ]操作。

请参阅：从.NET应用程序访问Microsoft Office数据 [< a href =https://msdn.microsoft.com/en-us/library/ms971514.aspxtarget =_ blanktitle =New Window> ^ ]



过去我写过小提示，这可能对你有所帮助：如何：使用一个OleDbConnection从多个工作簿中获取数据？ [ ^ ]

In addition to solution 1 by CHill60[^]...

As Caroline and Sinisa mentioned using Excel files as databases might provide several problems, because those files aren't real databases. So, you shouldn't use Excel files if you plan to store more than few records. For large data storage, every database is better then Excel file! But...

If you would like to play with Excel files as databases, you have to use OleDb[^] to be able to provide CRUD[^] operations.
See: Accessing Microsoft Office Data from .NET Applications[^]

In the past i wrote small tip, which might be helpful for you: How to: Get Data from Multiple Workbooks using One OleDbConnection?[^]

这篇关于使用c＃将Ms-excel作为数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！