JavaWeb中POI导入和导出Excel、Map键值类型转换、时间格式化、对象赋值等常见工具类集锦

导入表格解析工具类

使用前先在maven项目中添加依赖

<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.15</version>
		</dependency>

ImportExcelUtil.java

package com.kilomob.powernetwork.permission.common;

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

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
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;

import com.kilomob.powernetwork.common.util.MapUtil;

public class ImportExcelUtil {

	
	/**
	 * 
	 * @Description map配置，名称为value,key为字段名
	 * @param in
	 * @param fileName
	 * @param map
	 * @return
	 * @throws Exception
	 * List<Map<String,Object>>
	 * @exception:
	 * @author: fengjk
	 * @time:2017年5月3日 下午8:27:10
	 */
	public static List<Map<String, Object>> getListByExcel(InputStream in, String fileName, Map<String, String> map)
			throws Exception {
		// 创建Excel工作薄
		Workbook work = getWorkbook(in, fileName);
		if (null == work) {
			throw new Exception("创建Excel工作薄为空！");
		}
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;

		List<Map<String, Object>> dataListMaps = new ArrayList<Map<String, Object>>();
		// 遍历Excel中所有的sheet
		for (int i = 0; i < work.getNumberOfSheets(); i++) {
			sheet = work.getSheetAt(i);
			if (sheet == null) {
				continue;
			}
			List<String> titleList = new ArrayList<String>();
			int colNum = 0; // 列数
			Row row0 = sheet.getRow(0);
			if(row0 == null){
				throw new RuntimeException("导入模版不正确，请下载正确的模版导入");
			}
			List<String> modelKeyList = new ArrayList<String>(20);
			modelKeyList.addAll(map.keySet());

			colNum = row0.getPhysicalNumberOfCells();
			for (int i1 = 0; i1 < colNum; i1++) {
				String nameString = (String) getCellValue(row0.getCell((short) i1));
				if(StringUtils.isNotBlank(nameString) && !nameString.equals(MapUtil.getStringValue(map,modelKeyList.get(i1)))){
					throw new RuntimeException("导入模版不正确，请下载正确的模版导入");
				}
				nameString = nameString.replace(" ", "");
				titleList.add(nameString);// 得到列名
			}

			// 遍历当前sheet中的所有行
			for (int j = sheet.getFirstRowNum(); j <=sheet.getLastRowNum(); j++) {
				row = sheet.getRow(j);
				if (row == null ||row.getFirstCellNum() == j) {
					continue;
				}
				// 遍历所有的列
				Map<String, Object> mapCell = new HashMap<String, Object>();
				for (int y = row.getFirstCellNum(); y <= row.getPhysicalNumberOfCells(); y++) {
					cell = row.getCell(y);
					if (null != cell) {
						mapCell.put(titleList.get(y), getCellValue(cell));
					}
				}
				Map<String, Object> mapNew = new HashMap<>();
				for (String key : map.keySet()) {
					mapNew.put(key, mapCell.get(map.get(key)));
				}
				dataListMaps.add(mapNew);
			}
		}
		work.close();
		return dataListMaps;
	}

	
	/**
	 * 描述：根据文件后缀，自适应上传文件的版本
	 * 
	 * @param inStr
	 *            ,fileName
	 * @return
	 * @throws Exception
	 */
	public static Workbook getWorkbook(InputStream inStr, String fileName)
			throws Exception {
		String fileType = fileName.substring(fileName.lastIndexOf("."));
		if(! inStr.markSupported()) {  
			inStr = new PushbackInputStream(inStr, 8);  
        }  
		if(POIFSFileSystem.hasPOIFSHeader(inStr)) {  
            return new HSSFWorkbook(inStr);  
        }  
        if(POIXMLDocument.hasOOXMLHeader(inStr)) {  
            return new XSSFWorkbook(OPCPackage.open(inStr));  
        }  
		throw new Exception("解析的文件格式有误！");
		
	}

	/**
	 * 描述：对表格中数值进行格式化
	 * 
	 * @param cell
	 * @return
	 */
	public static Object getCellValue(Cell cell) {
		Object value = null;
		DecimalFormat df = new DecimalFormat("0"); // 格式化number String字符
		SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); // 日期格式化
		DecimalFormat df2 = new DecimalFormat("0.00"); // 格式化数字

		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 = sdf.format(cell.getDateCellValue());
			} else {
				value = df2.format(cell.getNumericCellValue());
			}
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			value = cell.getBooleanCellValue();
			break;
		case Cell.CELL_TYPE_BLANK:
			value = "";
			break;
		default:
			break;
		}
		return value;
	}




}

使用例子

	File file = new File(path); // path文件路径
	InputStream in = new FileInputStream(file);
	List<Map<String, Object>> dataList = ImportExcelUtil.getListByExcel(in, file.getName(), this.getExcelColumMap());
	
	/**
	 * 
	 * @Description Map中value对应Excel列名，key对应上面解析出来Map数据的key
	 * @return
	 * Map<String,String>
	 * @exception:
	 * @author: fengjk
	 * @time:2017年4月14日 下午2:14:02
	 */
	public Map<String,String> getExcelColumMap(){
		Map<String,String> columMap = new LinkedHashMap<String,String>();
		columMap.put("name", "地区");
		columMap.put("longilatitude", "经纬度");
		
		return columMap;
	}

导出表格工具类

ExportExcelUtil.java

package com.kilomob.powernetwork.ma