JTable and Excel-like formulas

Question

After some research I've found a way (a long and tedious one if I understand it correctly..) to implement formulas inside my JTable.

here's what's the table like (it's edited with netbeans as you can probably tell, don't mind the massive null part, we'll get to that later)

    contractFlaminiaTable.setModel(new javax.swing.table.DefaultTableModel(
        new Object [][] {
            {"name1", null, null, null, null, null, null, null, null, null, null},
            {"name2", null, null, null, null, null, null, null, null, null, null},
            {"name3", null, null, null, null, null, null, null, null, null, null},
            {"name4", null, null, null, null, null, null, null, null, null, null},
            {"name5", null, null, null, null, null, null, null, null, null, null},
            {"name6", null, null, null, null, null, null, null, null, null, null},
            {"name7", null, null, null, null, null, null, null, null, null, null},
            {"name8", null, null, null, null, null, null, null, null, null, null},
            {"TOTAL", null, null, null, null, null, null, null, null, null, null}
        },
        new String [] {
            "  ", "col1", "col2", "col3", "col4", "col5", "col6", "col7", "col8", "col9", "TOTAL"
        }
    ) {
        Class[] types = new Class [] {
            java.lang.String.class, java.lang.Integer.class, java.lang.Integer.class, java.lang.Integer.class, java.lang.Integer.class, java.lang.Integer.class, java.lang.Integer.class, java.lang.Integer.class, java.lang.Integer.class, java.lang.Integer.class, java.lang.Object.class
        };
        boolean[] canEdit = new boolean [] {
            false, true, true, true, true, true, true, true, true, true, false
        };

        public Class getColumnClass(int columnIndex) {
            return types [columnIndex];
        }

        public boolean isCellEditable(int rowIndex, int columnIndex) {
            return canEdit [columnIndex];
        }
    });
    contractFlaminiaTable.setRowHeight(30);
    contractFlaminiaTable.getTableHeader().setReorderingAllowed(false);
    flaminiaScrollPane2.setViewportView(contractFlaminiaTable);
    contractFlaminiaTable.getColumnModel().getColumn(0).setResizable(false);
    contractFlaminiaTable.getColumnModel().getColumn(1).setResizable(false);
    contractFlaminiaTable.getColumnModel().getColumn(2).setResizable(false);
    contractFlaminiaTable.getColumnModel().getColumn(3).setResizable(false);
    contractFlaminiaTable.getColumnModel().getColumn(4).setResizable(false);
    contractFlaminiaTable.getColumnModel().getColumn(5).setResizable(false);
    contractFlaminiaTable.getColumnModel().getColumn(6).setResizable(false);
    contractFlaminiaTable.getColumnModel().getColumn(7).setResizable(false);
    contractFlaminiaTable.getColumnModel().getColumn(8).setResizable(false);
    contractFlaminiaTable.getColumnModel().getColumn(9).setResizable(false);
    contractFlaminiaTable.getColumnModel().getColumn(10).setResizable(false);
    contractFlaminiaTable.getColumnModel().getColumn(10).setPreferredWidth(48);

So, the problem is, inside the name1/2/etc rows and columns there should be numbers (instead of the null spam which was made automatically by netbeans, I do have a "reset" button in the app making a custom tableModel but that was an old build which won't be useful here) and in the total row/column there should be a sum of the numbers in the respective column/row.

From the many threads I've read there's the getValueAt method which I could use to sum the cells in the respective row/column. I'm not too sure that's a good choice here, though, due to the massive amount of cells it'd need to read each time (it's supposed to update the total dinamically when the user inputs numbers in the table, not to count I also have no idea how to implement it in specific cells).

And here are the questions:

a) Would it be possible to make an excel-like formula to add the cells? (something like adding cells c2:c6, since I do export that table in an excel file I thought if somethine like that was even possible..)

2a) Would I have to change the columns type to object or said formulas would still be considered Integers? (apart from the name column which is implemented as a String type all the other columns are Integer type, also the end user should only be able to insert numbers not to mess with the formulas but changing the type to Object would actually make it possible to insert letters as well)

b) Would it be possible to make a non excel-like formula (in case a- it's not possible that is) using eventListeners on the table? (something like the "data inside row(x), col(y) changed, updated the total cell with the sum!")

c) Since the data inside the table is exported to an xls file (already implemented that correctly after getting a few pointers in another thread) would it be possible to use said file to populate the table again or would I have to repaint it every time?

I'm sorry to ask all these questions together (especially the c- since it's not related to the title..) but I didn't really find anything on the jTable class tutorial which oracle provides nor any constructive advice in other similar-ish threads.

Please be patient, as stated in the other thread I'm still a beginner (if I can be considered one that is!) also, thanks in advance for the help, the pointers in the other really helped me out!

PS: I hope I explained it in an understandable way, being new to the programming world doesn't make things easy when you have to explain stuff..

Again, thanks in advance for the help~

EDIT:

Here's the code I came up with to implement formulas inside the table (actually it's a weird usage of get/setValueAt() but it seems to work rather good (a bit clunky but having room for improvement just makes me learn more things which is always nice at the end of the day)

class TotalButton {

public TotalButton(JTable jTable) {
    // get table model
    TableModel model = jTable.getModel();

    // declare for variables
    int x;
    int y;

    // initialize table placeholder
    int totalValue = 0;

    int rowCount = model.getRowCount();
    int columnCount = model.getColumnCount();

    // for loop for the first row
    for (x = 0; x < rowCount; x++) {
        for (y = 1; y < columnCount; y++) {
            if (y == 10) {
                break;           // break when the loop reaches the total column
            }
            if (x == 0) {

                String value = (String) model.getValueAt(x, y);
                if ("".equals(value)) {
                    value = "0";            // avoiding empty string to integer error
                }
                int tempVal = Integer.parseInt(value);           // turn the string into a number

                totalValue = totalValue + tempVal;          // adds the numbers to the total
            }
        }

        String finalValue = Integer.toString(totalValue);           // turn the number to a string
        model.setValueAt(finalValue, 0, 10);            // set total column value
    }

Basically I added a button to calculate and populate the total row/column of the table (added a keyListener at some point but, as I said, it felt kind of clunky..not that using a button feels any different but well..).

The loop you can read in the snippet is added for every cell in the Total row/column (posted only a part since it's pretty much the same thing for every cell).

Also had to change the null-s with ""-s in the jTable model so that I could set column types to String (which was later used for the Integer.parseInt() and Integer.toString() ).

On a side note, while the keyListener felt a bit clunky it would update the table in "real" time (saying "real" because inserting the new value in the table wouldn't count as a keyPressed() until the next keystroke for some reason).

Again, thanks for the pointers, the program seems to work rather fine even if it could really use some code/logic polishing (which I'm working on at the moment).

PS: I know that's far from being the best solution but I'm kind of happy since I made it on my own after researching quite some (of course, as already said, without the pointers I wouldn't have been able to write that code)

PPS: again, thank you :P

trashgod · Accepted Answer

For numbers, let your TableModel store expressions as String and let your implementation of getValueAt() return the result of evaluating the expression. Several libraries are available.

JTable and Excel-like formulas

Answers (2)

Related Questions