1.
查找出List對象2.
BusPriceExportCommand
package com.icicle.goldenfly.web.admin.busAdmin;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.util.Calendar;
import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import com.easymvc.ActionException;
import com.easymvc.ReturnableCommand;
import com.easymvc.annotation.In;
import com.easymvc.annotation.Out;
import com.easymvc.dispatcher.CommandDispatcher;
import com.easymvc.dispatcher.Dispatcher;
import com.easymvc.dispatcher.DownloadDispatcher;
import com.icicle.framework.member.client.criteria.OrderBy;
import com.icicle.framework.member.client.criteria.SetRestriction;
import com.icicle.framework.member.client.interfaces.ContentManager;
import com.icicle.framework.order.client.enums.BusTicketSource;
import com.icicle.framework.order.client.models.BusTicketPrice;
import com.icicle.goldenfly.bus.client.criteria.BusTicketPriceCriteria;
import com.icicle.goldenfly.member.client.ClientManagerFactory;
import com.icicle.goldenfly.web.order.report.ExportBusTicketPriceUtil;
/**
* 下載 車票價格數據 command
* @author James pu
*
*/
public class BusPriceExportCommand extends ReturnableCommand{
/**
*
*/
private static final long serialVersionUID = 1705270988040058854L;
private static final Logger logger = Logger.getLogger(BusPriceExportCommand.class);
@In(init=false)
private String source;//來源
@In(init=false)
private String fromBusStop;//起始站點
@In(init=false)
private String toBusStop;//目的站點
private InputStream exportStream;//輸出流
/**
* 接口管理類
*/
private ContentManager manager;
public BusPriceExportCommand(){
manager = ClientManagerFactory.getInstance().getContentManager();
}
@Override
protected String execute2() throws ActionException {
try{
//查詢條件
BusTicketPriceCriteria criteria=new BusTicketPriceCriteria();
if(StringUtils.isNotEmpty(source)){
criteria.setSource(SetRestriction.in(BusTicketSource.valueOf(source)));
}
if(StringUtils.isNotEmpty(fromBusStop)){
criteria.setFromBusStop(fromBusStop);
}
if(StringUtils.isNotEmpty(toBusStop)){
criteria.setToBusStop(toBusStop);
}
criteria.setOrderBys(OrderBy.desc("id"));
List<BusTicketPrice> tmpList=manager.findBusTicketPrice(criteria);
//生成輸出流
ByteArrayOutputStream out = new ByteArrayOutputStream();
ExportBusTicketPriceUtil exprotUtil=new ExportBusTicketPriceUtil();
try{
exprotUtil.initWorkbook(out);
exprotUtil.createSheet("車票價格", 1);
exprotUtil.writeDetails(tmpList);
}
finally{
exprotUtil.closeWorkbook();
out.close();
}
exportStream = new ByteArrayInputStream(out.toByteArray());
return SUCCESS;
}
catch(Exception e){
logger.error(Calendar.getInstance().getTime()+":BusPriceExportCommand.execute2={}", e);
this.addErrorMessage("下載時發生異常." + e.toString());
return FAILURE;
}
}
@Override
public Dispatcher getMapping2(String result) {
if(SUCCESS.equals(result)){
return new DownloadDispatcher(exportStream, "application/ms-excel", "busPriceImportFormat.xls");
}else{
return new CommandDispatcher(this.getReturnCommand(), this.getOutputMessage());
}
}
public String getSource() {
return source;
}
public void setSource(String source) {
this.source = source;
}
public String getFromBusStop() {
return fromBusStop;
}
public void setFromBusStop(String fromBusStop) {
this.fromBusStop = fromBusStop;
}
public String getToBusStop() {
return toBusStop;
}
public void setToBusStop(String toBusStop) {
this.toBusStop = toBusStop;
}
}
3.ExportBusTicketPriceUtil
package com.icicle.goldenfly.web.order.report;
import java.io.IOException;
import java.util.List;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import org.apache.commons.lang.StringUtils;
import com.icicle.framework.member.client.MemberClientUtils;
import com.icicle.framework.order.client.models.BusTicketPrice;
/**
* 處理車票價格導出數據 為Excel格式
* @author James pu
*
*/
public class ExportBusTicketPriceUtil extends JxlExcelExportFacade<BusTicketPrice>{
public void writeDetails(List<BusTicketPrice> busTicketPriceList)
throws RowsExceededException, WriteException, IOException {
this.writeHeader("來源[CNHKBUS-中港通,TRANSISLAND-環島通]", "出發站點代碼","目的站點代碼", "單程 賣價",
"雙程 賣價", "單程 買入價", "雙程 買入價", "單程 門市價","雙程 門市價","票數量","有效期","備注[50字內]"
);
for(BusTicketPrice busTicketPrice :busTicketPriceList){
writeContents(busTicketPrice);
}
}
@Override
protected JxlFormatAndValue[] getContents(BusTicketPrice busTicketPrice, Object... parameters) {
JxlFormatAndValue[] fnvs = new JxlFormatAndValue[15];
// 來源[CNHKBUS-中港通,TRANSISLAND-環島通]
fnvs[0] = new JxlFormatAndValue(
StringUtils.defaultString(busTicketPrice.getSource().name()), CELL_FORMAT_DEFAULT);
// 出發站點代碼
fnvs[1] = new JxlFormatAndValue(
StringUtils.defaultString(busTicketPrice.getFromBusStop()), CELL_FORMAT_DEFAULT);
// 目的站點代碼
fnvs[2] = new JxlFormatAndValue(
StringUtils.defaultString(busTicketPrice.getToBusStop()), CELL_FORMAT_DEFAULT);
// 單程 賣價
fnvs[3] = new JxlFormatAndValue(
MemberClientUtils.getFirstNotNull(busTicketPrice.getPriceOfSingle(),0.0), CELL_FORMAT_DEFAULT_DECIMAL);
// 雙程 賣價
fnvs[4] = new JxlFormatAndValue(MemberClientUtils.getFirstNotNull(busTicketPrice.getPriceOfRound(),0.0), CELL_FORMAT_DEFAULT_DECIMAL);
// 單程 買入價
fnvs[5] = new JxlFormatAndValue(
MemberClientUtils.getFirstNotNull(busTicketPrice.getDealPriceOfSingle(),0.0), CELL_FORMAT_DEFAULT_DECIMAL);
// 雙程 買入價
fnvs[6] = new JxlFormatAndValue(MemberClientUtils.getFirstNotNull(busTicketPrice.getDealPriceOfRound(),0.0), CELL_FORMAT_DEFAULT_DECIMAL);
// 單程 門市價
fnvs[7] = new JxlFormatAndValue(
MemberClientUtils.getFirstNotNull(busTicketPrice.getRealPriceOfSingle(),0.0), CELL_FORMAT_DEFAULT_DECIMAL);
//雙程 門市價
fnvs[8] = new JxlFormatAndValue(MemberClientUtils.getFirstNotNull(busTicketPrice.getRealPriceOfRound(),0.0), CELL_FORMAT_DEFAULT_DECIMAL);
// 票數量
fnvs[9] = new JxlFormatAndValue(MemberClientUtils.getFirstNotNull(busTicketPrice.getRestNumber(), 0), CELL_FORMAT_DEFAULT_INTEGER);
//有效期
fnvs[10] = new JxlFormatAndValue(busTicketPrice.getExpiredDate(), CELL_FORMAT_DEFAULT_DATE);
//備注[50字內]
fnvs[11] = new JxlFormatAndValue(StringUtils.defaultString(busTicketPrice.getRemark()), CELL_FORMAT_DEFAULT);
return fnvs;
}
}
4.JxlExcelExportFacade
package com.icicle.goldenfly.web.order.report;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Colour;
import jxl.write.DateFormat;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.CellValue;
import jxl.write.biff.RowsExceededException;
/**
* make list of maps to xls output stream.
*/
public abstract class JxlExcelExportFacade<T> {
protected final NumberFormat NUMBER_FORMAT = new NumberFormat("#,##0.00");
protected final NumberFormat INTEGER_FORMAT = new NumberFormat("#,##0");
protected final DateFormat DATETIME_FORMAT = new DateFormat("yyyy-MM-dd HH:mm:ss");
protected final DateFormat DATE_FORMAT = new DateFormat("yyyy-MM-dd");
protected WritableFont FONT;
protected WritableFont BOLD_FONT;
protected WritableCellFormat CELL_FORMAT_HEADER;
protected WritableCellFormat CELL_FORMAT_DEFAULT;
protected WritableCellFormat CELL_FORMAT_DEFAULT_INTEGER;
protected WritableCellFormat CELL_FORMAT_DEFAULT_DECIMAL;
protected WritableCellFormat CELL_FORMAT_DEFAULT_DATETIME;
protected WritableCellFormat CELL_FORMAT_DEFAULT_DATE;
protected WritableCellFormat CELL_FORMAT_HIGHLIGHT;
protected WritableCellFormat CELL_FORMAT_HIGHLIGHT_INTEGER;
protected WritableCellFormat CELL_FORMAT_HIGHLIGHT_DECIMAL;
protected WritableCellFormat CELL_FORMAT_HIGHLIGHT_DATETIME;
protected WritableCellFormat CELL_FORMAT_HIGHLIGHT_DATE;
private int xIndex;
private int yIndex;
protected WritableWorkbook workbook;
protected Map<String, WritableSheet> sheetsMap = new LinkedHashMap<String, WritableSheet>();
protected Map<String, WritableCellFormat> cacheMap = new LinkedHashMap<String, WritableCellFormat>();
protected WritableSheet sheet;
protected static final Logger logger = Logger.getLogger(JxlExcelExportFacade.class);
public JxlExcelExportFacade() {
try {
FONT = new WritableFont(WritableFont.TAHOMA);
BOLD_FONT = new WritableFont(FONT);
BOLD_FONT.setBoldStyle(WritableFont.BOLD);
CELL_FORMAT_DEFAULT = new WritableCellFormat(FONT);
CELL_FORMAT_DEFAULT_INTEGER = new WritableCellFormat(FONT, INTEGER_FORMAT);
CELL_FORMAT_DEFAULT_DECIMAL = new WritableCellFormat(FONT, NUMBER_FORMAT);
CELL_FORMAT_DEFAULT_DATETIME = new WritableCellFormat(FONT, DATETIME_FORMAT);
CELL_FORMAT_DEFAULT_DATE = new WritableCellFormat(FONT, DATE_FORMAT);
CELL_FORMAT_HIGHLIGHT = new WritableCellFormat(FONT);
CELL_FORMAT_HIGHLIGHT.setBackground(Colour.VERY_LIGHT_YELLOW);
CELL_FORMAT_HEADER = new WritableCellFormat(BOLD_FONT);
CELL_FORMAT_HIGHLIGHT_INTEGER = new WritableCellFormat(FONT, INTEGER_FORMAT);
CELL_FORMAT_HIGHLIGHT_INTEGER.setBackground(Colour.VERY_LIGHT_YELLOW);
CELL_FORMAT_HIGHLIGHT_DECIMAL = new WritableCellFormat(FONT, NUMBER_FORMAT);
CELL_FORMAT_HIGHLIGHT_DECIMAL.setBackground(Colour.VERY_LIGHT_YELLOW);
CELL_FORMAT_HIGHLIGHT_DATETIME = new WritableCellFormat(FONT, DATETIME_FORMAT);
CELL_FORMAT_HIGHLIGHT_DATE = new WritableCellFormat(FONT, DATE_FORMAT);
CELL_FORMAT_HIGHLIGHT_DATETIME.setBackground(Colour.VERY_LIGHT_YELLOW);
} catch (WriteException e) {
e.printStackTrace();
}
}
public void initWorkbook(OutputStream outputStream) throws IOException {
final WorkbookSettings workbookSettings = new WorkbookSettings();
workbookSettings.setGCDisabled(false);
workbookSettings.setEncoding("UTF-8");
workbook = Workbook.createWorkbook(outputStream, workbookSettings);
}
public void initWorkbook(File file) throws IOException {
final WorkbookSettings workbookSettings = new WorkbookSettings();
workbookSettings.setEncoding("UTF-8");
workbook = Workbook.createWorkbook(file, workbookSettings);
}
public WritableSheet createSheet(String label, Integer index) {
WritableSheet ws = workbook.createSheet(label, index);
sheet = ws;
sheetsMap.put(label, ws);
xIndex = 0;
yIndex = 0;
return ws;
}
public WritableSheet setWorkingSheet(String label) {
WritableSheet ws = null;
if (sheetsMap.containsKey(label)) {
ws = sheetsMap.get(label);
sheet = ws;
}
return ws;
}
public void writeContents(T object, Object... parameters) throws RowsExceededException,
WriteException, IOException {
JxlFormatAndValue[] contents = getContents(object, parameters);
writeContents(contents);
}
public void writeGroupContents(List<T> objects, Object... parameters) throws RowsExceededException,
WriteException, IOException {
List<JxlFormatAndValue[]> collection = getGroupContents(objects, parameters);
for(JxlFormatAndValue[] fnvs : collection){
writeContents(fnvs);
}
}
protected abstract JxlFormatAndValue[] getContents(T object, Object... parameters);
protected List<JxlFormatAndValue[]> getGroupContents(List<T> objects, Object... parameters){
List<JxlFormatAndValue[]> list = new ArrayList<JxlFormatAndValue[]>();
for(T object : objects){
list.add(getContents(object, parameters));
}
return list;
}
public void writeContents(JxlFormatAndValue... contents)
throws RowsExceededException, WriteException, IOException {
for (JxlFormatAndValue content : contents) {
writeCell(content);
}
nextRow();
}
public void writeHeader(String... headerData)
throws RowsExceededException, WriteException, IOException {
writeHeader(0, 0, headerData);
}
public void writeHeader(int row, int col, String... headerData)
throws RowsExceededException, WriteException, IOException {
xIndex = col;
yIndex = row;
for (String key : headerData) {
final Label label = new Label(xIndex++, yIndex, key,
CELL_FORMAT_HEADER);
sheet.addCell(label);
}
nextRow();
}
public void mergeCells(int row, int col, int rowspan, int colspan)
throws RowsExceededException, WriteException{
sheet.mergeCells(row, col, row + rowspan - 1, col + colspan -1);
}
public void writeCell(JxlFormatAndValue content) throws RowsExceededException,
WriteException {
if(content == null){
nextCell();
}else{
if( content.getColspan() > 1 || content.getRowspan() > 1 ){
sheet.mergeCells(xIndex, yIndex,
xIndex + content.getColspan() - 1,
yIndex + content.getRowspan() - 1);
}
writeCell(content.getValue(), content.getFormat());
}
}
public void writeCell(Object value, WritableCellFormat writableCellFormat)
throws RowsExceededException, WriteException {
if (value == null) {
nextCell();
} else {
CellValue cellValue = null;
if (Date.class.isAssignableFrom(value.getClass())) {
if (writableCellFormat == null) {
cellValue = new jxl.write.DateTime(xIndex++, yIndex,
(Date) value, CELL_FORMAT_DEFAULT_DATETIME);
} else {
cellValue = new jxl.write.DateTime(xIndex++, yIndex,
(Date) value, writableCellFormat);
}
} else if (Number.class.isAssignableFrom(value.getClass())) {
Number numberValue = (Number)value;
if (writableCellFormat == null) {
cellValue = new jxl.write.Number(xIndex++, yIndex,
numberValue.doubleValue(), CELL_FORMAT_DEFAULT_DECIMAL);
} else {
cellValue = new jxl.write.Number(xIndex++, yIndex,
numberValue.doubleValue(), writableCellFormat);
}
} else {
if (writableCellFormat == null) {
cellValue = new Label(xIndex++, yIndex, value.toString(),
CELL_FORMAT_DEFAULT);
} else {
cellValue = new Label(xIndex++, yIndex, value.toString(),
writableCellFormat);
}
}
if (cellValue != null) {
sheet.addCell(cellValue);
}
}
}
public void nextCell() {
xIndex++;
}
public void nextRow() {
xIndex = 0;
yIndex++;
}
public void closeWorkbook() throws IOException {
try {
if (workbook != null) {
workbook.write();
}
if (workbook != null) {
workbook.close();
}
} catch (WriteException e) {
e.printStackTrace();
}
}
public int getxIndex() {
return xIndex;
}
public void setxIndex(int xIndex) {
this.xIndex = xIndex;
}
public int getyIndex() {
return yIndex;
}
public void setyIndex(int yIndex) {
this.yIndex = yIndex;
}
}