excel formula stuff

excel_formula_extractor.java

package com.excel_formula_extractor;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ExcelFormulaExtractor {

	public static void readExcelFormula(String fileName) throws IOException {
		// SS Workbook object
		Workbook workbook;
		// Handles both XSSF and HSSF automatically
		workbook = WorkbookFactory.create(new FileInputStream(fileName));
		// FileInputStream fis = new FileInputStream(fileName);
		// assuming xlsx file
		// Workbook workbook = new HSSFWorkbook(fis);
		// Sheet sheet = workbook.getSheetAt(1);
		FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
		// for (Sheet sheet : workbook) {

		for (int sn = 0; sn < workbook.getNumberOfSheets(); sn++) {
			Sheet sheet = workbook.getSheetAt(sn);
			System.out.println("Sheet Index=" + sn);
			System.out.println("Sheet Name=" + sheet.getSheetName());
			System.out.println("Sheet Visibility=" + workbook.getSheetVisibility(sn).toString());
			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {
				Row row = rowIterator.next();
				Iterator<Cell> cellIterator = row.cellIterator();

				while (cellIterator.hasNext()) {
					Cell cell = cellIterator.next();
					switch (cell.getCellType()) {
					case STRING:
						System.out.println(cell.getStringCellValue());
						break;
					case FORMULA:
						try {
							System.out.println("Cell Formula=" + cell.getCellFormula());
							System.out.println("Cell Formula Result Type=" + cell.getCachedFormulaResultType());
							if (cell.getCachedFormulaResultType() == CellType.NUMERIC) {
								System.out.println("Formula Value=" + cell.getNumericCellValue());
							} else if (cell.getCachedFormulaResultType() == CellType.STRING) {
								System.out.println("Formula Value=" + cell.getStringCellValue());
							}
						} catch (Exception e) {
							// System.out.println(e);
						}
						try {
							CellValue cellValue = evaluator.evaluate(cell);

							switch (cellValue.getCellType()) {
							case BOOLEAN:
								System.out.println("Formula Computed Value=" + cellValue.getBooleanValue());
								break;
							case NUMERIC:
								System.out.println("Formula Computed Value=" + cellValue.getNumberValue());
								break;
							case STRING:
								System.out.println("Formula Computed Value=" + cellValue.getStringValue());
								break;
							case BLANK:
								break;
							case ERROR:
								System.out.println("Formula Computed Value=" + cellValue.getErrorValue());
								break;
							// CELL_TYPE_FORMULA will never occur
							case FORMULA:
								break;
							case _NONE:
								break;
							default:
								break;
							}
						} catch (Exception e) {
							// System.out.println(e);
						}
					case BLANK:
						break;
					case BOOLEAN:
						break;
					case ERROR:
						break;
					case NUMERIC:
						System.out.println(cell.getNumericCellValue());
						break;
					case _NONE:
						break;
					default:
						break;
					}
				}
			}
		}
	}

	public static void main(String args[]) throws IOException {
		File f = new File(args[0]);
		if (f.exists() && f.isFile() && f.canRead()) {
			readExcelFormula(args[0]);
		}
	}
}