Hi,

I struggle with creating pivot tables and charts using Aspose.Cells 4.8.0.0.
I'm trying to create first a pivot table and then attach a pivot chart in one go. This process works using Excel 2003 but produces an empty chart with 2007.

I have set up a project to re-produce this problem but then I ran into another issue both for 2003 and 2007. The PivotTables collection in the sheet is empty, despite it's clearly visible using Excel itself. So I don't get very far at all trying to reproduce this.

I guess we have to focus on one problem at a time so if someone could please tell me what I'm doing wrong trying to find a pivot table. Sheets used and sample VS2008 SP1 project is attached. I'm using Windows 7 x64.

Sample event handlers used (I have no entries in the collection of pivot tables for both these sheets but as I said they are there if I use Excel):

   private void btnOpen2007File_Click(object sender, EventArgs e)
        {
            Workbook wb = new Workbook();
            if (opfExample.ShowDialog(this) == DialogResult.OK)
            {
                wb.Open(opfExample.FileName, FileFormatType.Excel2007Xlsx);
                Worksheet wsPivot = wb.Worksheets["Pivot"];
                Worksheet wsData = wb.Worksheets["Data"];
                PivotTable pivot = wsPivot.PivotTables[0];

                int chartIx = wsPivot.Charts.Add(ChartType.Column, pivot.TableRange2.EndRow + 2, 0, pivot.TableRange2.EndRow + 20, pivot.TableRange2.EndColumn);
                Chart chart = wsPivot.Charts[chartIx];
                chart.PivotSource = wsPivot.Name + "!" + pivot.Name;

                sfExample.FileName = "GridExport2007WithChart.xlsx";
                if (sfExample.ShowDialog(this) == DialogResult.OK)
                {
                    wb.Save(sfExample.FileName, FileFormatType.Excel2007Xlsx);
                    Process.Start(sfExample.FileName);
                }
            }
        }

        private void btnOpen2003File_Click(object sender, EventArgs e)
        {
            Workbook wb = new Workbook();
            if (opfExample.ShowDialog(this) == DialogResult.OK)
            {
                wb.Open(opfExample.FileName, FileFormatType.Excel2003);
                Worksheet wsPivot = wb.Worksheets["Pivot"];
                Worksheet wsData = wb.Worksheets["Data"];

                PivotTable pivot = wsPivot.PivotTables[0];

                int chartIx = wsPivot.Charts.Add(ChartType.Column, pivot.TableRange2.EndRow + 2, 0, pivot.TableRange2.EndRow + 20, pivot.TableRange2.EndColumn);
                Chart chart = wsPivot.Charts[chartIx];
                chart.PivotSource = wsPivot.Name + "!" + pivot.Name;

                sfExample.FileName = "GridExport2003WithChart.xls";
                if (sfExample.ShowDialog(this) == DialogResult.OK)
                {
                    wb.Save(sfExample.FileName, FileFormatType.Excel2003);
                    Process.Start(sfExample.FileName);
                }
            }
        }

I'd appreciate some help with this.

Thanks
Bert

Problem Part 2,

I'm replying to my own post but this repro is actually an example of my root problem - that charts are not created correctly in Excel 2007. I think the first part may be my issue as it seems like there are some things Aspose.Cells can't do, like for example finding existing Excel created pivot tables and I also found it was the same issue using the Cells.Ranges collection which does not seem to pick up Excel named ranges.

So this is what I'm trying to do. Creating a pivot table and attached chart using Aspose.Cells. As mentioned it works for Excel 2003 but not for Excel 2007. I have attached my two spreadsheets with only the data part in them. The project code has been revised to create the pivot table as well:

   private void btnOpen2007File_Click(object sender, EventArgs e)
        {
            Workbook wb = new Workbook();

            if (opfExample.ShowDialog(this) == DialogResult.OK)
            {
                wb.Open(opfExample.FileName, FileFormatType.Excel2007Xlsx);
                Worksheet wsPivot = wb.Worksheets["Pivot"];
                Worksheet wsData = wb.Worksheets["Data"];
                Range dataRange = wsData.Cells.CreateRange(0, 0, 9, 6);
                dataRange.Name = "DataRange";

                int pivotIndex = wsPivot.PivotTables.Add(dataRange.Name,
                                    0,
                                    0,
                                    "PivotTableAuto");
                PivotTable pivot = wsPivot.PivotTables[pivotIndex];
                for (int i = 0; i < pivot.BaseFields.Count; i++)
                {
                    PivotField field = (PivotField) pivot.BaseFields[i];
                    field.DisplayName = field.Name;
                    field.IsAutoSubtotals = false;
                    field.ShowInOutlineForm = true;
                    field.ShowCompact = false;

                    if (pivot.RowFields.Count < 1)
                    {
                        pivot.AddFieldToArea(PivotFieldType.Row, field);
                        continue;
                    }

                    pivot.AddFieldToArea(PivotFieldType.Data, field);
                }

                pivot.AddFieldToArea(PivotFieldType.Column, pivot.DataField);
                pivot.IsAutoFormat = true;
                pivot.AutoFormatType = PivotTableAutoFormatType.Classic;
                pivot.RowGrand = false;
                pivot.DataField.ShowInOutlineForm = true;
                pivot.DataField.ShowCompact = false;

                int chartIx = wsPivot.Charts.Add(ChartType.Column, pivot.TableRange2.EndRow + 2, 0, pivot.TableRange2.EndRow + 20, pivot.TableRange2.EndColumn);
                Chart chart = wsPivot.Charts[chartIx];
                chart.PivotSource = wsPivot.Name + "!" + pivot.Name;

                sfExample.FileName = "GridExport2007WithChart.xlsx";
                if (sfExample.ShowDialog(this) == DialogResult.OK)
                {
                    wb.Save(sfExample.FileName, FileFormatType.Excel2007Xlsx);
                    Process.Start(sfExample.FileName);
                }
            }
        }

        private void btnOpen2003File_Click(object sender, EventArgs e)
        {
            Workbook wb = new Workbook();

            if (opfExample.ShowDialog(this) == DialogResult.OK)
            {
                wb.Open(opfExample.FileName, FileFormatType.Excel2003);
                Worksheet wsPivot = wb.Worksheets["Pivot"];
                Worksheet wsData = wb.Worksheets["Data"];
                Range dataRange = wsData.Cells.CreateRange(0, 0, 9, 6);
                dataRange.Name = "DataRange";

                int pivotIndex = wsPivot.PivotTables.Add(dataRange.Name,
                                    0,
                                    0,
                                    "PivotTableAuto");
                PivotTable pivot = wsPivot.PivotTables[pivotIndex];
                for (int i = 0; i < pivot.BaseFields.Count; i++)
                {
                    PivotField field = (PivotField) pivot.BaseFields[i];
                    field.DisplayName = field.Name;
                    field.IsAutoSubtotals = false;
                    field.ShowInOutlineForm = true;
                    field.ShowCompact = false;

                    if (pivot.RowFields.Count < 1)
                    {
                        pivot.AddFieldToArea(PivotFieldType.Row, field);
                        continue;
                    }

                    pivot.AddFieldToArea(PivotFieldType.Data, field);
                }

                pivot.AddFieldToArea(PivotFieldType.Column, pivot.DataField);
                pivot.IsAutoFormat = true;
                pivot.AutoFormatType = PivotTableAutoFormatType.Classic;
                pivot.RowGrand = false;
                pivot.DataField.ShowInOutlineForm = true;
                pivot.DataField.ShowCompact = false;

                int chartIx = wsPivot.Charts.Add(ChartType.Column, pivot.TableRange2.EndRow + 2, 0, pivot.TableRange2.EndRow + 20, pivot.TableRange2.EndColumn);
                Chart chart = wsPivot.Charts[chartIx];
                chart.PivotSource = wsPivot.Name + "!" + pivot.Name;

                sfExample.FileName = "GridExport2003WithChart.xls";
                if (sfExample.ShowDialog(this) == DialogResult.OK)
                {
                    wb.Save(sfExample.FileName, FileFormatType.Excel2003);
                    Process.Start(sfExample.FileName);
                }
            }
        }

The end result is that Excel2003 creates what I want but I get an empty chart frame for Excel 2007. It seeems like it's not referencing the pivot table correctly for some reason.

Sorry about the double post here.

Bert

Hi Bert,

Thanks for providing us the template excel files with sample project.

After an initial test, we found the issue as you have mentioned. The pivot chart is not rendered fine in MS Excel 2007 xlsx file format. We will look into your issue soon. Your issue has been logged into our issue tracking system (internal) with an issue id: CELLSNET-11796. If there is any update for your issue, we will let you know soon.

Thank you.

---

Amjad Sahi
Support Developer,
Aspose Sialkot Team
Contact Us

Hi,

 

Please try the attached version. We have fixed an issue for "the pivot chart not rendered fine in xlsx file" you mentioned.

Please let us know if you still find the problem.

 

Thank you

---

Amjad Sahi
Support Developer,
Aspose Sialkot Team
Contact Us

The issues you have found earlier (filed as 11796) have been fixed in this update.

---

This message was posted using Notification2Forum from Downloads module by aspose.notifier.