java implementation of excel import and export and solution
Because of the company's business requirements, excel uses the MultipartFile class to import and export tables
, here are some key codes. I hope they can help
//To obtain the input stream of excel file, it must be. Xlsx suffix. If it is xlsx suffix, use HSSFWorkBook
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(multipartFile.getInputStream());
//Get table
XSSFSheet sheetAt = xssfWorkbook.getSheetAt(0);
// Create sheet
Sheet sheet = null;
//Get the total number of Excel sheets
// int sheetNumbers = xssfWorkbook.getNumberOfSheets();
// // sheet list
// List<Map<String, PictureData>> sheetList = new ArrayList<Map<String, PictureData>>();
sheet = xssfWorkbook.getSheetAt(0);
// map waiting to store excel pictures
Map<String, PictureData> sheetIndexPicMap;
// Judge to get pictures and objects
XSSFWorkbook sheetPictrues07 = getSheetPictrues07(0, (XSSFSheet) sheet, xssfWorkbook);
XSSFSheet sheetAt2 = sheetPictrues07.getSheetAt(0);
sheet.shiftRows(1, 1, -1);
The following code is the processing of obtaining only pictures
/**
* Get Excel 2007 pictures \ word \execl \PowerPoint
*
* @param sheetNum Current sheet number
* @param sheet Current sheet object
* @param workbook Workbook object
* @return Map key:Picture cell index (0_1_1) String, value: picture stream 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();
//Offset object
CTMarker ctMarker = anchor.getFrom();
//Get form book
XSSFSheet sheetAt = workbook.getSheetAt(0);
//Get row
XSSFRow row = sheetAt.getRow(ctMarker.getRow());
//Create column
XSSFCell cell = row.createCell(ctMarker.getCol());
//Fill in the picture number corresponding to the uploaded qiniu cloud
cell.setCellValue(printsImg(pic.getPictureData()));
}
}
If you want to get the embedded object, you need to judge the implicit xml format type of its object file
/**
* Get Excel 2007 pictures \ word \execl \PowerPoint
*
* @param sheetNum Current sheet number
* @param sheet Current sheet object
* @param workbook Workbook object
* @return Map key:Picture cell index (0_1_1) String, value: picture stream 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();
//Get form book
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 photo
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();
//Offset object
CTMarker ctMarker = anchor.getFrom();
//Get row
XSSFRow row = sheetAt.getRow(ctMarker.getRow());
//Create column
XSSFCell cell = row.createCell(ctMarker.getCol());
//Fill in the picture number corresponding to the uploaded qiniu cloud
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;
}
The next step is export, but the next step is to explain two difficulties (I think)
- When importing
For the pictures in the table, I can get their location, upload them to qiniu cloud, and then insert their picture address into the cell of the corresponding location. It's OK.
However, if your file is a file, after judging the file type, I can only get the file stream and read the file through packagePart.getInputStream(), but I can't get the file location (cell location), so I can't correspond to the data of this line, and I can't know which line of data it is the carrying attachment
- When exporting
If the file is inserted into the corresponding cell, it is OK if it is a picture
There is a blind spot when inserting excel into java. poi anyway, I tried for a long time and couldn't insert the object file into the corresponding cell,
I can't insert it through XSSFSheet object or XSSword object. There is one in the picture, as shown in the figure below
ByteArrayOutputStream byteArrayOut = excelData(list.get(i).getPhoto());
//Pixel distance between picture format and cells left, top, right and 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));
Therefore, it is finally decided to replace the inserted attachment file with a hyperlink. The code is as follows
//File generation hyperlink method
XSSFCreationHelper creationHelper = wb.getCreationHelper();
XSSFHyperlink hyperlink = (XSSFHyperlink) creationHelper.createHyperlink(HyperlinkType.URL);
hyperlink.setAddress("domain name"+list.get(i).getSurveyManual());
row.createCell(2).setHyperlink(hyperlink);
row.createCell(2).setCellValue("Click to download the attachment");
Finally, seven cattle cloud tools are attached
@Slf4j
public class QiniuCloudUtil {
/**
* Set the AK and SK of the account to be operated
*/
private static final String ACCESS_KEY = "xxxxxxxxxxxxxxxxxxxxxxxxxx";
private static final String SECRET_KEY = "xxxxxxxxxxxxxxxxxxxxxxxxxx";
// Space name to upload
private static final String BUCKETNAME = "xxxxxxxxxx";
/**
* Used when reading the external chain domain name
*/
private static final String DOMAIN = "xxxxxxxxxxxxxxxxxxx";
/**
* secret key
*/
private static final Auth AUTH = Auth.create(ACCESS_KEY, SECRET_KEY);
/**
* Suffix extensions allowed for files
*/
public static String[] IMAGE_FILE_ETD = new String[] { "png", "bmp", "jpg", "jpeg","pdf" };
@Resource
private RestTemplate restTemplate;
/**
* upload
*
* @param file
* @return
* @throws IOException
*/
public static String upload(InputStream file, String ext) throws IOException {
// Create an upload object. Zone * represents region
Region region = Region.region2();
Configuration configuration = new Configuration(region);
UploadManager uploadManager = new UploadManager(configuration);
try {
// Call the put method to upload
String token = AUTH.uploadToken(BUCKETNAME);
if (StringUtils.isEmpty(token)) {
System.out.println("Not obtained token，Please try again!");
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);
// Print returned information
if (res.isOK()) {
Map map = JSON.parseObject(res.bodyString(), Map.class);
return map.get("key").toString();
}
} catch (QiniuException e) {
Response r = e.response;
// Abnormal information printed when the request fails
e.printStackTrace();
log.error("error " + r.toString());
try {
// Text information of the response
log.error(r.bodyString());
} catch (QiniuException e1) {
log.error("error " + e1.error());
}
}
return null;
}
/**
* upload
*
* @param file
* @return
* @throws IOException
*/
public static String uploadFile(byte[] file, String ext) throws IOException {
// Create an upload object. Zone * represents region
Region region = Region.region2();
Configuration configuration = new Configuration(region);
UploadManager uploadManager = new UploadManager(configuration);
try {
// Call the put method to upload
String token = AUTH.uploadToken(BUCKETNAME);
if (StringUtils.isEmpty(token)) {
System.out.println("Not obtained token，Please try again!");
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);
// Print returned information
if (res.isOK()) {
Map map = JSON.parseObject(res.bodyString(), Map.class);
return map.get("key").toString();
}
} catch (QiniuException e) {
Response r = e.response;
// Abnormal information printed when the request fails
e.printStackTrace();
log.error("error " + r.toString());
try {
// Text information of the response
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 {
// Create an upload object. Zone * represents region
Region region = Region.region2();
Configuration configuration = new Configuration(region);
UploadManager uploadManager = new UploadManager(configuration);
try {
// Call the put method to upload
String token = AUTH.uploadToken(BUCKETNAME);
if (StringUtils.isEmpty(token)) {
System.out.println("Not obtained token，Please try again!");
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);
// Print returned information
if (res.isOK()) {
Map map = JSON.parseObject(res.bodyString(), Map.class);
return map.get("key").toString();
}
} catch (QiniuException e) {
Response r = e.response;
// Abnormal information printed when the request fails
e.printStackTrace();
log.error("error " + r.toString());
try {
// Text information of the response
log.error(r.bodyString());
} catch (QiniuException e1) {
log.error("error " + e1.error());
}
}finally {
inputStream.close();
}
return null;
}
/**
* Download data
* @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;
}
/**
* Verify file format
* @param fileName
* @return
*/
public static boolean isFileAllowed(String fileName) {
for (String ext : IMAGE_FILE_ETD) {
if (ext.equals(fileName)) {
return true;
}
}
return false;
}
}
The above is the process of exporting and importing excel tables from java. I hope you can give me some advice. Thank you.