import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Collection;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFName;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;

/** Class to highlight issue with updating a cell which is read
 *  by a form control (radio button in this case). The value
 *  is set correctly, however when the document is opened in Excel
 *  the radio button gets set but the value which was correct gets
 *  set to "0".
 *
 *  @author Gareth Smith
 */
public class RadioButtonIssueTest {
  
  /** Creates new RadioButtonIssueTest.
   */
  public RadioButtonIssueTest() {
  }
  
  public static void main(String[] args) throws Exception {
    HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream("radioIssue.xls"));
    
    // There should be a single named range in this workbook.
    if (workbook.getNumberOfNames() != 1) {
      throw new Exception("Should only be 1 named range in this workbook!");
    }
    HSSFName name = workbook.getNameAt(0);
    
    // Get the cell this name refers to
    String ref = name.getReference();
    String sheetRef = "problemSheet";
    String knownRef = sheetRef + "!$B$2";
    if (!ref.equals(knownRef)) {
      throw new Exception("Name didn't appear in the correct place");
    }
    
    HSSFSheet sheet = workbook.getSheet(sheetRef);
    HSSFRow row = sheet.getRow(1);
    HSSFCell cell = row.getCell(Short.parseShort("1"));
    if (cell == null) {
      throw new Exception("Cell not found at known location");
    }
    
    // Set the value of the cell to 1 : should currently be blank and will reset to 0 when 
    // the xls is opened in Excel.
    double cellValue = 3;
    cell.setCellValue(cellValue);
    if (cell.getNumericCellValue() != cellValue) {
      throw new Exception("The value has not been set correctly");
    }
    
    OutputStream out = new FileOutputStream("radioIssue_parsed.xls");
    workbook.write(out);
    out.flush();
    out.close();
  }
  
}