Data structure to hold Excel column names with their values

Question

I am learning how to read Excel workbooks in Java and want to know what a good Data Structure will be for holding Column names with the values under them.

Example: Excel Sheet

I want to be able to call a method: getColumnValue(String columnName, int indexOfValue) and get the value at that position for the specified column name.

I was thinking of a Hash Table with Linked Lists. However, that seems like a lot of overhead as there can be any number of columns and values associated with them.

What would be a good recommendation for this? Thanks in advance!

• JoulinRouge: why don't use a library? They already have all that a user may need to read an excel

Answer 1

Assumed you need a generic solution, where the program is not tied to a specific column structure at run time, you can use an ArrayList of rows, where each row is a string array String[]. You will need an additional HashTable<String,int> to store the mapping of the column name to the column index (a HashMap will probably work, either). So if you have

 HashTable<String,int> columnMapping;
 ArrayList<String[]> tableValues;

you can implement getColumnValue by

 String getColumnValue(String columnName, int indexOfValue)
 {
 return tableValues.get(columnMapping.get(columnName))[indexOfValue];
 }

You may consider to add some error handling here, some code which determines the column names and some more code which reads the excel rows into the String arrays with one entry per column. If you need the values in a typed form, consider to replace the String[] rows by Object[] and store the cell content as objects of type String, Integer, Double or Date. You can also implement a speficic class CellContent and store your rows as CellContent[] arrays, if you have specific requirements and a need to implement specific behaviour or methods for a content element.

Note this is a rough scetch of a general purpose solution. One can modify this and create different variants, for more specific requirements.

If you need a less generic variant, because your program expects a specific column structure, best solution is probably to create a specific DTO class MyRowType for holding the specific values of a row, and use a variable like ArrayList<MyRowType> tableValues for storing the table content.

• Vylic: Thanks for the detailed explanation! This looks like how I want to implement it.

Answer 2

If you need performance, you probably need Arrays to hold column values, else 1. you will have memory overhead and 2. accessing lower rows will be slow.

Columns can be either a Hashmap (name -> Array) or directly Object fields (the latter one is preferable but requires fields and types to be statically known).

This would be the basic, column-based answer that assumes types in every column are the same, each column is often/always filled and represent the same data. If your data is more sparse, you can store by row, each row being one Hashmap (column name -> value) you can then store in an Array or a Hashmap.

• Vylic: Can you expand on the "Object fields" part? Do you mean each column is an object (making a class for each column) and having the fields inside those classes? Don't think I'm understanding correctly.
• Diane M: I meant, for a given excel file, you have a matching class. Here you would have a class MyExcel with fields ArrayList<Integer> senders;ArrayList<Integer> receivers; and so on. Apologies for not being exact on Java types.