Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Fri May 31, 2013 11:09 am

By default a pivot table is created and its working only in excel 2010 version.
is there any possibility to get it worked in excel 2007.
could you please let me know.

chaitanya
 
Posts: 12
Joined: Thu May 16, 2013 10:42 am

Mon Jun 03, 2013 3:34 am

Dear chaitanya,

Thanks for your inquiry.
Pivot table can work in excel 2007 version. Please test the code workbook.SaveToFile(name, ExcelVersion.Version2007);.
If the pivot table in the output file doesn't work, please tell us, and provide us your whole code and the output file.

Thanks and Regards,
Amy
E-iceblue support team
User avatar

amy.zhao
 
Posts: 2766
Joined: Wed Jun 27, 2012 8:50 am

Mon Jun 03, 2013 6:55 am

hi Amy,

thanks for your reply.

its not working even if I add workbook.SaveToFile(name, ExcelVersion.Version2007);. .

please find my code below:

Workbook workbook = new Workbook();

workbook.LoadFromFile("D:\\ppt\\WebSite1\\Excel files\\sample1.xlsx");

Worksheet sheet = workbook.Worksheets[0];

sheet.Name = "Data";

Worksheet sheet2 = workbook.CreateEmptySheet();

sheet2.Name = "Pivot Table";

CellRange dataRange = sheet.Range["A1:E9"];
PivotCache cache = workbook.PivotCaches.Add(dataRange);
PivotTable pt = sheet2.PivotTables.Add("Pivot Table", sheet.Range["A1"], cache);

var r1 = pt.PivotFields["YEAR_C"];
r1.Axis = AxisTypes.Column;
pt.Options.RowHeaderCaption = "YEAR_C";

var r2 = pt.PivotFields["WAVE_C"];
r2.Axis = AxisTypes.Column;
pt.Options.RowHeaderCaption = "WAVE_C";

var r3 = pt.PivotFields["AGE_R"];
r3.Axis = AxisTypes.Row;

var r4 = pt.PivotFields["SEX_DESC_R"];
r4.Axis = AxisTypes.Row;

pt.DataFields.Add(pt.PivotFields["Weighted Count"], "SUM of Weighted Count", SubtotalTypes.Sum);

pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12;

workbook.SaveToFile("D:\\ppt\\WebSite1\\Excel files\\sample2.xlsx", ExcelVersion.Version2007);
System.Diagnostics.Process.Start("D:\\ppt\\WebSite1\\Excel files\\sample2.xlsx");

please find attached zip file which has output excel file.
Attachments
sample2.zip
(8.64 KiB) Downloaded 322 times

chaitanya
 
Posts: 12
Joined: Thu May 16, 2013 10:42 am

Tue Jun 04, 2013 7:41 am

Dear chaitanya,

Thanks for your reply.
Please add the code workbook.Version = ExcelVersion.Version2007; to your original code.
I attached the whole code below.

Code: Select all
          Workbook workbook = new Workbook();

          workbook.LoadFromFile("D:\\ppt\\WebSite1\\Excel files\\sample1.xlsx");
           [b] workbook.Version = ExcelVersion.Version2007;[/b]

            Worksheet sheet = workbook.Worksheets[0];

            sheet.Name = "Data";

            Worksheet sheet2 = workbook.CreateEmptySheet();

            sheet2.Name = "Pivot Table";

            CellRange dataRange = sheet.Range["A1:E9"];
            PivotCache cache = workbook.PivotCaches.Add(dataRange);
            PivotTable pt = sheet2.PivotTables.Add("Pivot Table", sheet.Range["A1"], cache);

            var r1 = pt.PivotFields["YEAR_C"];
            r1.Axis = AxisTypes.Column;
            pt.Options.RowHeaderCaption = "YEAR_C";

            var r2 = pt.PivotFields["WAVE_C"];
            r2.Axis = AxisTypes.Column;
            pt.Options.RowHeaderCaption = "WAVE_C";

            var r3 = pt.PivotFields["AGE_R"] as PivotField;
            r3.Axis = AxisTypes.Row;

            var r4 = pt.PivotFields["SEX_DESC_R"];
            r4.Axis = AxisTypes.Row;

            pt.DataFields.Add(pt.PivotFields["Weighted Count"], "SUM of Weighted Count", SubtotalTypes.Sum);

            pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12;

          workbook.SaveToFile("D:\\ppt\\WebSite1\\Excel files\\sample2.xlsx", ExcelVersion.Version2007);


If you still have this issue after try it, please tell us.

Regards,
Amy
E-iceblue support team
User avatar

amy.zhao
 
Posts: 2766
Joined: Wed Jun 27, 2012 8:50 am

Wed Jun 05, 2013 4:44 am

hi amy,

its working fine now. Thanks you so much

chaitanya
 
Posts: 12
Joined: Thu May 16, 2013 10:42 am

Return to Spire.XLS