1

I am trying to edit an existing excel file using SXSSF. This excel may contain 1 Million records. I have to validate each row in excel and if the recors is invalid, append error message in the last column of row with errors in it. Validation process is done first, at that time i will note down the row number which is invalid. once all the validation is over, i'll take a copy of the file and write the error details at the end of each failed rows. Since SXSSF is write only, I am getting a null value when i tried to get the invalid row with the row number. Please suggest a better way to resolve it.

I came across some suggestions to use SAX + SXSSF to do it in the following threads.

poi read existing excel and edit with large data

How do i read and edit huge excel files using POI?

I know how to read excel using SAX. But dont know how to relate it with SXSSF and edit the excel.But it will be great if some one share a sample code.

Thanks in advance.

Community
  • 1
  • 1
Renjith
  • 1,122
  • 1
  • 19
  • 45
  • Can you not just take the easy route, sling another few GB of memory into your server, and do it all with XSSF? – Gagravarr May 07 '14 at 10:06
  • Ok. Since we may have to process n number of such records in a single day, we are also concerned about speed. That is the only reason we deviated from XSSF approach towards SAX+XSSF approach to read the excel. With this approach we could see considerable difference in speed while reading the excel data. – Renjith May 07 '14 at 11:16
  • How does that speed difference compare against programmer time? Saving 5 minutes a day of processing time, at a cost of several man-weeks of programmer time, may well not make overall sense! – Gagravarr May 07 '14 at 11:47
  • :), that's true. But on reading an excel file(30kb), we spotted a difference of (XSSF(3 seconds) & SAX (700ms)). Hope that is a considerable difference when it comes to huge files. – Renjith May 07 '14 at 12:17
  • That seems slow to me. A 50 columns, 2000 rows, 420kb .xlsx loads quicker than that on my laptop! Can you try the approach from the [Apache POI FAQ entry "I think POI is using too much memory! What can I do? "](http://poi.apache.org/faq.html#faq-N10109) and just double check there's not something odd with your setup and/or file? – Gagravarr May 07 '14 at 13:41
  • It was done on my office desktop, which is 'really... SLOW'. But the link you shared seems interesting. I'll look into it and get back to you. – Renjith May 07 '14 at 14:38
  • @Renjith did you find a solution can you share? I 'm facing same problem – ardjavatake2selenium Feb 20 '20 at 16:53
  • @ardjavatake2selenium I am sorry, i dont remember what I really did to resolve it. Possibly I must have used some writer to write an additional tag. The way i read the file is detailed here: https://stackoverflow.com/questions/24779440/efficient-way-to-search-records-from-an-excel-file-using-apache-poi?answertab=active#tab-top – Renjith Feb 25 '20 at 06:46

0 Answers0