Where can I find an example Java code that uses jxl.jar to read an existing Excel file and modify it?

This tutorial describes the scenario where a spreadsheet is read in, it's contents altered in some way and the modified spreadsheet written out.

The first stage is to read in the spreadsheet in the normal way:

Workbook workbook = Workbook.getWorkbook(new File("myfile.xls"));

This creates a readable spreadsheet. To obtain a writable version of this spreadsheet, a copy must be made, as follows:

WritableWorkbook copy = Workbook.createWorkbook(new File("output.xls"), workbook);

The API functions this way is for reasons of 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 above. 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. For this reason copying and modifying large spreadsheets can be expensive in terms of processing and memory.

Once we have a writable interface to the workbook, we may retrieve and modify cells. The following code fragment illustrates how to modify the contents of a label cell located in cell B3 in sheet 2 of the workbook.

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

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

There is no need to call the add() method on the sheet, since the cell is already present on the sheet. The contents of numerical and date cells may be modified in a similar way, by using the setValue() and setDate() methods respectively.

Although cell formats are immutable, the contents of a cell may be displayed differently by assigning a different format object to the cell. The following code fragment illustrates changing the format of numerical cell (in position C5) so that the contents will be displayed to five decimal places.

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

NumberFormat fivedps = new NumberFormat("#.#####"); 
WritableCellFormat cellFormat = new WritableCellFormat(fivedps); 
cell.setFormat(cellFormat);

Since the copy of the workbook is an ordinary writable workbook, new cells may be added to the sheet, thus:

Label label = new Label(0, 2, "New label record"); 
sheet2.addCell(label); 

Number number = new Number(3, 4, 3.1459); 
sheet2.addCell(number);

Here is the complete source code of example program JxlReadAndWrite.java:

// Copyright (c) FYIcenter.com
import java.io.*;
import java.util.*;
import jxl.*; 
import jxl.write.*;
import jxl.write.Number;

// Example of reading data from an existing Excel file and write to another
public class JxlReadAndWrite {
   public static void main(String [] args) throws Exception {
    create();

    Workbook workbook = Workbook.getWorkbook(new File("output.xls")); 
    WritableWorkbook copy = Workbook.createWorkbook(new File("modified.xls"), workbook);
      WritableSheet sheet = copy.getSheet(0); 

      WritableCell cell = sheet.getWritableCell(0,4); 
      modifyData(cell);
    
      cell = sheet.getWritableCell(0,6); 
      modifyData(cell);

      copy.write(); 
      copy.close();
   }

   public static void modifyData(WritableCell cell) throws Exception {
      if (cell.getType() == CellType.LABEL) { 
         Label l = (Label) cell; 
         l.setString("Modified label");
      } else if (cell.getType() == CellType.NUMBER) { 
         Number n = (Number) cell; 
         n.setValue(2.71828);
      } else if (cell.getType() == CellType.DATE) { 
         DateTime d = (DateTime) cell; 
         d.setDate(new Date(1000*60*60*24));
      } else {
         System.out.println("Other data... ");
    }
   }

// Create a new Excel file 
   public static void create() throws Exception {
    jxl.write.WritableWorkbook workbook 
       = jxl.Workbook.createWorkbook(new java.io.File("output.xls"));
      jxl.write.WritableSheet sheet = workbook.createSheet("First Sheet", 0);

WritableCellFormat integerFormat = new WritableCellFormat (NumberFormats.INTEGER); 
Number numberA5 = new Number(0, 4, 3.141519, integerFormat); 
sheet.addCell(numberA5); 

Date now = new Date(0);
DateFormat customDateFormat = new DateFormat ("dd MMM yyyy hh:mm:ss"); 
WritableCellFormat dateFormat = new WritableCellFormat (customDateFormat); 
DateTime dateA7 = new DateTime(0, 6, now, dateFormat); 
sheet.addCell(dateA7); 

      workbook.write(); 
      workbook.close();
   }
}

You can compile and run the above example in a command window as shown below:

C:\fyicenter>c:\local\jdk-1.8.0\bin\javac 
   -cp .;\fyicenter\jexcelapi\jxl.jar JxlReadAndWrite.java

C:\fyicenter>c:\local\jdk-1.8.0\bin\java 
   -cp .;\fyicenter\jexcelapi\jxl.jar JxlReadAndWrite
Number data: 3.141519
Date data: Wed Dec 31 14:00:00 EST 1969

The above example creates Excel file, output.xsl, then reads back it and makes a copy with modification as modified.xsl.

⇒ FAQ for Java Excel API jxl.jar

⇐ Example of Read Excel Cell Type with jxl.jar

⇑ Java Source Code Example for jxl.jar

⇑⇑ FAQ for Java Excel API jxl.jar