none
Excel File getting Corrupted while updating the Footer in OpenXML RRS feed

  • Question

  • Hi,

    I am using openXML 2.5 & SharePoint 2013.

    I am getting the File content from SharePoint & trying to update the footer and upload again in SharePoint. But After upload I am unable to open the file saying corrupted or file format not correct.

    public static byte[] UpdateExcelFooter(byte[] binFileContent)
            {
    
                try
                {
                    Stream streamFileContent = new MemoryStream(binFileContent);
    
    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(streamFileContent, true))
                    {
    
                        //Get the Set of Lists to update the Footer Section
                        var excelSheetList = doc.WorkbookPart.Workbook.Descendants<Sheet>().ToList();
                        //Loop through the Sheets to Update the Footer Section
                        foreach (var theSheet in excelSheetList)
                        {
                            if (theSheet != null)
                            {
                                //Get the Worksheet Part
                                WorksheetPart wsPart = (WorksheetPart)(doc.WorkbookPart.GetPartById(theSheet.Id));
                                if (wsPart != null)
                                {
                                    //Get the Worksheet
                                    Worksheet workSheet = wsPart.Worksheet;
                                    if (workSheet != null)
                                    {
                                        HeaderFooter hf = workSheet.Descendants<HeaderFooter>().FirstOrDefault();
                                        if (hf != null)
                                        {
                                            //Sample Footer for Excel
                                            //string footerText = @"&L&""Verdana,Italic\""&7Doc. No.:  F/1987 Version No.:  1     Revision  No.:  0     Date of Issue: 13.10.16 &C&""Verdana,Italic""&9 INTERNAL&7            &R&""Verdana,Italic""&7&P of &N";
    
                                            //Get the Original Existing Footer
                                            string footerText = hf.OddFooter.Text;
    
                                            hf.OddFooter = new OddFooter { Text = "New Footer" };
                                        }
                                    }
                                }
                            }
                        }
                        doc.WorkbookPart.Workbook.Save();
                        
                        byte[] updatedFileContent = new byte[streamFileContent.Length];
                        streamFileContent.Read(updatedFileContent, 0, (int)streamFileContent.Length);
    
    
                    }
    }
                catch (Exception ex)
                {
                    //Log Exception in case of any issues
                }
                //Return the Updated Filte content after updating the Footer
                return updatedFileContent;
            }

    Using the byte[] returned to upload in SharePoint 2013 Library. Its uploading for showing its corrupted

    I am new to OpenxML & it seems its getting corrupted during footer update.

    The above code working fine if I am passing the file path instead of using stream to open the document.

    Need an urgent help on this, please post your comments. 

    Thanks 

    Snehasis Samal

    Tuesday, July 4, 2017 3:01 PM

Answers

  • Hi,

    I got the solution. Just added below line for the memory stream, & it worked like a charm

    Added 
    
    streamFileContent.Seek(0, SeekOrigin.Begin);
    
    Before
    
    byte[] updatedFileContent1 = new byte[streamFileContent.Length];
    streamFileContent.Read(updatedFileContent1, 0, (int)streamFileContent.Length);

    Thanks All

    • Marked as answer by SSAMAL Tuesday, July 4, 2017 6:04 PM
    Tuesday, July 4, 2017 6:03 PM