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

Question

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.

Solution 1

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

Solution 2

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

Solution 3

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!

Comments

[Jaison]

It works until you add a data filter in the file.Pls Check if it works after adding a data filter in the file.

[Jaison]

This solution works fine if you want to write a new cell. But my file already contain values and I want to insert new column. that means all the info that is currently present in file should shift to the next column.

[Ishank]

the code is useful when u wanna write cells to an already existing excel file.Ur problem sounds like u just have to first read the already existing rows-take the data in the last row-insert the new row using the code above and populate the new cell with the last cell's data and then write the last cells data to the last row, so, <b>all u need is to add some more logic to the above code..</b>