因为公司业务需求，要完成针对表格的导入导出，excel这里使用MultipartFile类接收
，下面是部分关键代码，希望有所帮助
//获取excel文件的输入流，必须是.xlsx后缀，如果是xlsx后缀，要用HSSFWorkBook
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(multipartFile.getInputStream());
//获取表格
XSSFSheet sheetAt = xssfWorkbook.getSheetAt(0);
// 创建sheet
Sheet sheet = null;
//获取excel sheet总数
// int sheetNumbers = xssfWorkbook.getNumberOfSheets();
// // sheet list
// List> sheetList = new ArrayList>();
sheet = xssfWorkbook.getSheetAt(0);
// map等待存储excel图片
Map sheetIndexPicMap;
// 判断获取图片和对象
XSSFWorkbook sheetPictrues07 = getSheetPictrues07(0, (XSSFSheet) sheet, xssfWorkbook);
XSSFSheet sheetAt2 = sheetPictrues07.getSheetAt(0);
sheet.shiftRows(1, 1, -1);
下面的代码是只获取图片的处理
public XSSFWorkbook getSheetPictrues07(int sheetNum, XSSFSheet sheet, XSSFWorkbook workbook) throws IOException, OpenXML4JException, XmlException {
for (POIXMLdocumentPart dr : sheet.getRelations()) {
if (dr instanceof XSSFDrawing) {
XSSFDrawing drawing = (XSSFDrawing) dr;
List shapes = drawing.getShapes();
for (XSSFShape shape : shapes) {
XSSFPicture pic = (XSSFPicture) shape;
XSSFClientAnchor anchor = pic.getPreferredSize();
//所在偏移量对象
CTMarker ctMarker = anchor.getFrom();
//获取表格簿
XSSFSheet sheetAt = workbook.getSheetAt(0);
//获取行
XSSFRow row = sheetAt.getRow(ctMarker.getRow());
//创建列
XSSFCell cell = row.createCell(ctMarker.getCol());
//填入其对应上传七牛云的图片编号
cell.setCellValue(printsImg(pic.getPictureData()));
}
}
如果你还要获取嵌入对象的话，需要判断其对象文件的隐性xml格式类型
public XSSFWorkbook getSheetPictrues07(int sheetNum, XSSFSheet sheet, XSSFWorkbook workbook) throws IOException, OpenXML4JException, XmlException {
for (POIXMLdocumentPart dr : sheet.getRelations()) {
PackagePart packagePart = dr.getPackagePart();
String contentType = packagePart.getContentType();
//获取表格簿
XSSFSheet sheetAt = workbook.getSheetAt(0);
switch (contentType) {
case "application/vnd.ms-excel": {//offic 2003 excel
HSSFWorkbook embeddedWorkbook = new HSSFWorkbook(packagePart.getInputStream());
printsImg(packagePart.getInputStream(),"xlx");
break;
}
// Excel Workbook - OpenXML file format offic 2007 excel
case "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet": {
XSSFWorkbook embeddedWorkbook = new XSSFWorkbook(packagePart.getInputStream());
printsImg(packagePart.getInputStream(),"xlsx");
break;
}
// Word document - binary (OLE2CDF) file format offic 2003 word
case "application/msword": {
HWPFdocument document = new HWPFdocument(packagePart.getInputStream());
printsImg(packagePart.getInputStream(),"doc");
break;
}
// Word document - OpenXML file format 2007
case "application/vnd.openxmlformats-officedocument.wordprocessingml.document": {
XWPFdocument document = new XWPFdocument(packagePart.getInputStream());
printsImg(packagePart.getInputStream(),"docx");
break;
}
// PowerPoint document - binary file format 2003 ppt
case "application/vnd.ms-powerpoint": {
HSLFSlideShow slideShow = new HSLFSlideShow(packagePart.getInputStream());
printsImg(packagePart.getInputStream(),"ppt");
break;
}
// PowerPoint document - OpenXML file format
case "application/vnd.openxmlformats-officedocument.presentationml.presentation": {
OPCPackage docPackage = OPCPackage.open(packagePart.getInputStream());
XSLFSlideShow slideShow = new XSLFSlideShow(docPackage);
printsImg(packagePart.getInputStream(),"pptx");
break;
}
//PowerPoint document - OpenXML file format 照片
case "application/vnd.openxmlformats-officedocument.drawing+xml": {
if (dr instanceof XSSFDrawing) {
XSSFDrawing drawing = (XSSFDrawing) dr;
List shapes = drawing.getShapes();
for (XSSFShape shape : shapes) {
XSSFPicture pic = (XSSFPicture) shape;
XSSFClientAnchor anchor = pic.getPreferredSize();
//所在偏移量对象
CTMarker ctMarker = anchor.getFrom();
//获取行
XSSFRow row = sheetAt.getRow(ctMarker.getRow());
//创建列
XSSFCell cell = row.createCell(ctMarker.getCol());
//填入其对应上传七牛云的图片编号
cell.setCellValue(printsImg(pic.getPictureData()));
}
}
break;
}
// Any other type of embedded object.
default:
System.out.println("Unknown Embedded document: " + contentType);
// InputStream inputStream = packagePart.getInputStream();
// (Worksheet)sheet;
OPCPackage aPackage = packagePart.getPackage();
workbook.getSheetIndex(packagePart.getPartName().getName());
break;
}
接下来是导出，但接下来说明一下，两个难点（个人认为）
- 导入时
在表格里面的图片，我可以获取到他的所处位置，然后上传至七牛云，然后将其图片地址插入对应位置的单元格中，是可以的。
但是如果，你的是文件的话，判断文件类型之后，我只能通过packagePart.getInputStream()去获取文件的流，读取到文件，可是我无法获取到文件的位置（所处单元格位置），就没办法和该行的数据对应，我就不能知道他是哪一行数据的携带附件
- 导出时
如果是将文件插入对应的单元格，如果是图片的话，是可以的
在java插入excel是有盲区的，poi反正我尝试了很久，往对应的单元格当中插入对象文件，还是不行，
我无法通过XSSFSheet对象 或者XSSword对象去插入它，图片的到是有一个如下图
ByteArrayOutputStream byteArrayOut = excelData(list.get(i).getPhoto());
//图片格式距离单元格left，top，right，bottom的像素距离
XSSFClientAnchor anchor1 = new XSSFClientAnchor(0, 0, 0, 0, 7,i +1, 8, index);
drawingPatriarch.createPicture(anchor1, wb.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
所以最后决定换成插入的附件文件采用超链接的方式，代码如下
//文件生成超链接方式
XSSFCreationHelper creationHelper = wb.getCreationHelper();
XSSFHyperlink hyperlink = (XSSFHyperlink) creationHelper.createHyperlink(HyperlinkType.URL);
hyperlink.setAddress("域名"+list.get(i).getSurveyManual());
row.createCell(2).setHyperlink(hyperlink);
row.createCell(2).setCellValue("点击下载附件");
最后在附上七牛云工具类
@Slf4j
public class QiniuCloudUtil {
private static final String ACCESS_KEY = "xxxxxxxxxxxxxxxxxxxxxxxxxx";
private static final String SECRET_KEY = "xxxxxxxxxxxxxxxxxxxxxxxxxx";
// 要上传的空间名
private static final String BUCKETNAME = "xxxxxxxxxx";
private static final String DOMAIN = "xxxxxxxxxxxxxxxxxxx";
private static final Auth AUTH = Auth.create(ACCESS_KEY, SECRET_KEY);
public static String[] IMAGE_FILE_ETD = new String[] { "png", "bmp", "jpg", "jpeg","pdf" };
@Resource
private RestTemplate restTemplate;
public static String upload(InputStream file, String ext) throws IOException {
// 创建上传对象，Zone*代表地区
Region region = Region.region2();
Configuration configuration = new Configuration(region);
UploadManager uploadManager = new UploadManager(configuration);
try {
// 调用put方法上传
String token = AUTH.uploadToken(BUCKETNAME);
if (StringUtils.isEmpty(token)) {
System.out.println("未获取到token，请重试！");
return null;
}
String imageName ="fileupload/" + UUID.randomUUID().toString().replaceAll("-","")+"."+ext;
System.out.println("File name = " + imageName);
Response res = uploadManager.put(file, imageName, token,null,null);
// 打印返回的信息
if (res.isOK()) {
Map map = JSON.parseObject(res.bodyString(), Map.class);
return map.get("key").toString();
}
} catch (QiniuException e) {
Response r = e.response;
// 请求失败时打印的异常的信息
e.printStackTrace();
log.error("error " + r.toString());
try {
// 响应的文本信息
log.error(r.bodyString());
} catch (QiniuException e1) {
log.error("error " + e1.error());
}
}
return null;
}
public static String uploadFile(byte[] file, String ext) throws IOException {
// 创建上传对象，Zone*代表地区
Region region = Region.region2();
Configuration configuration = new Configuration(region);
UploadManager uploadManager = new UploadManager(configuration);
try {
// 调用put方法上传
String token = AUTH.uploadToken(BUCKETNAME);
if (StringUtils.isEmpty(token)) {
System.out.println("未获取到token，请重试！");
return null;
}
ByteArrayOutputStream output = new ByteArrayOutputStream();
byte[] buffer = new byte[4096];
int n = 0;
// while (-1 != (n = file.read(buffer))) {
// output.write(buffer, 0, n);
// }
String fileName ="excelFile/" + UUID.randomUUID().toString().replaceAll("-","")+"."+ext;
System.out.println("File name = " + fileName);
Response res = uploadManager.put(file, fileName, token);
// 打印返回的信息
if (res.isOK()) {
Map map = JSON.parseObject(res.bodyString(), Map.class);
return map.get("key").toString();
}
} catch (QiniuException e) {
Response r = e.response;
// 请求失败时打印的异常的信息
e.printStackTrace();
log.error("error " + r.toString());
try {
// 响应的文本信息
log.error(r.bodyString());
} catch (QiniuException e1) {
log.error("error " + e1.error());
}
}finally {
// output.close();
}
return null;
}
public static String uploadFileStream(InputStream inputStream, String ext) throws IOException {
// 创建上传对象，Zone*代表地区
Region region = Region.region2();
Configuration configuration = new Configuration(region);
UploadManager uploadManager = new UploadManager(configuration);
try {
// 调用put方法上传
String token = AUTH.uploadToken(BUCKETNAME);
if (StringUtils.isEmpty(token)) {
System.out.println("未获取到token，请重试！");
return null;
}
String fileName ="excelFile/" + UUID.randomUUID().toString().replaceAll("-","")+"."+ext;
System.out.println("File name = " + fileName);
Response res = uploadManager.put(inputStream, fileName, token,null,null);
// 打印返回的信息
if (res.isOK()) {
Map map = JSON.parseObject(res.bodyString(), Map.class);
return map.get("key").toString();
}
} catch (QiniuException e) {
Response r = e.response;
// 请求失败时打印的异常的信息
e.printStackTrace();
log.error("error " + r.toString());
try {
// 响应的文本信息
log.error(r.bodyString());
} catch (QiniuException e1) {
log.error("error " + e1.error());
}
}finally {
inputStream.close();
}
return null;
}
public byte[] download(String fileUrl) throws IOException {
ResponseEntity res = restTemplate.exchange(fileUrl, HttpMethod.GET, null, byte[].class);
byte[] body = res.getBody();
return body;
}
public static boolean isFileAllowed(String fileName) {
for (String ext : IMAGE_FILE_ETD) {
if (ext.equals(fileName)) {
return true;
}
}
return false;
}
}
以上就是java导出导入excel表的过程，当中的难点解决方式，也希望有大佬指点一下，谢谢。
