导入excel格式的数据

表单

<pre name="code" class="html"><form id="myFormId"  action="${path}/person!importPerson.do" method="post" enctype="multipart/form-data" target="frmright">
	<div class="box1" id="formContent">
			<table class="tableStyle" width="100%">
				<tr>
					<td><input type="file"  id="buildingExcel" name="buildingExcel" accept="application/vnd.ms-excel" class="validate[required]" style="min-width:200px;"/></td>
					<td><input type="submit" value="导入"/></td>
				</tr>
				<tr>
					<td class="padding_top5" colspan="2">说明：导入的excel需要遵循规定的格式</td>
				</tr>
				<tr>
					<td colspan="2"><a class="red underline" href="${path }/demo/person.xls">点击这里</a>下载导入的excel数据模板</td>
				</tr>
			</table>
		</div>
	</form>
<!-- 异步提交start -->
<script type="text/javascript">
function initComplete(){
	
	//表单提交
	$('#myFormId').submit(function(){ 
	    //判断表单的客户端验证是否通过
		var valid = $('#myFormId').validationEngine({returnIsValid: true, showOnMouseOver:false});
	    if(valid){
			   $(this).ajaxSubmit({
			        //表单提交成功后的回调
			        success: function(responseText, statusText, xhr, $form){
			            top.Dialog.alert(responseText,function(){
			            	closeWin();
			            });
			        }
			    }); 
			 }
		    //阻止表单默认提交事件
		    return false; 
	});
}
//重置
function closeWin(){
	var update = false;
	var isupdate = '';
	if(isupdate != ''){
		update = true;
	}else{
		update = false;
	}
	//刷新数据
	top.frmright.refresh(true);
	//关闭窗口
	top.Dialog.close();
}
</script>

后台方法

</pre><pre name="code" class="java">//上传文件
		private String uploadFile1() {
			ServletFileUpload uploadHandler = new ServletFileUpload(
					new DiskFileItemFactory());
			List<FileItem> items = null;
			String destPath = "";
			try {
				items = uploadHandler.parseRequest(request);
				for (FileItem item : items) {
					if (!item.isFormField()) {
						String fileSrcName = item.getName();
						// 存储相对路径
						SimpleDateFormat sf = new SimpleDateFormat("yyyy"
								+ File.separator + "MM" + File.separator + 

"dd");

						String path = sf.format(new Date()) + File.separator
								+ "admin" + File.separator;
						Random random = new Random();
						temp = Integer.toString(random.nextInt(9999) + 10000);
						temp = temp.substring(1, temp.length());
						temp = new SimpleDateFormat("yyyy-MM-dd_HHmmss").format(new 

Date()) +
								temp + "_";
						// 文件后缀名
						String dileDotExt = FileUtils
								.getFileSuffixName(fileSrcName);
						destPath = AideConfig.WORK_DIR + File.separator + path
								+ temp + dileDotExt;

						File file = new File(destPath);
						if (!file.getParentFile().exists()
								|| !file.getParentFile().isDirectory()) {
							file.getParentFile().mkdirs();
						}
						item.write(file);

					}

				}
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return "";
			}
			return destPath;

		}
		
		/**
		 * 
		 * @author 
		 * @time 上午8:31:26
		 * @return String
		 * @description 导入信息
		 */
	@Action
	public String importPerson(){
		String path = uploadFile1();
		String[] keys = { 
				"num","departnum","workunit","name", "sex","nations","IDcard", "birthday","nativeplace",
				
				"partys","joinpartytime","timeofwork","schooltime","schoolandmajors1","gratime1","degree",
				
				"educations","schoolandmajors2","Progress","nowtechnicalposition","Series","doubles",
				
				"positiontype","reviewtime","PoliticalWork","PoliticalWorkType","PoliticalWorkTime",
				
				"nowadministrativeduties","appointedtime","personType","AssistantoRattachment"
				};
		List<Map<String, Object>> dataList = null;
		try {
			dataList = ExcelUtil.readExcel(keys, path);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			return e.getMessage();
		}
		File f = new File(path); // 删除文件
		if (f.exists())
			f.delete();
		for (int i = 0; i < dataList.size(); i++) { // 处理主键
			String UUID = java.util.UUID.randomUUID().toString();
			dataList.get(i).put("id", UUID);
		}
		int a = personService.importPerson(dataList);
		if(a > 0){
			return "导入成功";
		}else{
			return "导入失败";
		}
	}
	

工具类：

package com.aide.publics.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;

public class ExcelUtil {
	
	/**
	 * 导入excel文件到ListMap
	 * 
	 * @param keys []
	 *            控制ListMap中的关键字
	 * @param filepath
	 *            已上传的文件路径
	 * @return 每个map都是原文件的一行
	 */
	public static List<Map<String,Object>> readExcel(String []keys,String filePath) throws Exception{
		POIFSFileSystem fs=null;
		List<Map<String,Object>> dataList=new ArrayList<Map<String, Object>>();
		
		try {
			fs = new POIFSFileSystem(new FileInputStream(
					filePath));
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new Exception("文件不存在！");
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		HSSFWorkbook workBook=null;
		 try {
			workBook = new HSSFWorkbook(fs);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
         /**
          * 获得Excel中工作表个数
          */
		 for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
			 HSSFSheet sheet = workBook.getSheetAt(i);
			 int rows = sheet.getPhysicalNumberOfRows(); // 获得行数
             if (rows > 0) {
                 sheet.getMargin(HSSFSheet.TopMargin);
                 //以第0行的列数为准
                 HSSFRow row0 = sheet.getRow(0);
                 int cells = row0.getLastCellNum();//获得列数
                 if(cells!=keys.length){
                	 return null;
                 }
                 for (int j = 1; j < rows; j++) { // 行循环
                     HSSFRow row = sheet.getRow(j);
                     if (row != null) {
                         
                         Map<String, Object> hm = new HashMap<String, Object>();
                         for (int k = 0; k < cells; k++) { // 列循环
                             HSSFCell cell = row.getCell(k);
                             if(cell==null){//处理为null的情况
                            	 hm.put(keys[k],"");
                            	 continue;
                             }
							switch (cell.getCellType()) {

							// 数值型
							case HSSFCell.CELL_TYPE_NUMERIC:
								if (HSSFDateUtil.isCellDateFormatted(cell)) {
									// 如果是date类型则 ，获取该cell的date值
									SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
									hm.put(keys[k], sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString());
								
								} else {// 纯数字，强制转换为字符串
									cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
									hm.put(keys[k], cell.getStringCellValue());
								}
								break;

							/* 此行表示单元格的内容为string类型 */
							case HSSFCell.CELL_TYPE_STRING: // 字符串型
								hm.put(keys[k], cell.getRichStringCellValue()
										.toString());
								break;

							// 公式型
							case HSSFCell.CELL_TYPE_FORMULA:
								// 读公式计算值
								String tempFormula = String.valueOf(cell
										.getNumericCellValue());
								if (tempFormula.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串

									tempFormula = cell.getRichStringCellValue()
											.toString();
								}
								// cell.getCellFormula();读公式
								hm.put(keys[k],tempFormula);
								break;

							// 布尔
							case HSSFCell.CELL_TYPE_BOOLEAN:
								hm.put(keys[k],"" + cell.getBooleanCellValue());
								
								break;

							/* 此行表示该单元格值为空 */
							case HSSFCell.CELL_TYPE_BLANK: // 空值
								hm.put(keys[k],"");
								break;
						
							case HSSFCell.CELL_TYPE_ERROR: // 故障
								hm.put(keys[k],"");
								break;
							default:
								hm.put(keys[k], cell.getRichStringCellValue()
										.toString());
							}
                         }
                         dataList.add(hm);
                     }//上面的是完整的读出一行，可以执行插入操作的说
                 }
             }
         }
		return dataList;
	}
	/**
	 * 导出ListMap到workbook
	 * 
	 * @param sourceList
	 *            ListMap结构的输入
	 * @param titleMap
	 *            控制关键字以及第一行显示的列名
	 * @return workbook
	 */
	public static HSSFWorkbook writeExcel(List<Map<String,Object>> sourceList,Map<String,Object> titleMap){
		// 声明一个表格
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个工作薄
        HSSFSheet sheet = workbook.createSheet();
        
        String [] columMaxString=new String[titleMap.size()] ;//保存该列的最长字符串
        //产生列名
        HSSFRow row = sheet.createRow(0);
        int index=0;
    	for (Map.Entry <String, Object>entry : titleMap.entrySet()) {
    		columMaxString[index]=entry.getValue().toString();//初始化
    		HSSFCell cell = row.createCell(index);
            HSSFRichTextString text = new HSSFRichTextString(entry.getValue().toString());
            cell.setCellValue(text);
            index++;
    	}
        
        // 遍历集合数据，产生数据行
        
        for(int i =0; i<sourceList.size();i++){
        	row = sheet.createRow(i+1);//因为第0行是列名
        	Map<String, Object> map = new HashMap<String, Object>();
        	map=sourceList.get(i);
        	index=0;
        	for (Map.Entry <String, Object>entry : titleMap.entrySet()) {       
        		HSSFCell cell= row.createCell(index);
        		
				//有时为null
				if(map.get(entry.getKey())!=null){
					cell.setCellValue(new HSSFRichTextString(map.get(entry.getKey()).toString()));
					if(map.get(entry.getKey()).toString().length()>columMaxString[index].length()){
						columMaxString[index]=map.get(entry.getKey()).toString();//该列最长的字符串
					}
				}
				else{
					cell.setCellValue("");
				}
				index++;
        	}
        }
        for(int i=0;i<titleMap.size();i++){
        	//根据字节数控制宽度，+2是为了预留空隙
        	sheet.setColumnWidth(i,((columMaxString[i].getBytes().length+2)*256));
        }
		return workbook;
	}
}