All geek questions in one place

Download excel file

My problem is that I have a lot of information in the database, and ideally I would like to extract it from an excel file to load my client.

I use the NPOI library, which is excellent and is already implemented in a console application on the system, however this was not written by me.

What happens at the moment when I click ActionLink for my controller, only "System.IO.MemoryStream" is displayed on a blank white page ..

Obviously, this is not the desired effect. The way I would like is when the user clicks on the link, the report is loaded.

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 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 here is my controller, which, it seems to me, is in my problem:

  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; }
+4
source share
2 answers

If this is your action method in your controller, you can return a FileResult by returning a FileStreamResult that 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", }; }
+7
source

2 refers to

  • too many lines can cause memory problems.
  • when u declares a new variable such as dataRow.CreateCell. because u causes COM interaction, try using every u object. obj.Dispose (); and the marshal. Failure (obj); I don't think NPOI can handle this.
+2
source

Source: https://habr.com/ru/post/1338121/

More articles:


All Articles