pivot table is working only in 2010 version

[chaitanya]

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.

[amy.zhao]

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

[chaitanya]

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.

[amy.zhao]

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

[chaitanya]

hi amy,

its working fine now. Thanks you so much