下载Excel文件

Question

我的问题是我有很多数据库中的信息，理想情况下，我希望将它们从数据库中提取到Excel文件中，以供客户下载。

我正在使用很棒的NPOI库，并且已经在系统中实现了控制台应用程序，但这不是我编写的。

目前发生的情况是，当我单击控制器的ActionLink时，会显示一个空白的白色页面，什么也没有，只显示“System.IO.MemoryStream”。

显然，这不是期望的效果。我希望用户单击链接时，报告可以自动下载。

以下是报告类：

    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;
        }
    }
}

这是我的控制器，我认为这里是我的问题所在：

    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;
    }

- 109221793

Answer 1

如果这是你的控制器中的操作方法，你可以通过返回FileStreamResult来返回一个FileResult，它在其构造函数中需要传入stream和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",
        };
}

- hunter

---

谢谢，这个方法很有效！还有一个问题。当我下载这个文件时，它下载为未知文件类型，而不是 Excel 文件。但如果我点击“打开方式”，然后选择 Excel，它就可以正常打开。有没有办法让它直接下载为 Excel 文件？ - 109221793

听起来你没有将.xls扩展名与Excel关联起来。 - Tim Jones

Answer 2

2个问题

1. 行数过多可能会导致内存问题。
2. 当您声明新变量，例如dataRow.CreateCell时。因为调用了COM互操作，请尝试处理您使用的每个对象。obj.Dispose();和Marshal.Release(obj);我不认为NPOI会管理它。

- Bonshington