Update pivot tables datasources

Aspose.Cells Product Family
1

Hi,I would like to update the datasources of all my pivot tables with sheet.Cells.MaxRow before saving. But since tcd.Datasource returns a string array I don't know what to do and would like to avoid to parse the datasource string.

I wrote the following code :

foreach (Excel.Worksheet sheet in _doc.Worksheets)
{
foreach (Excel.Pivot.PivotTable tcd in sheet.PivotTables)
{
string[] datasources = tcd.DataSource;
// TODO: need to update the string array and throw the new value to ChangeDataSource method
tcd.ChangeDataSource();
}
}

2

Hi,


See the simplest code on how to change the pivot table data source, you may refer to it and create your own code segments.

Sample code:

var wb = new Workbook(@“E:\test2\Book1.xlsx”);
var pivotTable = wb.Worksheets[0].PivotTables[“PivotTable1”];

///e.g =Sheet2!$A$1:$B$11
Console.WriteLine(“current datasource: {0}”, pivotTable.DataSource[0]);

Console.WriteLine(“attempting to change datasource from Sheet2 to Sheet3…”);
pivotTable.ChangeDataSource(new[] { “=Sheet3!$A$1:$B$11” });

//should now be Sheet3
Console.WriteLine(“current datasource: {0}”, pivotTable.DataSource[0]);

//refresh it
pivotTable.RefreshData();

//save file
const string newFileName = @“E:\test2\PivotTable-SourceChanged.xlsx”;
wb.Save(newFileName);


Also, Please use the new fix: Aspose.Cells for .NET v7.3.0.6

3

We are using an excel file as a template and fill some sheets with rows, pivot tables datasources are set in the excel template file (by selecting all the sheet), in runtime I don't know where the datasources get the rows.

In runtime, I need to remove the (empty) option from all pivot filters and then to do that I want to update the datasource to take only filled rows (not the full sheet).

Do you have a method to parse datasource string (like Donnees!$A:$O) to get the sheet name, the start column name, the end column name, the start row name, the end row name) ?

I could parse it and rebuild a datasource string myself but I don't know if datasource string are always formatted using the same format ("=[Sheet name]![$Col name start$Row index start]:[$Col name end$Row index end]). So my goal is to update $Row index end with sheet.Cells.MaxDataRow.

4

Hi,


I am not totally sure about needs. But, kindly use your own code to parse datasource to get sheetname, first row, first column, last row and last column etc.

See the simple code for reference. Kindly use your own code (after referring to my code) to perform your desired task.

Sample code:

var wb = new Workbook(@“E:\test2\PivotTable.xlsx”);
var pivotTable = wb.Worksheets[0].PivotTables[“PivotTable1”];

string source = pivotTable.DataSource[0];

string sheetname = source.Replace("=", “”).Split(’!’)[0];
//Get the sheet name
MessageBox.Show(sheetname);

//Now get the first row, first column, last row, last column.
string rangearea = source.Replace("=", “”).Split(’!’)[1];
string[] strCellRange = rangearea.Replace("$", “”).Split(’:’);
int frow, fcol, lrow, lcol;
CellsHelper.CellNameToIndex(strCellRange[0], out frow, out fcol);
CellsHelper.CellNameToIndex(strCellRange[1], out lrow, out lcol);

string r1 = CellsHelper.CellIndexToName(frow, fcol);
string r2 = CellsHelper.CellIndexToName(lrow, lcol);

MessageBox.Show(r1 + “:” + r2);
5

Thanks, I've finally developed my own datasources string parser to handle the following cases :

1. Donnees!$A:$O : All datas FROM col A TO Col O
2. Donnees!$A$4 : Identify one cell ($A$4)
3. Donnees!$2:$4 : All datas FROM row 2 TO row 4
4. Donnees!$B:$B : Identify one column : B
5. Donnees!$5:$5 : Identify one row : 5
6. Donnees!$A$5:$C$22: Range FROM col A, line 5 TO col C, line 22

I hope I handled all datasources formats types.

I have another question :

Can I uncheck all (empty) values for all pivot filters of all pivot tables by code ?

Thanks

6
Hi,

DomZ:

I have another question :

Can I uncheck all (empty) values for all pivot filters of all pivot tables by code ?


Could you give us details/steps involved on how to do it in MS Excel for a pivot table. Please attach a sample file and more details. Also, attach some screen shots to perform the functionality in MS Excel. We will check it soon.


Thank you.