使用WritableSheet导出excel

这次的报表和以前做的都不一样，

这次使用到了模板文件， ^_^  学到了很多东西，先记录下来

//-------------------------------------完單項目剩餘物料申報表--------------------------------------------------------------
	
	@RequestMapping(value = "com/el/ProjectEnd/exportExecl2.action")
	public void exportExecl2(HttpServletRequest request, HttpServletResponse response) {
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Content-Disposition", "attachment; filename=PESurplusMaterial.xls; target=_blank");
		ExportToExcel2 ete2 = new ExportToExcel2();
		Enumeration<?> enu = request.getParameterNames();
		int projEndId = 0;
		while(enu.hasMoreElements()){
			String rn = (String) enu.nextElement();

			String id = request.getParameter(rn);

			projEndId = Integer.parseInt(id);
		}

		ProjItemSumHead projItemSumHead = this.projItemSumHeadService.findProjItemSumHeadByProjEndId(projEndId);
		
		try {
			OutputStream os = ete2.exportSurplusMaterialExcel(response, request,projItemSumHead);
			try {
				if(os != null){
					os.flush();
					os.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		} catch (BiffException e1) {
			e1.printStackTrace();
		} catch (WriteException e1) {
			e1.printStackTrace();
		} catch (IOException e1) {
			e1.printStackTrace();
		}
	}




package com.el.pe.util.export;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Locale;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Colour;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import com.el.pe.model.ProjItemSumHead;

public class ExportToExcel2 {
    public OutputStream exportSurplusMaterialExcel(HttpServletResponse response, HttpServletRequest request,ProjItemSumHead projItemSumHead) throws IOException, BiffException, WriteException {
        OutputStream os = response.getOutputStream();
        
        String pe_templateurl = request.getSession().getServletContext().getRealPath("/WEB-INF/pe_template/PESurplusMaterial.xls");
        System.out.println("url is ------------"+pe_templateurl);
        Workbook workbook = null;
        InputStream in = new FileInputStream(pe_templateurl);
        workbook = Workbook.getWorkbook(in);
        WorkbookSettings ws = new WorkbookSettings();
        Locale locale = new Locale("zh","CN");
        ws.setLocale(locale);
        ws.setEncoding("utf-8");
// 下面这句代码是关键，将workbook写入os流中
         WritableWorkbook wbook=Workbook.createWorkbook(os,workbook,ws); 
        WritableSheet writeSheet = wbook.getSheet(0);// sheet名称 
        
        WritableFont font1 = new WritableFont(WritableFont.TIMES,10,WritableFont.NO_BOLD);         
        WritableCellFormat format1=new WritableCellFormat(font1); 
        
        WritableFont font2 = new WritableFont(WritableFont.TIMES,10,WritableFont.NO_BOLD);         
        WritableCellFormat format2=new WritableCellFormat(font2); 
        format2.setBackground(Colour.YELLOW2);
//5代表列，11代表行 
        writeSheet.addCell(new Label(5,10,projItemSumHead.getProjId(),format1));//項目編號
        writeSheet.addCell(new Label(5,11,"項目名稱",format1));
        writeSheet.addCell(new Label(5,12,projItemSumHead.getCustGroup(),format1));//洋行
        writeSheet.addCell(new Label(5,13,"TBA",format1));
        
        writeSheet.addCell(new Label(25,10,projItemSumHead.getProductYear(),format1));//
        writeSheet.addCell(new Label(25,11,"總訂單數量",format1));
        writeSheet.addCell(new Label(25,12,"以落貨數量",format1));
        writeSheet.addCell(new Label(25,13,projItemSumHead.getLastOrderDate().toLocaleString(),format1));
        
        writeSheet.addCell(new Label(2,19,projItemSumHead.getUserGroup(),format2));
        writeSheet.addCell(new Label(10,19,projItemSumHead.getSubUserGroup(),format2));
        
        wbook.write(); // 写入文件  
        wbook.close();  
        workbook.close();
        in.close();
        return os;
        
    }
    
}