Modifying existing excel using jxl

Question

I m not able to edit the existing excel sheet using jxl. It always creates a new one. Can anyone please help me out with it. Please give a small sample code.

Solution 1

jxl is designed for increased read efficiency (since this is the primary use of the API). In order to improve performance, data which relates to output information (eg. all the formatting information such as fonts) is not interpreted when the spreadsheet is read, since this is superfluous when interrogating the raw data values.

However, if we need to modify this spreadsheet a handle to the various write interfaces is needed, which can be obtained using the copy method.

Workbook workbook = Workbook.getWorkbook(new File("myfile.xls"));
WritableWorkbook copy = Workbook.createWorkbook(new File("temp.xls"), workbook);

This copies the information that has already been read in as well as performing the additional processing to interpret the fields that are necessary to for writing spreadsheets. The disadvantage of this read-optimized strategy is that we have two spreadsheets held in memory rather than just one, thus doubling the memory requirements.

But after this, you can do whatever you want. Like:

WritableSheet sheet2 = copy.getSheet(1); 
WritableCell cell = sheet2.getWritableCell(1, 2); 

if (cell.getType() == CellType.LABEL) 
{ 
  Label l = (Label) cell; 
  l.setString("modified cell"); 
}
copy.write(); 
copy.close();
workbook.close();

Note: this is directly taken from Andy Khan's tutorial page.

Solution 2

I know that this is quite an old question, but if anyone will encounter the same problem, then to preserve the correct formatting (font type, colouring, etc. ) you should save the cell format before casting it to Label, and then force the cell to the previous formatting. Code:

CellFormat cfm = cell.getCellFormat();
Label l = (Label) cell; 
l.setString("modified cell");
cell.setCellFormat(cfm);

Comments

[Artjom B.]

Although sometimes it is ok to answer a question only with code, but most of the time the post is improved by adding an explanation. You can edit your answer to include one.

[Pushpendra]

Hi @Lalli i am using your answer but it's not working I am getting error on this line WritableWorkbook copy = Workbook.createWorkbook(new File("temp.xls"), workbook); ERROR is java.io.FileNotFoundException: temp.xls: open failed: EROFS (Read-only file system)