Java中用Apache POI產生excel和word文檔

標籤：apache poi   word匯出   excel匯出   

概述：

最近在做項目的過程中遇到了excel的資料匯出和word的圖文表報告的匯出功能，最後決定用Apache POI來完成該項功能。本文就項目實現過程中的一些思路與代碼與大家共用，同時，也作為自己的一個總結，以備後用。

功能：

1、從資料庫查詢資料匯出為excel；

2、匯出word的包括，內容有文字，圖片，表格等。

效果：

匯出excel

匯出word

實現代碼：

1、匯出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  {      /**      * @功能：手工構建一個簡單格式的Excel      */      private static List<Student> getStudent() throws Exception      {          List<Student> list = new ArrayList<Student>();          SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd");            Student user1 = new Student(1, "張三", 16, df.parse("1997-03-12"));          Student user2 = new Student(2, "李四", 17, df.parse("1996-08-12"));          Student user3 = new Student(3, "王五", 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      {          // 第一步，建立一個webbook，對應一個Excel檔案          HSSFWorkbook wb = new HSSFWorkbook();          // 第二步，在webbook中添加一個sheet,對應Excel檔案中的sheet          HSSFSheet sheet = wb.createSheet("學生表一");          // 第三步，在sheet中添加表頭第0行,注意老版本poi對Excel的行數列數有限制short         HSSFCellStyle headStyle = wb.createCellStyle();         HSSFFont f  = wb.createFont();              f.setFontHeightInPoints((short) 11);// 字型大小           f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗           headStyle.setFont(f);         headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 建立一個置中格式          headStyle.setBorderBottom((short) 1);        headStyle.setBorderRight((short) 1);        headStyle.setFillBackgroundColor((short) 20);        HSSFRow row = sheet.createRow((int) 0);         // 第四步，建立儲存格，並設定值表頭 設定表頭置中          HSSFCellStyle style = wb.createCellStyle();          style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 建立一個置中格式          style.setBorderBottom((short) 1);        style.setBorderRight((short) 1);        String[] header = new String[]{"學號","姓名","年齡","生日"};          HSSFCell cell = null;        for(int i=0;i<header.length;i++){        cell = row.createCell((short) i);         cell.setCellValue(header[i]);              cell.setCellStyle(headStyle);             sheet.setColumnWidth(i, 5000);        }        // 第五步，寫入實體資料 實際應用中這些資料從資料庫得到，          List<Student> list = CreateSimpleExcelToDisk.getStudent();            for (int i = 0; i < list.size(); i++)          {              row = sheet.createRow((int) i + 1);              Student stu = (Student) list.get(i);              // 第四步，建立儲存格，並設定值              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);        }          // 第六步，將檔案存到指定位置          try          {              FileOutputStream fout = new FileOutputStream("D:/students.xls");              wb.write(fout);              fout.close();              System.out.println("輸出成功！");        }          catch (Exception e)          {              e.printStackTrace();          }      }  }

其中，student類的代碼如下：

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、匯出word報告

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;        //添加文本        String content ="    額爾古納河在1689年的《中俄尼布楚條約》中成為中國和俄羅斯的界河，額爾古納河上遊稱海拉爾河，源於大興安嶺西側，西流至阿該巴圖山腳， 折而北行始稱額爾古納河。額爾古納河在黑龍江省漠河縣以西的內蒙古自治區額爾古納右旗的恩和哈達附近與流經俄羅斯境內的石勒喀河匯合後始稱黑龍江。沿額爾古納河沿岸地區土地肥沃，森林茂密，水草豐美， 魚類品種很多，動植物資源豐富，宜農宜木，是人類理想的天堂。";        para = doc.createParagraph();   para.setAlignment(ParagraphAlignment.LEFT);//設定靠左對齊        run = para.createRun();        run.setFontFamily("仿宋");        run.setFontSize(13);        run.setText(content);        doc.createParagraph();        //添加圖片        String[] imgs = {"D:\\bar.png","D:\\pie.png"};        for(int i=0;i<imgs.length;i++){            para = doc.createParagraph();            para.setAlignment(ParagraphAlignment.CENTER);//設定靠左對齊            run = para.createRun();            InputStream input = new FileInputStream(imgs[i]);            run.addPicture(input, XWPFDocument.PICTURE_TYPE_JPEG,             imgs[i], Units.toEMU(350), Units.toEMU(170));            para = doc.createParagraph();        para.setAlignment(ParagraphAlignment.CENTER);//設定靠左對齊            run = para.createRun();            run.setFontFamily("仿宋");            run.setFontSize(11);            run.setText(imgs[i]);        }        doc.createParagraph();                //添加表格        XWPFTable table  = doc.createTable(2,3);        table.setCellMargins(3, 5, 3, 5);//        table.addNewCol();//添加新列//        table.createRow();//添加新行        String[] title = new String[]{"境內河流","境外河流","合計"};        String[] value = new String[]{"1","2","3"};        XWPFTableRow row;        XWPFTableCell cell;        CTTcPr cellPr;        for(int j=0;j<2;j++){        row = table.getRow(j);        row.setHeight(400);        for(int i=0;i<title.length;i++){                cell = row.getCell(i);        cellPr = cell.getCTTc().addNewTcPr();        cellPr.addNewTcW().setW(BigInteger.valueOf(3000));        para = cell.getParagraphs().get(0);        para.setAlignment(ParagraphAlignment.CENTER);        run = para.createRun();                      run.setFontFamily("仿宋");            run.setFontSize(11);        if(j==0){//標題        run.setBold(true);        run.setText(title[i]);        }        else{        run.setText(value[i]);        }        }          }        String path = "D:\\test.doc";        OutputStream os = new FileOutputStream(path);        doc.write(os);        if(os!=null){try{os.close();System.out.println("檔案已輸出！");}catch(IOException e){e.printStackTrace();}}    }}

3、word模板替換

word模板

替換後效果

代碼：

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 = "額爾古納河在1689年的《中俄尼布楚條約》中成為中國和俄羅斯的界河，額爾古納河上遊稱海拉爾河，源於大興安嶺西側，西流至阿該巴圖山腳， 折而北行始稱額爾古納河。額爾古納河在黑龍江省漠河縣以西的內蒙古自治區額爾古納右旗的恩和哈達附近與流經俄羅斯境內的石勒喀河匯合後始稱黑龍江。沿額爾古納河沿岸地區土地肥沃，森林茂密，水草豐美， 魚類品種很多，動植物資源豐富，宜農宜木，是人類理想的天堂。";        map.put("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");        //注意biyezheng_moban.doc文檔位置,此例中為應用根目錄        XWPFDocument doc=new ExportWordTest().replaceDoc("D:\\word_temp.docx", map);        try {        OutputStream os = new FileOutputStream("D:\\tttt.doc");        doc.write(os);            os.close();            System.out.println("輸出成功！");        }         catch (IOException e) {            e.printStackTrace();        }            }      /**     * 讀取word模板並替換變數     * @param srcPath     * @param map     * @return     */    public XWPFDocument replaceDoc(String srcPath, Map<String, Object> param) {        try {            // 讀取word模板            InputStream fis = new FileInputStream(srcPath);            XWPFDocument doc = new XWPFDocument(fis);            //處理段落              List<XWPFParagraph> paragraphList = doc.getParagraphs();              processParagraph(paragraphList,doc,param);            //處理表格            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.getParagraphs();                          processParagraph(paragraphListTable, doc, param);                      }                  }              }              return doc;        }         catch (Exception e) {            e.printStackTrace();            return null;        }    }        public void processParagraph(List<XWPFParagraph> paragraphList,    XWPFDocument doc,Map<String, Object> 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) {//文本替換                                      text = text.replace(key, value.toString());                                      System.out.println(text);                                }                                 else{                                    text = text.replace(key, "");                                  }                              }                          }                          if(isSetText){                              run.setText(text,0);                          }                      }        }        }       }    } }

POI相關jar包和API：http://pan.baidu.com/s/1eQ6fE8a

Java中用Apache POI產生excel和word文檔