Thursday, September 19, 2013

Easy way read Excel sheets through Java Application



Easy way read Excel sheets through Java Application
In this article I’m going to introduce a easy way to convert  Excel spread sheets to XML files using  Java language. Suppose you are having a Excel sheet with this template.
                                   
Step 1
In my case I have an application GUI to browse tho location of the Excel sheet. You can use your own way to import Excel sheets to your appliation and it doesn’t matter whatever the way you use. Now you have the location of the excel sheet.

Step 2
To read the whole Excel sheet data you can use the following java (method)code segment.




public Vector readExecl(String fileName) {
        Vector cellVector = new Vector();
           try {
            FileInputStream myInput = new FileInputStream(fileName);
            POIFSFileSystem myfileSystem = new POIFSFileSystem(myInput);
            HSSFWorkbook myworkbook = new HSSFWorkbook(myfileSystem);
            Workbook myworkbook = WorkbookFactory.create(new File("file.xlsx"))
            HSSFSheet mySheet = myworkbook.getSheetAt(0);
            Iterator rowIter = mySheet.rowIterator();
 while (rowIter.hasNext()) {
HSSFRow myRow = (HSSFRow) rowIter.next();
Iterator cellIter = myRow.cellIterator();
                                    Vector cellStoreVector = new Vector();
while (cellIter.hasNext()) {
HSSFCell myCell = (HSSFCell) cellIter.next();
{
                                                                        String s = myCell.toString();
                                                                        cellStoreVector.addElement(myCell);
                                                            }
}
                                    cellVector.addElement(cellStoreVector);
}
        } catch (Exception ex) {
            System.out.println(ex);
        }
return cellVector;
}

To use this method you have to add a jar file(“Apache POI”) to your project.

Click here to download the latest version of the jar

The latest development release is Apache POI 3.10 Beta 1

Then right click on your projects “Libraries” and select “Add JAR/Folder” and select the location of your jar file.

This method need the location of the Excel sheet as the input parameter. The output of this method is a Vector that contain all the data in the Excel sheet row vice. Vector is a data type that can keep any kind of data. It’s most like to as array data type.

In this method it read the excel sheet row vice for each row it create a Vector and each cell of that row also create a vector.

<cellVector   <cellStoreVector>  <cellStoreVector>  >    this is an entire row with two cells(Columns).

Vector data type is not consider the data type that it going to store, you can store Integer values, String values, Double values in a same vector.

The “Iterator” object goes row by row of the excel sheet and read the data of the row until the iterator has next.

“HSSFRow” objects can find the rows from the  “Iterator” object.

“HSSFCell” objects can identify the cells of a given “cellIterator” object.

When all the cells of a given row has been finished it add that  “cellStoreVector” to the “cellVector”

Finally the method returns the   “cellVector”    that contain all the data in the Excel sheet.

Now all the data is stored in that vector. If  you want to do some filtering of  the data you need to read the vector data again.


To read the stored Vector data

·         This following method can be used for filter the data according to your requirement. In here I want to get the data of the row 6 of the Excel sheet.

·         The created vector should be pass to the method.

·         Then the inside vectors are detached from the main vector and read them in a sorted order.

·         Finally that method will return a vector with filtered data.

 

 

public Vector showExeclStuDetailsHeaders(Vector execldata) {

            Vector v = new Vector();

 for (int i = 0; i < execldata.size(); i++) {

                       if (i == 6) {

                        Vector celVec = (Vector) execldata.elementAt(i);

                        for (int j = 0; j < celVec.size(); j++) {

HSSFCell cell = (HSSFCell) celVec.elementAt(j);

                                    String c = cell.toString();

                                    v.add(j, c);

}

}

        }

        return v;

    }

Friday, August 2, 2013

Use Progress bars in jTable in java




Here is a simple example for use progress bars in jTable using  Java language. In this case I want to display a table with some registration numbers of students and their  attendance percentage with the conducted sessions and the attendant sessions.
Suppose I want to display the attendance percentage in the last column of the table using jProgressBar.




 1st column :- Reg No
 2nd column :- Conducted sessions
 3rdcolumn :- Attended sessions
 4thcolumn :- Percentage of the attendance 

  • First we need to prepare our data set to be added to the table. In this case the data is hard coded in the code itself(first yellow highlighted section ). You can use your own way to get the data to the method.
  • We can indicate the progress bar colors with different colors. To do that we have to first set the percentage ranges to the the progress bar(second highlighted section).

 Here is the code segment for do that task, you can customize this coding according to your requirement.

Code
public class IndicatorTableExample extends JPanel {
  private static final int MAX = 100;

  private static final int MIN = 0;

  public IndicatorTableExample() {
    setLayout(new BorderLayout());

    DefaultTableModel dm = new DefaultTableModel() {
      public Class getColumnClass(int col) {
        switch (col) {
        case 0:
          return String.class;
        case 1:
          return Integer.class;
        case 2:
          return Integer.class;
        default:
          return Object.class;
        }
      }

      public boolean isCellEditable(int row, int col) {
        switch (col) {
        case 2:
          return false;
        default:
          return true;
        }
      }

      public void setValueAt(Object obj, int row, int col) {
        if (col != 1) {
          super.setValueAt(obj, row, col);
          return;
        }
        try {
          Integer integer = new Integer(obj.toString());
          super.setValueAt(checkMinMax(integer), row, col);
        } catch (NumberFormatException ex) {
          ex.printStackTrace();
        }
      }
    };
    dm.setDataVector(new Object[][] {
        { "ITXXXXXX", 6, 6,precentage(6,6) },
        { "ITXXXXXX",
6, 5,precentage(6,5) },
        { "ITXXXXXX",
6, 4,precentage(6,4) },
        {
"ITXXXXXX", 6, 5,precentage(6,5) } },
        new Object[] { "Registration no", "
Conducted sessions ", "Attended sessions", "
Percentage" });

    JTable table = new JTable(dm);

    IndicatorCellRenderer renderer = new IndicatorCellRenderer(MIN, MAX);
    renderer.setStringPainted(true);
    renderer.setBackground(table.getBackground());

    // set limit value and fill color
    Hashtable limitColors = new Hashtable();
    limitColors.put(new Integer(0), Color.green);
    limitColors.put(new Integer(60), Color.yellow);
    limitColors.put(new Integer(80), Color.red);

    renderer.setLimits(limitColors);
    table.getColumnModel().getColumn(2).setCellRenderer(renderer);

    table.getModel().addTableModelListener(new TableModelListener() {
      public void tableChanged(TableModelEvent e) {
        if (e.getType() == TableModelEvent.UPDATE) {
          int col = e.getColumn();
          if (col == 1) {
            int row = e.getFirstRow();
            TableModel model = (TableModel) e.getSource();
            Integer value = (Integer) model.getValueAt(row, col);
            model.setValueAt(checkMinMax(value), row, ++col);
          }
        }
      }
    });

    JScrollPane pane = new JScrollPane(table);
    add(pane, BorderLayout.CENTER);
  }

  public static void main(String[] args) {
    JFrame f = new JFrame("IndicatorTable Example");
    f.getContentPane()
        .add(new IndicatorTableExample(), BorderLayout.CENTER);
    f.setSize(400, 120);
    f.setVisible(true);
    f.addWindowListener(new WindowAdapter() {
      public void windowClosing(WindowEvent e) {
        System.exit(0);
      }
    });
  }

  private Integer checkMinMax(Integer value) {
    int intValue = value.intValue();
    if (intValue < MIN) {
      intValue = MIN;
    } else if (MAX < intValue) {
      intValue = MAX;
    }
    return new Integer(intValue);
  }
}

/**
 * @version 1.0 03/03/99
 */

class IndicatorCellRenderer extends JProgressBar implements TableCellRenderer {
  private Hashtable limitColors;

  private int[] limitValues;

  public IndicatorCellRenderer() {
    super(JProgressBar.HORIZONTAL);
    setBorderPainted(false);
  }

  public IndicatorCellRenderer(int min, int max) {
    super(JProgressBar.HORIZONTAL, min, max);
    setBorderPainted(false);
  }

  public Component getTableCellRendererComponent(JTable table, Object value,
      boolean isSelected, boolean hasFocus, int row, int column) {
    int n = 0;
    if (!(value instanceof Number)) {
      String str;
      if (value instanceof String) {
        str = (String) value;
      } else {
        str = value.toString();
      }
      try {
        n = Integer.valueOf(str).intValue();
      } catch (NumberFormatException ex) {
      }
    } else {
      n = ((Number) value).intValue();
    }
    Color color = getColor(n);
    if (color != null) {
      setForeground(color);
    }
    setValue(n);
    return this;
  }

  public void setLimits(Hashtable limitColors) {
    this.limitColors = limitColors;
    int i = 0;
    int n = limitColors.size();
    limitValues = new int[n];
    Enumeration e = limitColors.keys();
    while (e.hasMoreElements()) {
      limitValues[i++] = ((Integer) e.nextElement()).intValue();
    }
    sort(limitValues);
  }

  private Color getColor(int value) {
    Color color = null;
    if (limitValues != null) {
      int i;
      for (i = 0; i < limitValues.length; i++) {
        if (limitValues[i] < value) {
          color = (Color) limitColors
              .get(new Integer(limitValues[i]));
        }
      }
    }
    return color;
  }

  private void sort(int[] a) {
    int n = a.length;
    for (int i = 0; i < n - 1; i++) {
      int k = i;
      for (int j = i + 1; j < n; j++) {
        if (a[j] < a[k]) {
          k = j;
        }
      }
      int tmp = a[i];
      a[i] = a[k];
      a[k] = tmp;
    }
  }
}