Example of Excel Number Format with jxl.jar

Q

Where can I find an example Java code that uses jxl.jar to add number cell format to a new Excel file?

✍: FYIcenter.com

A

You can follow these suggestions and example to add number cell format to a new Excel file with jxl.jar:

Number formatting information may be passed to the cell format object by a similar mechanism to that described for fonts.

A variety of predefined number formats are defined statically. These may be used to format numerical values as follows:

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

WritableCellFormat floatFormat = new WritableCellFormat (NumberFormats.FLOAT); 
Number number3 = new Number(1, 4, 3.141519, floatFormat); 
sheet.addCell(number3);

The above code inserts the value 3.14159 into cells A5 and B5, using the preset integer and floating points format respectively. When Excel renders these cells, A5 will display as "3" and B5 will display as "3.14", even though both cells contain the same floating point value.

It's possible for a user to define their own number formats, by passing in a number format string. The string passed in should be in the same format as that used by the java.text.DecimalFormat class. To format a number to display up to five decimal places in cell C5, the following code fragment may be used:

NumberFormat fivedps = new NumberFormat("#.#####"); 
WritableCellFormat fivedpsFormat = new WritableCellFormat(fivedps); 
Number number4 = new Number(2, 4, 3.141519, fivedpsFormat); 
sheet.addCell(number4);

It is, of course, also possible to specify font information as well eg. to display the same value in the 16 point times bold font defined earlier we can write

WritableCellFormat fivedpsFontFormat = new WritableCellFormat (times16font, fivedps); 
Number number5 = new Number(3, 4, 3.141519, fivedpsFontFormat); 
sheet.addCell(number5);

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

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

// Example of adding formatting properties for Numbers in a new Excel file
public class JxlAddNumberFormat {
   public static void main(String [] args) 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 number2 = new Number(0, 4, 3.141519, integerFormat); 
sheet.addCell(number2); 

WritableCellFormat floatFormat = new WritableCellFormat (NumberFormats.FLOAT); 
Number number3 = new Number(1, 4, 3.141519, floatFormat); 
sheet.addCell(number3);

NumberFormat fivedps = new NumberFormat("#.#####"); 
WritableCellFormat fivedpsFormat = new WritableCellFormat(fivedps); 
Number number4 = new Number(2, 4, 3.141519, fivedpsFormat); 
sheet.addCell(number4);

// Create a cell format for Times 16, bold and italic 
WritableFont times16font = new WritableFont(WritableFont.TIMES, 16, WritableFont.BOLD, true); 
WritableCellFormat times16format = new WritableCellFormat (times16font); 

WritableCellFormat fivedpsFontFormat = new WritableCellFormat (times16font, fivedps); 
Number number5 = new Number(3, 4, 3.141519, fivedpsFontFormat); 
sheet.addCell(number5);

      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 JxlAddNumberFormat.java

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

The output file, output.xls, is ready for Microsoft Excel to open.

 

Java Source Code Example for jxl.jar

⇒⇒FAQ for Java Excel API jxl.jar

2018-02-28, 517👍, 0💬