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.               
13.             /** 
14.              * 定义单元格样式 
15.              */  
16.             WritableFont wf_title = new WritableFont(WritableFont.ARIAL, 11,  
17.                     WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,  
18.                     jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色  
19.             WritableFont wf_head = new WritableFont(WritableFont.ARIAL, 11,  
20.                     WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,  
21.                     jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色  
22.             WritableFont wf_table = new WritableFont(WritableFont.ARIAL, 11,  
23.                     WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,  
24.                     jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色  
25.   
26.             WritableCellFormat wcf_title = new WritableCellFormat(wf_title); // 单元格定义  
27.             wcf_title.setBackground(jxl.format.Colour.WHITE); // 设置单元格的背景颜色  
28.             wcf_title.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式  
29.             wcf_title.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK); //设置边框  
30.               
31.             WritableCellFormat wcf_title1 = new WritableCellFormat(wf_title); // 单元格定义  
32.             wcf_title1.setBackground(jxl.format.Colour.LIGHT_GREEN); // 设置单元格的背景颜色  
33.             wcf_title1.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式  
34.             wcf_title1.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK); //设置边框  
35.               
36.             WritableCellFormat wcf_title2 = new WritableCellFormat(wf_title); // 单元格定义  
37.             wcf_title2.setBackground(jxl.format.Colour.YELLOW2); // 设置单元格的背景颜色  
38.             wcf_title2.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式  
39.             wcf_title2.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK); //设置边框  
40.   
41.             WritableCellFormat wcf_head1 = new WritableCellFormat(wf_head);   
42.             wcf_head1.setBackground(jxl.format.Colour.LIGHT_GREEN);  
43.             wcf_head1.setAlignment(jxl.format.Alignment.CENTRE);   
44.             wcf_head1.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK);   
45.               
46.             WritableCellFormat wcf_head2 = new WritableCellFormat(wf_head);   
47.             wcf_head2.setBackground(jxl.format.Colour.YELLOW2);  
48.             wcf_head2.setAlignment(jxl.format.Alignment.CENTRE);   
49.             wcf_head2.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK);   
50.   
51.   
52.             WritableCellFormat wcf_table1 = new WritableCellFormat(wf_table);   
53.             wcf_table1.setBackground(jxl.format.Colour.LIGHT_GREEN);   
54.             wcf_table1.setAlignment(jxl.format.Alignment.CENTRE);   
55.             wcf_table1.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK);   
56.               
57.             WritableCellFormat wcf_table2 = new WritableCellFormat(wf_table);   
58.             wcf_table2.setBackground(jxl.format.Colour.YELLOW2);   
59.             wcf_table2.setAlignment(jxl.format.Alignment.CENTRE);   
60.             wcf_table2.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK);   
61.               
62.             sheetOne.setColumnView(0, 15); // 设置列的宽度  
63.             sheetOne.setColumnView(1, 15); // 设置列的宽度  
64.             sheetOne.setColumnView(2, 15); // 设置列的宽度  
65.             sheetOne.setColumnView(3, 15); // 设置列的宽度  
66.             sheetOne.setColumnView(4, 15); // 设置列的宽度  
67.             sheetOne.setColumnView(5, 15); // 设置列的宽度  
68.             sheetOne.setColumnView(6, 15); // 设置列的宽度  
69.             sheetOne.setColumnView(7, 15); // 设置列的宽度  
70.             sheetOne.setColumnView(8, 15); // 设置列的宽度  
71.             sheetOne.setColumnView(9, 15); // 设置列的宽度  
72.             sheetOne.setColumnView(10, 15); // 设置列的宽度  
73.             sheetOne.setColumnView(11, 15); // 设置列的宽度  
74.             sheetOne.setColumnView(12, 15); // 设置列的宽度  
75.             sheetOne.setColumnView(13, 15); // 设置列的宽度  
76.   
77.             //在Label对象的构造子中指名单元格位置是第一列第一行(0,0)   
78.             //以及单元格内容为test   
79.             Label title=new Label(0,0,"统计",wcf_title);  
80.             Label titleOne=new Label(0,1,"统计1",wcf_title1);  
81.             Label titleTwo=new Label(2,1,"统计2",wcf_title2);  
82.   
83.             Label column1=new Label(0,2,"姓名",wcf_head1);  
84.             Label column2=new Label(1,2,"所选课程",wcf_head1);  
85.               
86.             Label column3=new Label(2,2,"姓名",wcf_head2);  
87.             Label column4=new Label(3,2,"所选课程",wcf_head2);  
88.   
89.   
90.   
91.             //或者WritableCell cell =  new jxl.write.Number(column, row, value, wcf)  
92.             //将定义好的单元格添加到工作表中   
93.             sheetOne.addCell(title);   
94.             sheetOne.addCell(titleOne);   
95.             sheetOne.addCell(titleTwo);   
96.   
97.             sheetOne.addCell(column1);   
98.             sheetOne.addCell(column2);   
99.             sheetOne.addCell(column3);   
100.             sheetOne.addCell(column4);   
101.   
102.   
103.             //合： 第1列第1行  到 第13列第1行  
104.             sheetOne.mergeCells(0, 0, 3, 0);   
105.             sheetOne.mergeCells(0, 1, 1, 1);   
106.             sheetOne.mergeCells(2, 1, 3, 1);   
107.               
108.               
109.               
110.               
111.               
112.               
113.             /*动态数据   */  
114.             Label content1=new Label(0,3,"张三",wcf_table1);  
115.             Label content2=new Label(0,4,"张三",wcf_table1);  
116.             Label content3=new Label(0,5,"张三",wcf_table1);  
117.             Label kecheg1=new Label(1,3,"语文",wcf_table1);  
118.             Label kecheg2=new Label(1,4,"数学",wcf_table1);  
119.             Label kecheg3=new Label(1,5,"英语",wcf_table1);  
120.               
121.             sheetOne.addCell(content1);  
122.             sheetOne.addCell(content2);  
123.             sheetOne.addCell(content3);  
124.             sheetOne.addCell(kecheg1);  
125.             sheetOne.addCell(kecheg2);  
126.             sheetOne.addCell(kecheg3);  
127.               
128.             sheetOne.mergeCells(0, 3, 0, 2+3);   
129.               
130.               
131.             Label content11=new Label(2,3,"李四",wcf_table2);  
132.             Label content22=new Label(2,4,"李四",wcf_table2);  
133.             Label content33=new Label(2,5,"李四",wcf_table2);  
134.             Label kecheg11=new Label(3,3,"语文",wcf_table2);  
135.             Label kecheg22=new Label(3,4,"数学",wcf_table2);  
136.             Label kecheg33=new Label(3,5,"英语",wcf_table2);  
137.               
138.             sheetOne.addCell(content11);  
139.             sheetOne.addCell(content22);  
140.             sheetOne.addCell(content33);  
141.             sheetOne.addCell(kecheg11);  
142.             sheetOne.addCell(kecheg22);  
143.             sheetOne.addCell(kecheg33);  
144.               
145.             sheetOne.mergeCells(2, 3, 2, 2+3);   
146.   
147.   
148.             //写入数据并关闭文件   
149.             book.write();   
150.             book.close();   
151.         }catch(Exception e){   
152.             System.out.println(e);   
153.         }   
154.     }   
155. }   

 

 

其他代码参考

[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.     //动态生成数据  
14.     if(zjSonSysList != null && zjSonSysList.size() > 0){  
15.         for (Iterator iter = zjSonSysList.entrySet().iterator(); iter.hasNext();){  
16.             Entry entry = (Entry) iter.next();    
17.             String key = (String) entry.getKey();  
18.             List<CountVO> sonSysVO = (List<CountVO>) entry.getValue();  
19.   
20.             for (int t = 0; t < sonSysVO.size(); t++) {  
21.   
22.                 Label content1=new Label(7,zjEndNum+t,sonSysVO.get(t).getNumber1(),wcf_table2);  
23.                 Label content2=new Label(8,zjEndNum+t,sonSysVO.get(t).getNumber2(),wcf_table2);  
24.                 Label content3=new Label(9,zjEndNum+t,sonSysVO.get(t).getNumber3(),wcf_table2);  
25.                 Label content4=new Label(10,zjEndNum+t,sonSysVO.get(t).getNumber4(),wcf_table2);  
26.                 Label content5=new Label(11,zjEndNum+t,sonSysVO.get(t).getNumber5(),wcf_table2);  
27.                 Label content6=new Label(12,zjEndNum+t,sonSysVO.get(t).getNumber6(),wcf_table2);  
28.                 Label mk=new Label(13,zjEndNum+t,sonSysVO.get(t).getNumber7(),wcf_table2);  
29.   
30.                 sheet.addCell(content1);  
31.                 sheet.addCell(content2);  
32.                 sheet.addCell(content3);  
33.                 sheet.addCell(content4);  
34.                 sheet.addCell(content5);  
35.                 sheet.addCell(content6);  
36.                 sheet.addCell(mk);  
37.   
38.                 if(t == (sonSysVO.size() - 1)){  
39.                     zjEndNum = zjEndNum+t+1;  
40.                 }else{  
41.                     zjEndNum = zjEndNum+t;    
42.                 }  
43.   
44.             }  
45.             int offer = sonSysVO.size();  
46.             //合并： 第1列第1行  到 第13列第1行  
47.             sheet.mergeCells(7, zjEndNum-offer, 7, zjEndNum-1);   
48.             sheet.mergeCells(8, zjEndNum-offer, 8, zjEndNum-1);   
49.             sheet.mergeCells(9, zjEndNum-offer, 9, zjEndNum-1);   
50.             sheet.mergeCells(10, zjEndNum-offer, 10, zjEndNum-1);   
51.             sheet.mergeCells(11, zjEndNum-offer, 11, zjEndNum-1);   
52.             sheet.mergeCells(12, zjEndNum-offer, 12, zjEndNum-1);  
53.         }  
54.     }  
55.   
56.     //使用完毕后恢复，供下一个类型sheet使用  
57.     zjEndNum = 3;  
58.     zjSonSysList.clear();  
59.     zjList.clear();  
60. }  

自定义背景色方法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.        
17.                diff = Math.abs(rgb[0] - cl.getRed())    
18.                  + Math.abs(rgb[1] - cl.getGreen())    
19.                  + Math.abs(rgb[2] - cl.getBlue());    
20.                if (diff < minDiff) {    
21.                 minDiff = diff;    
22.                 color = crtColor;    
23.                }    
24.           }    
25.        }    
26.        if (color == null)    
27.           color = Colour.BLACK;    
28.        return color;    
29.    }