1. 第一步 导包
2. jsp页面
<form class="form-horizontal" role="form" method="post" action="uploadExcel" enctype="multipart/form-data">
<div class="form-group">
<label class="col-lg-4 control-label">excel文件</label>
<div class="col-lg-8">
<input type="file" required="required" name="uploadFile" class="fileName">
<input type="hidden" name="uploadFileName" class="hiddenFileName">
</div>
</div>
<hr />
<div class="form-group">
<div class="col-lg-offset-1 col-lg-9">
<button type="submit" class="btn btn-success" id="upload">上传并导入</button>
</div>
</div>
</form>
3. appl.xml--就是配置 IOC
<bean id="uploadExcelAction" class="com.onlineTest.action.UploadExcelAction">
</bean>
还有AOP
<tx:advice id="txAdvice" transaction-manager="transactionManager">
<tx:attributes>
<tx:method name="save*" propagation="REQUIRED" />
<tx:method name="add*" propagation="REQUIRED" />
<tx:method name="importExcel" propagation="REQUIRED" />
<tx:method name="*" propagation="SUPPORTS"/>
<tx:method name="*" read-only="true"/>
</tx:attributes>
</tx:advice>
<aop:config proxy-target-class="true">
<aop:pointcut id="serviceOperation" expression="execution(* com.onlineTest.serviceImpl.*.*(..))" />
<aop:advisor advice-ref="txAdvice" pointcut-ref="serviceOperation" />
</aop:config>
4.struts.xml 给 path 赋值
<action name="uploadExcel" class="uploadExcelAction">
<param name="savePath">/excel</param>
<result name="import" type="redirectAction">test_addTestsByExcel</result>
</action>
5. 接收 excel文件的action
public class UploadExcelAction extends BaseAction{
private String uploadFileName;
private File uploadFile;
private String savePath;
public String execute(){
try {
getSession().setAttribute("savePath", this.getSavePath());
FileUtils.copyFile(uploadFile, new File(this.getSavePath()));
} catch (IOException e) {
e.printStackTrace();
}
return "import";
}
@SuppressWarnings("deprecation")
public String getSavePath() {
return ServletActionContext.getRequest().getRealPath(savePath)+"\\"+this.uploadFileName;
}
}
6. 上传之后 就是 把 服务器 里面的 excel文件地址拿到
并且 把 excel 的数据 解析出来
action里面的代码
//excel导入题目
public String addTestsByExcel(){
System.out.println(getSession().getAttribute("savePath").toString());
List<Test> tt=iTestService.importExcel(getSession().getAttribute("savePath").toString());
getRequest().setAttribute("tests", tt);
return findTests();
}
seviceImpl层
/*/
* 增加题目 根据 savepath 把文件拿到,在解析文件(第一行是 0 表头 不要, 从1开始, 每一列 必须和 数据库的表 一一对应) 解析出来 数据 在一一 放入到 数据库表中 使用的 hibernate 映射
* 通过POI解析excel导入到数据库
*/
public void importExcel(String savePath) {
Student student = (Student) this.getSession().getAttribute("student");
//得到要解析的excel文件
File file = new File(savePath);
try {
FileInputStream fs = new FileInputStream(file);
// 初始化一个工作簿
HSSFWorkbook hwb = new HSSFWorkbook(fs);
// 第一张表单
HSSFSheet sheet = hwb.getSheetAt(0);
HSSFRow row = null;
// 遍历改表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数
for (int i = 0; i <hwb.getNumberOfSheets(); i++) {
sheet = hwb.getSheetAt(i);
// 遍历改行所有的行,j表示行数 getPhysicalNumberOfRows()表示得到行的总数
for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {
row = sheet.getRow(j);
String testId =Integer.toString((int) row.getCell(0).getNumericCellValue());
String subId = Integer.toString((int) row.getCell(1).getNumericCellValue());
String stId = student.getStId();
String orgId = student.getOrganization().getOrgId();
String testName = row.getCell(2).toString();
String difficulty = row.getCell(3).toString();
Date joinTime =new Date();
String testType = row.getCell(4).toString();
String principleType = row.getCell(5).toString();
String answer = row.getCell(6).toString();
String isExec = row.getCell(7).toString();
String testTitle = row.getCell(8).toString();
Integer score = (int) row.getCell(9).getNumericCellValue();
Test test = new Test(testId, subId, stId, orgId, testName,
difficulty, joinTime, testType, principleType,
answer, isExec, testTitle, score);
iTestDao.save(test);
}
}
} catch (Exception e) {
e.printStackTrace();
}
}