java使用poi导入excel工具类

pom.xml加入依赖

<dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi</artifactId>

<version>3.17</version>

</dependency>

<dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml</artifactId>

<version>3.17</version>

</dependency>

<dependency>

<groupId>org.apache.commons</groupId>

<artifactId>commons-lang3</artifactId>

<version>3.4</version>

</dependency>

点击复制

package com.testweb.utils;

import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.xssf.usermodel.XSSFWorkbook;

public class ImportExcelUtil {
	
	private final static String excel2003L = ".xls";
	private final static String excel2007U = ".xlsx";
	
	public List<List<Object>> getListByExcel(InputStream in ,String fileName) throws Exception{	//file.getInputStream();
		List<List<Object>> listob = null;
		//创建excel工作簿
		Workbook work = this.getWorkbook(in, fileName);
		if(null == work) {
			throw new Exception("创建Excel工作簿为空");
		}
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;
		listob = new ArrayList<List<Object>>();
		//遍历excel中所有的sheet
		for (int i = 0; i < work.getNumberOfSheets(); i++) {
			sheet = work.getSheetAt(i);
			if(sheet==null) {continue;}
			
			//遍历当前sheet中所有的行
			//sheet.getFirstRowNum() 第一行为标题
			for (int j = 1; j < sheet.getLastRowNum(); j++) {
				row = sheet.getRow(j);
				if(row==null) {continue;}	//|| row.getFirstCellNum()==j
				
				//遍历所有的列
				List<Object> li = new ArrayList<Object>();
				//row.getFirstCellNum()
				for (int k = 0; k < row.getLastCellNum(); k++) {
					cell = row.getCell(k);
					li.add(this.getCellValue(cell));
				}
				listob.add(li);
			}
		}
		work.close();
		return listob;
	}
	
	/**
	 * 描述：根据文件后缀，自适应上传文件的版本
	 * @param in
	 * @param fileName
	 * @return
	 * @throws Exception
	 */
	public Workbook getWorkbook(InputStream in ,String fileName) throws Exception {
		Workbook wb = null;
		String fileType = fileName.substring(fileName.lastIndexOf("."));
		if(excel2003L.equals(fileType)) {
			wb = new HSSFWorkbook(in);
		}else if(excel2007U.equals(fileType)) {
			wb = new XSSFWorkbook(in);
		}else {
			throw new Exception("解析的文件有误！");
		}		
		return wb;
	}
	
	/**
	 * 描述：对表格中的值进行格式化
	 */
	public Object getCellValue(Cell cell) {
		Object value = null;
		DecimalFormat df = new DecimalFormat("0");	//格式化number String字符串
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");	//日期格式化
		DecimalFormat df2 = new DecimalFormat("0.00");	//格式化数字
		if(cell==null) {
			value = "";
			return value;
		}
		switch(cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:
			value = cell.getRichStringCellValue().getString();
			break;
		case Cell.CELL_TYPE_NUMERIC:
			if("General".equals(cell.getCellStyle().getDataFormatString())) {
				value = df.format(cell.getNumericCellValue());
			}else if("/m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
				value = df2.format(cell.getDateCellValue());
			}else {
				value = df2.format(cell.getNumericCellValue());
			}
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			value = cell.getBooleanCellValue();
		case Cell.CELL_TYPE_BLANK:
			value = "";
			break;
		default:
			break;
		}
		return value;
	}
	
}