poi 导出excel

来源：互联网发布：js input text value 编辑：程序博客网时间：2024/06/01 09:14
   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，写内容会失败。
阅读全文
0 0