Excel 中的数据透视表选项
我需要设计一个小项目，用于在.NET中生成excel报告，并将其发送给用户使用。
Excel 报告将包含数据透视表。
我对它们没有太多经验，但我可以想到三种实现替代方案：
- 为其设置查询、填充它、断开连接发送它。 这样，用户将能够对值进行分组并玩一点，但他将无法刷新数据。
- 生成一个小型 Access 数据库并将其与 Excel 文件一起发送，然后连接到它。
- 将数据复制到 Excel（可能在其他工作表中）并连接到那里的数据。 我认为这会使 Excel 文件变得非常大。
就性能与可用性而言，最好的选择是什么？ 还有其他我不知道的选择吗？
I need to design a small project for generating excel reports in .NET, which will be sent to users to use.
The excel reports will contain PivotTables.
I don't have much experience with them, but I can think of three implementation alternatives:
- Set a query for it, populate it, send it disconnected. This way the user will be able to group values and play a little, but he will not be able to refresh the data.
- Generate a small access database and send it along with the excel file, connect to it.
- Copy the data to the excel (perhaps in some other sheet) and connect to the data there. This will make the excel file very large I think.
What would be the best alternative in regards to performance vs usability? Is there another alternative I don't know about?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
由于这是一个小项目，您可以依靠 Excel 来存储应用程序中的数据。 它将更容易开发和测试，并且更易于维护。
Since it's a small project, you can rely on Excel for data storage from the application. It'll be easier to develop and test, and simpler to maintain.
@Espo
不，用户将无法访问原始数据源，这就是为什么我正在考虑使用报告所需的数据子集创建一个小型访问数据库
@Espo
No, the users will not have access to the original datasource, that's why I'm considering creating a small access database with the data subset required for the report
选项 3 听起来最简单，我认为 Excel 存储数据的效率似乎并不比 Access 更高。 两个文件的问题在于，即使在不同的位置，它们之间的链接也能正常工作。
Option 3 sounds the simplest and I would have thought Excel is as if not more efficient than Access for storing the data. The trouble with two files is getting the links between them to work even in a different location.
您必须保持数据“离线”吗？
在这些有大量数据的情况下，我通常会使用网络上现有的 SQL 服务器。 如果是在办公室使用，用户无论如何都会在线。
只需记住为此报告在 SQL Server 上创建一个具有受限访问权限的专用用户，并且不要将“sa”密码存储在 Excel 文件中。
如果您所说的“发送给用户”是指办公室外的用户，那么这不是一个好的解决方案。 如果是这样的话，我会尝试将数据包含在Excel表中，看看它会变成多大。 如果文件不太大，这将是最用户友好的解决方案。
另外，我在网上找到了这个： 使用 Web 查询的不同方式在 Microsoft Office Excel 2003 中。 这将允许您将数据存储在公共网站上（如果需要，可以使用秘密 URL），然后让 Excel 提取数据。 这样，您就不必用大文件填满用户的收件箱，并且您还可以稍后更新数据，而无需重新发送 Excel 文件。
Do you have to keep the data "offline"?
What I usually do in these cases, where you have quite a bit of data, is to use an existing SQL server already on the network. If it is to be used at the office, the users will be online anyway.
Just remember to create a dedicated user with restricted access on the SQL server for this report and don't store the "sa" password in the excel file.
If you mean users outside of the office by "sent to the users", this would not be a good solution. If that is the case, I would try to include the data in the excel sheet and see how big it will become. That will be the most user friendly solution if the file is not too big.
Also, I found this online: Different Ways of Using Web Queries in Microsoft Office Excel 2003. This will let you store the data on a public website (with a secret URL if you want) and then let Excel pull the data. That way you do not have to fill the users' inbox with large files, and you can also update the data later without resending the excel file.
@paulmorriss
也许...问题是单张纸可以包含多少数据是有限制的...我正在考虑选项2，除非有人告诉我这在性能方面不是一个好主意
@paulmorriss
Maybe... the problem with that is that there are limitations as to how much data a single sheet can contain... I'm thinking option 2, unless someone tells me it is not a good idea in regards to performance