java导出Excel

最新推荐文章于 2024-09-05 14:40:56 发布
c230704
最新推荐文章于 2024-09-05 14:40:56 发布
阅读量225
点赞数 1
分类专栏： java
本文链接：https://blog.csdn.net/c230704/article/details/93206207
版权
java 专栏收录该内容
23 篇文章
订阅专栏
本文详细介绍了如何使用Java编程语言来导出Excel文件，包括选择合适的库如Apache POI，创建工作簿，设置单元格数据，样式和格式，以及进行高效的数据写入操作。通过实例代码展示了从数据源获取数据并生成Excel文件的完整流程。
摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ include file="/common/taglibs.jsp" %>
<!DOCTYPE html>
<html lang="en">
<head>
    <script type="text/javascript" src="${ctx}/staticNew/ace/js/jquery.js"></script>
    <link rel="stylesheet" href="${ctx}/staticNew/css/common.css"/>
    <link rel="stylesheet" href="${ctx}/staticNew/ace/css/chosen.css"/>
    <!-- jsp文件头和头部 -->
    <%@ include file="../../index/top.jsp" %>
    <link rel="stylesheet" href="${ctx}/staticNew/ace/css/datepicker.css"/>
    <link rel="stylesheet" type="text/css" href="${ctx}/static/css/style.css">
    <!-- 百度echarts -->
    <script src="${ctx}/plugins/echarts/echarts.min.js"></script>
    <script type="text/javascript" src="${ctx}/static/js/validate.js"></script>
    <script src="${ctx}/staticNew/ace/js/date-time/bootstrap-datepicker.js"></script>
    <script type="text/javascript">
        function queryButton() {
            $("#queryForm").submit();
        }

        function detail(orderId) {
            window.location.href = "${ctx}/weixinOrder/detail?orderId=" + orderId;
        }

        //高级搜索
        function surperSearch() {
            var display = $('#superSearch').css('display');
            if (display == 'none') {
                $("#superSearch").show();
                $("#superSearch1").show();
                $("#superSearch2").show();
            } else {
                $("#superSearch").hide();
                $("#superSearch1").hide();
                $("#superSearch2").hide();
            }
        }

        $(function () {
            //日期框
            $('.date-picker').datepicker({
                autoclose: true,
                todayHighlight: true
            });
            //点击日历图标弹出日期框
            $('#startTime').datepicker().next().on(ace.click_event,
                function () {
                    $(this).prev().focus();
                });
            $('#endTime').datepicker().next().on(ace.click_event,
                function () {
                    $(this).prev().focus();
                });
        });

        function exportExcel() {
            $("#queryForm").attr("action", "${ctx}/b2c/form/exportDistributorSalesStatistics");
            $("#queryForm").attr("method", "post");
            $("#queryForm").submit();
            $("#queryForm").attr("action", "${ctx}/b2c/form/distributorSalesStatistics?pageNo=1");
            $("#queryForm").attr("method", "get");
        }

        // 回车键查询
        function keyOnClick(e) {
            var theEvent = window.event || e;
            var code = theEvent.keyCode || theEvent.which;
            if (code == 13) {  //回车键的键值为13
                queryButton();  //调用搜索事件
            }
        }
    </script>
</head>
<body class="no-skin" onkeydown="keyOnClick(event);">
<%-- 销售商品汇总表 --%>
<!-- /section:basics/navbar.layout -->
<div class="main-container" id="main-container">
    <!-- /section:basics/sidebar -->
    <div class="main-content">
        <div class="main-content-inner">
            <div class="page-content">
                <div class="row">
                    <%--<div class="itab" style="margin-top:6px;">
                        <ul>
                          &lt;%&ndash;  <li><a href= "${ctx}/b2c/form/sumSaleProduct" class="selected">销售商品汇总表</a></li> &ndash;%&gt;
                            &lt;%&ndash;<li><a href= "${ctx}/b2c/form/sumSaleCategory">销售品类汇总表</a></li> &ndash;%&gt;
                           &lt;%&ndash;  <li><a href= "${ctx}/b2c/form/commissionStaff">员工提成报表</a></li>  &ndash;%&gt;
                         </ul>
                    </div>--%>
                    <div class="col-xs-12" style=" margin-top: 20px">
                        <!-- 检索  -->
                        <form id="queryForm" action="${ctx}/b2c/form/distributorSalesStatistics?pageNo=1"
                              method="GET">
                            <table class="query_table" style="padding-left: 5px;">
                                <tr>
                                    <td style="padding-left: 5px;">
                                        <div class="nav-search">
												<span class="input-icon">
													<input type="text" name="filter_LIKES_merchantName" class="nav-search-input"
                                                           value="${param['filter_LIKES_merchantName']}" placeholder="分销商名称"/>
													<i class="ace-icon fa fa-search nav-search-icon"></i>
												</span>
                                        </div>
                                    </td>
                                    <td style="padding-left: 5px;">
                                        <div class="nav-search">
										<span class="input-icon">
											<input type="text" name="filter_LIKES_mid" class="nav-search-input"
                                                   value="${param['filter_LIKES_mid']}" placeholder="分销商ID"/>
											<i class="ace-icon fa fa-search nav-search-icon"></i>
										</span>
                                        </div>
                                    </td>

                                    <td style="padding-left: 5px;">
                                        <div class="input-group">
                                            <input class="span10 date-picker" name="startTime" id="startTime"
                                                   value="${startTime}" type="text" data-date-format="yyyy-mm-dd"
                                                   style="width: 88px; height: 29px; font-size: 13px;"
                                                   placeholder="开始时间" title="开始时间"/>
                                            <span class="input-group-addon"><i
                                                    class="fa fa-calendar bigger-110"></i></span>
                                        </div>
                                    </td>
                                    <td><i class="fa fa-exchange"></i></td>
                                    <td>
                                        <div class="input-group">
                                            <input class="span10 date-picker" name="endTime" id="endTime"
                                                   value="${endTime}" type="text" data-date-format="yyyy-mm-dd"
                                                   style="width: 88px; height: 29px; font-size: 13px;"
                                                   placeholder="结束时间" title="结束时间"/>
                                            <span class="input-group-addon"><i
                                                    class="fa fa-calendar bigger-110"></i></span>
                                        </div>
                                    </td>
                                    <td style="padding-left: 5px;">
                                        <a class="btn btn-mini btn-info" onclick="queryButton()">查询</a>
                                    </td>
                                    <td style="vertical-align:center;padding-left:5px;">
                                        <a class="btn btn-mini btn-info" onclick="exportExcel();"
                                           title="导出到EXCEL">导出</a>
                                    </td>

                                </tr>

                            </table>
                        </form>
                        <!-- 检索  -->
                        <table id="simple-table"
                               class="table table-striped table-bordered table-hover"
                               style="margin-top:5px;">
                            <thead>
                            <tr>
                                <th class="center">分销商id</th>
                                <th class="center">分销商名称</th>
                                <th class="center">成交订单数</th>
                                <th class="center">拉新用户数</th>
                                <th class="center">下单用户数</th>
                                <th class="center">下单转换率</th>
                                <th class="center">成交金额</th>
                                <th class="center">佣金率</th>
                                <th class="center">佣金支出</th>

                            </tr>
                            </thead>
                            <c:choose>
                                <c:when test="${not empty page.result}">
                                    <c:forEach items="${page.result}" var="item"
                                               varStatus="status">
                                        <tr>
                                            <td align="center">${item.mid}</td>
                                            <td align="center">${item.merchantName}</td>
                                            <td align="center">${item.numberOfTransactionOrders}</td>
                                            <td align="center">${item.numberOfNewUsers}</td>
                                            <td align="center">${item.numberOfSubscribers}</td>
                                            <td align="center">${item.downwardSingleConversionRate}%</td>
                                            <td align="center">${item.turnoverAmount}</td>
                                            <td align="center">${item.commissionRate / 100}%</td>
                                            <td align="center">${item.commissionExpenses}</td>

                                        </tr>
                                    </c:forEach>
                                </c:when>
                                <c:otherwise>
                                    <tr class="main_info">
                                        <td colspan="100" class="center">没有相关数据</td>
                                    </tr>
                                </c:otherwise>
                            </c:choose>
                            <tr>

                            </tr>
                        </table>
                    </div>
                </div>
                <!-- /.row -->
            </div>
            <!-- /.page-content -->
        </div>
    </div>
    <!-- /.main-content -->
    <!-- 返回顶部 -->
    <a href="#" id="btn-scroll-up" class="btn-scroll-up btn btn-sm btn-inverse">
        <i class="ace-icon fa fa-angle-double-up icon-only bigger-110"></i>
    </a>

</div>
<!-- /.main-container -->

<!-- basic scripts -->
<!-- 页面底部js¨ -->
<%@ include file="../../index/foot.jsp" %>
<!-- 删除时确认窗口 -->
<script src="${ctx}/staticNew/ace/js/bootbox.js"></script>
<!-- ace scripts -->
<script src="${ctx}/staticNew/ace/js/ace/ace.js"></script>
<script src="${ctx}/staticNew/ace/js/date-time/bootstrap-datepicker.js"></script>
<!-- 下拉框 -->
<script src="${ctx}/staticNew/ace/js/chosen.jquery.js"></script>
<!--提示框-->
<script type="text/javascript" src="${ctx}/staticNew/js/jquery.tips.js"></script>
<!-- inline scripts related to this page -->
</body>
</html>



----------------------------------------------------------------------------------------------
package com.ecommerce.core.service.sales;


import com.ecommerce.common.Tools;
import com.ecommerce.core.dao.sales.DistributorSalesStatisticsDao;
import com.ecommerce.core.dao.sales.UserConsumptionStatisticsDao;
import com.ecommerce.core.vo.sales.DistributorSalesStatistics;
import com.ecommerce.core.vo.sales.SaleSumVo;
import com.ecommerce.core.vo.sales.UserConsumptionStatistics;
import com.startrekstar.yd.core.orm.Page;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.List;
import java.util.Map;

/**
 * 查询用户消费统计表
 */
@Service
public class DistributorSalesStatisticsService {

    @Autowired
    private DistributorSalesStatisticsDao distributorSalesStatisticsDao;

    //查询用户消费统计表
    public Page<DistributorSalesStatistics> getAllDistributorSalesStatistics(Map<String, Object> searchParams, Page<DistributorSalesStatistics> page) {
        return distributorSalesStatisticsDao.getAllDistributorSalesStatistics(searchParams, page);
    }

    /**
     * 分销商销售报表导出
     */
    public void export(List<DistributorSalesStatistics> list, HttpServletResponse response) {

        HSSFWorkbook wb = generateWorkBook(list);
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        try {
            wb.write(outputStream);
            outputStream.flush();
        } catch (IOException e) {
            // logger.error("数据生成失败 ", e);
        }
        byte[] byteArray = outputStream.toByteArray();

        try {
            Tools.responseDownloader(response, byteArray, "分销商销售汇总_ .xls");
        } catch (Exception e) {
            e.printStackTrace();
            // logger.error("数据导出失败 ", e);
            System.out.println("faile");
        }
    }

    private HSSFWorkbook generateWorkBook(List<DistributorSalesStatistics> data) {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("sheet 1");
        DecimalFormat decimalFormat1 = new DecimalFormat("#,###,###,##0.00");
        sheet.setDefaultColumnWidth(17);
        for (int i = 0; i < 13; i++) {
            sheet.setColumnWidth(i, 10 * 600);
        }
        sheet.setDefaultRowHeight((short) 300);
        CellStyle dateTimeStyle = wb.createCellStyle();
        dateTimeStyle.setDataFormat((short) 22);
        Font font1 = wb.createFont();
        font1.setColor(Font.COLOR_RED);
        CellStyle sumStyle = wb.createCellStyle();
        sumStyle.setFont(font1);
        sumStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

        CellStyle redStyle = wb.createCellStyle();
        redStyle.setFont(font1);

        String[] titles = new String[]{"分销商ID", "分销商名称", "成交订单数", "拉新用户数", "下单用户数", "下单转换率", "成交金额", "佣金率", "佣金支出"};
        HSSFFont font = wb.createFont();
        font.setColor(HSSFColor.RED.index);
        CellStyle titleStyle = wb.createCellStyle();
        titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
        titleStyle.setFont(font);

        HSSFRow headRow = sheet.createRow(0);
        for (int i = 0; i < titles.length; i++) {
            dateTimeStyle.setFont(font);
            HSSFCell cell = headRow.createCell(i);
            cell.setCellStyle(dateTimeStyle);
            cell.setCellValue(titles[i]);
        }
        for (int i = 1; i <= data.size(); i++) {
            HSSFRow dataRow = sheet.createRow(i);
            DistributorSalesStatistics log = data.get(i - 1);
            dataRow.createCell(0).setCellValue(log.getMid());//mid;//分销商ID
            dataRow.createCell(1).setCellValue(log.getMerchantName());//merchantName;//分销商名称
            dataRow.createCell(2).setCellValue(log.getNumberOfTransactionOrders());//numberOfTransactionOrders;//成交订单数
            dataRow.createCell(3).setCellValue(log.getNumberOfNewUsers());//numberOfNewUsers;//拉新用户数
            dataRow.createCell(4).setCellValue(log.getNumberOfTransactionOrders());//numberOfSubscribers;//下单用户数
            String r2= ((double)log.getDownwardSingleConversionRate())+"%";
            dataRow.createCell(5).setCellValue(r2);//downwardSingleConversionRate;//下单转换率
            String r4= ((decimalFormat1.format(log.getTurnoverAmount())))+"元";
            dataRow.createCell(6).setCellValue(r4);//turnoverAmount;//成交金额

            String r1= ( log.getCommissionRate()/100)+"%";
            dataRow.createCell(7).setCellValue(r1);//commissionRate;//佣金率
            String r3= (decimalFormat1.format(log.getCommissionExpenses()))+"元";
            dataRow.createCell(8).setCellValue(r3);//commissionExpenses;//佣金支出
        }
        return wb;

    }


}