poi导入 Excel文件

最新推荐文章于 2024-05-14 06:33:05 发布

mfc1561

最新推荐文章于 2024-05-14 06:33:05 发布

阅读量145

点赞数

分类专栏： poi 文章标签： poi 批量添加模板验证

本文链接：https://blog.csdn.net/mfc1561/article/details/80351509

版权

poi 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

后台代码：

/**
	 * 执行导入操作
	 * 
	 * @return
	 */
	@RequestMapping(value = "/console/uploadFile/save")
	public String upload(@RequestParam(value = "ceshi1", required = false) String ceshi1,
			@RequestParam(value = "type", required = false) String type,
			@RequestParam(value = "uploadify", required = false) MultipartFile uploadify,
			@RequestParam(value = "state", required = false) String state,
			@RequestParam(value = "commCode", required = false) String commCode, Model model,
			HttpServletRequest request) {
		User currentUser = getCurrentUser(request);
		/*String user=currentUser.getAccount();
		 * String path = request.getSession().getServletContext().getRealPath("");
		 * */
		
		String path = "D:\\upload";
		String newName = UUID.randomUUID().toString()+ uploadify.getOriginalFilename().substring(uploadify.getOriginalFilename().lastIndexOf("."));
		File targetFile = new File(path, newName);
		if (!targetFile.exists()) {
			targetFile.mkdirs();
		}
		
		// 保存
		try {
			/* 根据社区编号和模板ID查询M0_list表，有记录则返回页面，询问是否上传 */

			uploadify.transferTo(targetFile);
		} catch (Exception e) {
			e.printStackTrace();
		}
		long fileSize = uploadify.getSize();
		if (fileSize>5242880) {
			Map<String, Object> msg =new HashMap<String, Object>();
			msg.put("error", "最大上传文件为5M");
			List<Information> informationList= informationService.queryList();
			model.addAttribute("informationList", informationList);
			model.addAttribute("msg", msg);
			model.addAttribute("ceshi1", ceshi1);
			return "/uploadFile/shujuhuizong";
		}
		/*model.addAttribute("commCode", commCode);*/
		// 模板1 社区基本情况表
		
			// 校验社区编号、列名、值
			Map<String, Object> msg = ContentService.importExcel(currentUser,state, ceshi1, targetFile,newName);
			
			model.addAttribute("msg", msg);
		

		
		List<Information> informationList= informationService.queryList();
		model.addAttribute("informationList", informationList);
		return "/uploadFile/shujuhuizong";

	}

service实现

package com.xingkr.sys.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.xingkr.sys.domain.M0_list;
import com.xingkr.sys.domain.Content;
import com.xingkr.sys.domain.User;
import com.xingkr.sys.mapper.ContentMapper;
import com.xingkr.sys.service.Column_informationService;
import com.xingkr.sys.service.ContentService;
import com.xingkr.sys.util.PageInfoUtil;
import com.xingkr.sys.util.PageInfoUtil2;

@Service
public class ContentServiceImp implements ContentService {
	@Autowired
	private Column_informationService column_informationService;
	@Autowired
	private ContentMapper communityMapper;
	@Autowired
	private com.xingkr.sys.service.IM0_listService IM0_listService;
	// 开始导入数据的行数
	private static final int STARTROW = 3;

	// 社区编号
	private static final String SQBH = "社区名称:";

	// 序号
	private static final String XH = "编号";
	// 数字格式，防止长数字成为科学计数法形式，或者int变为double形式
	private DecimalFormat df = new DecimalFormat("0");

	public Map<String, Object> importExcel(User user, String state, String ceshi1, File targetFile, String newName) {
		Map<String, Object> map = new HashMap<String, Object>();
		// 导入数据
		List<Content> list = new ArrayList<Content>();
		Workbook book = null;

		/* 创建一个读取文件的流 */
		InputStream input = null;
		try {
			input = new FileInputStream("D:\\upload\\" + newName);
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		}

		/* 如果是低版本创建低版本对象 */
		if (newName.endsWith(".xls")) {
			try {
				book = new HSSFWorkbook(input);
			} catch (Exception e) {
				map.put("error", "上传失败");
				return map;
			}
		}
		/* 如果是高版本创建高版本对象 */
		if (newName.endsWith(".xlsx")) {
			try {
				book = new XSSFWorkbook(input);
			} catch (Exception e) {
				map.put("error", "上传失败");
				return map;
			}
		}
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;

		sheet = book.getSheetAt(0);

		// 校验第0行，c0[0]为社区编号，c0[1]为值
		row = sheet.getRow(1);
		// 社区编码
		cell = row.getCell(1);
		// 社区名称
		String commCode = cell.getStringCellValue().toString().replace(" ", "");
		/* 后台验证社区编码 */
		/*
		 * Pattern p = null; Matcher m = null; boolean flg = true; p =
		 * Pattern.compile("[0-9]{2}[A-Za-z]{3}[0-9]{4}"); m =
		 * p.matcher(commCode); flg = m.matches(); if (!flg) { map.put("error",
		 * "社区编码格式错误！"); return map; }
		 */
		String uuid = UUID.randomUUID().toString();
		if (ceshi1.equals("1")) {
			map.put("code", "01tzq0001");
		}
		if (ceshi1.equals("2")) {
			map.put("code", "01tzq0002");
		}
		map.put("ceshi1", ceshi1);
		/* 验证第二行第一列是否为社区名称 */
		if (row.getCell(0) != null && SQBH.equals(row.getCell(0).getStringCellValue().toString().trim())) {
			if (!row.getCell(1).getStringCellValue().isEmpty()) {
				// 集合转数组
				int Tab_id = Integer.valueOf(ceshi1);
				List<String> list2 = column_informationService.getColumnNames(Tab_id);

				// String[] colsList = (String[]) list2.toArray(new
				// String[list2.size()]);
				// 检验第1行的各个列名
				for (int k = 0; k < list2.size(); k++) {
					String tmpStr = (String) list2.get(k);
					if (sheet.getRow(2).getCell(k) == null
							|| !tmpStr.equals(sheet.getRow(2).getCell(k).getStringCellValue().trim())) {
						int z = k + 2;
						map.put("error", "上传失败,第三行第" + (z - 1) + "列"
								+ sheet.getRow(2).getCell(k + 1).getStringCellValue().trim() + "模板错误");
						return map;
					}
				}
				/* 查询是否上传过 */
				int flagNum = 0;
				if (!ceshi1.isEmpty()) {
					int pid = Integer.valueOf(ceshi1);
					if (pid == 1) {
						String data = getDate(row.getCell(7));
						flagNum = IM0_listService.selectIM0_list("01tzq0001", pid, data);
					}
					if (pid == 2) {
						String data = getDate(row.getCell(4));
						flagNum = IM0_listService.selectIM0_list("01tzq0002", pid, data);
					}
				}
				String f1="应急照明";
				for (int i = STARTROW; i < sheet.getLastRowNum(); i++) {
					Content personnel = new Content();
					int j = i + 1;
					row = sheet.getRow(i);
					cell = row.getCell(0);
					if (ceshi1.equals("1")) {
						// 物资大类
						if (!row.getCell(0).getStringCellValue().replace(" ", "").equals("")) {
							f1 = row.getCell(0).getStringCellValue().replace(" ", "");
						}
						// 物资名称
						String f2 = row.getCell(1).getStringCellValue().replace(" ", "");
						// 数量
						String f3 = df.format(row.getCell(2).getNumericCellValue());
						// 价格
						String f4 = df.format(row.getCell(3).getNumericCellValue());
						// 生产日期
						String f5 = getDate(row.getCell(4));
						// 储备日期
						String f6 = getDate(row.getCell(5));
						// 储存地点
						String f7 = row.getCell(6).getStringCellValue().replace(" ", "");
						// 社区内的居民人数
						/*
						 * String residentNum =
						 * df.format(row.getCell(6).getNumericCellValue()).
						 * replace(" ", ""); if (row.getCell(6).getCellType()
						 * ==0 || row.getCell(6).getCellType() != 3) { if
						 * (!residentNum.equals("")) { int residentNum1 =
						 * Integer.valueOf(residentNum);
						 * personnel.setResidentNum(residentNum1); } } else {
						 * map.put("error",
						 * "上传失败,第"+j+"行第7列社区内的居民人数"+residentNum+"格式错误"); return
						 * map; }
						 */

						if (flagNum > 0) {
							personnel.setRemark(commCode);
						}
						personnel.setList_Id(uuid);
						personnel.setF1(f1);
						personnel.setF2(f2);
						personnel.setF3(f3);
						personnel.setF4(f4);
						personnel.setF5(f5);
						personnel.setF6(f6);
						personnel.setF7(f7);
						personnel.setCode("01tzq0001");
						list.add(personnel);

					}
					if (ceshi1.equals("2")) {
						if (!df.format(cell.getNumericCellValue()).equals("0")
								&& !df.format(cell.getNumericCellValue()).equals("")) {
							/*//编号
							String f1 = df.format(row.getCell(0).getNumericCellValue());*/
							//类别
							f1 = row.getCell(1).getStringCellValue().replace(" ", "");
							// 排查内容
							String f2 = row.getCell(2).getStringCellValue().replace(" ", "");
							// 排查人
							String f3 = row.getCell(3).getStringCellValue().replace(" ", "");
							// 排查时间
							String f4 = getDate(row.getCell(4));
							// 备注
							String f5 = row.getCell(5).getStringCellValue().replace(" ", "");

							if (flagNum > 0) {
								personnel.setRemark(commCode);
							}
							personnel.setList_Id(uuid);
							personnel.setF1(f1);
							personnel.setF2(f2);
							personnel.setF3(f3);
							personnel.setF4(f4);
							personnel.setF5(f5);
							personnel.setCode("01tzq0002");
							list.add(personnel);
						} else {
							map.put("error", "上传失败,第" + j + "行第1列序号值不能为空或0");
							return map;
						}
					}
				}

				// 批量插入
				int successNum = communityMapper.insertMore(list);
				/**
				 * 保存导入信息
				 */
				M0_list mo = new M0_list();
				// 社区编码
				if (ceshi1.equals("1")) {
					mo.setCode("01tzq0001");
				}
				if (ceshi1.equals("2")) {
					mo.setCode("01tzq0002");
				}
				// 社区名称
				mo.setComm_name(commCode);
				// ID
				mo.setId(uuid);
				// 创建时间日期转String
				SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
				String CreateDate = formatter.format(new Date());
				mo.setCreatTime(CreateDate);
				// 填表时间
				Date date1;
				if (ceshi1.equals("1")) {
					String data = getDate(sheet.getRow(1).getCell(7));
					try {
						date1 = formatter.parse(data);
						mo.setFill_date(date1);
					} catch (ParseException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}

				if (ceshi1.equals("2")) {
					String data = getDate(sheet.getRow(1).getCell(4));
					try {
						date1 = formatter.parse(data);
						mo.setFill_date(date1);
					} catch (ParseException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}

				// 模板ID
				if (!ceshi1.isEmpty()) {
					int ceshi = Integer.valueOf(ceshi1);
					mo.setPid(ceshi);
				}
				// 模板名称
				mo.setPname(sheet.getRow(0).getCell(0).getStringCellValue());
				// 创建人
				mo.setCreator(user.getAccount());

				IM0_listService.insertIM0_list(mo);

				if (flagNum > 0) {
					map.put("error", "1");
					return map;
				} else {
					map.put("success", "导入成功");
					return map;
				}

			} else {
				map.put("error", "上传失败,第二行第二列社区名称不能为空");
				return map;
			}
		} else {
			map.put("error", "上传失败,第二行第一列" + SQBH + "列名错误");
			return map;
		}
	}

	// 将excel中时间格式字段进行处理

	private String getDate(Cell cell) {
		DecimalFormat df = new DecimalFormat("#");
		if (cell == null) {
			return "";
		}
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_NUMERIC:
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

				return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
			}

			return df.format(cell.getNumericCellValue());
		case HSSFCell.CELL_TYPE_STRING:
			return cell.getStringCellValue();
		case HSSFCell.CELL_TYPE_FORMULA:
			return cell.getCellFormula();
		case HSSFCell.CELL_TYPE_BLANK:
			return "";

		}
		return "";

	}

	@Override
	public List<Content> findAllContent(Map<String, Object> map, PageInfoUtil pageInfo) {
		if (pageInfo != null) {
			map.put("currentRecord", pageInfo.getCurrentRecord());
			map.put("pageSize", pageInfo.getPageSize());
		}

		return communityMapper.findAllContent(map);
	}

	public int count(Map<String, Object> map) {
		// TODO Auto-generated method stub
		return communityMapper.count(map);
	}

	@Override
	public Content findAllM1(String code) {
		// TODO Auto-generated method stub
		return communityMapper.findAllM1(code);
	}

	@Override
	public void deleteContent(String code) {
		// TODO Auto-generated method stub
		communityMapper.deleteContent(code);
	}

	@Override
	public void updataContent(String code) {
		// TODO Auto-generated method stub
		communityMapper.updataContent(code);
	}
	/**
	 * 查询模板详情
	 */
	@Override
	public List<Content> findContentById(Map<String, Object> map,PageInfoUtil2 pageInfo) {
		if (pageInfo != null) {
			map.put("currentRecord", pageInfo.getCurrentRecord());
			map.put("pageSize", pageInfo.getPageSize());
		}
		return communityMapper.findContentById(map);
	}
	/**
	 * 查询模板详情分页
	 */
	@Override
	public Integer findContentcount(Map<String, Object> map) {
		return communityMapper.findContentcount(map);
	}

}

前台jsp页面

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core_rt"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/functions" prefix="fn"%>
<!DOCTYPE html>
<html>
<head>
  <meta charset="UTF-8">
  <title></title>
  <link rel="stylesheet" href="${pageContext.request.contextPath}/css/style.css" />
<script src="${pageContext.request.contextPath}/js/jquery-1.11.3.min.js" type="text/javascript"></script>
<script type="text/javascript" src="${pageContext.request.contextPath}/site/js/navigation.js"></script>
<script type="text/javascript" src="<c:out value="${pageContext.request.contextPath}"/>/js/My97DatePicker/WdatePicker.js"></script>
<script type="text/javascript" src="${pageContext.request.contextPath}/js/layer/layer.js"></script>	
<script type="text/javascript">
/* 模板下载 */
function uplodaFile() {
	var vs = $('[name="ceshi1"] option:selected').val();
	window.location.href = '${pageContext.request.contextPath}/download?id=' + vs;
}
/* 上传验证 */
$("#uploadify").click(function() {
	var ce = $("#ceshi1").find("option:selected").text();
	$("#ceshi1").html('<option>' + ce + '</option>');
})

function File() {
	var obj = document.getElementById('uploadify');

	if (obj.value == '') {
		layer.msg('请选择要上传的文件', {
			time : 10000
		});
		return false;
	}
	var stuff = obj.value.match(/^(.*)(\.)(.{1,8})$/)[3];
	var type = $("#type").val();
	if (type == 'word') {
		if (stuff != 'doc') {
			layer.msg('文件类型不正确，请选择.doc文件', {
				time : 10000
			});
			return false;
		}
	} else if (type == 'excel') {
		if (stuff != 'xls' && stuff != 'xlsx') {
			layer.msg('文件类型不正确，请选择.xls或.xlsx文件', {
				time : 10000
			});
			return false;
		}

	}
	$("#articleForm").submit();
};

/* 信息提示 */
$(function() {

	var param = $("#error").val() + $("#success").val();
	var code = $("#code").val();
	var ceshi1 = $("#ceshi1").val();
	//验证是否上传过模板
	if (param == "1") {
		layer
				.confirm(
						"<font style='color: black;'>你已经上传过此模板,继续上传将覆盖上个模板,你确认要继续上传吗？</font>",
						{
							icon : 3,
							title : '提示'
						},
						function(index) {
							layer.close(index);
							var url = "${pageContext.request.contextPath}/console/uploadFile/Verification?code="
									+ code + "&ceshi1=" + ceshi1;
							window.location = url;//这句话的作用就是提交表单
						});
	} else if (param != null && param != '') {
		layer.msg(param, {
			time : 10000
		});
	}

});
function changese() {
	var vs = $('[name="ceshi1"] option:selected').val();
	//alert(vs);
	$("#image").html("");
	$("#image")
			.html(
					"<img src='${pageContext.request.contextPath}/images/uploadModel/m"+vs+".png' />");
}


function comethis(){
	window.location.href='${pageContext.request.contextPath}/index';
}

/* 动态菜单栏 */
$(function() {
	$.ajax({
	type : 'post',
	url : '${pageContext.request.contextPath}/title',
	success : function(data) {
		if (data != null && data.length > 0) {
			var $ul = $("#newsUl");
			$ul.empty();
			var html = "";
			var html2 = "";
			
			var cn = "";
			var cp = "";
			for ( var i in data) {
				cn = data[i].channel_name;
				cp = data[i].channel_path;
				if(cn == "应急广播"){
					html2 += "<a href='${pageContext.request.contextPath}/channel/"+cp+"' class='menu2'>"+cn+"</a>";
				}else if(cn == "救助系统"){
					html2 += "<a href='${pageContext.request.contextPath}/channel/"+cp+"' class='menu3'>"+cn+"</a>";
				}else if(data[i].channel_type!=null){
					html += "<li>";
					html += "<a id='"+cp+"' href='${pageContext.request.contextPath}/channel/"+cp+"'>";
					html += "<span class='s0'><img src='${pageContext.request.contextPath}/images/"+cp+".png'></span>";
					html += "<span class='s1'>" + cn + "</span>";
					html += "<span class='s2'>" + cp + "</span>";
					html += "</a>";
					html += "</li>";
					}
				}
				$ul.append(html);

				var $ul2 = $(".nav_rig");
				$ul2.empty();
				$ul2.append(html2);
			}
			var param = "${topChannel.channel_path}"
					+ "${channel_path}";
			if (param == null || param == '') {
				navigation.select("首页");
			} else {
				navigation.select(param);
			}
		},
		error : function() {
			return;
		}
	});
})
function comethis(){
		window.location.href='${pageContext.request.contextPath}/index';
	}
</script>
</head>
<body>
  <div class="head">
    <div class="header2">
    <form action="${pageContext.request.contextPath}/search">
      <div class="web_width">
		<img class="logo2" src="${pageContext.request.contextPath}/images/logo2.png" alt="返回首页" />
        <div class="top_right">
          <div class="top_search">
            <input type="text" class="in1" name="name" id="search" value="${name}" placeholder="文档名称搜索">
          	<input type="submit" class="in2" name="searchBtn" value="">

          </div>
          <div class="top_a">
            <a href="#">${userKey_user.name}</a>
          </div>
			<span class="top_span">   |  <a href="${pageContext.request.contextPath}/logout">退出</a></span>
        </div>
      </div>
      </form>
    </div>
    <div class="navbar">
      <div class="web_width">
        <ul id="param">
          <li>
            <a href="${pageContext.request.contextPath}/index">
              <span class="s0"><img src="${pageContext.request.contextPath}/images/nav13.png"></span>
              <span class="s1">首页</span>
              <span class="s2">Home</span>
            </a>
          </li>
         <div id="newsUl"></div>
         
          <div class="clear"></div>
        </ul>
        <div class="nav_rig"></div>

      </div>

    </div>
  </div>
  <div class="main2">
    <div class="web_width">
      <div class="dqwz">

        <div class="l">
          <!-- 当前位置 ：<a href="#">首页</a> > <a href="#">社区概况</a> > <a href="#">政策法规</a> > 国家级文件 --> 
        </div>


      </div>
      <div class="main_content">
        <div class="title3">
          社区信息上传

        </div>
<form id="articleForm" action="${pageContext.request.contextPath}/console/uploadFile/save" method="post" enctype="multipart/form-data">
       <div class="content5 pb30">
         <div class="sel_leixing">
           <div class="sel fl">
             <span >文件类型</span>
             <!-- <label for="ceshi1"></label>  -->
             <select name="type" id="type" style="cursor: pointer;" >
					<option value="excel" selected="selected">excel</option>
			</select> 

           </div>
           <div class="sel fr">
             <span>模板类型</span>
             <!-- <label for="ceshi1"></label>  -->
             <select name="ceshi1" id="ceshi1" οnchange="changese()" style="cursor: pointer;" >
					<c:forEach items="${informationList}" var="il">
						<option
							<c:if test="${il.id eq ceshi1 }">selected="selected"</c:if>
							value="${il.id }">${il.name } </option>
					</c:forEach>
			</select>

           </div>


         </div>
         <div class="scwj">
           <div class="fl">
			<input type="hidden" value="${msg.error }" id="error" />
			<input type="hidden" value="${msg.success }" id="success" />
			<input type="hidden" value="${msg.code }" id="code" />
			<input type="hidden" value="${msg.ceshi1 }" id="ceshi1" />
             <input type="button" class="sc_btn" value="点击这里上传文件"  ><br>
             <input type="file" class="sc_fl" value="点击这里上传文件" name="uploadify" id="uploadify" style="cursor: pointer;">
             请上传扩展名以.xls结尾的文件.最大上传文件为5M

           </div>
           <div class="fr">
             <a href="#" class="sc" οnclick="File()">上传文件</a>
             <a href="#" class="xz" οnclick="uplodaFile()">下载模版</a>
           </div>
           <div class="clear"></div>
         </div>
         
          <div class="tab2" id="image"  align="center" >
    		<img src="${pageContext.request.contextPath}/images/uploadModel/m1.png" />
         </div>
       </div>
       </form>
      </div>
    </div>

  </div>
  <div class="footer2">
    <div>Copyright©2017-2018  　北京金广通科技有限公司　 公司地址：北京市石景山区八大处路45号　电话：010-50916568　　版权所有</div>
  </div>


<script>
  $(function(){
    $('#js_left_menu h4').on('click',function(){
      $('#js_left_menu dl').hide();
      $(this).next().show();
    })
  });
</script>
</body>
</html>

mfc1561

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
poi导入 Excel文件

后台代码：/** * 执行导入操作 * * @return */ @RequestMapping(value = "/console/uploadFile/save") public String upload(@RequestParam(value = "ceshi1", required = false) String ceshi1, @RequestParam(v...
复制链接

扫一扫