controller层代码
/*** 批量导入*/@RequestMapping("/file")@ApiOperation(value = "批量导入人员信息", httpMethod = "POST",consumes="application/json;charset=UFT-8",produces="application/json;charset=UFT-8",notes = "批量导入人员信息")public Map<String, Object> addbatch(@ApiParam(name="file",value="文件",required=true) MultipartFile file){Map<String, Object> map = new HashMap<String, Object>();String result = iImportService.readExcelFile(file);map.put("message", result);return map;}service层代码
public String readExcelFile(MultipartFile file) {// TODO Auto-generated method stubString result = "";//创建处理EXCEL的类ReadExcel readExcel = new ReadExcel();//解析excel，获取上传的事件单List<Map<String, Object>> beExamList = readExcel.getExcelInfo(file);//至此已经将excel中的数据转换到list里面了,接下来就可以操作list,可以进行保存到数据库,或者其他操作,for(Map<String, Object> modal:beExamList){SysDictData entity = new SysDictData();entity.setDictLabel((String)modal.get("dictLabel"));entity.setDictValue((String)modal.get("dictValue"));entity.setRemark((String)modal.get("remark"));entity.setNowTier((int)modal.get("nowTier"));entity.setDictType("org_category_dict");sysDictDataMapper.insertDictData(entity);}return result;}
工具类中提取excel文件内容到map中
package mon.utils.file;import java.io.IOException;import java.io.InputStream;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import mon.utils.uuid.EmptyUtil;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.springframework.web.multipart.MultipartFile;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* @return*/public List<Map<String, Object>> getExcelInfo(MultipartFile mFile) {String fileName = mFile.getOriginalFilename();// 获取文件名// List<Map<String, Object>> userList = new LinkedList<Map<String, Object>>();try {if (!validateExcel(fileName)) {// 验证文件名是否合格return null;}boolean isExcel = true;// 根据文件名判断文件是版本还是版本if (isExcel(fileName)) {isExcel = false;}return createExcel(mFile.getInputStream(), isExcel);} catch (Exception e) {e.printStackTrace();}return null;}/*** 根据excel里面的内容读取客户信息** @param is输入流* @param isExcel excel是还是版本* @return* @throws IOException*/public List<Map<String, Object>> createExcel(InputStream is, boolean isExcel) {try {Workbook wb = null;if (isExcel) {// 当excel是时,创建excelwb = new HSSFWorkbook(is);} else {// 当excel是时,创建excelwb = new XSSFWorkbook(is);}return readExcelValue(wb);// 读取Excel里面客户的信息} catch (IOException e) {e.printStackTrace();}return null;}/*** 读取Excel里面客户的信息** @param wb* @return*/private List<Map<String, Object>> readExcelValue(Workbook wb) {// 得到第一个shellSheet sheet = wb.getSheetAt(0);// 得到Excel的行数this.totalRows = sheet.getPhysicalNumberOfRows();// 得到Excel的列数(前提是有行数)if (totalRows > 1 && sheet.getRow(0) != null) {this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();}List<Map<String, Object>> userList = new ArrayList<Map<String, Object>>();// 循环Excel行数for (int r = 9; r < totalRows; r++) {Row row = sheet.getRow(r);if (row == null) {continue;}// 循环Excel的列Map<String, Object> map = new HashMap<String, Object>();for (int c = 5; c <= 7; c++) {Cell cell = row.getCell(c);if (null != cell) {if (c == 5) {map.put("dictValue", cell.getStringCellValue());// 名称} else if (c == 6) {map.put("dictLabel",cell.getStringCellValue());// 性别} else if (c == 7) {map.put("remark", cell.getStringCellValue());//}}}map.put("nowTier",3);// 添加到listuserList.add(map);}return userList;}/*** 验证EXCEL文件** @param filePath* @return*/public boolean validateExcel(String filePath) {if (filePath == null || !(isExcel(filePath) || isExcel(filePath))) {errorMsg = "文件名不是excel格式";return false;}return true;}// @描述：是否是的excel，返回true是public static boolean isExcel(String filePath) {return filePath.matches("^.+\\.(?i)(xls)$");}// @描述：是否是的excel，返回true是public static boolean isExcel(String filePath) {return filePath.matches("^.+\\.(?i)(xlsx)$");}}
对excel中的数字做处理前先转换格式为String
cell.setCellType(CellType.STRING);
如果觉得《导入EXCEL文件数据到数据库中》对你有帮助，请点赞、收藏，并留下你的观点哦！
