有关在Excel工作表中传输传感器数据的最佳方法所需的建议 [英] Suggestions required for best way to stream sensors data in excel sheet

问题描述

大家好，

我需要建议我们应该遵循的最佳方法，将大量实时数据从传感器流式传输到Excel工作表。

我们有传感器数据插入套接字，我们需要使用excel进行分析。我们已经在VC ++中创建了一个dll，它读取这些传感器数据并将原始数据转储到文本文件中。但现在我们需要在数据到达时分析它们。我们在一秒钟内获得大约50000次更新，对于excel分析，仅使用大约1000个重要传感器。

为此我们正在考虑像

I这样的方法。使用dll创建服务并使用套接字多播从其重新广播数据。在excel中，实现套接字读取器代码。

II。将数据放入数据库并从数据库读取excel。

III。发布webservice并让excel从Web服务下载数据。

IV。使用Excel RTD（RealTimeData）服务获取数据。



看到数据的大小并考虑未来的增长，实现这一目标的最佳方法是什么？欢迎在此类批量计算期间冻结Excel UI（对数据库，Web服务的1000请求），在Excel UDP套接字中丢弃数据包等方面的经验。



谢谢ppolymorphe。以下是对您的查询的回复：



1. excel表的用途：

绘制数据。计算不同传感器上的不同计算，以研究特定组合是否确实表现出行为。

2.数据是否连续：

是的，数据是连续的。存储数据以供将来使用已经通过将原始数据存储在文件中来完成。我们不想触摸该组件，因为它已经稳定并且在延迟2小时后进行后处理是有益的。我们现在想要开始实时分析。

3.只需重播现有数据文件即可重建数据序列流。这个范围不是我们的目标。

4.用户无意阅读数据。他会说添加5个传感器数据并根据时间绘制它的值并观察它。问题更像是股票价格进入的股票市场数据，用户绘制不同股票的数据。

5.是的，我们目前使用的方法是：

a。我们从服务器重新广播数据。在excel中编写了一个C ++套接字程序，用于在另一个线程中读取该数据并将其保存在内存中。然后，我们为excel写了一个VBA来集成这个dll。我们用C ++应用程序和VB.net应用程序测试了这个dll，它运行得很好。但是当我们将它与excel集成时，它会在读取excel读取数据后随机崩溃。如果我们保持与excel相同的线程，它不会崩溃，但随后我们丢弃了很多数据包。

b。我们为excel创建了webservice，每隔几秒就从中心位置读取这些数据。但是每秒更新1000个单元格太慢了。相同的外观将是数据库的问题。聚合通话和结果是一种选择，但仍然需要每秒约100次通话。



我们愿意接受基于网络分析等不同框架的建议，前提是它是开源的（如果excel不是这里的最佳选择）。















谢谢，

Hi Guys,
I need suggestions on best approach we should follow to stream bulk live data from sensors into excel sheet.
We have sensors data coming in sockets and we need to analyze it using excel. We already have a dll created in VC++ which reads these sensors data and dumps raw data in text file. But now we need to analyze them while data is arriving itself. We get about 50000 updates in a second and for excel analysis only about 1000 important sensors would be used.
For this we are thinking of approaches like
I. Creating a service with the dll and re-broadcasting the data from it using socket multicast. In excel, implement socket reader code.
II. Putting data in database and reading in excel from database.
III. Publish webservice and let excel download data from the webservice.
IV. Use Excel RTD (RealTimeData) service to fetch data.

Seeing the size of the data and considering future growth, what is the best way to implement this? Experiences regarding freezing of Excel UI during such bulk computations (1000 request to database, webservice), dropping of packets in Excel UDP sockets, etc are welcomed.

Thank you ppolymorphe. Below are the replies to your queries:

1.Purpose of excel sheet:
Plotting of data. Computing different calculations on different sensors for studying if a particular combination does show a behavior.
2. Is Data continuous:
Yes, data is continuous. Storing of data for future use is already done by storing the raw data in file. We don't want to touch that component as it's already stable and good for doing post processing after 2 hours delay. We now want to start real time analysis.
3. Rebuilding the sequence flow of data can be achieved just by replaying the existing data file. It's not our objective in this scope.
4. User doesn't intent to read the data. He will say add 5 sensors data and plot it's value against time and just observe it. Problem is more like a Stock Market data where stock prices are coming in, and user plots data of different stocks.
5. Yes, approaches we currently played around with are:
a. We rebroadcasted the data from a server. In excel wrote a C++ socket program to read this data in another thread and keep it in memory. Then, we wrote a VBA for excel to integrate this dll. We have tested this dll with C++ application and VB.net application and it works perfectly. But when we integrate it with excel, it crashes randomly after reading excel reads the data. If we keep the same thread as excel, it doesn't crash, but then we drop a lot of packets.
b. We created webservice for excel to read these data from a central location after every few seconds. But updating 1000 cells in each second is too slow. Same looks would be an issue with database. Aggregating calls and results is an option, but would still require about 100 calls a second.

We are open to suggestion on different frameworks like web based analytics provided it's open source (if excel is not the best choice here).







Thanks,

推荐答案

没有解决方案问题是因为你没有给我们提供详细信息。



要回答你的问题，我们需要全部细节，因为要处理的数据量很大。 />


- 是连续流吗？ （如天气数据）或短暂的流量？ （比如在一批事情的过程中）

- 您是否需要访问数据历史记录？你需要能够重建数据流以便以后分析吗？

- Excel应该对数据做什么？ （即使每秒1000个值太多，操作员也无法阅读）



- 您是否已经尝试使用样本数据源提供Excel？怎么回事？

There is no solution to your question because of the details you didn't gave us.

To answer your question, we need "all" details because of the amount of data to handle.

- Is it a continuous flow ? (like weather data) or short lived flow ? (like during the process of a batch of things)
- Do you need to access history of data ? do you need to be able to rebuild the flow of data for later analyse ?
- What is Excel supposed to do with the data ? (Even 1000 value per second is too much for an operator to read)

- Have you already tried to feed Excel with a sample burst of data ? How have it went ?

这篇关于有关在Excel工作表中传输传感器数据的最佳方法所需的建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！