A simple problem

[RMTTeam]

I'm working right now with aspose.cells 4.8.0.0 which you guys have done a fantastic job on.

I found 2 bugs... the first I found a workaround for, the second I did not.

1) The first only occurs in Excel 2003. I have row fields of "A" and "B" and A has some very long value. I also have a page field of "C" which has a short label. So when I do the AutoFitColumns at the end, it fits ALL columns correctly... except for the first one. It uses the width of "C"'s label, as opposed to the width of "A".

I worked around this by also AutoFitting the first spreadsheet, taking the column width of the same column, and then applying it AFTER I do the autofit. So, no issues -- this is for your guys information. This error does NOT occur in Excel 2007 so it may not be worth fixing.

2) Same pivot table. In Excel 2003, the row fields display the proper header names ("A" and "B"). In EXcel 2007 the row fields display the following headers "Row Labels" and "B". Similarly, the column field which is correct in Excel 2003 shows "Column Labels" in Excel 2007.

I'm probably missing something simple but I am at my wits end going through the documentation to find it.

---

This message was posted using Aspose.Live 2 Forum

[Amjad_Sahi]

Hi,

jamesmnorman:

1) The first only occurs in Excel 2003. I have row fields of “A” and “B” and A has some very long value. I also have a page field of “C” which has a short label. So when I do the AutoFitColumns at the end, it fits ALL columns correctly… except for the first one. It uses the width of “C”'s label, as opposed to the width of “A”.

I worked around this by also AutoFitting the first spreadsheet, taking the column width of the same column, and then applying it AFTER I do the autofit. So, no issues – this is for your guys information. This error does NOT occur in Excel 2007 so it may not be worth fixing.

We would love to correct if we find this issue. We appreciate if you could provide us the template file(s)( input (if any) and output(processed by Aspose.Cells for .NET)) with sample code etc. We will check it soon.

jamesmnorman:

2) Same pivot table. In Excel 2003, the row fields display the proper header names (“A” and “B”). In EXcel 2007 the row fields display the following headers “Row Labels” and “B”. Similarly, the column field which is correct in Excel 2003 shows “Column Labels” in Excel 2007.

Could you show a simple console application to show the issue with the generated file(s). We will check it soon.


Thank you.

[RMTTeam]

If you have Adventureworks installed on SQL 2005 (and a C drive you can write to) you should be able to tweak the connection string and run this code as is.

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);
}

}
}

[RMTTeam]

By the way, the console application above reflects BOTH errors.

[Amjad_Sahi]

Hi,

Please try the attached latest fix v4.8.0.12. If you still find the issue, kindly post your generated xls and xlsx files here, we will check it soon.

Thank you.

[RMTTeam]

Hi Amjad, I tried it with my test program and both cases still failed.

I’m wondering if the bug was misunderstood, I’ve added four screenshots – 2 that reflect what we currently get, and two that reflect what I would expect

Granted, these are formatting issues, but our internal customers tend to expect a lot.

[RMTTeam]

By the way, here are the files.

[RMTTeam]

Also when we get the updated DLL from you, can we get the .net 2.0 version as opposed to 1.1?

[Amjad_Sahi]

Hi,

Thanks for providing us files with details.

1) The issue of column width:
Currently, we do not support to calculate the data of a pivot table, so, the Worksheet.AutoFitColumns does not effect. Just to update you, we will support to read the pivot table from the template file and calculate data of the pivot table in Q1 2010.

2) The issue of pivot table in Excel 2007:
We have found this issue and we will fix it soon.

"Also when we get the updated DLL from you, can we get the .net 2.0 version as opposed to 1.1?"
OK, we will provide you the .net2.0 version of the fix.


Thank you.

[Amjad_Sahi]

Hi,

Just to inform you, we have also logged your second issue into our issue tracking system with an issue id: CELLSNET-11310.

A fixed version would be sent to you when it is resolved.

Thank you.

[RMTTeam]

This is fine as I have created a workaround for issue #1, but not issue #2.

For others who are having issues with #1 above, if you look at my code sample above, after the AutoFit commands, you want to issue this command

wb.Worksheets[“PivotMain”].Cells.SetColumnWidth(0, wb.Worksheets[“Data”].Cells.GetColumnWidth(2));


Where the “2” represents the index of the column from the original spreadsheet I am using to set the first PivotFieldType.Row . (Since I put everything starting at A1, it also happens to be the ordinal position of the field in the dataset.)

[nausherwan.aslam]

Hi,

Thank you for considering Aspose.

Please try the attached latest version of Aspose.Cells. We have fixed your mentioned issue regarding pivot table in Excel 2007. Please do let us know if you still face any problem.

Thank You & Best Regards,

[RMTTeam]

Problem #2 is indeed fixed, and as I said above I have a workaround for problem #1 that is only two lines of code, so I am a happy guy