java实现excel导入导出以及解决方案
因为公司业务需求，要完成针对表格的导入导出，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<Map<String, PictureData>> sheetList = new ArrayList<Map<String, PictureData>>();
sheet = xssfWorkbook.getSheetAt(0);
// map等待存储excel图片
Map<String, PictureData> sheetIndexPicMap;
// 判断获取图片和对象
XSSFWorkbook sheetPictrues07 = getSheetPictrues07(0, (XSSFSheet) sheet, xssfWorkbook);
XSSFSheet sheetAt2 = sheetPictrues07.getSheetAt(0);
sheet.shiftRows(1, 1, -1);下面的代码是只获取图片的处理
/**
* 获取Excel2007图片 \ word \execl \PowerPoint
*
* @param sheetNum 当前sheet编号
* @param sheet 当前sheet对象
* @param workbook 工作簿对象
* @return Map key:图片单元格索引（0_1_1）String，value:图片流PictureData
*/
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<XSSFShape> 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格式类型
/**
* 获取Excel2007图片 \ word \execl \PowerPoint
*
* @param sheetNum 当前sheet编号
* @param sheet 当前sheet对象
* @param workbook 工作簿对象
* @return Map key:图片单元格索引（0_1_1）String，value:图片流PictureData
*/
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<XSSFShape> 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 {
/**
* 设置需要操作的账号的AK和SK
*/
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;
/**
* 上传
*
* @param file
* @return
* @throws IOException
*/
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;
}
/**
* 上传
*
* @param file
* @return
* @throws IOException
*/
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;
}
/**
* 下载数据
* @param fileUrl
* @return
* @throws IOException
*/
public byte[] download(String fileUrl) throws IOException {
ResponseEntity<byte[]> res = restTemplate.exchange(fileUrl, HttpMethod.GET, null, byte[].class);
byte[] body = res.getBody();
return body;
}
/**
* 验证文件格式
* @param fileName
* @return
*/
public static boolean isFileAllowed(String fileName) {
for (String ext : IMAGE_FILE_ETD) {
if (ext.equals(fileName)) {
return true;
}
}
return false;
}
}以上就是java导出导入excel表的过程，当中的难点解决方式，也希望有大佬指点一下，谢谢。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
