Creating excel document as attachment on a sharepoint list

Question

Hi,

I've got some problems adding a excel document as attachment on a custom list. I've made an eventreceiver which collects data from other lists and puts the data into an excel document.

What i've tried to do to correct the problem:

• tested CreateContentRow(index, item) in a separate console application (works)
• checked if theres something wrong with fetching information from other lists (works)
• tried saving to a document library (the file gets saved without content)
• tried to open the xlsx document to se if there's something wrong with the xml (no custom xml added).

The code works just fine, but the document is saved but it's identical to the template, no content added.

using (var memory = new MemoryStream())
                {

                    var binary = template.OpenBinary();
                    memory.Write(binary, 0, binary.Length);

                    using (var document = SpreadsheetDocument.Open(memory, true))
                    {
                        var workbookPart = document.WorkbookPart;

                        var worksheetparts = workbookPart.WorksheetParts;

                        var worksheetpart = worksheetparts.FirstOrDefault();

                        var sheetData = worksheetpart.Worksheet.GetFirstChild<SheetData>();

                        var index = 2;
                        foreach (var item in items)
                        {
                            var row = CreateContentRow(index, item);
                            index++;

                            sheetData.AppendChild(row);
                        }
                        properties.ListItem.Attachments.Add("name" + string.Format("{0:yyyy-MM-dd_HHmmss}", DateTime.Now) + ".xlsx", memory.ToArray());
                        properties.ListItem.Update();
                    }
                }

I'm using the same approach on another list where i generate .docx documents there it works just fine. Is there a big difference in how i should save the document depending on if its .xlsx or .docx ?

When i debug the cells and rows are added to the sheetdata, but it does not get saved. Any ideas on how to fix this ?

Answer 1

In order to get the content saved i needed to add save statements.

wspart.Worksheet.Save();
document.WorkbookPart.Workbook.Save();

When i added the lines above before adding the attachemnt everything worked as it should.