java excel导出导入带图片(支持多个) easypoi
java excel 导入带图片，导出带图片（支持多个）
说不多说 show my code。
导出
control
@ApiOperation(value="xx导出",tags = {"xx"},produces = "application/octet-stream")
@GetMapping(value="/xx/xxExport")
public void xxExport(xxDTO dto){
xxService.xxExport(dto);
}
ServiceImpl
@Override
public void xxExport(xxDTO dto){
/*方法一：按照模板导出*/
File file = null;
try {
// 获取模板文件 （按自己方式和存放的地方）
file = this.getTemplateFile("xx.xlsx", "xx/xx");
if (!file.exists()) {
throw new GzcssBootRuntimeException(CommonConstant.SC_BAD_REQUEST,"未找到导出模板！");
}
String absolutePath = file.getAbsolutePath();
TemplateExportParams params = new TemplateExportParams(absolutePath);
params.setScanAllsheet(true);
params.setColForEach(true);
Map<String, Object> map = new HashMap<>();
List<xxVO> list = new ArrayList<>();
//查询出符合要求的数据
List<xxVO> xxList = this.baseMapper.queryCarList(xxDto);
if (CollectionUtils.isEmpty(xxList)){
//查询列表数据
map.put("list", list);
Workbook workbook = ExcelExportUtil.exportExcel(params, map);
this.download(workbook, "xx信息导出");//fileName
}
else {
for (xxVO ite:xxList) {
//由于我这边是数据存附件id，要去查附件信息，拿到附件的url去excel渲染
if (!StringUtils.isEmpty(ite.getFjid())){
Result<RestSysAttachment> aresult = sysAttachmentNzdcFeign.queryById(ite.getFjid());
RestSysAttachment asysAttachment = aresult.getResult();
if (null != asysAttachment ) {
ite.setFjidUrl(asysAttachment.getAccessUrl());
}
}
// 这个图片是存多个附件id，id和id用","英文逗号，隔开
if (!StringUtils.isEmpty(ite.getFjId2())){
List<String> ids = Arrays.asList(ite.getFjId2().split(","));
String url = "";
for (String id:ids){
Result<RestSysAttachment> bresult = sysAttachmentNzdcFeign.queryById(id);
RestSysAttachment bsysAttachment = bresult.getResult();
if (null != bsysAttachment) {
url = url + ","+ bsysAttachment.getAccessUrl();
}
}
ite.setPhotoHsjcjl(url.substring(1));
}
}
//导出列表
map.put("list", list);
XSSFWorkbook workbook = (XSSFWorkbook) ExcelExportUtil.exportExcel(params, map);
//导出数据是第几个sheet表格
XSSFSheet sheet = workbook.getSheetAt(0);
//导出的数据是第几行开始
int rowNum = 2;
//处理图片
for (int i = 0; i < list.size(); i++) {
XSSFRow row=sheet.getRow(rowNum);
xxVO va = list.get(i);
byte[] data = new byte[0];
//单个图片
if (StrUtil.isNotBlank(va.getFjid())) {
//根据图片路径读取图片
String realName = StrUtil.subAfter(va.getFjid(), "/attachment", false);
String fileSuffix = realName.contains(".") ? realName.substring(realName.lastIndexOf(".")) : "";
AttachmentModel attachmentModel = new AttachmentModel();
attachmentModel.setRealName(realName);
attachmentModel.setSuffix(fileSuffix);
//将file转换成byte[]，写入图片
InputStream is = new FileInputStream(fileUtils.outputFile(attachmentModel));
data = new byte[is.available()];
is.read(data);
is.close();
}
if (data.length > 0) {
//将图片写入excel表格中
XSSFDrawing drawing = sheet.createDrawingPatriarch();
//设置图片像素、导出excel的位置
XSSFClientAnchor anchor = new XSSFClientAnchor(5 * Units.EMU_PER_POINT, 5 * Units.EMU_PER_POINT, -4 * Units.EMU_PER_POINT, -5 * Units.EMU_PER_POINT, (short) 14, i + 2, (short) 15, i + 3);
anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_DO_RESIZE);
//插入图片
drawing.createPicture(anchor, workbook.addPicture(data, XSSFWorkbook.PICTURE_TYPE_JPEG));
}
//多个图片
byte[] data1 = new byte[0];
if (StrUtil.isNotBlank(va.getPhotoHsjcjl())) {
//根据图片路径读取图片
List<String> urls = Arrays.asList(va.getFj2Id().split(","));
int maxSize = urls.size();
if(maxSize>0){
//50：图片高45加两个图片之间的5间隙
//5：是两个图的间隙
row.setHeightInPoints(50*maxSize+5);
}
int m = 0;
//根据图片多少插入
for(String mo :urls) {
String realName1 = StrUtil.subAfter(mo, "/attachment", false);
String fileSuffix1 = realName1.contains(".") ? realName1.substring(realName1.lastIndexOf(".")) : "";
AttachmentModel attachmentModel1 = new AttachmentModel();
attachmentModel1.setRealName(realName1);
attachmentModel1.setSuffix(fileSuffix1);
//将file转换成byte[]，写入图片
InputStream is1 = new FileInputStream(fileUtils.outputFile(attachmentModel1));
data1 = new byte[is1.available()];
is1.read(data1);
is1.close();
if (data1.length > 0) {
//将图片写入excel表格中
XSSFDrawing drawing1 = sheet.createDrawingPatriarch();
XSSFClientAnchor anchor1 = new XSSFClientAnchor(5*Units.EMU_PER_POINT, (5*(m+1)+45*m)*Units.EMU_PER_POINT, -4*Units.EMU_PER_POINT,-(5+50*(maxSize-m-1))*Units.EMU_PER_POINT, (short) 16, i + 2, (short) 17, i + 3);
anchor1.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_DO_RESIZE);
//插入图片
drawing1.createPicture(anchor1, workbook.addPicture(data1, XSSFWorkbook.PICTURE_TYPE_JPEG));
}
m++;
}
}
rowNum ++;
}
gzcssExcelUtils.download(workbook, "施工车辆信息导出");//fileName
}
}catch (Exception e){
log.error(e.getMessage(),e);
}finally {
if(file !=null){
file.delete();
}
}
}
public File getTemplateFile(String name, String path) throws Exception {
String fix = name.substring(name.indexOf("."));
String dest = System.getProperty("java.io.tmpdir") + File.separator + SequenceUtil.generateRandomString()+fix;
AttachmentModel model = new AttachmentModel();
model.setRealName(name);
model.setAttachmentType(path);
return fileUtils.outputFile(model, dest);
}
public void download(Workbook workbook, String title) throws IOException {
String tempFileName = RandomUtil.randomNumbers(16);
String tempFileSuffix = "";
if (workbook instanceof HSSFWorkbook) {
tempFileSuffix = ".xls";
} else if (workbook instanceof XSSFWorkbook) {
tempFileSuffix = ".xlsx";
}
File tempFile = File.createTempFile(tempFileName, tempFileSuffix);
String fileName = title + tempFileSuffix;
FileOutputStream fos = new FileOutputStream(tempFile);
workbook.write(fos);
fos.close();
String contentType = Files.probeContentType(Paths.get(tempFile.getAbsolutePath()));
HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
response.setHeader("Content-Type", contentType);
if (this.isIE()) {
fileName = URLEncoder.encode(fileName, "UTF8");
} else {
fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
}
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
FileUtil.writeToStream(tempFile, response.getOutputStream());
if (tempFile.exists()) {
tempFile.delete();
}
}
导入带图片
control
@ApiOperation(value = "导入xx", tags = {"xx"})
@PostMapping(value = "/sgCarBaseInfos/import")
public Result<ImportResult> importCarBaseInfo(@ApiParam(value = "导入模板", required = true)
@RequestParam(value = "file") MultipartFile file) {
ImportResult importResult = sgCarBaseInfoService.importCarBaseInfo(file);
if(importResult.getFailNum()==0){
return Result.buildSuccess(200,"导入成功" ,importResult);
}else{
return Result.buildError(400,"导入失败", importResult);
}
}
ImportResult importCarBaseInfo(MultipartFile file);
serviceImpl
@Override
@Transactional(rollbackFor = Exception.class)
public ImportResult importCarBaseInfo(MultipartFile file) {
ImportResult res = new ImportResult();
ImportParams params = new ImportParams();
params.setNeedVerify(true);
params.setTitleRows(2);//列是第几行开始
params.setImportFields(new String[]{"序号","xx*", "a*", "b*", "c*", "d", "xx", "xx*", "xx*", "xx*","xx*", "xx*", "xx*", "图片1 单个*", "xx*", "图片2（多个）*"});
try {
//读取EXCEL
ExcelImportResult<xxVO> data = ExcelImportUtil.importExcelMore(file.getInputStream(), CarExcelImortVO.class, params);
//EXCEL数据
List<xxVO> checkDataList = data.getList();
List<xxVO> eList = data.getFailList();
if (null != checkDataList){
for (int e = 0;e<checkDataList.size();e++) {
if(ObjectUtil.checkObjAllFieldsIsNullForImport(checkDataList.get(e))){
checkDataList.remove(e);
}
}
//因为特殊，校验都给后台校验
for (int r = 0;r<eList.size();r++) {
if(!ObjectUtil.checkObjAllFieldsIsNullForImport(eList.get(r))){
checkDataList.add(eList.get(r));
}
}
}
//2.处理图片
XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
Sheet sheet = workbook.getSheetAt(0);
String dw = PoiCellUtil.getCellValue(sheet, 1, 0);
//图片1*
Map<Integer, List<XSSFShape>> siteImgMap = new HashMap<>();
this.arrangeExcelImg(workbook, siteImgMap, 13); // 在导入excel 里面的第几行
//图片2*
Map<Integer, List<XSSFShape>> siteImgMap1 = new HashMap<>();
this.arrangeExcelImg(workbook, siteImgMap1, 15); //在导入excel 里面的第几行
//校验数据
List<CarExcelImortVO> errorMsgList = new ArrayList<>();
int m = 3;
String au ="";
for (CarExcelImortVO detailVO : checkDataList) {
StringBuilder msg = new StringBuilder();
//校验 导入数据。自行添加即可
if (!ObjectUtil.checkObjAllFieldsIsNullForImport(detailVO)) {
if (StringUtils.isEmpty(detailVO.getPersonName())) {
msg.append("姓名不能为空；");
}
。。。。。。。。。。。
//图片1
String a = "";
String b = "";
if (CollUtil.isEmpty(siteImgMap.get(m))) {
msg.append("图片1缺失、如果不为空，请设置图片为浮动在单元格表面，且不能超出单元格范围；");
a ="1";
}
//图片2
if (CollUtil.isEmpty(siteImgMap1.get(m))) {
msg.append("图片2缺失、如果不为空，请设置图片为浮动在单元格表面，且不能超出单元格范围；");
b= "1";
}
//此次是不关校验成功和失败，都将图片上传，方便失败时下载带图片。可自行处理
//上传图片
if (!"1".equals(a)){
String photoYkm = "";
List<XSSFShape> shapes = siteImgMap.get(m);
for (int i = 0; i < shapes.size(); i++) {
RestSysAttachment attachment = attachmentImportUpload(shapes.get(i), SgConstant.CAR_BASE_INFO, "图片1" + i);
if (null != attachment){
photoYkm = attachment.getId();
}
}
detailVO.setPhotoYkm(photoYkm);
}
if (!"1".equals(b)){
String photoHsjcjl = "";
List<XSSFShape> shapes1 = siteImgMap1.get(m);
for (int i = 0; i < shapes1.size(); i++) {
RestSysAttachment attachment1 = attachmentImportUpload(shapes1.get(i), SgConstant.CAR_BASE_INFO, "图片2" + i);
if(photoHsjcjl.length() == 0){
if (null != attachment1){
photoHsjcjl = attachment1.getId();
}
}else {
photoHsjcjl = photoHsjcjl+","+attachment1.getId();
}
}
detailVO.setPhotoHsjcjl(photoHsjcjl);
}
m++;
if (msg.length() > 0) {
detailVO.setErrorMsg(msg.toString());
errorMsgList.add(detailVO);
}
}
}
if (errorMsgList.size() >0){
//有错误，返回错误列表
String identifier = res.getIdentifier();
redisUtil.hset(CAR_FAIL_INFO, "exportFailList_" + identifier, errorMsgList, 2 * 24 * 60 * 60L);
redisUtil.hset(CAR_FAIL_INFO, "exportFailDw_" + identifier, au, 2 * 24 * 60 * 60L);
res.setFailNum(checkDataList.size());
}else {
//全部校验通过
//入库
。。
}
} catch (Exception exception) {
String message = exception.getMessage();
log.error("施工车辆信息导入异常：" + exception.getMessage(), exception);
if (message.equals("不是合法的Excel模板")) message = "请使用模板文件进行导入";
throw new GzcssBootRuntimeException(CommonConstant.SC_BAD_REQUEST, message, exception);
}
return res ;
}
/**
* 整理图片
*
* @param workbook
* @param siteImgMap
* @param col
* @return void
*/
private void arrangeExcelImg(XSSFWorkbook workbook, Map<Integer, List<XSSFShape>> siteImgMap, int col) {
if (workbook.getSheetAt(0).getDrawingPatriarch() != null) {
for (XSSFShape shape : workbook.getSheetAt(0).getDrawingPatriarch().getShapes()) {
XSSFClientAnchor anchor = (XSSFClientAnchor) shape.getAnchor();
if (shape instanceof XSSFPicture) {
if (anchor.getCol1() == col) {
if (siteImgMap.get(anchor.getRow1()) == null) {
List<XSSFShape> siteImg = new ArrayList<>();
siteImg.add(shape);
siteImgMap.put(anchor.getRow1(), siteImg);
} else {
siteImgMap.get(anchor.getRow1()).add(shape);
}
}
}
}
}
}
private RestSysAttachment attachmentImportUpload(XSSFShape shape, String attachmentType, String fileName) {
if (shape != null) {
AttachmentModel attachmentModel = new AttachmentModel();
attachmentModel.setRealName(fileName);
attachmentModel.setAttachmentType(attachmentType);
attachmentModel.setName(fileName);
attachmentModel.setId(SequenceUtil.generateRandomString());
XSSFPicture pic = (XSSFPicture) shape;
XSSFPictureData pictureData = pic.getPictureData();
String suffix = "." + pictureData.suggestFileExtension();
attachmentModel.setSuffix(pictureData.suggestFileExtension());
attachmentModel.setName(fileName + suffix);
File tempFile = null;
try {
tempFile = File.createTempFile(fileName, suffix);
OutputStream output = new FileOutputStream(tempFile);
BufferedOutputStream bufferedOutput = new BufferedOutputStream(output);
bufferedOutput.write(pic.getPictureData().getData());
fileUtils.upload(attachmentModel, tempFile);
output.close();
bufferedOutput.flush();
bufferedOutput.close();
} catch (Exception e) {
log.error("图片导入上传问题：" + e.getMessage());
} finally {
if (tempFile.exists()) {
tempFile.delete();
}
}
//插入附件对象 上传附件到服务器或则指定的地方
。。。
return attachment;
} else {
return null;
}
}
