jxl.jar导入导出Excel表格

//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>