相关依赖
org.jeecg easypoi-base2.4.0
大致思路:
1、判断获取的文件后缀 是xls还是xlsx
2、声明WorkBook,获取Sheet
3、通过sheet 获取row,注意观察,是不是默认从0开始计数
4、获取每一个cell,判断其类型,给每一种类型赋值
5、调用mapper层,进行批量 *** 作
相关代码示例:
1、实体类
package com.xw.model; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import java.io.Serializable; @Data @AllArgsConstructor @NoArgsConstructor public class ExcelBatchimportData implements Serializable { private String preferentialPhone; private String preferentialCarNumber; private String groupingId; private String createTime; private String modifyTime; }
2、controller
package com.xw.controller; import com.xw.service.GroupConfigService; import org.slf4j.Logger; import org.slf4j.LoggerFactory; 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 java.util.HashMap; import java.util.Map; @Controller @RequestMapping("/rs") public class TestController { @Autowired private GroupConfigService groupConfigService; private Logger logger = LoggerFactory.getLogger(TestController.class); @RequestMapping(value = "/importExcel",method = RequestMethod.POST) @ResponseBody public MapimportExcel(@RequestParam("file") MultipartFile file){ Map map = new HashMap<>(); try { map = groupConfigService.importExcel(file); }catch (Exception e){ map.put("status",-1); map.put("data", "导入异常"); logger.error(e.getMessage(),e); } return map; } }
3、service
package com.xw.service; import com.xw.mapper.GroupConfigMapper; import com.xw.model.ExcelBatchimportData; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFDateUtil; 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.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.web.multipart.MultipartFile; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.*; @Service public class GroupConfigService { private static final String XLS = "xls"; private static final String XLSX = "xlsx"; @Autowired private GroupConfigMapper groupConfigMapper; private Logger logger = LoggerFactory.getLogger(GroupConfigService.class); public MapimportExcel(MultipartFile file) { List tblFixChangeList = new ArrayList<>(); Map map = new HashMap<>(); Workbook workbook = null; String filename = file.getOriginalFilename(); try { if (filename != null) { if (filename.endsWith(XLS)) { // 2003 workbook = new HSSFWorkbook(file.getInputStream()); } else if (filename.endsWith(XLSX)) { // 2007 workbook = new XSSFWorkbook(file.getInputStream()); } else { throw new Exception("文件不是Excel文件"); } } else { logger.error("文件为空"); } // 获取sheet Sheet sheet = workbook.getSheet("Sheet1"); int lastRowNum = sheet.getLastRowNum(); if (lastRowNum == 0) { throw new Exception("请填写行数"); } for (int i = 1; i < lastRowNum + 1; i++) { Row row = sheet.getRow(i); if (row != null) { // 读取cell单元格内容 ExcelBatchimportData tblFixChange = new ExcelBatchimportData(); // 手机号 String phone = getCellValue(row.getCell(0)); tblFixChange.setPreferentialPhone(phone); // 车票号 String carNumber = getCellValue(row.getCell(1)); tblFixChange.setPreferentialCarNumber(carNumber); String groupId = getCellValue(row.getCell(2)); tblFixChange.setGroupingId(groupId); // 日期格式化操作 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String time = sdf.format(new Date()); tblFixChange.setCreateTime(time); tblFixChange.setModifyTime(time); tblFixChangeList.add(tblFixChange); } } // 批量插入 groupConfigMapper.addBatchMembers(tblFixChangeList); map.put("status", 1); map.put("data", "导入数据成功"); } catch (Exception e) { map.put("status", -1); map.put("data", "导入数据异常"); logger.error(e.getMessage(), e); } return map; } private String getCellValue(Cell cell) { // 单元格内容 String value = ""; if (cell != null) { // 以下是判断数据的类型 switch (cell.getCellType()) { // 数字 case HSSFCell .CELL_TYPE_NUMERIC: value = cell.getNumericCellValue() + ""; // 判断cell是不是日期类型的 if (HSSFDateUtil.isCellDateFormatted(cell)) { // 获取日期类型的单元格 Date date = cell.getDateCellValue(); if (null != date) { value = new SimpleDateFormat("yyyy-MM-dd").format(date); } else { value = ""; } } else { value = new DecimalFormat("0").format(cell.getNumericCellValue()); } break; // 字符串 case HSSFCell.CELL_TYPE_STRING: value = cell.getStringCellValue(); break; // Boolean case HSSFCell.CELL_TYPE_BOOLEAN: value = cell.getBooleanCellValue() + ""; break; // 公式 case HSSFCell.CELL_TYPE_FORMULA: value = cell.getCellFormula() + ""; break; // 空格 case HSSFCell.CELL_TYPE_BLANK: value = ""; break; // 错误 case HSSFCell.CELL_TYPE_ERROR: value = "非法字符"; default: value = "未知类型"; break; } } return value.trim(); } }
4、mapper
package com.xw.mapper; import com.xw.model.ExcelBatchimportData; import org.apache.ibatis.annotations.Param; import java.util.List; public interface GroupConfigMapper { void addBatchMembers(@Param("excelBatchimportData") ListexcelBatchimportData); }
5、mapper.xml
insert into group_config(preferential_phone, preferential_carNumber, grouping_id, create_time, modify_time) values ( #{item.preferentialPhone}, #{item.preferentialCarNumber}, #{item.groupingId}, #{item.createTime}, #{item.modifyTime}, )
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)