We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Excel2007Xlsx and pivot tables

Dear Mr Amjad Sahi

I am a registered client of Aspose.cells for .NET and I just found your
email in on a topic
(https://forum.aspose.com/t/92195">Laurence.

Dear Jacques,

I think you template file is Excel97-2003 format. If it's Excel2007Xlsx format, Aspose.Cells will preserve it.

We are working on converting Excel97-2003 pivot table to Excel2007 format.

Dear Laurence Chen

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

I am using an Excel 2007 xlsx file as template as you can take from the stripped down code and template attachment (created with Excel 2007) I joined to my message. This piece of code generates an empty pivot with FileFormatType.Excel2007Xslx and works like a charm with FileFormatType.Excel2003.

My Aspose.Cells.dll says : file version 4.5.0.0

Yours faithfully

Jacques Schrapp

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="XslxPivot.aspx.cs" Inherits="Topics_Templates" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Drawing;
using System.IO;
using System.Data;


using Aspose.Cells;

public partial class Topics_Templates : System.Web.UI.Page {
protected void Page_Load(object sender, EventArgs e) {

Workbook wb = new Workbook();
string designerFile = Path.Combine(Path.GetDirectoryName(HttpContext.Current.Request.PhysicalPath), "Template.xlsx");
wb.Open(designerFile);

Worksheet wsPivotData = wb.Worksheets["PivotData"];

wb.Worksheets.Add();
Worksheet wsPivotTable = wb.Worksheets[wb.Worksheets.Count - 1];
wsPivotTable.Name = "PivotTable";
wsPivotTable.Type = SheetType.Worksheet;

PivotTables pts = wsPivotTable.PivotTables;
int index = pts.Add("=" + wsPivotData.Name + "!A1:P97", "A1", "PivotTable");
PivotTable pt = pts[index];

int colFieldIndex;
colFieldIndex = pt.AddFieldToArea(PivotFieldType.Column, Column.TimeSpanShort); pt.ColumnFields[colFieldIndex].IsAutoSubtotals = false;

int rowFieldIndex;
rowFieldIndex = pt.AddFieldToArea(PivotFieldType.Row, Column.DataType); pt.RowFields[rowFieldIndex].IsAutoSubtotals = true;
rowFieldIndex = pt.AddFieldToArea(PivotFieldType.Row, Column.DataUnit); pt.RowFields[rowFieldIndex].IsAutoSubtotals = false;
rowFieldIndex = pt.AddFieldToArea(PivotFieldType.Row, Column.GroupName); pt.RowFields[rowFieldIndex].IsAutoSubtotals = false;
rowFieldIndex = pt.AddFieldToArea(PivotFieldType.Row, Column.EmployeeCount); pt.RowFields[rowFieldIndex].IsAutoSubtotals = false;

int dataFieldIndex;
dataFieldIndex = pt.AddFieldToArea(PivotFieldType.Data, Column.DataValue);
PivotField pf = pt.DataFields[dataFieldIndex];

HttpResponse response = HttpContext.Current.Response;
response.ClearHeaders();
response.ClearContent();
if (wb.Worksheets.Count > 0) {
response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
wb.Save("reportName.xlsx", FileFormatType.Excel2007Xlsx, Aspose.Cells.SaveType.OpenInBrowser, response);
}

}
internal struct Column {
internal const int GroupName = 0, EmployeeCount = 1, DataType = 2, DataUnit = 3, DataTypeSortOrder = 4, DateOrig = 5, DateFrom = 6, DateStop = 7,
TimeSpanLong = 8, TimeSpanShort = 9, DataValue = 10, PeriodSequence = 11, ResultSequence = 12, RowIndex = 13, ExcelFunction = 14, Visibility = 15, LastColumn = 15;
}
}

Hi,

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Currently Aspose.Cells will keep the pivot data if open/save a 2007xlsx format file. But it does not support get/set pivot table and pivot chart from 2007xlsx format file.

So, if your template file contains a pivot table, Aspose.Cells will preserve it. But currently you cannot add pivot table to the template file. We are working on this feature and hopefully it can be finished in a month. Thank you for your patience.

@mgethelred.ch,

We now support the feature (create, manipulate, refresh and render Pivot Tables, pivot charts and shapes in XLS/XLSX (MS Excel 2007 - 2019)) with enhancements in newer versions. We recommend you to upgrade to and give it a try to latest version of Aspose.Cells. We also recommend you to see the document with example code for your reference:
Create Pivot Tables and Pivot Charts