java jxl 导出excel 附带导出图片和单元格样式

sess = HibernateSessionFactory.currentSession();tran = sess.beginTransaction();String id = Util.getRequest(request, "id");//订单idSellCI sci = (SellCI) sess.get(SellCI.class, Integer.parseInt(id));//订单产品列表List list = new ArrayList();String hql = "from SellCIList where sciId="+id;list = sess.createQuery(hql).list();String fname = "" + System.nanoTime();// Excel文件名response.reset();// 清空输出流response.setHeader("Content-disposition", "attachment;filename="+ fname + ".xls");// 设定输出文件头response.setContentType("application/msexcel");// 定义输出类型//img_logo.png  图片WritableWorkbook book = Workbook.createWorkbook(response.getOutputStream());WritableSheet sheet = book.createSheet("报关CI", 0);  /** ********* 打印属性 **************** */sheet.getSettings().setOrientation(PageOrientation.PORTRAIT) ;    // 设置为直(LANDSCAPE横向)打印            sheet.getSettings().setPaperSize(PaperSize.A4) ;            // 设置纸张                        sheet.getSettings().setFitHeight(297) ;                        // 打印区高度            sheet.getSettings().setFitWidth(210) ;                        // 打印区宽度                            //列宽度设置sheet.setColumnView(0, (int) 16); //1sheet.setColumnView(1, (int) 16); //2sheet.setColumnView(2, (int) 16); //3sheet.setColumnView(3, (int) 16); //4sheet.setColumnView(4, (int) 16); //5sheet.setColumnView(5, (int) 16); //5//列宽度设置            sheet.getSettings().setTopMargin(0.8) ;            sheet.getSettings().setBottomMargin(0.3) ;            sheet.getSettings().setLeftMargin(0.9) ;            sheet.getSettings().setRightMargin(0.1) ;            // 设置页脚            sheet.getSettings().getFooter().getCentre().appendPageNumber() ;    // 为页脚添加页数            sheet.getSettings().setFooterMargin(0.07) ;                            // 设置页脚边距（下）                        //去掉整个sheet中的网格线              sheet.getSettings().setShowGridLines(false);            WritableFont wf = null;            WritableCellFormat wcfF = null;            Label label = null;int rowNum = 0;//行参数picpath=request.getRealPath("images")+"\\img_logo1.png";File file=new File(picpath);WritableImage image=new WritableImage(0, rowNum, 1, 2, file);sheet.setRowView(rowNum, 750);   //设置行高sheet.addImage(image);wf = new WritableFont(WritableFont.createFont("Calibri"), 15, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.CENTRE);//垂直居中sheet.mergeCells(1, rowNum, 5, rowNum);label = new Label(1, rowNum, "Shenzhen Bangdao Electronic Technology Co., Ltd.",wcfF);sheet.addCell(label);rowNum++;//行增加sheet.setRowView(rowNum, 450); wf = new WritableFont(WritableFont.createFont("Calibri"), 15, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.CENTRE); //垂直居中sheet.mergeCells(1, rowNum, 5, rowNum);label = new Label(1, rowNum, "Commercial Invoice",wcfF);sheet.addCell(label);rowNum++;sheet.setRowView(rowNum, 300); wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.RIGHT);//居右sheet.mergeCells(0, rowNum, 4, rowNum);label = new Label(0, rowNum, "Invoice  Number：",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.LEFT);//居右label = new Label(5, rowNum, sci.getCiNumber(),wcfF);sheet.addCell(label);rowNum++;sheet.setRowView(rowNum, 300); wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.RIGHT);//居右sheet.mergeCells(0, rowNum, 4, rowNum);label = new Label(0, rowNum, "Invoice Date：",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.LEFT);//居右label = new Label(5, rowNum, sci.getIndate(),wcfF);sheet.addCell(label);rowNum++;sheet.setRowView(rowNum, 300); sheet.mergeCells(0, rowNum, 5, rowNum);label = new Label(0, rowNum, " ");sheet.addCell(label);rowNum++;sheet.setRowView(rowNum, 300); wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.CENTRE); //垂直居中wcfF.setBorder(Border.BOTTOM, BorderLineStyle.THIN,Colour.BLACK); //设置底部边框颜色sheet.mergeCells(0, rowNum, 1, rowNum);label = new Label(0, rowNum, "Receiver",wcfF);sheet.addCell(label);label = new Label(2, rowNum, " ");sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.CENTRE); //垂直居中wcfF.setBorder(Border.BOTTOM, BorderLineStyle.THIN,Colour.BLACK); //设置底部边框颜色sheet.mergeCells(3, rowNum, 5, rowNum);label = new Label(3, rowNum, "Sender",wcfF);sheet.addCell(label);rowNum++;sheet.setRowView(rowNum, 300); wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.RIGHT);//居右label = new Label(0, rowNum, "Contact Person:",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.LEFT);//居右label = new Label(1, rowNum, sci.getCustCname(),wcfF);sheet.addCell(label);label = new Label(2, rowNum, " ");sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.RIGHT);//居右label = new Label(3, rowNum, "Company name:",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.LEFT);//居右sheet.mergeCells(4, rowNum, 5, rowNum);label = new Label(4, rowNum, sci.getSuppName(),wcfF);sheet.addCell(label);rowNum++;sheet.setRowView(rowNum, 300); wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.RIGHT);//居右label = new Label(0, rowNum, "Delivery address:",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.LEFT);//居右label = new Label(1, rowNum, sci.getCustAddress(),wcfF);sheet.addCell(label);label = new Label(2, rowNum, " ");sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.RIGHT);//居右label = new Label(3, rowNum, "Sender address:",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.LEFT);//居右sheet.mergeCells(4, rowNum, 5, rowNum);label = new Label(4, rowNum, sci.getSuppAddress(),wcfF);sheet.addCell(label);rowNum++;sheet.setRowView(rowNum, 300); wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.RIGHT);//居右label = new Label(0, rowNum, "Postcode/Zip Code:",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.LEFT);//居右label = new Label(1, rowNum, sci.getCustPostcode(),wcfF);sheet.addCell(label);label = new Label(2, rowNum, " ");sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.RIGHT);//居右label = new Label(3, rowNum, "Postcode/Zip Code:",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.LEFT);//居右sheet.mergeCells(4, rowNum, 5, rowNum);label = new Label(4, rowNum, sci.getSuppPostcode(),wcfF);sheet.addCell(label);rowNum++;sheet.setRowView(rowNum, 300); wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.RIGHT);//居右label = new Label(0, rowNum, "Country:",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.LEFT);//居右label = new Label(1, rowNum, sci.getCustCountry(),wcfF);sheet.addCell(label);label = new Label(2, rowNum, " ");sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.RIGHT);//居右label = new Label(3, rowNum, "Country:",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.LEFT);//居右sheet.mergeCells(4, rowNum, 5, rowNum);label = new Label(4, rowNum, sci.getSuppCountry(),wcfF);sheet.addCell(label);rowNum++;sheet.setRowView(rowNum, 300); wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.RIGHT);//居右label = new Label(0, rowNum, "Tel:",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.LEFT);//居右label = new Label(1, rowNum, sci.getCustTel(),wcfF);sheet.addCell(label);label = new Label(2, rowNum, " ");sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.RIGHT);//居右label = new Label(3, rowNum, "Contact Person:",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.LEFT);//居右sheet.mergeCells(4, rowNum, 5, rowNum);label = new Label(4, rowNum, sci.getSuppCname(),wcfF);sheet.addCell(label);rowNum++;sheet.setRowView(rowNum, 300); wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.RIGHT);//居右sheet.mergeCells(0, rowNum, 3, rowNum);label = new Label(0, rowNum, "Tel:",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.LEFT);//居右sheet.mergeCells(4, rowNum, 5, rowNum);label = new Label(4, rowNum, sci.getSuppTel(),wcfF);sheet.addCell(label);rowNum++;sheet.setRowView(rowNum, 300); sheet.mergeCells(0, rowNum, 5, rowNum);label = new Label(0, rowNum, " ");sheet.addCell(label);rowNum++;sheet.setRowView(rowNum, 300); wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.CENTRE); //垂直居中wcfF.setBorder(Border.BOTTOM, BorderLineStyle.THIN,Colour.BLACK); //设置底部边框颜色sheet.mergeCells(0, rowNum, 5, rowNum);label = new Label(0, rowNum, " ",wcfF);sheet.addCell(label);rowNum++;sheet.setRowView(rowNum, 300); wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.CENTRE); //垂直居中wcfF.setBorder(Border.BOTTOM, BorderLineStyle.THIN,Colour.BLACK); //设置底部边框颜色wcfF.setBorder(Border.RIGHT, BorderLineStyle.THIN,Colour.BLACK); //设置右边框颜色label = new Label(0, rowNum, "C/NO",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.CENTRE); //垂直居中wcfF.setBorder(Border.BOTTOM, BorderLineStyle.THIN,Colour.BLACK); //设置底部边框颜色wcfF.setBorder(Border.RIGHT, BorderLineStyle.THIN,Colour.BLACK); //设置右边框颜色label = new Label(1, rowNum, "Product Name",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.CENTRE); //垂直居中wcfF.setBorder(Border.BOTTOM, BorderLineStyle.THIN,Colour.BLACK); //设置底部边框颜色wcfF.setBorder(Border.RIGHT, BorderLineStyle.THIN,Colour.BLACK); //设置右边框颜色label = new Label(2, rowNum, "Picture",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.CENTRE); //垂直居中wcfF.setBorder(Border.BOTTOM, BorderLineStyle.THIN,Colour.BLACK); //设置底部边框颜色wcfF.setBorder(Border.RIGHT, BorderLineStyle.THIN,Colour.BLACK); //设置右边框颜色label = new Label(3, rowNum, "Unit Price",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.CENTRE); //垂直居中wcfF.setBorder(Border.BOTTOM, BorderLineStyle.THIN,Colour.BLACK); //设置底部边框颜色wcfF.setBorder(Border.RIGHT, BorderLineStyle.THIN,Colour.BLACK); //设置右边框颜色label = new Label(4, rowNum, "Quantity",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.CENTRE); //垂直居中wcfF.setBorder(Border.BOTTOM, BorderLineStyle.THIN,Colour.BLACK); //设置底部边框颜色wcfF.setBorder(Border.RIGHT, BorderLineStyle.THIN,Colour.BLACK); //设置右边框颜色label = new Label(5, rowNum, "Amount",wcfF);sheet.addCell(label);SellCIList scilist = null;ProductBase pb = null;for (int i = 0; i < list.size(); i++) {scilist = (SellCIList) list.get(i);rowNum++;sheet.setRowView(rowNum, 300); wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.CENTRE); //垂直居中wcfF.setBorder(Border.BOTTOM, BorderLineStyle.THIN,Colour.BLACK); //设置底部边框颜色wcfF.setBorder(Border.RIGHT, BorderLineStyle.THIN,Colour.BLACK); //设置右边框颜色label = new Label(0, rowNum, (i+1)+"",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.CENTRE); //垂直居中wcfF.setBorder(Border.BOTTOM, BorderLineStyle.THIN,Colour.BLACK); //设置底部边框颜色wcfF.setBorder(Border.RIGHT, BorderLineStyle.THIN,Colour.BLACK); //设置右边框颜色label = new Label(1, rowNum, scilist.getPname(),wcfF);sheet.addCell(label);pb = (ProductBase) sess.get(ProductBase.class, scilist.getPid()+"");if(pb.getPics() != null && !"".equals(pb.getPics())) {wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.CENTRE); //垂直居中wcfF.setBorder(Border.BOTTOM, BorderLineStyle.THIN,Colour.BLACK); //设置底部边框颜色wcfF.setBorder(Border.RIGHT, BorderLineStyle.THIN,Colour.BLACK); //设置右边框颜色label = new Label(2, rowNum, "",wcfF);picpath = request.getRealPath("attach\\"+pb.getPics());file=new File(picpath);image=new WritableImage(2, rowNum, 0.9, 0.9, file);sheet.addImage(image);} else {wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.CENTRE); //垂直居中wcfF.setBorder(Border.BOTTOM, BorderLineStyle.THIN,Colour.BLACK); //设置底部边框颜色label = new Label(2, rowNum, "无",wcfF);sheet.addCell(label);}wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.CENTRE); //垂直居中wcfF.setBorder(Border.LEFT, BorderLineStyle.THIN,Colour.BLACK);wcfF.setBorder(Border.BOTTOM, BorderLineStyle.THIN,Colour.BLACK); //设置底部边框颜色wcfF.setBorder(Border.RIGHT, BorderLineStyle.THIN,Colour.BLACK); //设置右边框颜色label = new Label(3, rowNum, scilist.getPrice()+"",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.CENTRE); //垂直居中wcfF.setBorder(Border.BOTTOM, BorderLineStyle.THIN,Colour.BLACK); //设置底部边框颜色wcfF.setBorder(Border.RIGHT, BorderLineStyle.THIN,Colour.BLACK); //设置右边框颜色label = new Label(4, rowNum, scilist.getQty()+"",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.CENTRE); //垂直居中wcfF.setBorder(Border.BOTTOM, BorderLineStyle.THIN,Colour.BLACK); //设置底部边框颜色wcfF.setBorder(Border.RIGHT, BorderLineStyle.THIN,Colour.BLACK); //设置右边框颜色label = new Label(5, rowNum, scilist.getAmount()+"",wcfF);sheet.addCell(label);}rowNum++;sheet.setRowView(rowNum, 300); wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.RIGHT);//居右sheet.mergeCells(0, rowNum, 4, rowNum);label = new Label(0, rowNum, "Shipping fee by DHL:",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.LEFT);//居右label = new Label(5, rowNum, sci.getExpressName(),wcfF);sheet.addCell(label);rowNum++;sheet.setRowView(rowNum, 300); wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.RIGHT);//居右sheet.mergeCells(0, rowNum, 4, rowNum);label = new Label(0, rowNum, "Operating Charge:",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.LEFT);//居右label = new Label(5, rowNum, sci.getFreight()+"",wcfF);sheet.addCell(label);rowNum++;sheet.setRowView(rowNum, 300); wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.RIGHT);//居右sheet.mergeCells(0, rowNum, 4, rowNum);label = new Label(0, rowNum, "Total:",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.LEFT);//居右label = new Label(5, rowNum, sci.getTotal()+"",wcfF);sheet.addCell(label);rowNum++;sheet.setRowView(rowNum, 300); sheet.mergeCells(0, rowNum, 5, rowNum);label = new Label(0, rowNum, " ");sheet.addCell(label);rowNum++;sheet.setRowView(rowNum, 300); wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.RIGHT);//居右label = new Label(0, rowNum, "Payment:",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.LEFT);//居右sheet.mergeCells(1, rowNum, 5, rowNum);label = new Label(1, rowNum, sci.getPayWay(),wcfF);sheet.addCell(label);rowNum++;sheet.setRowView(rowNum, 300); wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.RIGHT);//居右label = new Label(0, rowNum, "BANK:",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.LEFT);//居右sheet.mergeCells(1, rowNum, 5, rowNum);label = new Label(1, rowNum, sci.getBankname(),wcfF);sheet.addCell(label);rowNum++;sheet.setRowView(rowNum, 300); wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.RIGHT);//居右label = new Label(0, rowNum, "ACOUNTNAME:",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.LEFT);//居右sheet.mergeCells(1, rowNum, 5, rowNum);label = new Label(1, rowNum, sci.getAccountName(),wcfF);sheet.addCell(label);rowNum++;sheet.setRowView(rowNum, 300); wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.RIGHT);//居右label = new Label(0, rowNum, "ACCOUNTNUMBER:",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.LEFT);//居右sheet.mergeCells(1, rowNum, 5, rowNum);label = new Label(1, rowNum, sci.getAccountNo(),wcfF);sheet.addCell(label);rowNum++;sheet.setRowView(rowNum, 300); wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.RIGHT);//居右label = new Label(0, rowNum, "SWIFTCODE:",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.LEFT);//居右sheet.mergeCells(1, rowNum, 5, rowNum);label = new Label(1, rowNum, sci.getSwiftCode(),wcfF);sheet.addCell(label);rowNum++;sheet.setRowView(rowNum, 300); wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.RIGHT);//居右label = new Label(0, rowNum, "BANKADDRESS:",wcfF);sheet.addCell(label);wf = new WritableFont(WritableFont.createFont("Calibri"), 12, WritableFont.NO_BOLD, false);wcfF = new WritableCellFormat(wf);wcfF.setWrap(true);//自动换行wcfF.setBackground(Colour.WHITE);// 设置单元格的背景颜色wcfF.setAlignment(jxl.format.Alignment.LEFT);//居右sheet.mergeCells(1, rowNum, 5, rowNum);label = new Label(1, rowNum, sci.getBankAddress(),wcfF);sheet.addCell(label);tran.commit();try {book.write();book.close();} catch (IOException ex) {ex.printStackTrace();}

中间有些操作数据库的可能要改改！  只是一些代码片段！