记录自己写的第一个有用的东西《网页收集信息直接录入Excel中》

来源：互联网发布：中航工业津电知乎编辑：程序博客网时间：2024/05/16 06:50
为什么要写这个小Demo：每次收集学生信息汇总是最让人头疼的事性，因为每个人提交完以后还要往Excel表格中录入。
实现技术：easyui+spring+struts+poi
实现功能步骤：
第一步：
写一个静态页面，form表单提交到后台action中from表单数据
<!DOCTYPE html><html><head><meta charset="UTF-8"><title>学生信息采集</title><!-- 导入jquery核心类库 --><script type="text/javascript" src="js/jquery-1.8.3.js"></script><!-- 导入easyui类库 --><link rel="stylesheet" type="text/css" href="js/easyui/themes/default/easyui.css"><link rel="stylesheet" type="text/css" href="js/easyui/themes/icon.css"><link rel="stylesheet" type="text/css" href="js/easyui/ext/portal.css"><link rel="stylesheet" type="text/css" href="css/default.css"><script type="text/javascript" src="js/easyui/jquery.easyui.min.js"></script><script type="text/javascript" src="js/easyui/ext/jquery.portal.js"></script><script type="text/javascript" src="js/easyui/ext/jquery.cookie.js"></script><script src="js/easyui/locale/easyui-lang-zh_CN.js" type="text/javascript"></script><script type="text/javascript">$(function() {//$("body").css({//visibility: "visible"//});// 对save按钮条件 点击事件$('#save').click(function() {// 判断form元素 是否满足 校验规则if($("#studentId").form('validate')) {$("#studentId").submit();} else {$.messager.alert("警告", "表单中存在非法输入项！", "warning");}});// 对save按钮条件 点击事件$('#add').click(function() {alert("小朋友，这里没功能不要乱点！");});// 对save按钮条件 点击事件$('#no').click(function() {// 清空表单$('#studentId').form("reset");});});</script></head><body><center><div style="background-image: url(image/b.jpg); background-repeat: no-repeat; background-size:100% 100%;"><div style="font-size:50px; margin-bottom:20px;margin-top:20px;">欢迎大家加入传智大家庭</div><div><form id="studentId" action="student_save.action" method="post"><table class="table-edit" width="95% "><tr bgcolor="#00FFFF"><td colspan="1"><b>本人基本信息</b></td></tr><tr><td>姓名</td><td><input type="text" class="easyui-validatebox" data-options="required:true" name="username" /></td><td>性别</td><td><!--<input type="text" name="sex" data-options="required:true" class="easyui-validatebox" />--><input type="radio" name="sex" value="男" checked />男<input type="radio" name="sex" value="女" />女</td></tr><tr><td>年龄</td><td><input type="text" name="age" data-options="required:true" class="easyui-validatebox" /></td><td>QQ</td><td><input type="text" name="qq" class="easyui-validatebox" data-options="required:true" /></td></tr><tr><td>手机号</td><td><input type="text" name="phone" class="easyui-validatebox" data-options="required:true" /></td><td>身份证号</td><td><input type="text" name="identification" class="easyui-validatebox" data-options="required:true" /></td></tr><tr><td>电子邮箱</td><td><input class="easyui-validatebox" type="email" name="email" data-options="validType:'email'" /></td><td>现住详细地址</td><td><input type="text" name="meAddress" class="easyui-validatebox" data-options="required:true" /></td></tr><tr bgcolor="#00FFFF"><td colspan="1"><b>学校信息</b></td></tr><tr><td>毕业院校</td><td><input type="text" class="easyui-validatebox" data-options="required:true" name="school" /></td><td>毕业时间</td><td><input type="text" class="easyui-datebox" data-options="editable:false" name="graduateTime" /></td></tr><tr><td>专业</td><td><input type="text" class="easyui-validatebox" name="specialty" data-options="required:true" /></td><td>学历</td><td><input type="text" class="easyui-validatebox" name="educationalBackground" data-options="required:true" /></td></tr><tr><td>英语水平</td><td><input type="radio" name="english" value="四级" />四级<input type="radio" name="english" value="六级" />六级<input type="radio" name="english" value="专四" />专四<input type="radio" name="english" value="专六" />专六<input type="radio" name="english" value="其它" checked />其它<!--<select class="easyui-combobox" name="english"><option value="四级">四级</option><option value="六级">六级</option><option value="专四">专四</option><option value="专六">专六</option><option value="其它">其它</option></select>--></td></tr><tr bgcolor="#00FFFF"><td colspan="1"><b>家庭信息</b></td></tr><tr><td>家庭联系人</td><td><input type="text" class="easyui-validatebox" data-options="required:true" name="parents" /></td><td>家庭联系电话</td><td><input type="text" class="easyui-validatebox" data-options="required:true" name="parentsPhone" /></td></tr><tr><td>家庭住址</td><td><input type="text " class="easyui-validatebox" data-options="required:true" name="homeAddress" /></td><td>婚否</td><td><input type="radio" name="maritalStatus" value="是" />是<input type="radio" name="maritalStatus" value="否" />否<!--<input type="text" class="easyui-validatebox" data-options="required:true" name="maritalStatus" />--></td></tr><tr><td>是否有子女</td><td><input type="radio" name="children" value="是" />是<input type="radio" name="children" value="否" />否</td></tr><tr bgcolor="#00FFFF"><td colspan="1"><b>工作信息</b></td></tr><tr><td>是否有工作经验</td><td><input type="radio" name="work" value="是" checked />是<input type="radio" name="work" value="否" />否<!--<select class="easyui-combobox" name="work"><option value="是">是</option><option value="否">否</option></select>--></td></tr><tr><td>最近一份工作(类型)</td><td><input type="text" name="workLast" /></td></tr><tr bgcolor="#00FFFF"><td colspan="1"><b>升级信息</b></td></tr><tr><td>是否升级</td><td><input type="radio" name="upgrade" value="是" checked/>是<input type="radio" name="upgrade" value="否" />否<!--<select class="easyui-combobox" name="upgrade"><option value="是">是</option><option value="否">否</option></select>--></td></tr><tr><td>不升级原因</td><td><input type="text" name="cause" /></td></tr><tr bgcolor="#00FFFF"><td colspan="1"><b>学员意见或建议</b></td></tr><td colspan="4"><textarea name="remark" style="width:100%;height: 80px;"></textarea></td></table></form><div><a id="save" data-options="iconCls:'icon-save'" href="#" class="easyui-linkbutton " plain="true ">保存</a><a id="no" data-options="iconCls: 'icon-no'" href="#" class="easyui-linkbutton " plain="true ">取消</a></div></div></div></center></body></html>
第二步：
配置web.xml配置文件
<?xml version="1.0" encoding="UTF-8"?><web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">  <display-name>HM</display-name>  <welcome-file-list>    <welcome-file>index.html</welcome-file>    <welcome-file>index.htm</welcome-file>    <welcome-file>index.jsp</welcome-file>    <welcome-file>default.html</welcome-file>    <welcome-file>default.htm</welcome-file>    <welcome-file>default.jsp</welcome-file>  </welcome-file-list>  <listener>    <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>  </listener>  <context-param>    <param-name>contextConfigLocation</param-name>    <param-value>classpath:applicationContext.xml</param-value>  </context-param>  <filter>    <filter-name>StrutsPrepareAndExecuteFilter</filter-name>    <filter-class>org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter</filter-class>  </filter>  <filter-mapping>    <filter-name>StrutsPrepareAndExecuteFilter</filter-name>    <url-pattern>/*</url-pattern>  </filter-mapping></web-app>
第三步：
配置applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?><beans xmlns="http://www.springframework.org/schema/beans"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"xsi:schemaLocation="        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd"><!-- 扫描action和实体类的注解用 --><context:component-scan base-package="com.kaifang"/></beans>
第四步：
创建实体类，实体类的字段要和form表中的字段一致
package com.kaifang.domain;public class Student {private Integer id;private String username;// 姓名private String sex;// 姓别private String age;// 年龄private String qq;// qqprivate String phone;// 本人手机private String identification;// 身份证号private String email;// 邮件private String meAddress;// 现住地址private String school;// 学校private String graduateTime;// 毕业时间private String specialty;// 专业private String educationalBackground;// 学历private String parents;// 家庭联系人private String parentsPhone;// 家庭联系电话private String maritalStatus;// 婚否private String children;// 是否有子女private String homeAddress;// 家庭住址private String work;// 是否有工作经验private String workLast;// 最后工作private String english;// 英语水平private String upgrade;// 是否升级private String cause;public String getCause() {return cause;}public void setCause(String cause) {this.cause = cause;}private String remark;// 备注public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public String getAge() {return age;}public void setAge(String age) {this.age = age;}public String getQq() {return qq;}public void setQq(String qq) {this.qq = qq;}public String getPhone() {return phone;}public void setPhone(String phone) {this.phone = phone;}public String getIdentification() {return identification;}public void setIdentification(String identification) {this.identification = identification;}public String getEmail() {return email;}public void setEmail(String email) {this.email = email;}public String getMeAddress() {return meAddress;}public void setMeAddress(String meAddress) {this.meAddress = meAddress;}public String getSchool() {return school;}public void setSchool(String school) {this.school = school;}public String getGraduateTime() {return graduateTime;}public void setGraduateTime(String graduateTime) {this.graduateTime = graduateTime;}public String getSpecialty() {return specialty;}public void setSpecialty(String specialty) {this.specialty = specialty;}public String getEducationalBackground() {return educationalBackground;}public void setEducationalBackground(String educationalBackground) {this.educationalBackground = educationalBackground;}public String getParents() {return parents;}public void setParents(String parents) {this.parents = parents;}public String getParentsPhone() {return parentsPhone;}public void setParentsPhone(String parentsPhone) {this.parentsPhone = parentsPhone;}public String getMaritalStatus() {return maritalStatus;}public void setMaritalStatus(String maritalStatus) {this.maritalStatus = maritalStatus;}public String getChildren() {return children;}public void setChildren(String children) {this.children = children;}public String getHomeAddress() {return homeAddress;}public void setHomeAddress(String homeAddress) {this.homeAddress = homeAddress;}public String getWork() {return work;}public void setWork(String work) {this.work = work;}public String getWorkLast() {return workLast;}public void setWorkLast(String workLast) {this.workLast = workLast;}public String getEnglish() {return english;}public void setEnglish(String english) {this.english = english;}public String getUpgrade() {return upgrade;}public void setUpgrade(String upgrade) {this.upgrade = upgrade;}public String getRemark() {return remark;}public void setRemark(String remark) {this.remark = remark;}@Overridepublic String toString() {return "Student [id=" + id + ", username=" + username + ", sex=" + sex + ", age=" + age + ", qq=" + qq+ ", phone=" + phone + ", identification=" + identification + ", email=" + email + ", meAddress="+ meAddress + ", school=" + school + ", graduateTime=" + graduateTime + ", specialty=" + specialty+ ", educationalBackground=" + educationalBackground + ", parents=" + parents + ", parentsPhone="+ parentsPhone + ", maritalStatus=" + maritalStatus + ", children=" + children + ", homeAddress="+ homeAddress + ", work=" + work + ", workLast=" + workLast + ", english=" + english + ", upgrade="+ upgrade + ", cause=" + cause + ", remark=" + remark + "]";}}
第五步：
编写Studentaction 使用ModelDriven模型驱动获取form表单交的数据并写到D盘根目录下保存，获取提交人员的IP实现只提交一次的操作！
package com.kaifang.action;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import java.util.HashSet;import java.util.Set;import javax.servlet.http.HttpServletRequest;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.apache.struts2.ServletActionContext;import org.apache.struts2.convention.annotation.Action;import org.apache.struts2.convention.annotation.Namespace;import org.apache.struts2.convention.annotation.ParentPackage;import org.apache.struts2.convention.annotation.Result;import org.springframework.context.annotation.Scope;import org.springframework.stereotype.Controller;import com.kaifang.domain.Student;import com.opensymphony.xwork2.ActionSupport;import com.opensymphony.xwork2.ModelDriven;@ParentPackage("struts-default")@Namespace("/")@Controller@Scope("prototype")public class StudentAction extends ActionSupport implements ModelDriven<Student> {private static final long serialVersionUID = -2974993378615025102L;private Student student = new Student();private static Set<String> set = new HashSet<String>();static int i = 0;@Overridepublic Student getModel() {return student;}@Action(value = "student_save", results = { @Result(name = "success", type = "redirect", location = "success.html"),@Result(name = "input", type = "redirect", location = "error.html") })public synchronized String save() throws IOException {String ip = getClientIP();if (!set.add(ip)) {return INPUT;}String path = "D:/";String fileName = "学生信息收集";String fileType = "xlsx";Workbook hssfWorkbook = null;Sheet sheet = null;String excelPath = path + File.separator + fileName + "." + fileType;File file = new File(excelPath);// 创建工作文档对象// 判断是否有文件if (!file.exists()) {// 没有文件if (fileType.equals("xls")) {hssfWorkbook = new HSSFWorkbook();} else if (fileType.equals("xlsx")) {hssfWorkbook = new XSSFWorkbook();} else {System.out.println("文件格式不正确");}// 创建sheet对象sheet = (Sheet) hssfWorkbook.createSheet("学员信息收集");OutputStream outputStream = new FileOutputStream(excelPath);// 表头Row headRow = sheet.createRow(0);headRow.createCell(0).setCellValue("序号");headRow.createCell(1).setCellValue("姓名");headRow.createCell(2).setCellValue("性别");headRow.createCell(3).setCellValue("年龄");headRow.createCell(4).setCellValue("QQ");headRow.createCell(5).setCellValue("本人手机");headRow.createCell(6).setCellValue("身份证号");headRow.createCell(7).setCellValue("邮件");headRow.createCell(8).setCellValue("现住地址");headRow.createCell(9).setCellValue("学校");headRow.createCell(10).setCellValue("毕业时间");headRow.createCell(11).setCellValue("专业");headRow.createCell(12).setCellValue("学历");headRow.createCell(13).setCellValue("家庭联系人");headRow.createCell(14).setCellValue("家庭联系电话");headRow.createCell(15).setCellValue("婚否");headRow.createCell(16).setCellValue("是否有子女");headRow.createCell(17).setCellValue("家庭住址");headRow.createCell(18).setCellValue("是否有工作经验");headRow.createCell(19).setCellValue("最后工作");headRow.createCell(20).setCellValue("英语水平");headRow.createCell(21).setCellValue("是否升级");headRow.createCell(22).setCellValue("不升级原因");headRow.createCell(23).setCellValue("备注");hssfWorkbook.write(outputStream);outputStream.flush();outputStream.close();}FileInputStream fs = new FileInputStream(file);// 有文件if (fileType.equals("xls")) {hssfWorkbook = new HSSFWorkbook(fs);} else if (fileType.equals("xlsx")) {hssfWorkbook = new XSSFWorkbook(fs);} else {System.out.println("文件格式不正确");}sheet = hssfWorkbook.getSheetAt(0);Row dataRow = sheet.createRow(sheet.getLastRowNum() + 1);dataRow.createCell(0).setCellValue(set.size());if (!"".equals(student.getUsername()) || null != student.getUsername()) {dataRow.createCell(1).setCellValue(student.getUsername());}if (!"".equals(student.getSex()) || null != student.getSex()) {dataRow.createCell(2).setCellValue(student.getSex());}if (!"".equals(student.getAge()) || null != student.getAge()) {dataRow.createCell(3).setCellValue(student.getAge());}if (!"".equals(student.getQq()) || null != student.getQq()) {dataRow.createCell(4).setCellValue(student.getQq());}if (!"".equals(student.getPhone()) || null != student.getPhone()) {dataRow.createCell(5).setCellValue(student.getPhone());}if (!"".equals(student.getIdentification()) || null != student.getIdentification()) {dataRow.createCell(6).setCellValue(student.getIdentification());}if (!"".equals(student.getEmail()) || null != student.getEmail()) {dataRow.createCell(7).setCellValue(student.getEmail());}if (!"".equals(student.getMeAddress()) || null != student.getMeAddress()) {dataRow.createCell(8).setCellValue(student.getMeAddress());}if (!"".equals(student.getSchool()) || null != student.getSchool()) {dataRow.createCell(9).setCellValue(student.getSchool());}if (!"".equals(student.getGraduateTime()) || null != student.getGraduateTime()) {dataRow.createCell(10).setCellValue(student.getGraduateTime());}if (!"".equals(student.getSpecialty()) || null != student.getSpecialty()) {dataRow.createCell(11).setCellValue(student.getSpecialty());}if (!"".equals(student.getEducationalBackground()) || null != student.getEducationalBackground()) {dataRow.createCell(12).setCellValue(student.getEducationalBackground());}if (!"".equals(student.getParents()) || null != student.getParents()) {dataRow.createCell(13).setCellValue(student.getParents());}if (!"".equals(student.getParentsPhone()) || null != student.getParentsPhone()) {dataRow.createCell(14).setCellValue(student.getParentsPhone());}if (!"".equals(student.getMaritalStatus()) || null != student.getMaritalStatus()) {dataRow.createCell(15).setCellValue(student.getMaritalStatus());}if (!"".equals(student.getChildren()) || null != student.getChildren()) {dataRow.createCell(16).setCellValue(student.getChildren());}if (!"".equals(student.getHomeAddress()) || null != student.getHomeAddress()) {dataRow.createCell(17).setCellValue(student.getHomeAddress());}if (!"".equals(student.getWork()) || null != student.getWork()) {dataRow.createCell(18).setCellValue(student.getWork());}if (!"".equals(student.getWorkLast()) || null != student.getWorkLast()) {dataRow.createCell(19).setCellValue(student.getWorkLast());}if (!"".equals(student.getEnglish()) || null != student.getEnglish()) {dataRow.createCell(20).setCellValue(student.getEnglish());}if (!"".equals(student.getUpgrade()) || null != student.getUpgrade()) {dataRow.createCell(21).setCellValue(student.getUpgrade());}if (!"".equals(student.getCause()) || null != student.getCause()) {dataRow.createCell(22).setCellValue(student.getCause());}if (!"".equals(student.getRemark()) || null != student.getRemark()) {dataRow.createCell(23).setCellValue(student.getRemark());}// String title[] = { "序号", "姓名", "性别", "年龄", "QQ", "本人手机", "身份证号",// "邮件","现住地址","学校","毕业时间","专业","学历","家庭联系人","家庭联系电话","婚否","是否有子女"// ,"家庭住址","是否有工作经验","最后工作","英语水平","是否升级","不升级原因","意见或建议"};// 创建文件流OutputStream stream = new FileOutputStream(excelPath);// 写入数据hssfWorkbook.write(stream);// 关闭hssfWorkbook.close();fs.close();System.out.println("IP是:" + ip + "   学生叫:" + student.getUsername() + "   已经提交：" + set.size());return SUCCESS;}// 获取提交的IPpublic String getClientIP() {HttpServletRequest request = ServletActionContext.getRequest();String ip = request.getHeader("x-forwarded-for");if (null == ip || 0 == ip.length() || "unknown".equalsIgnoreCase(ip)) {ip = request.getHeader("Proxy-Client-IP");}if (null == ip || 0 == ip.length() || "unknown".equalsIgnoreCase(ip)) {ip = request.getHeader("WL-Proxy-Client-IP");}if (null == ip || 0 == ip.length() || "unknown".equalsIgnoreCase(ip)) {ip = request.getHeader("X-Real-IP");}if (null == ip || 0 == ip.length() || "unknown".equalsIgnoreCase(ip)) {ip = request.getRemoteAddr();}return ip;}}
阅读全文
0 0