You're welcome. As I said, I do not have any idea of the way JExcel approaches that task as it is a long time since I used the API - in fact I think that feature did not even exist at the time. However, I know that JExcel parses the Excel file to work with it and so suspect it will do just that here; if so, it may have the same memory issues with large files. The question is what information JExcel extracts from the file as it parses it; many of the memory problems we see are caused by POI's extracting LOTS of information from the file and making this available through the API in my opinion.
All the best and if you need any more help, just post a question to the list and I am confident someone will respond. Yours Mark B BoBoMonkey wrote: > > Thanks your point is noted, I have no clue as what approach is used by the > jexcelapi. I would try to delve into the eventmodel api during free time. > Thanks again for you insightful and helpful post. > > > MSB wrote: >> >> That may well look easy using JExcel but remember that, as David >> intimated with regard to VBA, the API is actually hiding the >> implementation from you. How do you know that JExcel is not reading in >> the entire workbook and creating an in-memory copy that it can search - >> think the DOM that can be used to manipulate in-memory version of xml >> documents. You need to check very carefully to ensure that JExcel will >> not gobble up memory if you are working with very large worksheets. Bear >> in mind that I do not know what JExcel is doing so could very well be >> criticising the API unfairly; my intention here is only to emphasise that >> you need to test JExcel and place it under the same scrutiny. >> >> Before choosing to abandon POI completely, may I suggest that you >> consider the alternative methodology that the API offers - to quote from >> the website; >> >> "If you're merely reading spreadsheet data, then use the eventmodel api >> in either the org.apache.poi.hssf.eventusermodel package, or the >> org.apache.poi.xssf.eventusermodel package, depending on your file >> format." >> >> >> Have a look here; >> >> http://poi.apache.org/spreadsheet/how-to.html#event_api >> >> and it may well be the case that you can use a similar approach to parse >> the Excel file searching for just the terms you want and outputting the >> cell addresses for each. Being completely honest with you, I have never >> used the event driven approach myself but do understand that several list >> members have. Further, they have had considerable success working with >> very large files in this manner and I am confident that they would be >> only too happy to advise and assist if you chose this approach and needed >> to post questions to the list. >> >> To answer your final question, as far as I am aware, there is no >> interoperability between VBA and Java. I would hazard a guess that the >> only way to accomplish something like this would be either to use >> something like Python to glue components together (and you could very >> well still be limited to Windows platforms only) or to move to the .NET >> platform and use Microsoft's unsupported (I believe) 'version' of Java. >> That platform should allow you to create components using VBA and MS Java >> and glue them together but even it could limit the platforms your code >> will run on. >> >> Yours >> >> Mark B >> >> >> BoBoMonkey wrote: >>> >>> >>> >>> Thank I found the answer, it's easily possible through JExcelApi >>> (http://jexcelapi.sourceforge.net/). >>> >>> the code is as below. >>> >>> >>> Cell tableStart=sheet.findCell(textToBeFound); >>> startRow=tableStart.getRow(); >>> startCol=tableStart.getColumn(); >>> >>> Cell tableEnd= sheet.findCell(textToBeFound,startRow, startCol, >>> 64000, >>> 100, false); >>> >>> endRow=tableEnd.getRow(); >>> endCol=tableEnd.getColumn(); >>> >>> >> >> > > -- View this message in context: http://www.nabble.com/How-to-perform-Find-and-Findnext-on-an-excel-sheet-using-POI-tp25721755p25745972.html Sent from the POI - User mailing list archive at Nabble.com. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
