Use Apache POI in Java to generate excel and Word documents

Use Apache POI in Java to generate excel and Word documents

Overview:

Recently, when I was working on a project, I encountered the excel data export and word text table report export functions. Finally, I decided to use Apache POI to complete this function. This article shares some ideas and code in the project implementation process, and also serves as a summary for future use.

 

Function:

1. Export the query data from the database to excel;

2. Export the word, including text, images, and tables.

 

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: create a simple format Excel */private static List manually
 
  
GetStudent () throws Exception {List
  
   
List = new ArrayList
   
    
(); SimpleDateFormat df = new SimpleDateFormat ("yyyy-mm-dd"); Student user1 = new Student (1, "James", 16, df. parse ("1997-03-12"); Student user2 = new Student (2, "Li Si", 17, df. parse ("1996-08-12"); Student user3 = new Student (3, "Wang Wu", 26, 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 {// step 1, create a webbook, corresponding to an Excel file HSSFWorkbook wb = new HSSFWorkbook (); // Step 2: Add a sheet to the webbook, corresponding to the sheet HSSFSheet sheet = wb in the Excel file. createSheet ("Student table 1"); // Step 3: Add row 0th to the table header. Note that the earlier version of poi has a limit on the number of rows in Excel. createCellStyle (); HSSFFont f = wb. createFont (); f. setFontHeightInPoints (short) 11); // font size f. setBoldweight (HSSFFont. BOLDWEIGHT_BOLD); // bold headStyle. setFont (f); headStyle. setAlignment (HSSFCellStyle. ALIGN_CENTER); // create a headStyle in the center format. setBorderBottom (short) 1); headStyle. setBorderRight (short) 1); headStyle. setFillBackgroundColor (short) 20); HSSFRow row = sheet. createRow (int) 0); // Step 4: Create a cell and set the value header to center the header with HSSFCellStyle = wb. createCellStyle (); style. setAlignment (HSSFCellStyle. ALIGN_CENTER); // create a center style. setBorderBottom (short) 1); style. setBorderRight (short) 1); String [] header = new String [] {"student ID", "name", "Age", "Birthday"}; HSSFCell cell = null; for (int I = 0; I
    
     
List = CreateSimpleExcelToDisk. getStudent (); for (int I = 0; I <list. size (); I ++) {row = sheet. createRow (int) I + 1); Student stu = (Student) list. get (I); // Step 4: Create a cell and set the value of cell = row. createCell (short) 0); cell. setCellValue (stu. getId (); cell. setCellStyle (style); cell = row. createCell (short) 1); cell. setCellValue (stu. getName (); cell. setCellStyle (style); cell = row. createCell (short) 2); cell. SetCellValue (stu. getAge (); cell. setCellStyle (style); cell = row. createCell (short) 3); cell. setCellValue (new SimpleDateFormat ("yyyy-mm-dd "). format (stu. getBirth (); cell. setCellStyle (style);} // Step 6: Save the file to the specified location. try {FileOutputStream fout = new FileOutputStream ("D:/students.xls"); wb. write (fout); fout. close (); System. out. println ("output successful! ");} Catch (Exception e) {e. printStackTrace ();}}}
    
   
  
 

The code of 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 age)      {          this.age = age;      }        public Date getBirth()      {          return birth;      }        public void setBirth(Date birth)      {          this.birth = birth;      }  }

2. Export the 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 the text String content = "ergou was the border river between China and Russia in the China-Russian Treaty of November 1689, And the Hailar River in the upper part of ergu river originated from the west side of daxing'anling, the west flow to the foot of the map, and the north line began to call the ergu region. In the vicinity of enhe and hada in the right flag of erguna in the Inner Mongolia Autonomous Region, west of Mohe County, Heilongjiang Province, it began to be called Heilongjiang after it joined the shlekhash river flowing through Russia. Along the coast of ergu, the land is fertile, the forests are dense, the water plants are beautiful, there are many fish varieties, rich animal and plant resources, and Yi Nong Yi Mu is an ideal paradise for mankind. "; Para = doc. createParagraph (); para. setAlignment (ParagraphAlignment. LEFT); // set the LEFT-aligned run = para. createRun (); run. setFontFamily (""); run. setFontSize (13); run. setText (content); doc. createParagraph (); // Add the image String [] imgs = {"D :\\ bar.png", "D :\\ pie.png"}; for (int I = 0; i3, word template replacement
 
Word template
Effect after replacement
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
  
   
Map = new HashMap
   
    
(); String sum = "ergu was the border river between China and Russia in the Sino-Russian Treaty of 1689, And the Hailar River, the upstream of ergu, originated from the west side of Daxinganling, the west flow to the foot of the map, and the north line began to call the ergu region. In the vicinity of enhe and hada in the right flag of erguna in the Inner Mongolia Autonomous Region, west of Mohe County, Heilongjiang Province, it began to be called Heilongjiang after it joined the shlekhash river flowing through Russia. Along the coast of ergu, the land is fertile, the forests are dense, the water plants are beautiful, there are many fish varieties, rich animal and plant resources, and Yi Nong Yi Mu is an ideal paradise for mankind. "; Map. put ("basin", "ergonia basin"); map. put ("sum", sum); map. put ("junregistered", "1"); map. put ("jwhl", "1"); map. put ("jnhp", "1"); map. put ("batch HP", "1"); map. put ("jnsk", "1"); map. put ("SK", "1"); map. put ("hj", "6"); // biyezheng_moban.doc file location. In this example, XWPFDocument doc = new ExportWordTest () is the application root directory (). replaceDoc ("D :\\ word_temp.docx", map); try {OutputStream OS = new FileOutputStream ("D :\\ tttt.doc"); doc. write (OS); OS. c Lose (); System. out. println ("output successful! ");} Catch (IOException e) {e. printStackTrace () ;}}/*** read the word template and replace the variable * @ param srcPath * @ param map * @ return */public XWPFDocument replaceDoc (String srcPath, Map
    
     
Param) {try {// read the word template InputStream FCM = new FileInputStream (srcPath); XWPFDocument doc = new XWPFDocument (FCM); // process the section List
     
      
ParagraphList = doc. getParagraphs (); processParagraph (paragraphList, doc, param); // Process Table Iterator
      
       
It = doc. getTablesIterator (); while (it. hasNext () {XWPFTable table = it. next (); List
       
         Rows = table. getRows (); for (XWPFTableRow row: rows) {List
        
          Cells = row. getTableCells (); for (XWPFTableCell cell: cells) {List
         
           ParagraphListTable = cell. getParagraphs (); processParagraph (paragraphListTable, doc, param) ;}} return doc;} catch (Exception e) {e. printStackTrace (); return null ;}} public void processParagraph (List
          
            ParagraphList, XWPFDocument doc, Map
           
             Param) {if (paragraphList! = Null & paragraphList. size ()> 0) {for (XWPFParagraph paragraph: paragraphList) {List
            
              Runs = paragraph. getRuns (); for (XWPFRun run: runs) {String text = run. getText (0); if (text! = Null) {boolean isSetText = false; for (Entry
             
               Entry: param. entrySet () {String key = entry. getKey (); if (text. indexOf (key )! =-1) {isSetText = true; Object value = entry. getValue (); if (value instanceof String) {// Replace text with text = text. replace (key, value. toString (); System. out. println (text);} else {text = text. replace (key, "") ;}}if (isSetText) {run. setText (text, 0 );}}}}}}}
             
            
           
          
         
        
       
      
     
    
   
  
 
 
POI jar packages and APIs: http://pan.baidu.com/s/1eQ6fE8a