后台处理excel下载输出流

前台

<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();

	    }

	}

秒客网

后台处理excel下载输出流

相关文章