system.invalidoperationexception Sequence contains no elements openxml

Question

User-849204934 posted

I wonna achieve something like this , using OpenXML to fetch data from SQL Server and automatically populate an MS word Document.

I have content controls which look like this 

I have this worry, it fires this exception here system.invalidoperationexception Sequence contains no elements

I am quite new to OpenXML so kindly do not get really annoyed with me.

My code looks like this

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Wordprocessing;
using DocumentFormat.OpenXml.Drawing;
using System.Data.SqlClient;
using System.IO;

namespace DesktopSignaturetest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string constring = @"Data Source=DESKTOP-9CM4N5S\SQLEXPRESS;Initial Catalog=SignatureBox2;User ID=sa;Password=123456;";
            using (SqlConnection con = new SqlConnection(constring))
            {
                con.Open();
                string q = "select * from SignatureBox_DB where StaffID = @StaffID";
                using (SqlCommand cmd = new SqlCommand(q, con))
                {
                    cmd.Parameters.AddWithValue("@StaffID", textBox1.Text);
                    using (SqlDataReader rd = cmd.ExecuteReader())
                    {
                        try
                        {
                            if (rd.Read())
                            {
                                string fileName = @"C:\Users\emi\Desktop\test.jpg";
                                byte[] imageBytes = Convert.FromBase64String(rd["SignatureBase64"].ToString());
                                string fullname = rd["FullName"].ToString();
                                string designation = rd["Designation"].ToString();
                                MemoryStream ms = new MemoryStream(imageBytes, 0, imageBytes.Length);
                                ms.Write(imageBytes, 0, imageBytes.Length);
                                Image image = Image.FromStream(ms, true, true);
                                image.Save(fileName, System.Drawing.Imaging.ImageFormat.Jpeg);

                                using (WordprocessingDocument doc = WordprocessingDocument.Open(@"C:\Users\emi\Desktop\MSWordTest.docx", true))
                                {
                                    MainDocumentPart mainPart = doc.MainDocumentPart;
                                    SdtBlock block = mainPart.Document.Body.Descendants<SdtBlock>().Where
                                    (r => r.SdtProperties.GetFirstChild<Tag>().Val == "Name").Single();

                                    SdtBlock desg = mainPart.Document.Body.Descendants<SdtBlock>().Where
                                    (r => r.SdtProperties.GetFirstChild<Tag>().Val == "Designation").Single();


                                    SdtBlock cc = doc.MainDocumentPart.Document.Body.Descendants<SdtBlock>()
                                    .FirstOrDefault(c =>
                                    {
                                        SdtProperties p = c.Elements<SdtProperties>().FirstOrDefault();
                                        if (p != null)
                                        {
                                            // Is it a picture content control?
                                            SdtContentPicture pict =
                                                p.Elements<SdtContentPicture>().FirstOrDefault();
                                            // Get the alias.
                                            SdtAlias a = p.Elements<SdtAlias>().FirstOrDefault();
                                            if (pict != null && a.Val == "Signature")
                                                return true;
                                        }
                                        return false;
                                    });

                                    string embed = null;
                                    if (cc != null)
                                    {
                                        Drawing dr = cc.Descendants<Drawing>().FirstOrDefault();
                                        if (dr != null)
                                        {
                                            Blip blip = dr.Descendants<Blip>().FirstOrDefault();
                                            if (blip != null)
                                                embed = blip.Embed;
                                        }
                                    }
                                    if (embed != null)
                                    {
                                        IdPartPair idpp = doc.MainDocumentPart.Parts
                                            .Where(pa => pa.RelationshipId == embed).FirstOrDefault();
                                        if (idpp != null)
                                        {
                                            DocumentFormat.OpenXml.Drawing.Text name = block.Descendants<DocumentFormat.OpenXml.Drawing.Text>().Single();
                                            DocumentFormat.OpenXml.Drawing.Text desgx = desg.Descendants<DocumentFormat.OpenXml.Drawing.Text>().Single();
                                            name.Text = fullname;
                                            desgx.Text = designation;
                                            ImagePart ip = (ImagePart)idpp.OpenXmlPart;
                                            using (FileStream fileStream =
                                                File.Open(fileName, FileMode.Open))
                                                ip.FeedData(fileStream);
                                            MessageBox.Show("Done!");
                                        }
                                    }
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.ToString());
                        }
                        finally
                        {
                            con.Close();
                        }
                    }
                }
            }
        }
    }
}

And it points to line 54 , and line 54 is looking at something like this :

SdtBlock block = mainPart.Document.Body.Descendants<SdtBlock>().Where
(r => r.SdtProperties.GetFirstChild<Tag>().Val == "Name").Single(); //<------------- Error here

Please what exactly am i getting wrong?

Answer 1

User665608656 posted

Hi Samriz,

I have this worry, it fires this exception here system.invalidoperationexception Sequence contains no elements

This error shows that in this query it can not find no elements , so you cannot use Single() directly.

I recommend you that confirm your word has the content controls named "Name" and "Designation", otherwise the SdtBlock collection will be null.

To avoid this error, you can change your code like this:

  SdtBlock block = mainPart.Document.Body.Descendants<SdtBlock>().Where
                (r => r.SdtProperties.GetFirstChild<Tag>().Val == "Name").FirstOrDefault(); 

Here is a link about how to insert text into your content control in word by using openxml in c#, you could refer to it:

https://stackoverflow.com/a/36623224

Best Regards,

YongQing.