java之通过Excel批量添加数据

1.div

						<div title="批量添加" data-options="closable:false">			   <div id="pnlImport" class="easyui-panel" title=" "					data-options="resizable:true,closed:false,fit:true,tools:'#tools_c$#_2'">							<form id="updata" method="post" enctype="multipart/form-data">													<table>								<tr>																		<td colspan="2">								  									   <input id="uploadExcel" name="file" type="file">									</td>								</tr>															</table>					    </form>					</div>				</div>2.js
		 $('#updata', c$).form('submit', {						url :    '${path}/bu/importCatedata',						dataType: 'text', 						contentType : "application/x-www-form-urlencoded;charset=utf-8",						success : function(result) {							var data = $.parseJSON(result);																	if(data.code == 0){								alert(data.message);												}							else{								alert(data.message);								document.getElementById("uploadExcel").value = "";							}													},						error : function() {													}					}); 3.后台处理工具类与实现
package com.eznext.modul.municipal.util;import java.io.IOException;import java.io.InputStream;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFWorkbook;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;public class ImportExcelUtil {		PRivate final static String excel2003L =".xls";    //2003- 版本的excel	private final static String excel2007U =".xlsx";   //2007+ 版本的excel		/**	 * 描述：获取IO流中的数据，组装成List<List<Object>>对象	 * @param in,fileName	 * @return	 * @throws IOException 	 */	public  List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{		List<List<Object>> list = null;				//创建Excel工作薄		Workbook work = this.getWorkbook(in,fileName);		if(null == work){			throw new Exception("创建Excel工作薄为空！");		} 		Sheet sheet = null;		Row row = null;		Cell cell = null;				list = new ArrayList<List<Object>>();		//遍历Excel中所有的sheet		for (int i = 0; i < work.getNumberOfSheets(); i++) {			sheet = work.getSheetAt(i);			if(sheet==null){continue;}			//遍历当前sheet中的所有行			for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum()+1; j++) {				row = sheet.getRow(j);				if(row==null||row.getFirstCellNum()==j){continue;}								//遍历所有的列				List<Object> li = new ArrayList<Object>();				for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {					cell = row.getCell(y);					li.add(cell);				}				list.add(li);			}		}		work.close();		return list;	}		/**	 * 描述：根据文件后缀，自适应上传文件的版本 	 * @param inStr,fileName	 * @return	 * @throws Exception	 */	public  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{		Workbook wb = null;		String fileType = fileName.substring(fileName.lastIndexOf("."));		if(excel2003L.equals(fileType)){			wb = new HSSFWorkbook(inStr);  //2003-		}else if(excel2007U.equals(fileType)){			wb = new XSSFWorkbook(inStr);  //2007+		}else{			throw new Exception("解析的文件格式有误！");		}		return wb;	}	}
	/**	 * 导入管养数据	 * @param file	 * @param principal	 * @return	 * @throws BiffException	 */	@ResponseBody	@RequestMapping(value = "/bu/importCatedata")	public ActionResult importCatedata(MultipartFile file,Principal principal) throws BiffException {		    List<List<Object>> listob = null; 			try {				listob = new ImportExcelUtil().getBankListByExcel(file.getInputStream(),file.getOriginalFilename());				tmaintainService.BatChadd(listob);				return ActionResult.Succeed();			} catch (Exception e) {				return ActionResult.Failed(e);			}			}