0

Sorry for long post but it's my first and I'm not know for my Goldilocks detail level, it's either too much or too little.

I'm working on an internal site using .Net Framework 4.8: Webforms and attempting to create an Excel spreadsheet using data pulled from an Oracle view.

I'm using NPOI (https://github.com/nissl-lab/npoi) to try and accomplish this export. I was able to use this same library to import spreadsheets into Oracle, so hoped I would have the same success exporting. So far, no joy.

Here's what's happening right now...

My method successfully pulls the records I need, NPOI creates the worksheet, header row, cells, and data is populated in the worksheet object - I can see the data in the debugger. I then attempt to load the worksheet into a MemoryStream and then attempt to return the MemoryStream as a spreadsheet attachment. My expectation is that I'd see the browser's (Edge) Save As dialog appear letting me save the spreadsheet. Instead, the method ends and nothing - no dialog, no browser error, no error in the debugger.

This is the result of three days trying to complete this task. Google has taken me to quite a few sites - so many that I really don't remember all the steps I've tried these past few days. Seems most sites Google suggested were for .Net Core and even more that were for MVC. Naturally I've search SO, but no joy here either. I've hit enough of a wall that I've finally signed up to SO.

Here's my method. You can probably guess, I have an asp:Button that calls this method that's part of the page's code-behind:

protected void ExportHRAppToExcel_btn_Click(object sender, EventArgs e)
{

    try
    {
        string sql = @"select id, name, desc from list_select";

        ConnectionHrTrack conn = new ConnectionHrTrack();
        OracleDataAdapter da = new OracleDataAdapter(sql, conn.Open());

        DataTable dt = new DataTable();

        da.Fill(dt);

        da.Dispose();
        conn.Close();
        
        // filling in the workbook was adapted from
        // https://www.c-sharpcorner.com/blogs/export-to-excel-using-npoi-dll-library

        var properties = new[] { "id", "name", "desc" };
        var headers = new[] { "ID", "Name", "Application Description" };

        IWorkbook workbook;
        workbook = new XSSFWorkbook();
        ISheet sheet = workbook.CreateSheet("APPS");

        // create/fill header row  
        IRow row1 = sheet.CreateRow(0);

        for (int j = 0; j < dt.Columns.Count; j++)
        {
            ICell cell = row1.CreateCell(j);
            String columnName = dt.Columns[j].ToString();
            cell.SetCellValue(columnName);
        }

        // create/fill data rows  
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            IRow row = sheet.CreateRow(i + 1);
            for (int j = 0; j < dt.Columns.Count; j++)
            {

                ICell cell = row.CreateCell(j);
                String columnName = dt.Columns[j].ToString();
                cell.SetCellValue(dt.Rows[i][columnName].ToString());
            }
        }

        using (MemoryStream exportData = new MemoryStream())
        {
            Response.Clear();
            workbook.Write(exportData);
            
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "hr_apps.xlsx"));
            
            Response.BinaryWrite(exportData.ToArray());

            // in lieu of Response.End(), which is said to kill the memorystream and did return exceptions,
            // used the following.  Found the same three lines in different forums, one of which was:
            // https://stackoverflow.com/questions/20988445/how-to-avoid-response-end-thread-was-being-aborted-exception-during-the-exce
            Response.Flush(); // Sends all currently buffered output to the client.
            Response.SuppressContent = true;  // Gets or sets a value indicating whether to send HTTP content to the client.
            HttpContext.Current.ApplicationInstance.CompleteRequest(); // Causes ASP.NET to bypass all events and filtering in the HTTP pipeline chain of execution and directly execute the EndRequest event.
        }

    }
    catch (Exception ex)
    {
        // here so i can see the exception when debugging in VS since the above returns *nothing* to the browser
        Console.WriteLine(ex.Message);
    }
}

I've seen enough posts to know that NPOI will create a spreadsheet that's downloadable. So, obviously the error is on my side, but I haven't been able to find it. And maybe the code in my method is fine but the way I'm trying to consume it is not. Alas, I've not seen any examples of how methods are used, just the samples of code within.

Any suggestions would be greatly appreciated - especially if they help me get past this wall of frustration.

EDIT: I forgot to mention, Chrome reports the following at the conclusion of the operation:

Resource interpreted as Document but transferred with MIME type application/octet-stream: "{localhost URL/page}".

None of my Google searches resulted anything close to useful.

** RESOLVED **

Apparently the files have been created. For some reason, Edge never notified me to ask if I wanted to save/open. Instead, it had been saving the files in MicrosoftEdgeDownloads folder in AppData Local and doing so quietly. I'm not sure why I didn't get the notification since I have it set to notify me.

In my defense, I rarely use Edge.

  • Just an idea cause i don't use NPOI - have you tried to write to workbook directly to the response body? Like `workbook.Write(Response.Body);` ?! – Yosh May 18 '21 at 20:28
  • @Yosh, thanks for responding but that wouldn't render the desired results, which is to create a downloaded Excel file. Additionally, the Response object does not have a definition for .Body. – Larry Steele May 19 '21 at 00:52
  • Sorry, my fault, I had the Asp.net Core HttpResponse Object in mind. Glad you resolved the issue! :) – Yosh May 19 '21 at 16:46

0 Answers0