This project is read-only.

Slow iteration in XSSFSheet

Apr 15, 2015 at 3:12 PM
Hello, I have a performance issue with XSSFSheet. I have a sheet with 65536 rows (yes, that is a maximum for HSSFSheet). If I use exactly the same processing of the same data being stored as XLS and XLSX it takes 5s for HSSF and more than 20s for XSSF to process all the rows. I've fried to change the way I iterate over all rows (using index access or IEnumerator) but with no difference.

Is there any way I could speed this up please? I'll always want to process all rows and all cells in the sheet. XSSF is my choice because of the data limits and for other reasons but this is really unpleasant.

Thanks for any help,

Martin
Apr 15, 2015 at 10:15 PM
The reason is simple. HSSF is for binary based Excel format called BIFF format. XSSF is for XML based Excel format called OpenXML. XML is very slow in .NET (maybe XmlReader can be faster but it's complicated to use for some cases).
Apr 16, 2015 at 5:28 PM
I understand this is a reason for slow serialization/deserialization. But does it have the same impact on the speed when traversing data already read in the memory? I have managed to speed my application by caching some values so it is workable now.

Thanks for the reply.

Martin