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?
