JAVA的poi实现模版导出excel(带图片).doc

下面是本人使用java的poi实现使用模板到处excel，内容包含图片，使用两种不同的方式实现其到处excel。但是使用jxl实现到处excel只能到处png格式。package com.rxc.servlet;import java.awt.image.BufferedImage;import java.io.BufferedInputStream;import java.io.BufferedOutputStream;import java.io.ByteArrayOutputStream;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import javax.imageio.ImageIO;import javax.servlet.ServletException;import javax.servlet.ServletOutputStream;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFClientAnchor;import org.apache.poi.hssf.usermodel.HSSFPatriarch;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.poifs.filesystem.POIFSFileSystem;public class OutExcelServlet extends HttpServlet private static final long serialVersionUID = 1L;public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException this.doPost(request, response);public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException String accident_id = request.getParameter(accident_id);String OnputimagePath = null;/*使用poi实现excel模版 导出excel*/try POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(d:设备异常分析报告.xls);HSSFWorkbook wb = new HSSFWorkbook(fs);HSSFSheet sheet = wb.getSheet(设备异常分析报告);HSSFRow row = sheet.getRow(2);HSSFCell cell = row.getCell(short) 2);if (cell = null)cell = row.createCell(short) 2);cell.setCellType(HSSFCell.CELL_TYPE_STRING);cell.setCellValue(12321212);ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();String InputimagePath = d:1012051443548604211337adf6.jpg; BufferedImage bufferImg = ImageIO.read(new File(InputimagePath);ImageIO.write(bufferImg,JPG,byteArrayOut);/设置图片大小，位置HSSFClientAnchor anchor = new HSSFClientAnchor(5,0,500,122,(short) 0, 5,(short)10,15); /创建HSSFPatriarch patri = sheet.createDrawingPatriarch();patri.createPicture(anchor ,wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG);/ 输出文件OnputimagePath = d:test1.xls;FileOutputStream fileOut = new FileOutputStream(OnputimagePath);wb.write(fileOut);fileOut.close(); catch (Exception e) e.printStackTrace(); / 检查文件是否存在 File obj = new File(OnputimagePath); if (!obj.exists() response.setContentType(text/html;charset=GBK); response.getWriter().print(指定文件不存在！); return; / 读取文件名：用于设置客户端保存时指定默认文件名 int index = OnputimagePath.lastIndexOf(); / 前提：传入的path字符串以“”表示目录分隔符 String fileName = OnputimagePath.substring(index + 1); / 写流文件到前端浏览器 ServletOutputStream out = response.getOutputStream(); response.setHeader(Content-disposition, attachment;filename= + fileName); BufferedInputStream bis = null; BufferedOutputStream bos = null; try bis = new BufferedInputStream(new FileInputStream(OnputimagePath); bos = new BufferedOutputStream(out); byte buff = new byte2048; int bytesRead; while (-1 != (bytesRead = bis.read(buff, 0, buff.length) bos.write(buff, 0, bytesRead); catch (IOException e) throw e; finally if (bis != null) bis.close(); if (bos != null) bos.close(); /*使用jxl实现excel模版 导出excel 但导出图片仅支持png格式* String realpath = D:/test.xls; try CopeDate data = new CopeDate(); MapList mp = data.getdata(accident_id); /选择模板文件： Workbook wb = Workbook.getWorkbook(new File(realpath); /第二步：通过模板得到一个可写的Workbook：第一个参数是一个输出流对象,第二个参数代表了要读取的模板 File targetFile = new File(D:/test1.xls); WritableWorkbook wwb = Workbook.createWorkbook(targetFile, wb); /第三步：选择模板中名称为设备异常的Sheet： WritableSheet wws = wwb.getSheet(JustCell); /第四步：选择单元格，写入动态值，根据单元格的不同类型转换成相应类型的单元格： Label A1 = new Label(0, 1,mp.getRow(0).get(accident_name); wws.addCell(A1); /插入图片 File imgFile = new File(D:/test.png); WritableImage image = new WritableImage(2, 1, 8, 20, imgFile); wws.addImage(image); wwb.write(); wwb.close(); wb.close(); catch (BiffException e) /