导入poi的Jar包
ExcelReader.java
package utils;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.poifs.filesystem.POIFSFileSystem;
import com.model.Student;
/**
*
* @author (版权归原作者) 用于读取excel
*/
public class ExcelReader {
private HSSFWorkbook wb = null;// book [includes sheet]
private HSSFSheet sheet = null;
private HSSFRow row = null;
private int sheetNum = 0; // 第sheetnum个工作表
private int rowNum = 0;
private FileInputStream fis = null;
private File file = null;
public ExcelReader() {
}
public ExcelReader(File file) {
this.file = file;
}
public void setRowNum(int rowNum) {
this.rowNum = rowNum;
}
public void setSheetNum(int sheetNum) {
this.sheetNum = sheetNum;
}
public void setFile(File file) {
this.file = file;
}
// 读取excel文件获得HSSFWorkbook对象
public void open() throws IOException {
fis = new FileInputStream(file);
wb = new HSSFWorkbook(new POIFSFileSystem(fis));
fis.close();
}
/**
* 返回sheet表数目
*
* @return int
*/
public int getSheetCount() {
int sheetCount = -1;
sheetCount = wb.getNumberOfSheets();
return sheetCount;
}
/**
* sheetNum下的记录行数
*
* @return int
*/
public int getRowCount() {
if (wb == null)
System.out.println("=============>WorkBook为空");
HSSFSheet sheet = wb.getSheetAt(this.sheetNum);
int rowCount = -1;
rowCount = sheet.getLastRowNum();
return rowCount;
}
/**
* 读取指定sheetNum的rowCount
*
* @param sheetNum
* @return int
*/
public int getRowCount(int sheetNum) {
HSSFSheet sheet = wb.getSheetAt(sheetNum);
int rowCount = -1;
rowCount = sheet.getLastRowNum();
return rowCount;
}
/**
* 得到指定行的内容
*
* @param lineNum
* @return String[]
*/
public String[] readExcelLine(int lineNum) {
return readExcelLine(this.sheetNum, lineNum);
}
// 指定工作表和行数的内容
public String[] readExcelLine(int sheetNum, int lineNum) {
if (sheetNum < 0 || lineNum < 0)
return null;
String[] strExcelLine = null;
try {
sheet = wb.getSheetAt(sheetNum);
row = sheet.getRow(lineNum);
int cellCount = row.getLastCellNum();
strExcelLine = new String[cellCount + 1];
for (int i = 0; i <= cellCount; i++) {
strExcelLine[i] = readStringExcelCell(lineNum, i);
}
} catch (Exception e) {
e.printStackTrace();
}
return strExcelLine;
}
// 读取指定列的内容
public String readStringExcelCell(int cellNum) {
return readStringExcelCell(this.rowNum, cellNum);
}
// 指定行和列编号的内容
public String readStringExcelCell(int rowNum, int cellNum) {
return readStringExcelCell(this.sheetNum, rowNum, cellNum);
}
// 指定工作表、行、列下的内容
public String readStringExcelCell(int sheetNum, int rowNum, int cellNum) {
if (sheetNum < 0 || rowNum < 0)
return "";
String strExcelCell = "";
try {
sheet = wb.getSheetAt(sheetNum);
row = sheet.getRow(rowNum);
if (row.getCell((short) cellNum) != null) { // add this condition
switch (row.getCell((short) cellNum).getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA:
strExcelCell = "FORMULA ";
break;
case HSSFCell.CELL_TYPE_NUMERIC: {
strExcelCell = String.valueOf(row.getCell((short) cellNum)
.getNumericCellValue());
}
break;
case HSSFCell.CELL_TYPE_STRING:
strExcelCell = row.getCell((short) cellNum)
.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BLANK:
strExcelCell = "";
break;
default:
strExcelCell = "";
break;
}
}
} catch (Exception e) {
e.printStackTrace();
}
return strExcelCell;
}
/**
* Fuction 根据文件的路径 解析excel文件
*
*/
public List<Student> getStuExcle(String filePath) {
System.out.println("excel reader:"+filePath);
File file = new File(filePath);
ExcelReader readExcel = new ExcelReader(file);
// 打开文件
try {
readExcel.open();
} catch (IOException e) {
e.printStackTrace();
}
readExcel.setSheetNum(0); // 设置读取索引为0的工作表
// 总行数
int count = readExcel.getRowCount();
List<Student> ls = new ArrayList<Student>();
// 循环读取Excel文件中的内容
for (int i = 1; i <= count; i++) {
String[] rows = readExcel.readExcelLine(i);
Student student = new Student();
if (rows.length >= 23) {
System.out.println("row"+rows[0]);
student.setStuId(Integer.valueOf(rows[0]));
student.setStuNo(rows[1]);//学号
student.setStuName(rows[2]);//姓名
student.setStuBeforeName(rows[3]);//曾用名
student.setStuSex(rows[4]); //性别
student.setStuBirth(rows[5]);//出生日期
student.setStuMinZu(rows[6]);//名族
student.setStuOrigin(rows[7]);//籍贯
student.setStuPolity(rows[8]);//政治面貌
student.setStuCollege(rows[9]);//学院
student.setStuProName(rows[10]);//专业名称
student.setStuClass(rows[11]); //行政班级
student.setStuInDate(rows[12]);//入学日期
student.setStuGraduateSchool(rows[13]);//毕业中学
student.setStuDorm(rows[14]);//宿舍号
student.setStuPhone(rows[15]);//联系电话
student.setStuPostalCode(rows[16]);//邮政编码
student.setStuIdCard(rows[17]);//身份证号
student.setStuFatherName(rows[18]); //父亲姓名
student.setStuMotherName(rows[19]);//母亲姓名
student.setStuAddress(rows[20]);//家庭住址
student.setStuParentPhone(rows[21]);//家长联系方式
student.setStuPicture(rows[22]);//头像
System.out.println("uuuuu"+student.getStuId());
ls.add(student);
}
}
return ls;
}
}
InsertExcel.java
package utils;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts2.ServletActionContext;
import com.dao.StudentDao;
import com.model.Student;
import com.service.StudentService;
import com.sun.org.apache.bcel.internal.generic.NEW;
/**
* Funtion 导入excle表格中的数据
*
* */
public class InsertExcel{
StudentService service;
public StudentService getService() {
return service;
}
public void setService(StudentService service) {
this.service = service;
}
//StudentService service=new StudentService();
public String excelImport() throws IOException
{
HttpServletRequest request = ServletActionContext.getRequest();
HttpServletResponse response = ServletActionContext.getResponse();
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
String filename = request.getParameter("filename");
String secFilename = request.getParameter("secFilename");
ExcelReader excelReader = new ExcelReader();
//Student student ;
//学生获取excle信息
List<Student> list = new ArrayList<Student>(); // 用于接收的ls
// 切割字符串
String headStr = "";
System.out.println("sss"+filename);
if (filename != null && !"".equals(filename))
{
headStr = filename.substring(1, 3);
}
// 说明路径名被匿名篡改了
if (!headStr.equals(":\\"))
{
if (!secFilename.equals("") && !secFilename.equals("请输入文件路径"))
{
list = excelReader.getStuExcle(secFilename);
System.out.println(list.size());
}
} else
{
list = excelReader.getStuExcle(filename);
}
if(list!=null)
{
for(int i=0;i<list.size();i++)
{ //dao = new StudentDao();
Student student = new Student();
student.setStuId(list.get(i).getStuId());
student.setStuNo(list.get(i).getStuNo());
student.setStuName(list.get(i).getStuName());
student.setStuBeforeName(list.get(i).getStuBeforeName());
student.setStuSex(list.get(i).getStuSex());
student.setStuBirth(list.get(i).getStuBirth());
student.setStuMinZu(list.get(i).getStuMinZu());
student.setStuOrigin(list.get(i).getStuOrigin());
student.setStuPolity(list.get(i).getStuPolity());
student.setStuCollege(list.get(i).getStuCollege());
student.setStuProName(list.get(i).getStuProName());
student.setStuClass(list.get(i).getStuClass());
student.setStuInDate(list.get(i).getStuInDate());
student.setStuGraduateSchool(list.get(i).getStuGraduateSchool());
student.setStuDorm(list.get(i).getStuDorm());
student.setStuPhone(list.get(i).getStuPhone());
student.setStuPostalCode(list.get(i).getStuPostalCode());
student.setStuIdCard(list.get(i).getStuIdCard());
student.setStuFatherName(list.get(i).getStuFatherName());
student.setStuMotherName(list.get(i).getStuMotherName());
student.setStuAddress(list.get(i).getStuAddress());
student.setStuParentPhone(list.get(i).getStuParentPhone());
student.setStuPicture(list.get(i).getStuPicture());
System.out.println("insert excel:ssssss"+student);
service.u_addStudent(student);
}
}
return "success";
}
}
readExcel.jsp
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme() + "://"
+ request.getServerName() + ":" + request.getServerPort()
+ path + "/";
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title>
<link rel="stylesheet" href="<%=basePath%>resourse/css/bootstrap.min.css" type="text/css" media="screen"/>
<link rel="stylesheet" href="<%=basePath%>resourse/css/bootstrap-responsive.min.css" type="text/css" media="screen"/>
<link rel="stylesheet" href="<%=basePath%>resourse/css/docs.css" type="text/css" media="screen"/>
<script type="text/javascript" src="<%=basePath%>js/jquery-1.8.0.min.js"></script>
<script type="text/javascript" src="<%=basePath%>resourse/js/bootstrap.min.js"></script>
<script>
function getFileName() {
alert($("#filename").val());
}
function loadExcel() {
var firstFileName = $("#filename").val();
var SecFileName = $("#secFilename").val();
//alert("1x"+firstFileName +"2x"+ SecFileName);
if(firstFileName != "") {
alert("ssss");
$("#loadExcle").submit();
}
else if(SecFileName != "" && SecFileName != "请输入文件路径") {
alert("qqqqq");
$("#loadExcle").submit();
}
else {
alert("uuu");
$("#warn_load").show();
return false;
}
}
</script>
</head>
<body>
<form name="loadExcle" id="loadExcle" method="post" action="<%=basePath%>studentinfo/InsertExcel">
<div class="hero-unit">
<h1>导入须知</h1>
<p> 由于浏览器的安全设置带来的不遍,请谅解
<br> IE浏览器可以选择上一种文件选择方式 ,其他浏览器请选择下面的浏览方式
<br> (IE浏览器请检查设置)Internet选项 -> 安全 -> 自定义级别 -> 将本地文件上载至服务器时包含本地目录路径 -> 选"启动" -> 确定
</p>
<p>
<a class="btn btn-primary btn-large" href=" http://zhidao.baidu.com/?autorefresh=1378261177279">
Learn more
</a>
</p>
</div>
<div class="alert alert-block alert-error fade in" id="warn_load" style="display: none" >
<button type="button" class="close" data-dismiss="alert">×</button>
<h4 class="alert-heading" style="margin-left: 60px;size: 10px;font-size: 15px">对不起,请输入文件的路径或者输入正确的路径</h4>
</div>
<input style="margin-left:60px" type="file" id="filename" name="filename" οnchange="setBlogrollImageName(this)" class="btn btn-info" ></input>
<br>
<input id="secFilename" name="secFilename" class="btn btn-warning" style="margin-left:60px" value="请输入文件路径" height="50px"></input>
<input type="submit" id="submit_button" class="btn btn-warning" οnclick="loadExcel();" value="上传文件"></input>
<br>
</form>
</body>
</html>