CODEWITHSUNDEEP
javajxl
Jaison
Jaison

Reputation: 132

Inserting new column to an already existing excel file using jxl api

I'm using jxl api for editing an existing excel file. But when i try to add a new column and write the sheet its showing null pointer exception. The code that I'm using is as shown below :

File file = new File("d:\\test.xls");
Workbook workbook;
WritableWorkbook copy = null;
if (file.exists()) {

    try {
        workbook = Workbook.getWorkbook(file);
        copy = Workbook.createWorkbook(new File("C:\\TEMP\\temp.xls"),
                workbook);
    } catch (BiffException e) {

        e.printStackTrace();
    } catch (FileNotFoundException fnf) {
        fnf.printStackTrace();

    } catch (IOException e) {

        e.printStackTrace();
    }

}   
WritableSheet sheet = copy.getSheet(0);

sheet.insertColumn(2); //this statement causes error  
                      //if I comment it the code works fine

try {
    copy.write();
    copy.close();
}
catch(Exception e)
{

    e.printStackTrace();
}

Please help me to solve this problem and insert new column .

I'm able to edit the single cells of excel successfully and write the file.

Upvotes: 3

Views: 13576

Answers (4)

Lukas
Lukas

Reputation: 402

It is not possible with jxl, you must use apache poi http://poi.apache.org/ (or something else if there is anything else)

With jxl you can only read or write workbooks, not edit existing ones. You can also copy workbook and edit newly created. WritableWorkbook newWorkbook = Workbook.createWorkbook(newFile, workbookTemplate, wbSettings); But you will lose a lot of formating ald all AutoFilters, which was mine problem when I was useing jxl.

Great examples are here http://poi.apache.org/spreadsheet/quick-guide.html http://www.kodejava.org/browse/49.html

Upvotes: 0

PowR
PowR

Reputation: 36

Probably the api you are using is not the best one. I also had this problem (nullpointer exception at the insertcolumn line) and couldnt find any solution until I downloaded jexcelapi.

HTH

Upvotes: 2

Ishank
Ishank

Reputation: 2926

We can insert a new cell and thus a new row/column like this-

Workbook aWorkBook = Workbook.getWorkbook(new File("Originalfile.xls"));
        WritableWorkbook aCopy = Workbook.createWorkbook(new File("Originalfile.xls"), aWorkBook);
        WritableSheet aCopySheet = aCopy.getSheet(0);//index of the needed sheet
        WritableCell aWritableCell = aCopySheet.getWritableCell(1,1);//no need!
        jxl.write.Label anotherWritableCell =  new jxl.write.Label(1,12 ,"SUN");
                //position of the new cell in column,row
            //can be a new Label() or new Number() or new Formula

            aCopySheet.addCell(anotherWritableCell);

        aCopy.write();
        aCopy.close();

I am not clear on what the insertRow() or insertColumn() methods do. Hope it helps!

Upvotes: 1

Mark Yao
Mark Yao

Reputation: 408

The above code can run fine in my computer. you'd better to tell us the exception information and put the whole code here.

Upvotes: 2

Related Questions