CODEWITHSUNDEEP
excelapache-poi
Apps
Apps

Reputation: 3389

Reading MS excel using Apache POI

We have an excel file which uses lot of built-in formulas, vlookups, Macros etc. We need to convert this excel to a web based solution.

Can we use Apache POI for this?

When we went through the documentation, we saw that it reads the excel every time and then evaluates the formula. But for multiple users accessing the applicatin, we are not sure if it is good to read excel and evaluate formula every time. Can we convert all the excel formulas to methods in a Java code and invoke it from there? Can this be done from Apache POI?

Upvotes: 0

Views: 613

Answers (1)

JKK
JKK

Reputation: 446

I do a lot of work in excel / access integration with VBA and some stuff in Java Hibernate when the record counts are real high for preprocessing on the client side before inserting records to a database.

I've had a lot of cases like this where the data is being manipulated over a few thousand lines of code and a couple formulas driven by other formulas doing queries and on and on. The problem sounds like it's way out of scope for Apache POI. The POI is meant for extracting data from more or less a static file where as your data sounds much more dynamic like a lot of my own work. I'm afraid a web solution would require a redesign, I can't think of a simple solution for this one.

Try to re examin the data your working with and see how much of it you can process on the server side and store results into a daily table to keep it performant. Reduce the number of records to constrain the record set as much as possible and simplify your queries a user may try to launch on the web form. Maybe you're doing more work than you need to in excel, any way to reduce the query's work load the better.

Back end preprocessing has helped to keep things fast and simple for me, just make sure it's all automated and well documented. Don't want some admin to come along and wonder why some task is running each night and think it's not important then have your front end running off of old bad data.

Sorry, hope that helps to give you some ideas. POI doesn't seem like a good solution in this case to me. Excel documents have a way of being way overly complex, look at ways to drill it down at the source. You might be doing half the work just formatting the data in order to process it. Some SQL statements or stored expressions might solve it in a fraction of the time. Be critical of all excel formulas!

Upvotes: 2

Related Questions