java實現excel模板導出

一. 準備工作

　　1. 點擊此下載相關開發工具

　　2. 將poi-3.8、jxls-core-1.0兩個jar包放到工程中，並引用

　　3. 將excel模板runRecord.xls放到RunRecordBSImpl.java類路徑下

二. RunRecordBSImpl.java類

 1 import java.io.BufferedInputStream;
 2 import java.io.FileInputStream;
 3 import java.io.FileOutputStream;
 4 import java.io.InputStream;
 5 import java.io.OutputStream;
 6 import java.util.ArrayList;
 7 import java.util.HashMap;
 8 import java.util.List;
 9 import java.util.Map;
10 
11 import org.apache.poi.hssf.usermodel.HSSFSheet;
12 import org.apache.poi.ss.usermodel.Workbook;
13 import org.apache.poi.ss.util.CellRangeAddress;
14 
15 import net.sf.jxls.transformer.XLSTransformer;
16 
17 public class RunRecordBSImpl {
18     public static void main(String[] args){
19         RunRecordBSImpl test = new RunRecordBSImpl();
20         test.export();
21     }
22     
23     public void export(){
24         String templateFileName = "runRecord.xls";  
25         String templateFilePath =  this.getClass().getResource("").getPath();
26         String destFileName= "設備運行記錄卡.xls";
27         
28         Map<String, Object> beans = new HashMap<String, Object>();
29         
30         // 設備信息
31         Map<String, Object> product = new HashMap<String, Object>();
32         product.put("model", "XG2016");
33         product.put("version", "V1.0");        
34         beans.put("product", product);
35         
36         // 年、月、日
37         beans.put("year", "2016");
38         beans.put("month", "08");
39         beans.put("day", "08");
40         
41         // 運行記錄
42         Map<String, Object> record = new HashMap<String, Object>();
43         record.put("inputVoltage", "200");
44         record.put("inputVoltageStatus", 0);
45         record.put("inputCurrent", "50");
46         record.put("inputCurrentStatus", 0);
47         beans.put("record", record);
48         
49         // 異常處理
50         List<Map<String, Object>> exceptions = new ArrayList<Map<String, Object>>();
51         Map<String, Object> e1 = new HashMap<String, Object>();
52         e1.put("detail", "輸入電壓過大");
53         e1.put("handle", "降低輸入電壓");
54         e1.put("handleMode", 1);
55         exceptions.add(e1);
56         
57         Map<String, Object> e2 = new HashMap<String, Object>();
58         e2.put("detail", "輸入電流過大");
59         e2.put("handle", "降低輸入電流");
60         e2.put("handleMode", 1);
61         exceptions.add(e2);
62         
63         beans.put("exceptions", exceptions);
64         
65         InputStream in = null;
66         OutputStream out = null;
67         
68         try {
69             XLSTransformer transformer = new XLSTransformer();
70             in = new BufferedInputStream(new FileInputStream(templateFilePath+templateFileName));  
71             Workbook workbook=transformer.transformXLS(in, beans);
72             HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(0);
73             
74             int startMergeLine = 7; // 開始合併的行數
75             // 合併異常處理清單單元格
76             if(exceptions.size()>0){
77                 sheet.addMergedRegion(new CellRangeAddress(startMergeLine, startMergeLine+exceptions.size()-1, 0, 0));  
78             }
79                              
80             //將內容寫入輸出流並把緩存的內容全部發出去  
81             out = new FileOutputStream(templateFilePath+destFileName);
82             workbook.write(out);  
83             out.flush();
84         } catch (Exception e) {
85             e.printStackTrace();
86         } finally{
87             try{
88                 if(out!=null)
89                     out.close();
90                 if(in!=null)
91                     in.close();
92             }catch(Exception e){
93                 e.printStackTrace();
94             }
95         }
96     }
97 }

三. 運行測試

　　以java應用運行RunRecordBSImpl.java類，則可在該類路徑下，按excel模板生成"設備運行記錄卡.xls"文件。