Search code examples
excelpowerpivot

Programmatically add rows to an Excel data model via C#

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.

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.

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 enter image description here

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

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.

Solution

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

    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))

    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.