c# - 为什么使用 NPOI 将 DataTable 导出到 Excel 导致 ERR_CONNECTION_RESET

Question

我使用 ASP.NET C# 和 Angular 8 开发了一个 Web API。我尝试将一些数据从 Oracle 导出到服务器端的 Excel 文件中（使用 NPOI）并在客户端下载它。我可以看到 memoryStream 有数据，但在“返回响应”中它没有返回给客户端，而是再次调用函数。这是我的代码：

HTML：

 <button
            class="col-sm-2 button-style button-text"
            style="color: white; background-color: #19D893;height: 100%;"
            (click)="exportPriceList()">
        Export
        </button>

输入脚本：

public exportPriceList(): void {

      let fileName='filename'; 
      const fileType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';

      console.log('before promise');

      const promise = new Promise((resolve, reject) => {                                 
        this.export().subscribe(
          success => {
            console.log('start success');
      
            const blob = new Blob ([success], {type: fileType});
      
            console.log('window.navigator ', window.navigator  );
            console.log('window.navigator.msSaveOrOpenBlob ', window.navigator.msSaveOrOpenBlob  );
      
            if (window.navigator && window.navigator.msSaveOrOpenBlob) {
              
      
              window.navigator.msSaveOrOpenBlob(blob, );
            } else {
                const a = document.createElement('a');
                a.href = URL.createObjectURL(blob);
                a.download = fileName ;
                document.body.appendChild(a);
                a.click();
                document.body.removeChild(a);
            }
        },
        err => {
            alert(err);
        });
       
     });

      promise.then((res) => {
        console.log('promise!!!! ', res );
      });
      promise.catch((err) => {
      });
    }

public export()
  {
    console.log('start export to excel');

    const fileName = 'filename1'; 
    const fileType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';

    const url =  this.apiService.baseUrl + 'api/PriceList/Export?token=' + this.auth.authDetails.getValue().token +
        '&priceListType=' + this.selectedPriceListType.Key + '&fileType=' + fileType + '&bank=' + this.selectedBank.Key;

    return this.http.get(url, {responseType:  'arraybuffer'});// this.http.get(url, {responseType:  'blob'});
  }

API 控制器：

[HttpGet]
    [Route("api/PriceList/Export")]
    public HttpResponseMessage Export([FromUri]string token, [FromUri]int priceListType,[FromUri]string fileType, [FromUri]int? bank=null)
    {
        try
        {
            var stream = dal.ExportPriceListServer(priceListType, bank);

            var response = new HttpResponseMessage(System.Net.HttpStatusCode.OK);

            response.Content = new StreamContent(stream);
            response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
            {
                FileName= "PriceList_" + bank + "_" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx" 
            };
            response.Content.Headers.ContentType = new MediaTypeHeaderValue(fileType);

            return response;
        }
        catch (Exception ex)
        {
            exc.throwException(EventLogEntryType.Error, ex.ToString());
            throw;
        }
    }

DAL：（生成 excel 的最终函数。）

public MemoryStream ExportToExcel(DataTable dt)
    {
        try
        {
            using (var stream = new MemoryStream())
            {
                // Declare XSSFWorkbook object for create sheet  
                var workbook = new XSSFWorkbook();
                var sheet = workbook.CreateSheet("sheet 1");

                int rowIndex = 0;
                var headerRow = sheet.CreateRow(1);

                //Below loop is create header  
                foreach (DataColumn column in dt.Columns)
                {
                    XSSFCell xc = (XSSFCell)headerRow.CreateCell(column.Ordinal);
                    xc.SetCellValue(GetColumnTitle(column.ColumnName));
                    //xc.CellStyle = xstl;
                }

                rowIndex = 2;
                foreach (DataRow row in dt.Rows)
                {
                    XSSFRow dataRow = (XSSFRow)(sheet.CreateRow(rowIndex));

                    foreach (DataColumn column in dt.Columns)
                    {
                        XSSFCell c = (XSSFCell)dataRow.CreateCell(column.Ordinal);
                        string val = row[column].ToString();

                        int x;
                        if (Int32.TryParse(val, out x) && (!String.IsNullOrEmpty(val)))//column.IsNumeric()
                        {
                            c.SetCellValue(Int64.Parse(val));
                            c.SetCellType(CellType.Numeric);
                        }
                        else
                        {
                            c.SetCellValue(row[column].ToString());
                        }

                        // no need to auto size all the time, after 100 is ok... (it costs a lot)
                        if (rowIndex == 100)
                        {
                            sheet.AutoSizeColumn(column.Ordinal);
                        }

                    }

                    rowIndex++;
                }

                // Declare one MemoryStream variable for write file in stream  

                workbook.Write(stream, true);

                return stream;
            }
        }
        catch (Exception ex)
        {

            Console.Write(ex);
            return null;
        }
    }

这是我在 chrome 调试器中看到的：[Network][1]

在 Timing 我看到“Stallen”：[Stallen][2]

rates.component.ts:100 before promise
zone.js:2969 GET .....     **net::ERR_CONNECTION_RESET**
scheduleTask @ zone.js:2969
push../node_modules/zone.js/dist/zone.js.ZoneDelegate.scheduleTask @ zone.js:407
onScheduleTask @ zone.js:297
push../node_modules/zone.js/dist/zone.js.ZoneDelegate.scheduleTask @ zone.js:401
push../node_modules/zone.js/dist/zone.js.Zone.scheduleTask @ zone.js:232
push../node_modules/zone.js/dist/zone.js.Zone.scheduleMacroTask @ zone.js:255
scheduleMacroTaskWithCurrentZone @ zone.js:1114
(anonymous) @ zone.js:3001
proto.<computed> @ zone.js:1394
(anonymous) @ http.js:1630
push../node_modules/rxjs/_esm5/internal/Observable.js.Observable._trySubscribe

@ Observable.js:43 push../node_modules/rxjs/_esm5/internal/Observable.js.Observable.subscribe @Observable.js:29 push../node_modules/rxjs/_esm5/internal/operators/finalize.js.FinallyOperator .call @finalize.js:13 push../node_modules/rxjs/_esm5/internal/Observable.js.Observable.subscribe @Observable.js:24 (匿名) @subscribeTo.js:21 subscribeToResult @subscribeToResult.js:11 push ../node_modules/rxjs/_esm5/internal/operators/[e-mail]:74 push../node_modules/rxjs/_esm5/internal/operators/mergeMap.js.MergeMapSubscriber._tryNext@mergeMap。 js:68 push../node_modules/rxjs/_esm5/internal/operators/[e-mail]:51 push../node_modules/rxjs/_esm5/internal/Subscriber.js。[e-mail]:54（匿名）@scalar.js:5 push../node_modules/rxjs/_esm5/internal/[e-mail]:43 push../node_modules/rxjs /_esm5/internal/[e-mail]:29 push../node_modules/rxjs/_esm5/internal/operators/[e-mail]:29 push../node_modules /rxjs/_esm5/internal/[e-mail]:24 push../node_modules/rxjs/_esm5/internal/operators/[e-mail]:15 push.. /node_modules/rxjs/_esm5/internal/[e-mail]:24 push../node_modules/rxjs/_esm5/internal/operators/[e-mail]:18 push ../node_modules/rxjs/_esm5/internal/[e-mail]:24（匿名）@rates.component.ts:144 [e-mail]:891 push../src/app/Components/ content/rates/[e-mail]:143 [e-mail]:100 [e-mail]:19545 [e-mail]:20639 [e-mail]:20342 dispatchEvent @core.js:16994（匿名）@core.js:17441（匿名）@platform-b​​rowser.js:993 push../node_modules/zone.js/dist/[e-mail]： 421 [e-mail]:14051 push../node_modules/zone.js/dist/[e-mail]:420 push../node_modules/zone.js/dist/zone.js.Zone [e-mail]:188 push../node_modules/zone.js/dist/[e-mail]:496 [e-mail]:1540 [e-mail]:1566

你有什么解决办法吗？

谢谢，

标签： c#excelangularasp.net-web-apinpoi

Answer 1

我解决了这个问题。我更改代码：

API 控制器：

MemoryStream stream = dal.ExportPriceListServer(priceListType);

            var response = new HttpResponseMessage(System.Net.HttpStatusCode.OK);

            response.Content = new ByteArrayContent(stream.ToArray());
            response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
            {
                FileName = "test.xlsx"
            };
            response.Content.Headers.ContentType = new MediaTypeHeaderValue(fileType);

            return response;

DAL：周围使用 Just for write the stream

 // Declare one MemoryStream variable for write file in stream  
            using (var stream = new MemoryStream())
            {
                workbook.Write(stream, true);

                return stream;
            }