SSH中用apache的jxl.jar导入导出Excel表格

一.在SSH中导入生Excel表格的依赖包Jxl.jar 二.在service层创建接口IExcelService package com.crms.service;

import java.io.InputStream; import java.util.List; /** *  struts2导出Excel * @author Administrator * */ public interface IExcelService {   public InputStream getExcelInputStream(List<Object[]> objList);     }

三.创建接口的实现类 package com.crms.service.impl;

import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.InputStream; import java.io.OutputStream; import java.util.List; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.struts2.ServletActionContext; import jxl.Workbook; import jxl.format.Alignment; import jxl.format.UnderlineStyle; import jxl.format.VerticalAlignment; import jxl.write.Label; import jxl.write.WritableCellFormat; import jxl.write.WritableFont; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import jxl.write.WriteException; import jxl.write.biff.RowsExceededException; import com.crms.service.IExcelService;

/** * 写入Excel文件 * * 用來生成一个Excel文件   *    * @author Administrator * */ public class ExcelServiceImpl implements IExcelService { // 定义request ,response. HttpServletResponse response = ServletActionContext.getResponse(); HttpServletRequest request = ServletActionContext.getRequest(); /** * 将OutputStream转化为InputStream */ public InputStream getExcelInputStream(List<Object[]> dataList) { ByteArrayOutputStream out = new ByteArrayOutputStream(); putDataOnOutputStream(out,dataList); return new ByteArrayInputStream(out.toByteArray()); }

/** * 生成数据表(Excel) */ private void putDataOnOutputStream(OutputStream os,List<Object[]> dataList) {

//构造Workbook（工作薄）对象    WritableWorkbook workbook=null; //创建工作表 WritableSheet ws=null; try { //首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象 workbook = Workbook.createWorkbook(os); /*              * 创建一个可写入的工作表               * Workbook的createSheet方法有两个参数，              * 第一个是工作表的名称，第二个是工作表在工作薄中的位置              * 生成名为"第一页"的工作表，参数0表示这是第一页              */ ws= workbook.createSheet("offerUp", 0); //创建列名 // 定义格式, 字体, 下划线, 斜体, 粗体, 颜色 WritableFont wf = new WritableFont(WritableFont.ARIAL, 10,     WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); // 创建格式化对象实例    WritableCellFormat totalx2Format = new WritableCellFormat(wf);    // 垂直居中    totalx2Format.setVerticalAlignment(VerticalAlignment.CENTRE);    // 水平居中    totalx2Format.setAlignment(Alignment.CENTRE); // 设置标题.ws.addCell(new jxl.write.Label(列(从0开始), 行(从1开始), 内容.)); ws.addCell(new Label(0, 0, "编号",totalx2Format)); ws.addCell(new Label(1, 0, "客户名称",totalx2Format)); ws.addCell(new Label(2, 0, "订单金额（元）",totalx2Format)); // 合并单元格，参数格式（开始列，开始行，结束列，结束行） ws.mergeCells(0, 0, 0, 0); ws.mergeCells(1, 0, 1, 0); ws.mergeCells(2, 0, 2, 0); // 设置单元格的宽度 ws.setColumnView(0, 10); ws.setColumnView(1, 40); ws.setColumnView(2, 20);

//添加数据 if (null!=workbook) {            //下面开始添加单元格数据                for(int i=0;i<dataList.size();i++){                    for(int j=0;j<dataList.get(0).length;j++){                        //这里需要注意的是，在Excel中，第一个参数表示列，第二个表示行                        Label labelC = new Label(j, i+1,dataList.get(i)[j].toString(),totalx2Format);                        try {                            //将生成的单元格添加到工作表中                        ws.addCell(labelC);                        } catch (RowsExceededException e) {                            e.printStackTrace();                        } catch (WriteException e) {                            e.printStackTrace();                        }                    }                }    }     //从内存中写入文件中   workbook.write();                   //关闭资源，释放内存    workbook.close();    } catch (Exception e) { e.printStackTrace(); } } } 四.Action层将数据库的数据传给Excel,并返回结果 package com.crms.web.action;

import java.io.InputStream; import java.util.ArrayList; import java.util.List;

import com.crms.biz.StatisticsBiz; import com.crms.pojo.Customerinfo; import com.crms.pojo.msms.Orderdetail; import com.crms.pojo.msms.Orders; import com.crms.service.IExcelService; import com.crms.service.impl.ExcelServiceImpl; /** * struts2导出Excel * @author Administrator * */

public class ExcelAction { private StatisticsBiz statisticsBiz; private List<Orderdetail> orderdetailList; private Customerinfo customerinfo; private ExcelServiceImpl excelServiceImpl=new ExcelServiceImpl();

public StatisticsBiz getStatisticsBiz() { return statisticsBiz; }

public void setStatisticsBiz(StatisticsBiz statisticsBiz) { this.statisticsBiz = statisticsBiz; }

public List<Orderdetail> getOrderdetailList() { return orderdetailList; }

public void setOrderdetailList(List<Orderdetail> orderdetailList) { this.orderdetailList = orderdetailList; }

public IExcelService getExcelService() { return excelService; } public void setExcelService(IExcelService excelService) { this.excelService = excelService; }

public ExcelServiceImpl getExcelServiceImpl() { return excelServiceImpl; }

public void setExcelServiceImpl(ExcelServiceImpl excelServiceImpl) { this.excelServiceImpl = excelServiceImpl; }

//+++++++++++++++++++++++++++ private InputStream excelStream; private IExcelService excelService = new ExcelServiceImpl();

public InputStream getExcelStream() { return excelStream; }

public void setExcelStream(InputStream excelStream) { this.excelStream = excelStream; }

public String execute() { String customerId = null; List<Object[]> objectList = new ArrayList<Object[]>(); try { orderdetailList = statisticsBiz.getOrderdetailService().queryAll(); Integer i = 0; for (Orderdetail orderdetail_2 : orderdetailList) { customerId = ((Orders) (orderdetail_2.getOrders())).getCustomer_id(); customerinfo = statisticsBiz.getCustomerinfoService().queryById(customerId); ((Orders) (orderdetailList.get(i).getOrders())).setCustomer_id(customerinfo.getCustomerName()); Object [] object={i+1,customerinfo.getCustomerName(),orderdetail_2.getSub_price()}; objectList.add(object); i++; }

} catch (Exception e) { e.printStackTrace(); } excelStream = excelService.getExcelInputStream(objectList); return "excel"; } } 五.Struts2配置文件 <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE struts PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 2.1//EN" "http://struts.apache.org/dtds/struts-2.1.dtd"> <!--  struts2导出Excel --> <struts> <package name="excel" namespace="/export2excel"  extends="struts-default"> <action name="excel" class="com.crms.web.action.ExcelAction"> <result name="excel_*" type="stream"> <param name="contentType"> application/vnd.ms-excel               </param> <param name="inputName">excelStream</param> <param name="contentDisposition"> filename="{1}.xls"               </param> <param name="bufferSize">1024</param> </result> </action>   </package>   </struts>