Use apachepoi to create an Excel file with images

In the latest project, the function of exporting list data to an Excel table is used. The project uses Apache poi to generate an Excel file.

Due to the complexity of the technology used, I would like to list some implementation details here as a record and memo.

First, we need to use the jar package POI. I am using the project framework. The version may be old, but the function is still complete.

　　

I first paste the code and explain it:

1 package test; 2 3 Import Java. AWT. image. bufferedimage; 4 Import Java. io. bytearrayoutputstream; 5 import Java. io. file; 6 Import Java. io. fileoutputstream; 7 Import java.net. URL; 8 9 Import javax. imageIO. imageIO; 10 Import javax. swing. filechooser. filesystemview; 11 12 Import Org. apache. poi. hssf. usermodel. hssfclientanchor; 13 Import Org. apache. poi. hssf. usermodel. hssfpatriarch; 14 Import Org. apache. poi. hssf. usermodel. hssfsheet; 15 Import Org. apache. poi. hssf. usermodel. hssfworkbook; 16 17 // test Apache poi18 public class main {19 public static void main (string [] ARGs) {20 // Step 1: obtain image resource 21 bufferedimage Bi = NULL; // declare image 22 // 1. get image resources from the network (the following picture is Baidu homepage picture) 23 // URL url = new URL ("http://www.baidu.com/img/shouye_b5486898c692066bd2cbaeda86d74448.gif"); 24 // Bi = ImageIO. read (URL); 25 // 2. obtain image resources from a physical storage device (the following example shows obtaining images from the author's desktop) 26 file F = new file (filesystemview. getfilesystemview (). gethomedirectory () + "/baidu.gif"); 27 Bi = ImageIO. read (f); 28 29 // Step 2: process the image (most of the time the image fails to be processed) 30 bytearrayoutputstream Bos = new bytearrayoutputstream (); 31 ImageIO. write (Bi, "jpg", Bos); 32 33 // Step 3: Create an Excel file and insert an image (POI is required here) 34 hssfworkbook WB = new hssfworkbook (); // create a workbook 35 hssfsheet sheet = WB. createsheet ("sample"); // create a sheet-_-36 hssfpatriarch patriarch = sheet. createdrawingpatriarch (); // create an object for drawing 37 // The above parts are shared, patriarch is responsible for 38 hssfclientanchor anchor = new hssfclientanchor (0, 0, 0, (short) 0, 0, (short) 1, 1); // create an image dock object 39 // anchor. setanchortype (2); set the image for which this attribute is set. The first image does not need to be set to 40 patriarch. createpicture (anchor, WB. addpicture (Bos. tobytearray (), hssfworkbook. picture_type_jpeg); // this step is to insert the image to the bed sheet sample according to the above attributes. // Step 4: Save (or download) generated Excel (saved to the desktop) 43 file fo = new file (filesystemview. getfilesystemview (). gethomedirectory () + "/sample.xls"); 44 fileoutputstream Fos = NULL; 45 Fos = new fileoutputstream (FO); 46 WB. write (FOS); 47 FOS. flush (); 48 FOS. close (); 49} 50}

　　

(The exception is not handled, because in the end we need to write the code more standardized, and the exception processing should be better, just for reference)

I will explain a little. First, the image is generally obtained in two ways: Network and disk. We can see that POI's support and resolution for images is not so powerful. We need to use ImageIO to extract the image content, and uses the JPG format to write the buffer for poi.

The second step is to place images in Excel. By the way, we will mention the various anchor parameters. To facilitate your understanding, I will first post the code running results to you:

　　

As you can see, an image is displayed in the grid with the lower right corner of "" at "". This explains the last four parameters of anchor, that is, the coordinates in the upper left corner and lower right corner of the image, I don't know why the X coordinate is short?

(So we didn't create row and column in sheet to place images, because the images and text in office suites are not placed in a plane, so we can understand that: text is placed in the grid of rows and columns, while images are immersed in the grid)

So what are the first four parameters of anchor? In fact, Excel divides a grid into x256 small grids to make the images more accurate, we can set the horizontal and vertical offset (0-1023 or 0-255) for the image relative to the upper left corner and lower right corner. After reading this, you will understand:

　　

(Set the first four parameters of anchor to 512,126)

　　

(Set the first four parameters of anchor to 0, 0, 512,126)

　　

(Undoubtedly, set the four parameters for anchor to 512,126,512,126)

You can use sheet. setcolumwidth and row. setheight to set the column width and Row Height.

Paste my code:

1 package test; 2 3 Import Java. AWT. image. bufferedimage; 4 Import Java. io. bytearrayoutputstream; 5 import Java. io. file; 6 Import Java. io. filenotfoundexception; 7 Import Java. io. fileoutputstream; 8 Import Java. io. ioexception; 9 Import java.net. malformedurlexception; 10 Import java.net. URL; 11 12 Import javax. imageIO. imageIO; 13 Import javax. swing. filechooser. filesystemview; 14 15 Import Org. apache. poi. hssf. usermodel. hssfclientanchor; 16 Import Org. apache. poi. hssf. usermodel. hssfpatriarch; 17 import Org. apache. poi. hssf. usermodel. hssfsheet; 18 Import Org. apache. poi. hssf. usermodel. hssfworkbook; 19 20 // test Apache poi21 public class main {22 public static void main (string [] ARGs) {23 bufferedimage Bi = NULL; 24 bytearrayoutputstream Bos = new bytearrayoutputstream (); 25 hssfworkbook WB = new hssfworkbook (); 26 hssfsheet sheet = WB. createsheet ("sample"); 27 hssfpatriarch patriarch = sheet. createdrawingpatriarch (); 28 fileoutputstream Fos = NULL; 29 30 try {31 URL url = new URL ("http://www.baidu.com/img/shouye_b5486898c692066bd2cbaeda86d74448.gif"); 32 Bi = ImageIO. read (URL); 33 34 // file F = new file (filesystemview. getfilesystemview (). gethomedirectory () + "/baidu.gif"); 35 // Bi = ImageIO. read (f); 36 37 ImageIO. write (Bi, "jpg", Bos); 38 hssfclientanchor anchor = new hssfclientanchor (512,126,512,126, (short) 0, (short) 1, 1); 40 // anchor. setanchortype (2); 41 patriarch. createpicture (anchor, WB. addpicture (Bos. tobytearray (), hssfworkbook. picture_type_jpeg); 42 43 file fo = new file (filesystemview. getfilesystemview () 44. gethomedirectory () + "/sample.xls"); 45 Fos = new fileoutputstream (FO); 46 WB. write (FOS); 47 FOS. flush (); 48 FOS. close (); 49 50 51} catch (malformedurlexception e) {52 // todo auto-generated catch block53 E. printstacktrace (); 54} catch (filenotfoundexception e) {55 // todo auto-generated catch block56 E. printstacktrace (); 57} catch (ioexception e) {58 // todo auto-generated catch block59 E. printstacktrace (); 60} 61} 62}

Note: bytearrayoutputstream and hssfclientanchor are disposable products. They must be declared before they are actually used and then released (it is an informal demonstration. Obviously, Bos will overflow after multiple images ).

Anchortype starts from 2, and 1 does not need to be set.

The generated XLS is downloaded from the server to the client and will be discussed later.

 

You are welcome to move to our chat group and pass the time together when you are bored:

Or contact me via QQ:

(Last editing time: 13:46:00)