Writing a content in excel in incremental way

[Vibhas Kumar]

Hi friends i am facing one issue while writing the content in an excel sheet incrementally.When i try to add the next content in an existing excel sheet. I loose the previous one.Can any one help me to resolve this.Here is the code for this. I have created two java classes 1.ReadXLSheet -->to read the file 2.Create--> to write the content incrementally.

Kindly look into it and resolve this issue.

   package com.ExcelExample;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Locale;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import jxl.DateCell;


public class ReadXLSheet {
	private WritableCellFormat times;
	public void init(String filePath) {
		FileInputStream fs = null;
		try {
			fs = new FileInputStream(new File(filePath));
			contentReading(fs);
		} catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				fs.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	// Returns the Headings used inside the excel sheet
	public void getHeadingFromXlsFile(Sheet sheet) {
		int columnCount = sheet.getColumns();
		for (int i = 0; i < columnCount; i++) {
			System.out.println(sheet.getCell(i, 0).getContents());
		}
	}



	public void contentReading(InputStream fileInputStream)
			throws WriteException {
		WorkbookSettings ws = null;
		Workbook workbook = null;
		Sheet s = null;
		Cell rowData[] = null;
		int rowCount = '0';
		int columnCount = '0';
		DateCell dc = null;
		int totalSheet = 0;

		try {
			ws = new WorkbookSettings();
			ws.setLocale(new Locale("en", "EN"));
			workbook = Workbook.getWorkbook(fileInputStream, ws);

			totalSheet = workbook.getNumberOfSheets();
			if (totalSheet > 0) {
				System.out.println("Total Sheet Found:" + totalSheet);
				for (int j = 0; j < totalSheet; j++) {
					System.out.println("Sheet Name:"
							+ workbook.getSheet(j).getName());
				}
			}

		
			s = workbook.getSheet(0);

			// Reading Individual Cell
			getHeadingFromXlsFile(s);

			
			System.out.println("Total Rows inside Sheet:" + s.getRows());
			rowCount = s.getRows();
			
			
			create c = new create();
			c.writeContent(rowCount);
			// Total Total No Of Columns in Sheet
			System.out.println("Total Column inside Sheet:" + s.getColumns());
			columnCount = s.getColumns();

		
			workbook.close();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (BiffException e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		try {
			ReadXLSheet xlReader = new ReadXLSheet();
			xlReader.init("c:/temp/lars.xls");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
} 

*************************************************************************

  			
package com.ExcelExample;

import java.io.File;
import java.io.IOException;
import java.util.Locale;

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 create {

	private WritableCellFormat timesBoldUnderline;
	private WritableCellFormat times;
	private String inputFile;
	
public void setOutputFile(String inputFile) {
	this.inputFile = inputFile;
	}

	public void write(int rowCount) throws IOException, WriteException {
	
		File file = new File(inputFile);
		System.out.println("Inside write 1");
		WorkbookSettings wbSettings = new WorkbookSettings();
		
		wbSettings.setLocale(new Locale("en", "EN"));
		
		System.out.println("Inside write 2");
		
		
		WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
		
		System.out.println("Inside write 3");
		workbook.createSheet("Report", 0);
		System.out.println("Inside write 4");
		
		WritableSheet excelSheet = workbook.getSheet(0);
		System.out.println("Inside write 4");
		createLabel(excelSheet);
		
		System.out.println("Inside write 5");
		createContent(excelSheet,rowCount);
		System.out.println("Inside write 6");

		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, "Header 1");
		addCaption(sheet, 1, 0, "This is another header");
		

	}

	private void createContent(WritableSheet sheet, int rowCount) throws WriteException,
			RowsExceededException {
		// Now a bit of text
		for (int i = 1; i < rowCount; i++) {
			// First column
			addLabel(sheet, 0, i+rowCount, "Adding New text " );
			// Second column
			addLabel(sheet, 1, i+rowCount, "New Text text");
		}
	}

	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 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 void writeContent(int rowCount)  throws WriteException, IOException {
		create test = new create();
		test.setOutputFile("c:/temp/lars.xls");
		test.write(rowCount);
		System.out
				.println("Please check the result file under c:/temp/lars.xls ");
	}
}
  

Thanks in advance

[Rob Spoor]

Please mind your language. I've removed the offensive word from the subject.

[Greg Charles]

Hi Kumar,

You'll probably get better responses if you post less, more targeted code samples, and more information about what you're doing and what's going wrong.

In this case, I haven't worked with the jxl.Workbook class, but I see you create a new one each time you call the write() method, and pass it (I assume) the same file as a parameter. When you later call workbook.write(), I'm guessing it overwrites whatever was in the file with the new contents of the workbook. If that's not what you want to happen, you're going to have to go back to Workbook and see if it has another interface you can use.

Also, take a look at this code:

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

That's just confusing.

[Lester Burnham]

The javadocs of Workbook.createWorkbook(File,WorkbookSettings) talk of "creating" a file - which doesn't sound as if it would preserve an existing one. One of the other createWorkbook methods may be more suitable.