CODEWITHSUNDEEP
c#.net-3.5vstoexcel-2007
Alexandre Brisebois
Alexandre Brisebois

Reputation: 6753

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

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

Upvotes: 0

Views: 1677

Answers (2)

Alexandre Brisebois
Alexandre Brisebois

Reputation: 6753

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;
}

Upvotes: 0

StocksR
StocksR

Reputation: 993

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.

Upvotes: 1

Related Questions