Use Apache POI to generate Excel and Word documents in Java

Overview:

Recently in the process of doing the project encountered Excel data export and Word Chart Table report export function, and finally decided to use Apache poi to complete the function. This article on the project implementation process of some ideas and code to share with you, at the same time, also as a summary of their own, for later use.

Function:

1, from the database query data export to Excel;

2, the export Word includes, the content has the text, the picture, the form and so on.

Effect:

Export Excel

Export word

Implementation code:

1. Export Excel

Package Beans.excel;import Java.io.FileOutputStream;  Import Java.text.SimpleDateFormat;  Import java.util.ArrayList;    Import java.util.List;  Import Org.apache.poi.hssf.usermodel.HSSFCell;  Import Org.apache.poi.hssf.usermodel.HSSFCellStyle;  Import Org.apache.poi.hssf.usermodel.hssffont;import Org.apache.poi.hssf.usermodel.HSSFRow;  Import Org.apache.poi.hssf.usermodel.HSSFSheet;  Import Org.apache.poi.hssf.usermodel.HSSFWorkbook; public class Createsimpleexceltodisk {/** * @ Function: Manually build a simple format of Excel */private static LIST&LT;STUDENT&G T          Getstudent () throws Exception {list<student> List = new arraylist<student> ();            SimpleDateFormat df = new SimpleDateFormat ("Yyyy-mm-dd");          Student user1 = new Student (1, "Zhang San", +, Df.parse ("1997-03-12"));          Student user2 = new Student (2, "John Doe", +, Df.parse ("1996-08-12"));          Student User3 = new Student (3, "Harry", +, Df.parse ("1985-11-12"));          List.add (user1); LIst.add (User2);            List.add (USER3);      return list; } @SuppressWarnings ("deprecation") public static void main (string[] args) throws Exception {//First step, creating          A webbook, corresponding to an Excel file Hssfworkbook wb = new Hssfworkbook ();          In the second step, add a sheet in WebBook, corresponding to the sheet hssfsheet sheet = wb.createsheet ("Student Form One") in the Excel file;         In the third step, add the No. 0 row of the table header in sheet, note that the old version of POI has a limited number of line numbers for Excel hssfcellstyle Headstyle = Wb.createcellstyle ();              Hssffont f = wb.createfont (); F.setfontheightinpoints ((short) 11);//font f.setboldweight (hssffont.boldweight_bold);//Bold headstyle.se         TFont (f); Headstyle.setalignment (Hssfcellstyle.align_center);        Create a center format headstyle.setborderbottom ((short) 1);        Headstyle.setborderright ((short) 1);        Headstyle.setfillbackgroundcolor ((short) 20);         Hssfrow row = Sheet.createrow ((int) 0); Fourth step, create a cell, and set the value header to set the table header centered Hssfcellstyle style = Wb.creaTecellstyle (); Style.setalignment (Hssfcellstyle.align_center);        Create a center format style.setborderbottom ((short) 1);        Style.setborderright ((short) 1);          String[] Header = new string[]{"study number", "Name", "Age", "Birthday"};        Hssfcell cell = null;         for (int i=0;i
Where the code for the Student class is as follows:
Package Beans.excel;import Java.util.date;public class Student {private int id;      private String name;      private int age;      Private Date birth;        Public Student ()      {      } public        Student (int id, String name, Int. age, Date birth)      {          this.id = ID;          this.name = name;          This.age = age;          This.birth = birth;      }        public int getId ()      {          return ID;      }        public void setId (int id)      {          this.id = ID;      }        Public String getName ()      {          return name;      }        public void SetName (String name)      {          this.name = name;      }        public int getage ()      {          return age;      }        public void Setage (int.)      {          this.age = age;      }        Public Date Getbirth ()      {          return birth;      }        public void Setbirth (Date birth)      {          This.birth = birth;      }  }

2. Export Word Report
Package Beans.excel;import Java.io.fileinputstream;import Java.io.fileoutputstream;import java.io.IOException; Import Java.io.inputstream;import Java.io.outputstream;import Java.math.biginteger;import Org.apache.poi.util.units;import Org.apache.poi.xwpf.usermodel.paragraphalignment;import Org.apache.poi.xwpf.usermodel.xwpfdocument;import Org.apache.poi.xwpf.usermodel.xwpfparagraph;import Org.apache.poi.xwpf.usermodel.xwpfrun;import Org.apache.poi.xwpf.usermodel.xwpftable;import Org.apache.poi.xwpf.usermodel.xwpftablecell;import Org.apache.poi.xwpf.usermodel.xwpftablerow;import Org.openxmlformats.schemas.wordprocessingml.x2006.main.cttcpr;public class Exportdoctest {public static void main (        String[] args) throws Exception {xwpfdocument doc = new xwpfdocument ();        Xwpfparagraph para;        Xwpfrun run; Add text String content = "Argun in the 1689 of the Sino-Russian Nerchinsk treaty became China and Russia, the boundary between the river, Argun upstream, said Hailar, originated from the west of Daxinganling, west to the south of the Batu foothills, and the Northern Line began to call Argun. Argun in Heilongjiang province west of the Inner Mongolia Autonomous Region Eerguna right flag of the TU and Hada near and through the Russian territory after the confluence of the Stone River was called Heilongjiang. Along the ArgunCoastal areas fertile land, dense forests, lush aquatic plants, fish varieties, rich in animal and plant resources, Yi Nong Yi Wood, is the ideal paradise for mankind.        ";   Para = Doc.createparagraph ();        Para.setalignment (paragraphalignment.left);//Set Left to align Run = para.createrun ();        Run.setfontfamily ("imitation");        Run.setfontsize (13);        Run.settext (content);        Doc.createparagraph ();        Add Picture string[] IMGs = {"D:\\bar.png", "D:\\pie.png"};            for (int i=0;i
3. Word Template Replacement

Word templates

Post-replacement effect
Code:
Package Beans.excel;import Java.io.fileinputstream;import Java.io.fileoutputstream;import java.io.IOException; Import Java.io.inputstream;import java.io.outputstream;import Java.util.hashmap;import Java.util.Iterator;import Java.util.list;import Java.util.map;import Java.util.map.entry;import org.apache.poi.xwpf.usermodel.XWPFDocument; Import Org.apache.poi.xwpf.usermodel.xwpfparagraph;import Org.apache.poi.xwpf.usermodel.xwpfrun;import Org.apache.poi.xwpf.usermodel.xwpftable;import Org.apache.poi.xwpf.usermodel.xwpftablecell;import Org.apache.poi.xwpf.usermodel.xwpftablerow;public class Exportwordtest {public static void main (string[] args) throws        Exception, IOException {map<string, object> map=new hashmap<string, object> (); String sum = "Argun in 1689, the Sino-Russian Nerchinsk treaty became the boundary between China and Russia, Argun upstream, said the Hailar River, originated from the west of Daxinganling, west to the south of the Batu foothills, folding and northbound began to be called Argun. Argun in Heilongjiang province west of the Inner Mongolia Autonomous Region Eerguna right flag of the TU and Hada near and through the Russian territory after the confluence of the Stone River was called Heilongjiang. Along the Argun coastal area fertile land, dense forests, lush aquatic plants, fish varieties, rich in animal and plant resources, Yi Nong Yi Wood, is the ideal paradise for mankind.        "; Map.put ("Basin","Argun basin");        Map.put ("sum", sum);        Map.put ("Jnhl", "1");        Map.put ("JWHL", "1");        Map.put ("JNHP", "1");        Map.put ("jwhp", "1");        Map.put ("Jnsk", "1");        Map.put ("Jwsk", "1");        Map.put ("HJ", "6");         Note the Biyezheng_moban.doc document location, in this example, Xwpfdocument doc=new exportwordtest () to apply the root directory. Replacedoc ("D:\\word_temp.docx", map);        try {outputstream OS = new FileOutputStream ("D:\\tttt.doc");            Doc.write (OS);            Os.close (); SYSTEM.OUT.PRINTLN ("Output is successful!        ");        } catch (IOException e) {e.printstacktrace (); }}/** * Read the Word template and replace the variable * @param srcpath * @param map * @return */Public Xwpfdocum Ent Replacedoc (String srcpath, map<string, object> param) {try {//Read Word template Inputstre            Am FIS = new FileInputStream (Srcpath);            Xwpfdocument doc = new xwpfdocument (FIS); Working with paragraph list<xwpfparagraph&Gt              Paragraphlist = Doc.getparagraphs ();            Processparagraph (Paragraphlist,doc,param);              Processing table Iterator<xwpftable> it = Doc.gettablesiterator ();                  while (It.hasnext ()) {xwpftable table = It.next ();                  list<xwpftablerow> rows = Table.getrows ();                      for (Xwpftablerow row:rows) {list<xwpftablecell> cells = row.gettablecells (); for (Xwpftablecell cell:cells) {list<xwpfparagraph> paragraphlisttable = CELL.G                          Etparagraphs ();                      Processparagraph (paragraphlisttable, doc, param);        }}} return doc;            } catch (Exception e) {e.printstacktrace ();        return null; }} public void Processparagraph (list<xwpfparagraph> paragraphlist, xwpfdocument doc,map<string, OBJ ect> param{if (paragraphlist! = null && paragraphlist.size () > 0) {for (Xwpfparagraph paragraph:paragraphlist          ) {list<xwpfrun> runs = paragraph.getruns ();                      for (Xwpfrun run:runs) {String text = run.gettext (0);                          if (text = null) {Boolean issettext = false;                              For (entry<string, object> entry:param.entrySet ()) {String key = Entry.getkey ();                                  if (Text.indexof (key)! =-1) {Issettext = true;                                  Object value = Entry.getvalue ();                                      if (value instanceof String) {//Replaces text = Text.replace (key, value.tostring ());                                System.out.println (text); } else{Text = Text.replaCE (Key, "");                              }}} if (Issettext) {                          Run.settext (text,0); }                      }        }        }       }    } }

POI-related jar packages and api:http://pan.baidu.com/s/1eq6fe8a






Use Apache POI to generate Excel and Word documents in Java