解析excel
步骤 上传文件-----> 解析excel后List<Object>接收 -----> 存进数据库 -----> 删除文件
框架 struts easyui 引入了poi包来解析excel
先是jsp和js页面
jsp 这里根据需求传递了两个参数 因为后面存入数据库 需要的字段
<div id="uploaddataWin" >
<form id="uploaddataForm" method="post" action="<%=path%>/adminJson/workExamQuestionsInfo_export" id="fileUpload" name="fileUpload" enctype="multipart/form-data">
<table>
<tr>
<td>
<label for="d_updkiTitle" required=true>考试名称:</label>
<input class="easyui-combobox" name="data.deiId" id="d_updkiTitle" style="width:150px;" editable=true valueField="id" textField="text" editable=false required="true" ></input>
</td>
</tr>
<tr>
<td>
<label for="d_updeqiTitle" required=true>考点名称:</label>
<input class="easyui-combobox" name="data.deqiId" id="d_updeqiTitle" style="width:150px;" editable=true valueField="id" textField="text" editable=false required="true" ></input>
</td>
</tr>
<tr><td>
<input class="form-control" type="file" id="excelFile" name="file"/>
</td></tr>
<tr>
<td class="dataFormButtonRow">
<input type="hidden" id="d_deqiId1" name="data.deqiId"></input>
<input type="hidden" id="d_deqiIdoeiId11" name="data.deiId"></input>
<input type="hidden" id="d_handlers" name="handler"></input>
<input type="submit" name="argsubmit" id="uploadbottom" style="display:none"/>
<a href="#" class="easyui-linkbutton" iconCls="icon-ok" style="width:80px" onclick ="uploadsubmit()">保存</a>
<a href="#" class="easyui-linkbutton" iconCls="icon-no" style="width:80px" onclick="winClose('uploaddataWin')">关闭</a>
</td>
</tr>
</table>
</form>
js
//1 弹出框的形式
$('#uploaddataWin').window({
iconCls : 'icon-save',
title : '上传题库',
width : 750,
modal : true,
shadow : true,
closed : true,
closable : true,
fit : false,
minimizable : false,
maximizable : false,
collapsible : false,
resizable : false
});
//2 toolbar
function dataGridToolbar(){
return [{
iconCls : 'icon-ok',
text : '导入题库',
handler : function() {
$('#uploaddataWin').window('open');
}
}]
}
//submit方法
function uploadsubmit(){
$('#uploaddataForm').form('submit', {
url : path + '/adminJson/workExamQuestionsInfo_export',
onSubmit : function() {
var result = $('#uploaddataForm').form('validate');
if (!result) {
$.messager.progress('close');
}
try {
var returnData = dataFormValidate();
result = returnData.result;
if (!returnData.result) {
$.messager.progress('close');
$.messager.alert('系统信息', returnData.returnMsg, 'error');
}
} catch (err) {
}
return result;
},
success : function(data) {
$.messager.progress('close');
try {
var data = eval('(' + data + ')');
if (1 == data.returnCode) {
formReset('uploaddataForm');
winClose('uploaddataWin');
dataGridLoad();
$.messager.alert('系统信息', data.returnMsg, 'info');
}
} catch (err) {
$.messager.alert('系统信息', '操作处理异常！', 'error');
}
}
});
}
后台java部分
public String export() throws Exception {
int result=0;
SessionUser sessionUser = (SessionUser) this.getSessionUser(Constant.Session_AdminInfo);
HttpServletRequest request = getRequest();
//步骤:上传-解析-删除文件
String realpath = ServletActionContext.getServletContext().getRealPath("/upload");
if (file != null) {
File savefile = new File(new File(realpath), fileFileName);
if (!savefile.getParentFile().exists())
savefile.getParentFile().mkdirs();
FileUtils.copyFile(file, savefile);
ExcelParseTool excelParseTool=new ExcelParseTool();
//解析excel得到对象集合
List<DExamQuestionsDetailInfo> examlist= excelParseTool.setFilePath(savefile.getPath());
DExamQuestionsDetailInfo dexamQuestionsDetailInfo=null;
for(int i=0;i<examlist.size();i++){
dexamQuestionsDetailInfo=new DExamQuestionsDetailInfo();
String deqiid=data.getDeqiId().split(",")[0];
dexamQuestionsDetailInfo.setDeqdiId(examQuestionsDetailInfoService.getNewPrimaryId(deqiid));
dexamQuestionsDetailInfo.setDeiId(data.getDeiId().split(",")[0]);
dexamQuestionsDetailInfo.setDeqiId(data.getDeqiId().split(",")[0]);
dexamQuestionsDetailInfo.setDeqdiType(examlist.get(i).getDeqdiAnswer().length()==1?"1":"2");
dexamQuestionsDetailInfo.setDeqdiContent(examlist.get(i).getDeqdiContent());
dexamQuestionsDetailInfo.setDeqdiItems(examlist.get(i).getDeqdiItems());
dexamQuestionsDetailInfo.setDeqdiAnswer(examlist.get(i).getDeqdiAnswer());
dexamQuestionsDetailInfo.setValid("1");
dexamQuestionsDetailInfo.setModifyDate(new Date());
dexamQuestionsDetailInfo.setViewSeq(Byte.valueOf("99"));
//添加一题
result+=examQuestionsDetailInfoService.insert(dexamQuestionsDetailInfo);
}
//题目存入数据库后删除解析的excel
if (savefile.exists() && savefile.isFile()) {
if (savefile.delete()) {
}
} else {
System.out.println("删除单个文件失败：" + fileFileName + "不存在！");
}
// System.out.println("题库上传成功");
}
if (result > 0) {
this.formJson.setSuccess(true);
this.formJson.setReturnCode(EnumHandlerResult.Success.getId());
this.formJson.setReturnMsg("题库上传"+EnumHandlerResult.Success.getText());
} else {
this.formJson.setSuccess(false);
this.formJson.setReturnCode(EnumHandlerResult.Error.getId());
this.formJson.setReturnMsg(EnumHandlerResult.Error.getText());
}
return Constant.Struts_JsonForword_Form;
}
解析 ExcelParseTool 工具类
创建对象后 调用setFilePath(参数传入文件路径) 返回List
由于是网上得到 所以注释和内容会不搭
public class ExcelParseTool {
private String mFilePath;
private List<DExamQuestionsDetailInfo> modelList;
//保存源文件内容
public List<DExamQuestionsDetailInfo> setFilePath(String filePath) throws IOException {
mFilePath = filePath;
modelList=new ArrayList<DExamQuestionsDetailInfo>();
parseWorkbook(initWorkBook(),modelList);
return modelList;
}
private static final String SUFFIX_2003 = ".xls";
private static final String SUFFIX_2007 = ".xlsx";
Workbook initWorkBook() throws IOException {
File file = new File(mFilePath);
InputStream is = new FileInputStream(file);
Workbook workbook = null;
//根据后缀，得到不同的Workbook子类，即HSSFWorkbook或XSSFWorkbook
if (mFilePath.endsWith(SUFFIX_2003)) {
workbook = new HSSFWorkbook(is);
} else if (mFilePath.endsWith(SUFFIX_2007)) {
workbook = new XSSFWorkbook(is);
}
return workbook;
}
void parseWorkbook(Workbook workbook, List<DExamQuestionsDetailInfo> modelList) {
int numOfSheet = workbook.getNumberOfSheets();
//依次解析每一个Sheet
for (int i = 0; i < numOfSheet; ++i) {
Sheet sheet = workbook.getSheetAt(i);
parseSheet(sheet, modelList);
}
}
private List<Method> mMethod;
private void parseSheet(Sheet sheet, List<DExamQuestionsDetailInfo> modelList) {
Row row;
int count = 0;
boolean flag=false;
//利用迭代器，取出每一个Row
Iterator<Row> iterator = sheet.iterator();
while(iterator.hasNext()) {
row = iterator.next();
//由于第一行是标题，因此这里单独处理
if (count == 0) {
mMethod = new ArrayList<Method>();
flag = parseRowAndFindMethod(row);
} else if(flag==true){
//其它行都在这里处理
parseRowAndFillData(row, modelList);
}
++count;
}
}
private boolean parseRowAndFindMethod(Row row) {
//利用parseRow处理每一行，得到每个cell中的String
List<String> rst = parseRow(row);
boolean Content=false;
boolean items=false;
boolean answer=false;
String methodName = null;
// 根据String得到需要调用的ApnModel中的方法
// 由于自己在ApnModel中定义的方法均是类似setMcc、setMnc等
// 因此才在一开始，将标题栏中每一项大写
for (String str : rst) {
if (str.equals("考题")) {
methodName="setDeqdiContent";
Content = true;
}
if (str.equals("选项")) {
methodName="setDeqdiItems";
items = true;
}
if (str.equals("答案")) {
methodName="setDeqdiAnswer";
answer = true;
}
try {
mMethod.add(DExamQuestionsDetailInfo.class.getDeclaredMethod(methodName, String.class));
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (SecurityException e) {
e.printStackTrace();
}
}
if(Content==true && items==true && answer==true){
return true;
}else{
return false;
}
}
//开始解析具体的数据
private void parseRowAndFillData(Row row, List<DExamQuestionsDetailInfo> modelList) {
//同样利用parseRow得到具体每一行的数据
List<String> rst = parseRow(row);
DExamQuestionsDetailInfo model = new DExamQuestionsDetailInfo();
//这里主要debug一下，避免由于Excel的格式可能不太对
//使得每一行的数据解析地不太对
//利用反射，将数据填充到具体的ApnModel
try {
for (int i = 0; i < mMethod.size(); ++i) {
mMethod.get(i).invoke(model, rst.get(i));
}
//保存到输出结果中
modelList.add(model);
} catch (Exception e) {
System.out.println(e.toString());
}
}
//这里是解析每一行的代码
private List<String> parseRow(Row row) {
List<String> rst = new ArrayList<String>();
Cell cell;
//利用迭代器得到每一个cell
Iterator<Cell> iterator = row.iterator();
while (iterator.hasNext()) {
cell = iterator.next();
//定义每一个cell的数据类型
cell.setCellType(Cell.CELL_TYPE_STRING);
//取出cell中的value
rst.add(cell.getStringCellValue());
}
return rst;
}
}
//////////////
