struts2 配合jaxl 制作Excel模板做的报表[云少原创]（有了它就不需要每一个单元格都要一个一个敲代码了哈哈）

一早上，刚刚把第二张表格模板做好了，我感觉这2天的辛苦没有白费，现在要是想做一个导出报表的事情只需要从数据库里面拿出数据然后将他们组装成一个DataModel这个对象就可以了，我一边说一边把代码拷贝出来，最后有时间我把整个jar包也拷贝出来，里面还有很多地方没有完善，希望各位能提出修改意见，或者是自己做一个哈哈，方便以后做Excel报表呵呵。

public class DataModel {private String column;  //列private String data;//数据private String row;//行public DataModel(String column, String row,String data) {this.column = column;this.data = data;this.row = row;}public String getColumn() {return column;}public String getData() {return data;}public String getRow() {return row;}public void setColumn(String column) {this.column = column;}public void setData(String data) {this.data = data;}public void setRow(String row) {this.row = row;}}

犹豫我这个里面需要插入图片，所以我准备了几个pojo，大家自己看下就懂了！

//图片坐标public class ImageLocation {private double x;private double y;private double width;private double height;public ImageLocation(double x,double y,double width,double height){this.x=x;this.y=y;this.width=width;this.height=height;}public double getX() {return x;}public void setX(double x) {this.x = x;}public double getY() {return y;}public void setY(double y) {this.y = y;}public double getWidth() {return width;}public void setWidth(double width) {this.width = width;}public double getHeight() {return height;}public void setHeight(double height) {this.height = height;}}

还有一个

ImageModel 

ImageModel 

public class ImageModel {private String oldImageDir; //这个是之前的位置private String newImageDir;//这个是复制以后的位置  ，因为图片只能是png ，所以有可能我们之前储存的是其他格式的文件所以我们将图片复制一份待会删//掉private ImageLocation imageLocation; //图片坐标public ImageLocation getImageLocation() {return imageLocation;}public void setImageLocation(ImageLocation imageLocation) {this.imageLocation = imageLocation;}public String getOldImageDir() {return oldImageDir;}public void setOldImageDir(String oldImageDir) {this.oldImageDir = oldImageDir;}public String getNewImageDir() {return newImageDir;}public void setNewImageDir(String newImageDir) {this.newImageDir = newImageDir;}}

让后我再将后面最要的一个类拿出来，这个类主要是方便Action提出请求后给它返回一个InputString，仔细看下代码吧O(∩_∩)O~

public class ExportExcel implements Export {/** *  */public InputStream createExcel(List<DataModel> fixedDatalist,List<DataModel> movedDataList, String templateFrom, String templateTo,List<ImageModel> imageList) {// TODO Auto-generated method stubByteArrayOutputStream out = new ByteArrayOutputStream();createTemplateWorkBook(out, fixedDatalist, movedDataList, templateFrom,templateTo, imageList);return new ByteArrayInputStream(out.toByteArray());}/** * 配置jxl相关内容 *  * @param out * @param fixedDatalist * @param movedDataList * @param templateFrom * @param templateTo */@SuppressWarnings("static-access")private void createTemplateWorkBook(ByteArrayOutputStream out,List<DataModel> fixedDatalist, List<DataModel> movedDataList,String templateFrom, String templateTo, List<ImageModel> imageList) {// TODO Auto-generated method stubtry {String excelFile = templateFrom;String excelFile2 = templateTo;File file = new File(excelFile);Workbook wb = Workbook.getWorkbook(new FileInputStream(file));WritableWorkbook wwb = wb.createWorkbook(new File(excelFile2), wb);WritableSheet sheet = wwb.getSheet(0);putAllDataOnOutputStream(wwb, sheet, fixedDatalist, movedDataList,imageList);// 导入所有数据FileInputStream in = new FileInputStream(excelFile2);byte[] b = new byte[4096];int n;while ((n = in.read(b)) != -1) {out.write(b, 0, n);}in.close();} catch (Exception e) {e.printStackTrace();}}private void putAllDataOnOutputStream(WritableWorkbook wwb,WritableSheet sheet, List<DataModel> fixedDataList,List<DataModel> movedDataList, List<ImageModel> imageList ) {try {// 调用单元格样式WritableCellFormat writeFontNameCell = cellStyleTemple();// TODO Auto-generated method stubList<File> returnImageList = null;if (fixedDataList!=null) {putFixedDatalist(sheet, fixedDataList, writeFontNameCell);// 输入固定数据}if (movedDataList!=null) {putMovedDatalist(sheet, movedDataList, writeFontNameCell);// 插入不确定数据}if (imageList!=null) {returnImageList = putImageDatalist(sheet, imageList,writeFontNameCell);// 插入图片}wwb.write();wwb.close();if (!imageList.isEmpty()) {for (int i = 0; i < returnImageList.size(); i++) {//returnImageList.get(i).delete();}}} catch (Exception e) {// TODO: handle exceptione.printStackTrace();}}private void putMovedDatalist(WritableSheet sheet, List<DataModel> movedDataList,WritableCellFormat writeFontNameCell) {// TODO Auto-generated method stubtry {Label label = null;for (int i = 0; i < movedDataList.size(); i++) {// 返回值是objectlabel = new Label(Integer.parseInt((movedDataList.get(i).getColumn())), Integer.parseInt(movedDataList.get(i).getRow()), movedDataList.get(i).getData(),writeFontNameCell);sheet.addCell(label);label = null;}} catch (Exception e) {e.printStackTrace();}}/** * 插入图片 * @param sheet * @param imageDir * @param writeFontNameCell * @return * @throws IOException  */private List<File> putImageDatalist(WritableSheet sheet, List<ImageModel> imageList,WritableCellFormat writeFontNameCell) throws IOException {// TODO Auto-generated method stub WritableImage writeableImage=null;String oldImageDir=null;String newImageDir=null;List<File> fileList=new ArrayList<File>();//将图片放到list 里面到前面删掉for (int i = 0; i < imageList.size(); i++) {oldImageDir=imageList.get(i).getOldImageDir();newImageDir=imageList.get(i).getNewImageDir();if (new File(oldImageDir).exists()) {copy(oldImageDir, newImageDir); File newFile= new File(newImageDir); fileList.add(newFile); writeableImage = new WritableImage(imageList.get(i).getImageLocation().getX(), imageList.get(i).getImageLocation().getY(),   imageList.get(i).getImageLocation().getWidth(), imageList.get(i).getImageLocation().getHeight(), newFile); newFile=null; sheet.addImage(writeableImage);}}return fileList;}/** * 复制文件到另一个文件夹 * @param infoDetail * @param evaluationDataList * @return * @throws IOException  */public  void copy(String  oldDir , String  newDir) throws IOException {   FileInputStream in = new FileInputStream(oldDir);OutputStream out = new FileOutputStream(newDir);byte[] buff = new byte[1024];int len = 0;while ((len = in.read(buff)) != -1) {out.write(buff, 0, len);}in.close();out.close();     }   /** * 插入固定数据 *  * @param sheet * @param fixedDataList * @param writeFontNameCell */private void putFixedDatalist(WritableSheet sheet,List<DataModel> fixedDataList, WritableCellFormat writeFontNameCell) {// TODO Auto-generated method stubtry {Label label = null;for (int i = 0; i < fixedDataList.size(); i++) {// 返回值是objectlabel = new Label(Integer.parseInt((fixedDataList.get(i).getColumn())), Integer.parseInt(fixedDataList.get(i).getRow()), fixedDataList.get(i).getData(),writeFontNameCell);sheet.addCell(label);label = null;}} catch (Exception e) {e.printStackTrace();}}/** * 字体，以及单元格样式配置 *  * @return * @throws WriteException */@SuppressWarnings("unused")private WritableCellFormat cellStyleTemple() throws WriteException {WritableFont writeNameFontStyle = new WritableFont(WritableFont.ARIAL,10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);WritableCellFormat writeFontNameCell = new WritableCellFormat(writeNameFontStyle);writeFontNameCell.setBackground(Colour.LIGHT_GREEN);writeFontNameCell.setBorder(Border.ALL, BorderLineStyle.THIN);writeFontNameCell.setAlignment(Alignment.CENTRE);writeFontNameCell.setVerticalAlignment(VerticalAlignment.CENTRE);writeFontNameCell.setWrap(true);return writeFontNameCell;}}

下面要做的就是将自己准备好的数据往里面添加就可以拉。。。come on ！！！

下面的是action的信息，自己看下有没有什么有用的。

HttpServletRequest request = ServletActionContext.getRequest();IOfficerInfoService officerInfoService = new OfficerInfoService();request.setCharacterEncoding("utf-8");String officerNum = request.getParameter("officernum");InfoDetailEntity infoDetail = officerInfoService.getOfficerInfoDetailByOfficerNum(officerNum);List<DataModel> evaluationDataList = new ArrayList<DataModel>();ComprehensiveEvaluationData comprehensiveEvaluationData = new ComprehensiveEvaluationData();evaluationDataList = comprehensiveEvaluationData.getFixedEvaluationDataModel(infoDetail, evaluationDataList);Export export = new ExportExcel();//配置模板信息String templateFrom=ServletActionContext.getServletContext().getRealPath("")+ "/excel/evaluationTemple1.xls";String templateTo=ServletActionContext.getServletContext().getRealPath("")+ "/excel/evaluationTemple2.xls";//照片的地址配置String oldDir=ServletActionContext.getServletContext().getRealPath("")+ "\\picture\\"+infoDetail.getOfficerinfolist().get(0).getOfficerImage();String newDir=ServletActionContext.getServletContext().getRealPath("")+ "\\picture\\temp\\"+infoDetail.getOfficerinfolist().get(0).getOfficerImage().substring(0,infoDetail.getOfficerinfolist().get(0).getOfficerImage().indexOf(".jpg"))+".png";//配置图片信息List<ImageModel> imageList=new ArrayList<ImageModel>();ImageModel image=new ImageModel();image.setImageLocation(new ImageLocation(9,1,3,8));image.setNewImageDir(newDir);image.setOldImageDir(oldDir);imageList.add(image);//配置好的动态数据List<DataModel> movedData = new ArrayList<DataModel>();movedData=comprehensiveEvaluationData.getMovedEvalutionDataModel(infoDetail, movedData);excelStream=export.createExcel(evaluationDataList, movedData, templateFrom, templateTo, imageList);downFileName = officerNum + ".xls";return "success";

下面贴一下模板和下载后的照片。。

里面有些信息不能拿出来见谅

刚毕业的菜鸟写的  希望大家见谅。。。。希望大家给提点意见也是可以的谢谢！！      下次有时间把他们做成一个jar包 ，然后将路径改成配置文件就万事大吉了吧 。。。还有什么需要改的请大家告诉我下哈 。。。没事下次搞个工作流程，最重要是看源码。。。