One of our customers has noted an issue with the following.
We have a pivot table with multiple page fields… when the customer goes to select the page fields, they appear out of order as such:
All
– GA
– AZ
– OR
They’d like them to appear in order. is this a known problem?
I was able to duplicate it using the same AdventureWorks sample I’ve had in previous posts. Go to the pivot table and pull down salesTerritory, you will see they are out of order.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Aspose.Cells;
namespace AsposeProblems
{
class Program
{
static void Main(string[] args)
{
string connectionString = “Data Source=_____;DATABASE=Adventureworks;Integrated Security=SSPI”;
string outputFileExcel2003 = @“c:\AsposeError2003.xls”;
string outputFileExcel2007 = @“c:\AsposeError2007.xlsx”;
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand comm = conn.CreateCommand();
comm.CommandText = "SELECT soh.[SalesPersonID],c.[FirstName] + ’ ’ + COALESCE(c.[MiddleName], ‘’) + ’ ’ + c.[LastName] AS [FullName],e.[Title],st.[Name] AS [SalesTerritory],soh.[SubTotal],YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] FROM [Sales].[SalesPerson] sp INNER JOIN [Sales].[SalesOrderHeader] soh ON sp.[SalesPersonID] = soh.[SalesPersonID] INNER JOIN [Sales].[SalesTerritory] st ON sp.[TerritoryID] = st.[TerritoryID] INNER JOIN [HumanResources].[Employee] e ON soh.[SalesPersonID] = e.[EmployeeID] INNER JOIN [Person].[Contact] c ON e.[ContactID] = c.ContactID ";
comm.CommandType = System.Data.CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
conn.Open();
da.Fill(ds);
conn.Close();
da.Dispose();
comm.Dispose();
conn.Dispose();
DataTable dt = ds.Tables[0];
Aspose.Cells.Workbook wb = new Workbook();
wb.Worksheets.Clear();
wb.Worksheets.Add(“Data”);
wb.Worksheets.Add(“PivotMain”);
wb.Worksheets[“Data”].Cells.ImportDataTable(dt, true, “A1”);
string wkRange = “=” + wb.Worksheets[0].Name + “!A1:” + GetColumnID(dt.Columns.Count, true) + (dt.Rows.Count + 1).ToString();
int pivotIndex = wb.Worksheets[“PivotMain”].PivotTables.Add(wkRange, “A1”, “PvMain”);
PivotTable pt = wb.Worksheets[“PivotMain”].PivotTables[pivotIndex];
pt.AddFieldToArea(PivotFieldType.Row, 2);
pt.AddFieldToArea(PivotFieldType.Row, 1);
pt.AddFieldToArea(PivotFieldType.Data, 4);
pt.AddFieldToArea(PivotFieldType.Column, 5);
pt.AddFieldToArea(PivotFieldType.Page, 3);
wb.Worksheets[“Data”].AutoFitColumns();
wb.Worksheets[“PivotMain”].AutoFitColumns();
wb.Save(outputFileExcel2003, FileFormatType.Excel2003);
wb.Save(outputFileExcel2007, FileFormatType.Excel2007Xlsx);
}
private static string GetColumnID(int columnPosition, bool isOneBased)
{
//isOneBased == false means is 0 based.
if (isOneBased) columnPosition–;
if (columnPosition < 0) return String.Empty;
if (columnPosition > 255) return String.Empty;
string alphaBet = “ABCDEFGHIJKLMNOPQRSTUVWXYZ”;
if (columnPosition <= 25)
return alphaBet.Substring(columnPosition, 1);
else
{
int major = (columnPosition / 26);
int minor = columnPosition % (major * 26);
major–;
return alphaBet.Substring(major, 1) + alphaBet.Substring(minor, 1);
}
}
}
}
Hi,
Kindly try the latest fix (attached). If you still find the issue, kindly post your excel file here to show the issue, we will check it soon.
Thank you.
Tried the fix with 4.8.2.11, no go. Fails in both Excel 2003 and Excel 2007 using the code above.
Attached are the files… take a look at the PivotMain worksheet, cell B1.
Hi,
Do you need to sort the page field items? I think you may try some API for your need, e.g, you can use IsAscendSort and IsAutoSort attributes of the PivotField class.
Sample code:
PivotField pf = pivotTable.PageFields[pageFieldIndex];
pf.IsAutoSort = true;
pf.IsAscendSort = true;
For reference, please see the document: http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/setting-pivottables-appearance.html
Thank you.
Thank you, this seemed to work. I’ll try this property in my larger project.