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
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,
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.
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