1

I have a large excel file to process with about 900K records. The requirement is that I have to read the excel file and do some processing on it for e.g. comparing the records for the difference in data and then display the data.

  1. I have decided not to user Excel Macros (VBA programming) as I have got to know that macros hang for very large number of records when processing the data.

  2. I have thought of using Apache POI to read the Excel file and process the data and then for displaying purpose use Java FX.

My queries:

  1. What could be the different approaches for processing large excel file and then displaying the data in table (spreadsheet / excel) format. ?

  2. Is my approach of using Apache POI to process the data and Java FX to display the data right? Or there can be better approaches ?

  3. Most importantly how do I filter the data based on a column header in Java FX ? This is an important requirement of my project.

  4. Also, I have to Bulk update a particular field. For e.g. I have a field as ABC and corresponding to that field I write some data in the next column. Then wherever I have that field in the table the particular value should be copied in the next column.

Please do share your valuable suggestions.

Community
  • 1
  • 1
sid
  • 129
  • 3
  • 11
  • 2
    My suggestion: don't use Excel spreadsheets for representing your datasets. Use a database or a CSV file. (Look at all the other questions where people talk about problems with large spreadsheets ....) – Stephen C May 27 '17 at 08:49
  • 1
    Your question is too broad. A long while back I wrote a proof-of-concept JavaFX table view that had >1,000,000 rows, loading them from a web resource in a background thread (can't find the code now, but it is out there somewhere...); it works fine. Filtering a table view is pretty standard, e.g. http://code.makery.ch/blog/javafx-8-tableview-sorting-filtering/ (you can easily put the text field in the column header). Last requirement just sounds like a dependency between properties in the model, which in JavaFX you would handle with properties and/or bindings. – James_D May 27 '17 at 13:34
  • hi stephen .... how would a CSV file help me ? please could you elaborate more on the same... can I filter a CSV file on the basis of columns ... can I compare 2 CSV rows .... can apache POI read a CSV file with 900K records .... – sid May 27 '17 at 17:59

1 Answers1

1

Your question is very broad, but you may get some ideas from this example that uses a SwingWorker<TableModel, String> to read large text files and display them in a JTable. The same approach in JavaFX should see similar benefits. The corresponding model in JavaFX might be a Task<ObservableList<Row>>, illustrated here for Task<Canvas>. This example shows how to traverse the rows and cells in a Sheet. You should be able to filter rows as they arrive or later in the TableView, as shown here. Naturally, you'll need to prototype this and profile to be sure it meets your requirements.

trashgod
  • 203,806
  • 29
  • 246
  • 1,045
  • This [comment](https://stackoverflow.com/questions/44214756/processing-and-displaying-large-amount-of-data-in-java/44216953?noredirect=1#comment75445615_44214756) makes me think it's worth pursuing. I'm pretty sure `TableView` uses flyweight rendering, as does `JTable`. – trashgod May 27 '17 at 18:25