java jxl mergecells_转：jxl导出excel（合并单元格）

1 import java.io.*;2 import jxl.*;3 importjxl.format.UnderlineStyle;4 import jxl.write.*;5 public classCreateXLS {6 public static voidmain(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 = newWritableCellFormat(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 = newWritableCellFormat(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 = newWritableCellFormat(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 = newWritableCellFormat(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 }156

157 import java.io.*;158 import jxl.*;159 importjxl.format.UnderlineStyle;160 import jxl.write.*;161 public classCreateXLS {162 public static voidmain(String args[]) {163 try{164 //打开文件

165 WritableWorkbook book= Workbook.createWorkbook(new File("测试.xls"));166 //生成名为“第一页”的工作表，参数0表示这是第一页

167 WritableSheet sheetOne=book.createSheet("第一页",0);168

169 /**

170 * 定义单元格样式171 */

172 WritableFont wf_title = new WritableFont(WritableFont.ARIAL, 11,173 WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,174 jxl.format.Colour.BLACK); //定义格式 字体 下划线 斜体 粗体 颜色

175 WritableFont wf_head = new WritableFont(WritableFont.ARIAL, 11,176 WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,177 jxl.format.Colour.BLACK); //定义格式 字体 下划线 斜体 粗体 颜色

178 WritableFont wf_table = new WritableFont(WritableFont.ARIAL, 11,179 WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,180 jxl.format.Colour.BLACK); //定义格式 字体 下划线 斜体 粗体 颜色

181

182 WritableCellFormat wcf_title = new WritableCellFormat(wf_title); //单元格定义

183 wcf_title.setBackground(jxl.format.Colour.WHITE); //设置单元格的背景颜色

184 wcf_title.setAlignment(jxl.format.Alignment.CENTRE); //设置对齐方式

185 wcf_title.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK); //设置边框

186

187 WritableCellFormat wcf_title1 = new WritableCellFormat(wf_title); //单元格定义

188 wcf_title1.setBackground(jxl.format.Colour.LIGHT_GREEN); //设置单元格的背景颜色

189 wcf_title1.setAlignment(jxl.format.Alignment.CENTRE); //设置对齐方式

190 wcf_title1.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK); //设置边框

191

192 WritableCellFormat wcf_title2 = new WritableCellFormat(wf_title); //单元格定义

193 wcf_title2.setBackground(jxl.format.Colour.YELLOW2); //设置单元格的背景颜色

194 wcf_title2.setAlignment(jxl.format.Alignment.CENTRE); //设置对齐方式

195 wcf_title2.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK); //设置边框

196

197 WritableCellFormat wcf_head1 = newWritableCellFormat(wf_head);198 wcf_head1.setBackground(jxl.format.Colour.LIGHT_GREEN);199 wcf_head1.setAlignment(jxl.format.Alignment.CENTRE);200 wcf_head1.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK);201

202 WritableCellFormat wcf_head2 = newWritableCellFormat(wf_head);203 wcf_head2.setBackground(jxl.format.Colour.YELLOW2);204 wcf_head2.setAlignment(jxl.format.Alignment.CENTRE);205 wcf_head2.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK);206

207

208 WritableCellFormat wcf_table1 = newWritableCellFormat(wf_table);209 wcf_table1.setBackground(jxl.format.Colour.LIGHT_GREEN);210 wcf_table1.setAlignment(jxl.format.Alignment.CENTRE);211 wcf_table1.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK);212

213 WritableCellFormat wcf_table2 = newWritableCellFormat(wf_table);214 wcf_table2.setBackground(jxl.format.Colour.YELLOW2);215 wcf_table2.setAlignment(jxl.format.Alignment.CENTRE);216 wcf_table2.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.BLACK);217

218 sheetOne.setColumnView(0, 15); //设置列的宽度

219 sheetOne.setColumnView(1, 15); //设置列的宽度

220 sheetOne.setColumnView(2, 15); //设置列的宽度

221 sheetOne.setColumnView(3, 15); //设置列的宽度

222 sheetOne.setColumnView(4, 15); //设置列的宽度

223 sheetOne.setColumnView(5, 15); //设置列的宽度

224 sheetOne.setColumnView(6, 15); //设置列的宽度

225 sheetOne.setColumnView(7, 15); //设置列的宽度

226 sheetOne.setColumnView(8, 15); //设置列的宽度

227 sheetOne.setColumnView(9, 15); //设置列的宽度

228 sheetOne.setColumnView(10, 15); //设置列的宽度

229 sheetOne.setColumnView(11, 15); //设置列的宽度

230 sheetOne.setColumnView(12, 15); //设置列的宽度

231 sheetOne.setColumnView(13, 15); //设置列的宽度232

233 //在Label对象的构造子中指名单元格位置是第一列第一行(0,0)234 //以及单元格内容为test

235 Label title=new Label(0,0,"统计",wcf_title);236 Label titleOne=new Label(0,1,"统计1",wcf_title1);237 Label titleTwo=new Label(2,1,"统计2",wcf_title2);238

239 Label column1=new Label(0,2,"姓名",wcf_head1);240 Label column2=new Label(1,2,"所选课程",wcf_head1);241

242 Label column3=new Label(2,2,"姓名",wcf_head2);243 Label column4=new Label(3,2,"所选课程",wcf_head2);244

245

246

247 //或者WritableCell cell = new jxl.write.Number(column, row, value, wcf)248 //将定义好的单元格添加到工作表中

249 sheetOne.addCell(title);250 sheetOne.addCell(titleOne);251 sheetOne.addCell(titleTwo);252

253 sheetOne.addCell(column1);254 sheetOne.addCell(column2);255 sheetOne.addCell(column3);256 sheetOne.addCell(column4);257

258

259 //合： 第1列第1行 到 第13列第1行

260 sheetOne.mergeCells(0, 0, 3, 0);261 sheetOne.mergeCells(0, 1, 1, 1);262 sheetOne.mergeCells(2, 1, 3, 1);263

264

265

266

267

268

269 /*动态数据*/

270 Label content1=new Label(0,3,"张三",wcf_table1);271 Label content2=new Label(0,4,"张三",wcf_table1);272 Label content3=new Label(0,5,"张三",wcf_table1);273 Label kecheg1=new Label(1,3,"语文",wcf_table1);274 Label kecheg2=new Label(1,4,"数学",wcf_table1);275 Label kecheg3=new Label(1,5,"英语",wcf_table1);276

277 sheetOne.addCell(content1);278 sheetOne.addCell(content2);279 sheetOne.addCell(content3);280 sheetOne.addCell(kecheg1);281 sheetOne.addCell(kecheg2);282 sheetOne.addCell(kecheg3);283

284 sheetOne.mergeCells(0, 3, 0, 2+3);285

286

287 Label content11=new Label(2,3,"李四",wcf_table2);288 Label content22=new Label(2,4,"李四",wcf_table2);289 Label content33=new Label(2,5,"李四",wcf_table2);290 Label kecheg11=new Label(3,3,"语文",wcf_table2);291 Label kecheg22=new Label(3,4,"数学",wcf_table2);292 Label kecheg33=new Label(3,5,"英语",wcf_table2);293

294 sheetOne.addCell(content11);295 sheetOne.addCell(content22);296 sheetOne.addCell(content33);297 sheetOne.addCell(kecheg11);298 sheetOne.addCell(kecheg22);299 sheetOne.addCell(kecheg33);300

301 sheetOne.mergeCells(2, 3, 2, 2+3);302

303

304 //写入数据并关闭文件

305 book.write();306 book.close();307 }catch(Exception e){308 System.out.println(e);309 }310 }311 }312