orders 导出 excel表格的操作
OrdersAction 中的代码
public String exportExcelOrders() {
// 根据条件查询获取总记录数
if (whereSql == null || "undefined".equals(whereSql)) {
whereSql = "";
} else {
try {
whereSql = new String(whereSql.getBytes("ISO8859-1"), "UTF-8");
} catch (UnsupportedEncodingException e) {
System.out.println("*************************字符转换异常处理");
e.printStackTrace();
}
}
// 查询总记录
int total = ordersService.getCount(Orders.class, whereSql);
// 获取分页对象
this.pagination = new Pagination(this.getPage(), total, this.getRows());
// 得到导出的execl的数据
List datas = ordersService.getObjects(Orders.class,
this.pagination.getRowFrom(), this.pagination.getRowTo(),
whereSql, "");
// 导出execl操作
// 创建流对象
ByteArrayOutputStream os = new ByteArrayOutputStream();
// 创建excel对象
WritableWorkbook wbook;
try {
wbook = Workbook.createWorkbook(os);
// 标题
String tmptitle = "公告列表";
// sheet名称
WritableSheet wsheet = wbook.createSheet(tmptitle, 0);
// 设置excel中字体的样式
WritableFont wfont = new WritableFont(WritableFont.ARIAL, 16,
WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
Colour.RED);
// 单元格对象
WritableCellFormat wcfFC = new WritableCellFormat(wfont);
// wcfFC.setBackground(Colour.AQUA);
// 设置排列方式
wcfFC.setAlignment(Alignment.CENTRE);
// 设置单元的lable
wsheet.addCell(new Label(1, 0, tmptitle, wcfFC));
// 设置字体样式
wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 14,
WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
Colour.BLACK);
// 单元格设置样式
wcfFC = new WritableCellFormat(wfont);
wsheet.addCell(new Label(0, 2, "序号"));
wsheet.addCell(new Label(1, 2, "订单时间"));
wsheet.addCell(new Label(2, 2, "订单金额"));
wsheet.addCell(new Label(3, 2, "送货方式"));
wsheet.addCell(new Label(4, 2, "订单状态"));
wsheet.addCell(new Label(5, 2, "订单数量"));
wsheet.addCell(new Label(6, 2, "客户"));
wsheet.addCell(new Label(7, 2, "送货地址"));
for (int i = 0; i < datas.size(); i++) {
Orders entity = (Orders) datas.get(i);
wsheet.addCell(new Label(0, i + 3, entity.getId() + ""));
if (entity.getTime() != null) {
SimpleDateFormat sdf = new SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss");
String timeStr = sdf.format(entity.getTime());
wsheet.addCell(new Label(1, i + 3, timeStr));
} else {
wsheet.addCell(new Label(1, i + 3, ""));
}
wsheet.addCell(new Label(2, i + 3, entity.getSum() + ""));
wsheet.addCell(new Label(3, i + 3, entity.getSendMode() + ""));
if (entity.getStatus() == null) {
wsheet.addCell(new Label(4, i + 3, ""));
} else if (entity.getStatus() == 0) {
wsheet.addCell(new Label(4, i + 3, "未支付,未发货"));
} else if (entity.getStatus() == 1) {
wsheet.addCell(new Label(4, i + 3, "已支付,未发货"));
} else if (entity.getStatus() == 2) {
wsheet.addCell(new Label(4, i + 3, "未支付,已发货"));
} else if (entity.getStatus() == 3) {
wsheet.addCell(new Label(4, i + 3, "已支付,未发货"));
} else if (entity.getStatus() == 4) {
wsheet.addCell(new Label(4, i + 3, "交易成功"));
}
wsheet.addCell(new Label(5, i + 3, entity.getOrderNum() + ""));
if (entity.getCustomers() == null) {
wsheet.addCell(new Label(6, i + 3, ""));
} else {
wsheet.addCell(new Label(6, i + 3, entity.getCustomers()
.getId() + ""));
}
if (entity.getSendAddress() == null) {
wsheet.addCell(new Label(7, i + 3, ""));
} else {
wsheet.addCell(new Label(7, i + 3, entity.getSendAddress()
.getId() + ""));
}
}
wbook.write(); // 写入文件
wbook.close();
os.close(); // 关闭流
} catch (Exception e) {
e.printStackTrace();
}
fileName = "orders";
excelStream = new ByteArrayInputStream(os.toByteArray());
return SUCCESS;
}
initOrders中触发按钮的事件
'-',
{
id : 'btnsave',
text : '导出excel表格',
iconCls : 'icon-save',
disable : false,
handler : function() {
var whereSql = $(
"#ordersList")
.datagrid(
"options").queryParams.whereSql;
var pageOption = $(
"#ordersList")
.datagrid(
"getPager")
.pagination(
"options");
alert(whereSql);
document.location.href = encodeURI("../../csdn/exportExcelOrders.action?page="
+ pageOption.pageNumber
+ "&rows="
+ pageOption.pageSize
+ "&whereSql="
+ whereSql);
}
shruts-orders中的代码
<!--导出excel表格-->
<action name="exportExcelOrders" class="cn.csdn.hr.buy360.action.OrdersAction" method="exportExcelOrders">
<result type="stream" name="success">
<param name="contentType">application/vnd.ms-excel</param>
<param name="InputName">excelStream</param>
<param name="contentDisposition">attachment;filename="${fileName}.xls"</param>
<param name="bufferSize">1024</param>
</result>
</action>
OrdersAction 中的代码
public String exportExcelOrders() {
// 根据条件查询获取总记录数
if (whereSql == null || "undefined".equals(whereSql)) {
whereSql = "";
} else {
try {
whereSql = new String(whereSql.getBytes("ISO8859-1"), "UTF-8");
} catch (UnsupportedEncodingException e) {
System.out.println("*************************字符转换异常处理");
e.printStackTrace();
}
}
// 查询总记录
int total = ordersService.getCount(Orders.class, whereSql);
// 获取分页对象
this.pagination = new Pagination(this.getPage(), total, this.getRows());
// 得到导出的execl的数据
List datas = ordersService.getObjects(Orders.class,
this.pagination.getRowFrom(), this.pagination.getRowTo(),
whereSql, "");
// 导出execl操作
// 创建流对象
ByteArrayOutputStream os = new ByteArrayOutputStream();
// 创建excel对象
WritableWorkbook wbook;
try {
wbook = Workbook.createWorkbook(os);
// 标题
String tmptitle = "公告列表";
// sheet名称
WritableSheet wsheet = wbook.createSheet(tmptitle, 0);
// 设置excel中字体的样式
WritableFont wfont = new WritableFont(WritableFont.ARIAL, 16,
WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
Colour.RED);
// 单元格对象
WritableCellFormat wcfFC = new WritableCellFormat(wfont);
// wcfFC.setBackground(Colour.AQUA);
// 设置排列方式
wcfFC.setAlignment(Alignment.CENTRE);
// 设置单元的lable
wsheet.addCell(new Label(1, 0, tmptitle, wcfFC));
// 设置字体样式
wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 14,
WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
Colour.BLACK);
// 单元格设置样式
wcfFC = new WritableCellFormat(wfont);
wsheet.addCell(new Label(0, 2, "序号"));
wsheet.addCell(new Label(1, 2, "订单时间"));
wsheet.addCell(new Label(2, 2, "订单金额"));
wsheet.addCell(new Label(3, 2, "送货方式"));
wsheet.addCell(new Label(4, 2, "订单状态"));
wsheet.addCell(new Label(5, 2, "订单数量"));
wsheet.addCell(new Label(6, 2, "客户"));
wsheet.addCell(new Label(7, 2, "送货地址"));
for (int i = 0; i < datas.size(); i++) {
Orders entity = (Orders) datas.get(i);
wsheet.addCell(new Label(0, i + 3, entity.getId() + ""));
if (entity.getTime() != null) {
SimpleDateFormat sdf = new SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss");
String timeStr = sdf.format(entity.getTime());
wsheet.addCell(new Label(1, i + 3, timeStr));
} else {
wsheet.addCell(new Label(1, i + 3, ""));
}
wsheet.addCell(new Label(2, i + 3, entity.getSum() + ""));
wsheet.addCell(new Label(3, i + 3, entity.getSendMode() + ""));
if (entity.getStatus() == null) {
wsheet.addCell(new Label(4, i + 3, ""));
} else if (entity.getStatus() == 0) {
wsheet.addCell(new Label(4, i + 3, "未支付,未发货"));
} else if (entity.getStatus() == 1) {
wsheet.addCell(new Label(4, i + 3, "已支付,未发货"));
} else if (entity.getStatus() == 2) {
wsheet.addCell(new Label(4, i + 3, "未支付,已发货"));
} else if (entity.getStatus() == 3) {
wsheet.addCell(new Label(4, i + 3, "已支付,未发货"));
} else if (entity.getStatus() == 4) {
wsheet.addCell(new Label(4, i + 3, "交易成功"));
}
wsheet.addCell(new Label(5, i + 3, entity.getOrderNum() + ""));
if (entity.getCustomers() == null) {
wsheet.addCell(new Label(6, i + 3, ""));
} else {
wsheet.addCell(new Label(6, i + 3, entity.getCustomers()
.getId() + ""));
}
if (entity.getSendAddress() == null) {
wsheet.addCell(new Label(7, i + 3, ""));
} else {
wsheet.addCell(new Label(7, i + 3, entity.getSendAddress()
.getId() + ""));
}
}
wbook.write(); // 写入文件
wbook.close();
os.close(); // 关闭流
} catch (Exception e) {
e.printStackTrace();
}
fileName = "orders";
excelStream = new ByteArrayInputStream(os.toByteArray());
return SUCCESS;
}
initOrders中触发按钮的事件
'-',
{
id : 'btnsave',
text : '导出excel表格',
iconCls : 'icon-save',
disable : false,
handler : function() {
var whereSql = $(
"#ordersList")
.datagrid(
"options").queryParams.whereSql;
var pageOption = $(
"#ordersList")
.datagrid(
"getPager")
.pagination(
"options");
alert(whereSql);
document.location.href = encodeURI("../../csdn/exportExcelOrders.action?page="
+ pageOption.pageNumber
+ "&rows="
+ pageOption.pageSize
+ "&whereSql="
+ whereSql);
}
shruts-orders中的代码
<!--导出excel表格-->
<action name="exportExcelOrders" class="cn.csdn.hr.buy360.action.OrdersAction" method="exportExcelOrders">
<result type="stream" name="success">
<param name="contentType">application/vnd.ms-excel</param>
<param name="InputName">excelStream</param>
<param name="contentDisposition">attachment;filename="${fileName}.xls"</param>
<param name="bufferSize">1024</param>
</result>
</action>