Example in java to read and write an excel sheet

How to read and write an excel sheet in java

/**
* Developed for ..... Account Section (For ...),
*/

package reader;

import java.io.File;

import java.io.IOException;

import java.security.acl.LastOwnerException;

import java.util.Vector;

import writer.WriteExcel;

import jxl.Cell;

import jxl.CellType;

import jxl.Sheet;

import jxl.Workbook;

import jxl.read.biff.BiffException;

import jxl.write.WriteException;

public class ReadExcel {

  private String inputFile;

  private double openingBalDL =130.95;

  private double openingBalCL =0.00; // Credit Last

  private double openingBalDN =0.00; // Debit New

  private double openingBalCN =0.00;

  private double allDebits = 0.00;

  private double allCredits = 0.00;

  private String dateLast = "4/3/12";

  private String dateNew = null;

  private Vector array = new Vector();

  public void setInputFile(String inputFile) {
    this.inputFile = inputFile;
  }

  public void read() throws IOException  {
    File inputWorkbook = new File(inputFile);
    Workbook w;
    try {
      w = Workbook.getWorkbook(inputWorkbook);
      // Get the first sheet
      Sheet sheet = w.getSheet(0);
      // Loop over first 10 column and lines
      System.out.println(" no of rows "+sheet.getRows());
      System.out.println(" no of col "+sheet.getColumns());
      int totCols = sheet.getColumns();
      int totRows = sheet.getRows();
      Cell cell = sheet.getCell(0, 1);  // col,row
      System.out.println(cell.getContents());
      if(cell.getContents().equalsIgnoreCase("Opening Balance B/D")){
          System.out.println(" --------------- true ---------------- ");
      }
    
              double debit = 0.00;
              double credit = 0.00;
              double openingBalDb = 130.95;
              double openingBalCr = 0.00;
              double balanceTotDb = 0.00;
              double balanceTotCr = 0.00;
              double closingBalCr = 0.00;
              double closingBalDb = 0.00;
              Vector value = null;
          for(int row =1; row               value = new Vector();
              Cell dateCell = sheet.getCell(0, row);
              Cell refCell = sheet.getCell(1, row);
              Cell partCell = sheet.getCell(2, row);
              Cell debCell = sheet.getCell(3, row);
              Cell crdCell = sheet.getCell(4, row);
              value.add(dateCell.getContents());
              value.add(refCell.getContents());
              value.add(partCell.getContents());
              if(!dateCell.getContents().equalsIgnoreCase("")){  // date ( all debit, credit0
                   debit = debit + Double.parseDouble(debCell.getContents());
                   credit = credit + Double.parseDouble(crdCell.getContents());
                   value.add(debCell.getContents());
                   value.add(crdCell.getContents());
                   System.out.println(" Row : "+row + " Debit : "+debit + " Credit : "+credit);
              } else{ // Balance total , closing balance , grant total, Opeining bala
                      if(partCell.getContents().equalsIgnoreCase("Balance Total")){
                          balanceTotDb = openingBalDb + debit;
                          balanceTotCr = credit;
                          System.out.println(" Balance Total Db : "+balanceTotDb + " Balance Total Cr :"+balanceTotCr);
                          openingBalDb = balanceTotDb;
                          value.add(balanceTotDb);
                          value.add(balanceTotCr);
                      }
                      if(partCell.getContents().equalsIgnoreCase("Closing Balance C/D")){
                          closingBalCr = 0.00;
                          closingBalDb = 0.00;
                        
                          if(balanceTotDb >= balanceTotCr){ //+ credit
                              closingBalCr = balanceTotDb - balanceTotCr;
                          }else{ // - debit
                              closingBalDb = balanceTotCr - balanceTotDb;
                          }
                          System.out.println(" Closing Balance Db "+closingBalDb + " Closing Balance Cr "+closingBalCr);
                          value.add(closingBalDb);
                          value.add(closingBalCr);
                      }if(partCell.getContents().equalsIgnoreCase("Grand Total")){
                          double grantTotalDb = balanceTotDb + closingBalDb;
                          double grantTotalCr = balanceTotCr + closingBalCr;
                          System.out.println(" Grant Total Db : "+grantTotalDb + " Grant Total Cr : "+grantTotalCr);
                          value.add(grantTotalDb);
                          value.add(grantTotalCr);
                      }
                      if(partCell.getContents().equalsIgnoreCase("Opening Balance B/D")){
                          double opBalDb = closingBalCr;
                          double opBalCr = closingBalDb;
                          System.out.println(" Opening Balance Db : " + opBalDb + " Opening Balance Cr : "+opBalCr);
                          value.add(opBalDb);
                          value.add(opBalCr);
                          debit = 0.00;
                        credit = 0.00;  
                        balanceTotDb = 0.00;
                        balanceTotCr = 0.00;
                        closingBalDb = 0.00;
                        closingBalCr = 0.00;
                      }
              }
              array.add(value);
              //Cell cell = sheet.getCell(col, row);
             // System.out.println(cell.getContents());
          }
  
       System.out.println(" array size "+array.size());
       WriteExcel test = new WriteExcel();
       test.setOutputFile("D:/Nijesh/Report.xls",array);
       try {
        test.write();
    } catch (WriteException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
 
    } catch (BiffException e) {
      e.printStackTrace();
    }
  }

  public static void main(String[] args) throws IOException {
    ReadExcel test = new ReadExcel();
    test.setInputFile("D:/Jerin/Ultron/CashBook.xls");
    test.read();
  }

}

-----------------------

package writer;

import java.io.File;

import java.io.IOException;

import java.util.Locale;

import java.util.Vector;

import jxl.CellView;

import jxl.Workbook;

import jxl.WorkbookSettings;

import jxl.format.UnderlineStyle;

import jxl.write.Formula;

import jxl.write.Label;

import jxl.write.Number;

import jxl.write.WritableCellFormat;

import jxl.write.WritableFont;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

import jxl.write.WriteException;

import jxl.write.biff.RowsExceededException;

public class WriteExcel {

  private WritableCellFormat timesBoldUnderline;

  private WritableCellFormat times;

  private String inputFile;

  private Vector array = null;


  public void setOutputFile(String inputFile) {
      this.inputFile = inputFile;
  }

  public void setOutputFile(String inputFile, Vector array) {
      this.inputFile = inputFile;
      this.array = array;  
    }

  public void write() throws IOException, WriteException {
    File file = new File(inputFile);
    WorkbookSettings wbSettings = new WorkbookSettings();

    wbSettings.setLocale(new Locale("en", "EN"));

    WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
    workbook.createSheet("Report", 0);
    WritableSheet excelSheet = workbook.getSheet(0);
    createLabel(excelSheet);
    createContent(excelSheet);

    workbook.write();
    workbook.close();
  }

  private void createLabel(WritableSheet sheet)
      throws WriteException {
    // Lets create a times font
    WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
    // Define the cell format
    times = new WritableCellFormat(times10pt);
    // Lets automatically wrap the cells
    times.setWrap(true);

    // Create create a bold font with unterlines
    WritableFont times10ptBoldUnderline = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD, false,
        UnderlineStyle.SINGLE);
    timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline);
    // Lets automatically wrap the cells
    timesBoldUnderline.setWrap(true);

    CellView cv = new CellView();
    cv.setFormat(times);
    cv.setFormat(timesBoldUnderline);
    cv.setAutosize(true);

    // Write a few headers
    addCaption(sheet, 0, 0, "Date");
    addCaption(sheet, 1, 0, "Reference");
    addCaption(sheet, 2, 0, "Particulars");
    addCaption(sheet, 3, 0, "Debit");
    addCaption(sheet, 4, 0, "Credit");
  

  }

  private void createContent(WritableSheet sheet) throws WriteException,
      RowsExceededException {
    
      int size = array.size();
    
      for(int row = 0; row < size; row ++){
          Vector val = array.get(row);
          for(int col =0; col<5 data-blogger-escaped-br="" data-blogger-escaped-col="">              addValues(sheet,col, row, val.get(col));
          }
        }
    
    
    
    
      /*
    for (int i = 1; i < 10; i++) {
      // First column
      addNumber(sheet, 0, i, i + 10);
      // Second column
      addNumber(sheet, 1, i, i * i);
    }
    */

  }

  private void addCaption(WritableSheet sheet, int column, int row, String s)
      throws RowsExceededException, WriteException {
    Label label;
    label = new Label(column, row, s, timesBoldUnderline);
    sheet.addCell(label);
  }

  private void addNumber(WritableSheet sheet, int column, int row,
      Integer integer    ) throws WriteException, RowsExceededException {
  
    Number number;
    number = new Number(column, row, integer, times);
    sheet.addCell(number);
  
  
  }

  private void addValues(WritableSheet sheet, int column, int row, Object obj)
          throws WriteException, RowsExceededException {
        Label label;
        label = new Label(column, row, obj.toString(), times);
        sheet.addCell(label);
      }

  private void addLabel(WritableSheet sheet, int column, int row, String s)
      throws WriteException, RowsExceededException {
    Label label;
    label = new Label(column, row, s, times);
    sheet.addCell(label);
  }

  public static void main(String[] args) throws WriteException, IOException {
    WriteExcel test = new WriteExcel();
    test.setOutputFile("D:/Akshara/Report.xls");
    test.write();
    System.out
        .println("Please check the result file under E:/Bithesh/consumerfed.xls ");
  }


}

--------------

You have to download jxl.jar to download this apps

The above program calculate balance total, closing balance, opening balance, grant total from given ledger entries in excels sheet...



Read : How to copy all images (resource) from ms word document?