public void downloadOrder(String orderNo, Boolean canPay, long startDate, long endDate, Constant.OrderStatus status, HttpServletResponse response) {
LocalDateTime localDateTimeStart = ConverterUtil.getTimeByTimestamp(startDate);
LocalDateTime localDateTimeEnd = ConverterUtil.getTimeByTimestamp(endDate);
LocalDateTime start = getStartLocalDateTime(localDateTimeStart);
LocalDateTime end = getEndLocalDateTime(localDateTimeEnd);
List<OrderEntity> orderEntities = orderDao.findAll((root, criteriaQuery, criteriaBuilder) -> {
Predicate condition = criteriaBuilder.and(
criteriaBuilder.greaterThanOrEqualTo(root.get(OrderEntity_.updateTime),start),
criteriaBuilder.lessThanOrEqualTo(root.get(OrderEntity_.updateTime),end)
);
if(!StringUtils.isEmpty(orderNo)) {
Predicate condition2 = criteriaBuilder.equal(root.get(OrderEntity_.orderNo),orderNo);
condition = criteriaBuilder.and(condition,condition2);
}
if(canPay!=null) {
Predicate condition2 = criteriaBuilder.equal(root.get(OrderEntity_.canPay),canPay);
condition = criteriaBuilder.and(condition,condition2);
}
if(status!=null){
Predicate condition2 = criteriaBuilder.equal(root.get(OrderEntity_.orderStatus),status);
condition = criteriaBuilder.and(condition,condition2);
}
return criteriaQuery.where(condition).getRestriction();
});
OutputStream os = null;
/**
* 、订单明细ID、供货商、商品编号、商品名称、下单时间、规格、售卖单价、数量、合计、结算价（合计-进货价格）、订单实际支付价格、运费，支付方式，状态，用户ID，姓名，身份证，手机号，地址，邮政编码，备注，物流公司，物流单号
* */
try {
String fileName = "订单统计_"+ getDownloadTime(start) + "到" + getDownloadTime(end)+"_";
fileName += status == null ? ".xls" : "_" + Order.getOrderStatus(status) + ".xls";
response.setContentType("application/unknown; charset=GB2312");
response.setHeader("content-disposition", "attachment;filename="
+ new String(fileName.getBytes("GB2312"), "iso-8859-1"));
os = response.getOutputStream();
HSSFWorkbook wb = new HSSFWorkbook();
//给sheet命名
HSSFSheet s = wb.createSheet("orderSheet");
//设置样式
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
cellStyle.setWrapText(true);//设置自动换行
HSSFRow row = s.createRow(0);
HSSFCell cell = null;
setCell(row, 0, "商城订单号", cellStyle);
setCell(row, 1, "用户ID", cellStyle);
setCell(row, 2, "姓名", cellStyle);
setCell(row, 3, "身份证", cellStyle);
setCell(row, 4, "手机号", cellStyle);
setCell(row, 5, "地址", cellStyle);
setCell(row, 6, "邮政编码", cellStyle);
setCell(row, 7, "备注", cellStyle);
setCell(row, 8, "订单状态", cellStyle);
setCell(row, 9, "支付方式", cellStyle);
setCell(row, 10, "订单总价", cellStyle);
setCell(row, 11, "下单时间", cellStyle);
setCell(row, 12, "运费", cellStyle);
setCell(row, 13, "订单行ID", cellStyle);
setCell(row, 14, "供货商", cellStyle);
setCell(row, 15, "商品编号", cellStyle);
setCell(row, 16, "商品名称", cellStyle);
setCell(row, 17, "商品状态", cellStyle);
setCell(row, 18, "物流公司（供货商填写）", cellStyle);
setCell(row, 19, "物流单号（供货商填写）", cellStyle);
setCell(row, 20, "规格", cellStyle);
setCell(row, 21, "商品数量", cellStyle);
setCell(row, 22, "进货单价", cellStyle);
setCell(row, 23, "售卖单价", cellStyle);
setCell(row, 24, "数量", cellStyle);
setCell(row, 25, "合计", cellStyle);
setCell(row, 26, "结算价（合计-进货价格）", cellStyle);
int i = 1;
if(orderEntities != null && orderEntities.size() > 0) {
for(OrderEntity order : orderEntities){
if(order != null) {
List<OrderItemEntity> orderItemEntities = order.getOrderItems();
int maxRow = 0;//最大行
StringBuffer sb = new StringBuffer();
if(orderItemEntities != null && orderItemEntities.size() > 0) {
int temp = i;//写每一行
for(OrderItemEntity orderItemEntity : orderItemEntities) {
//商品信息
String goodsIdStr = orderItemEntity.getCommodity().getGoodsId();
List<MallUtil.Item> items = MallUtil.getGoodsFromGoodsStr(goodsIdStr);
//商品订单信息
int size = items.size();
String itemId = items.get(0).itemID;
//供货商编号
Map<String,String> supplierNos = getMap(orderItemEntity.getSupplierNo(), items.size(), itemId);
//状态
Map<String,String> statuses = getMap(orderItemEntity.getStatus(), items.size(), itemId);
//公司物流编号
Map<String,String> logisticsCodes = getMap(orderItemEntity.getLogisticsCode(), items.size(), itemId);
//物流单号
Map<String,String> expressFormNos = getMap(orderItemEntity.getExpressFormNo(),items.size(),itemId);
double importPrices = 0;//进货总价
for(MallUtil.Item item : items) {
row = s.createRow(temp);
String goodId = item.itemID;//商品ID
GoodsEntity goodsEntity = goodsDao.getOne(goodId);
if(goodsEntity != null) {
setCell(row, 14, supplierNos!=null?supplierNos.get(goodId):"", cellStyle);//供货商
setCell(row, 15, ""+goodsEntity.getProductNo(), cellStyle);//商品编号
setCell(row, 16, ""+goodsEntity.getName(), cellStyle);//商品名称
setCell(row, 17, statuses!=null?Order.getOrderItemStatus(Integer.valueOf(statuses.get(goodId))):"", cellStyle);//商品状态
setCell(row, 18, logisticsCodes!=null?logisticsCodes.get(goodId):"", cellStyle);//物流公司
setCell(row, 19, expressFormNos!=null?expressFormNos.get(goodId):"", cellStyle);//物流编号
setCell(row, 20, ""+goodsEntity.getUnit(), cellStyle);//规格
setCell(row, 21, ""+item.num, cellStyle);//商品数量
setCell(row, 22, ""+goodsEntity.getPurchasePrice(), cellStyle);//进货单价
importPrices+=MallUtil.mul(orderItemEntity.getCount(),goodsEntity.getPurchasePrice());
temp+=1;
}else {
throw new BusinessException(ErrorCode.MALL_NOT_EXIST);
}
}
//订单明细下的商品种类
int typeNum = items.size();//2
maxRow += typeNum;
int firstRow = i+maxRow-typeNum;
int lastRow = i+maxRow-1;
if(typeNum > 1) {//合并单元格
addCellRangeAddress(firstRow, lastRow, 13, s, wb);//订单明细ID
addCellRangeAddress(firstRow, lastRow, 23, s, wb);//售卖单价
addCellRangeAddress(firstRow, lastRow, 24, s, wb);//数量
addCellRangeAddress(firstRow, lastRow, 25, s, wb);//合计
addCellRangeAddress(firstRow, lastRow, 26, s, wb);//结算（赢利=合计-进货价）
row = s.getRow(firstRow);
}
setCell(row, 13, ""+orderItemEntity.getId(), cellStyle);
setCell(row, 23, ""+orderItemEntity.getPrice(), cellStyle);
setCell(row, 24, ""+orderItemEntity.getCount(), cellStyle);
double price = MallUtil.mul(orderItemEntity.getCount(),orderItemEntity.getPrice());
setCell(row, 25, ""+ price, cellStyle);
setCell(row, 26, ""+MallUtil.sub(price,importPrices), cellStyle);
}
}
//合并单元格范围 参数（int firstRow, int lastRow, int firstCol, int lastCol)
int firstRow = i;
int lastRow = i+maxRow-1;
if(lastRow > firstRow) {
addCellRangeAddress(firstRow, lastRow, 0, s, wb);//订单号
addCellRangeAddress(firstRow, lastRow, 1, s, wb);//用户ID
addCellRangeAddress(firstRow, lastRow, 2, s, wb);//姓名
addCellRangeAddress(firstRow, lastRow, 3, s, wb);//身份证
addCellRangeAddress(firstRow, lastRow, 4, s, wb);//手机号
addCellRangeAddress(firstRow, lastRow, 5, s, wb);//地址
addCellRangeAddress(firstRow, lastRow, 6, s, wb);//邮政编码
addCellRangeAddress(firstRow, lastRow, 7, s, wb);//商品备注
addCellRangeAddress(firstRow, lastRow, 8, s, wb);//订单状态
addCellRangeAddress(firstRow, lastRow, 9, s, wb);//支付方式
addCellRangeAddress(firstRow, lastRow, 10, s, wb);//订单实际支付价格
addCellRangeAddress(firstRow, lastRow, 11, s, wb);//下单时间
addCellRangeAddress(firstRow, lastRow, 12, s, wb);//运费
row = s.getRow(firstRow);
}
setCell(row, 0, ""+order.getOrderNo(), cellStyle);
setCell(row, 1, ""+order.getUser().getUserID(), cellStyle);
setCell(row, 2, ""+order.getName(), cellStyle);
setCell(row, 3, ""+order.getIdNo(), cellStyle);
setCell(row, 4, ""+order.getMobile(), cellStyle);
setCell(row, 5, ""+order.getAddress(), cellStyle);
setCell(row, 6, ""+order.getPostcode(), cellStyle);
setCell(row, 7, ""+order.getDemo(), cellStyle);
setCell(row, 8, ""+Order.getOrderStatus(order.getOrderStatus()), cellStyle);
setCell(row, 9, ""+Order.getPayChannel(order.getPayChannel()), cellStyle);
setCell(row, 10, ""+order.getPayTotalFee(), cellStyle);
setCell(row, 11, ""+order.getCreateTime(), cellStyle);
setCell(row, 12, ""+order.getFreight(), cellStyle);
i+=maxRow;
}
}
}
//写入excel 关闭流
wb.write(os);
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
os.flush();
os.close();
}catch (Exception e) {
e.printStackTrace();
}
}
}
//设置单元格
private static void setCell(HSSFRow row, int col, String content, CellStyle cellStyle) {
Cell cell = row.createCell(col);
cell.setCellStyle(cellStyle);
cell.setCellValue(content);
}
//设置合并单元格
private void addCellRangeAddress(int firstRow, int lastRow, int col, HSSFSheet sheet, Workbook wb) {
CellRangeAddress address = new CellRangeAddress(firstRow, lastRow, col, col);
sheet.addMergedRegion(address);//订单明细ID
setRegionBorder(1,address,sheet,wb);
}
//给合并单元格增加边框
private static void setRegionBorder(int border, CellRangeAddress region, HSSFSheet sheet, Workbook wb){
RegionUtil.setBorderBottom(border,region, sheet, wb);
RegionUtil.setBorderLeft(border,region, sheet, wb);
RegionUtil.setBorderRight(border,region, sheet, wb);
RegionUtil.setBorderTop(border,region, sheet, wb);
}
private String getDownloadTime(LocalDateTime date) {
String str = date.toString();
if(StringUtils.isEmpty(str)) {
return "";
}
return str.substring(0,10);
}
private Map<String,String> getMap(String str, int size, String itemId) {
if(StringUtils.isEmpty(str)) {
return null;
}
if(!StringUtils.isEmpty(str) && size > 1) {
return MallUtil.getMapFromGoodsStr(str);
}else {
Map<String,String> map = new HashMap<String,String>();
map.put(itemId, str);
return map;
}
}单元格的列，行都从0开始。
合并单元格注意问题：
如果合并0-2行，则写单元格内容时，先取到0行的row，然后再设置cell列，如果取2行的row，写内容会失败。
扫一扫
390
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
