java设置excel标题栏_java使⽤poi⾃定义excel标题头并导出
（spring。。。
项⽬使⽤的是jeecg开源(springmvc+spring+hibernate+。。。。。。等)此代码仅供参考！如有更好的意见或建议可留⾔。
1 controller 层2
3 /**
4 * excel⾃定义导出
5 *@paramhAqscTieupsummary
6 *@paramrequest
7 *@paramrespon
8 *@paramdataGrid9英语四级及格分数
*@parammodelMap10 *@return
abitcool11 */
12 @SuppressWarnings("deprecation")13 @RequestMapping(params = "exportEXL")14 publicString
exportEXL(HAqscTieupsummaryEntity hAqscTieupsummary,15 HttpServletRequest request, HttpServletRespon respon,16 DataGrid dataGrid, ModelMap modelMap) {17 try{18 String dateType = "yyyy";19 SimpleDateFormat df = new SimpleDateFormat(dateType);//设置⽇期格式
20 SimpleDateFormat df1 = new SimpleDateFormat("yyyy.MM.dd");//设置⽇期格式21 //创建HSSFWorkbook对象(excel的⽂档对象)
22 HSSFWorkbook wb = newHSSFWorkbook();23 HSSFRow row = null;24 HSSFCell cell = null;25 //建⽴新的sheet对象(excel 的表单) 并设置sheet名字
26 HSSFSheet sheet = wb.createSheet("占压管线台账信息");27 sheet.tDefaultRowHeightInPoints(30);//设置缺省列⾼
sheet.tDefaultColumnWidth(20);//设置缺省列宽28 //----------------标题样式---------------------
29 HSSFCellStyle titleStyle = wb.createCellStyle(); //标题样式
30 titleStyle.tAlignment(HSSFCellStyle.ALIGN_CENTER);31
titleStyle.tVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);32 Font ztFont =wb.createFont();33
ztFont.tItalic(fal); //设置字体为斜体字
34 ztFont.tColor(Font.COLOR_NORMAL); //将字体设置为“红⾊”
35 ztFont.tFontHeightInPoints((short)16); //将字体⼤⼩设置为18px
36 ztFont.tFontName("宋体"); //将“宋体”字体应⽤到当前单元格上
37 ztFont.tBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //加粗38 //ztFont.tUnderline(Font.U_DOUBLE);//添加
(Font.U_SINGLE单条下划线/Font.U_DOUBLE双条下划线)39 //ztFont.tStrikeout(true);//是否添加删除线
40 titleStyle.tFont(ztFont);41 //-------------------------------------------42 //----------------⼆级标题格样式----------------------------------
43 HSSFCellStyle titleStyle2 = wb.createCellStyle(); //表格样式
韩语入门基础44 titleStyle2.tAlignment(HSSFCellStyle.ALIGN_CENTER);45
titleStyle2.tVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);46 Font ztFont2 =wb.createFont();47
ztFont2.tItalic(fal); //设置字体为斜体字
48 ztFont2.tColor(Font.COLOR_NORMAL); //将字体设置为“红⾊”
49 ztFont2.tFontHeightInPoints((short)11); //将字体⼤⼩设置为18px
50 ztFont2.tFontName("宋体"); //字体应⽤到当前单元格上
51 ztFont2.tBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //加粗52 //ztFont.tUnderline(Font.U_DOUBLE);//添加
(Font.U_SINGLE单条下划线/Font.U_DOUBLE双条下划线)53 //ztFont.tStrikeout(true);//是否添加删除线
autorun是什么>マンコ54 titleStyle2.tFont(ztFont2);55 //----------------------------------------------------------56 //----------------单元格样式----------------------------------
57 HSSFCellStyle cellStyle = wb.createCellStyle(); //表格样式
58 cellStyle.tAlignment(HSSFCellStyle.ALIGN_CENTER);59
cellStyle.tVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);60
cellStyle.tBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
61 cellStyle.tBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
62 cellStyle.tBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
补妆
63 cellStyle.tBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
64 Font cellFont =wb.createFont();65 cellFont.tItalic(fal); //设置字体为斜体字
韩语我爱你66 cellFont.tColor(Font.COLOR_NORMAL); //将字体设置为“红⾊”
67 cellFont.tFontHeightInPoints((short)10); //将字体⼤⼩设置为18px
68 cellFont.tFontName("宋体"); //字体应⽤到当前单元格上69 //cellFont.tBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
70 cellStyle.tFont(cellFont);71 cellStyle.tWrapText(true);//设置⾃动换⾏72 //----------------------------------------------------------73 //----------------------创建第⼀⾏---------------74 //在sheet⾥创建第⼀⾏，参数为⾏索引(excel的⾏)，可以是0～65535之间的任何⼀个
75 row = ateRow(0);76 //创建单元格(excel的单元格，参数为列索引，可以是0～255之间的任何⼀个
77 cell = ateCell(0);78 //合并单元格CellRangeAddress构造参数依次表⽰起始⾏，截⾄⾏，起始列， 截⾄列
79 sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 19));80 //设置单元格内容
81 cell.tCellValue("占压城市地下管线、输油⽓管道、化⼯产品管道违法违规建设汇总表");82 cell.tCellStyle(titleStyle);83 //----------------------------------------------84
85 //------------------创建第⼆⾏(单位、填表⽇期)---------------------
86 row = ateRow(1); //创建第⼆⾏
87 cell = ateCell(0);88 cell.tCellValue("填报单位名称(盖章)： ");89 cell.tCellStyle(titleStyle2);90
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 3));91 cell = ateCell(4);92 sheet.addMergedRegion(new CellRangeAddress(1, 1, 4, 5));93 TSBaUr tb = SessionUrName(); //获取当前登录⽤户信息
94 String uid =tb.getId();95 String deptId =DeptId(uid);96 String deptName =DeptName(deptId);97 cell.tCellValue(deptName);98 //cell.tCellValue("*****");
99 cell.tCellStyle(titleStyle2);100 cell = ateCell(13); //填表时间
101 sheet.addMergedRegion(new CellRangeAddress(1, 1, 13, 16));102 cell.tCellValue("填表时
间："+df1.format(newDate()));103 cell.tCellStyle(titleStyle2);104 //HSSFCell cell14 = ateCell(15);//填表时间105
//cell14.tCellValue();106 //cell14.tCellValue("2017.11.30");107 //cell14.tCellStyle(titleStyle2);108 //----------------------------------------------109
110 //------------------创建表头start---------------------
111 row = ateRow(2); //创建第三⾏
112 sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));113 cell = ateCell(0);114 cell.tCellValue("序号");115 cell.tCellStyle(cellStyle);116
117 sheet.addMergedRegion(new CellRangeAddress(2, 3, 1, 1));118 cell = ateCell(1);119 cell.tCellValue("隐患等级评定");120 cell.tCellStyle(cellStyle);121
医学生考研网
述");130 cell.tCellStyle(cellStyle);131
132 cell = ateCell(4);133 cell.tCellStyle(cellStyle);134 cell = ateCell(5);135 cell.tCellStyle(cellStyle);136
137 sheet.addMergedRegion(new CellRangeAddress(2, 2, 6, 10));138 cell = ateCell(6);139 cell.tCellValue("管线情况");140 cell.tCellStyle(cellStyle);141
142 cell = ateCell(7);143 cell.tCellStyle(cellStyle);144 cell = ateCell(8);145 cell.tCellStyle(cellStyle);146 cell = ateCell(9);147 cell.tCellStyle(cellStyle);148 cell = ateCell(10);149 cell.tCellStyle(cellStyle);150
151 sheet.addMergedRegion(new CellRangeAddress(2, 2, 11, 13));152 cell = ateCell(11);153 cell.tCellValue("占压物情况");154 cell.tCellStyle(cellStyle);155
156 cell = ateCell(12);157 cell.tCellStyle(cellStyle);158 cell = ateCell(13);159
cell.tCellStyle(cellStyle);160vomit
161 sheet.addMergedRegion(new CellRangeAddress(2, 2, 14, 14));162 cell = ateCell(14);163 cell.tCellValue("占压物⽤途");164 cell.tCellStyle(cellStyle);165
166 sheet.addMergedRegion(new CellRangeAddress(2, 3, 15, 15));167 cell = ateCell(15);168 cell.tCellValue("已采⽤的安全防护措施");169 cell.tCellStyle(cellStyle);170
171 sheet.addMergedRegion(new CellRangeAddress(2, 3, 16, 16));172 cell = ateCell(16);173 cell.tCellValue("备注");174 cell.tCellStyle(cellStyle);175
176 sheet.addMergedRegion(new CellRangeAddress(2, 3, 17, 17));177 cell = ateCell(17);178 cell.tCellValue("联系⼈电话");179 cell.tCellStyle(cellStyle);180
lundbeck181 sheet.addMergedRegion(new CellRangeAddress(2, 3, 18, 18));182 cell = ateCell(18);183 cell.tCellValue("是否已和区管委和供热办联系");184 cell.tCellStyle(cellStyle);185
186 sheet.addMergedRegion(new CellRangeAddress(2, 3, 19, 19));187 cell = ateCell(19);188 cell.tCellValue("是否采取防范措施");189 cell.tCellStyle(cellStyle);190
191 //--------------------------- 创建第四⾏--------------------
192 row = ateRow(3);193 sheet.addMergedRegion(new CellRangeAddress(3, 3, 3, 3));194 cell =
198 cell = ateCell(0);199 cell.tCellStyle(cellStyle);200
201 sheet.addMergedRegion(new CellRangeAddress(3, 3, 4, 4));202 cell = ateCell(4);203 cell.tCellValue("所在街道");204 cell.tCellStyle(cellStyle);205
206 cell = ateCell(1);207 cell.tCellStyle(cellStyle);208
209 sheet.addMergedRegion(new CellRangeAddress(3, 3, 5, 5));210 cell = ateCell(5);211 cell.tCellValue("详细地址");212 cell.tCellStyle(cellStyle);213
214 sheet.addMergedRegion(new CellRangeAddress(3, 3, 6, 6));215 cell = ateCell(6);216 cell.tCellValue("管线建成时间");217 cell.tCellStyle(cellStyle);218
219 sheet.addMergedRegion(new CellRangeAddress(3, 3, 7, 7));220 cell = ateCell(7);221 cell.tCellValue("管线埋深");222 cell.tCellStyle(cellStyle);223
224 sheet.addMergedRegion(new CellRangeAddress(3, 3, 8, 8));225 cell = ateCell(8);226 cell.tCellValue("管径");227 cell.tCellStyle(cellStyle);228
管线长度");237 cell.tCellStyle(cellStyle);238
239 sheet.addMergedRegion(new CellRangeAddress(3, 3, 11, 11));240 cell = ateCell(11);241 cell.tCellValue("占压单位(个⼈)名称");242 cell.tCellStyle(cellStyle);243
244 sheet.addMergedRegion(new CellRangeAddress(3, 3, 12, 12));245 cell = ateCell(12);246 cell.tCellValue("占压物建成时间");247 cell.tCellStyle(cellStyle);248
249 sheet.addMergedRegion(new CellRangeAddress(3, 3, 13, 13));250 cell = ateCell(13);251 cell.tCellValue("占压物⾯积(平⽅⽶)");252 cell.tCellStyle(cellStyle);253
254 sheet.addMergedRegion(new CellRangeAddress(3, 3, 14, 14));255 cell = ateCell(14);256 cell.tCellValue("经营、出租、⾃⽤、居住");257 cell.tCellStyle(cellStyle);258
259 cell = ateCell(15);260 cell.tCellStyle(cellStyle);261 cell = ateCell(16);262
cell.tCellStyle(cellStyle);263 cell = ateCell(17);264 cell.tCellStyle(cellStyle);265 cell = ateCell(18);266 cell.tCellStyle(cellStyle);267 cell = ateCell(19);268 cell.tCellStyle(cellStyle);269 //-------------------------表头end---------------------
270 CriteriaQuery cq = new CriteriaQuery(HAqscTieupsummaryEntity.class,271 dataGrid);272
xtend.hqlarch.HqlGenerateUtil.installHql(cq,273 hAqscTieupsummary,
.getListByCriteriaQuery(cq, fal);276 for (int i = 0; i < hAqscTieupsummarys.size(); i++) { //向表格插⼊数据
277 List data = new ArrayList<>(); //将前台传来的数据存⼊到list中278
//System.out.(i).getSeqNum());
279 HAqscTieupsummaryEntity entity =(i);280 data.SeqNum());281 String yhjb
=YhDjpd();282 String hyjb =Hyjb(yhjb);283 data.add(hyjb); //隐患级别
284 data.YhName());285 String countryName = Atcounty()); //区县
286 data.add(countryName);287 String code =Atdistrict();288 String streetName = StreetByCode(code); //街道
289 data.add(streetName);290 data.Address());291 Date buildtime =PipelineBuildtime();292 if (buildtime!=null) {293 String format =df.format(buildtime);294 data.add(format);295 }el{296 data.add("");297 }298 data.PipelineDepth());299 data.PipeSize());300 data.PipelinePr());301
data.TppipelineLength());302 data.TieupName());303 Date goodsBuildtime
=TieupgoodsBuildtime();304 if (buildtime!=null) {305 String format =df.format(goodsBuildtime);306
data.add(format);307 }el{308 data.add("");309 }310 data.TieupgoodsArea());311 String uType
=TieupgoodsU();312 data.UType(uType));313 data.USecuritymeasures());314 data.Remark());315 data.Telephone());316 data.IsContact()));317 data.IsUcuritymeasures());318 int rowNum = 4+i; //从第四⾏开始
319 row =ateRow(rowNum);320 for (int j = 0; j < data.size(); j++) { //将数据添加到单元格中321
//System.out.(j));
322 sheet.addMergedRegion(newCellRangeAddress(rowNum, rowNum, j, j));323 cell =ateCe
ll(j);324
cell.tCellValue(""+(j)+"");325 cell.tCellStyle(cellStyle);326 }327 }328
329 //输出Excel⽂件
330 OutputStream output =OutputStream();();332 respon.tHeader("Content-disposition",333 "attachment; filename=details.xls"); //filename = ⽂件名
334 respon.tContentType("application/mxcel");335 wb.write(output);336 output.clo();337 } catch(IOException e) {338 //TODO Auto-generated catch block
339 e.printStackTrace();340 }341 return null;342 }
