jxl向excel中添加链接、公式以及浮点数以百分比显示

Java代码  

1. package com.soft.export;   
2.   
3. import java.io.File;   
4. import jxl.CellType;   
5. import jxl.Workbook;   
6. import jxl.biff.DisplayFormat;   
7. import jxl.format.Alignment;   
8. import jxl.format.Border;   
9. import jxl.format.BorderLineStyle;   
10. import jxl.format.Colour;   
11. import jxl.format.VerticalAlignment;   
12. import jxl.write.Formula;   
13. import jxl.write.Number;   
14. import jxl.write.NumberFormat;   
15. import jxl.write.NumberFormats;   
16. import jxl.write.WritableCellFormat;   
17. import jxl.write.WritableFont;   
18. import jxl.write.WritableHyperlink;   
19. import jxl.write.WritableSheet;   
20. import jxl.write.WritableWorkbook;   
21. import jxl.write.WriteException;   
22. import jxl.write.biff.RowsExceededException;   
23.   
24. import org.junit.Test;   
25.   
26. public class GSTest {   
27.     @Test  
28.     public void testGS(){   
29.         WritableWorkbook workbook = null;   
30.         try{   
31.             workbook = Workbook.createWorkbook(new File("C:/Desktop/link.xls"));   
32.             WritableSheet sheet1 = workbook.createSheet("sheet1", 0);   
33.             WritableSheet sheet2 = workbook.createSheet("sheet2", 1);   
34.   
35.             jxl.write.Number labelNF1 = new jxl.write.Number(0, 2,0.5);   
36.             jxl.write.Number labelNF2 = new jxl.write.Number(0, 3,45678);   
37.             sheet1.addCell(labelNF1);   
38.             sheet1.addCell(labelNF2);   
39.             //将建公式   
40.             Formula f =    
41.                 new Formula(5, 5, "SUM(A3:A4)", getDataCellFormat(CellType.NUMBER_FORMULA));     
42.             sheet1.addCell(f);                
43.                
44.             WritableFont wf = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false);   
45.             //数字显示格式为浮点数百分比   
46.             DisplayFormat displayFormat = NumberFormats.PERCENT_FLOAT;            
47.             WritableCellFormat wcfF = new WritableCellFormat(wf,displayFormat);   
48.             wcfF.setAlignment(Alignment.CENTRE);   
49.             wcfF.setVerticalAlignment(VerticalAlignment.CENTRE);   
50.             wcfF.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);         
51.             Number number = new jxl.write.Number(5, 0, Double.parseDouble("1"), wcfF);   
52.             sheet1.addCell(number);   
53.                
54.             //添加超链接   
55.             addHyperlink(5,5,sheet1,sheet2,"link test");   
56.                
57.             workbook.write();   
58.             workbook.close();   
59.         }   
60.         catch(Exception e){   
61.             e.printStackTrace();      
62.         }   
63.     }   
64.        
65.     /**  
66.      *   
67.      * @param col  链接所在列  
68.      * @param row  链接所在行  
69.      * @param sheet要将链接添加到哪一个工作表  
70.      * @param destSheet 要连接到哪个工作表  
71.      * @param linkName  链接名称  
72.      */  
73.     public static void addHyperlink(int col,int row,WritableSheet sheet,WritableSheet destSheet,String linkName){   
74.         try {   
75.             WritableHyperlink whl = new WritableHyperlink(col,row,linkName,destSheet,0,0);   
76.             sheet.addHyperlink(whl);   
77.         } catch (RowsExceededException e) {      
78.             e.printStackTrace();   
79.         } catch (WriteException e) {   
80.             e.printStackTrace();   
81.         }   
82.     }   
83.        
84.     /**    
85.      * 得到数据格式    
86.      * @return    
87.      */     
88.     public WritableCellFormat getDataCellFormat(CellType type){      
89.         WritableCellFormat wcf = null;      
90.         try {   
91.             //字体样式      
92.             if(type == CellType.NUMBER || type == CellType.NUMBER_FORMULA){//数字      
93.                NumberFormat nf = new NumberFormat("#.00");      
94.                wcf = new WritableCellFormat(nf);       
95.             }else if(type == CellType.DATE || type == CellType.DATE_FORMULA){//日期      
96.                 jxl.write.DateFormat df = new jxl.write.DateFormat("yyyy-MM-dd hh:mm:ss");       
97.                 wcf = new jxl.write.WritableCellFormat(df);       
98.             }else{      
99.                 WritableFont wf =    
100.                     new WritableFont(WritableFont.TIMES,10, WritableFont.NO_BOLD,false);   
101.                 wcf = new WritableCellFormat(wf);      
102.             }   
103.             //对齐方式      
104.             wcf.setAlignment(Alignment.CENTRE);      
105.             wcf.setVerticalAlignment(VerticalAlignment.CENTRE);      
106.             //边框      
107.             wcf.setBorder(Border.LEFT,BorderLineStyle.THIN);      
108.             wcf.setBorder(Border.BOTTOM,BorderLineStyle.THIN);      
109.             wcf.setBorder(Border.RIGHT,BorderLineStyle.THIN);      
110.             //背景色      
111.             wcf.setBackground(Colour.WHITE);      
112.                   
113.             wcf.setWrap(true);//自动换行      
114.                   
115.         } catch (WriteException e) {      
116.          e.printStackTrace();      
117.         }      
118.         return wcf;      
119.     }   
120.        
121. }