Example of Read Excel Cell Type with jxl.jar

Q

Where can I find an example Java code that uses jxl.jar to read cell types of an existing Excel file?

✍: FYIcenter.com

A

You can follow these suggestions and example to read cell types of an existing Excel file with jxl.jar:

If it is required to access the cell's contents as the exact type ie. as a numerical value or as a date, then the retrieved Cell must be cast to the correct type and the appropriate methods called. The section of code below illustrates how JExcelApi may be used to retrieve a genuine java double and java.util.Date object from an Excel spreadsheet. For completeness the label is also cast to it's correct type, although in practice this makes no difference. The example also illustrates how to verify that cell is of the expected type - this can be useful when validating that the spreadsheet has cells in the correct place.

String stringa1 = null; 
double numberb2 = 0; 
Date datec2 = null; 

Cell a1 = sheet.getCell(0,0); 
Cell b2 = sheet.getCell(1,1); 
Cell c2 = sheet.getCell(2,1); 

if (a1.getType() == CellType.LABEL) { 
  LabelCell lc = (LabelCell) a1; 
  stringa1 = lc.getString(); 
} 

if (b2.getType() == CellType.NUMBER) { 
  NumberCell nc = (NumberCell) b2; 
  numberb2 = nc.getValue(); 
} 

if (c2.getType() == CellType.DATE) { 
  DateCell dc = (DateCell) c2; 
  datec2 = dc.getDate(); 
} 

// Do stuff with dates and doubles 
...

When you have finished processing all the cells, use the close() method. This frees up any allocated memory used when reading spreadsheets and is particularly important when reading large spreadsheets.

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

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

// Example of reading data type from an existing Excel file
public class JxlReadDataType {
   public static void main(String [] args) throws Exception {
	  create();

	  jxl.Workbook workbook = jxl.Workbook.getWorkbook(new File("output.xls")); 
      jxl.Sheet sheet = workbook.getSheet(0);
  
      Cell cell = sheet.getCell(0,4); 
      printData(cell);
	  
      cell = sheet.getCell(0,6); 
      printData(cell);

      workbook.close();
   }

   public static void printData(Cell cell) throws Exception {
      if (cell.getType() == CellType.LABEL) { 
         LabelCell lc = (LabelCell) cell; 
         String stringData = lc.getString(); 
         System.out.println("String data: "+stringData);
      } else if (cell.getType() == CellType.NUMBER) { 
         NumberCell nc = (NumberCell) cell; 
         double numberData = nc.getValue(); 
         System.out.println("Number data: "+numberData);
      } else if (cell.getType() == CellType.DATE) { 
         DateCell dc = (DateCell) cell; 
         Date dateData = dc.getDate(); 
         System.out.println("Date data: "+dateData);
      } 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 JxlReadDataType.java

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

The above example creates Excel file, output.xsl, then reads cell data type and value back from it.

 

Java Source Code Example for jxl.jar

⇒⇒FAQ for Java Excel API jxl.jar

2018-02-28, 349👍, 0💬