Downloading an excel file

Question

My issue is I have a lot of information in a database, and ideally I'd like to pull it from that into an excel file for my client to download.

I'm using the NPOI library which is great, and is already implemented in a console application in the system, however this was not written by me.

What happens currently, when I click on the ActionLink for my controller, a blank white page displays saying nothing but "System.IO.MemoryStream"..

Obviously this isn't the desired effect. The way I would like it is when the user clicks on the link, the report downloads.

Here is the class for the report:

    public class RepairReporting
    {
        public Stream GenerateRepairFile(List<Int64> itemIds)
        {
            // Getting the complete workbook...
            //
            MemoryStream ms = new MemoryStream();
            HSSFWorkbook templateWorkbook = new HSSFWorkbook();

            // Create a worksheet by it's name.
            //
            HSSFSheet sheet = templateWorkbook.CreateSheet("Repairs Report");
            sheet.ForceFormulaRecalculation = true;



            HSSFRow dataRow = sheet.CreateRow(0);

            HSSFCell cell = dataRow.CreateCell(0);
            cell.SetCellValue("Repairs");


            cell = dataRow.CreateCell(1);
            cell.SetCellValue(DateTime.Now);

            // Build the header row
            //
            dataRow = sheet.CreateRow(1);

            string[] colHeaders = new string[]{ "Product Code",
                                                "Product Name",
                                                "Customer", 
                                                "Date Submitted For Repair",
                                                "Date Sent For Repair", 
                                                "Expected Release Date",    
                                                "Estimated Cost",   
                                                "Actual Cost",  
                                                "Total Repair Price (END PRICE)"
                                                };

            int colPosition = 0;

            // Write all the headers out.
            //
            foreach (string colHeader in colHeaders)
            {
                cell = dataRow.CreateCell(colPosition++);
                cell.SetCellValue(colHeader);
            }

            // Build the item rows.
            //
            int row = 2;

            foreach (Int64 itemId in itemIds)
            {
                using (ModelContainer ctn = new ModelContainer())
                {

                    Item currentItem = (from t in ctn.Items
                                          where t.ItemID == itemId && t.RepairSelection == true
                                          select t).First();


                    dataRow = sheet.CreateRow(row++);
                    colPosition = 0;

                    cell = dataRow.CreateCell(colPosition++);
                    cell.SetCellValue(currentItem.ProductCode);

                    cell = dataRow.CreateCell(colPosition++);
                    cell.SetCellValue(currentItem.Product);

                    cell = dataRow.CreateCell(colPosition++);
                    cell.SetCellValue(currentItem.Customer.Name);


                    cell.SetCellValue(currentItem.Repair.SubmissionDate.Value.ToString("MM/dd/yyyy"));


                    if (currentItem.Repair.SentForConversion != null)
                    {
                        cell = dataRow.CreateCell(colPosition++);
                        cell.SetCellValue(currentItem.Repair.SentForRepair.Value.ToString("MM/dd/yyyy"));
                    }
                    else
                    {
                        colPosition++;
                        colPosition++;
                    }

                    if (currentItem.Repair.ReleaseDate != null)
                    {
                        cell = dataRow.CreateCell(colPosition++);
                        cell.SetCellValue(currentItem.Repair.ReleaseDate.Value.ToString("MM/dd/yyyy"));
                    }
                    else
                    {
                        colPosition++;
                        colPosition++;
                    }


                    if (currentItem.Repair.CostEstimation != null)
                    {
                        cell = dataRow.CreateCell(colPosition++);
                        cell.SetCellValue(currentItem.Repair.CostEstimation.Value.ToString());
                    }
                    else
                    {
                        colPosition++;
                        colPosition++;
                    }

                    if (currentItem.Repair.ActualCost != null)
                    {
                        cell = dataRow.CreateCell(colPosition++);
                        cell.SetCellValue(currentItem.Repair.ActualCost.Value.ToString());
                    }
                    else
                    {
                        colPosition++;
                        colPosition++;
                    }

                    if (currentTitle.Repair.TotalRepairPrice != null)
                    {
                        cell = dataRow.CreateCell(colPosition++);
                        cell.SetCellValue(currentItem.Repair.TotalRepairPrice.Value.ToString());
                    }
                    else
                    {
                        colPosition++;
                        colPosition++;
                    }

                }

            }


            templateWorkbook.Write(ms);
            ms.Position = 0;

            return ms;
        }
    }
}

And then here is my controller, which I think is where my issue lies:

    public Stream repairReport()
    {
        ModelContainer ctn = new ModelContainer();

        List<Title> items = null;

        var itemObjects = ctn.Items.Where(t => t.RepairSelection == true)
            .Select(t =>t);

        items = itemObjects.ToList();

        RepairReporting rtp = new RepairReporting();


        List<long> itemIDs = items.Select(t => t.ItemID).ToList();

        Stream repairReport = rtp.GenerateRepairFile(itemIDs);

        return repairReport;
    }

Answer 1

If this is your action method in your Controller you can return a FileResult by returning a FileStreamResult which takes a stream in its constructor along with a ContentType

public FileResult RepairReport()
{
    ModelContainer ctn = new ModelContainer();

    List<Title> items = ctn.Items.Where(t => t.RepairSelection == true)
        .Select(t =>t).ToList();

    RepairReporting rtp = new RepairReporting();

    List<long> itemIDs = items.Select(t => t.ItemID).ToList();

    Stream repairReport = rtp.GenerateRepairFile(itemIDs);

    return new FileStreamResult(repairReport, "application/ms-excel")
        {
            FileDownloadName = "RepairReport.xls",
        };
}

Answer 2

2 concerns

1. too many rows might cause memory problem.
2. when u declare new variable such as dataRow.CreateCell. because u calling COM interop, try to dispose every object u use. obj.Dispose(); and Marshal.Release(obj); i dont think NPOI manage that.