How to use Java to generate Excel files and insert pictures in Windows _java

Generate a simple Excel file
in the real office, we often have such a request: the report directly in Excel to open. There is such a demand in practice. Query the user for all information provided by the selected resource and generate Excel for download. But in the query we need to check the information provided by the user to see if there is a mistake (ID). There is error generating error information in Excel.
The Apache POI project is the more mature HSSF interface for working with Excel objects. In fact, poi not only works with Excel, it can also handle word, PowerPoint, Visio, and even outlook.
Here I'll first explain how to build Excel using POI.
First, before we build Excel, we need to understand the organization of Excel files. In poi, this is understood: An Excel file corresponds to a workbook, and a workerbook is composed of several sheet. A sheet has multiple row, and a row typically has multiple cell.
For the above four nouns we can understand in the following figure

Several basic objects are provided for the build Excel,poi:

• Document Object for Hssfworkbook:excel
• Hssfsheet:excel's Form
• Hssfrow:excel's Line
• Lattice unit of Hssfcell:excel

From the pictures above and the organizational structure of Excel, we can understand the steps to create Excel.

        1, Generate Document Object Hsshworkbook.
        2, generate form Hssfsheet through Hssfworkbook.
        3, generating rows through Hssfsheet hssfrow
         4, Generate cell Hssfcell via Hssfrow.
     Below is the presentation code:
     ID error bean (errorcondition.java)

 public class Errorcondition {private string name;//name private string Idcard;// Identity card private String status; Error status private String message; 
    Error information errorcondition (String name,string idcard,string status,string message) {this.name = name; 
    This.idcard = Idcard; 
    This.status = status; 
  this.message = message; 
  Public String GetName () {return name; 
  public void SetName (String name) {this.name = name; 
  Public String Getidcard () {return idcard; 
  } public void Setidcard (String idcard) {this.idcard = Idcard; 
  Public String GetStatus () {return status; 
  public void SetStatus (String status) {this.status = status; 
  Public String GetMessage () {return message; 
  public void Setmessage (String message) {this.message = message; } 
 
} 

     processing Class (Exporterrorexcel.java)

public class Exporterrorexcel {public static void main (string[] args) {//First step to create workbook Hssfworkbook WB = n 
     
    EW Hssfworkbook (); 
     
    The second step is to create sheet hssfsheet sheet = wb.createsheet ("ID error message"); 
    Third step to create row row: Add Table header 0 rows Hssfrow row = sheet.createrow (0);   
    Hssfcellstyle style = Wb.createcellstyle (); Style.setalignment (Hssfcellstyle.align_center);     Center//Step fourth Create cells Hssfcell cell = Row.createcell (0);         First cell cell.setcellvalue ("name");          Set value Cell.setcellstyle (style);          Content Centered cell = Row.createcell (1); 
    Second cell cell.setcellvalue ("identity card"); 
     
    Cell.setcellstyle (style);          Cell = Row.createcell (2); 
    Third cell cell.setcellvalue ("Error state"); 
     
    Cell.setcellstyle (style);          Cell = Row.createcell (3); 
    Cell Fourth Cell.setcellvalue ("error message"); 
     
    Cell.setcellstyle (style); Step Fifth Insert data list<errorcondition> List = EXPORTERROREXCEL.GEterrorcondition (); 
      for (int i = 0; i < list.size (); i++) {errorcondition errorcondition = List.get (i); 
      Create Rows row = Sheet.createrow (i+1); 
      Create the cell and add the data Row.createcell (0). Setcellvalue (Errorcondition.getname ()); 
      Row.createcell (1). Setcellvalue (Errorcondition.getidcard ()); 
      Row.createcell (2). Setcellvalue (Errorcondition.getstatus ()); 
    Row.createcell (3). Setcellvalue (Errorcondition.getmessage ()); The sixth step is to save the build Excel file to the specified path under try {fileoutputstream fout = new FileOutputStream ("D:\\ERRORCONDITION.XL 
      S "); 
      Wb.write (Fout); 
    Fout.close (); 
    catch (IOException e) {e.printstacktrace (); 
  SYSTEM.OUT.PRINTLN ("Excel file Build succeeded ..."); public static list<errorcondition> Geterrorcondition () {list<errorcondition> List = new Arraylis 
     
    T<errorcondition> (); 
    errorcondition r1 = new errorcondition ("John", "4306821989021611", "L", "Length error"); Errorcondition r2 = new errorcondition ("Dick", "430682198902191112", "X", "checksum error"); 
     
    errorcondition r3 = new Errorcondition ("Harry", "" "," N "," Identity card information is empty "); 
    List.add (R1); 
    List.add (R2); 
     
    List.add (R3); 
  return list; 
 } 
}

The above six steps allow you to generate an Excel file at a specified location.

Java POI Implementation inserts a picture into Excel
to do web development is to deal with Excel. Today, the boss gave me a task-export Excel. Start thinking is quite simple, nothing more than to find, build excel,response download can. But a little different, is to add a picture, that is to add a picture for a long time. At the same time the network did not find a better data, so write this blog log, for their own and Bo friends inquiries, reference.
There is a Hssfpatriarch object in poi that is the top-level manager for paint, and its createpicture (anchor, Pictureindex) method can insert a picture in Excel. So to insert a picture in Excel, three steps can be done. First, get Hssfpatriarch object, two, new Hssfclientanchor object, three, call Createpicture method can. Implementation is very easy to achieve, if you want to do it is a little difficult. Here we first insert a picture:

public class Excelimagetest {public static void main (string[] args) {FileOutputStream fileout = null;   
    BufferedImage bufferimg = null; First put the read in the picture into a bytearrayoutputstream in order to produce ByteArray try {bytearrayoutputstream bytearrayout = new Bytearrayou   
      Tputstream ();   
      bufferimg = Imageio.read (New File ("f:/picture/Photo/anonymous/small Zhao 11.jpg")); 
       
      Imageio.write (bufferimg, "JPG", bytearrayout);   
      Hssfworkbook wb = new Hssfworkbook ();  
      Hssfsheet Sheet1 = wb.createsheet ("Test picture");   
      Paint the top-level manager, a sheet can only get one (must pay attention to this) Hssfpatriarch Patriarch = Sheet1.createdrawingpatriarch ();   
      Anchor is primarily used to set the properties of a picture Hssfclientanchor anchor = new Hssfclientanchor (0, 0, 255, 255, (short) 1, 1, (short) 5, 8);   
      Anchor.setanchortype (3); Insert Picture Patriarch.createpicture (anchor, Wb.addpicture (Bytearrayout.tobytearray (), hssfworkbook.picture_type_jpeg))  
      ;   
  Fileout = new FileOutputStream ("d:/test Excel.xls");    Write Excel file Wb.write (fileout); 
    SYSTEM.OUT.PRINTLN ("----excle file has been generated------"); 
    catch (Exception e) {e.printstacktrace (); 
        }finally{if (fileout!= null) {try {fileout.close (); 
        catch (IOException e) {e.printstacktrace (); 
 } 
      } 
    } 
  } 
}

Following is the result of execution:

As for why this is the result, mainly because of the constructor created by Hssfclientanchor (0, 0, 255, 255, (short) 1, 1, (short) 5, 8), I'll explain this constructor: Hssfclientanchor ( int dx1,int dy1,int dx2,int dy2,short col1,int row1,short col2, int row2); The meaning of each parameter is as follows:

• dx1:the x coordinate within.
• Dy1:the y coordinate within.
• dx2:the x coordinate within the second cell.
• Dy2:the y coordinate within the second cell.
• Col1:the column (0 based) of the The "the".
• Row1:the Row (0 based) of the The "the".
• Col2:the column (0 based) of the second cell.
• Row2:the Row (0 based) of the second cell.

Here dx1 and Dy1 define the starting position of the image at the beginning of the cell, and DX2 and Dy2 define the end cell position. Col1, Row1 defines the start cell, the col2, and the row2 defines the end cell.

The following are created by two different constructors, from which we can see clearly the meanings and differences of the eight parameters above.

The above is inserts a picture, then realizes inserts many picture? In fact, it is very simple to construct a number of different Hssfclientanchor objects, control the eight parameters, as follows:

Hssfclientanchor Anchor1 = new Hssfclientanchor (0, 0, 1023,100, (short) 1, 1, (short) 5, 8); 
      Hssfclientanchor Anchor2 = new Hssfclientanchor (0, 0, 1023,100, (short) 1, 9, (short) 5,); 
      
      Insert Picture 
      patriarch.createpicture (Anchor1, Wb.addpicture (Bytearrayout.tobytearray (), Hssfworkbook.picture_type_ JPEG)); 
      Patriarch.createpicture (Anchor2, Wb.addpicture (Bytearrayout.tobytearray (), hssfworkbook.picture_type_jpeg));

Like the rest of the code, you get the following results: