根据excel列动态创建mysql表_根据数据库字段动态生成excel模版下载，上传模版获取数据存入数据库(poi 反射)...
环境：mysql5.7.28 java8 Spring boot 2.2.4 mybatis-plus3.10
动态：根据需求，用户可以选择对应的字段生成excle模版 下载
poi 反射：poi是excel的第三方jar，反射的作用是给表实体对象属性赋值，方便入库操作。
现在很多的应用都有批量导入的功能，批量导入用的最多的也是excel。我们实际的项目中也用了很多这方面的功能，所以博主系统的CV了一下这方面的代码，下面分步骤进行该功能的实现。此方法的优点：不限于模版字段的排列顺序，避免过多的重复set代码。动态的生成模版信息。
注意：数据库实体类属性变量名，要严格按照驼峰的模式命名，方便数据的读取，反射的赋值。
如果数据量过大，建议采用多线程的方式导入数据，数据的分割根据实际情况，本文采用的是单线程方式执行。
1:依赖的jar包
org.apache.poi
poi-ooxml
3.17
org.apache.poi
poi
3.17
commons-io
commons-io
2.4
2:生成对应数据库的模版
生成模版的前提是查询数据库有哪些字段，有了字段的信息，就可以根据java se中的流知识，生成对应的模版文件，下面的这个是查询表结构的所有信息。因为是要生成字段对应的模版，所以我们把sql修改一下即可select * from information_schema.COLUMNS where table_name = '表名'
查询字段的sql如下：select COLUMN_NAME from information_schema.COLUMNS where table_name = '表名'
同样的这个查询也可以用mybatis框架进行映射，返回的是List 类型，对应的mapper层次如下。List queryColumn();
到这步，我们的字段信息就有了，也就是excel的表头信息有了，下面就是根据表头信息生成对应的模版了。
生成代码如下图：需要说明的是传入的参数：数据库的字段信息，模版的名称(可任意取)，生成模版的路径所在地
public static boolean createModel(List list, String modelName, String modelPath) {
boolean newFile = false;
//创建excel工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//创建工作表sheet
HSSFSheet sheet = workbook.createSheet();
//创建第一行
HSSFRow row = sheet.createRow(0);
HSSFCell cell;
//设置样式
CellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.AQUA.getIndex());
//插入第一行数据的表头
for (int i = 0; i < list.size(); i ) {
cell = row.createCell(i);
cell.setCellStyle(style);
cell.setCellValue(list.get(i));
}
//创建excel文件
File file = new File(modelPath File.separator modelName);
try {
//删除该文件夹下原来的模版文件
deleteDir(new File(modelPath File.separator));
//判断对应的文件夹是否有，无则新建
File myPath = new File(modelPath);
if (!myPath.exists()) {
myPath.mkdir();
}
//创建新的模版文件
newFile = file.createNewFile();
//将excel写入
FileOutputStream stream = FileUtils.openOutputStream(file);
workbook.write(stream);
stream.close();
} catch (IOException e) {
e.printStackTrace();
}
return newFile;
}
下面的是删除原来模版文件的工具方法private static boolean deleteDir(File dir) {
if (dir.isDirectory()) {
String[] children = dir.list();
if (children != null)
//递归删除目录中的子目录下
for (String child : children) {
boolean success = deleteDir(new File(dir, child));
if (!success) {
return false;
}
}
}
// 目录此时为空，可以删除
return dir.delete();
}
文件已经生成了，剩下的就是下载文件。这里需要说明一下，如何动态的生成模版
动态的生成模版就是动态的获取数据库的字段，只需根据用户选取的数据，在sql的查询，或者代码中做修改。如下的not in就是排除这些不需要的字段，当然你也可以选择其他方式进行过滤，程序中过滤是最好的选择。
select COLUMN_NAME from information_schema.COLUMNS where table_name = 'cpa_account_list'
and column_name not in ('account_type_id','account_status','create_time','id','out_time','use_time'
,'update_time')
生成模版后，通过浏览器访问即可下载。下载的代码如下：传入文件的生成路径，文件的名字，下载生成新的文件名(可任意)public static ResponseEntity download(String filePath, String fileName, String newName) {
String path;
ResponseEntity response = null;
try {
path = filePath separator fileName;
log.info("下载的路径为-->[{}]", path);
File file = new File(path);
InputStream inputStream = new FileInputStream(file);
HttpHeaders headers = new HttpHeaders();
headers.add("Cache-Control", "no-cache, no-store, must-revalidate");
headers.add("Content-Disposition",
"attachment; filename="
new String(newName.getBytes(StandardCharsets.UTF_8)) ".xlsx");
headers.add("Pragma", "no-cache");
headers.add("Expires", "0");
response = ResponseEntity.ok().headers(headers)
.contentType(MediaType.parseMediaType("application/octet-stream"))
.body(new InputStreamResource(inputStream));
} catch (FileNotFoundException e1) {
log.error("找不到指定的文件", e1);
}
return response;
}
最后就是在web层次调用上述方法，即可完成下载，博主的controlle代码如下，仅供参考import org.springframework.core.io.InputStreamResource;
import org.springframework.http.ResponseEntity;@GetMapping(value = "/downloadModel", produces = "application/json;charset=UTF-8")
@ApiOperation(value = "账号信息的模板下载", produces = "application/json;charset=UTF-8")
public Object downloadAccountModel() {
//文件名
String modelFileName = "accountList.xlsx";
//下载展示的文件名
ResponseEntity response = null;
try {
List columns = cpaAccountListService.queryColumn();
// 传人数据库的字端，创建资料的模版
boolean model = CpaDownloadFileUtil.createModel(columns, modelFileName, modelPath);
if (model) response = CpaDownloadFileUtil.download(modelPath, modelFileName, "AccountListModel");
} catch (Exception e) {
e.printStackTrace();
log.error("下载模板失败");
}
return response;
}
采用的是swagger测试下载的结果如下
3: 将模版的数据导入到数据库中
批量导入模版中的数据，关键点就是如何将数据准确的读取，生成java对象,放入集合中。其次是利用mybatis-plus的批量导入数据即可。
下面为读取excel的方法，只读取sheet0的数据。读取的数据为一行行数组。将数组放入到集合中返回。具体的解释，代码注释都有。
需要注意处理单元格数据为空的方法。/**
* 解析excel
* auth psy
* @param inp excel InputStream.
* @return 对应数据列表
*/
public static List> readExcel(InputStream inp) {
Workbook wb = null;
try {
wb = WorkbookFactory.create(inp);
// 获取地0个sheet的数据
Sheet sheet = wb.getSheetAt(0);
List> excels = new ArrayList<>();
// 遍历每一行数据
int cellsNumber = 0;
for (int i = 0; i <= sheet.getLastRowNum(); i ) {
if (i == 0) {
// 获取每一行总共的列数
cellsNumber = sheet.getRow(i).getPhysicalNumberOfCells();
}
List excelRows = new ArrayList<>();
// 遍历每一行中的每一列中的
for (int j = 0; j < cellsNumber; j ) {
// i和j组成二维坐标可以定位到对应到单元格内
Cell cell = sheet.getRow(i).getCell(j);
if (i >= 1) {
// 如果单元格到内容为空就设置为"null"代表的是无数据
if (cell == null) {
excelRows.add("null");
} else {
// 不是空值的单元格数据
excelRows.add(getValue(cell));
}
} else {
// 该数据为表格的表头信息，单独存储与集合的首位
excelRows.add(getValue(cell));
}
}
excels.add(excelRows);
}
return excels;
} catch (Exception e) {
log.error("导入excel错误 : " e.getMessage());
return null;
} finally {
try {
if (wb != null) {
wb.close();
}
if (inp != null) {
inp.close();
}
} catch (Exception e) {
log.error("导入excel关流错误 : " e.getMessage());
}
}
}
由于poi的版本不同，获取excel数据的格式方法也不同，本文所使用的工具方法为下，传入的是数据的单元格的对象。public static String getValue(Cell cell) {
String birthdayVal = null;
switch (cell.getCellTypeEnum()) {
case STRING:
birthdayVal = cell.getRichStringCellValue().getString();
break;
case NUMERIC:
if ("General".equals(cell.getCellStyle().getDataFormatString())) {
// 此处为double类型的,转成对应的String类型数据
birthdayVal = Integer.toString(new Double(cell.getNumericCellValue()).intValue());
} else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
birthdayVal = DateToStr(cell.getDateCellValue());
} else {
birthdayVal = DateToStr(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
}
break;
}
return birthdayVal;
}
public static String DateToStr(Date date) {
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
return format.format(date);
}
模版数据中，第一行数据，ip_address为空 ，我读取的时候，设置为“null”，如下结果图显示，同时也发现，我读取的数据是一个数组(list)这个时候数据的读取已经完成了。
首先明确两点内容：1:模版的表头信息，就是数据库的字段
2:数据库的字段与实体属性的对应是驼峰命名的方式(user_id--->userId)。
知道以上两点，问题就变成了如何将数据库的值，赋值到实体类的属性。思路：首先将数据库字段(表头)转成实体的属性变量名，然后通过每一行获取的数据，利用反射的原理，通过类属性名，将对应的单元格信息赋值到对象的属性中。最后保存到集合中，如此循环，便可以将excel对应的表数据，逐行赋值到每一个对象中了。最后就是批量入库操作。
下面是代码的实现：
web层面@PostMapping(value = "/addDataByModel", produces = "application/json;charset=UTF-8")
@ApiOperation(value = "通过模版导入对应的资料数据", produces = "application/json;charset=UTF-8")
public Object addDataByModel(MultipartFile file) {
//文件名
try {
List cpaDataLists = null;
InputStream inputStream = file.getInputStream();
List> lists = CpaExcelUtil.readExcel(inputStream);
if (lists != null) {
cpaDataLists = CpaImportDbUtil.getCpaDataList(lists);
}
if (null != cpaDataLists) {
boolean b = cpaDataListService.saveBatch(cpaDataLists, cpaDataLists.size());
if (b) {
log.info("导入资料的的个数为--->[{}]", cpaDataLists.size());
return ReturnResult.success(ReturnMsg.SUCCESS.getCode(), ReturnMsg.SUCCESS.getMsg(), cpaDataLists.size());
}
}
} catch (Exception e) {
e.printStackTrace();
log.error("通过模版导入资料数据出现异常！");
}
return ReturnResult.error(ReturnMsg.ERROR.getCode(), ReturnMsg.ERROR.getMsg());
}
将读取的excel数据变成对应的集合，集合中是实体对象与数据库字段的对应public static List getCpaAccountList(List> excels) throws Exception {
List cpaAccountLists = new ArrayList<>();
CpaAccountList cpaAccount;
// 第一行代表的是该表格的数据库字段，需要单独拿出来进行处理
List cellList = excels.get(0);
// 将首位数据移除
excels.remove(0);
String filedName;
String value;
// 遍历每一行的数据
for (List excel : excels) {
// 遍历每一行的中的每一列数据
cpaAccount = new CpaAccountList();
for (int i = 0; i < cellList.size(); i ) {
filedName = cellList.get(i).toString();
value = excel.get(i).toString();
if ("null".equals(value)) {
continue;
}
// 通过反射的方式，给属性值set value
setValue(cpaAccount, cpaAccount.getClass(), filedName,
CpaAccountList.class.getDeclaredField(fieldToProperty(filedName)).getType(), value);
}
cpaAccount.setCreateTime(LocalDateTime.now());
cpaAccount.setAccountStatus(1);
cpaAccountLists.add(cpaAccount);
}
return cpaAccountLists;
}
/**
* @return
* @author PSY
* @date 2020/2/25 15:21
* @接口描述: 将数据库字段转换成类的属性
* @parmes
*/
public static String fieldToProperty(String field) {
if (null == field) {
return "";
}
char[] chars = field.toCharArray();
StringBuilder sb = new StringBuilder();
for (int i = 0; i < chars.length; i ) {
char c = chars[i];
if (c == '_') {
int j = i 1;
if (j < chars.length) {
sb.append(StringUtils.upperCase(CharUtils.toString(chars[j])));
i ;
}
} else {
sb.append(c);
}
}
return sb.toString();
}
/**
* @return
* @author PSY
* @date 2020/2/25 15:21
* @接口描述: 通过属性，获取对应的set方法，并且设置值
* @parmes
*/
public static void setValue(Object obj, Class> clazz, String filedName, Class> typeClass, Object value) {
filedName = fieldToProperty(filedName);
String methodName = "set" filedName.substring(0, 1).toUpperCase() filedName.substring(1);
try {
Method method = clazz.getDeclaredMethod(methodName, typeClass);
method.invoke(obj, getClassTypeValue(typeClass, value));
} catch (Exception ex) {
ex.printStackTrace();
}
}
private static Object getClassTypeValue(Class> typeClass, Object value) {
// 对于String类型的有个强行转换成int类型的操作。
if (typeClass == LocalDateTime.class && null != value) {
DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
return LocalDateTime.parse(value.toString(), df);
}
if (typeClass == LocalDateTime.class) {
return null;
}
if (typeClass == Integer.class) {
value = Integer.valueOf(value.toString());
return value;
} else if (typeClass == short.class) {
if (null == value) {
return 0;
}
return value;
} else if (typeClass == byte.class) {
if (null == value) {
return 0;
}
return value;
} else if (typeClass == double.class) {
if (null == value) {
return 0;
}
return value;
} else if (typeClass == long.class) {
if (null == value) {
return 0;
}
return value;
} else if (typeClass == String.class) {
if (null == value) {
return "";
}
return value;
} else if (typeClass == boolean.class) {
if (null == value) {
return true;
}
return value;
} else if (typeClass == BigDecimal.class) {
if (null == value) {
return new BigDecimal(0);
}
return new BigDecimal(value "");
} else {
return typeClass.cast(value);
}
}
以上代码关键的就是反射的应用，一一对应实体属性。
数据库中含有500条数据，导入成功。
