jxl读excel模板

	os = response.getOutputStream();
			response.reset();// 清空输出流
			response.setContentType("request/vnd.ms-excel");
			response.addHeader("Content-Disposition", new String(
					"attachment; filename=invoiceExcel.xls".getBytes("GBK"),
					"ISO-8859-1"));

public class InvoiceExcel {
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public static void export(String excelPath, List<Test2> invoiceList,OutputStream os){
		//选择模板文件：
		try {
			InputStream is = new FileInputStream(excelPath); 
			Workbook wb = Workbook.getWorkbook(is);
			//通过模板得到一个可写的Workbook：
			 WritableCell wc = null;
		
			WritableWorkbook wwb = Workbook.createWorkbook(os, wb);
			//选择模板中名称为StateResult的Sheet：
			
			WritableSheet ws = wwb.getSheet("commercial invoice");
		
			WritableCellFormat wcf = ExcelFormatter.getWritableCellFormatCellFormat();
			WritableCellFormat noWCF	= new WritableCellFormat();
			noWCF.setBorder(Border.ALL, BorderLineStyle.NONE);
			//选择单元格，写入动态值，根据单元格的不同类型转换成相应类型的单元格：
			Label lable;
			wc = ws.getWritableCell(7, 9);
			wc = ExcelFormatter.cloneCellWithValue(7, 9, "789456",noWCF);
			ws.addCell(wc);
			
			wc = ws.getWritableCell(7, 11);
			wc = ExcelFormatter.cloneCellWithValue(7, 11, "1100019/MAG11041",noWCF);
			ws.addCell(wc);

			wc = ws.getWritableCell(7, 14);
			wc = ExcelFormatter.cloneCellWithValue(7, 14, "BY SEA",noWCF);
			ws.addCell(wc);
			
			wc = ws.getWritableCell(0, 16);
			wc = ExcelFormatter.cloneCellWithValue(0, 16, "# 2011 - shanghai dd 03/01/2011",noWCF);
			ws.addCell(wc);
			
			wc = ws.getWritableCell(5, 16);
			wc = ExcelFormatter.cloneCellWithValue(5, 16, "1,22",noWCF);
			ws.addCell(wc);
			
			wc = ws.getWritableCell(6, 16);
			wc = ExcelFormatter.cloneCellWithValue(6, 16, "Moscow",noWCF);
			ws.addCell(wc);
			
			wc = ws.getWritableCell(7, 16);
			wc = ExcelFormatter.cloneCellWithValue(7, 16, "worE",noWCF);
			ws.addCell(wc);
			
			wc = ws.getWritableCell(0, 10);
			wc = ExcelFormatter.cloneCellWithValue(0, 10, "OOO CUMMINS Moscow",noWCF);
			ws.addCell(wc);
			
			wc = ws.getWritableCell(0, 11);
			wc = ExcelFormatter.cloneCellWithValue(0, 11, "Russia,  Khimki Area, Klyazma, 1G,  Moscow region, Russia, 141402",noWCF);
			ws.addCell(wc);
			
			
			
			//表格主体循环打入数据
			
			 for(int i=0;i<invoiceList.size();i++){  
				 wc = ws.getWritableCell(0, 18+i);
				 wc = ExcelFormatter.cloneCellWithValue(0, 18+i, invoiceList.get(i).getBoxno(),wcf);
				 ws.addCell(wc);
				 wc = ws.getWritableCell(1, 18+i);
				 wc = ExcelFormatter.cloneCellWithValue(1, 18+i, invoiceList.get(i).getOrderno(),wcf);
				 ws.addCell(wc);
				 wc = ws.getWritableCell(2, 18+i);
				 wc = ExcelFormatter.cloneCellWithValue(2, 18+i, invoiceList.get(i).getCo(),wcf);
				 ws.addCell(wc);
				 wc = ws.getWritableCell(3, 18+i);
				 wc = ExcelFormatter.cloneCellWithValue(3, 18+i, invoiceList.get(i).getPartno(),wcf);
				 ws.addCell(wc);
				 wc = ws.getWritableCell(4, 18+i);
				 wc = ExcelFormatter.cloneCellWithValue(4, 18+i, invoiceList.get(i).getSsrelationship(),wcf);
				 ws.addCell(wc);
				 wc = ws.getWritableCell(5, 18+i);
				 wc = ExcelFormatter.cloneCellWithValue(5, 18+i, invoiceList.get(i).getDsction(),wcf);
				 ws.addCell(wc);
				 wc = ws.getWritableCell(6, 18+i);
				 wc = ExcelFormatter.cloneCellWithValue(6, 18+i, invoiceList.get(i).getQty(),wcf);
				 ws.addCell(wc);
				 wc = ws.getWritableCell(7, 18+i);
				 wc = ExcelFormatter.cloneCellWithValue(7, 18+i, invoiceList.get(i).getUnitprice(),wcf);
				 ws.addCell(wc);
				 wc = ws.getWritableCell(8, 18+i);
				 wc = ExcelFormatter.cloneCellWithValue(8, 18+i, invoiceList.get(i).getAmout(),wcf);
				 ws.addCell(wc);
				 

			 }
			 wwb.write();     
			 // 关闭文件     
     wwb.close();
     System.out.println("导出成功");
		} catch (Exception e) {
			System.out.println("导出失败");
			e.printStackTrace();
		}   	
		
	}

}

	/**
	 * 验证输入的数据格式转换
	 * @param col
	 * @param row
	 * @param value
	 * @param wcFormat
	 * @return
	 */
	public static WritableCell cloneCellWithValue(int col, int row, Object value,WritableCellFormat wcFormat) {
		WritableCell wc = null;
		// 判断数据是否为STRING类型，是用LABLE形式插入，否则用NUMBER形式插入
		if (value == null) {
			wc = new jxl.write.Blank(col, row,wcFormat);
		} else if (value instanceof String) {
			jxl.write.Label label = new jxl.write.Label(col, row,
					value.toString(),wcFormat);
			wc = label;
		} else {
			wc = new jxl.write.Number(col, row,
					new Double(value.toString()).doubleValue(),wcFormat);
		}
		return wc;
	}

	public static WritableCellFormat getWritableCellFormatCellFormat(){
		
		WritableCellFormat wcf = new WritableCellFormat();     
	     
		try {
			  // 设置居中   
			wcf.setAlignment(Alignment.CENTRE);
			wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}     
	       
		
		return wcf;
	}
}

PS:项目中用到的一个关于jxl 读取excel模板的代码 公大家学习

评论

1 楼 310628570 2011-08-22  

挺有用的啊~ 谢谢了~