poi 导出excel (简易)

最新推荐文章于 2022-10-12 00:54:53 发布

danchejikafo

最新推荐文章于 2022-10-12 00:54:53 发布

阅读量400

点赞数

分类专栏： java基础

本文链接：https://blog.csdn.net/danchejikafo/article/details/81200247

版权

java基础专栏收录该内容

19 篇文章 0 订阅

订阅专栏

1.pom.xml

       <!--导出EXCEL-->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.14</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.14</version>
		</dependency>

2.公用方法

    //设置单元格
	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 firstCol, int lastCol, HSSFSheet sheet, Workbook wb) {
		CellRangeAddress address = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
		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);

	}

3.service

	@Transactional
	public boolean downloadUsrCommissions(Cash cash, HttpServletResponse response) {
		List<Commission> lstCash = query4UsrCommissions(cash);
        Doctor doctor = new Doctor();
        doctor.setDocId(cash.getDocId());
        Doctor search = doctorDao.queryById(doctor);
        if(search != null) {
            OutputStream os = null;
            try {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String time = StringUtils.isEmpty(cash.getLastTime())?"全部":cash.getLastTime();
                String fileName = "专家收入明细_ "+search.getCnName()+"_"+ time +".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("sheet");

                //设置样式
                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);
                setCell(row, 0, "用户", cellStyle);
                setCell(row, 1, "支付金额", cellStyle);
                setCell(row, 2, "平台服务费", cellStyle);
                setCell(row, 3, "实际收益", cellStyle);
                setCell(row, 4, "支付时间", cellStyle);
                setCell(row, 5, "用户是否已退款", cellStyle);

                if(null != lstCash && lstCash.size() > 0){
                    int rowIndex = 1;

                    for(Commission commission : lstCash) {
                        row = s.createRow(rowIndex);
                        setCell(row, 0, commission.getUsrName(), cellStyle);
                        setCell(row, 1, commission.getFee(), cellStyle);
                        setCell(row, 2, commission.getServiceFee(), cellStyle);
                        setCell(row, 3, commission.getActualFee(), cellStyle);
                        setCell(row, 4, sdf.format(commission.getTraDate()), cellStyle);
                        setCell(row, 5, "2".equals(commission.getStatus())?"支付成功":"已退款", cellStyle);
                        rowIndex++;
                    }
                    String totalActIn = "0.00";
                    List<Commission> total = lstCash.stream().filter(commission -> "2".equals(commission.getStatus())).collect(Collectors.toList());//未退款的才计算
                    for(Commission tmp : total){
                        totalActIn = MathUtil.addBigDecimal(totalActIn, tmp.getActualFee());
                    }


                    row = s.createRow(rowIndex);
                    addCellRangeAddress(rowIndex,rowIndex,0,5,s,wb);
                    setCell(row, 0, "专家："+search.getCnName() + ",电话："+search.getTel(), cellStyle);

                    rowIndex+=1;
                    row = s.createRow(rowIndex);
                    addCellRangeAddress(rowIndex,rowIndex,0,1,s,wb);
                    setCell(row, 0, "银行："+search.getBank(), cellStyle);
                    addCellRangeAddress(rowIndex,rowIndex,2,3,s,wb);
                    setCell(row, 2, "银行卡号："+search.getBankCardNo(), cellStyle);
                    addCellRangeAddress(rowIndex,rowIndex,4,5,s,wb);
                    setCell(row, 4, "累计收入："+totalActIn, cellStyle);
                }

                //写入excel 关闭流
                wb.write(os);
            }catch (Exception e){
                e.printStackTrace();
            }finally {
                try {
                    os.flush();
                    os.close();
                }catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }else {
            return false;
        }
		return true;
	}