前台
<ul class="navtop-right">
<li >
<a href="/portal/trip/importExec" title="Data Download">
<img src="${pageContext.request.contextPath}/style/images/excel6.jpg" width=20px height=20px style="padding-top:15px"/>
</a>
</li>
</ul>
后台
HSSFRow row = sheet.createRow(0);: 创建第0行
HSSFCell c2 = row.createCell(2); 创建0行2列
HSSFCell c3 = row.createCell(4); 说明2列占了2列
sheet.addMergedRegion(new CellRangeAddress(0, 1,(short)0, (short)0)); 说明 合并单元格, 0行,1行合并成一行
@RequestMapping(value = "importExec", method = RequestMethod.GET)
@ResponseBody
public void importExec(HttpServletRequest request, HttpServletResponse response) throws UnsupportedEncodingException {
String fname = "Biztrip list";
response.reset();// 清空输出流
response.setHeader("Content-disposition","attachment; filename=" + fname + ".xls");// 设定输出文件头
response.setContentType("application/msexcel");//EXCEL格式 Microsoft excel
//创建workbook
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
HSSFFont f = workbook.createFont();
// f.setColor(HSSFColor.RED.index);
f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗
style.setFont(f);
style.setFillForegroundColor(HSSFColor.LIME.index);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//创建sheet页
HSSFSheet sheet = workbook.createSheet("Business Trip Info.");
//创建单元格
HSSFRow row = sheet.createRow(0);
HSSFCell c0 = row.createCell(0);
c0.setCellValue(new HSSFRichTextString("No"));
c0.setCellStyle(style);
HSSFCell c1 = row.createCell(1);
c1.setCellValue(new HSSFRichTextString("Name"));
c1.setCellStyle(style);
HSSFCell c2 = row.createCell(2);
c2.setCellValue(new HSSFRichTextString("Part"));
c2.setCellStyle(style);
HSSFCell c3 = row.createCell(4);
c3.setCellValue(new HSSFRichTextString("Purpose"));
c3.setCellStyle(style);
HSSFCell c4 = row.createCell(5);
c4.setCellValue(new HSSFRichTextString("Schedule"));
c4.setCellStyle(style);
HSSFCell c5 = row.createCell(8);
c5.setCellValue(new HSSFRichTextString("Destination"));
c5.setCellStyle(style);
HSSFCell c6 = row.createCell(11);
c6.setCellValue(new HSSFRichTextString("Report"));
c6.setCellStyle(style);
HSSFCell c7 = row.createCell(12);
c7.setCellValue(new HSSFRichTextString("Ref."));
c7.setCellStyle(style);
HSSFRow row1 = sheet.createRow(1);
HSSFCell c8 = row1.createCell(5);
c8.setCellValue(new HSSFRichTextString("Start"));
c8.setCellStyle(style);
HSSFCell c9 = row1.createCell(6);
c9.setCellValue(new HSSFRichTextString("End"));
c9.setCellStyle(style);
HSSFCell c10 = row1.createCell(7);
c10.setCellValue(new HSSFRichTextString("Days"));
c10.setCellStyle(style);
HSSFCell c11 = row1.createCell(8);
c11.setCellValue(new HSSFRichTextString("Country"));
c11.setCellStyle(style);
HSSFCell c12 = row1.createCell(9);
c12.setCellValue(new HSSFRichTextString("Region"));
c12.setCellStyle(style);
HSSFCell c13 = row1.createCell(10);
c13.setCellValue(new HSSFRichTextString("Dept."));
c13.setCellStyle(style);
sheet.addMergedRegion(new CellRangeAddress(0, 1,(short)0, (short)0));
sheet.addMergedRegion(new CellRangeAddress(0, 1,(short)1, (short)1));
sheet.addMergedRegion(new CellRangeAddress(0, 1,(short)2, (short)3));
sheet.addMergedRegion(new CellRangeAddress(0, 1,(short)4, (short)4));
sheet.addMergedRegion(new CellRangeAddress(0, 0,(short)5, (short)7));
sheet.addMergedRegion(new CellRangeAddress(0, 0,(short)8, (short)10));
sheet.addMergedRegion(new CellRangeAddress(0, 1,(short)11, (short)11));
sheet.addMergedRegion(new CellRangeAddress(0, 1,(short)12, (short)12));
List<Trip> tripList = tripService.findAll();
for(int i=0;i<tripList.size();i++){
row=sheet.createRow((int)i+2);
Trip trip = (Trip)tripList.get(i);
row.createCell((short)0).setCellValue(new HSSFRichTextString(i+1+""));
row.createCell((short)1).setCellValue(new HSSFRichTextString(trip.getName()));
row.createCell((short)2).setCellValue(new HSSFRichTextString(trip.getPart()));
row.createCell((short)3).setCellValue(new HSSFRichTextString(trip.getSubPart()));
row.createCell((short)4).setCellValue(new HSSFRichTextString(trip.getPurpose()));
row.createCell((short)5).setCellValue(new HSSFRichTextString(trip.getScheduleStart()));
row.createCell((short)6).setCellValue(new HSSFRichTextString(trip.getScheduleEnd()));
row.createCell((short)7).setCellValue(new HSSFRichTextString(trip.getDuration()));
row.createCell((short)8).setCellValue(new HSSFRichTextString(trip.getDestination()));
row.createCell((short)9).setCellValue(new HSSFRichTextString(trip.getRegion()));
row.createCell((short)10).setCellValue(new HSSFRichTextString(trip.getDepartment()));
row.createCell((short)11).setCellValue(new HSSFRichTextString(trip.getReport()));
row.createCell((short)12).setCellValue(new HSSFRichTextString(trip.getReferrence()));
}
try{
workbook.write(response.getOutputStream());
}
catch (Exception e){
e.printStackTrace();
}
}