1 需要上传Excel例子
2 解析成功图片
3 上传页面
3.1 代码
pom.xml需要加入,文件上传需要的的file-upload.jar和解析Exl的poi.jar
pom.xml
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.8</version> <exclusions> <exclusion> <artifactId>commons-codec</artifactId> <groupId>commons-codec</groupId> </exclusion> </exclusions> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.8</version> </dependency> <!-- https://mvnrepository.com/artifact/com.github.davidcarboni/encrypted-file-upload --> <dependency> <groupId>com.github.davidcarboni</groupId> <artifactId>encrypted-file-upload</artifactId> <version>1.0.2</version> </dependency>
UserAction.java
import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.ResponseBody; import org.springframework.web.multipart.MultipartFile; import com.ccd.user.service.UserService; @Controller @RequestMapping("user") public class UserAction { @Autowired private UserService userService; @RequestMapping(value = "/upload", method = RequestMethod.POST) @ResponseBody public String upload(@RequestParam(value = "file", required = false) MultipartFile file, HttpServletRequest request, HttpServletResponse response) { // 这里今天controller层 String result = userService.readExcelFile(file); return result; } }
UserService.java
import org.springframework.web.multipart.MultipartFile; public interface UserService { /** * 读取excel中的数据,生成list */ String readExcelFile( MultipartFile file); }
MeetingRoomServiceImpl.java
import java.util.List; import org.springframework.stereotype.Service; import org.springframework.web.multipart.MultipartFile; import com.ccd.user.model.UserTest; import com.ccd.user.service.UserService; import com.ccd.util.ReadExcel; @Service public class MeetingRoomServiceImpl implements UserService{ @Override public String readExcelFile(MultipartFile file) { String result =""; //创建处理EXCEL的类 ReadExcel readExcel=new ReadExcel(); //解析excel，获取上传的事件单 List<UserTest> useList = readExcel.getExcelInfo(file); //至此已经将excel中的数据转换到list里面了,接下来就可以操作list,可以进行保存到数据库,或者其他操作, //和你具体业务有关,这里不做具体的示范 if(useList != null && !useList.isEmpty()){ result = "chenggong"; }else{ result = "shibai"; } return result; } }
ReadExcel.java
import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; 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.springframework.web.multipart.MultipartFile; import com.ccd.user.model.UserTest; public class ReadExcel { //总行数 private int totalRows = 0; //总条数 private int totalCells = 0; //错误信息接收器 private String errorMsg; //构造方法 public ReadExcel(){} //获取总行数 public int getTotalRows() { return totalRows;} //获取总列数 public int getTotalCells() { return totalCells;} //获取错误信息 public String getErrorInfo() { return errorMsg; } /** * 读EXCEL文件，获取信息集合 * @param fielName * @return */ public List<UserTest> getExcelInfo(MultipartFile mFile) { String fileName = mFile.getOriginalFilename();//获取文件名 List<UserTest> userList=null; try { if (!validateExcel(fileName)) {// 验证文件名是否合格 return null; } boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本 if (isExcel2007(fileName)) { isExcel2003 = false; } userList = createExcel(mFile.getInputStream(), isExcel2003); } catch (Exception e) { e.printStackTrace(); } System.out.println("ReadExcel工具类类 文件名 :"+fileName); System.out.println(userList); return userList; } /** * 根据excel里面的内容读取客户信息 * @param is 输入流 * @param isExcel2003 excel是2003还是2007版本 * @return * @throws IOException */ public List<UserTest> createExcel(InputStream is, boolean isExcel2003) { List<UserTest> userList=null; try{ Workbook wb = null; if (isExcel2003) {// 当excel是2003时,创建excel2003 wb = new HSSFWorkbook(is); } else {// 当excel是2007时,创建excel2007 wb = new XSSFWorkbook(is); } userList = readExcelValue(wb);// 读取Excel里面客户的信息 } catch (IOException e) { e.printStackTrace(); } System.out.println("根据excel里面的内容读取客户信息 ReadExcel工具类类 文件名 :"); System.out.println(userList); return userList; } /** * 读取Excel里面客户的信息 * @param wb * @return */ private List<UserTest> readExcelValue(Workbook wb) { // 得到第一个shell Sheet sheet = wb.getSheetAt(0); // 得到Excel的行数 this.totalRows = sheet.getPhysicalNumberOfRows(); // 得到Excel的列数(前提是有行数) if (totalRows > 1 && sheet.getRow(0) != null) { this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells(); } List<UserTest> userList = new ArrayList<UserTest>(); // 循环Excel行数 for (int r = 1; r < totalRows; r++) { Row row = sheet.getRow(r); if (row == null){ continue; } UserTest user = new UserTest(); // 循环Excel的列 for (int c = 0; c < this.totalCells; c++) { Cell cell = row.getCell(c); if (null != cell) { if (c == 0) { //如果是纯数字,比如你写的是25,cell.getNumericCellValue()获得是25.0,通过截取字符串去掉.0获得25 if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){ String name = String.valueOf(cell.getNumericCellValue()); user.setName(name.substring(0, name.length()-2>0?name.length()-2:1));//名称 }else{ user.setName(cell.getStringCellValue());//名称 } } else if (c == 1) { if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){ String sex = String.valueOf(cell.getNumericCellValue()); user.setSex(sex.substring(0, sex.length()-2>0?sex.length()-2:1));//性别 }else{ user.setSex(cell.getStringCellValue());//性别 } } else if (c == 2){ if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){ String age = String.valueOf(cell.getNumericCellValue()); user.setAge(age.substring(0, age.length()-2>0?age.length()-2:1));//年龄 }else{ user.setAge(cell.getStringCellValue());//年龄 } } } } // 添加到list userList.add(user); } return userList; } /** * 验证EXCEL文件 * * @param filePath * @return */ public boolean validateExcel(String filePath) { if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) { errorMsg = "文件名不是excel格式"; return false; } return true; } // @描述：是否是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)$"); } }
