jimingminlovefly

统计

最新评论

案例-excel导出,下载数据库内容


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;
 }

}

 

posted on 2011-11-04 17:28 计明敏 阅读(460) 评论(0)  编辑  收藏 所属分类: java


只有注册用户登录后才能发表评论。


网站导航: