Mybatis简单的通用报表导入

Mybatis简单的通用报表导入

刚刚毕业找到工作，正处于培训阶段比较清闲，就想着写点通用组件以后要用到的时候就可以节省很多精力，这里是一个Mybatis的自己写的一个报表导入。这块打算用Mybatis-plus重写一个，先记录一下，说不定工作几年回来看看自己写的代码还有点小羞耻。

Service层

//多了就写成枚举 private final static String AFTER_EXCEL2007_FORMAT = "xlsx"; private final static String BEFORE_EXCEL2007_FORMAT = "xls"; /** * @param isTitle Excel文件首行是否为标题行 * @param tableName 表名 * @param idName 该表主键名 * @param columns Excel文件每一列的对应的表的字段名 * @author Freeman * */ @Override public void importExcel(boolean isTitle, String tableName, String idName, String[] columns , MultipartFile file) throws IOException { String fileName = file.getOriginalFilename(); if (fileName == null) { throw new ApiException("请输入正确的文件类型"); } String fileFormat = fileName.substring(fileName.lastIndexOf(".") + 1); String[] values = new String[columns.length]; if (AFTER_EXCEL2007_FORMAT.equals(fileFormat)) { XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream()); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { XSSFSheet sheet = workbook.getSheetAt(i); insertExcel(isTitle, tableName, idName, columns, values, sheet); } } else if (BEFORE_EXCEL2007_FORMAT.equals(fileFormat)) { HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream()); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { HSSFSheet sheet = workbook.getSheetAt(i); insertExcel(isTitle, tableName, idName, columns, values, sheet); } } else { throw new ApiException("请输入正确的文件类型"); } } private void insertExcel(boolean isTitle, String tableName, String idName, String[] columns , String[] values, Sheet sheet) { int rowNum = sheet.getLastRowNum(); String cellStr = null; Row row = null; Cell cell = null; String[] withIdValues = new String[columns.length + 1]; String[] withIdColumns = new String[columns.length + 1]; withIdColumns[0] = idName; for (int i = isTitle ? 1 : 0; i < rowNum + 1; i++) { row = sheet.getRow(i); for (int j = 0; j < columns.length; j++) { cell = row.getCell(j); cellStr = cell == null ? null : cell.getStringCellValue(); withIdColumns[j + 1] = columns[j]; values[j] = cellStr; withIdValues[j + 1] = cellStr; } if (!"".equals(idName) && idName != null) { withIdValues[0] = UUID.randomUUID().toString(); uploadMapper.insertByExcel(tableName, withIdColumns, withIdValues); } else { uploadMapper.insertByExcel(tableName, columns, values); } } }

Mapper层

int insertByExcel(@Param("table") String table, @Param("columns") String[] columns , @Param("values") String[] values);

Mapper对应的sql

<insert id="insertByExcel"> INSERT INTO ${table} <foreach collection="columns" item="column" open="(" separator="," close=")"> ${column} </foreach> VALUES <foreach collection="values" item="value" open="(" separator="," close=")"> #{value} </foreach> </insert>