@Transactional(rollbackFor = Exception.class)
public String selectExcelName(MultipartFile multipartFile, String tableName,String token) {
SysUser userInfo = tokenStoreService.getUserInfo(token, SysUser.class);
//根据tableName获取tableId
String tableId = schemaMenuBaseInfoEntityMapper.selMessageByTableName(tableName);
//获取当前的日期
Date date = new Date();
//设置要获取到什么样的时间
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//获取String类型的时间
String createdate = sdf.format(date);
//根据tableId去nodiot_schema_table_column_base_info中获取字段名称
List<Map<String, Object>> mapList = schemaTableColumnBaseInfoEntityMapper.selectDemoById(tableId);
List<Map<String, Object>> list = new ArrayList<>();
InputStream inputStream = null; //文件流对象
Workbook wb = null;
try {
inputStream = multipartFile.getInputStream();//创建文件流
wb = new HSSFWorkbook(inputStream);//创建工作簿
} catch (IOException e) {
e.printStackTrace();
}
//存放第几列和字段的关联关系
Map<Integer, String> map1 = new HashMap<>();
Sheet sheetAt = wb.getSheetAt(0);
Map<String, PictureData> sheetPictrues03Map = ExcelUtils.getSheetPictrues03( (HSSFSheet) sheetAt, (HSSFWorkbook) wb);
String rowAndCellkey = "";
if(null != sheetPictrues03Map && sheetPictrues03Map.size() > 0){
for (Map.Entry<String, PictureData> entry : sheetPictrues03Map.entrySet()) {
rowAndCellkey = entry.getKey();
}
}
// 获取图片所存取的列 号
String cellString = rowAndCellkey.substring(rowAndCellkey.indexOf("_")+1, rowAndCellkey.length());
Map<String, String> pathMap = null;
if(null != sheetPictrues03Map && sheetPictrues03Map.size() > 0){
try {
//写入图片,并返回图片路径,key:图片坐标,value:图片路径
pathMap = printImg(sheetPictrues03Map);
} catch (IOException e) {
e.printStackTrace();
}
}
int firstRowNum = sheetAt.getFirstRowNum();
int lastRowNum = sheetAt.getLastRowNum();
for (int i = firstRowNum; i <= lastRowNum; i++) { //遍历行
Map<String, Object> maps = new HashMap<>();
Row row = sheetAt.getRow(i);
int firstCellNum = row.getFirstCellNum();
int lastCellNum = row.getLastCellNum();
for (int i1 = firstCellNum; i1 < lastCellNum; i1++) { //遍历列
if (i == 0) { //从第一行开始
for (Map<String, Object> map : mapList) { // 遍历比对,put数据
if (row.getCell(i1).toString().equals(map.get("name"))) {
map1.put(i1, map.get("java_field").toString());
break;
}
}
} else {
Cell cell = row.getCell(i1);
if (cell == null) {
maps.put(map1.get(i1), "");
} else {
maps.put(map1.get(i1), "'" + cell.toString() + "'");
}
}
if (i > 0) {// 不是标头列时,添加图片路径
if(null != pathMap && pathMap.size() > 0){
String path = pathMap.get(i + "_" +cellString);
maps.put(map1.get(Integer.parseInt(cellString)), "'" + path + "'");
}
}
}
if (i != 0) {
list.add(maps);
}
}
StringBuilder sql = new StringBuilder();
StringBuilder sqls = new StringBuilder();
try {
for (Map<String, Object> stringObjectMap : list) {
Set<String> strings = stringObjectMap.keySet();
sql.delete(0, sql.length());
sqls.delete(0, sqls.length());
sqls.append("('" + UUID.randomUUID().toString().replaceAll("-", "") + "', ' "+userInfo.getUserName()+" ' , ' "+createdate+" ' , ' "+userInfo.getDeptId()+" ' ,");
sql.append("insert into " + tableName + "( id ,create_user,create_date,dept_id,");
for (String string : strings) {
if (!"".equals(stringObjectMap.get(string))) {
sql.append(string + ",");
sqls.append(stringObjectMap.get(string) + ",");
}
}
String substring = sql.substring(0, sql.length() - 1) + ") values ";
String result = substring + (sqls.substring(0, sqls.length() - 1) + ")");
schemaTableColumnBaseInfoEntityMapper.inserta(result);
}
} catch (Exception e) {
e.printStackTrace();
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return "error";
}
return "true";
}
//写入图片,并返回图片路径,key:图片坐标,value:图片路径
private Map<String, String> printImg(Map<String, PictureData> sheetList) throws IOException {
Map<String, String> pathMap = new HashMap();
Object[] key = sheetList.keySet().toArray();
for (int i = 0; i < sheetList.size(); i++) {
// 获取图片流
PictureData pic = sheetList.get(key[i]);
// 获取图片索引
String picName = key[i].toString();
// 获取图片格式 后缀
String ext = pic.suggestFileExtension();
String fileName = java.util.UUID.randomUUID().toString().replaceAll("-","");
byte[] data = pic.getData();
ResultBody<ResultFileModel> upload = remoteFileService.upload(data, 1, applicationName, fileName+"."+ext);
if(null != upload && null != upload.getData()){
String imagePath = upload.getData().getPath();
Map<String,Object> mapUrl = new HashMap<>();
mapUrl.put("uid",fileName);
mapUrl.put("url",imagePath);
mapUrl.put("fileUrl",imagePath);
mapUrl.put("downloadURL",imagePath);
mapUrl.put("imgURL",imagePath);
mapUrl.put("state","done");
mapUrl.put("name",fileName+"."+ext);
mapUrl.put("pdfFileUrl", "");
mapUrl.put("fileType", "ext");
pathMap.put(picName, JSONObject.toJSONString(mapUrl));
}
}
return pathMap;
}
java 动态导入excel信息(表格里面带图片)
最新推荐文章于 2023-08-18 15:10:36 发布