java poi上传excel保存数据库

jsp代码

<form id="fileUpload"   method="post" enctype="multipart/form-data">      <input id="file" name="file" type="file"/>      <input type="hidden" name="cid" value="${cid!}"/>      <input class="submit" type="button" value="导入" onclick="importExcel();"/></form>

js

function importExcel(){var file = $("#file").val();    if(file=='') {layer.msg("请选择需上传的文件!");return false;}    if(file.indexOf('.xls')==-1||file.indexOf('.xlsx')==-1){layer.msg("文件格式不正确！");return false;}    var index=layer.msg("正在上传中....",{time:90000000000000000000000});    var option = {         　　 url : '/manage/import/proprietary/batch.do',        　　  type : 'POST',        　　  success : function(json) {         var result = $(json).html();//截取内容(json加了<pre>标签)         var r = JSON.parse(result);           if(r.success){           layer.close(index);   layer.open({        content: '上传成功',        end:function(){        location.reload();}        });             return;           }           　　   layer.msg(r.failedMessage);          },          error: function(json) {           layer.msg("失败");           }       };$("#fileUpload").ajaxSubmit(option);return false;//避免重复提交报错}

后台

controll

public void importMyProprietary(HttpServletResponse response,HttpServletRequest request,@RequestParam(value = "file", required = false) MultipartFile file,String cid) throws IOException{Integer id = CmsUtils.getUser(request).getId();String ip = RequestUtils.getIpAddr(request);String url = request.getRequestURI();try {importMng.importMyProprietary(file,id,ip,Integer.valueOf(cid),url);ResponseUtils.renderSuccessJsonMsg(response);} catch (Exception e) {e.printStackTrace();ResponseUtils.renderFailedJsonMsg(response, e.getMessage());}}

public void importMyProprietary(MultipartFile file,Integer id,String ip,Integer cid,String url) throws Exception {InputStream is = file.getInputStream();Workbook wb = null;try {// excel03版本wb = new XSSFWorkbook(is);} catch (Exception ex) {// excel07版本wb = new HSSFWorkbook(is);}//获得工作表Sheet sheet = wb.getSheetAt(0);// 得到总行数int rowNum = sheet.getLastRowNum();Row headRow = sheet.getRow(0);int colNum = headRow.getPhysicalNumberOfCells();if(colNum!=29){throw new Exception("格式不正确");}for(int i = 1; i <= rowNum; i++){Row row = sheet.getRow(i);//必选项滤空if(row.getCell(1)==null||row.getCell(1).getStringCellValue()==""){continue;}//插入Content表Content content=new Content();content.setTopLevel((byte)0);content.setHasTitleImg(false);content.setRecommend(false);content.setViewsDay(0);content.setCommentsDay((short)0);content.setDownloadsDay((short)0);content.setUpsDay((short)0);content.setSpecial(false);content.setOwen(false);long currentTimeMillis = System.currentTimeMillis();SimpleDateFormat sd=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");String formatTime = sd.format(currentTimeMillis);Date date=sd.parse(formatTime);content.setChannel(channelDao.findById(cid));content.setUser(cmsUserDao.findById(id));content.setType(contentTypeDao.findById(1));content.setModel(cmsModelDao.findById(40));content.setSite(cmsSiteDao.findById(1));content.setSortDate(date);content.setStatus((byte)2);//读取列数据插入//EXT表ContentExt ext=new ContentExt();ext.setReleaseDate(date);ext.setNeedRegenerate(true);ext.setIsMobile(true);ext.setNeedMobileStatic(true);//TXT表ContentTxt txt=new ContentTxt();Map<String, String> m=new HashMap<String, String>();ext.setTitle(row.getCell(1).getStringCellValue());m.put("patentCode", row.getCell(2).getStringCellValue());ext.setAuthor(row.getCell(3).getStringCellValue());m.put("idCard",String.valueOf((int)row.getCell(4).getNumericCellValue()));m.put("school", row.getCell(5).getStringCellValue());m.put("major", row.getCell(6).getStringCellValue());m.put("degree", row.getCell(7).getStringCellValue());m.put("company", row.getCell(8).getStringCellValue());m.put("email", row.getCell(9).getStringCellValue());m.put("contacter", row.getCell(10).getStringCellValue());m.put("job", row.getCell(11).getStringCellValue());m.put("phone", row.getCell(12).getStringCellValue());m.put("address", row.getCell(13).getStringCellValue());m.put("postCode", String.valueOf((int)row.getCell(14).getNumericCellValue()));m.put("owner", row.getCell(15).getStringCellValue());m.put("field", row.getCell(16).getStringCellValue());m.put("patentStatus", row.getCell(17).getStringCellValue());m.put("amount", row.getCell(18).getStringCellValue());m.put("lawStatus", row.getCell(19).getStringCellValue());m.put("hasSample", row.getCell(20).getStringCellValue());m.put("hasCommodity", row.getCell(21).getStringCellValue());m.put("isAppraisal", row.getCell(22).getStringCellValue());m.put("intention", row.getCell(23).getStringCellValue());txt.setTxt(row.getCell(24).getStringCellValue());if(row.getCell(25)!=null)txt.setTxt1(row.getCell(25).getStringCellValue());if(row.getCell(26)!=null)txt.setTxt2(row.getCell(26).getStringCellValue());if(row.getCell(27)!=null)txt.setTxt3(row.getCell(27).getStringCellValue());if(row.getCell(28)!=null)m.put("area", row.getCell(28).getStringCellValue());m.put("patentType", "发明专利");content.setAttr(m);//监听器preSave(content);contentDao.save(content);contentExtMng.save(ext, content);contentTxtMng.save(txt, content);ContentCheck check = new ContentCheck();check.setCheckStep((byte)3);contentCheckMng.save(check, content);contentCountMng.save(new ContentCount(), content);afterSave(content);CmsLog cmsLog=new CmsLog();cmsLog.setUser(cmsUserDao.findById(id));cmsLog.setSite(cmsSiteDao.findById(1));cmsLog.setCategory(3);cmsLog.setTime(date);cmsLog.setIp(ip);cmsLog.setUrl(url);cmsLog.setTitle("增加文章");cmsLog.setContent("id="+content.getId()+";title="+ext.getTitle());cmsLogDao.save(cmsLog);}if(is!=null){is.close();//关闭流}}//监听器private List<ContentListener> listenerList;public void setListenerList(List<ContentListener> listenerList) {this.listenerList = listenerList;}private void preSave(Content content) {if (listenerList != null) {for (ContentListener listener : listenerList) {listener.preSave(content);}}}private void afterSave(Content content) {if (listenerList != null) {for (ContentListener listener : listenerList) {listener.afterSave(content);}}}}

public interface ImportMng {void importMyProprietary(MultipartFile file,Integer id,String ip,Integer cid,String url) throws Exception;}

poi jar包 3.16 需要layer.js

导入的包

import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;


import com.jeecms.cms.dao.main.ChannelDao;
import com.jeecms.cms.dao.main.CmsLogDao;
import com.jeecms.cms.dao.main.CmsModelDao;
import com.jeecms.cms.dao.main.CmsSiteDao;
import com.jeecms.cms.dao.main.CmsUserDao;
import com.jeecms.cms.dao.main.ContentDao;
import com.jeecms.cms.dao.main.ContentTypeDao;
import com.jeecms.cms.entity.main.CmsLog;
import com.jeecms.cms.entity.main.Content;
import com.jeecms.cms.entity.main.ContentCheck;
import com.jeecms.cms.entity.main.ContentCount;
import com.jeecms.cms.entity.main.ContentExt;
import com.jeecms.cms.entity.main.ContentTxt;
import com.jeecms.cms.manager.main.ContentCheckMng;
import com.jeecms.cms.manager.main.ContentCountMng;
import com.jeecms.cms.manager.main.ContentExtMng;
import com.jeecms.cms.manager.main.ContentTxtMng;
import com.jeecms.cms.manager.main.ImportMng;
import com.jeecms.cms.service.ContentListener;