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>