批量导入Excel中的数据

1：jsp页面

<div id="provisionDatagridToolbar" style="display: none;">
		<sec:authorize url="/provision/add">
			<a href='#' class="easyui-linkbutton"
				data-options="plain:true,iconCls:'ope-add'" οnclick="provisionManage.addProvision()">新增</a>
		</sec:authorize>
		<sec:authorize url="/provision/import">
			<a href="javascript:void(0)" class="easyui-linkbutton" plain="true"
				iconcls="ope-import" οnclick="provisionManage.batchImport()">批量导入(三级品类计提信息)</a>
		</sec:authorize>
		<sec:authorize url="/provision/template">
			<a class="easyui-linkbutton" plain="true" iconcls="ope-download"
				href="<%=request.getContextPath()%>/html/ProvisionTemplate.xlsx">
				下载模板(三级品类计提信息) </a>
		</sec:authorize>
	</div>

<!--  导入三级分类信息对话框开始 -->
		<div id="dlg_importProvision" class="easyui-dialog"
				style="width: 550px; height: 220px; padding: 30px 20px" closed="true" modal="true">
			<form id="fm_importProvision" method="post" enctype="multipart/form-data">
				<table  class="fm-table fm-table-td">
					<tr>
						<div><span style="padding-left: 46px;display: inherit;">提示：请使用模版。文件大小不超过10M。</span></div>
						<span> </span>
						<th>选择文件：</th>
						<td>
							<!-- accept属性限定xlsx格式文件，但是该属性只在FF和chrome生效，IE10以下不生效 -->
							<input id="ProvisionExcelFile" name="filename" type="file" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" style="COLOR: RED;font-size: 15px;">
						</td>
					</tr>
					<tr>
						<th> </th>
						<td>
						<input style="width: 100px;" value="导入Excel" type="submit"/>
						<input style="width: 100px;" value="取消" type="button" οnclick="javascript:$('#dlg_importProvision').dialog('close')"/>
						</td>
					</tr>
				</table>
			</form>
		</div>
		<!--  导入三级分类信息对话框结束 -->
	     <script type="text/javascript" src="<%=request.getContextPath()%>/js/common/jquery.form.js"></script> 
		<script type="text/javascript"	src="<%=request.getContextPath()%>/js/record/provision/provisionDataManage.js">	</script>

2：js页面

var provisionManage = {
		init : function() {
			provisionManage.initDataGrid();	
			provisionManage.formInit();
		},
batchImport:function() {
			$('#fm_importProvision').form('reset');
			$('#dlg_importProvision').dialog('open').dialog('setTitle', '导入三级品类计提信息');
		},

		formInit : function(){
			// 初始化form
			var options_provision = {
					url : uiUtils.rootPath() + '/provision/importProvision',
					dataType : 'json',
					beforeSerialize : provisionManage.doBeforeSerialize_provision,
					success : provisionManage.handelResonse_provision,
					error: provisionManage.errorResonse_provision
				}
			$('#fm_importProvision').ajaxForm(options_provision);	
			$('#fm_importProvision').submit(function() {
				// 提交表单
				//alert(options_provision.url);
				$(this).ajaxSubmit();
				// return false to prevent normal browser submit and page navigation
				return false;
			})
		},

		doBeforeSerialize_provision:function () {
			// return false to cancel submit
			var excelfile = $('#ProvisionExcelFile').val();
			//alert(excelfile);
			if ("" == excelfile || null == excelfile) {
				alert('请选择文件!!!!');
				return false;
			}
			
			var p = excelfile.substr(excelfile.length-5);
			if (p!=".xlsx") {
				alert('文件格式不对！请选择.xlsx格式的文件。');
				return false;
			}
		},

		 handelResonse_provision: function(result) {
			// 提交成功后调用
			 $.messager.show({
					title : '提示',
					show:'show',
					timeout:300
					})
			 
			if (result.success) {
				$('#dlg_importProvision').dialog('close');
				$.messager.show({
					title : '提示',
					msg : result.msg,
					show : 'show', // fade是渐隐，另外两种是“show”和“slide”
					timeout : 3000
				// 持续时间
				});
				$('#dataGridProvision').datagrid('reload');
			} else {
				$.messager.show({
					title : '错误提示',
					msg : result.msg,
					show : 'show', // fade是渐隐，另外两种是“show”和“slide”
					timeout : 3000
				// 持续时间
				});
			}
//			 $('#dataGridProvision').datagrid('reload');
			// 判断是否要导出txt
			if (result.exportTxt) {
				 var url_getTxt = uiUtils.rootPath()+'/provision/exportProvisionTxt';
				 window.location.href = url_getTxt;
				alert("需要导出txt文件");
			}
		},	
		
		errorResonse_provision:function(){
			alert("执行出错！！！");
		}
}
//主函数
$(document).ready(function() {
	provisionManage.init();
})

3：controller页面  ProvisionRecordController.class 

@Controller
@RequestMapping(value = "provision")
public class ProvisionRecordController extends BaseController {

	@Autowired
	ProvisionService provisionService;
<span style="white-space:pre">	</span>public static final List<String> RESULTLIST = new ArrayList<String>();
	
	@RequestMapping(value = "importProvision", method = RequestMethod.POST)
	@ResponseBody
	public Map<String, Object> importProvision(@RequestParam("filename") MultipartFile file) {
		ProvisionRecordController.RESULTLIST.clear();
		Map<String, Object> resu = new HashMap<String, Object>();
		String message = "";
		String name = file.getOriginalFilename();
		System.out.println(name);
		long size = file.getSize();
		if ((name == null || name.equals("")) && size == 0) {
			message = "文件不正确。";
			resu.put("success", false);
			resu.put("msg", message);
			return resu;
		}
		try {
			InputStream in = file.getInputStream();
			ResovleProvisionExcel rpe = new ResovleProvisionExcel();
			ResovleProvisionExcel.ERRORLIST_PROVISION.clear(); // 每次导入前清空错误列表。
			boolean exportTxt = false; // 是否需要导出txt
			//通过处理，获取EXCEL中每列数据，存入列表中
			Result<List<ChannelCommPointConfig>> resovled = rpe.resolveExcelToProvision(in);
			// 得到错误列表
			List<String> errorList = ResovleProvisionExcel.ERRORLIST_PROVISION;
			if (errorList.size() > 0) {
				exportTxt = true;
			}
			List<String> resultList = new ArrayList<String>();
			if (resovled.isSuccess()) {
				List<ChannelCommPointConfig> provisionList = resovled.getObj();
				//将获取到的ChannelCommPointConfig信息列表进行导入数据库操作
				Result<List<String>> result=provisionService.importProvisionInfo(provisionList);
				if (result.isSuccess()) {
					List<String> resList = result.getObj();
					if (resList.size() > 1) { // 长度大于1，表示有错误信息
						exportTxt = true;
					}
					resultList.addAll(resList);
					String successNum = resList.get(0);
					successNum = "成功导入【" + successNum + "】条数据。";
					resultList.set(0, successNum); // 把第一行的数字替换成字符串
					resu.put("success", true);
					resu.put("exportTxt", exportTxt); // 如果需要导出txt，则在结果中设置标记位。再由前台请求导出。
					resu.put("msg", successNum);
				} else {
					message = "数据导入出错。";
					resultList.add(0, "成功导入【0】条数据。");
					resu.put("success", false);
					resu.put("exportTxt", exportTxt);
					resu.put("msg", message);
					// 这里不return，导入出错也可能在需要导出先前的解析错误到txt
				}
			} else { // 解析错误，只有表头校验错误这种情况会返回校验错误
				resu.put("success", false);
				resu.put("msg", resovled.getErrorMessage());
				return resu;
			}
			resultList.add(0, name); // 实际效果是insert到第一行,把文件名放到第一行
			resultList.addAll(errorList); // 放入解析时的错误
			ProvisionRecordController.RESULTLIST.addAll(resultList);
		} catch (Exception e) {
			resu.put("success", false);
			resu.put("msg", "系统错误。本次所有数据未导入。");
			e.printStackTrace();
		}
		return resu;
	}
	
	
	
	@RequestMapping(value = "exportProvisionTxt", method = RequestMethod.GET)
	public void exportProvisionTxt(HttpServletRequest request, HttpServletResponse response) {
		List<String> resultList = ProvisionRecordController.RESULTLIST;
		String name = resultList.get(0); // 第一行为文件名
		// =============== 以下内容为导出结果到txt中 ===============//
		String nameMain = name.substring(0, name.lastIndexOf("."));
		String txtFileName1 = DateFormatUtils.format(new Date(), "yyyyMMddHHmmss");// .concat(nameMain);
		txtFileName1 = nameMain.concat(txtFileName1);
		txtFileName1 = txtFileName1.concat(".txt");
		String txtFileName = null;
		
		try {
			// 判断浏览器来源
			if (request.getHeader("user-agent").indexOf("MSIE") != -1) {
				// 如果是IE，则使用utf-8编码
				txtFileName = java.net.URLEncoder.encode(txtFileName1, "utf-8");
			} else {
				// 如果是FF、chrome，就是用ISO8859-1
				txtFileName = new String(txtFileName1.getBytes("utf-8"), "iso-8859-1");
			}
			
			response.setContentType("text/plain"); // 设置导出内容的格式
			response.addHeader("Content-Disposition", "attachment;filename=" + txtFileName);// filename指定默认的名字
			BufferedOutputStream buff = null;
			StringBuffer write = new StringBuffer();
			String enter = "\r\n";
			ServletOutputStream outSTr = null;
			outSTr = response.getOutputStream();// 建立
			buff = new BufferedOutputStream(outSTr);
			resultList.add(2, "=======以下为错误信息=======");
			for (String string : resultList) {
				write.append(string);
				write.append(enter);
			}
			buff.write(write.toString().getBytes("UTF-8"));
			buff.flush();
			buff.close();
			outSTr.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

4：ResovleProvisionExcel

package com.sf.settlement.scos.web.controller.record.provision;

import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.sf.heike.common.domain.Result;
import com.sf.settlement.scoss.domain.record.provision.dto.ChannelCommPointConfig;

/**
 * useto 解析三级品类信息excel文件
 * 
 */
public class ResovleProvisionExcel {
	/** 
	 * 三级品类信息：每一行为5格
	 * 每一行单元格的数量，由于模板为5个栏目，固定为5。
	 * 避免在第二格为空时，无法进入错误处理	 
	 */
	public static Integer CELLCOUNT_PROVISION = 5;
	/**
	 * 用来存放解析失败的数据
	 * 注：使用static final设定的静态对象本身不能改变，但是这种容器对象中的值可以改变
	 */
	public static final List<String> ERRORLIST_PROVISION = new ArrayList<String>();
	
	/**
	 * 对应三级品类信息导入模版的表头
	 */
	private static final String[] TITLELIST_PROVISION = {"渠道编码",
		"渠道名称",
		"三级品类编码",
		"三级品类名称",
		"计提点数"};
	
	/**
	 * 校验表头：用于三级品类信息的导入
	 * @param xssfRow 表头行
	 * @return
	 */
	private boolean validateTitle(XSSFRow xssfRow) {
		// 当前行单元格的数量
		int cells_count = xssfRow.getLastCellNum();
		int title_length =  TITLELIST_PROVISION.length;
		if (cells_count != title_length) {
			return false;
		}
		for(int cellNum=0; cellNum < cells_count; cellNum++) {
			// 获取当前单元格
			XSSFCell xssfCell = xssfRow.getCell(cellNum);
			/** 
			 * 为了处理：Excel异常Cannot get a text value from a numeric cell 
			 * 将所有列中的内容都设置成String类型格式 
			 */
			if(xssfCell != null){  
				xssfCell.setCellType(Cell.CELL_TYPE_STRING);  
			}
//			if(!xssfCell.getStringCellValue().equals(TITLELIST_PROVISION[cellNum])){
//				return false;
//			}
		}
		return true;
	}
	
	/**
	 * 校验普通文本单元格是否有效
	 * 1.不能为空 2.只能为String类型或者NUMERIC类型（BLANK不行）
	 * @param xssfCell
	 * @return
	 */
	private boolean validateStringCell(XSSFCell xssfCell) {
		if (null != xssfCell && ((xssfCell.getCellType() == Cell.CELL_TYPE_STRING )||(xssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC ))) {	// 单元格为null或者为空单元格
			xssfCell.setCellType(Cell.CELL_TYPE_STRING);
			String value = xssfCell.getStringCellValue().trim();	// trim后如果为空
			if ("".equals(value)){
				return false;
			} else {
				return true;
			}
		} else {		//其它类型判断为false
			return false;
		}
	}
	
	/**
	 * 校验计提点数的有效性
	 * 1.空  则输入计提点数为0
	 * 2.数值类型  则输入的计提点数为0到100之间的数（可有两位小数）
	 * @param xssfCell
	 * @return -1 有误  1为0  2正确
	 */
	private int validatePoint(XSSFCell xssfCell) {
		if (null == xssfCell || (xssfCell.getCellType() == Cell.CELL_TYPE_BLANK ||xssfCell.getRawValue()=="")) {
			return 1;
		} else if (xssfCell.getCellType()==Cell.CELL_TYPE_NUMERIC) {
			return 2;
		}
		else {
			return -1;
		}
	}
	/**
	 * 解析Excel到三级品类信息
	 * @param in
	 * @return
	 * @throws Exception
	 */
	public Result<List<ChannelCommPointConfig>> resolveExcelToProvision(InputStream in) throws Exception {
		Result<List<ChannelCommPointConfig>> result = new Result<List<ChannelCommPointConfig>>(true);
		List<ChannelCommPointConfig> list = new ArrayList<ChannelCommPointConfig>();
		// 工作簿
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(in);
		// 获取当前工作表
		XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
		// 验证表头
		XSSFRow xssfRow_title = xssfSheet.getRow(1);
		if(!validateTitle(xssfRow_title)) {
			result.setErrorMessage("表头验证错误。");
			result.setSuccess(false);
			return result;
		}
		
		// 当前sheet中有多少行
		int rows_count = xssfSheet.getLastRowNum();
		int rowNum = 0;
		// 循环行，获取数据
		for(rowNum = 2;rowNum <= rows_count; rowNum++) {
			boolean hasErrorInThisRow = false;
			// 获取当前行,如果有空行，就不在往下解析
			XSSFRow xssfRow = xssfSheet.getRow(rowNum);
			if (null == xssfRow) {
				result.setObj(list);
				result.setSuccess(true);
				return result;
			}
			/** 获取首个单元格：如果收个单元格为空，则不再往下查找, 行首不能为空,要求不能有空行 */
			XSSFCell firstCell = xssfRow.getCell(0);
			
			if (!validateStringCell(firstCell)){	// 检查第一个单元格是否有效，必须为文本类型
				result.setObj(list);
				result.setSuccess(true);
				return result;
			}
			
			ChannelCommPointConfig ccpc = new ChannelCommPointConfig();
			// 取第一格内容作为当前行的标记，用于当前行有错时指示错误
			String CurrentRow = xssfRow.getCell(0).getStringCellValue().trim();
			// 当前行单元格的数量
			//int cells_count = xssfRow.getLastCellNum();
			int cells_count = CELLCOUNT_PROVISION;
			//循环列 开始
			for(int cellNum=0; cellNum < cells_count; cellNum++) {
				XSSFCell xssfCell = xssfRow.getCell(cellNum);
				switch(cellNum){
				case 0:		// 渠道编码
					if (validateStringCell(xssfCell)) {
						ccpc.setChannelCode(xssfCell.getStringCellValue().trim());
					}else{
						ResovleProvisionExcel.ERRORLIST_PROVISION.add(CurrentRow+ "\t渠道编码有误。");
						hasErrorInThisRow = true;
					}
					break;
				case 1:		// 渠道名称
					if (validateStringCell(xssfCell)) {
						ccpc.setChannelName(xssfCell.getStringCellValue().trim());
					}else{
						ResovleProvisionExcel.ERRORLIST_PROVISION.add(CurrentRow+ "\t渠道名称有误。");
						hasErrorInThisRow = true;
					}
					break;
				case 2:		// 三级品类编码
					if (validateStringCell(xssfCell)) {
						ccpc.setThreeCategoryCode(xssfCell.getStringCellValue().trim());
					}else{
						ResovleProvisionExcel.ERRORLIST_PROVISION.add(CurrentRow+ "\t三级品类编码有误。");
						hasErrorInThisRow = true;
					}
					break;
				case 3:		// 三级品类名称
					if (validateStringCell(xssfCell)) {
						ccpc.setThreeCategoryName(xssfCell.getStringCellValue().trim());
					}else{
						ResovleProvisionExcel.ERRORLIST_PROVISION.add(CurrentRow+ "\t三级品类名称有误。");
						hasErrorInThisRow = true;
					}
					break;
				case 4:		// 计提点数
					if (validatePoint(xssfCell)==1) {
						BigDecimal bg1=new BigDecimal(0);
						
						ccpc.setCommissionPoint(bg1);
					}else if(validatePoint(xssfCell)==2)
					{
						double sPoint = xssfCell.getNumericCellValue();
						if(sPoint>=0 && sPoint<=100)
						{
							DecimalFormat df = new DecimalFormat("#.00");
							BigDecimal bg2=new BigDecimal(df.format(sPoint));
							ccpc.setCommissionPoint(bg2);
						}
						else{
							ResovleProvisionExcel.ERRORLIST_PROVISION.add(CurrentRow+ "\t计提点数有误。");
							hasErrorInThisRow = true;
						}
					}					
					else{
						ResovleProvisionExcel.ERRORLIST_PROVISION.add(CurrentRow+ "\t计提点数有误。");
						hasErrorInThisRow = true;
					}
					break;
				default:
					continue;
				}
				
				if (hasErrorInThisRow) {
					break;   // 这个break是跳出包含它的for循环，也就是（某一个Cell出错后）从Row中的Cell循环中跳出，继而执行下面的语句
				}
			}
			//循环列 结束
			
			if (hasErrorInThisRow == true) {
				continue;
			} else {
				list.add(ccpc);
			}
		}
		//循坏行 结束
		result.setObj(list);
		result.setSuccess(true);
		return result;
	}	
}

5： 后台通过provisionService.importProvisionInfo(provisionList)方法 调用manager层的batchInsert()方法将列表中的数据以此插入数据库中。

@Override
	public List<String> batchInsert(List<ChannelCommPointConfig> listNew) {
		// TODO Auto-generated method stub
		List<String> result = new ArrayList<String>();
		if (null != listNew) {
			int successCount = 0;
			for (ChannelCommPointConfig ccpc : listNew) {
				int added = channelCommPointConfigMapper.insert(ccpc);
				if (added >0) {
					successCount++;
				} else {
					result.add(ccpc.getChannelCode()+"==="+ccpc.getChannelName()+"\t添加失败。");
					continue;
				}
			}
			// 把成功的个数放在首位
			result.add(0, String.valueOf(successCount));
		}
		return result;
	}