JAVA-excel 导入

java excel 导入

@(JAVA)[excel 导入]

jsp

<input type="file" id="upload" name="upload" class="hidden">
<button type="button" id="btn-import">导入</button>

js

$(document).on('click', "#btn-import", function() {
  $('#upload').click();
});

action

@Autowired
private XXXSrv srv;
	
private List<File> upload; // 得到上传的文件
private List<String> uploadFileName; // 得到文件的名称
//省略get,set方法

public void importFile() {
		if (CollectionUtils.isNotEmpty(uploadFileName)) {
			String fileName = uploadFileName.get(0);
			String extention = fileName.substring(fileName.lastIndexOf("."));
			if (fileName.indexOf(extention) > -1 && (extention.equals(".xls") || extention.equals(".xlsx"))) {
				srv.importExcel(upload.get(0), extention);
				sendSuccessMsg("", "导入成功！");
			} else {
				this.sendFailMsg("", "不支持上传的文件类型");
			}
		} else {
			this.sendFailMsg("", "上传的文件为空");
		}
	}

XXXSrvImpl

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hibernate.criterion.Order;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

//省略接口
@Override
public void importExcel(File file, String extention) {
	FileInputStream in;
	try {
		in = new FileInputStream(file);
		List<Map<String, Object>> excelData = null;
		if (extention.equals(".xls")) {
			HSSFWorkbook workbook = new HSSFWorkbook(in);
			excelData = ExcelImportUtil.read2007Excel(workbook);
		} else if (extention.equals(".xlsx")) {
			XSSFWorkbook workbook = new XSSFWorkbook(in);
			excelData = ExcelImportUtil.readExcel(workbook);
		}
		ExcelImportUtil.toObjectList(excelData, User.class);
	} catch (FileNotFoundException e) {
		e.printStackTrace();
	} catch (IOException e) {
		e.printStackTrace();
	} catch (Exception e) {
		e.printStackTrace();
	}
}

帮助类 ExcelImportUtil

/**
 * 功能：读取EXCEL中的数据到List<Map<String, Object>>中
 *
 * xlsx
 *
 */
public static List<Map<String, Object>> read2007Excel(HSSFWorkbook workbook) {
	List<Map<String, Object>> excelData = new LinkedList<Map<String, Object>>();
	int columnLength = workbook.getSheetAt(0).getRow(0).getPhysicalNumberOfCells();
	HSSFSheet sheet = workbook.getSheetAt(0);
	String[] columnNames = createColumnNameArray(sheet.getRow(0), columnLength);
	for (int rowIndex = 1; rowIndex < sheet.getPhysicalNumberOfRows(); rowIndex++) {
		Map<String, Object> cellMap = Maps.newHashMap();
		HSSFRow row = sheet.getRow(rowIndex);
		for (int cellIndex = 0; cellIndex < row.getPhysicalNumberOfCells(); cellIndex++) {
			cellMap.put(columnNames[cellIndex], getCellValue(row.getCell(cellIndex)));
		}
		excelData.add(cellMap);
	}
	return excelData;
}
/**
 * 功能：读取EXCEL中的数据到List<Map<String, Object>>中
 * xls
 */
public static List<Map<String, Object>> readExcel(XSSFWorkbook workbook) {
	List<Map<String, Object>> excelData = new LinkedList<Map<String, Object>>();
	int columnLength = workbook.getSheetAt(0).getRow(0).getPhysicalNumberOfCells();
	XSSFSheet sheet = workbook.getSheetAt(0);
	String[] columnNames = createColumnNameArray(sheet.getRow(0), columnLength);
	for (int rowIndex = 1; rowIndex < sheet.getPhysicalNumberOfRows(); rowIndex++) {
		Map<String, Object> cellMap = Maps.newHashMap();
		XSSFRow row = sheet.getRow(rowIndex);
		for (int cellIndex = 0; cellIndex < row.getPhysicalNumberOfCells(); cellIndex++) {
			cellMap.put(columnNames[cellIndex], getCellValue(row.getCell(cellIndex)));
		}
		excelData.add(cellMap);
	}
	return excelData;
}
	
/**
 * 功能：创建EXCEL列名数组
 * 
 */
private static String[] createColumnNameArray(HSSFRow row, int length) {
	String[] columnName = new String[length];
	for (int cellIndex = 0; cellIndex < row.getPhysicalNumberOfCells(); cellIndex++) {
		columnName[cellIndex] = getCellValue(row.getCell(cellIndex));
	}
	return columnName;
}

/**
 * 功能：创建EXCEL当前单元格内容
 *
 */
private static String getCellValue(HSSFCell cell) {
	String value = null;
	if (cell != null) {
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_NUMERIC:
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
				value = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
			} else {
				value = String.valueOf(cell.getNumericCellValue());
			}
			break;
		case HSSFCell.CELL_TYPE_STRING:
			value = cell.getStringCellValue();
			break;
		case HSSFCell.CELL_TYPE_BOOLEAN:
			value = String.valueOf(cell.getBooleanCellValue());
			break;
		case HSSFCell.CELL_TYPE_ERROR:// 错误
			break;
		case HSSFCell.CELL_TYPE_FORMULA:
			String val = String.valueOf(cell.getNumericCellValue());
			value = val.equals("NaN") ? String.valueOf(cell.getStringCellValue()) : val;// 如果获取的数据值非法,就将其装换为对应的字符串
			break;
		case HSSFCell.CELL_TYPE_BLANK:// 空值
			break;
		default:
			value = cell.getStringCellValue();
			break;
		}
	}
	return value;
}

/**
 * 功能：利用反射将 List<Map<String,Object>>结构 生成相应的List<T>数据
 *
 */
public static <T> List<T> toObjectList(List<Map<String, Object>> list, Class<T> clazz) throws Exception {
	List<T> objList = new LinkedList<T>();
	for (int i = 0; i < list.size(); i++) {
		Set<Map.Entry<String, Object>> set = list.get(i).entrySet();
		Iterator<Entry<String, Object>> it = set.iterator();
		T obj = clazz.newInstance();
		Method[] methods = clazz.getDeclaredMethods();
		while (it.hasNext()) {
			Map.Entry<String, Object> entry = (Map.Entry<String, Object>) it.next();
			for (Method m : methods) {
				if (m.getName().startsWith("set")) {
					String methodName = entry.getKey().toString();
					StringBuffer sb = new StringBuffer(methodName);
					sb.replace(0, 1, (methodName.charAt(0) + "").toUpperCase());
					methodName = "set" + sb.toString();
					if (methodName.equals(m.getName())) {
						m.invoke(obj, entry.getValue());
						break;
					}
				}
			}
		}
		objList.add(obj);
	}
	return objList;
}

JAVA-excel 导入

java excel 导入

推荐阅读