0

I have datatable that I'm populating with data and passing through to a method which is supposed to write the data to a memory stream and save it as an Excel spreadsheet. The datatable is correctly populated, but when the memorystream writes it, the capacity of the memorystream is 0. It's as though it just doesn't write anything. Could this be caused by some sort of memory leak? Below is my code that I use to write the workbook (populated by the datatable) using the memorystream

    public static MemoryStream Grid_Export_Excel(DataTable data)
    {
        try
        {
            var workbook = new XSSFWorkbook();
            var sheet = workbook.CreateSheet();
            var headerRow = sheet.CreateRow(0);

            // Set the column names in the header row
            for (int i = 0; i < data.Columns.Count; i++)
                headerRow.CreateCell(i).SetCellValue(data.Columns[i].ColumnName);

            // Freeze the header row so that it's not scrolled
            sheet.CreateFreezePane(0, 1, 0, 1);

            for (int r = 0; r < data.Rows.Count; r++)
            {
                // Create a new row
                var row = sheet.CreateRow(r + 1);

                // Get the DataRow
                var dataRow = data.Rows[r];

                for (int c = 0; c < data.Columns.Count; c++)
                {
                    // Get the fieldname
                    var fieldname = data.Columns[c].ColumnName;

                    // Make sure that the field exists in the datarow
                    if (!dataRow.Table.Columns.Contains(fieldname))
                        continue;

                    // Create the cell in the row
                    row.CreateCell(c).SetCellValue(dataRow[fieldname].ToString());
                }
            }

            // Write the workbook to a stream and return as FileStreamResult
            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            return ms;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
    }
Dmitry Bychenko
  • 180,369
  • 20
  • 160
  • 215
user1397978
  • 255
  • 1
  • 7
  • 24
  • Are you missing something in the _workbook.Write_ line? Where is supposed to write to? – Steve Sep 12 '18 at 08:21
  • Memoryleaks do not lose data but memory space. How do you connect the stream with your data?? Try `workbook.Write(ms);` – TaW Sep 12 '18 at 08:21
  • You are not writing to the memory stream at all, which is why it contains no data. – Matthew Watson Sep 12 '18 at 08:22
  • maybe ms.Flush() ? – Nick Sep 12 '18 at 08:22
  • Apologies - I have updated my code. I am writing to the memorystream (workbook.Write(ms)) Where should I place ms.flush()? – user1397978 Sep 12 '18 at 08:25
  • Side note: `catch (Exception ex) { throw new Exception(ex.Message); }` is totally *useless*, drop it – Dmitry Bychenko Sep 12 '18 at 08:41
  • `MemoryStream.Flush()` [does not actually do anything](https://referencesource.microsoft.com/#mscorlib/system/io/memorystream.cs,996367c59ff4f5de) so calling it isn't going to help. Your problem would appear to be an issue with `workbook.Write()` rather than anything to do with not flushing the `MemoryStream`. – Matthew Watson Sep 12 '18 at 10:11
  • I seemed to have figured out the problem. There is apparently dependancy between NPOI nuget packages and SharpZipLib. SharpZipLib was using an older version while NPOI a later. This is what caused the data not to be pulled through and written – user1397978 Sep 13 '18 at 11:22

0 Answers0