Urgent request: Deleting a sheet from workbook produced by Aspose using POI

Hi,

We have certainly functionality that uses POI which we want to replace in a phased manner using Aspose. Till that time, we would like to use the existing code.

For merging 2 workbooks, we use Aspose.

But after merging 2 workbooks using Aspose, we are not able to delete the sheet using POI on the same workbook.

But the same POI API works fine on any normal excel workbook. Please find the sample code and sample workbook attached.

Book1.xls - Input file

outputWithAspose.xls - output excel produced by Aspose

/*
* File : $Source: $
* Version : $Revision: $
* Date : $Date: $
* Modified by : $Author: $
*/

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

import com.aspose.cells.FileFormatType;
import com.aspose.cells.License;
import com.aspose.cells.Workbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class TestAspose
{
static String wd = "C:\\Developer\\TEMP\\frame\\";

@SuppressWarnings("unchecked")
public static void main(String[] args) throws Exception
{
File file = new File("C:\\Developer\\TEMP\\frame\\");
File[] inputFiles = file.listFiles();

License license = new License();
license.setLicense(new FileInputStream("C:\\Developer\\work\\workspace\\TestAspose\\lib\\Aspose.Cells.lic"));
Workbook outputWorkbook = new Workbook();
for (File inputFile : inputFiles)
{

if (!inputFile.getAbsolutePath().equalsIgnoreCase("C:\\Developer\\TEMP\\frame\\master_blank.xls"))
{
Workbook book = new Workbook();

book.open(inputFile.getAbsolutePath());
outputWorkbook.combine(book);
}
}

String fileName = wd + "outputWithAspose.xls";
outputWorkbook.save(fileName, FileFormatType.EXCEL97TO2003);
//
// An excel file name. You can create a file name with a full path
// information.

String filename1 = wd + "text1.xls";
FileInputStream fis = null;
FileOutputStream fos = null;
try
{
//
// Create a FileInputStream that will be use to read the excel file.
//
fis = new FileInputStream(fileName);
fos = new FileOutputStream(filename1);
//
// Create an excel workbook from the file system.
//
HSSFWorkbook workBook = new HSSFWorkbook(fis);

int controlSheetIndex = workBook.getSheetIndex("ControlSheet");
if (controlSheetIndex != -1)
{
System.out.println("control sheet found");

workBook.removeSheetAt(controlSheetIndex);

}

workBook.write(fos);

}
catch (IOException e)
{
e.printStackTrace();
}
finally
{
if (fis != null)
{
fis.close();
}
}

}

}

Exception:

Exception in thread "main" java.lang.ClassCastException: org.apache.poi.hssf.record.BOFRecord cannot be cast to org.apache.poi.hssf.record.TabIdRecord
at org.apache.poi.hssf.model.InternalWorkbook.fixTabIdRecord(InternalWorkbook.java:782)
at org.apache.poi.hssf.model.InternalWorkbook.removeSheet(InternalWorkbook.java:752)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.removeSheetAt(HSSFWorkbook.java:827)
at poi.TestDeleteSheet.main(TestDeleteSheet.java:54)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:110)

Please help at the earliest.

Hi Dilip,


We have forwarded your issue to our Development Team. Soon they will get back to you on this.
Thanks for your patience.

Hi,

We have analyzed your issue a bit. In fact it is a bug of POI
for checking TabId records(0x013D). Even for xls files that are
generated by MS Excel, if there are more than 4112 sheets in the file,
the POI will give same an exception when deleting sheets, you may check
this. So, it is better for you to report such a bug to POI team to
solve this issue completely.

Anyways, we can make an enhancement
for the Aspose.Cells generated xls files so, when there are less than
4122 sheets to avoid such an exception from POI APIs for you. We will
make it at the end of this week or begin of the next week.

Thank you

Thanks. That would be really helpful if you could make the enhancement for wokbook less than 4122 sheets to avoid issue in POI as our workbook will not have more than 4122 sheets.

Thanks

Dilip

Hi,

In fact it is more like a bug of POI than issue of Aspose.Cells for Java. Anyways we can provide a fix for you to deal with this situation. We will provide a fix soon.

Yes, your help is much appreciated.

Thanks

DJ

Hi,

Please try Aspose.Cells for Java v2.5.2.10.
We have made enhancements for the generated xls file to avoid the issue from POI.

Thank you.


Thanks for the quick turnaround.

Thanks

DJ

I’m testing the latest aspose-cells-7.2.0.jar and having exactly the same problem…


the aspose-cells-2.5.2.10.jar breaks my code…

any work around ?

thanks
Hi,

Yes, older version e.g v2.5.4.x will break your existing new codes, the reason is we have changed our API model a bit to make it similar to our .NET component. Now, there is no difference b/w JAVA version or .NET version regarding APIs, features, performance, enhancements etc.

Please try our latest fix v7.2.0.4: Aspose.Cells for Java v7.2.0.4


If you still find the issue with v7.2.0.4, please give us / attach your runnable JAVA program with template file(s), we will check your issue soon.

Thank you.
Hi,

Please try the new fix Aspose.Cells for Java v7.2.0.6

We have made enhancements to make the cells created in XLS file compatible with POI.


Thank you.

The issues you have found earlier (filed as CELLSJAVA-40183) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by aspose.notifier.