通过C＃以编程方式将行添加到Excel数据模型 [英] Programmatically add rows to an Excel data model via C#

问题描述

我们正在考虑允许客户从Web应用程序下载Excel文件，该文件包含其数据的原始导出以及基于该数据的一些基本图表和数据透视表。

We're looking at allowing our customers to download an Excel file from our web application which contains a raw export of their data along with some basic charts and pivot tables based on that data.

我们要完成这项工作的基本方法是，我们有一个固定的Excel文件，该文件包含一个工作表中的所有报告元素，并为另一工作表中的基础数据留出空间。当用户请求其Excel报告时，我们以编程方式将其结果填充到数据工作表中，并将最终的Excel文件发送给他们。

The basic way, we want to make this work is that we have a fixed Excel file which contains all the reporting elements in one worksheet and have room for the underlying data in another worksheet. When the user requests their Excel report, we programmatically fill out the data worksheet with their results and send them the final Excel file.

使用一组固定的数据进行原型制作。我们使用的数据集已添加到Excel数据模型中，然后我们设置了图表和其他报告元素。但是，当在我们的应用程序中将该文件用作生成的Excel文件的模板时，我们发现数据模型的定义仍然保留-这意味着我们使用表定义构建了 protype $ A $ 1：$ T $ 5879

Everything seemed a bit to easy when doing the prototyping with a fixed set of data. The dataset we worked with was added to the Excel Data Model and we then set up the charts and other reporting elements. However, when using that file as the template for the generated Excel file in our application we are finding that the definition of the data model still remains - meaning, that we built the "protype" with a table definition of $A$1:$T$5879

但是在生成报告时，该定义不会更改为包含添加的数据集可能具有的任何大小。

but when generating the report, that definition isn't changed to contain whatever size the added dataset might have.

我们正在使用EPPlus来生成Excel工作表，到目前为止找不到解决此类问题的任何解决方案。这很可能是由于我们是Excel新手。目的是为了获得用户体验，Excel工作表中包含的图表和数据透视表可以反映Excel文件中包含的全部数据集，而无需执行任何操作。

We're using EPPlus to work with the generation of our Excel sheets and have so far been unable to find any sort of solution to this kind of problem. This might very much be due to us being quite Excel novices. The goal is to have the user experience, that the charts and pivot tables contained in the Excel sheet reflects the total dataset contained in the Excel file without them having to do anything.

推荐答案

好，我实际上已经找到了解决方案。
解决方案就在我们眼前。

Ok, I've actually found a solution for it. The solution was right infront of us.

我们将数据集定义为命名集-这是在公式下和名称中完成的经理。我们有一个范围来定义我们的数据集-定义范围时的引用字段可以采用公式。因此，要给它固定大小，我们可以使用以下代码： = OFFSET（Data！$ A $ 1; 0; 0; COUNTA（Data！$ A：$ A）; COUNTA（Data！$ 1：$ 1） ））

We define the dataset as a named set - this is done under under the "Formulas" and inside the "Name Manager". We have a range which defines our dataset - the "Refers To" field when defining a range can take a formula. So intead of giving it a fixed size, we use this: =OFFSET(Data!$A$1;0;0;COUNTA(Data!$A:$A);COUNTA(Data!$1:$1))

这将参考 A1 计算行和列的数量在我们的数据工作表中。我们所有的枢轴都将在启动时重新加载，并且似乎可以正常工作。

This counts the amount of rows and columns, with reference to A1 in our Data worksheet. All our pivots are set to reload on startup and that seems to work.

这篇关于通过C＃以编程方式将行添加到Excel数据模型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！