How to Create a Java Spreadsheet Data Structure

By Kevin Walker

i number background image by kuhar from Fotolia.com

At the simplest level, you can create a data structure for a spreadsheet in Java using a simple 2D array. However, this approach leaves a great deal to be desired. Ideally, a spreadsheet data structure should be able to efficiently resize, insert, and delete entire rows and columns, and these operations are computationally expensive in a Java array, requiring Java to rebuild the entire data structure from scratch behind the scenes each time such an operation is performed.

Instead, you should use something a bit more sophisticated: a linked list of linked lists. This will allow the insertion and removal of rows and columns as well as the resizing of the table to happen much more quickly. However, it will come with a small performance cost when it comes time to access specific cells of the spreadsheet out of turn.

Step 1

Create a new Java file and name it "SpreadsheetStructure.java." If you have a favorite Integrated Development Environment (IDE), you can do this by clicking "File," "New Class." Alternatively, you can open Windows Notepad by clicking "Start," "All Programs," "Accessories," "Notepad" and saving the file with that file name.

Step 2

Create your class interface by pasting the following into the file you created in Step 1. The rest of the steps will be dedicated to filling in the code for each of the methods defined in this step:

public class Spreadsheet {

LinkedList<LinkedList> data = new LinkedList<LinkedList>();



public Spreadsheet() {  }





public void addRow() {  }



public void addRow(int index) { }





public void removeRow(int index) {  }



public void removeColumn(int index) {   }





public void addColumn() {   }



public void addColumn(int index) {  }



public void setCell(int x, int y, String newData) { }



public int getWidth() { }



public int getHeight() {    }





public String toString() {  }



/**

 * Test the class,

 */

public static void main(String[] args) {    }

}

The basic data structure will be a LinkedList of rows containing a LinkedList of columns. Though a simple 2D array would be simpler to implement, it would also be much slower for many common spreadsheet operations, especially inserting and deleting rows.

Step 3

Paste the following code between the brackets of the constructor method, "Spreadsheet":

public Spreadsheet() {

    addRow();

    addColumn();

}

This simply ensures that we start with at least a single row and column.

Step 4

Paste the following to fill in the two addRow methods. One takes no arguments and automatically adds the row at the end of the spreadsheet, while the other allows the user to specify where to add the row:

public void addRow() {

    data.addLast(new LinkedList<String>());

    for (int x = 0; x < getHeight(); x++) {

        data.getLast().add(new String());

    }



}



public void addRow(int index) {

    data.add(index, new LinkedList<String>());

    for (int x = 0; x < getHeight(); x++) {

        data.get(index).add(new String());

    }

}

Step 5

Paste the code to remove a row into your source file:

public void removeRow(int index) {

    data.remove(index);

}

Step 6

Paste the code for the two addColumn methods. These function similarly to the addRow methods--there are two, one for adding a column to the end of the sheet and one for inserting a column in the middle:

public void addColumn() {

    for (LinkedList l : data) {

        l.addLast(new String());

    }

}



public void addColumn(int index) {



    for (LinkedList l : data) {

        l.add(index, new String());

    }

}

Step 7

Paste the code for removing columns:

public void removeColumn(int index) {

    for (LinkedList l : data) {

        l.remove(index);

    }

}

Step 8

Paste the code that sets the contents of a given cell to a string of data:

public void setCell(int x, int y, String newData) {

    data.get(x).set(y, newData);

}

Step 9

Fill in the methods for getting the width and height of the spreadsheet:

public int getWidth() {

    return data.getFirst().size();

}



public int getHeight() {



    return data.size();

}

Step 10

Implement the toString method. This will return a formatted string displaying, in a table of rows and columns, the data in the spreadsheet:

public String toString() {

    String temp = "";

    for (LinkedList l : data) {

        for (Object o : l) {

            String s = (String) o;

            if (s.equals("")) s = "-empty-";

            temp += s + "  ";

        }

        temp += "\n";

    }

    return temp;

}

Step 11

Paste the following main method that puts the spreadsheet data structure through its paces and ensures everything works as expected:

public static void main(String[] args) {

    Spreadsheet sheet = new Spreadsheet();

    System.out.println(sheet.toString());

    System.out.print(sheet.getWidth());

    System.out.print(" x ");

    System.out.println(sheet.getHeight());



    sheet.addRow();



    System.out.print(sheet.getWidth());

    System.out.print(" x ");

    System.out.println(sheet.getHeight());



    sheet.setCell(0,0,"1");

    sheet.setCell(0,1,"2");

    sheet.setCell(1,0,"3");

    sheet.setCell(1,1,"4");



    System.out.println(sheet.toString());



    sheet.addColumn(1);

    sheet.setCell(1,0, "TEMP");

    sheet.setCell(1,1, "TEMP");



    System.out.println(sheet.toString());



    sheet.removeRow(1);



    System.out.println(sheet.toString());



    sheet.removeColumn(1);



    System.out.println(sheet.toString());

}

Writer Bio