package com.test.excel;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.text.SimpleDateFormat;

public class Test1 {

    public static void main(String[] args) throws Exception {
        FileInputStream fis = new FileInputStream("D:\\bak\\v2/test3.xls");
        Workbook workbook = new HSSFWorkbook(fis);
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(0);
        Cell cell = row.getCell(0);
        System.out.println(cell.getStringCellValue());

        for (Row row2 : sheet) {
            for (Cell cell1 : row2) {
                System.out.print(getCellValueByCell(cell1) + ";");
            }
            System.out.println();
        }
    }

    /**
     * 获取单元格各类型值，返回字符串类型
     *
     * @param cell cell
     * @return String
     */
    public static String getCellValueByCell(Cell cell) {
        //判断是否为null或空串
        if (cell == null || cell.toString().trim().equals("")) {
            return "";
        }
        String cellValue = "";
        CellType cellType = cell.getCellType();
        switch (cellType) {
            case NUMERIC: // 数字
                short format = cell.getCellStyle().getDataFormat();
                if (DateUtil.isCellDateFormatted(cell)) { // 日期
                    SimpleDateFormat sdf;
                    if (format == 20 || format == 32) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else if (format == 14 || format == 31 || format == 57 || format == 58) {
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                    } else if (format == 179) {
                        sdf = new SimpleDateFormat("HH:mm:ss");
                    } else {
                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    }
                    cellValue = sdf.format(cell.getDateCellValue());
                } else {
                    cell.setCellType(CellType.STRING);
                    cellValue = cell.getStringCellValue();
                }
                break;
            case STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue() + "";
                break;
            case FORMULA: // 公式
                cell.setCellType(CellType.STRING);
                cellValue = cell.getStringCellValue();
                break;
            case BLANK: // 空值
                cellValue = "";
                break;
            case ERROR: // 故障
                cellValue = "ERROR VALUE";
                break;
            default:
                cellValue = "UNKNOWN VALUE";
                break;
        }
        return cellValue;
    }
}

帆软报表获取单元格的值的方式在分页预览，填报预览，新填报预览都是不同的，没有统一的方式，这给使用者带来一定的不便。现在总结一下。

分页预览：

 getCellValue: function (cellPositionLabl) {
           // cellPositionLabl=A1这样的形式
            //  这是分页预览  
            var columnIndex = cellPositionLabl.charCodeAt(0) - 65;
            var rowIndex = parseInt(cellPositionLabl.substr(1, cellPositionLabl.length)) - 1
            console.log("rowIndex:" + rowIndex + ",columnIndex:" + columnIndex)

            var rowIds = "tr[tridx=" + rowIndex + "]";
            var value = $(rowIds, "div.content-container").children().eq(columnIndex).html();
            return value

}

填报预览：

 getCellValue: function (cellPositionLabl) {
           // cellPositionLabl=A1这样的形式
          //  填报预览
            columnIndex = cellPositionLabl.charCodeAt(0) - 65;
            var rowIndex = parse

	/**
	 * 获取单元格的值, 所见即所得(日期未完善)
	 * @param cell
	 * @return
	 */
	public static String getCellValue(Cell cell) {
		if(cell == null)
			return "";
		DataFormatter dataFormatter = new DataFormatter();

		String res = null;

		if(cell.getCellType() == CellType.FORMULA) {
			FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
			res = dataFormatter.formatCellValue(cell, evaluator);
		} else {
			res = dataFormatter.formatCellValue(cell);
		}
		String dataFormatString = cell.getCellStyle().getDataFormatString();
		if (!StringUtil.isNull(dataFormatString)) {
			if(dataFormatString.matches("@\".+\"")) {
				Pattern p =  Pattern.compile("@\"(.+)\"");
				Matcher m = p.matcher(dataFormatString);
				m.find();
				res = res + m.group(1);
			}
		}

		return res;
	}