Excel 导入导出 以及下载模板小白板

前端部分特别简单

<!DOCTYPE html>
<html lang="en">

<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<!-- Meta, title, CSS, favicons, etc. -->
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title></title>
 <style>
 .x_content {
    float: left;
    clear: both;
}
 </style>
</head>
<body class="nav-md">
	<!-- page content -->
	<div class="">
		<div class="clearfix"></div>
		<div class="row">
			<div class="col-md-12 col-sm-12 col-xs-12">
				<div class="x_panel">
					<div class="x_content">
						<div class="col-sm-8">
							<div class="input-group"  style="width: 300px;margin-top:5px">
								<input type="text" id="planName" placeholder="请输入"
									style="height:34px" class="input-sm form-control"> <span
									class="input-group-btn">
									<button type="button" onclick="searchPlan()" id="searchBtn"
										class="btn  btn-primary">搜索</button>
								</span>
							</div>
						</div>
						<div class="col-sm-4" style="text-align:right;margin-top:5px">
							<button type="button" class="btn btn-primary " 
								onclick="addPlanProject()" id="planAddBtn">新增</button>
						<button type="button" class="btn btn-primary "
								onclick="importOutMenu()">导入</button>
							<button type="button" class="btn btn-primary "
								onclick="outPortMenu()">下载模板</button>
							<button type="button" class="btn btn-primary "
								onclick="outMenu()">导出</button>
						  </div>
						  <div style="float:left;width:100%;margin:1px 10px"><span style="font-size:13px;font-weight:bold">提示:</span><span style="font-size:12px"> 以下是项目列表</span></div>
							<table class="table table-striped table-bordered" id="jihuaTable" 
								lay-filter="jihuaTable"></table>

					</div>
				</div>
			</div>
		</div>
	</div>
<!-- 	  <a class="layui-btn layui-btn-xs" lay-event="edit"> 改 </a>
  <a class="layui-btn layui-btn-danger layui-btn-xs" lay-event="del"> 删 </a> -->
	<script type="text/html" id="barDemo">

<i class="layui-icon" lay-event="look11">&#xe615;</i>&nbsp&nbsp
<i class="layui-icon" lay-event="edit">&#xe642;</i>&nbsp&nbsp
<i class="layui-icon" lay-event="del">&#xe640;</i>
</script>
   <script src="../js/layui.all.js"></script>
	<script type="text/html" id="status">
 {{#  if(d.status == 0){ }}
      <a class="layui-btn layui-btn-xs" lay-event="publish">发布</a>
     {{#  } else if(d.status==1){ }}
         <a class="layui-btn layui-btn-xs" lay-event="canclePublish">取消发布</a>
         <a class="layui-btn layui-btn-xs" lay-event="auditOption">审核</a>
   {{#  } else if(d.status==2){ }}
         <span style="color:green">审核已通过</span>
 {{#  }   else { }}
   <span style="color:red">审核已拒绝</span>
{{#  } }}



</script>
	<script>
	var  planName='';
		$(function() {
			$.cookie('type', 0);//内
			authorityOutBtn('planAddBtn','isAdd');
			 getData();
			 enterListenter('planName','searchBtn');
		})
		
		
	function  searchPlan(){
			planName=$("#planName").val();
			getData();
		}	
		 function getData() {
		var data = {
			"type" : 1,
			"planName":planName
		};
		 //var index = layer.msg('数据请求中...', {icon: 16,   shade: [0.5],   scrollbar: false,   time: 0});
		layui.use('table', function() {
			var table = layui.table;
			  table.render({
				    elem: '#jihuaTable'
				    ,height:460
				    ,url:  serverUrl.url + serverUrl.queryPlanInsideListByPage
				     ,where : data
				    ,page: true 
				    ,limit:10
				    ,even: true
				     ,loading:true
				    ,cols : [ [ 
						{
							type : 'numbers',
							title : '序号',
							align : 'center',
							width : '7%'
						}, {
							field : 'plan_project_name',
							title : '项目名称',
							align : 'center',
							width : '15%'
						}, {
							field : 'custom_id',
							title : '单位',
							align : 'center',
							width : '10%'
						}, {
							field : 'serial_number',
							title : '编号',
							align : 'center',
							width : '9%'
						}, {
							field : 'ProjectYear',
							title : '年度',
							align : 'center',
							width : '8%'
						}, {
							field : 'org_id',
							title : '单位',
							align : 'center',
							width : '15%'
						}, {
							field : 'duration_times',
							title : '期数',
							align : 'center',
							width : '8%'
						}, {
							field : 'status',
							title : '状态操作',
							align : 'center',
							toolbar : '#status',
							width : '15%'
						}, {
							fixed : 'right',
							title : '操作',
							align : 'center',
							toolbar : '#barDemo'
						} ] ]
			       ,
	          done: function(res, curr, count){
	        	  authorityBtn('.layui-icon','isDelete','del');
	        	  authorityBtn('.layui-icon','isUpdate','edit');
	        	  authorityBtn('.layui-btn','isPublish','publish');
	        	  authorityBtn('.layui-btn','isPublish','canclePublish');
	        	  authorityBtn('.layui-btn','isAudit','auditOption');
	        	   //layer.close(index);
	            }
				  });

			//监听工具条
			table.on('tool(jihuaTable)', function(obj) {
				var data = obj.data;

				if (obj.event === 'del') {
					var  delMsg='确定删除【'+data.plan_project_name+'】项目的吗?';
					layer.confirm(delMsg,{title:'删除提示'}, function(index) {
						var params = {
							"planTrainId" : data.plan_train_id,
							"optionTypeString" : "2"
						};
						//////////////////////////////
						updatePlan(params, 1);

					});
				} else if (obj.event === 'edit') {
					///编辑页面
					$.cookie('type','1');
					$.cookie('optionType', 1);
					$.cookie('planId', data.plan_train_id);
					lookPlanProject('编辑', 1);
				} else if (obj.event === 'look11') {
					$.cookie('optionType', 3);
					//查看页面
					$.cookie('planId', data.plan_train_id);
					lookPlanProject('查看 ', 0);
				} else if (obj.event === 'publish') {
					layer.confirm('确定发布吗?', function(index) {
						var params = {
							"planTrainId" : data.plan_train_id,
							"optionTypeString" : "3",
							"_status" : 1
						};
						updatePlan(params, 2);
					});
				} else if (obj.event === 'canclePublish') {
					layer.confirm('确定取消发布吗?', function(index) {
						///已发布 取消发布
						var params = {
							"planTrainId" : data.plan_train_id,
							"optionTypeString" : "3",
							"_status" : 0
						};
						updatePlan(params, 2);
					});
				} else if (obj.event === 'auditPass')
				///去审核通过
				{
					var params = {
						"planTrainId" : data.plan_train_id,
						"optionTypeString" : "3",
						"_status" : 2
					};
					updatePlan(params, 2);

				} else if (obj.event === 'auditNoPass') {
					//审核拒绝
					var params = {
						"planTrainId" : data.plan_train_id,
						"optionTypeString" : "3",
						"_status" : 3
					};
					updatePlan(params, 2);
				} else if (obj.event === 'auditOption') {
					$.cookie("planTrainId", data.plan_train_id);
					//审核
					audtiOption();
				}
			});
		});
		 	};
		 
		function updatePlan(params, type) {
			$.ajax({
				type : "POST",
				dataType : "json",
				url : serverUrl.url + serverUrl.saveOrUpdatePlan,
				data : params,
				cache : false,
				success : function(result) {
					if (result.returnCode == 0) {
						var index = parent.layer.getFrameIndex(window.name);
						parent.layer.close(index);
						//parent.showMsg(result.reason);
						$(".layui-laypage-btn").click();
						layer.msg('操作成功!', {
							icon : 0
						});
						if (type == 1) {
							//obj.del();
						} else {
							//getData();
							//layui.table.reload('#jihuaTable',{page:{curr:$(".layui-laypage-em").next().html()}})
							$(".layui-laypage-btn").click();
						}
						layer.close(index);
						//getData();
					} else {
						//parent.showMsg(result.reason);
						layer.msg('<a style="color:red">' + result.reason
								+ '</a>', {
							icon : 2
						});
					}

				},
				error : function() {
					layer.msg('<a style="color:red">' + '添加菜单失败!' + '</a>', {
						icon : 2
					});
				}
			});
		}
		function lookPlanProject(subTitle, type) {
			var option = layer.open({
				type : 2,
				title : subTitle,
				shadeClose : false,
				maxmin : false,
				shade : [ 0.2, '#393D49' ],
				area : [ '1000px', '580px' ],
				scrollbar : false,
				offset : 'auto',
				content : 'jihua_wai_edit_look.html',
				end : function() {
					if (type == 1) {
						$(".layui-laypage-btn").click();
					}
				}
			});
		}
		//导入   
	    function importOutMenu() {
	       layer.open({
	           type: 2,
	           title: 外导入',
	           shadeClose: false,
	           maxmin: false,
	           fix: true,
	           shade: [0.2, '#393D49'],
	           area: ['400px', '180px'],
	           scrollbar: false,
	           content: 'importTeacher4.html'
	       });
	   }
		
		//下载模板
        function outPortMenu(){
        	
           window.location.href = serverUrl.url + serverUrl.exportPlanFileByExcel;
           layer.msg('正下载中,请耐心等待', {   icon: 16   ,shade: 0.01 });
        }
        //导出
        function outMenu(){
        	 //layer.load(2);
        	 window.location.href = serverUrl.url + serverUrl.exportPlanByExcel;
        	// layer.closeAll('loading');
        	 layer.msg('正在导出中,请耐心等待', {   icon: 16   ,shade: 0.01 ,time : 8*1000}); 
        	 
        }
		function showPlanProjectPage() {
			var option = layer.open({
				type : 2,
				title : '新增培训',
				shadeClose : false,
				maxmin : false,
				shade : [ 0.2, '#393D49' ],
				area : [ '1000px', '580px' ],
				scrollbar : false,
				offset : 'auto',
				content : 'jihua_wai_add.html'
			});
			//layer.full(option);

		}
		function addPlanProject() {
			 $.cookie('type','1');
			showPlanProjectPage();
		}
		function audtiOption() {
			layer.open({
				type : 2,
				title : '审核',
				shadeClose : false,
				maxmin : false,
				fix : true,
				shade : [ 0.2, '#393D49' ],
				area : [ '700px', '230px' ],
				scrollbar : false,
				content : 'plan_audit_page.html'
			});
		}
	  
	</script>
</body>

</html>

后端部分controller 这个是带模板的导入导出方法 都是先读取模板再 将查出来的数据插入到表格中 你需要将模板先放入到项目中

package com.jysoft.core.busiInfo.controller;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import com.jysoft.core.busiInfo.po.TTrainPlan;
import com.jysoft.core.busiInfo.service.TrainPlanService;
import com.jysoft.utils.DateUtil;
import com.jysoft.utils.GUID;
import com.jysoft.utils.RequestUtil;
import com.jysoft.utils.RestResult;
import com.jysoft.utils.SessionUtils;
import com.sgcc.uap.rest.annotation.RawResponseBody;


	
	// 导入(xf 2/28)
	 @SuppressWarnings("unchecked")
	 @RequestMapping(value = "/addPlanListByXls", method = { RequestMethod.GET, RequestMethod.POST })
	 @RawResponseBody
	 public RestResult addPlanListByXlsV2(@RequestParam(value = "file") MultipartFile file, HttpServletRequest request,
	   HttpSession session) throws IOException {
	 
	  Map<String, String> reslut = new HashMap<String, String>();
	  // 获取输入流
	  FileInputStream fis = (FileInputStream) file.getInputStream();
	  // 2.加载POI对象
	     Workbook workbook = new HSSFWorkbook(fis); 
	     System.out.println("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"+workbook);
	  String projectPath = request.getSession().getServletContext().getRealPath("");
	  projectPath = projectPath.replaceAll("\\\\", "//");
	  String webappPath = projectPath.substring(0, projectPath.lastIndexOf("/"));
	  String directoryPath = webappPath + "/" + "excel";
	  String type=request.getParameter("type");
	  Map<String, Object> paramsMap = new HashMap<String, Object>();
	  SessionUtils sessionUtils = new SessionUtils(request);
	  paramsMap.put("plan_train_id", GUID.getRandomId());
	  paramsMap.put("createBy", sessionUtils.getValue("loginUserId").toString());
	  paramsMap.put("directoryPath", directoryPath);
	  paramsMap.put("type", type);
	  try {
	   reslut=service.excuteInPutExcel(workbook, paramsMap, file);
	   if (reslut.containsKey("msg")) {
	    return new RestResult(1, "导入数据失败!", reslut);
	   }
	   return new RestResult(0, "导入数据成功!", reslut);
	   
	  } catch (Exception e) {
	   e.printStackTrace();
	   reslut.put("success", "1");
	   return new RestResult(1, "导入数据异常!", reslut);
	   }

	 }
	
		
		

		/**
		 * 下载模板
		 * 
		 * @param res
		 * @throws Exception
		 */
		@RequestMapping(value = "/exportPlanFileByExcel", method = RequestMethod.GET)
		public void downloadFile(HttpServletResponse res, HttpServletRequest request) throws Exception {
			// 1.拿到模板文件
			String projectPath = request.getSession().getServletContext().getRealPath("");
			projectPath = projectPath.replaceAll("\\\\", "//");
            //打印路径 位置
			System.out.println(projectPath);
            //获取模板  该位置的模板 路径 加上文件名
			File modelFile = new File(projectPath + "/WEB-INF/导入模板.xls");
			FileInputStream fis = new FileInputStream(modelFile);

			/*// 加载单元格下拉列表
			String[] strings = new String[]{"列表1","列表2","列表3"};
			XSSFWorkbook workbook = new XSSFWorkbook(fis);
			TestPOIDropDown.XSSFSetDropDown(strings,workbook);*/
			
			// 设置下载类型,文件名称,编码
			Calendar now = Calendar.getInstance();
			String fileName = now.get(Calendar.YEAR) + "-" + (now.get(Calendar.MONTH) + 1) + "-"
					+ now.get(Calendar.DAY_OF_MONTH) + java.net.URLDecoder.decode("-导入导出模板", "UTF-8") + ".xls";
			// 设置响应头,控制浏览器下载该文件
			res.setContentType("application/vnd.ms-excel");
			res.setHeader("Content-Disposition",
					"attachment; filename=" + new String(fileName.getBytes("GB2312"), "ISO8859-1"));

			/*// 将处理好的Excel文件导出到前端
			OutputStream out = res.getOutputStream();
			workbook.write(out);
			out.flush();
			out.close();*/

			/*
			 * String fileName = new
			 * String("导入模板.xls".getBytes(),"ISO-8859-1");
			 * res.setHeader("Content-Disposition", "attachment;fileName=" +
			 * fileName); res.setCharacterEncoding("UTF-8");
			 */

			 // 2.临时容器
			 byte[] buffer = new byte[1024];
			 BufferedInputStream bis = null;
			try {
			 fis = new FileInputStream(modelFile);// 模板输入流
			 bis = new BufferedInputStream(fis);// 缓冲数据流
			
			OutputStream os = res.getOutputStream();// 浏览器前端输出流
			
			 // 向response缓冲区输出文件数据
			 int i = bis.read(buffer);
			while (i != -1) {
			 os.write(buffer, 0, i);
			 i = bis.read(buffer);
			 }
			
		     } catch (Exception e) {
			 e.printStackTrace();
			 } finally {
			 if (bis != null) {
			 try {
			 bis.close();
			 } catch (IOException e) {
			 e.printStackTrace();
			 }
			 }
			 if (fis != null) {
			 try {
			 fis.close();
			 } catch (IOException e) {
			 e.printStackTrace();
			 }
			}
			 }
		}

		/**
		 * 导出Excel文件
		 * 
		 * @param res
		 * @throws Exception
		 */
		@RequestMapping(value = "/exportPlanByExcel", method = RequestMethod.GET)
		@ResponseBody
		public void exportPlanByExcel(HttpServletResponse res, HttpServletRequest request) throws Exception {
			/**
			 * 1.先拿到模板文件对象 2.获取到模板文件POI对象 3.创建新的导出文件 4.将模板对象中想数据复制到导出文件中
			 * 5.将你要导出的数据依次导出到文件中
			 */
			// 获取路径
			String projectPath = request.getSession().getServletContext().getRealPath("");
			projectPath = projectPath.replaceAll("\\\\", "//");
			System.out.println(projectPath);

			// 1.拿到模板
			FileInputStream fis = new FileInputStream(new File(projectPath + "/WEB-INF/导入模板.xls"));
			// 2.加载POI对象
			// Workbook workbook1 = WorkbookFactory.create(fis);
			HSSFWorkbook workbook = new HSSFWorkbook(fis);
			// 3.向模板中加载业务数据

			/**
			 * 调用service exportOutFile
			 */
			workbook = service.exportToExcel(workbook);
	    
			
			Calendar now = Calendar.getInstance();
			String fileName = now.get(Calendar.YEAR) + "-" + (now.get(Calendar.MONTH) + 1) + "-"
					+ now.get(Calendar.DAY_OF_MONTH) + java.net.URLDecoder.decode("-导出文档", "UTF-8") + ".xls";
			// 设置响应头,控制浏览器下载该文件
			res.setContentType("application/vnd.ms-excel");
			res.setHeader("Content-Disposition",
					"attachment; filename=" + new String(fileName.getBytes("GB2312"), "ISO8859-1"));


			// 将处理好的Excel文件导出到前端
			OutputStream out = res.getOutputStream();
			workbook.write(out);
			out.flush();
			out.close();
		}
		
		
		/**
		 * 导出計劃外Excel文件
		 * 
		 * @param res
		 * @throws Exception
		 */
		@RequestMapping(value = "/exportPlanOutByExcel", method = RequestMethod.GET)
		@ResponseBody
		public void exportPlanOutByExcel(HttpServletResponse res, HttpServletRequest request) throws Exception {
			/**
			 * 1.先拿到模板文件对象 2.获取到模板文件POI对象 3.创建新的导出文件 4.将模板对象中想数据复制到导出文件中
			 * 5.将你要导出的数据依次导出到文件中
			 */
			// 获取路径
			String projectPath = request.getSession().getServletContext().getRealPath("");
			projectPath = projectPath.replaceAll("\\\\", "//");
//			System.out.println(projectPath);

			// 1.拿到模板
			FileInputStream fis = new FileInputStream(new File(projectPath + "/WEB-INF/导入模板.xls"));
			// 2.加载POI对象
			// Workbook workbook1 = WorkbookFactory.create(fis);
			HSSFWorkbook workbook = new HSSFWorkbook(fis);
			// 3.向模板中加载业务数据

			/**
			 * 调用service exportOutFile
			 */
			workbook = service.exportOutFile(workbook);
			
			
			Calendar now = Calendar.getInstance();
			String fileName = now.get(Calendar.YEAR) + "-" + (now.get(Calendar.MONTH) + 1) + "-"
					+ now.get(Calendar.DAY_OF_MONTH) + java.net.URLDecoder.decode("-导出文档", "UTF-8") + ".xls";
			// 设置响应头,控制浏览器下载该文件
			res.setContentType("application/vnd.ms-excel");
			res.setHeader("Content-Disposition",
					"attachment; filename=" + new String(fileName.getBytes("GB2312"), "ISO8859-1"));

			// 4.将模板导出成新的Excel文件
			// File newExcelFile = new File("新的文件临时路径");
			// newExcelFile.createNewFile();
			// FileOutputStream fos = new FileOutputStream(newExcelFile);

			// 将处理好的Excel文件导出到前端
			OutputStream out = res.getOutputStream();
			workbook.write(out);
			out.flush();
			out.close();
		}
}

service层

package com.jysoft.core.busiInfo.service;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.UUID;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.apache.http.HttpResponse;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddressList;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
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.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import com.jysoft.core.busiInfo.bizc.ITTrainPlanBizc;
import com.jysoft.core.busiInfo.bizc.ITTrainProjectBizc;
import com.jysoft.core.busiInfo.po.TTrainPlan;
import com.jysoft.core.busiInfo.po.TTrainProject;
import com.jysoft.utils.ConvertModeUtil;
import com.jysoft.utils.CopyUtils;
import com.jysoft.utils.DateUtil;
import com.jysoft.utils.ExcelUtils;
import com.jysoft.utils.GUID;
import com.jysoft.utils.OutExcelUtil;
import com.jysoft.utils.RestResult;
import com.sgcc.uap.persistence.IHibernateDao;
import com.sgcc.uap.persistence.util.SqlFileUtil;
@Service
public class TrainPlanService {
	@Autowired
	IHibernateDao hibernateDao;
	@Resource
	ITTrainPlanBizc ttrainplanBizc;
	@Resource
	ITTrainProjectBizc ttrainproject;


  
	 
	
	/**
	 * 导入数据进行业务处理
	 * 
	 * @param workbook
	 */
	@Transactional
	public Map<String, String> excuteInPutExcel(Workbook workbook, Map<String, Object> paramsMap, MultipartFile file) {
		Map<String, String> ret = new HashMap<String, String>();
		String plan_train_id = null;
		// String creatorId = paramsMap.get("createBy").toString();
		String create_by = paramsMap.get("createBy").toString();
		String update_by = paramsMap.get("createBy").toString();
		String publish_user_id = paramsMap.get("createBy").toString();
		String audit_user_id = paramsMap.get("createBy").toString();
		String type = String.valueOf(paramsMap.get("type"));

		String total_people_tims = null;
		String plan_project_name = null;
		String project_year = null;
		String serial_number = null;

		String one_category = null;// 一级分类
		String two_category = null;// 分类
		String three_category = null;// 分类
		String total_people_count = null;// 总人数
		String speciality_category = null;// 类别
		String duration_times = null;// 期次
		String train_content = null;// 内容 选填
		String each_day_count = null;
		String each_people_count = null;
		String training_object = null;
		String custom = null;
		String wheel_man = null;// 人 选填
		String org_name = null;// 单位名称 选填
		String remark = null;// 备注 选填
		String train_months = null;// 月份
		String org_id = null;

		// 拿到第一个sheet
		// XSSFSheet firstSheet = workbook.getSheetAt(0);
		Sheet sheetAt = workbook.getSheetAt(0);
		// 开始导入
		Iterator<Row> it = sheetAt.rowIterator();
		while (it.hasNext()) {
			Row row = it.next(); // 每行数据对象
			int rowNum = row.getRowNum(); // 行数索引
			if (rowNum >= 6) {
				Iterator<Cell> cellIt = row.cellIterator();// 没行中的单元格集合
				// ...

				// 遍历每行单元格集合
				int isat = 0;
				while (cellIt.hasNext()) {
					Cell next = cellIt.next(); // 单元格对象
					System.out.println("单元格对象是什么?????????????????????" + next);

					int columnIndex = next.getColumnIndex();// 单元格索引
					System.out.println("单元格索引是什么?????????????????????" + columnIndex);
					if (next != null) {
						next.setCellType(Cell.CELL_TYPE_STRING);
					}
					if (columnIndex == 0) {

						plan_train_id = next.getStringCellValue();
						if (plan_train_id == null || "".equals(plan_train_id)) {
							isat = 1;
							break;
						}
						// 序号
						plan_train_id = GUID.getRandomId();
					} else if (columnIndex == 1) {
						// 年度

						project_year = next.getStringCellValue();
						if (project_year == null || "".equals(project_year)) {

							isat = 1;
							break;
						}

					} else if (columnIndex == 2) {
						// 编号

						serial_number = next.getStringCellValue();

					} else if (columnIndex == 3) {
						

						plan_project_name = next.getStringCellValue();

					} else if (columnIndex == 4) {
						
						one_category = next.getStringCellValue();

					} else if (columnIndex == 5) {
						
						two_category = next.getStringCellValue();

					} else if (columnIndex == 6) {
						// 分类

						three_category = next.getStringCellValue();

					} else if (columnIndex == 7) {
					

					} else if (columnIndex == 8) {
						
						speciality_category = next.getStringCellValue();

					} else if (columnIndex == 9) {
						// 期次
						
						duration_times = next.getStringCellValue();

					} else if (columnIndex == 10) {
						// 月份
					
						train_months = next.getStringCellValue();

					} else if (columnIndex == 11) {
						// 天数
						
						each_day_count = next.getStringCellValue();

					} else if (columnIndex == 12) {
						// 人数
						
						each_people_count = next.getStringCellValue();

					} else if (columnIndex == 13) {
						
						total_people_count = next.getStringCellValue();

					} else if (columnIndex == 14) {
						
						total_people_tims = next.getStringCellValue();

					} else if (columnIndex == 15) {
						//对象
						training_object = next.getStringCellValue();

					} else if (columnIndex == 16) {
						// 内容
						train_content = next.getStringCellValue();

					} else if (columnIndex == 17) {
						// 单位
						custom = next.getStringCellValue();

					} else if (columnIndex == 18) {
						// 负责人
						wheel_man = next.getStringCellValue();

					} else if (columnIndex == 19) {
						// 部门
						 org_name = next.getStringCellValue();
						 org_id=org_name;
					

					} else if (columnIndex == 20) {
						// 备注
						remark = next.getStringCellValue();

					}

				}
				if (isat == 1) {
					break;
				}
				
				String is_exist = "1";
				String status = "0";
				String audit_opinion = "暂无";
				Date create_date = new Date();
				Date update_date = new Date();
				//String type = "0";
				System.out.println("查看异常" + "来了吗");

				// 存入数据库plan_train_id,
				hibernateDao.executeSqlUpdate(SqlFileUtil.get("saveTrainPlan"),
						new Object[] { plan_train_id, plan_project_name,serial_number, each_people_count, each_day_count,
								total_people_count, total_people_tims, duration_times, org_id, training_object,
								wheel_man, project_year, speciality_category, one_category, two_category,
								three_category, train_months, train_content, is_exist, remark, status, create_date,
								update_date, publish_user_id, audit_user_id, audit_opinion, create_by, update_by, type,
								custom });

			}

		}
		ret.put("success", "1");
		return ret;
		// 调用Dao层,进行数据库插入操作..

	}

	


	// 导出(xf)
	@SuppressWarnings("unchecked")
	public HSSFWorkbook exportToExcel(HSSFWorkbook workbook) {
		/**
		 * 查数据
		 */
		List<Map<String, Object>> list = hibernateDao.queryForListWithSql(SqlFileUtil.get("queryTrainPlanList"),
				new Object[] {});
		/**
		 * 遍历数据集合, 向workbook中的单元格插入List<Map<String, String>>
		 * xiaLaLieBiaoDataList =
		 */
	

		// 设置样式
		HSSFSheet sheet = workbook.getSheetAt(0);
		// Sheet sheetAt = workbook.getSheetAt(0);
		Row row2 = sheet.getRow(4);
		CellStyle[] cellStyle = new CellStyle[row2.getLastCellNum()];
		for (int i = 0; i < cellStyle.length; i++) {
			cellStyle[i] = row2.getCell(i).getCellStyle();
		}

		int columnIndex = 1;
		for (int i = 0; i < list.size(); i++) {

			Map<String, Object> map = list.get(i);
			// int tempIndex = 10;

			// 拿到第n+6行的row對象
			Row row = sheet.getRow(i + 6);
			// 处理序号列表

			++columnIndex;
			for (String key : map.keySet()) {
				Object obj = map.get(key);
				String value = obj == null ? "" : obj.toString();

				if (key.equalsIgnoreCase("plan_train_id")) {
					// 序号
					Cell cell = row.createCell(0);
					cell.setCellValue(columnIndex);
					cell.setCellStyle(cellStyle[0]);
				}
				if (key.equalsIgnoreCase("project_year")) {
					// 年度
					Cell cell = row.createCell(1);
					cell.setCellValue(value);
					cell.setCellStyle(cellStyle[1]);
				}
				if (key.equalsIgnoreCase("serial_number")) {
					// 编号
					Cell cell = row.createCell(2);
					cell.setCellValue(value);
					cell.setCellStyle(cellStyle[2]);
				}
				if (key.equalsIgnoreCase("plan_project_name")) {
					// 名称
					Cell cell = row.createCell(3);
					cell.setCellValue(value);
					cell.setCellStyle(cellStyle[3]);
				}
				if (key.equalsIgnoreCase("one_category")) {
					// 一级分类
					Cell cell = row.createCell(4);
					cell.setCellValue(value);
					cell.setCellStyle(cellStyle[4]);
				}
				if (key.equalsIgnoreCase("two_category")) {
					// 二级分类
					Cell cell = row.createCell(5);
					cell.setCellValue(value);
					cell.setCellStyle(cellStyle[5]);
				}
				if (key.equalsIgnoreCase("three_category")) {
					// 三级分类
					Cell cell = row.createCell(6);
					cell.setCellValue(value);
					cell.setCellStyle(cellStyle[6]);
				}
				if (key.equalsIgnoreCase("speciality_category")) {
					// 类别
					Cell cell = row.createCell(8);
					cell.setCellValue(value);
					cell.setCellStyle(cellStyle[8]);
				}
				if (key.equalsIgnoreCase("duration_times")) {
					// 期数
					Cell cell = row.createCell(9);
					cell.setCellValue(value);
					cell.setCellStyle(cellStyle[9]);
				}
				if (key.equalsIgnoreCase("train_months")) {
					//月份
					Cell cell = row.createCell(10);
					cell.setCellValue(value);
					cell.setCellStyle(cellStyle[10]);
				}
				if (key.equalsIgnoreCase("each_day_count")) {
					// 天数
					Cell cell = row.createCell(11);
					cell.setCellValue(value);
					cell.setCellStyle(cellStyle[11]);
				}
				if (key.equalsIgnoreCase("each_people_count")) {
					// 人数
					Cell cell = row.createCell(12);
					cell.setCellValue(value);
					cell.setCellStyle(cellStyle[12]);
				}
				if (key.equalsIgnoreCase("total_people_count")) {
					// 总人数
					Cell cell = row.createCell(13);
					cell.setCellValue(value);
					cell.setCellStyle(cellStyle[13]);
				}
				if (key.equalsIgnoreCase("total_people_tims")) {
					// 总人次
					Cell cell = row.createCell(14);
					cell.setCellValue(value);
					cell.setCellStyle(cellStyle[14]);
				}
				if (key.equalsIgnoreCase("training_object")) {
					// 对象
					Cell cell = row.createCell(15);
					cell.setCellValue(value);
					cell.setCellStyle(cellStyle[15]);
				}
				if (key.equalsIgnoreCase("train_content")) {
					// 内容
					Cell cell = row.createCell(16);
					cell.setCellValue(value);
					cell.setCellStyle(cellStyle[16]);
				}
				if (key.equalsIgnoreCase("custom_id")) {
					// 单位
					Cell cell = row.createCell(17);
					cell.setCellValue(value);
					cell.setCellStyle(cellStyle[17]);
				}
				if (key.equalsIgnoreCase("wheel_man")) {
					// 负责人
					Cell cell = row.createCell(18);
					cell.setCellValue(value);
					cell.setCellStyle(cellStyle[18]);
				}
				if (key.equalsIgnoreCase("org_id")) {
					//部门
					Cell cell = row.createCell(19);
					cell.setCellValue(value);
					cell.setCellStyle(cellStyle[19]);
				}
				if (key.equalsIgnoreCase("remark")) {
					// 备注
					Cell cell = row.createCell(20);
					cell.setCellValue(value);
					cell.setCellStyle(cellStyle[20]);
				}
			}
		}
		/*
		 * //處理單元格下拉列表 String[] textlist = specCategoryDataSet.toArray(new
		 * String[specCategoryDataSet.size()]);
		 * setHSSFValidation(sheet,textlist,5,list.size(),8,8);
		 */
		return workbook;
	}

	/**
	 * 设置某些列的值只能输入预制的数据,显示下拉框.
	 * 
	 * @param sheet
	 *            要设置的sheet.
	 * @param textlist
	 *            下拉框显示的内容
	 * @param firstRow
	 *            开始行
	 * @param endRow
	 *            结束行
	 * @param firstCol
	 *            开始列
	 * @param endCol
	 *            结束列
	 * @return 设置好的sheet.
	 */
	public void setHSSFValidation(XSSFSheet sheet, String[] textlist, int firstRow, int endRow, int firstCol,
			int endCol) {
		// 加载下拉列表内容
		DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist);
		// 设置数据有效性加载在哪个单元格上,四个参数分别是:起始行、终止行、起始列、终止列
		CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
		// 数据有效性对象
		HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
		sheet.addValidationData(data_validation_list);
	}
}

发布了6 篇原创文章 · 获赞 0 · 访问量 67
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 深蓝海洋 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览