Reading Excel checkbox values in Java Apache POI

Question

I have spent countless hours trying to find a solution to this. I have tried Apache POI, JExcel and JXLS but no where have I found code to successfully read checkbox (form control) values.

If anyone has found a working solution then it would be great if you could share it here. Thanks!

UPDATE

I have written code that reads the checkbox but it cannot determine whether it is checked or not.

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.eventusermodel.HSSFEventFactory;
import org.apache.poi.hssf.eventusermodel.HSSFListener;
import org.apache.poi.hssf.eventusermodel.HSSFRequest;
import org.apache.poi.hssf.record.CommonObjectDataSubRecord;
import org.apache.poi.hssf.record.ObjRecord;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.record.SubRecord;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class App {
    private static final String path = "C:\\test.xls";
    private static final String Workbook = "Workbook";

    private static void readExcelfile() {
        FileInputStream file = null;
        try {
            file = new FileInputStream(new File(path));

            // Get the workbook instance for XLS file
            HSSFWorkbook workbook = new HSSFWorkbook(file);

            // Get first sheet from the workbook
            HSSFSheet sheet = workbook.getSheetAt(0);

            // Iterate through each rows from first sheet
            Iterator<Row> rowIterator = sheet.iterator();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();

                // For each row, iterate through each columns
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();

                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_BOOLEAN:
                        System.out.print(cell.getBooleanCellValue() + "\t\t");
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + "\t\t");
                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + "\t\t");
                        break;
                    }
                }
                System.out.println();
            }
            // file.close();
            // FileOutputStream out = new FileOutputStream(
            // new File(path));
            // workbook.write(out);
            // out.close();

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (file != null)
                    file.close();
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        }
    }

    private static void readCheckbox() {
        FileInputStream file = null;
        InputStream istream = null;
        try {
            file = new FileInputStream(new File(path));
            POIFSFileSystem poifs = new POIFSFileSystem(file);
            istream = poifs.createDocumentInputStream(Workbook);
            HSSFRequest req = new HSSFRequest();
            req.addListenerForAllRecords(new EventExample());
            HSSFEventFactory factory = new HSSFEventFactory();
            factory.processEvents(req, istream);
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (file != null)
                    file.close();
                if (istream != null)
                    istream.close();
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        }
    }

    public static void main(String[] args) {
        System.out.println("ReadExcelFile");
        readExcelfile();
        System.out.println("ReadCheckbox");
        readCheckbox();
    }
}

class EventExample implements HSSFListener {

    public void processRecord(Record record) {
        switch (record.getSid()) {
        case ObjRecord.sid:
            ObjRecord objRec = (ObjRecord) record;
            List<SubRecord> subRecords = objRec.getSubRecords();
            for (SubRecord subRecord : subRecords) {
                if (subRecord instanceof CommonObjectDataSubRecord) {
                    CommonObjectDataSubRecord datasubRecord = (CommonObjectDataSubRecord) subRecord;
                    if (datasubRecord.getObjectType() == CommonObjectDataSubRecord.OBJECT_TYPE_CHECKBOX) {
                        System.out.println("ObjId: "
                                + datasubRecord.getObjectId() + "\nDetails: "
                                + datasubRecord.toString());
                    }
                }
            }
            break;
        }
    }
}

Answer 1

Sorry for the late reply, but I ran into the same. I found a trick to determine the checkbox state.

In your example you ar looping over the SubRecords and you examine the CommonObjectDataSubRecord. But the value for the checkbox can be found in the one of the SubRecord.UnknownSubRecord. This is unfortunately a private class so you cannot call any method on it, but the toString() reveals the data, and with a little regex the value can be found. So using the code below I managed to retrieve the state of the checkbox:

Pattern p = Pattern.compile("\\[sid=0x000A.+?\\[0(\\d),");
if (!(subRecord instanceof CommonObjectDataSubRecord)) {
    Matcher m = p.matcher(subRecord.toString());
    if (m.find()) {
        String checkBit = m.group(1);
        if (checkBit.length() == 1) {
            boolean checked = "1".equals(checkBit);
            checkBox.setChecked(checked);
        }
    }
}

Now my challenge is to retrieve the checkbox value in a xlsx file...