享元模式在poi excel表格生成中的应用

享元模式简单介绍：

由工厂创建所需要的对象的集合，客户端通过工厂获取所需要的对象。

当客户端需要使用多个相同对象时，可以使用享元模式。这样可以减少相同对象的大量创建，减少对象创建的开销。

在poi中的excel表格，对于每个cell都需要一个CellStyle对象，这些CellStyle对象只有几类，对于每一类的CellStyle，对象都相同可以复用。

实际代码示例：

工厂类：

import java.util.HashMap;
import java.util.Map;


import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;


public class CellStyleFactory {

private Map<String,HSSFCellStyle> cellStyles;

public static final String FRONATSTYLE="front";
public static final String CATEGORYNAMESTYLE="categoryNameStyle";
public static final String CATEGORYNUMBERSTYLE="categoryNumberStyle";
public static final String DISHNAMESTYLE="dishNameStyle";
public static final String DISHKINDNAMESTYLE="dishKindNameStyle";
public static final String DISHNUMLEFTSTYLE="dishNumLeftStyle";
public static final String DISHNUMRIGHTSTYLE="dishNumRightStyle";

public CellStyleFactory(HSSFWorkbook wb){
cellStyles=new HashMap<String, HSSFCellStyle>();

HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

HSSFCellStyle frontRowstyle = wb.createCellStyle();
frontRowstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
frontRowstyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); // 前景色
frontRowstyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellStyles.put(FRONATSTYLE, frontRowstyle);

HSSFCellStyle categoryNameStyle = wb.createCellStyle();
categoryNameStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
categoryNameStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); // 前景色
categoryNameStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellStyles.put(CATEGORYNAMESTYLE, categoryNameStyle);

HSSFCellStyle categoryNnumberStyle = wb.createCellStyle();
categoryNnumberStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
categoryNnumberStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); // 前景色
categoryNnumberStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellStyles.put(CATEGORYNUMBERSTYLE, categoryNnumberStyle);

HSSFCellStyle dishNameStyle = wb.createCellStyle();
dishNameStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
cellStyles.put(DISHNAMESTYLE, dishNameStyle);

HSSFCellStyle dishKindNameStyle = wb.createCellStyle();
dishKindNameStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyles.put(DISHKINDNAMESTYLE, dishKindNameStyle);

HSSFCellStyle dishNumLeftStyle = wb.createCellStyle();
dishNumLeftStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
cellStyles.put(DISHNUMLEFTSTYLE, dishNumLeftStyle);

HSSFCellStyle dishNumRightStyle = wb.createCellStyle();
dishNumRightStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
cellStyles.put(DISHNUMRIGHTSTYLE, dishNumRightStyle);

}

public HSSFCellStyle getHSSFCellStyle(String kind){
return cellStyles.get(kind);

}




}

client类： Struts2 中action类：

import org.antlr.grammar.v3.ANTLRv3Parser.element_return;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.struts2.dispatcher.SessionMap;
import org.apache.struts2.interceptor.ServletRequestAware;
import org.apache.struts2.interceptor.ServletResponseAware;
import org.apache.struts2.interceptor.SessionAware;
import org.springframework.beans.factory.annotation.Autowired;


import com.dianping.ed.logger.EDLogger;
import com.dianping.ed.logger.LoggerManager;
import com.dianping.hobbit.dishsales.bo.CategorySales;
import com.dianping.hobbit.dishsales.bo.DishSalesResult;
import com.dianping.hobbit.dishsales.bo.DishSalesTab;
import com.dianping.hobbit.dishsales.bo.SalesSearchParams;
import com.dianping.hobbit.dishsales.bo.SkuSales;
import com.dianping.hobbit.dishsales.service.DishSalesVolumeService;
import com.dianping.orderdish.common.BaseAction;
import com.dianping.orderdish.log.LoggerConfig;
import com.dianping.orderdish.shop.basic.HbtShopBO;
import com.dianping.orderdish.shop.basic.HobbitShopRemoteService;
import com.dianping.orderdish.shop.order.service.impl.AccountShopService;
import com.dianping.orderdish.shop.order.util.CellStyleFactory;
import com.dianping.orderdish.util.DateUtils;
import com.opensymphony.xwork2.ActionSupport;
import com.sankuai.meituan.filter.util.User;
import com.sankuai.meituan.filter.util.UserUtils;


import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


/**
 * Created by Wujianfeng on 15/4/29. WEB: 商户列表
 */
@SuppressWarnings("unused")
public class ExcelExportAction extends BaseAction {


private static CellStyleFactory cellStyleFactory;
private static HSSFWorkbook wb;


private InputStream excelStream; // 输出流变量
private String excelFileName; // 下载文件名
private DishSalesResult dishSalesResult;
SalesSearchParams searchParams;
private static final EDLogger ERROR_LOG = LoggerManager.getLogger(LoggerConfig.ORDER_LIST_ERROR.getValue());


@Autowired
private DishSalesVolumeService dishSalesVolumeService;


@Autowired
private AccountShopService accountShopService;


public String exportExcel() {


try {
if (searchParams == null) {
String date = DateUtils.convertDateShort(new Date());
searchParams = new SalesSearchParams(getShopId(), date, date, "00:00", "23:59");
}
dishSalesResult = dishSalesVolumeService.getDishSalesResult(searchParams);
if (dishSalesResult == null) {
ERROR_LOG.error("dishSalesResult does not exist");
dishSalesResult=new DishSalesResult();
}
// 创建一个webbook，对应一个Excel文件
wb = new HSSFWorkbook();
cellStyleFactory = new CellStyleFactory(wb);
// 在webbook中添加一个sheet，对应Excel文件中的 sheet
HSSFSheet sheetWithSale = wb.createSheet("有销量菜品(" + dishSalesResult.getSoldDishTabVO().getDishCnt() + ")");
HSSFSheet sheetWithoutSale = wb
.createSheet("零销量菜品(" + dishSalesResult.getNotSoldDishTabVO().getDishCnt() + ")");
HSSFSheet sheetAll = wb.createSheet("全部菜品(" + dishSalesResult.getAllDishTabVO().getDishCnt() + ")");
createSheetContent(sheetWithSale, dishSalesResult.getSoldDishTabVO());
createSheetContent(sheetWithoutSale, dishSalesResult.getNotSoldDishTabVO());
createSheetContent(sheetAll, dishSalesResult.getAllDishTabVO());


// 将文件存到流中
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);


byte[] fileContent = os.toByteArray();
ByteArrayInputStream is = new ByteArrayInputStream(fileContent);


excelStream = is; // 文件流
String fileName = searchParams.getShopId() + "_" + searchParams.getStartDate() + searchParams.getStartTime()
+ "-" + searchParams.getEndDate() + searchParams.getEndTime() + "_count.xls";
excelFileName = fileName; // 设置下载的文件名


return SUCCESS;
} catch (Exception e) {
ERROR_LOG.error("download excel fail", e);
return ERROR;
} 


}


private void createSheetContent(HSSFSheet sheet, DishSalesTab tabVo) {
int rowNum = 0;


// 在sheet中添加表头第0行，
HSSFRow row = sheet.createRow(rowNum);


createColumn(row, 0, "菜品名", cellStyleFactory.getHSSFCellStyle(CellStyleFactory.FRONATSTYLE));
createColumn(row, 1, "单价", cellStyleFactory.getHSSFCellStyle(CellStyleFactory.FRONATSTYLE));
createColumn(row, 2, "销售量", cellStyleFactory.getHSSFCellStyle(CellStyleFactory.FRONATSTYLE));
createColumn(row, 3, "销售额", cellStyleFactory.getHSSFCellStyle(CellStyleFactory.FRONATSTYLE));
rowNum++;


if (tabVo == null) {
return;
}
List<CategorySales> categorySalesVOs = tabVo.getCategorySalesVOs();
if (categorySalesVOs == null) {
categorySalesVOs = new ArrayList<CategorySales>();
}
List<CategorySales> categoryVOs = tabVo.getCategorySalesVOs();
if (categoryVOs == null) {
return;
}
for (CategorySales categorySalesVO : categoryVOs) {
row = sheet.createRow(rowNum);
createColumn(row, 0, categorySalesVO.getName() + "(" + categorySalesVO.getDishCnt() + ")",
cellStyleFactory.getHSSFCellStyle(CellStyleFactory.CATEGORYNAMESTYLE));
createColumn(row, 1, "", cellStyleFactory.getHSSFCellStyle(CellStyleFactory.CATEGORYNUMBERSTYLE));
createColumn(row, 2, "" + categorySalesVO.getSalesVolume(),
cellStyleFactory.getHSSFCellStyle(CellStyleFactory.CATEGORYNUMBERSTYLE));
createColumn(row, 3, "" + categorySalesVO.getPriceTotal(),
cellStyleFactory.getHSSFCellStyle(CellStyleFactory.CATEGORYNUMBERSTYLE));
rowNum++;


List<SkuSales> skuSalesVOs = categorySalesVO.getSkuSales();
if (skuSalesVOs == null) {
continue;
}
for (SkuSales skuSalesVO : skuSalesVOs) {
if (skuSalesVO == null) {
continue;
}
List<SkuSales> dishSalesVOs = skuSalesVO.getDishSales();
// 是否有其他规格
if (dishSalesVOs != null && dishSalesVOs.size() != 0) {


row = sheet.createRow(rowNum);
createColumn(row, 0, skuSalesVO.getName(),
cellStyleFactory.getHSSFCellStyle(CellStyleFactory.DISHNAMESTYLE));
createColumn(row, 1, skuSalesVO.getPrice(),
cellStyleFactory.getHSSFCellStyle(CellStyleFactory.DISHNUMLEFTSTYLE));
createColumn(row, 2, "" + skuSalesVO.getSalesVolume(),
cellStyleFactory.getHSSFCellStyle(CellStyleFactory.DISHNUMRIGHTSTYLE));
createColumn(row, 3, "" + skuSalesVO.getPriceTotal(),
cellStyleFactory.getHSSFCellStyle(CellStyleFactory.DISHNUMRIGHTSTYLE));
rowNum++;


for (SkuSales dishSalesVO : dishSalesVOs) {
row = sheet.createRow(rowNum);
createColumn(row, 0, dishSalesVO.getName(),
cellStyleFactory.getHSSFCellStyle(CellStyleFactory.DISHKINDNAMESTYLE));
createColumn(row, 1, dishSalesVO.getPrice(),
cellStyleFactory.getHSSFCellStyle(CellStyleFactory.DISHNUMRIGHTSTYLE));
createColumn(row, 2, "" + dishSalesVO.getSalesVolume(),
cellStyleFactory.getHSSFCellStyle(CellStyleFactory.DISHNUMRIGHTSTYLE));
createColumn(row, 3, "" + dishSalesVO.getPriceTotal(),
cellStyleFactory.getHSSFCellStyle(CellStyleFactory.DISHNUMRIGHTSTYLE));
rowNum++;
}
} else {
row = sheet.createRow(rowNum);
createColumn(row, 0, skuSalesVO.getName(),
cellStyleFactory.getHSSFCellStyle(CellStyleFactory.DISHNAMESTYLE));
createColumn(row, 1, skuSalesVO.getPrice(),
cellStyleFactory.getHSSFCellStyle(CellStyleFactory.DISHNUMRIGHTSTYLE));
createColumn(row, 2, "" + skuSalesVO.getSalesVolume(),
cellStyleFactory.getHSSFCellStyle(CellStyleFactory.DISHNUMRIGHTSTYLE));
createColumn(row, 3, "" + skuSalesVO.getPriceTotal(),
cellStyleFactory.getHSSFCellStyle(CellStyleFactory.DISHNUMRIGHTSTYLE));
rowNum++;
}
}


}
}


private void createColumn(HSSFRow row, int column, String cellValue, HSSFCellStyle style) {
HSSFCell cell;
cell = row.createCell(column);
cell.setCellValue(cellValue);
cell.setCellStyle(style);
}


public InputStream getExcelStream() {
return excelStream;
}


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


public String getExcelFileName() {
return excelFileName;
}


public void setExcelFileName(String excelFileName) {
this.excelFileName = excelFileName;
}


public DishSalesResult getDishSalesResult() {
return dishSalesResult;
}


public void setDishSalesResult(DishSalesResult dishSalesResult) {
this.dishSalesResult = dishSalesResult;
}


}