import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
@RequiresPermissions("good:update")
@ResponseBody
@RequestMapping(value="shopListExportExcel")
public void shopListExportExcel(HttpServletResponse response,HttpServletRequest request, String goods_type,String is_on_sale,String goods_name,Integer goods_sku) throws Exception{
Integer isHotelGift = 0;
//获取大分类
GoodsTypeExample goodstypeexample = new GoodsTypeExample();
com.orgtec.hotel.model.GoodsTypeExample.Criteria goodstypc= goodstypeexample.createCriteria();
goodstypc.andIsDelEqualTo(0);
goodstypeexample.setOrderByClause("order_by desc");
List<GoodsType> types = shopGoodsTypeService.selectList(goodstypeexample);
//获取小分类
DetalGoodsTypeExample detalGoodsTypeExample = new DetalGoodsTypeExample();
detalGoodsTypeExample.createCriteria().andIsDelEqualTo(0);
detalGoodsTypeExample.setOrderByClause("order_by desc");
List<DetalGoodsType> detalTypes = shopGoodsTypeService.selectDetalTypeList(detalGoodsTypeExample);
for(GoodsType bigType:types) {
if(String.valueOf(bigType.getIsHotelGift()).equals("1")) {
isHotelGift = bigType.getId();
}
}
ShopGoodsExample example = new ShopGoodsExample();
Criteria c = example.createCriteria();
c.andGoodsTypeNotEqualTo(isHotelGift.byteValue()).andIsRecycleEqualTo((byte)0);
if(goods_name!=null&&!"".equals(goods_name)){
// c.andGoodsNameEqualTo(goods_name);
c.andGoodsNameLike("%"+goods_name+"%");
}
if(goods_type!=null&&!"0".equals(goods_type)){
c.andDetalGoodsTypeEqualTo(Byte.valueOf(goods_type));
}
if(is_on_sale!=null&&!"0".equals(is_on_sale)){
if("1".equals(is_on_sale)){
c.andIsAvailableEqualTo(Byte.valueOf(is_on_sale));
}else if("2".equals(is_on_sale)){
is_on_sale="0";
c.andIsAvailableEqualTo(Byte.valueOf(is_on_sale));
}else if("3".equals(is_on_sale)){
c.andIsNewEqualTo(Byte.valueOf("1"));
}else if("4".equals(is_on_sale)){
c.andIsRecommendEqualTo(Byte.valueOf("1"));
}
}
if(goods_sku!=null&&!"".equals(goods_sku)){
c.andGoodsSkuLike("%"+goods_sku+"%");
}
example.setOrderByClause("goods_id desc");
List<ShopGoods> goods = greenShopService.selectList(example);
HSSFWorkbook wb = new HSSFWorkbook();
Sheet sh = wb.createSheet(); // 创建一个片
/* 表头 */
{
int cellIndex = 0;
Row title = sh.createRow(0); // 创建一行
// 创建单元格
title.createCell(cellIndex++).setCellValue("商品名称");
title.createCell(cellIndex++).setCellValue("sku");
title.createCell(cellIndex++).setCellValue("大分类");
title.createCell(cellIndex++).setCellValue("小分类");
title.createCell(cellIndex++).setCellValue("价格");
title.createCell(cellIndex++).setCellValue("库存");
title.createCell(cellIndex++).setCellValue("是否上架");
title.createCell(cellIndex++).setCellValue("新品首发");
title.createCell(cellIndex++).setCellValue("人气推荐");
CellStyle style = wb.createCellStyle(); //单元格样式
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 设置背景颜色模式
style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); // 设置背景颜色
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font = wb.createFont();
font.setFontName("微软雅黑");
// font.setFontHeightInPoints((short) 12);//设置字体大小
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font); //设置字体
for (int i = 0; i < cellIndex ; i++) {
title.getCell(i).setCellStyle(style);
sh.setColumnWidth(i , 4000); //设置列宽
}
}
int i = 1;
int cellIndex = 0;
for (ShopGoods shopgoods : goods) {
Row row = sh.createRow(i++);
row.createCell(cellIndex++).setCellValue(shopgoods.getGoodsName());
row.createCell(cellIndex++).setCellValue(0);
//大分类
if(shopgoods.getGoodsType() ==-1) {
row.createCell(cellIndex++).setCellValue("服饰");
row.createCell(cellIndex++).setCellValue("");
} else {
for (GoodsType bType : types) {
if((int)shopgoods.getGoodsType() == bType.getId() ) {
System.out.println(bType.getName()+"........"+bType.getId());
row.createCell(cellIndex++).setCellValue("大分类");
break;
}
}
}
//小分类
for (DetalGoodsType detalType : detalTypes) {
if(detalType.getId() == (int)shopgoods.getDetalGoodsType()) {
row.createCell(cellIndex++).setCellValue("小分类");
// row.createCell(cellIndex++).setCellValue(detalType.getName());
}
}
row.createCell(cellIndex++).setCellValue(shopgoods.getNowPrice());
row.createCell(cellIndex++).setCellValue(shopgoods.getGoodsNum());
if(shopgoods.getIsAvailable() == 1) {
row.createCell(cellIndex++).setCellValue("是");
}else {
row.createCell(cellIndex++).setCellValue("否");
}
if(shopgoods.getIsNew() == 1) {
row.createCell(cellIndex++).setCellValue("是");
}else {
row.createCell(cellIndex++).setCellValue("否");
}
if(shopgoods.getIsHot() == 1) {
row.createCell(cellIndex++).setCellValue("是");
}else {
row.createCell(cellIndex++).setCellValue("否");
}
cellIndex = 0;
}
String agent = request.getHeader("User-Agent");
boolean isMSIE = (agent != null && agent.indexOf("MSIE") != -1);
String exportFileName = "商品订单"+DateUtil.formatFromDate2(new Date())+ ".xls";
String finalFileName = "";
if( isMSIE ){
finalFileName = java.net.URLEncoder.encode(exportFileName,"UTF8");
}else{
finalFileName = new String(exportFileName.getBytes("UTF-8"), "ISO-8859-1");
}
response.setHeader("Content-Disposition", "attachment; filename=" +finalFileName);
response.setContentType("application/octet-stream; charset=UTF-8");
wb.write(response.getOutputStream());
}