导出Excel
来源：互联网 发布：linux file 文件类型 编辑：程序博客网 时间：2024/06/15 20:55
1.OrderController.java
@RequestMapping("orderExport")public ModelAndView orderExport(String condition, String dMobileOrId, String statusS){ try { OrderBean orderBean = JSON.parseObject(condition, OrderBean.class); if(statusS != null && !statusS.equals("null")){ List<Integer> statusList = Lists.newArrayList(); String[] statusArray = statusS.split(","); if (statusArray != null && statusArray.length > 0) { for (String status : statusArray) { statusList.add(Integer.valueOf(status)); } orderBean.setStatusList(statusList); } } if(StringUtils.isNotBlank(dMobileOrId)){ Member member = null; if(dMobileOrId.length() == 11){ member = memberService.getByMobile(dMobileOrId); if(member != null){ orderBean.setSellerId(member.getId()); }else{ orderBean.setSellerId(Long.parseLong(dMobileOrId)); } }else{ orderBean.setSellerId(Long.parseLong(dMobileOrId)); } } List<List<?>> dataListLists = bOrderService.list(orderBean); if(dataListLists == null ){ return null; } String titles = "订单编号,收件人,收货人联系手机,收货地址,商品名字,发货信息,备注,业务类型,订单状态,售后状态,售后详细状态,退换货原因,退款方式,应退款金额,已退款金额,支付方式,购买件数,原始单价,快递费,折扣金额,应付金额,所属设计师id,所属设计师,提交时间"; return ObjectExcelView.createExcel(titles, dataListLists); } catch (Exception e) { e.printStackTrace(); } return new ModelAndView();}2.OrderBizService.javapublic List<List<?>> list(OrderBean orderBean) throws BizException { List<List<?>> listList = Lists.newArrayList(); List<Order> list = orderService.list(orderBean); /*"订单编号,收件人,收货人联系手机,收货地址,商品名字,发货信息,备注,业务类型,订单状态,售后状态,售后详细状态,退换货原因,退款方式,应退款金额,已退款金额,支付方式,购买件数,原始单价,快递费,折扣金额,应付金额,所属设计师id,所属设计师,提交时间"*/ List<Long> orderIdList = BizUtil.extractToList(list, "id"); Map<Long,List<?>> orderMap = Maps.newHashMap(); for (Order order : list) { List<Object> dataList = Lists.newArrayList(); //单号 dataList.add(order.getId()); String addressJson = order.getAddressJson(); Address address = JSON.parseObject(addressJson, Address.class); //收件人 //收货人联系手机 //收货地址 if(address != null){ dataList.add(address.getAddressee()); dataList.add(address.getMobile()); AreaCombo areaCombo = areaService.getAreaCombo(address.getAreaIdCombo()); if(areaCombo != null){ StringBuffer buffer = new StringBuffer(); Area province = areaCombo.getProvince(); if(province != null){ buffer.append(province.getPlaceZh()); } Area city = areaCombo.getCity(); if(city != null){ buffer.append(" ").append(city.getPlaceZh()); } Area county = areaCombo.getCounty(); if(county != null){ buffer.append(" ").append(county.getPlaceZh()); } buffer.append(" ").append(address.getDetail()); dataList.add(buffer.toString()); }else{ dataList.add("地址不详！"); } }else{ dataList.add(""); dataList.add(""); dataList.add(""); } //商品名字----------------4 //发货信息-规格------------5 //备注 dataList.add(order.getDescription()); //业务类型 SaleTypeEnum saleTypeEnum = SaleTypeEnum.valueOf(order.getSaleType()); dataList.add(saleTypeEnum == null?"错误的类型:"+order.getSaleType():saleTypeEnum.getRemark()); //订单状态 OrderStatusEnum orderStatusEnum = OrderStatusEnum.getByCode(order.getStatus()); dataList.add(orderStatusEnum == null?"状态码错误:"+order.getStatus() :orderStatusEnum.getRemark()); //售后状态-------------9 //售后详细状态-----------10 //退换货原因-------------11 //退款方式--------------12 //应退款金额--------------13 //已退款金额--------------14 //支付方式 ---------------15 if(BooleanUtils.toBoolean(order.getPayStatus())){ PayWayEnum payWayEnum = PayWayEnum.getByCode(order.getPayWay()); dataList.add(payWayEnum == null?"未知的支付方式:"+order.getPayWay():payWayEnum.getShowText()); }else{ dataList.add("-"); } //购买数量----------16 //原始单价----------17 //快递费------------18 dataList.add(order.getDeliveryFee()); //折扣金额----------19 //应付金额----------20 //所属设计师id-------21 //所属设计师---------22 //提交时间-----------23 orderMap.put(order.getId(), dataList); } //按订单项排 List<Item> itemList = itemService.listByOrderIdList(orderIdList); if(CollectionUtils.isEmpty(itemList)){ return listList; } //商品idList List<Long> productIdList = Lists.newArrayList(); //设计师idList List<Long> sellerIdList = Lists.newArrayList(); //订单项idList List<Long> itemIdList = Lists.newArrayList(); for (Item item : itemList) { productIdList.add(item.getProductId()); sellerIdList.add(item.getSellerId()); itemIdList.add(item.getId()); } //商品 List<MemberDetail> memberDetailList = memberDetailService.listByMemberIdList(sellerIdList); Map<String, MemberDetail> memberDetailMap = BizUtil.listToMap("memberId", memberDetailList); //设计师 List<Product> productList = productService.listByIdList(productIdList); Map<Long,Product> productMap = BizUtil.listToMap("id",productList); //售后 List<Return> returnList = returnService.findByOriginalOrderIdList(orderIdList); Map<Long,Return> returnMap = BizUtil.listToMap("orderDetailId", returnList); //原始订单id为key(如果订单已支付，但取消了，他会同一取消，这时没有订单项id) Map<Long,Return> returnMap2 = BizUtil.listToMap("originalOrderId", returnList); SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); for (Item item : itemList) { Long orderId = item.getOrderId(); @SuppressWarnings("unchecked") List<Object> dataList = (List<Object>) orderMap.get(orderId); //克隆一个新对象来操作，不然根据dataList修改会出现错误 List<Object> data = new ArrayList<>(dataList); StringBuffer buffer = new StringBuffer(); if(StringUtils.isNotBlank(item.getSpecJson())){ List<SpecJsonItem> specList = JSON.parseArray(item.getSpecJson(), SpecJsonItem.class); for (SpecJsonItem specJsonItem : specList) { buffer.append(specJsonItem.getName()).append(":").append(specJsonItem.getValue()); } } Product product = productMap.get(item.getProductId()); data.add(4,product != null ? product.getName():item.getProductId()); data.add(5, buffer.toString()); //售后状态-------------9 //售后详细状态-----------10 //退换货原因-------------11 //退款方式--------------12 //应退款金额--------------13 //已退款金额--------------14 Return returnt = returnMap.get(item.getId()); //取消订单没有订单项id，只能用原始订单查 Return returnt2 = returnMap2.get(item.getOrderId()); if(returnt != null){ ReturnTypeEnum returnTypeEnum = ReturnTypeEnum.getByCode(returnt.getReturnType()); data.add(9,returnTypeEnum == null?"状态码错误："+returnt.getReturnType() : returnTypeEnum.getRemark()); ReturnStateEnum returnStateEnum = ReturnStateEnum.getByCode(returnt.getStatus()); data.add(10,returnStateEnum == null?"状态码错误："+returnt.getStatus() :returnStateEnum.getRemark()); data.add(11,returnt.getReason()); PayWayEnum payWayEnum = PayWayEnum.getByCode(returnt.getRefundWay()); //如果是换货就不显示退款金额 if(returnt.getReturnType() == ReturnTypeEnum.RETURN_BARTER.getCode()){ data.add(12,"-"); data.add(13,"-"); data.add(14,"-"); }else{ data.add(12,payWayEnum == null?"状态码错误："+returnt.getRefundWay() :payWayEnum.getShowText()); data.add(13,returnt.getRefundFee()); if(returnStateEnum.equals(ReturnStateEnum.FINISH)){ data.add(14,returnt.getRefundFee()); }else{ data.add(14,"0"); } } }else if(returnt == null && returnt2 != null){ ReturnTypeEnum returnTypeEnum = ReturnTypeEnum.getByCode(returnt2.getReturnType()); data.add(9,returnTypeEnum == null?"状态码错误："+returnt2.getReturnType() : returnTypeEnum.getRemark()); ReturnStateEnum returnStateEnum = ReturnStateEnum.getByCode(returnt2.getStatus()); data.add(10,returnStateEnum == null?"状态码错误："+returnt2.getStatus() :returnStateEnum.getRemark()); data.add(11,returnt2.getReason()); PayWayEnum payWayEnum = PayWayEnum.getByCode(returnt2.getRefundWay()); //如果是换货就不显示退款金额 if(returnt2.getReturnType() == ReturnTypeEnum.RETURN_BARTER.getCode()){ data.add(12,"-"); data.add(13,"-"); data.add(14,"-"); }else{ data.add(12,payWayEnum == null?"状态码错误："+returnt2.getRefundWay() :payWayEnum.getShowText()); data.add(13,returnt2.getRefundFee()); if(returnStateEnum.equals(ReturnStateEnum.FINISH)){ data.add(14,returnt2.getRefundFee()); }else{ data.add(14,"0"); } } }else{ data.add(9,"-"); data.add(10, "-"); data.add(11, "-"); data.add(12, "-"); data.add(13, "-"); data.add(14, "0"); } //购买数量 data.add(16,item.getQuantity()); //原始单价 data.add(17,item.getPrice()); //折扣金额 data.add(19,item.getDiscountAmount()); //应付金额 data.add(20,item.getTotalPrice()); //设计师id 和昵称 Long sellerId = item.getSellerId(); data.add(21,sellerId); MemberDetail memberDetail = memberDetailMap.get(item.getSellerId()); if(memberDetail == null){ data.add(22,"设计师昵称不存在"); }else{ data.add(22,memberDetail.getNickname()); } if(item.getCreateTime() != null){ data.add(23,formatter.format(item.getCreateTime())); }else{ data.add(23,"提交时间为空"); } listList.add(data); } return listList;}3.ObjectExcelView.javapackage com.common.web.springmvc;import java.util.Arrays;import java.util.Date;import java.util.List;import java.util.Map;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.commons.collections.CollectionUtils;import org.apache.commons.lang3.time.DateFormatUtils;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.springframework.web.servlet.ModelAndView;import org.springframework.web.servlet.view.document.AbstractXlsView;import com.google.common.collect.Lists;import com.google.common.collect.Maps;/** * 导入到EXCEL 类名称：ObjectExcelView.java 类描述： * * @author FH 作者单位： 联系方式： * @version 1.0 */public class ObjectExcelView extends AbstractXlsView { @SuppressWarnings("unchecked") @Override protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request,HttpServletResponse response) throws Exception { String filename = DateFormatUtils.format(new Date(), "yyyyMMddHHmmss"); Cell cell; response.setContentType("application/octet-stream"); response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xls"); Sheet sheet = workbook.createSheet("sheet1"); List<String> titles = (List<String>) model.get("titles"); int len = titles.size(); CellStyle headerStyle = workbook.createCellStyle(); // 标题样式 headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); Font headerFont = workbook.createFont(); // 标题字体 headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setFontHeightInPoints((short) 11); headerStyle.setFont(headerFont); sheet.setDefaultColumnWidth(20); for (int i = 0; i < len; i++) { // 设置标题 String title = titles.get(i); cell = getCell(sheet, 0, i); cell.setCellStyle(headerStyle); setText(cell, title); } sheet.getRow(0).setHeight((short) (25 * 20)); CellStyle contentStyle = workbook.createCellStyle(); // 内容样式 contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); List<Map<String, Object>> varList = (List<Map<String, Object>>) model.get("varList"); if(CollectionUtils.isNotEmpty(varList)){ for (int i = 0; i < varList.size(); i++) { Map<String, Object> vpd = varList.get(i); for (int j = 0; j < len; j++) { Object object = vpd.get("var" + (j + 1)); cell = getCell(sheet, i + 1, j); cell.setCellStyle(contentStyle); setText(cell, object != null ? object : ""); } } } } private Cell getCell(Sheet sheet, int row, int col) { Row sheetRow = sheet.getRow(row); if (sheetRow == null) { sheetRow = sheet.createRow(row); } Cell cell = sheetRow.getCell(col); if (cell == null) { cell = sheetRow.createCell(col); } return cell; } private void setText(Cell cell, Object text){ if (text instanceof Double) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue((double) text); }else if (text instanceof Integer) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(new Double(text.toString())); }else { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(text.toString()); } } /** * 生成excel * * @author Jasun 2016年4月25日上午11:00:52 * @param titleStr * 表头，多个字段用“,”分割 * @param dataListLists * 表头对应的的信息，要保证表头和属性一致 * @return */ public static ModelAndView createExcel(String titleStr, List<List<?>> dataListLists) { String[] titles = titleStr.trim().split(","); if (titles.length == 0) return new ModelAndView(); Map<String, Object> dataMap = Maps.newHashMap(); // 设置表头 List<String> titlesList = Arrays.asList(titles); dataMap.put("titles", titlesList); // 设置excel数据 if (CollectionUtils.isNotEmpty(dataListLists)) { List<Map<String, Object>> varList = Lists.newArrayList(); for (List<?> dataLists : dataListLists) { Map<String, Object> varMap = Maps.newHashMap(); for (int i = 0; i < dataLists.size(); i++) { varMap.put("var" + (i + 1), dataLists.get(i)); } varList.add(varMap); } dataMap.put("varList", varList); } ObjectExcelView erv = new ObjectExcelView(); return new ModelAndView(erv, dataMap); }}阅读全文
0 0
- 导出EXCEL
- Excel导出
- 导出Excel
- 导出EXCEL
- 导出Excel
- EXCEL导出
- 导出Excel
- 导出excel
- 导出excel
- EXCEL导出
- 导出excel
- 导出Excel
- Excel导出。
- 导出excel
- Excel 导出
- 导出excel
- 导出excel
- 导出excel
- Android学习必用-推荐大牛的博客
- java-> Kotlin
- 【重大更新】DevExpress v17.1新版亮点（WinForms篇）
- 看完嵌入式培训视频的一些感受
- oracl面试题
- 导出Excel
- H5游戏的跨平台优点
- jstl中关于fmt:parseDate和fmt:formatDate
- Real-time plotting in Python and Matlab
- JavaFX实现自定义“表单验证工具类”，简化对控件TextField、PassWordField、TextArea添加验证时的代码
- Introduction to Consul
- 变长数组
- java编程思想阅读笔记（一）
- 微信“看一看”，“搜一搜”算法大揭秘
