创建Excel 并导出到Web客户端

package com.java.util;

import java.io.File;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
/**
	 * @param args
	 * @throws Exception
          * 以下是通过开源框架jxl实现的一个 解析、创建、修改的例子
	 */
public class ExcelParse {

	/**
	 * @param args
	 * @throws Exception
	 */
	public static void main(String[] args)   {

		ExcelParse eParse = new ExcelParse();
		try {
			//String string = eParse.parseXLS("F:\\j\\test.xls");
//			eParse.createXLS("f:\\j\\temp.xls");
			eParse.modifyXLS("F:\\j\\test.xls");

		} catch (Exception e) {
			e.printStackTrace() ;
		}

	}

	// 解释EXCEL 并返回Content
	@SuppressWarnings("unused")
	private String parseXLS(String path) throws Exception {

		StringBuffer sb = new StringBuffer(0);
		Workbook wk = Workbook.getWorkbook(new File(path));

		if (wk == null) return null;
		Sheet[] sheet = wk.getSheets(); // 取所有工作表

		for (Sheet _sheet : sheet) {
			System.out.println("当前工作表名称 :" + _sheet.getName());
			int rownum = _sheet.getRows();// 取当前工作表行数
			System.out.println("当前工作表行：" + rownum);

			for (int i = 0; i < rownum; i++) {
				Cell[] cells = _sheet.getRow(i);//取当前row中所有cell

				for (Cell cell : cells) {
					sb.append(cell.getContents() + " - - - ");
				}
				sb.append("\n");
			}
		}
		wk.close() ;
		return sb.toString();
	}

	//修改EXCEL
	public void modifyXLS(String path)throws Exception{
		Workbook wk = Workbook.getWorkbook(new File(path)) ;
		WritableWorkbook wwk = Workbook.createWorkbook(new File("f:\\j\\g.xls"), wk);
		WritableSheet ws = wwk.getSheet(0);
		// 第2行第0列
		WritableCell cell  = ws.getWritableCell(0,2);
		Label lable = (Label) cell;
		lable.setString("Fuck you !");
		wwk.write();
		wwk.close();
		wk.close();
	}

	//创建一个EXCEL
	@SuppressWarnings("unused")
	private void createXLS(String xlsPath) throws Exception{

		WritableWorkbook wwb = Workbook.createWorkbook(new File(xlsPath));
		WritableSheet ws = wwb.createSheet("测试", 0); //创建一个可写的sheet

		for (int i = 0; i < 10; i++) {

			for (int j = 0; j < 6; j++) {


			   //设置字体
				WritableFont wf = new WritableFont(WritableFont.TIMES, 18,
						WritableFont.BOLD, true); 
				//设置颜色
				wf= new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD, false,
						UnderlineStyle.NO_UNDERLINE, Colour.RED);  
				WritableCellFormat wcfF = new WritableCellFormat(wf);

				Label lable = new Label(j, i, "这是第"+(i)+"行第"+(j)+"列",wcfF);
				ws.addCell(lable);


			/*	//添加图片
				WritableImage wiImage = new WritableImage(j, i, 5,
						5, new File("f:\\j\\aa.png"));
				ws.addImage(wiImage);
				*/

				/*		
				 添加页眉页脚	
				HeaderFooter hf = new HeaderFooter();   
				     hf.getLeft().append("left");   
				     hf.getCentre().append("centre");   
				     hf.getRight().append("right");   
				     //加入页眉   
				     ws.getSettings().setHeader(hf);   
				    //加入页脚   
				    //ws.getSettings().setFooter(hf);  
			 	*/			
				}
		}
		wwb.write() ; //将内存中的数据写入目标
		wwb.close() ;
	}

}

     //以下代码是通过poi框架实现

	//导出Excel  列表
	public ActionForward printExcel(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception
	{
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Content-Disposition", "inline; filename=\"order.xls\"");
		response.setHeader("Pragma", "No-cache");
		response.setHeader("Cache-Control", "No-cache"); 
		response.setDateHeader("Expires", 0);

		String order = request.getParameter("order");
		HSSFWorkbook wb  = this.getHSSFWorkbook(orderMgr.getCommodityByOrderNo(order),order) ; 
		wb.write(response.getOutputStream());
		response.flushBuffer() ;
		return null;
	}


private HSSFWorkbook getHSSFWorkbook(List<Object[]> list , String order){
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("sheet1");
		sheet.setColumnWidth(0,5000) ;
		sheet.setColumnWidth(1,7000) ;
		sheet.setColumnWidth(2,5000) ;
		sheet.setColumnWidth(3,7000) ;
		sheet.setColumnWidth(4,5000) ;

//        HSSFCellStyle   style   =   wb.createCellStyle();
//        style.setAlignment(HSSFCellStyle.ALIGN_CENTER) ;
//        style.setFont(font); 

        HSSFCellStyle  pubStyle  = wb.createCellStyle() ;
        pubStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER) ;

		HSSFRow row = sheet.createRow(0);
		row.setHeight((short) 400 ) ;

		HSSFCell orderNO  = row.createCell(0) ;
		orderNO.setCellValue("订单号");
		orderNO.setCellStyle(pubStyle);

		row.createCell(1).setCellValue(order);

		HSSFRow rowb = sheet.createRow(1);
		HSSFCell ca = rowb.createCell(0);
		ca.setCellValue("货号");
		ca.setCellStyle(pubStyle);

		HSSFCell cb  =rowb.createCell(1);
		cb.setCellValue("书名");
		cb.setCellStyle(pubStyle);

		HSSFCell cc = rowb.createCell(2);
		cc.setCellValue("报定数量");
		cc.setCellStyle(pubStyle);

		HSSFCell cd =rowb.createCell(3);
		cd.setCellValue("当前架位号");
		cd.setCellStyle(pubStyle);

		HSSFCell ce = rowb.createCell(4);
		ce.setCellValue("当前库存数量");
		ce.setCellStyle(pubStyle);

		for (int i = 0; i < list.size(); i++)
		{	
			Object[] obj = list.get(i) ;
			HSSFRow tmpR = sheet.createRow(i+2);
			HSSFCell plucode =tmpR.createCell(0);
			plucode.setCellValue(obj[0].toString());
			plucode.setCellStyle(pubStyle);

			HSSFCell bookName = tmpR.createCell(1);
			bookName.setCellValue(obj[1].toString());
			bookName.setCellStyle(pubStyle);

			HSSFCell buyCount = tmpR.createCell(2);
			buyCount.setCellValue(obj[2].toString());
			buyCount.setCellStyle(pubStyle);

			HSSFCell point = tmpR.createCell(3);
			point.setCellValue(obj[3].toString());
			point.setCellStyle(pubStyle);

			HSSFCell discount=tmpR.createCell(4);
			discount.setCellValue(obj[4].toString());
			discount.setCellStyle(pubStyle);
		}
		return wb ;
	}