How to read and write excel file

Question

I want to read and write an Excel file from Java with 3 columns and N rows, printing one string in each cell. Can anyone give me simple code snippet for this? Do I need to use any external lib or does Java have built-in support for it?

I want to do the following:

for(i=0; i <rows; i++)
     //read [i,col1] ,[i,col2], [i,col3]

for(i=0; i<rows; i++)
    //write [i,col1], [i,col2], [i,col3]

Answer 1

There is a new easy and very cool tool (10x to Kfir): xcelite

Write:

public class User { 

  @Column (name="Firstname")
  private String firstName;

  @Column (name="Lastname")
  private String lastName;

  @Column
  private long id; 

  @Column
  private Date birthDate; 
}

Xcelite xcelite = new Xcelite();    
XceliteSheet sheet = xcelite.createSheet("users");
SheetWriter<User> writer = sheet.getBeanWriter(User.class);
List<User> users = new ArrayList<User>();
// ...fill up users
writer.write(users); 
xcelite.write(new File("users_doc.xlsx"));

Read:

Xcelite xcelite = new Xcelite(new File("users_doc.xlsx"));
XceliteSheet sheet = xcelite.getSheet("users");
SheetReader<User> reader = sheet.getBeanReader(User.class);
Collection<User> users = reader.read();

Answer 2

For reading data from .xlsx workbooks we need to use XSSFworkbook classes.

XSSFWorkbook xlsxBook = new XSSFWorkbook(fis);

XSSFSheet sheet = xlsxBook.getSheetAt(0); etc.

We need to use Apache-poi 3.9 @ http://poi.apache.org/

For detailed info with example visit : http://java-recent.blogspot.in

Answer 3

Please use Apache POI libs and try this.

public class TakingDataFromExcel {
   public static void main(String[] args) {
    try
    {
        FileInputStream x = new FileInputStream(new File("/Users/rajesh/Documents/rajesh.xls"));

        //Create Workbook instance holding reference to .xlsx file
        Workbook workbook = new HSSFWorkbook(x);

        //Get first/desired sheet from the workbook
        Sheet sheet = workbook.getSheetAt(0);

        //Iterate through each rows one by one
        for (Iterator<Row> iterator = sheet.iterator(); iterator.hasNext();) {
            Row row = (Row) iterator.next();
            for (Iterator<Cell> iterator2 = row.iterator(); iterator2
                    .hasNext();) {
                Cell cell = (Cell) iterator2.next();
                System.out.println(cell.getStringCellValue());              
            }               
        }         
        x.close();
    }
    catch (Exception e)
    {
        e.printStackTrace();
    }
   }
}

Answer 4

.csv or POI will certainly do it, but you should be aware of Andy Khan's JExcel. I think it's by far the best Java library for working with Excel there is.

Answer 5

I edited the most voted one a little cuz it didn't count blanks columns or rows well not totally, so here is my code i tested it and now can get any cell in any part of an excel file. also now u can have blanks columns between filled column and it will read them

  try {
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(Dir));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row;
HSSFCell cell;

int rows; // No of rows
rows = sheet.getPhysicalNumberOfRows();

int cols = 0; // No of columns
int tmp = 0;
int cblacks=0;

// This trick ensures that we get the data properly even if it doesn't start from first few rows
for(int i = 0; i <= 10 || i <= rows; i++) {
    row = sheet.getRow(i);
    if(row != null) {
        tmp = sheet.getRow(i).getPhysicalNumberOfCells();
        if(tmp >= cols) cols = tmp;else{rows++;cblacks++;}
    }

    cols++;
}
cols=cols+cblacks;
for(int r = 0; r < rows; r++) {
    row = sheet.getRow(r);
    if(row != null) {
        for(int c = 0; c < cols; c++) {
            cell = row.getCell(c);
            if(cell != null) {
                System.out.print(cell+"\n");//Your Code here
            }
        }
    }
}} catch(Exception ioe) {
ioe.printStackTrace();}

Answer 6

Try the Apache POI HSSF. Here's an example on how to read an excel file:

try {
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow row;
    HSSFCell cell;

    int rows; // No of rows
    rows = sheet.getPhysicalNumberOfRows();

    int cols = 0; // No of columns
    int tmp = 0;

    // This trick ensures that we get the data properly even if it doesn't start from first few rows
    for(int i = 0; i < 10 || i < rows; i++) {
        row = sheet.getRow(i);
        if(row != null) {
            tmp = sheet.getRow(i).getPhysicalNumberOfCells();
            if(tmp > cols) cols = tmp;
        }
    }

    for(int r = 0; r < rows; r++) {
        row = sheet.getRow(r);
        if(row != null) {
            for(int c = 0; c < cols; c++) {
                cell = row.getCell((short)c);
                if(cell != null) {
                    // Your code here
                }
            }
        }
    }
} catch(Exception ioe) {
    ioe.printStackTrace();
}

On the documentation page you also have examples of how to write to excel files.