how to refresh pivot table

[mengting0917]

how can i refresh pivot table on the first sheet,？

does Spire.xls supports refresh data source of pivot table now?

[burning.liu]

Dear mengting0917,

Thanks for your inquriy.

Spire.Xls supports autoupdate pivot table when data source has modified. Just change the data source range's value and save the workbook, the pivot table will be autoupdated.

Best Regards,
Burning
E-iceblue Support Team

[mengting0917]

Dear mengting0917,

Thanks for your inquriy.

Spire.Xls supports autoupdate pivot table when data source has modified. Just change the data source range's value and save the workbook, the pivot table will be autoupdated.

Best Regards,
Burning
E-iceblue Support Team

now i have a template workbook, sheet1 has a pivot table and its data source is sheet2 , according to your answer, i just change value of sheet2 and save the workbook, pivot table in sheet1 can autoupdated ?

[burning.liu]

Dear mengting0917,

Yes. It can be autoupdated when you save the workbook, you can try it.

Best Regards,
Burning
E-iceblue Support Team

[mengting0917]

Dear mengting0917,

Yes. It can be autoupdated when you save the workbook, you can try it.

Best Regards,
Burning
E-iceblue Support Team

thank you very much!!!

[burning.liu]

Dear mengting0917,

Please feel free to contact us if you have any problems.

Best Regards,
Burning
E-iceblue Support Team

[mengting0917]

Dear mengting0917,

Please feel free to contact us if you have any problems.

Best Regards,
Burning
E-iceblue Support Team

I have tested right now, pivot table can't refresh automatically.

here is my code:

private string AsposeConvertExcelToPDF(string excelPath)
{
string pdfPath = excelPath.Substring(0, excelPath.LastIndexOf('.')) + ".pdf";
pdfPath = CheckDirectoryandFileExists(pdfPath);



//Instantiate the Workbook object
//Open an Excel file
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(Server.MapPath(excelPath));

Aspose.Cells.Worksheet sheet1 = workbook.Worksheets[1];
sheet1.Cells["A2"].Value = "111111111111111111111";
//Save the document in PDF format
workbook.Save(Server.MapPath(excelPath));

workbook.Save(Server.MapPath(pdfPath), Aspose.Cells.SaveFormat.Pdf);
return pdfPath;
}

[burning.liu]

Dear mengting0917,

It seems that you're using Aspose which is unfamiliar to me. Please using Spire.Xls to test.

Best Regards,
Burning
E-iceblue Support Team

[mengting0917]

Dear mengting0917,

It seems that you're using Aspose which is unfamiliar to me. Please using Spire.Xls to test.

Best Regards,
Burning
E-iceblue Support Team

can not refresh still.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Spire.Xls;

namespace ToPDF
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("Oracle_UserAccoutReport_11_4_2014_1.xlsx");
workbook.Worksheets[1].Range["A2"].Value = "222222222222";
//workbook.SaveToFile("Oracle_UserAccoutReport_11_4_2014_1.xlsx");
workbook.Save();
workbook.SaveToFile("result_1.pdf", Spire.Xls.FileFormat.PDF);
//Workbook workbook1 = new Workbook();
//workbook1.LoadFromFile("Oracle_UserAccoutReport_11_4_2014_1_a.xlsx");
//workbook1.SaveToFile("result_1.pdf", Spire.Xls.FileFormat.PDF);

System.Diagnostics.Process.Start("result_1.pdf");
}
}
}

[burning.liu]

Dear mengting0917,

I used Spire.Xls v7.6.1 to test, please try that version.
If it cannot be refresh still, please upload the xlsx file to help us reproduce the issue.

Best Regards,
Burning
E-iceblue Support Team

[mengting0917]

Dear mengting0917,

I used Spire.Xls v7.6.1 to test, please try that version.
If it cannot be refresh still, please upload the xlsx file to help us reproduce the issue.

Best Regards,
Burning
E-iceblue Support Team

could you give me download link of Spire.Xls v7.6.1

the file i download doesn't contains spire.common.dll

[mengting0917]

Dear mengting0917,

I used Spire.Xls v7.6.1 to test, please try that version.
If it cannot be refresh still, please upload the xlsx file to help us reproduce the issue.

Best Regards,
Burning
E-iceblue Support Team

i downloaded spire.xls 7.6 form http://www.e-iceblue.com/Download/downl ... t-now.html

and tested, it doesn't works.

the attachment is my xlsx file.

[mengting0917]

Dear mengting0917,

I used Spire.Xls v7.6.1 to test, please try that version.
If it cannot be refresh still, please upload the xlsx file to help us reproduce the issue.

Best Regards,
Burning
E-iceblue Support Team

maybe you have set refresh pivot table on open. so the pivot table can refresh.

but i need to convert the xlsx file to pdf , so i need to refresh pivot table before opening.

[burning.liu]

Dear mengting0917,

In the code snippet below, I copy data from "A6:I6" to "A7:I7" and then save workbook to pdf. The pivot table changes in result pdf document, you can try it.

Code: Select all

Workbook workbook = new Workbook();
workbook.LoadFromFile("testfile.xlsx", ExcelVersion.Version2010);
Worksheet worksheet = workbook.Worksheets[1];
//Change Row 7
worksheet.Copy(worksheet.Range["A6:I6"], worksheet.Range["A7:I7"], true);
workbook.SaveToFile("testfile.pdf", FileFormat.PDF);


Best Regards,
Burning
E-iceblue Support Team

[mengting0917]

Dear mengting0917,

In the code snippet below, I copy data from "A6:I6" to "A7:I7" and then save workbook to pdf. The pivot table changes in result pdf document, you can try it.

Code: Select all

Workbook workbook = new Workbook();
workbook.LoadFromFile("testfile.xlsx", ExcelVersion.Version2010);
Worksheet worksheet = workbook.Worksheets[1];
//Change Row 7
worksheet.Copy(worksheet.Range["A6:I6"], worksheet.Range["A7:I7"], true);
workbook.SaveToFile("testfile.pdf", FileFormat.PDF);


Best Regards,
Burning
E-iceblue Support Team

I know why my xlsx couldn't refresh, my Spire.xls version was V7.6 , not V7.6.1.

i have tried your code using V7.6.1, the pivot table in result pdf document can shows correctly. thank you !

but there is a strange thing , when i save testfile.xlsx as another testfile1.xlsx and open it , the pivot table still shows old data.

Code: Select all

Workbook workbook = new Workbook();
            workbook.LoadFromFile("testfile.xlsx", ExcelVersion.Version2010);
            Worksheet worksheet = workbook.Worksheets[1];
            //Change Row 7
            worksheet.Range["A2"].Value = "22222222222";
            worksheet.Copy(worksheet.Range["A6:I6"], worksheet.Range["A7:I7"], true);
            workbook.SaveToFile("testfile1.xlsx", FileFormat.Version2010);
            workbook.SaveToFile("testfile.pdf", FileFormat.PDF);

by the way , the lisence of Spire.xls is so expensive that i can't afford it.