locked
Cannot add rows to Excel workbook, file corrupted RRS feed

  • Question

  • Hi,

    I created a template Excel file and used Open XML Productivity Tool for MS Office to generate the C# class that allows creating a workbook entirely from code. After this, I used the following code to add data rows to the created workbook and return the byte array (the workbook will be generated and downloaded when clicking on a button in an ASP.Net page), but when I download the generated file and try to open it, Excel 2010 says that the file is corrupted.


    Can you help me?

     

    public byte[] ExportWorkbook(IEnumerable<MyEntity> data)
            {
                MemoryStream stream = new MemoryStream();
                WorkbookTemplate document = new WorkbookTemplate();
                document.CreatePackage(stream);


                using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(stream, true))
                {
                    WorkbookPart workbook = spreadsheet.WorkbookPart;

                    WorksheetPart worksheet = workbook.WorksheetParts.First();
                    SheetData sheetData = worksheet.Worksheet.GetFirstChild<SheetData>();

                    int index = 10;

                    foreach (var item in data)
                    {
                        Row row = CreateRow(index, item);
                        index++;

                        sheetData.AppendChild(row);
                    }

                    spreadsheet.WorkbookPart.Workbook.Save();
                    spreadsheet.Close();
                   
                    return stream.ToArray();
                }
            }
    private Row CreateRow(int index, MyEntity item)
            {
                Row row = new Row();
                row.RowIndex = (UInt32) index;

                row.AppendChild(CreateTextCell("A", item.MyProperty, index));

                return row;
            }

            private Cell CreateTextCell(string column, string text, int index)
            {
                Cell cell = new Cell();
                cell.DataType = CellValues.String;
                cell.CellReference = column + index;
                cell.CellValue = new CellValue(text);

                return cell;
            }

     


    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, remember to "Mark as Answer".

    Se achou este post útil, por favor clique em "Votar como Útil". Se por um acaso respondeu sua dúvida, lembre de "Marcar como Resposta".


    Wednesday, December 7, 2011 11:07 AM

All replies

  • Hi Juliano,

    I'm trying to get the above code working, but following call to First() shows the member doesn't exist.

    WorksheetPart worksheet = workbook.WorksheetParts.First();

    Did you know this issue?

    Best Regards,


    Forrest Guo | MSDN Community Support | Feedback to us

    Friday, December 9, 2011 11:11 AM
  • this way it must work:

     

    WorksheetPart worksheet = workbook.Workbook.WorksheetParts.First();


    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, remember to "Mark as Answer".

    Se achou este post útil, por favor clique em "Votar como Útil". Se por um acaso respondeu sua dúvida, lembre de "Marcar como Resposta".

    Friday, December 9, 2011 12:17 PM
  • I solved my problem removing some formatting from my Excel workbook, but know I need to programmatically merge some cells from 800 rows. I made with the following code, but the process become much slower.

                    MergeCells mergeCells;
                    if (worksheet.Worksheet.Elements<MergeCells>().Count() > 0)
                    {
                        mergeCells = worksheet.Worksheet.Elements<MergeCells>().First();
                    }
                    else
                    {
                        mergeCells = new MergeCells();
    
                        // Insert a MergeCells object into the specified position.
                        if (worksheet.Worksheet.Elements<CustomSheetView>().Count() > 0)
                        {
                            worksheet.Worksheet.InsertAfter(mergeCells, worksheet.Worksheet.Elements<CustomSheetView>().First());
                        }
                        else if (worksheet.Worksheet.Elements<DataConsolidate>().Count() > 0)
                        {
                            worksheet.Worksheet.InsertAfter(mergeCells, worksheet.Worksheet.Elements<DataConsolidate>().First());
                        }
                        else if (worksheet.Worksheet.Elements<SortState>().Count() > 0)
                        {
                            worksheet.Worksheet.InsertAfter(mergeCells, worksheet.Worksheet.Elements<SortState>().First());
                        }
                        else if (worksheet.Worksheet.Elements<AutoFilter>().Count() > 0)
                        {
                            worksheet.Worksheet.InsertAfter(mergeCells, worksheet.Worksheet.Elements<AutoFilter>().First());
                        }
                        else if (worksheet.Worksheet.Elements<Scenarios>().Count() > 0)
                        {
                            worksheet.Worksheet.InsertAfter(mergeCells, worksheet.Worksheet.Elements<Scenarios>().First());
                        }
                        else if (worksheet.Worksheet.Elements<ProtectedRanges>().Count() > 0)
                        {
                            worksheet.Worksheet.InsertAfter(mergeCells, worksheet.Worksheet.Elements<ProtectedRanges>().First());
                        }
                        else if (worksheet.Worksheet.Elements<SheetProtection>().Count() > 0)
                        {
                            worksheet.Worksheet.InsertAfter(mergeCells, worksheet.Worksheet.Elements<SheetProtection>().First());
                        }
                        else if (worksheet.Worksheet.Elements<SheetCalculationProperties>().Count() > 0)
                        {
                            worksheet.Worksheet.InsertAfter(mergeCells, worksheet.Worksheet.Elements<SheetCalculationProperties>().First());
                        }
                        else
                        {
                            worksheet.Worksheet.InsertAfter(mergeCells, worksheet.Worksheet.Elements<SheetData>().First());
                        }
                    }
    
                    for (int i = 1; i <= data.Count(); i++)
                    {
                        MergeCell mergeCell = new MergeCell()
                        {
                            Reference = new StringValue(String.Format("A{0}:B{0}", i))
                        };
                        mergeCells.Append(mergeCell);
                    }
    

    Any thoughts?


    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, remember to "Mark as Answer".

    Se achou este post útil, por favor clique em "Votar como Útil". Se por um acaso respondeu sua dúvida, lembre de "Marcar como Resposta".

    Friday, December 9, 2011 12:22 PM
  • Hi, I try to get experienced people on this thread, this may take some time, appreciate your patience.

    Best Regards,


    Forrest Guo | MSDN Community Support | Feedback to us

    Monday, December 12, 2011 3:32 AM
  • I'm not sure I understand the problem. Are you saying that the code works but it runs slow or is something else going on?

    Will Buffington
    Microsoft Excel Support

    Monday, December 12, 2011 5:39 PM
  • I don't know if the code really works, because it runs too much slow and for 800 rows I always have to cancel it.

    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, remember to "Mark as Answer".

    Se achou este post útil, por favor clique em "Votar como Útil". Se por um acaso respondeu sua dúvida, lembre de "Marcar como Resposta".

    Monday, December 12, 2011 6:31 PM
  • Server side automation of Excel isn't a supported scenario, unfortunately. This KB Article might help point you in the right direction for what's going on:

    Considerations for server-side Automation of Office
    http://support.microsoft.com/kb/257757

    "Office applications assume that they are being run under an interactive desktop. In some circumstances, applications may need to be made visible for certain Automation functions to work correctly. If an unexpected error occurs, or if an unspecified parameter is needed to complete a function, Office is designed to prompt the user with a modal dialog box that asks the user what the user wants to do. A modal dialog box on a non-interactive desktop cannot be dismissed. Therefore, that thread stops responding (hangs) indefinitely. Although certain coding practices can help reduce the likelihood of this issue, these practices cannot prevent the issue entirely. This fact alone makes running Office Applications from a server-side environment risky and unsupported."

    But please read the rest of the article which contains a paragraph on alternatives to server side automation.

    Wednesday, December 14, 2011 9:59 PM