public String importExcel(MultipartFile file) {
if (file == null) {
throw new CustomException("当前文件为空");
}
//判断文件是否为xls以及xlsx表格
String fileName = file.getOriginalFilename();
if (StrUtil.isNotEmpty(fileName) && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
throw new CustomException("上传文件格式错误,请上传后缀为.xlsx的文件");
}
try {
//hutool读取excel工具
ExcelReader reader = cn.hutool.poi.excel.ExcelUtil.getReader(file.getInputStream());
//将文件转成XSSFWorkbook工作簿
XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
//获取工作薄中第一个excel表格
XSSFSheet sheet = wb.getSheetAt(0);
//核心:::获取excel表格中所有图片,处理图片上传到oss key:行号-列号
Map<String, XSSFPictureData> pictures = NewExcelUtils.getPictures(sheet);
Map<String, String> map = new HashMap<>();
Set<String> keySet = pictures.keySet();
for (String key : keySet) {
XSSFPictureData xssfPictureData = pictures.get(key);
byte[] data = xssfPictureData.getData();
InputStream inputStream = new ByteArrayInputStream(data);
//图片压缩
byte[] bytes1 = PicUtils.compressOfQuality(inputStream, 0.7f);
InputStream in = new ByteArrayInputStream(bytes1);
MultipartFile file1 = new MockMultipartFile(key + ".jpg", key + ".jpg", null, in);
String imgUrl = aliOssUtil.uploadImg(file1);
log.info("excel导入带图片,图片链接:{}", imgUrl);
map.put(key, imgUrl);
}
//行数
int rowCount = reader.getRowCount();
//列数
int columnCount = reader.getColumnCount();
int startIndex = 1;
int endIndex = 500;
int bathSize = 500;
int batch = 1;
List<List<Object>> resdAll = null;
while (endIndex < rowCount) {
resdAll = reader.read(startIndex, endIndex);
//保存数据库
eventSuperiorService.saveEventSuperior(resdAll, map);
startIndex = endIndex + 1;
batch++;
endIndex = batch * bathSize;
if (endIndex > rowCount) {
endIndex = rowCount;
}
}
resdAll = reader.read(startIndex, endIndex);
//保存数据库
eventSuperiorService.saveEventSuperior(resdAll, map);
} catch (IOException e) {
log.error(e.getMessage());
throw new CustomException("导入出错");
} catch (Exception e) {
e.printStackTrace();
}
return "导入成功";
}
/**
* 导入保存
*
* @param resdAll
* @param map
*/
@Override
public void saveEventSuperior(List<List<Object>> resdAll, Map<String, String> map) {
List<EventSuperior> eventSuperiors = new ArrayList<>();
for (int i = 0; i < resdAll.size(); i++) {
EventSuperior eventSuperior = new EventSuperior();
//excel中一行的数据
List<Object> list = resdAll.get(i);
//获取图片行号-列号集合
Set<String> keySet = map.keySet();
StringBuilder sb = new StringBuilder();
for (String key : keySet) {
Integer row = Integer.valueOf(key.substring(0, key.lastIndexOf("-")));
//当图片中的行号=当前行时
if (row == (i + 1)) {
sb.append(map.get(key)).append(",");
}
}
if (StringUtils.isNotBlank(sb)) {
String imageUrl = sb.substring(0, sb.lastIndexOf(","));
//保存图片路径
eventSuperior.setUploadImg(imageUrl);
}
eventSuperior.setOverseeCode(list.get(0).toString());
eventSuperior.setLevel(list.get(1).toString());
eventSuperior.setReplyDate(list.get(2).toString());
eventSuperior.setFunctionDept(list.get(3).toString());
String deptName = list.get(4).toString();
eventSuperior.setCommunity(deptName);
eventSuperior.setEventStatus(list.get(5) == null ? null : list.get(5).toString());
eventSuperior.setQuestionDescribe(list.get(6).toString());
eventSuperior.setAddressDescribe(list.get(7).toString());
eventSuperior.setBigCategory(list.get(8).toString());
if (list.get(9) != null && list.get(9) != "") {
eventSuperior.setLon(Double.parseDouble(list.get(9).toString()));
}
if (list.get(10) != null && list.get(10) != "") {
eventSuperior.setLat(Double.parseDouble(list.get(10).toString()));
}
eventSuperior.setAddTime(LocalDateTime.now());
eventSuperior.setResult(0);
//添加部门链条
if (StringUtils.isNotBlank(deptName)) {
Address address = addressService.getOneByParentIdAndName(null, deptName);
if (address != null) {
String chainName = StringUtils.isNotBlank(address.getChainNames()) ? address.getChainNames().replaceAll(",", "") + address.getName() : address.getName();
eventSuperior.setDeptId(address.getId());
eventSuperior.setChainName(chainName);
}
}
eventSuperiors.add(eventSuperior);
}
this.saveBatch(eventSuperiors);
}
/**
* 新excel工具类
* 2022.10.10
*/
public class NewExcelUtils {
/**
* @param sheetTitle excel表格名称
* @param head excel表头
* @param result excel表格数据
* @return
*/
public static SXSSFWorkbook getSxssfwbExcel(SXSSFWorkbook wb, SXSSFSheet sheet, String sheetTitle, List<Object> head, List<List<String>> result) {
if (wb == null) {
//创建一个工作薄
wb = new SXSSFWorkbook();
//int sheetNum = 0;// 记录额外创建的sheet数量
//创建excel表
sheet = wb.createSheet(sheetTitle);
// wb.setSheetName(sheetNum, sheetTitle+sheetNum);
}
sheet.setDefaultColumnWidth(15);
sheet.setDefaultRowHeight((short) (8 * 128));
int rownum = 0;
Row row = sheet.createRow(rownum);
Cell cell;
// 创建标题,此时row=0,即第一行
for (int j = 0; j < head.size(); j++) {
cell = row.createCell(j);
cell.setCellValue(head.get(j).toString());
}
// 遍历集合数据,创建excel内容,产生数据行
if (result != null) {
int index = 1;
List<String> m = null;
for (int i = 0; i < result.size(); i++) {
row = sheet.createRow(index);
int cellIndex = 0;
m = result.get(i);
for (String str : m) {
row.createCell((short) cellIndex).setCellValue(str);
cellIndex++;
}
index++;
}
}
return wb;
}
/**
* excel根据指定行列放入图片
*
* @param workbook 工作薄
* @param sheet excel表
* @param fileUrl 图片链接
* @param row 表格行
* @param col 表格列
* @param scaleX 图片在表格中横坐标 默认1.0
* @param scaleY 图片在表格中横坐标 默认1.0
*/
public static void toLeadPicture(SXSSFWorkbook workbook, SXSSFSheet sheet, String fileUrl, int row, int col, double scaleX, double scaleY) {
try {
//防止URL地址有中文,解码
String head = fileUrl.substring(0, fileUrl.lastIndexOf("/") + 1);
String suffix = fileUrl.substring(fileUrl.lastIndexOf("/") + 1);
String link = head + URLEncoder.encode(suffix, "UTF-8");
// 构造URL
URL url = new URL(link);
// 打开连接
URLConnection con = url.openConnection();
//设置请求超时为5s
con.setConnectTimeout(800000 * 1000);
// 输入流
InputStream is = con.getInputStream();
//图片压缩
byte[] bytes1 = PicUtils.compressOfQuality(is, 0.1f);
InputStream in = new ByteArrayInputStream(bytes1);
byte[] bytes = IOUtils.toByteArray(in);
@SuppressWarnings("static-access")
//图片输入流转成字节数组放到workbook 中
int pictureIdx = workbook.addPicture(bytes, workbook.PICTURE_TYPE_PNG);
CreationHelper helper = workbook.getCreationHelper();
Drawing drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = helper.createClientAnchor();
// 图片插入坐标
anchor.setCol1(col);
anchor.setRow1(row);
// 插入图片
Picture pict = drawing.createPicture(anchor, pictureIdx);
pict.resize(scaleX, scaleY);
// 设置宽度 这里如果设置图片的宽度,则excel表格设置的参数将失效
// sheet.setColumnWidth(row, 10 * 256);
// 设置高度 这里如果设置图片的高度,则excel表格设置的参数将失效
Row rowl = sheet.createRow(col);
// rowl.setHeight((short) (30 * 20));
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* excel导入
* 获取excel表格中图片
*
* @param xssfSheet
* @return
*/
public static Map<String, XSSFPictureData> getPictures(XSSFSheet xssfSheet) {
Map<String, XSSFPictureData> map = new HashMap<>();
List<XSSFShape> list = xssfSheet.getDrawingPatriarch().getShapes();
for (XSSFShape shape : list) {
XSSFPicture picture = (XSSFPicture) shape;
//行号-列号
XSSFClientAnchor xssfClientAnchor = (XSSFClientAnchor) picture.getAnchor();
//获取图片
XSSFPictureData pdata = picture.getPictureData();
// 行号-列号
String key = xssfClientAnchor.getRow1() + "-" + xssfClientAnchor.getCol1();
map.put(key, pdata);
byte[] data = pdata.getData();
InputStream inputStream = new ByteArrayInputStream(data);
try {
MockMultipartFile file = new MockMultipartFile(key, inputStream);
} catch (Exception e) {
e.printStackTrace();
}
}
return map;
}
}