How do I advance to the NextRecord on Microsoft.Office.Interlop.Word Mailmerge with C#?

Question

I have this method that "works", but only gives me one row of data. I used EPPlust to loop through the Excel file and calling the Word document template. The template is an 8.5 X 11 document with 21 labels. My goal is the fill the labels with information from the Excel file. I think I'm just missing a command to advance the next record. I've checked the Microsoft definition and it left me with more questions...and google has gotten me this far but I still need this little nugget of information to finish off this app.

Thanks,

  public static void MergeLabels(string labelDoc)
        {
            FileInfo fi = new FileInfo(labelDoc);
            string labelSave = labelDoc.Replace(".xlsx", ".docx");
            string labelTemplet = fi.DirectoryName.ToString() + "\\StockLabelsMerge.docx"; 
            using (ExcelPackage excelPackage = new ExcelPackage(fi))
            {    
                var application = new Microsoft.Office.Interop.Word.Application();
                var document = new Microsoft.Office.Interop.Word.Document();
                
                document = application.Documents.Add(Template: labelTemplet);


                foreach (var worksheetLoop in excelPackage.Workbook.Worksheets)
                {
                   // this.comboBox1.Items.Add(worksheet.Name);
            
               ExcelWorksheet workSheet = excelPackage.Workbook.Worksheets[worksheetLoop.Index];

                int colCount = workSheet.Dimension.End.Column; 
                int rowCount = workSheet.Dimension.End.Row; 
                string specieSize = string.Empty;
                string cabinet = string.Empty;
                string jobNumber = string.Empty;
            


                for (int row = 1; row <= rowCount; row++)
                {

                    if (workSheet.Cells[row, 1].Value != null)
                    {
                        jobNumber = workSheet.Cells[row, 1].Value.ToString();
                        if (workSheet.Cells[row, 2].Value != null)
                        {
                            cabinet = workSheet.Cells[row, 2].Value.ToString();
                            if (workSheet.Cells[row, 3].Value != null)
                            {
                                specieSize = workSheet.Cells[row, 3].Value.ToString();

                                foreach (Microsoft.Office.Interop.Word.Field label in document.Fields)
                                {
                                    if (label.Code.Text.Contains("F1"))
                                    {
                                        label.Select();
                                        application.Selection.TypeText(jobNumber);
                                    }
                                    else if (label.Code.Text.Contains("NoName"))
                                    {
                                        label.Select();
                                        application.Selection.TypeText(cabinet);
                                    }
                                    else if (label.Code.Text.Contains("NoName2"))
                                    {
                                        label.Select();
                                        application.Selection.TypeText(specieSize);


                                    }

                                        //next record?
                                      //  label.Next.Result;



                                }

                            }
                        }
                    }




                }
                   
                } 
               
                document.SaveAs2(FileName: labelSave);    
            }




        }

If someone knows what's missing, or even has a good suggestion, I would appreciate it.

Answer 1

it seems you loop through your Excel rows, fetch 3 data from each row and then you fill in 3 labels in the Word template. I think the issue is in the foreach for labels. You basically loop through each label (out of 21) and only fill the same 3 of them. What are the ids for the other 18? Another issue might be with the subsequent ifs. If any cell in the row is null, you won't hit the label filling code.