Warning: Cannot read drop down range Unrecognized token 43 Exception


If you get the exception mentioned while using the JExcel API for reading the content of Microsoft Files;

Warning: Cannot read drop down range Unrecognized token 43 Exception

Try to add the following lines of code before getting the first sheet of the Excel Document.

//Warning:  Cannot read drop down range Unrecognized token 43
WorkbookSettings workbooksetting = new WorkbookSettings();
workbooksetting.setCellValidationDisabled(true);
Workbook workbook = Workbook.getWorkbook(new File("C:\\yourexcelfile.xls"),workbooksetting);

However, I think this is a temporary solution for the problem, it is better if you take care of the Excel document’s format before reading it using the JExcel API.

Advertisements

Reading Microsoft Excel file using JExcel API Java


By using JExcel API, Microsoft Excel files could be read easily. You can find the simple solution below;

First download the latest JExcel Api jar from;
Download from here And put the jxl.jar file in the project’s lib folder.

Here you can see How Excel file looks;

Here is  the reader class for reading Excel files;

package excelreader;

import java.io.File;
import java.util.Locale;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;

/**
*
* @author tunatore
*/
public class ReadExcel {

public static void main(String[] args) throws Exception{

WorkbookSettings workbookSettings = new WorkbookSettings();
workbookSettings.setLocale(new Locale(“en”, “EN”));

File file = new File(ReadExcel.class.getResource(“jxlrwtest.xls“).getFile().replace(“%20″, ” “));
//set the file location for the .xls file
Workbook workbook = Workbook.getWorkbook(file, workbookSettings);
//create workbook object by the settings

Sheet customerSheet = workbook.getSheet(0); //getting the first sheet inside excel document
ReadExcel.readSheet(customerSheet); //start reading Excel document
workbook.close(); // free the memory by closing workbook

}

private static void readSheet(Sheet sheet) throws Exception {

//sheet.getCell(columnIndex,rowIndex)
for (int i = 0; i < sheet.getRows(); i++) { //getting all rows inside excel document
//start looping over rows
System.out.printf(“%-25s %-25s %-25s %-25s %n”,
//format the system output (left aligned)
sheet.getCell(0,i).getContents(),
//reading the first column’s index based row (row[i]) value
sheet.getCell(1,i).getContents(),
//reading the second column’s index based row (row[i]) value
sheet.getCell(2,i).getContents(),
//reading the third column’s  index based row (row[i]) value
sheet.getCell(3,i).getContents());
//reading the fourth column’s index based row  (row[i]) value

//do any operation here
//such as inserting the document content into a Database
}

}
}

And the results is from the System.out;



You can download the project’s source
Download source