POI读取Excel通用代码---支持2003,2007

package com.citics.crm.customerwidget.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi2.hssf.usermodel.HSSFCell;
import org.apache.poi2.hssf.usermodel.HSSFRow;
import org.apache.poi2.hssf.usermodel.HSSFSheet;
import org.apache.poi2.hssf.usermodel.HSSFWorkbook;

public class ExcelUtil {
	
	private ExcelUtil(){};
	private static List<String> columns;//要解析excel中的列名
	private static int sheetNum = 0;//要解析的sheet下标

	/**
	 * poi读取excle
	 * @return
	 */
	public static String readExcel(File file){
		StringBuilder retJson = new StringBuilder();
		InputStream inStream = null;
		try {
			inStream = new FileInputStream(file);
			HSSFWorkbook workbook = new HSSFWorkbook(inStream);
			HSSFSheet sheet = workbook.getSheetAt(sheetNum);//获得表
			int lastRowNum = sheet.getLastRowNum();//最后一行
			retJson.append("[");
			for(int i = 0 ;i < lastRowNum;i++){
				HSSFRow row = sheet.getRow(i);//获得行
				String rowJson = readExcelRow(row);
				retJson.append(rowJson);
				if(i<lastRowNum-1)
					retJson.append(",");
			}
			retJson.append("]");
		} catch (Exception e) {
			try {
				inStream = new FileInputStream(file);
				XSSFWorkbook workbook = new XSSFWorkbook(inStream);
				XSSFSheet sheet = workbook.getSheetAt(sheetNum);
				int lastRowNum = sheet.getLastRowNum();//最后一行
				retJson.append("[");
				for(int i = 0 ;i < lastRowNum;i++){
					XSSFRow row = sheet.getRow(i);//获得行
					String rowJson = readExcelRow(row);
					retJson.append(rowJson);
					if(i<lastRowNum-1)
						retJson.append(",");
				}
				retJson.append("]");
			} catch (IOException e1) {
				e1.printStackTrace();
			}
		}finally{
			close(null,inStream);
		}
		return retJson.toString();
	}
	/**
	 * 读取行值
	 * @return
	 */
	private static String readExcelRow(HSSFRow row){
		StringBuilder rowJson = new StringBuilder();
		int lastCellNum = ExcelUtil.columns.size();//最后一个单元格
		rowJson.append("{");
		for(int i = 0 ;i<lastCellNum;i++){
			HSSFCell cell = row.getCell(i);
			String cellVal = readCellValue(cell);
			rowJson.append(toJsonItem(columns.get(i), cellVal));
			if(i<lastCellNum-1)
				rowJson.append(",");
		}
		rowJson.append("}");
		return rowJson.toString();
	}
	/**
	 * 读取行值
	 * @return
	 */
	private static String readExcelRow(XSSFRow row){
		StringBuilder rowJson = new StringBuilder();
		int lastCellNum = ExcelUtil.columns.size();//最后一个单元格
		rowJson.append("{");
		for(int i = 0 ;i<lastCellNum;i++){
			XSSFCell cell = row.getCell(i);
			String cellVal = readCellValue(cell);
			rowJson.append(toJsonItem(columns.get(i), cellVal));
			if(i<lastCellNum-1)
				rowJson.append(",");
		}
		rowJson.append("}");
		return rowJson.toString();
	}

	/**
	 * 读取单元格的值
	 * @param hssfCell
	 * @return
	 */
	@SuppressWarnings("static-access")
	private static String readCellValue(HSSFCell hssfCell) {
		if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
			return String.valueOf(hssfCell.getNumericCellValue());
		} else {
			return String.valueOf(hssfCell.getRichStringCellValue());
		}
	}
	/**
	 * 读取单元格的值
	 * @param hssfCell
	 * @return
	 */
	@SuppressWarnings("static-access")
	private static String readCellValue(XSSFCell hssfCell) {
		if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
			return String.valueOf(hssfCell.getNumericCellValue());
		} else {
			return String.valueOf(hssfCell.getRichStringCellValue());
		}
	}
	/**
	 * 转换为json对
	 * @return
	 */
	private static String toJsonItem(String name,String val){
		return "\""+name+"\":\""+val+"\"";
	}
	/**
	 * 关闭io流
	 * @param fos
	 * @param fis
	 */
	private static void close(OutputStream out, InputStream in) {
        if (in != null) {
            try {
                in.close();
            } catch (IOException e) {
                System.out.println("InputStream关闭失败");
                e.printStackTrace();
            }
        }
        if (out != null) {
            try {
                out.close();
            } catch (IOException e) {
                System.out.println("OutputStream关闭失败");
                e.printStackTrace();
            }
        }
    }
	public static List<String> getColumns() {
		return ExcelUtil.columns;
	}
	public static void setColumns(List<String> columns) {
		ExcelUtil.columns = columns;
	}
	public static int getSheetNum() {
		return sheetNum;
	}
	public static void setSheetNum(int sheetNum) {
		ExcelUtil.sheetNum = sheetNum;
	}
	
}

 

调用方式

List<String> columns = new ArrayList<String>();
   columns.add("CUST_NAME1");//战略客户名
   columns.add("CUST_NAME2");//客户名
   columns.add("INCOME_TYPE");//收入类型
   columns.add("DEPT_NAME");//部门名称
   columns.add("YEAR");//年份
   columns.add("MONTH");//月份
   columns.add("AMT");//金额
   ExcelUtil.setColumns(columns);

   String retJson = ExcelUtil.readExcel(upload);