VSTO Excel 2007 PivotTable, having a PivotField in more than one column

Question

I am using VSTO with Excel 2007 to generate PivotTables and PivotCharts dynamically. I am having a problem when I need to have a PivotField in more than one column.

To accomplish this I create a PivotTable in Excel and serialize its properties into an XML document, which I then use to rebuild the PivotTable.

Ie: as a Value and as a Column

This is possible when building the PivotTable in Excel. Has found a way to do this using C# ?

Creating a PivotTable Programmatically

Answer 1

If you add a calculated field to a Piviot Table and make the formula simply be the name of the field you need a duplicate of that allows you to use the same field twice, the Calculated Field does have to be a Value field.

Prehaps you can do this programaticly.

Answer 2

So I would need to duplicate my DataField / PivotField to be able to add it a second time in a different column / row / position ?

Answer 3

Once you have your Dataset you can convert it to an object[,] and insert it into an Excel document. Then you can save the document to disk and stream it to the user.

            for (int cIndex = 1; cIndex < 1 + columns; cIndex++)
                sheet.Cells.set_Item(4, cIndex, data.Columns[cIndex - 1].Caption);
            if (rows > 0)
            {

                //select the range where the data will be pasted
                Range r = sheet.get_Range(sheet.Cells[5, 1], sheet.Cells[5 + (rows - 1), columns]);

                //Convert the datatable to an object array
                object[,] workingValues = new object[rows, columns];

                for (int rIndex = 0; rIndex < rows; rIndex++)
                    for (int cIndex = 0; cIndex < columns; cIndex++)
                        workingValues[rIndex, cIndex] = data.Rows[rIndex][cIndex].ToString();

                r.Value2 = workingValues;
             }