com.clickntap.square.poi.Excel Maven / Gradle / Ivy

Go to download
package com.clickntap.square.poi;

import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.json.JSONArray;
import org.json.JSONObject;

public class Excel {

	private XSSFWorkbook workbook;

	public Excel(InputStream in) throws Exception {
		workbook = new XSSFWorkbook(in);
	}

	public JSONObject json() {
		JSONObject json = new JSONObject();
		JSONArray rows = new JSONArray();
		Sheet sheet = workbook.getSheetAt(0);
		for (Row row : sheet) {
			JSONArray cols = new JSONArray();
			for (Cell cell : row) {
				switch (cell.getCellType()) {
				case STRING:
					cols.put(cell.getStringCellValue());
					break;
				case NUMERIC:
					if (DateUtil.isCellDateFormatted(cell)) {
						Date date = cell.getDateCellValue();
						SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
						String formattedDate = dateFormat.format(date);
						cols.put(formattedDate);
					} else {
						cols.put(cell.getNumericCellValue());
					}
					break;
				case BOOLEAN:
					cols.put(cell.getBooleanCellValue());
					break;
				case FORMULA:
					cols.put(cell.getCellFormula());
					break;
				default:
					cols.put("");
				}
			}
			rows.put(cols);
		}
		json.put("rows", rows);
		return json;
	}

}