【Javaweb】poi实现通过上传excel表格批量导入数据到数据库
转自:https://www.cnblogs.com/kyliechen/p/10732760.html
1.导入poi相关jar包
对于只操作2003及以前版本的excel,只需要导入poi-XXX.jar ,如果还需要对2007及以后版本进行操作,则需要导入
poi-ooxml-XXX.jar
poi-ooxml-schemas-XXX.jar
2.读取excel文件
ImportExcel工具类
import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.InputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; public class ImportExcel { // abc.xls public static boolean isXls(String fileName){ // (?i)忽略大小写 if(fileName.matches("^.+\\.(?i)(xls)$")){ return true; }else if(fileName.matches("^.+\\.(?i)(xlsx)$")){ return false; }else{ throw new RuntimeException("格式不对"); } } public static List<Map<String, Object>> readExcel(String fileName, InputStream inputStream) throws Exception{ boolean ret = isXls(fileName); Workbook workbook = null; // 根据后缀创建不同的对象 if(ret){ workbook = new HSSFWorkbook(inputStream); }else{ workbook = new XSSFWorkbook(inputStream); } Sheet sheet = workbook.getSheetAt(0); // 得到标题行 Row titleRow = sheet.getRow(0); int lastRowNum = sheet.getLastRowNum(); int lastCellNum = titleRow.getLastCellNum(); List<Map<String, Object>> list = new ArrayList<>(); for(int i = 1; i <= lastRowNum; i++ ){ Map<String, Object> map = new HashMap<>(); Row row = sheet.getRow(i); for(int j = 0; j < lastCellNum; j++){ // 得到列名 String key = titleRow.getCell(j).getStringCellValue(); Cell cell = row.getCell(j); cell.setCellType(CellType.STRING); map.put(key, cell.getStringCellValue()); } list.add(map); } workbook.close(); return list; } }
前端:给出上传链接
<div class="layui-form-item"> <label class="layui-form-label">选择文件</label> <div class="layui-input-block"> <input type="file" name="mFile" id="no1" class="layui-input"> </div> </div> <div class="layui-form-item"> <input class="layui-btn" style="margin-left: 10%" id="btn1" type="submit" value="确认导入"> </div>
后台controller层处理接收的excel文件
@RequestMapping("/staff/import.do") @ResponseBody public JsonBean importExcel(@RequestParam MultipartFile mFile){ try { String fileName = mFile.getOriginalFilename(); // 获取上传文件的输入流 InputStream inputStream = mFile.getInputStream(); // 调用工具类中方法,读取excel文件中数据 List<Map<String, Object>> sourceList = ImportExcel.readExcel(fileName, inputStream); // 将对象先转为json格式字符串,然后再转为List<SysUser> 对象 ObjectMapper objMapper = new ObjectMapper(); String infos = objMapper.writeValueAsString(sourceList); // json字符串转对象 List<Staff> list = objMapper.readValue(infos, new TypeReference<List<Staff>>() {}); // 批量添加 staffService.addStaffBatch(list); return JsonUtils.createJsonBean(1, null); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); return JsonUtils.createJsonBean(0, e.getMessage()); } }
【注意】excel每个列名要和数据库字段名一致!!
对于有date类型的数据,excel输入2019-12-12会变为日期格式数据,日期类型传到后台时会转成字符串,其格式会出错,就无法转换Date类型,
所以Excel表格一定要将时间相关数据用文本格式存储!!!
数据库对应实体类与时间相关属性要添加@DateTimeFormat(pattern="yyyy-MM-dd")注解