MVC+Bootstrap+NPOI实现Excel文件导出并下载

sunflower(*^ω^*)

于 2022-06-15 17:36:06 发布

阅读量351

点赞数 1

文章标签： mvc bootstrap 前端 c#

本文链接：https://blog.csdn.net/qq_45744011/article/details/125301408

版权

该博客展示了如何在后台使用C#实现Excel数据的导出功能，通过创建HSSFWorkbook对象，设置工作表和表头，遍历数据填充到Excel表格中，并将其以流的形式返回给前端。同时，前端使用Ajax和Fileinput插件实现Excel的导入功能，通过异步上传文件到服务器进行处理。

摘要由CSDN通过智能技术生成

后台方法如下

  public ActionResult ExportFirstinspection()
        {
            Context context = new Context();
            var listExaminee = context.FirstinspectionAccounts.ToList();
            //创建工作簿Excel
            HSSFWorkbook excelBook = new HSSFWorkbook();
            //为工作簿创建工作表并命名
            ISheet sheet1 = excelBook.CreateSheet("封样台账");
            //编写工作表 (1)表头 (2)数据：listStudent
            //创建第一行
            IRow row1 = sheet1.CreateRow(0);
            //创建其他列并赋值
            row1.CreateCell(0).SetCellValue("序号");
            row1.CreateCell(1).SetCellValue("条码");
            row1.CreateCell(2).SetCellValue("机型编码");
            row1.CreateCell(3).SetCellValue("版本号");
            row1.CreateCell(4).SetCellValue("数量");
            row1.CreateCell(5).SetCellValue("制作日期");
            row1.CreateCell(6).SetCellValue("封样状态");
            row1.CreateCell(7).SetCellValue("封样位置");
            row1.CreateCell(8).SetCellValue("领用班组");
            row1.CreateCell(9).SetCellValue("领用人");
            row1.CreateCell(10).SetCellValue("领用时长");
            row1.CreateCell(11).SetCellValue("领用状态");
            row1.CreateCell(12).SetCellValue("领用时间");
            row1.CreateCell(13).SetCellValue("类别");
            //遍历数据集合
            for (int i = 0; i < listExaminee.Count(); i++)
            {
                //创建行
                IRow rowTemp = sheet1.CreateRow(i + 1);
                rowTemp.CreateCell(0).SetCellValue(listExaminee[i].Id);
                rowTemp.CreateCell(1).SetCellValue(listExaminee[i].BarCode);
                rowTemp.CreateCell(2).SetCellValue(listExaminee[i].ModelCode);
                rowTemp.CreateCell(3).SetCellValue(listExaminee[i].VersionNumber);
                rowTemp.CreateCell(4).SetCellValue(listExaminee[i].Number.ToString());
                rowTemp.CreateCell(5).SetCellValue(listExaminee[i].ProductionDate.ToString());
                rowTemp.CreateCell(6).SetCellValue(listExaminee[i].FyState);
                rowTemp.CreateCell(7).SetCellValue(listExaminee[i].FyStation);
                rowTemp.CreateCell(8).SetCellValue(listExaminee[i].ReceivingTeam);
                rowTemp.CreateCell(9).SetCellValue(listExaminee[i].Receiver);
                rowTemp.CreateCell(10).SetCellValue(listExaminee[i].ReceivingDuration);
                rowTemp.CreateCell(11).SetCellValue(listExaminee[i].ReceivingState);
                rowTemp.CreateCell(12).SetCellValue(listExaminee[i].ReceivingTime.ToString());
                rowTemp.CreateCell(13).SetCellValue(listExaminee[i].TypeName);
            }
            //文件名
            var fileName = "封样台账" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
            //将Excel表格转化为流，输出
            MemoryStream bookStream = new MemoryStream();//创建文件流
            excelBook.Write(bookStream); //文件写入流（向流中写入字节序列）
            bookStream.Seek(0, SeekOrigin.Begin);//输出之前调用Seek，把0位置指定为开始位置
            return File(bookStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);//最后以文件形式返回
        }

前端调用如下

@model KZQFirstTestPaperless.Domain.ViewModel.FirstinspectionAccountViewModel
@using KZQFirstTestPaperless.Common
@{
    AjaxOptions ajax = new AjaxOptions()
    {
        UpdateTargetId = "ContentRow",
        HttpMethod = "Get"
    };
    ViewBag.Title = "首检台账";
    Layout = null;
}
@*<link href="~/Content/layui/css/layui.css" rel="stylesheet" />
    <script src="~/Content/layui/layui.js"></script>*@
<link href="~/Scripts/fileinput/fileinput.css" rel="stylesheet" />
<script src="~/Scripts/fileinput/fileinput.min.js"></script>
<script src="~/Scripts/fileinput/zh.js"></script>
<div class="panel panel-info" style="margin:20px">
    <div class="panel-heading text-center">
        <h4>首检台账</h4>
    </div>
    <div class="panel-body">
        <div id="tableRow" style="margin-top:10px">
            <div class="row" style="margin:10px 0px;">
                @using (Ajax.BeginForm("FirstinspectionIndex", "Firstinspection", new { selectType = 1 }, ajax))
                {
                    <div class="col-xs-4 col-sm-3 col-md-2">
                        <input class="form-control" name="barCode" placeholder="条码" value="@ViewBag.BarCode" />
                    </div>
                    <div class="col-xs-4 col-sm-3 col-md-2">
                        <input class="form-control" name="modelCode" placeholder="机型编码" value="@ViewBag.ModelCode" />
                    </div>
                    <div class="col-xs-4 col-sm-3 col-md-1">
                        <input class="form-control" name="versionNumber" placeholder="版本号" value="@ViewBag.VersionNumber" />
                    </div>
                    <div class="col-xs-4 col-sm-3 col-md-2">
                        <input class="form-control" name="receivingState" placeholder="领用状态" value="@ViewBag.ReceivingState" />
                    </div>
                    <div class="col-xs-4 col-sm-3 col-md-2">
                        <input class="form-control" name="fyStation" placeholder="封样位置" value="@ViewBag.FyStation" />
                    </div>
                    <div class="col-xs-4 col-sm-3 col-md-2">
                        <select class="form-control" name="typeName">
                            <option value="">--全部类别--</option>
                            <option value="变频">变频</option>
                            <option value="内机主板">内机主板</option>
                            <option value="显示器分体机">显示器分体机</option>
                            <option value="显示器柜机">显示器柜机</option>
                            <option value="遥控器">遥控器</option>
                        </select>
                        <span hidden>@ViewBag.TypeName</span>
                    </div>

                    <div class="col-xs-4 col-sm-3 col-md-1">
                        <button class="form-control btn btn-primary" type="submit">查询</button>
                    </div>
                }
            </div>
            <table id="print" class="table table-bordered table-hover table-striped table-responsive table2excel">
                <tr>
                    <th>
                        @Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].Id)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].BarCode)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].ModelCode)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].VersionNumber)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].Number)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].ProductionDate)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].FyState)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].FyStation)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].ReceivingTeam)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].Receiver)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].ReceivingDuration)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].ReceivingState)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].ReceivingTime)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.FirstinspectionAccounts[0].TypeName)
                    </th>
                    <td class="not"></td>
                </tr>
                @foreach (var item in Model.FirstinspectionAccounts)
                {
                    <tr>
                        <td>
                            @Html.DisplayFor(modelItem => item.Id)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.BarCode)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.ModelCode)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.VersionNumber)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.Number)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.ProductionDate)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.FyState)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.FyStation)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.ReceivingTeam)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.Receiver)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.ReceivingDuration)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.ReceivingState)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.ReceivingTime)
                        </td>
                        <td>
                            @Html.DisplayFor(modelItem => item.TypeName)
                        </td>
                        <td class="not">
                            <a href="/Firstinspection/EditFirstinspection?id=@Html.DisplayFor(modelItem => item.Id,ajax)"
                               data-target="#ajax"
                               data-toggle="modal">
                                编辑
                            </a>
                            @*@Html.ActionLink("编辑", "EditFirstinspection", new { id = item.Id })*@ |
                            @Ajax.ActionLink("删除", "DeleteFirstinspection", new { id = item.Id }, ajax)
                        </td>
                    </tr>
                }
                <tr class="not">
                    <td></td>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td>
                        <a href="/Firstinspection/AddFirstinspection"
                           data-target="#ajax"
                           data-toggle="modal">
                            新增
                        </a>
                        @*@Html.ActionLink("新增", "AddFirstinspection")*@
                    </td>
                </tr>
            </table>
            <div class="col-xs-4 col-sm-3 col-md-1">
                @*<input type="file" value="导入Excel" class="form-control btn btn-primary" id="import">*@
                <input type="file" value="导入Excel" id="import" multiple class="file" />
            </div>
            <div class="col-xs-4 col-sm-3 col-md-1">
                <button type="button" class="form-control btn btn-primary" id="export">导出Excel</button>
            </div>
            @*<button type="button" class="layui-btn" id="import"><i class="layui-icon layui-icon-upload"></i>导入Excel</button>*@
            @*<button type="button" class="form-control btn btn-primary" id="export"><i class="arrow-down-circle"></i>导出Excel</button>*@
        </div>
        <div class="text-right">
            @Html.PageAjaxLinks(Model.PagingInfo, "#ContentRow", x => Url.Action("FirstinspectionIndex", new { page = x, barCode = ViewBag.BarCode, modelCode = ViewBag.ModelCode, versionNumber = ViewBag.VersionNumber, receivingState = ViewBag.ReceivingState, typeName = ViewBag.TypeName, fyStation = ViewBag.FyStation }))
        </div>
    </div>
</div>
<div class="modal" id="ajax" role="dialog" aria-hidden="true">
    <div class="modal-dialog" role="document">
        <div class="modal-content">
        </div>
    </div>
</div>

<script>
    //导入Excel文件
    $('#import').fileinput({
        language: 'zh',     //设置语言
        dropZoneEnabled: false,      //是否显示拖拽区域
        dropZoneTitle: "可以将Excel拖放到这里",    //拖拽区域显示文字
        uploadUrl: '/Firstinspection/ImportFirstinspection',  //上传路径
        allowedFileExtensions: ['xls'],   //指定上传文件类型
        maxFileSize: 0,
        maxFileSize: 2048,   //上传文件最大值，单位kb
        uploadAsync: true,  //异步上传
        maxFileCount: 1    //上传文件最大个数。
    }).on("fileuploaded", function (event, data) { //异步上传成功后回调
        if (data.response) {
            alert('导入Excel文件成功！');
        }
        else {
            alert('导入Excel文件失败！');
        }
        location.reload();
    });
    //导出Excel文件
    $(function () {
        $("#export").click(function () {
            var url = "/Firstinspection/ExportFirstinspection"
            window.open(url)
        })
    })
</script>