java poi上传excel保存数据库_poi 上传excel-优快云博客

本文链接：https://blog.youkuaiyun.com/sunstubble/article/details/71741660

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>

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;