批量导入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'" onclick="provisionManage.addProvision()">新增</a></sec:authorize><sec:authorize url="/provision/import"><a href="javascript:void(0)" class="easyui-linkbutton" plain="true"iconcls="ope-import" onclick="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" onclick="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(){// 初始化formvar 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 navigationreturn false;})},doBeforeSerialize_provision:function () {// return false to cancel submitvar 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');// 判断是否要导出txtif (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 {@AutowiredProvisionService provisionService;<span style="white-space:pre"></span>public static final List<String> RESULTLIST = new ArrayList<String>();@RequestMapping(value = "importProvision", method = RequestMethod.POST)@ResponseBodypublic 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-1txtFileName = 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 blocke.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 {//其它类型判断为falsereturn 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()方法将列表中的数据以此插入数据库中。

@Overridepublic List<String> batchInsert(List<ChannelCommPointConfig> listNew) {// TODO Auto-generated method stubList<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;}