一、我使用的是maven,首先需要引入poi相关的jar包,如下:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
二、html页面,使用了thymeleaf
<form class="form-horizontal" id="form_table" action="#" th:action="@{/console/batchImport}" enctype="multipart/form-data" method="post">
<input class="form-input" type="file" name="filename"></input>
<br/>
<button type="submit" class="btn">开始导入</button>
</form>
三、controller
//导入
@PostMapping(value = "batchImport")
public String batchImportUserKnowledge(@RequestParam(value="filename") MultipartFile file,
HttpServletRequest request,HttpServletResponse response,HttpSession session,
@SessionAttribute(Constants.ACCOUNT_SESSION_KEY) Account account) throws IOException{
//判断文件是否为空
if(file==null){
session.setAttribute("msg","文件不能为空!");
return "redirect:toUserKnowledgeImport";
}
//获取文件名
String fileName=file.getOriginalFilename();
//验证文件名是否合格
if(!ExcelImportUtils.validateExcel(fileName)){
session.setAttribute("msg","文件必须是excel格式!");
return "redirect:toUserKnowledgeImport";
}
//进一步判断文件内容是否为空(即判断其大小是否为0或其名称是否为null)
long size=file.getSize();
if(StringUtils.isEmpty(fileName) || size==0){
session.setAttribute("msg","文件不能为空!");
return "redirect:toUserKnowledgeImport";
}
//批量导入
String message = knowledgeService.batchImport(fileName,file,account.getUsername());
session.setAttribute("msg",message);
return "redirect:toUserKnowledgeImport";
}
四、service层
/**
* 上传excel文件到临时目录后并开始解析
* @param fileName
* @param file
* @param userName
* @return
*/
public String batchImport(String fileName,MultipartFile mfile,String userName){
File uploadDir = new File("E:\\fileupload");
//创建一个目录 (它的路径名由当前 File 对象指定,包括任一必须的父路径。)
if (!uploadDir.exists()) uploadDir.mkdirs();
//新建一个文件
File tempFile = new File("E:\\fileupload\\" + new Date().getTime() + ".xlsx");
//初始化输入流
InputStream is = null;
try{
//将上传的文件写入新建的文件中
mfile.transferTo(tempFile);
//根据新建的文件实例化输入流
is = new FileInputStream(tempFile);
//根据版本选择创建Workbook的方式
Workbook wb = null;
//根据文件名判断文件是2003版本还是2007版本
if(ExcelImportUtils.isExcel2007(fileName)){
wb = new XSSFWorkbook(is);
}else{
wb = new HSSFWorkbook(is);
}
//根据excel里面的内容读取知识库信息
return readExcelValue(wb,userName,tempFile);
}catch(Exception e){
e.printStackTrace();
} finally{
if(is !=null)
{
try{
is.close();
}catch(IOException e){
is = null;
e.printStackTrace();
}
}
}
return "导入出错!请检查数据格式!";
}
/**
* 解析Excel里面的数据
* @param wb
* @return
*/
private String readExcelValue(Workbook wb,String userName,File tempFile){
//错误信息接收器
String errorMsg = "";
//得到第一个shell
Sheet sheet=wb.getSheetAt(0);
//得到Excel的行数
int totalRows=sheet.getPhysicalNumberOfRows();
//总列数
int totalCells = 0;
//得到Excel的列数(前提是有行数),从第二行算起
if(totalRows>=2 && sheet.getRow(1) != null){
totalCells=sheet.getRow(1).getPhysicalNumberOfCells();
}
List<UserKnowledgeBase> userKnowledgeBaseList=new ArrayList<UserKnowledgeBase>();
UserKnowledgeBase tempUserKB;
String br = "<br/>";
//循环Excel行数,从第二行开始。标题不入库
for(int r=1;r<totalRows;r++){
String rowMessage = "";
Row row = sheet.getRow(r);
if (row == null){
errorMsg += br+"第"+(r+1)+"行数据有问题,请仔细检查!";
continue;
}
tempUserKB = new UserKnowledgeBase();
String question = "";
String answer = "";
//循环Excel的列
for(int c = 0; c <totalCells; c++){
Cell cell = row.getCell(c);
if (null != cell){
if(c==0){
question = cell.getStringCellValue();
if(StringUtils.isEmpty(question)){
rowMessage += "问题不能为空;";
}else if(question.length()>60){
rowMessage += "问题的字数不能超过60;";
}
tempUserKB.setQuestion(question);
}else if(c==1){
answer = cell.getStringCellValue();
if(StringUtils.isEmpty(answer)){
rowMessage += "答案不能为空;";
}else if(answer.length()>1000){
rowMessage += "答案的字数不能超过1000;";
}
tempUserKB.setAnswer(answer);
}
}else{
rowMessage += "第"+(c+1)+"列数据有问题,请仔细检查;";
}
}
//拼接每行的错误提示
if(!StringUtils.isEmpty(rowMessage)){
errorMsg += br+"第"+(r+1)+"行,"+rowMessage;
}else{
userKnowledgeBaseList.add(tempUserKB);
}
}
//删除上传的临时文件
if(tempFile.exists()){
tempFile.delete();
}
//全部验证通过才导入到数据库
if(StringUtils.isEmpty(errorMsg)){
for(UserKnowledgeBase userKnowledgeBase : userKnowledgeBaseList){
this.saveUserKnowledge(userKnowledgeBase, userName);
}
errorMsg = "导入成功,共"+userKnowledgeBaseList.size()+"条数据!";
}
return errorMsg;
}
五、工具类代码:
public class ExcelImportUtils {
// @描述:是否是2003的excel,返回true是2003
public static boolean isExcel2003(String filePath) {
return filePath.matches("^.+\\.(?i)(xls)$");
}
//@描述:是否是2007的excel,返回true是2007
public static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
/**
* 验证EXCEL文件
* @param filePath
* @return
*/
public static boolean validateExcel(String filePath){
if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))){
return false;
}
return true;
}
}
六、对应的excel内容如下: