Free Support Forum - aspose.com

Pivot table error when using remarks field

I am getting an Excel error when using a remarks in the pivot. The field currently has no more than 256 characters on any row. The error popas up when opening the file. If I leave the remarks field out of the pivot, it generates and displays with no error. The Excel error is attached.

Hi,

Could you post your file and some codes?

I have a attached a small file with a remarks column. The last row has 256 characters and fails with the error message above. If I delete a character, the file works fine with 255 chars.

public static final void testRemarks() {
String filename = "C:/RMK_Source.xls";
String saveFilename = "C:/RMK_Source_w_Pivot.xls";

// Create the Excel workbook
Workbook wkb = new Workbook();
try {
wkb.open(filename);
}
catch(IOException e) {
return;
}

// create a second sheet for the data
wkb.getWorksheets().addSheet("Pivot");
Worksheet pivotSheet = wkb.getSheet(1);

PivotTables pivotTables = pivotSheet.getPivotTables();
String sourceData = "SOF_Export!A1:K257";
pivotTables.add(sourceData, 1, 0, "myPivot");

// write workbook
try
{
wkb.save( saveFilename );
}
catch(Exception e)
{
System.out.println("Exception class: " + e.getClass() + " '" + e.getMessage() + "'");
}
}

Hi,

The item of the pivot table only can contain 255 characters .

Please try this fix.

This didn't have any effect - I still get the same error.

Does Excel limit the size to 255? I can use the Excel interface to put the REMARKS in the pivot, but I guess it could truncate the data internally.

Also just an FYI - it looks like you left some debugging calls in setLicense().

step 1
step11
Aspose.Cells for Java
step12
step 2
step 3
step31:Aspose.Cells for Java
step 4
step 5

Hi,

Sorry for my mistake. I will remove it.

Hi,

It works fine in my machine. So please try the fix in

http://www.aspose.com/Community/forums/thread/59074.aspx and post your created file.

Excel will auto truncate the data and the data in the pivot table will not be same to the original data. So in my opinion, you should limit the size of string data.

I'm still getting an error on file open. It works fine with <256 characters, but fails once I get to 256. Using the code shown above with the Source Excel file, I get the results in the attached file.

Thanks.

Hi,

Please try this fix.

Well, you're going to love me today. The new jar did fix the problem with the sample data with the large remarks.

I am now getting the same problem but for two completely different reasons. I have attached two source Excel files with Date fields. One file has time detail in the dates, the other is down the day only.

The problem is the same, and can be resoved in one of two ways. The first way the bug goes away is by generating the pivot without times in the data - use the wo_Times.xls file. The second way was by deleting Column D. Without that column the pivot worked fine, even with times included.

public static final void testDates() throws SQLException {

// Pivot generats with NO time data in the dates...
//String filename = "C:/Dates_Source_wo_Time.xls";
//String saveFilename = "C:/Dates_Pivot_wo_Time.xls";

// Pivot will also generate if you delete column D (remember to change the SourceData string)
String filename = "C:/Dates_Source_w_Time.xls";
String saveFilename = "C:/Dates_Pivot_w_Time.xls";


// Open the Excel workbook
Workbook wkb = new Workbook();
try {
wkb.open(filename);
}
catch(IOException e) {
return;
}

// create a second sheet for the pivot
wkb.getWorksheets().addSheet("Pivot");
Worksheet pivotSheet = wkb.getSheet(1);

PivotTables pivotTables = pivotSheet.getPivotTables();
String sourceData = "DataSheet!A1:G55908";
pivotTables.add(sourceData, 1, 0, "myPivot");


// write workbook
try {
wkb.save( saveFilename );
}
catch(Exception e) {
System.out.println("Exception class: " + e.getClass() + " '" + e.getMessage() + "'");
}
}

Hi,

Please try this fix.

Note :Worksheet.getCommnents() method has bean renamed as Worksheet.getComments() .