JExcel Reada data from Excel sheet

Java Excel API is a java API enabling developers to read, write, and modify Excel spreadsheets dynamically. Any operating system which can run a Java virtual machine can both process and deliver Excel spreadsheets. One nice thing about JExcelApi is that it has no dependencies on any third party libraries.

The following example will explains reading data from excel sheet dynamically.

For this application you need one jar file
That is : jxl.jar

JExcelWriteExample.java

/**
 * File Name         :  JExcelReadExample.java
 * Created By        :  Nagaraju V
 * Created Date      :  Jun 19, 2009
 * Purpose           :  Reading data from excel cells.  
 */
package com.raj.jexcel;

import java.io.File;

import jxl.Sheet;
import jxl.Workbook;

/**
 * @author nagarajuv
 *
 */
public class JExcelReadExample {

 /**
  * @param args
  */
 public static void main(String[] args) {
  
  //Reading Data from ExcelSheet
       String file = "employee_xls.xls";
       File fp = new File(file);
       try {
           Workbook wb = Workbook.getWorkbook(fp);
           Sheet sheet = wb.getSheet(0);
           int columns = sheet.getColumns();
           int rows = sheet.getRows();
           
           System.out.println("No of Columns-->"+columns);
           System.out.println("No of rows-->"+rows);
           
           String data;
           for(int row = 0;row < rows;row++) {
               for(int col = 0;col < columns;col++) {
                   data = sheet.getCell(col, row).getContents();
                   if(col>0)
                   System.out.print(", ");
                   System.out.print(data);
               }
               System.out.println("");
           }
      }catch(Exception ioe) {
           System.out.println("Error: " + ioe);
      }
 }
}

Enjoy with this is nice jxlread example

JExcel Write Data to excel sheet

Java Excel API is a java API enabling developers to read, write, and modify Excel spreadsheets dynamically. Any operating system which can run a Java virtual machine can both process and deliver Excel spreadsheets. One nice thing about JExcelApi is that it has no dependencies on any third party libraries.

The following example will explains writing data and image into excel sheet dynamically.

For this application you need one jar file
That is : jxl.jar

JExcelReadExample.java

/**
 * File Name         :  JExcelWriteExample.java
 * Created By        :  NagarajuV
 * Created Date      :  Jun 12, 2009
 * Purpose           :  Writing data to excel cells.
 */
package com.raj.jexcel;

import java.io.*;
import jxl.*;
import java.util.*;
import jxl.Workbook;
import jxl.write.Number;
import jxl.write.*;
/**
 * @author nagarajuv
 *
 */

class JExcelWriteExample {
 public static void main(String[] args) {
  try {
   // Writing Data to ExcelSheet
   String filename = "input1.xls";
   WorkbookSettings ws = new WorkbookSettings();
   ws.setLocale(new Locale("en", "EN"));
   WritableWorkbook workbook = Workbook.createWorkbook(new File(
     filename), ws);
   WritableSheet s = workbook.createSheet("DataSheet", 0);
   WritableSheet s1 = workbook.createSheet("ImageSheet", 0);
   writeDataSheet(s);
   writeImageSheet(s1);
   workbook.write();
   workbook.close();
  } catch (IOException e) {
   e.printStackTrace();
  } catch (WriteException e) {
   e.printStackTrace();
  }
 }

 private static void writeDataSheet(WritableSheet s) throws WriteException {

  // Writing Data to ExcelSheet
  System.out.println("In writeDataSheet() Method");
  /* Format the Font */
  WritableFont wf = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD);
  WritableCellFormat cf = new WritableCellFormat(wf);
  cf.setWrap(true);

  /* Creates Label and writes date to one cell of sheet */
  Label l = new Label(0, 0, "Date", cf);
  s.addCell(l);
  WritableCellFormat cf1 = new WritableCellFormat(DateFormats.FORMAT9);

  DateTime dt = new DateTime(0, 1, new Date(), DateTime.GMT);

  s.addCell(dt);

  /* Creates Label and writes float number to one cell of sheet */
  l = new Label(2, 0, "Float", cf);
  s.addCell(l);
  WritableCellFormat cf2 = new WritableCellFormat(NumberFormats.FLOAT);
  Number n = new Number(2, 1, 3.1415926535, cf2);
  s.addCell(n);

  n = new Number(2, 2, -3.1415926535, cf2);
  s.addCell(n);

  /*
   * Creates Label and writes float number upto 3 decimal to one cell of
   * sheet
   */
  l = new Label(3, 0, "3dps", cf);
  s.addCell(l);
  NumberFormat dp3 = new NumberFormat("#.###");
  WritableCellFormat dp3cell = new WritableCellFormat(dp3);
  n = new Number(3, 1, 3.1415926535, dp3cell);
  s.addCell(n);

  /* Creates Label and adds 2 cells of sheet */
  l = new Label(4, 0, "Add 2 cells", cf);
  s.addCell(l);
  n = new Number(4, 1, 10);
  s.addCell(n);
  n = new Number(4, 2, 16);
  s.addCell(n);
  Formula f = new Formula(4, 3, "E2+E3");
  s.addCell(f);

  /* Creates Label and multipies value of one cell of sheet by 2 */
  l = new Label(5, 0, "Multipy by 2", cf);
  s.addCell(l);
  n = new Number(5, 1, 10);
  s.addCell(n);
  f = new Formula(5, 2, "F2 * 3");
  s.addCell(f);

  /* Creates Label and divide value of one cell of sheet by 2.5 */
  l = new Label(6, 0, "Divide", cf);
  s.addCell(l);
  n = new Number(6, 1, 12);
  s.addCell(n);
  f = new Formula(6, 2, "F2/2.5");
  s.addCell(f);

  /*-----------------------------------------------------------*/
  /* Format the Font */
  WritableFont wf3 = new WritableFont(WritableFont.TIMES, 10,
    WritableFont.BOLD);
  WritableCellFormat cf3 = new WritableCellFormat(wf3);
  cf3.setWrap(true);

  l = new Label(2, 8, "Nagesh", cf3);
  s.addCell(l);

  l = new Label(1, 9, "Water", cf3);
  s.addCell(l);
  n = new Number(2, 9, 35);
  s.addCell(n);

  l = new Label(1, 10, "Elictricity", cf3);
  s.addCell(l);
  n = new Number(2, 10, 57);
  s.addCell(n);

  l = new Label(1, 11, "Rent", cf3);
  s.addCell(l);
  n = new Number(2, 11, 750);
  s.addCell(n);

  f = new Formula(2, 13, "C10+C11+C12");
  s.addCell(f);
 }

 private static void writeImageSheet(WritableSheet s) throws WriteException {
  // Writing Image to ExcelSheet
  System.out.println("In writeImageSheet() Method");

  /* Creates Label and writes image to one cell of sheet*/
  Label l = new Label(0, 0, "Image");
  s.addCell(l);
  WritableImage wi = new WritableImage(0, 3, 5, 10, new File("images/wp_1280.png"));
  s.addImage(wi);

  /* Creates Label and writes hyperlink to one cell of sheet*/
  l = new Label(0, 15, "HYPERLINK");
  s.addCell(l);
  Formula f = new Formula(1, 15,
    "HYPERLINK(\"http://www.andykhan.com/jexcelapi\", "
      + "\"JExcelApi Home Page\")");
  s.addCell(f);
 }
}

Enjoy with this is nice jxlwrite example