poi 导入导出

参考：
--POI各Jar包的作用
http://blog.csdn.net/szwangdf/article/details/39053859
https://www.cnblogs.com/wangqc/p/poi.html
https://www.cnblogs.com/zhangchengbing/p/6340036.html

--导入导出原理或代码参考
http://blog.csdn.net/houxuehan/article/details/50960259
http://blog.csdn.net/u013871100/article/details/52901996
http://wanghongxu.iteye.com/blog/1879893
http://yl-fighting.iteye.com/blog/1726285
https://www.cnblogs.com/stono/p/6713158.html
http://blog.csdn.net/johnstrive/article/details/8393541
https://www.cnblogs.com/zhuixun/p/6600331.html
http://blog.csdn.net/aqsunkai/article/details/52270198
--POI 通用导出Excel(.xls,.xlsx)
http://blog.csdn.net/houxuehan/article/details/50960259
--java, poi 取消科学计数法
http://blog.csdn.net/this_super/article/details/6766139
--POI读取excel单元格，获取单元格各类型值，返回字符串类型
https://www.cnblogs.com/zy2009/p/6759933.html
--POI对EXCEL的操作【重点：如何设置CELL格式为文本格式】
http://blog.csdn.net/frist_huangsuli/article/details/7701446
--用SpringMvc实现Excel导出功能
https://www.cnblogs.com/liangblog/p/5831003.html


demo

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
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.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * poi导入导出excel文件工具类
 */
public class PoiUtils {
	/**
	 * 1、需要导入的jar poi组件--文件格式--需要引入的jar HSSF Excel XLS poi HSSF是POI工程对Excel
	 * 97(-2007)文件操作的纯Java实现 XSSF Excel XLSX poi-ooxml XSSF是POI工程对Excel 2007
	 * OOXML (.xlsx)文件操作的纯Java实现 Common SS Excel XLS and XLSX poi-ooxml
	 * 2、需要考虑的问题 excel文件格式：xls和xlsx excel文件的行数量：少数量、多数量
	 */
	private static final Log logger = LogFactory.getLog(PoiUtils.class);
	
	public static void main(String[] args) {
	File file=new File("D:\\测试数据222.xlsx");
	FileInputStream fileInputStream=null;
	try {
		fileInputStream=new FileInputStream(file);
	} catch (FileNotFoundException e) {
		e.printStackTrace();
	}
	readExcelFileDta2List(fileInputStream, ".xlsx", 1);
		
	}

	
	/**
	 * 以字符串的形式获取单元格的值
	 * @param cell
	 * @return
	 */
	private static String getCellStringValue(Cell cell) {
		/**
		 * CellType 类型 值 CELL_TYPE_NUMERIC 数值型 0 CELL_TYPE_STRING 字符串型 1
		 * CELL_TYPE_FORMULA 公式型 2 CELL_TYPE_BLANK 空值 3 CELL_TYPE_BOOLEAN 布尔型 4
		 * CELL_TYPE_ERROR 错误 5
		 */
		if (null == cell) {
			return "";
		}
		String result = "";
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:// String类型
			result = cell.getStringCellValue();
			break;
		case Cell.CELL_TYPE_NUMERIC:// 数字类型
			result = NumberToTextConverter.toText(cell.getNumericCellValue()); 
			break;
		case Cell.CELL_TYPE_BOOLEAN:// 布尔值
			result = String.valueOf(cell.getBooleanCellValue());
			break;
		case Cell.CELL_TYPE_FORMULA:// 公式
			result = cell.getCellFormula();
			break;
		case Cell.CELL_TYPE_BLANK:// 空格
			result = "";
			break;
		default: // 其它 CELL_TYPE_ERROR
			result = "";
			break;
		}
		return result;
	}

	/**
	 * 解析导入数据的文件流
	 * 
	 * @param inputStream
	 * @param suffix 如".xls"或".xlsx"
	 * @param startRowIndex
	 * @return
	 */
	public static List<String[]> readExcelFileDta2List(InputStream inputStream, String suffix, int startRowIndex) {
		Workbook workbook = null;
		if (ExcelCommonEnum.SUFFIX_XLS.getValue().equals(suffix)) {
			// excel文件名的后缀是“.xls”
			try {
				// HSSF是POI工程对Excel 97(-2007)文件操作的纯Java实现
				workbook = new HSSFWorkbook(inputStream);
			} catch (IOException e) {
				e.printStackTrace();
			}
		} else if (ExcelCommonEnum.SUFFIX_XLSX.getValue().equals(suffix)) {
			// excel文件名的后缀是“.xlsx”
			try {
				// XSSF是POI工程对Excel 2007 OOXML (.xlsx)文件操作的纯Java实现
				workbook = new XSSFWorkbook(inputStream);
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		Sheet sheet = workbook.getSheetAt(0);
		if (sheet == null) {
			return null;
		}
		ArrayList<String[]> list = new ArrayList<String[]>();
		int lastRowNum = sheet.getLastRowNum(); // 最大行数
		Row titleRow = sheet.getRow(0);//标题行,用于定义String[]长度
		for (int rowNum = startRowIndex; rowNum <= lastRowNum; rowNum++) {
			if (null != sheet.getRow(rowNum)) {
				Row row = sheet.getRow(rowNum);// 取第rowNum行为当前遍历行
				short firstCellNum = row.getFirstCellNum();// 0
				short lastCellNum = row.getLastCellNum();// 当前行的最大列数
				if (firstCellNum != lastCellNum) {
					// 如果列数不为0，即sheet不为空
					String[] values = new String[titleRow.getLastCellNum()];// 定义一个以当前行最大列数为长度的字符串数组
					for (int cellNum = firstCellNum; cellNum < titleRow.getLastCellNum(); cellNum++) {
						Cell cell = row.getCell(cellNum);
						if (null == cell) {
							values[cellNum] = ExcelCommonEnum.NULL.getValue();
						} else {
							String cellValue=getCellStringValue(cell);// 获取单元格的值
							values[cellNum] = StringUtils.isNotBlank(cellValue)?cellValue.trim():ExcelCommonEnum.NULL.getValue();
						}
					}
					list.add(values);
				}
			}
		}
		return list;
	}
}