jxl对Excel的操作，包含合并单元格

迷雾雪兰

浏览: 17103 次
性别:
来自: 武汉

最近访客更多访客>>

dreamtan

gs88pansh

niepeng880208

43423423xuemd

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

java

excel java wcf

01.import java.io.*;
02.import jxl.*;
03.import jxl.format.UnderlineStyle;
04.import jxl.write.*;
05.public class CreateXLS {
06.    public static void main(String args[]) {
07.        try {
08.            //打开文件
09.            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.}