jxl導出excel（合并單元格）

Demo

[java]  view plain copy

1. import java.io.*;   
2. import jxl.*;   
3. import jxl.format.UnderlineStyle;  
4. import jxl.write.*;   
5. public class CreateXLS {   
6.     public static void main(String args[]) {   
7.         try {   
8.             //打開檔案  
9.             WritableWorkbook book= Workbook.createWorkbook(new File("測試.xls"));   
10.             //生成名為“第一頁”的工作表，參數0表示這是第一頁   
11.             WritableSheet sheetOne=book.createSheet("第一頁",0);   
12.             WritableFont wf_title = new WritableFont(WritableFont.ARIAL, 11,  
13.                     WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,  
14.                     jxl.format.Colour.BLACK); // 定義格式 字型 下劃線 斜體 粗體 顔色  
15.             WritableFont wf_head = new WritableFont(WritableFont.ARIAL, 11,  
16.                     WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,  
17.                     jxl.format.Colour.BLACK); // 定義格式 字型 下劃線 斜體 粗體 顔色  
18.             WritableFont wf_table = new WritableFont(WritableFont.ARIAL, 11,  
19.                     WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,  
20.                     jxl.format.Colour.BLACK); // 定義格式 字型 下劃線 斜體 粗體 顔色  
21.             WritableCellFormat wcf_title = new WritableCellFormat(wf_title); // 單元格定義  
22.             wcf_title.setBackground(jxl.format.Colour.WHITE); // 設定單元格的背景顔色  
23.             wcf_title.setAlignment(jxl.format.Alignment.CENTRE); // 設定對齊方式  
24.             wcf_title.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK); //設定邊框  
25.             WritableCellFormat wcf_title1 = new WritableCellFormat(wf_title); // 單元格定義  
26.             wcf_title1.setBackground(jxl.format.Colour.LIGHT_GREEN); // 設定單元格的背景顔色  
27.             wcf_title1.setAlignment(jxl.format.Alignment.CENTRE); // 設定對齊方式  
28.             wcf_title1.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK); //設定邊框  
29.             WritableCellFormat wcf_title2 = new WritableCellFormat(wf_title); // 單元格定義  
30.             wcf_title2.setBackground(jxl.format.Colour.YELLOW2); // 設定單元格的背景顔色  
31.             wcf_title2.setAlignment(jxl.format.Alignment.CENTRE); // 設定對齊方式  
32.             wcf_title2.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK); //設定邊框  
33.             WritableCellFormat wcf_head1 = new WritableCellFormat(wf_head);   
34.             wcf_head1.setBackground(jxl.format.Colour.LIGHT_GREEN);  
35.             wcf_head1.setAlignment(jxl.format.Alignment.CENTRE);   
36.             wcf_head1.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK);   
37.             WritableCellFormat wcf_head2 = new WritableCellFormat(wf_head);   
38.             wcf_head2.setBackground(jxl.format.Colour.YELLOW2);  
39.             wcf_head2.setAlignment(jxl.format.Alignment.CENTRE);   
40.             wcf_head2.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK);   
41.             WritableCellFormat wcf_table1 = new WritableCellFormat(wf_table);   
42.             wcf_table1.setBackground(jxl.format.Colour.LIGHT_GREEN);   
43.             wcf_table1.setAlignment(jxl.format.Alignment.CENTRE);   
44.             wcf_table1.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK);   
45.             WritableCellFormat wcf_table2 = new WritableCellFormat(wf_table);   
46.             wcf_table2.setBackground(jxl.format.Colour.YELLOW2);   
47.             wcf_table2.setAlignment(jxl.format.Alignment.CENTRE);   
48.             wcf_table2.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK);   
49.             sheetOne.setColumnView(0, 15); // 設定列的寬度  
50.             sheetOne.setColumnView(1, 15); // 設定列的寬度  
51.             sheetOne.setColumnView(2, 15); // 設定列的寬度  
52.             sheetOne.setColumnView(3, 15); // 設定列的寬度  
53.             sheetOne.setColumnView(4, 15); // 設定列的寬度  
54.             sheetOne.setColumnView(5, 15); // 設定列的寬度  
55.             sheetOne.setColumnView(6, 15); // 設定列的寬度  
56.             sheetOne.setColumnView(7, 15); // 設定列的寬度  
57.             sheetOne.setColumnView(8, 15); // 設定列的寬度  
58.             sheetOne.setColumnView(9, 15); // 設定列的寬度  
59.             sheetOne.setColumnView(10, 15); // 設定列的寬度  
60.             sheetOne.setColumnView(11, 15); // 設定列的寬度  
61.             sheetOne.setColumnView(12, 15); // 設定列的寬度  
62.             sheetOne.setColumnView(13, 15); // 設定列的寬度  
63.             //在Label對象的構造子中指名單元格位置是第一列第一行(0,0)   
64.             //以及單元格内容為test   
65.             Label title=new Label(0,0,"統計",wcf_title);  
66.             Label titleOne=new Label(0,1,"統計1",wcf_title1);  
67.             Label titleTwo=new Label(2,1,"統計2",wcf_title2);  
68.             Label column1=new Label(0,2,"姓名",wcf_head1);  
69.             Label column2=new Label(1,2,"所選課程",wcf_head1);  
70.             Label column3=new Label(2,2,"姓名",wcf_head2);  
71.             Label column4=new Label(3,2,"所選課程",wcf_head2);  
72.             //或者WritableCell cell =  new jxl.write.Number(column, row, value, wcf)  
73.             //将定義好的單元格添加到工作表中   
74.             sheetOne.addCell(title);   
75.             sheetOne.addCell(titleOne);   
76.             sheetOne.addCell(titleTwo);   
77.             sheetOne.addCell(column1);   
78.             sheetOne.addCell(column2);   
79.             sheetOne.addCell(column3);   
80.             sheetOne.addCell(column4);   
81.             //合： 第1列第1行  到 第13列第1行  
82.             sheetOne.mergeCells(0, 0, 3, 0);   
83.             sheetOne.mergeCells(0, 1, 1, 1);   
84.             sheetOne.mergeCells(2, 1, 3, 1);   
85.             Label content1=new Label(0,3,"張三",wcf_table1);  
86.             Label content2=new Label(0,4,"張三",wcf_table1);  
87.             Label content3=new Label(0,5,"張三",wcf_table1);  
88.             Label kecheg1=new Label(1,3,"國文",wcf_table1);  
89.             Label kecheg2=new Label(1,4,"數學",wcf_table1);  
90.             Label kecheg3=new Label(1,5,"英語",wcf_table1);  
91.             sheetOne.addCell(content1);  
92.             sheetOne.addCell(content2);  
93.             sheetOne.addCell(content3);  
94.             sheetOne.addCell(kecheg1);  
95.             sheetOne.addCell(kecheg2);  
96.             sheetOne.addCell(kecheg3);  
97.             sheetOne.mergeCells(0, 3, 0, 2+3);   
98.             Label content11=new Label(2,3,"李四",wcf_table2);  
99.             Label content22=new Label(2,4,"李四",wcf_table2);  
100.             Label content33=new Label(2,5,"李四",wcf_table2);  
101.             Label kecheg11=new Label(3,3,"國文",wcf_table2);  
102.             Label kecheg22=new Label(3,4,"數學",wcf_table2);  
103.             Label kecheg33=new Label(3,5,"英語",wcf_table2);  
104.             sheetOne.addCell(content11);  
105.             sheetOne.addCell(content22);  
106.             sheetOne.addCell(content33);  
107.             sheetOne.addCell(kecheg11);  
108.             sheetOne.addCell(kecheg22);  
109.             sheetOne.addCell(kecheg33);  
110.             sheetOne.mergeCells(2, 3, 2, 2+3);   
111.             //寫入資料并關閉檔案   
112.             book.write();   
113.             book.close();   
114.         }catch(Exception e){   
115.             System.out.println(e);   
116.         }   
117.     }   
118. }   

其他代碼參考

[java]  view plain copy

1. public void createZJData(List<CountVO> zjList,WritableSheet sheet) throws RowsExceededException, WriteException {  
2.     //（為合并做準備）在運階段有多少個子系統  
3.     for (int j = 0; j < zjList.size(); j++) {  
4.         if(zjSonSysList.get(zjList.get(j).getNumber3()) != null){  
5.             zjSonSysList.get(zjList.get(j).getNumber3()).add(zjList.get(j));  
6.         }else{  
7.             List<CountVO> vos = new ArrayList<CountVO>();  
8.             vos.add(zjList.get(j));  
9.             zjSonSysList.put(zjList.get(j).getNumber3(), vos);  
10.         }  
11.     }  
12.     //動态生成資料  
13.     if(zjSonSysList != null && zjSonSysList.size() > 0){  
14.         for (Iterator iter = zjSonSysList.entrySet().iterator(); iter.hasNext();){  
15.             Entry entry = (Entry) iter.next();    
16.             String key = (String) entry.getKey();  
17.             List<CountVO> sonSysVO = (List<CountVO>) entry.getValue();  
18.             for (int t = 0; t < sonSysVO.size(); t++) {  
19.                 Label content1=new Label(7,zjEndNum+t,sonSysVO.get(t).getNumber1(),wcf_table2);  
20.                 Label content2=new Label(8,zjEndNum+t,sonSysVO.get(t).getNumber2(),wcf_table2);  
21.                 Label content3=new Label(9,zjEndNum+t,sonSysVO.get(t).getNumber3(),wcf_table2);  
22.                 Label content4=new Label(10,zjEndNum+t,sonSysVO.get(t).getNumber4(),wcf_table2);  
23.                 Label content5=new Label(11,zjEndNum+t,sonSysVO.get(t).getNumber5(),wcf_table2);  
24.                 Label content6=new Label(12,zjEndNum+t,sonSysVO.get(t).getNumber6(),wcf_table2);  
25.                 Label mk=new Label(13,zjEndNum+t,sonSysVO.get(t).getNumber7(),wcf_table2);  
26.                 sheet.addCell(content1);  
27.                 sheet.addCell(content2);  
28.                 sheet.addCell(content3);  
29.                 sheet.addCell(content4);  
30.                 sheet.addCell(content5);  
31.                 sheet.addCell(content6);  
32.                 sheet.addCell(mk);  
33.                 if(t == (sonSysVO.size() - 1)){  
34.                     zjEndNum = zjEndNum+t+1;  
35.                 }else{  
36.                     zjEndNum = zjEndNum+t;    
37.                 }  
38.             }  
39.             int offer = sonSysVO.size();  
40.             //合并： 第1列第1行  到 第13列第1行  
41.             sheet.mergeCells(7, zjEndNum-offer, 7, zjEndNum-1);   
42.             sheet.mergeCells(8, zjEndNum-offer, 8, zjEndNum-1);   
43.             sheet.mergeCells(9, zjEndNum-offer, 9, zjEndNum-1);   
44.             sheet.mergeCells(10, zjEndNum-offer, 10, zjEndNum-1);   
45.             sheet.mergeCells(11, zjEndNum-offer, 11, zjEndNum-1);   
46.             sheet.mergeCells(12, zjEndNum-offer, 12, zjEndNum-1);  
47.         }  
48.     }  
49.     //使用完畢後恢複，供下一個類型sheet使用  
50.     zjEndNum = 3;  
51.     zjSonSysList.clear();  
52.     zjList.clear();  
53. }  

自定義背景色方法getNearestColour("#99cc00")

[java]  view plain copy

1. public static Colour getNearestColour(String strColor) {    
2.        Color cl = Color.decode(strColor);    
3.        Colour color = null;    
4.        Colour[] colors = Colour.getAllColours();    
5.        if ((colors != null) && (colors.length > 0)) {    
6.           Colour crtColor = null;    
7.           int[] rgb = null;    
8.           int diff = 0;    
9.           int minDiff = 999;    
10.           for (int i = 0; i < colors.length; i++) {    
11.                crtColor = colors[i];    
12.                rgb = new int[3];    
13.                rgb[0] = crtColor.getDefaultRGB().getRed();    
14.                rgb[1] = crtColor.getDefaultRGB().getGreen();    
15.                rgb[2] = crtColor.getDefaultRGB().getBlue();    
16.                diff = Math.abs(rgb[0] - cl.getRed())    
17.                  + Math.abs(rgb[1] - cl.getGreen())    
18.                  + Math.abs(rgb[2] - cl.getBlue());    
19.                if (diff < minDiff) {    
20.                 minDiff = diff;    
21.                 color = crtColor;    
22.                }    
23.           }    
24.        }    
25.        if (color == null)    
26.           color = Colour.BLACK;    
27.        return color;    
28.    }