locked
VB.Net - How Can I Create Multiple Word Documents? RRS feed

  • Question

  • All,

    I have a simple two-tier Lightswitch application in VB.NET where the users populate the screen data, save the data and then can export the data to bookmarks on a word document template with the document saving automatically to a specific folder. This works well for the users; however, I would like to be able to loop through all of the records and execute multiple word documents instead of waiting for the users to manually to do this. Is this functionality possible? I’m thinking this may be possible on the server side to iterate through the saved records with a loop to create multiple word documents, but I’m stumped on how to make this happen. I’ve searched the MSDN forums,  and other internet resources, but came up empty. If anyone can help me out with the code, or point me out to a resource, I’d greatly appreciate it. 

    Regards,

    Jeff


    Friday, June 24, 2016 5:45 PM

Answers

  • Hi Jeff,

    I am pretty busy so can't answer your question using VB or specific to your requirement. I can give you a code snippet that uses the Syncfusion PDF and DocIO libraries to do document manipulation server side. You can get a free community license for Syncfusion here. This is from a prototype app so the code is NOT what I consider production quality.

    You can use your favorite C# to VB converter if you can't read C# and use this code as the basis for what you want to do.

    In essence you are using what's called a "Commanding Pattern", using the Inserting and Updating events to trigger your word document creation server side.

    This code snippet is for a proof of concept application that allows truck drivers to take pictures of their truck's defects using a cell phone. They can then record that a defect is fixed by adding photo's of their invoices. A PDF document is created on Google Drive that is updated each time the defect is updated. The PDF document consists of a cover page which is a Word Document that is Mail Merged with N+ pictures detailing the defect.

    The routine is an Upsert routine that is called from the TruckDefects Inserting and Updating events.

    An abbreviated logic flow is as follows:

    1. Ensure that there is a folder created on Google Drive using a template/mask.

    2. Open a Word document that was added to the solution a Resource. This is the Merge Template document.

    3. A merge record is created from the various entities and then merged using MergeNestedGroup as there are multiple master detail sections.

    4. The merged document (cover page) is then converted to PDF.

    5. All attachment images are resized, rotated to portrait then appended  to the cover page PDF.

    6 The document is saved to Google Drive.

    Note that once the image is appended to the PDF, a thumbnail is created for the database and the algorithm, on update just opens the existing PDF and replaces the cover page and appends the image attachments. I just wanted to mention this fact as it complicates the code a little.

    I hope this helps a little and good luck!

    		private void UpsertDefectDrive(TruckDefect defect)
    		{
    			try
    			{
    				var drive = new GoogleDrive("XXXXX.gserviceaccount.com", LightSwitchApplication.Resources.MyCert, "test", "CERT_THUMBPRINT");
    
    				if (string.IsNullOrEmpty(defect.GoogleFolderId))
    				{
    					var recordType = DataWorkspace.ApplicationData.GetDefectRecordType();
    					var folder = recordType.RecordFolder.FolderMask;
    					folder = folder.Replace("$TruckNumber", defect.Truck.Number.ToString());
    					folder = folder.Replace("$DriverFullName", defect.Truck.Driver.FullName);
    					folder = folder.Replace("$DriverLicense", defect.Truck.Driver.DriverLicense);
    					defect.GoogleFolderId = drive.EnsurePath(folder);
    				}
    
    				using (var documentStream = new MemoryStream(LightSwitchApplication.Resources.defect))
    				{
    					var document = new WordDocument(documentStream);
    
    					document.MailMerge.MergeField += (s, e) =>
    					{
    						if (e.FieldName == "Name" && defect.TruckDefectItem.Severity == "MAJOR")
    							e.CharacterFormat.TextBackgroundColor = Color.FromArgb(64, 255, 0, 0);
    
    						if (e.FieldName == "Severity" && defect.TruckDefectItem.Severity == "MAJOR")
    							e.CharacterFormat.TextBackgroundColor = Color.FromArgb(64, 255, 0, 0);
    
    						if (e.FieldName == "Category" && defect.TruckDefectItem.Severity == "MAJOR")
    							e.CharacterFormat.TextBackgroundColor = Color.FromArgb(64, 255, 0, 0);
    					};
    
    					document.MailMerge.MergeImageField += (s, e) =>
    					{
    						if (e.FieldName == "RepairedSignature" && defect.RepairedSignature != null)
    						{
    							var imageStream = new MemoryStream(defect.RepairedSignature);
    							e.Image = System.Drawing.Image.FromStream(imageStream);
    						}
    					};
    
    					dynamic merge = new ExpandoObject();
    					merge.Signature = null;
    					merge.ForDate = defect.ForDate.ToString();
    					merge.Severity = defect.TruckDefectItem.Severity;
    					merge.Category = defect.TruckDefectItem.Category.Name;
    					merge.Name = defect.TruckDefectItem.Name;
    					merge.Repaired = defect.Repaired.ToString();
    					merge.RepairedAt = defect.RepairedAt == null ? "" : defect.RepairedAt.Value.LocalDateTime.ToString();
    					merge.RepairedSignature = defect.RepairedSignature;
    					merge.CreatedBy = defect.CreatedBy;
    					merge.Created = defect.Created == null ? "" : defect.Created.Value.LocalDateTime.ToString();
    					merge.ModifiedBy = defect.ModifiedBy;
    					merge.Modified = defect.Modified == null ? "" : defect.Modified.Value.LocalDateTime.ToString();
    
    					merge.Notes = new List<ExpandoObject>();
    					if (defect.Notes.Count() > 0)
    					{
    						foreach (var note in defect.Notes.OrderBy(n => n.Created))
    						{
    							dynamic noteItem = new ExpandoObject();
    							noteItem = new ExpandoObject();
    							noteItem.Note_Created = note.Created.Value.LocalDateTime.ToString();
    							noteItem.Note_Text = note.Text;
    							merge.Notes.Add(noteItem);
    						}
    					}
    					else
    					{
    						dynamic noteItem = new ExpandoObject();
    						noteItem.Note_Created = "N/A";
    						noteItem.Note_Text = "No notes";
    						merge.Notes.Add(noteItem);
    					}
    
    					merge.Attachments = new List<ExpandoObject>();
    					if (defect.RecordAttachments.Count() > 0)
    					{
    						foreach (var attachment in defect.RecordAttachments.OrderBy(ra => ra.Created))
    						{
    							dynamic attachmentItem = new ExpandoObject();
    							attachmentItem = new ExpandoObject();
    							attachmentItem.Attachment_Created = attachment.Created.Value.LocalDateTime.ToString();
    							attachmentItem.Attachment_Text = attachment.Description;
    							merge.Attachments.Add(attachmentItem);
    						}
    					}
    					else
    					{
    						dynamic attachmentItem = new ExpandoObject();
    						attachmentItem.Attachment_Created = "N/A";
    						attachmentItem.Attachment_Text = "No attachments";
    						merge.Attachments.Add(attachmentItem);
    					}
    
    					var mergeList = new List<ExpandoObject>();
    					mergeList.Add(merge);
    
    					var mergeDataTable = new MailMergeDataTable("Defect", mergeList);
    
    					document.MailMerge.ExecuteNestedGroup(mergeDataTable);
    
    					var pdfConverter = new DocToPDFConverter();
    					var pdfDocument = pdfConverter.ConvertToPDF(document);
    					pdfDocument.DocumentInformation.Subject = $"Cover={pdfDocument.Pages.Count}";
    
    					if (!string.IsNullOrEmpty(defect.GoogleFileId))
    					{
    						var file = drive.DownloadFile(defect.GoogleFileId);
    						var googlePdf = new PdfLoadedDocument(file);
    						var subject = googlePdf.DocumentInformation.Subject;
    						if (subject.Contains("Cover="))
    						{
    							var numberPages = int.Parse(subject.Replace("Cover=", ""));
    							while (numberPages > 0)
    							{
    								googlePdf.Pages.RemoveAt(0);
    								--numberPages;
    							}
    
    						}
    
    						if (googlePdf.Pages.Count > 0)
    							pdfDocument.Append(googlePdf);
    					}
    
    					if (defect.RecordAttachments.Count() > 0)
    					{
    						foreach (var attachment in defect.RecordAttachments)
    						{
    							if (!attachment.Appended)
    							{
    								var page = pdfDocument.Pages.Add();
    								page.Section.PageSettings.Margins.All = 10;
    
    								var attachmentStream = new MemoryStream(attachment.Body);
    								var image = new PdfBitmap(attachmentStream);
    								float imageWidth = image.Width;
    								float imageHeight = image.Height;
    								float pageHeight = page.Graphics.ClientSize.Height;
    								float pageWidth = page.Graphics.ClientSize.Width;
    								float shrinkFactor;
    								var rotate = false;
    
    								if (imageWidth > imageHeight)
    								{
    									rotate = true;
    									imageWidth = image.Height;
    									imageHeight = image.Width;
    								}
    
    								if (image.Width > pageWidth)
    								{
    									shrinkFactor = imageWidth / pageWidth;
    									imageWidth = pageWidth;
    									imageHeight = imageHeight / shrinkFactor;
    								}
    
    								if (imageHeight > pageHeight)
    								{
    									shrinkFactor = imageHeight / pageHeight;
    									imageHeight = pageHeight;
    									imageWidth = imageWidth / shrinkFactor;
    								}
    
    								float x = (pageWidth - imageWidth) / 2;
    								float y = (pageHeight - imageHeight) / 2;
    
    								image.Quality = 75;
    
    								var status = page.Graphics.Save();
    
    								if (rotate)
    								{
    									page.Graphics.RotateTransform(90);
    									page.Graphics.DrawImage(image, y, -imageWidth - x, imageHeight, imageWidth);
    								}
    								else
    								{
    									page.Graphics.DrawImage(image, x, y, imageWidth, imageHeight);
    								}
    
    								page.Graphics.Restore(status);
    
    								var thumbnail = page.ExtractImages()[0].GetThumbnailImage(image.Height / 10, image.Width / 10, null, IntPtr.Zero);
    								using (var thumbnailStream = new MemoryStream())
    								{
    									thumbnail.Save(thumbnailStream, ImageFormat.Jpeg);
    									thumbnailStream.Seek(0, SeekOrigin.Begin);
    									thumbnailStream.Read(attachment.Body, 0, (int)thumbnailStream.Length);
    								}
    
    								attachment.Appended = true;
    							}
    						}
    					}
    
    					using (var pdfStream = new MemoryStream())
    					{
    						pdfDocument.Compression = PdfCompressionLevel.Best;
    						pdfDocument.Save(pdfStream);
    						pdfStream.Seek(0, SeekOrigin.Begin);
    
    						var description = new StringBuilder();
    						description.AppendLine($"Repaired: {defect.Repaired}");
    						description.AppendLine($"Severity: {defect.TruckDefectItem.Severity}");
    						description.AppendLine($"Category: {defect.TruckDefectItem.Category.Name}");
    						description.AppendLine($"Defect: {defect.TruckDefectItem.Name}");
    
    						if (string.IsNullOrEmpty(defect.GoogleFileId))
    							defect.GoogleFileId = drive.InsertFile(defect.GoogleFolderId, $"{defect.Number}", "application/pdf", pdfStream, description.ToString());
    						else
    							drive.UpdateFile(defect.GoogleFileId, pdfStream, description.ToString());
    					}
    				}
    			}
    
    			catch (Exception ex)
    			{
    				Debug.WriteLine(ex.ToString());
    				throw;
    			}
    		}
    
    		partial void TruckDefects_Inserting(TruckDefect entity)
    		{
    			var driver = entity.Truck.Driver;
    			var truck = entity.Truck;
    
    			var number = 1;
    			var lastDefect = DataWorkspace.ApplicationData.TruckDefects.Where(td => td.Truck.Number == truck.Number).OrderByDescending(td => td.Id).Take(1).Execute().SingleOrDefault();
    			if (lastDefect != null)
    				number = int.Parse(lastDefect.Number.Replace($"D{entity.Truck.Number}", "")) + 1;
    
    			entity.Number = $"D{entity.Truck.Number}{number:0000000}";
    
    			UpsertDefectDrive(entity);
    		}
    
    		partial void TruckDefects_Updating(TruckDefect entity)
    		{
    			UpsertDefectDrive(entity);
    		}


    Saturday, June 25, 2016 3:43 AM

All replies

  • Hi Jeff,

    I am pretty busy so can't answer your question using VB or specific to your requirement. I can give you a code snippet that uses the Syncfusion PDF and DocIO libraries to do document manipulation server side. You can get a free community license for Syncfusion here. This is from a prototype app so the code is NOT what I consider production quality.

    You can use your favorite C# to VB converter if you can't read C# and use this code as the basis for what you want to do.

    In essence you are using what's called a "Commanding Pattern", using the Inserting and Updating events to trigger your word document creation server side.

    This code snippet is for a proof of concept application that allows truck drivers to take pictures of their truck's defects using a cell phone. They can then record that a defect is fixed by adding photo's of their invoices. A PDF document is created on Google Drive that is updated each time the defect is updated. The PDF document consists of a cover page which is a Word Document that is Mail Merged with N+ pictures detailing the defect.

    The routine is an Upsert routine that is called from the TruckDefects Inserting and Updating events.

    An abbreviated logic flow is as follows:

    1. Ensure that there is a folder created on Google Drive using a template/mask.

    2. Open a Word document that was added to the solution a Resource. This is the Merge Template document.

    3. A merge record is created from the various entities and then merged using MergeNestedGroup as there are multiple master detail sections.

    4. The merged document (cover page) is then converted to PDF.

    5. All attachment images are resized, rotated to portrait then appended  to the cover page PDF.

    6 The document is saved to Google Drive.

    Note that once the image is appended to the PDF, a thumbnail is created for the database and the algorithm, on update just opens the existing PDF and replaces the cover page and appends the image attachments. I just wanted to mention this fact as it complicates the code a little.

    I hope this helps a little and good luck!

    		private void UpsertDefectDrive(TruckDefect defect)
    		{
    			try
    			{
    				var drive = new GoogleDrive("XXXXX.gserviceaccount.com", LightSwitchApplication.Resources.MyCert, "test", "CERT_THUMBPRINT");
    
    				if (string.IsNullOrEmpty(defect.GoogleFolderId))
    				{
    					var recordType = DataWorkspace.ApplicationData.GetDefectRecordType();
    					var folder = recordType.RecordFolder.FolderMask;
    					folder = folder.Replace("$TruckNumber", defect.Truck.Number.ToString());
    					folder = folder.Replace("$DriverFullName", defect.Truck.Driver.FullName);
    					folder = folder.Replace("$DriverLicense", defect.Truck.Driver.DriverLicense);
    					defect.GoogleFolderId = drive.EnsurePath(folder);
    				}
    
    				using (var documentStream = new MemoryStream(LightSwitchApplication.Resources.defect))
    				{
    					var document = new WordDocument(documentStream);
    
    					document.MailMerge.MergeField += (s, e) =>
    					{
    						if (e.FieldName == "Name" && defect.TruckDefectItem.Severity == "MAJOR")
    							e.CharacterFormat.TextBackgroundColor = Color.FromArgb(64, 255, 0, 0);
    
    						if (e.FieldName == "Severity" && defect.TruckDefectItem.Severity == "MAJOR")
    							e.CharacterFormat.TextBackgroundColor = Color.FromArgb(64, 255, 0, 0);
    
    						if (e.FieldName == "Category" && defect.TruckDefectItem.Severity == "MAJOR")
    							e.CharacterFormat.TextBackgroundColor = Color.FromArgb(64, 255, 0, 0);
    					};
    
    					document.MailMerge.MergeImageField += (s, e) =>
    					{
    						if (e.FieldName == "RepairedSignature" && defect.RepairedSignature != null)
    						{
    							var imageStream = new MemoryStream(defect.RepairedSignature);
    							e.Image = System.Drawing.Image.FromStream(imageStream);
    						}
    					};
    
    					dynamic merge = new ExpandoObject();
    					merge.Signature = null;
    					merge.ForDate = defect.ForDate.ToString();
    					merge.Severity = defect.TruckDefectItem.Severity;
    					merge.Category = defect.TruckDefectItem.Category.Name;
    					merge.Name = defect.TruckDefectItem.Name;
    					merge.Repaired = defect.Repaired.ToString();
    					merge.RepairedAt = defect.RepairedAt == null ? "" : defect.RepairedAt.Value.LocalDateTime.ToString();
    					merge.RepairedSignature = defect.RepairedSignature;
    					merge.CreatedBy = defect.CreatedBy;
    					merge.Created = defect.Created == null ? "" : defect.Created.Value.LocalDateTime.ToString();
    					merge.ModifiedBy = defect.ModifiedBy;
    					merge.Modified = defect.Modified == null ? "" : defect.Modified.Value.LocalDateTime.ToString();
    
    					merge.Notes = new List<ExpandoObject>();
    					if (defect.Notes.Count() > 0)
    					{
    						foreach (var note in defect.Notes.OrderBy(n => n.Created))
    						{
    							dynamic noteItem = new ExpandoObject();
    							noteItem = new ExpandoObject();
    							noteItem.Note_Created = note.Created.Value.LocalDateTime.ToString();
    							noteItem.Note_Text = note.Text;
    							merge.Notes.Add(noteItem);
    						}
    					}
    					else
    					{
    						dynamic noteItem = new ExpandoObject();
    						noteItem.Note_Created = "N/A";
    						noteItem.Note_Text = "No notes";
    						merge.Notes.Add(noteItem);
    					}
    
    					merge.Attachments = new List<ExpandoObject>();
    					if (defect.RecordAttachments.Count() > 0)
    					{
    						foreach (var attachment in defect.RecordAttachments.OrderBy(ra => ra.Created))
    						{
    							dynamic attachmentItem = new ExpandoObject();
    							attachmentItem = new ExpandoObject();
    							attachmentItem.Attachment_Created = attachment.Created.Value.LocalDateTime.ToString();
    							attachmentItem.Attachment_Text = attachment.Description;
    							merge.Attachments.Add(attachmentItem);
    						}
    					}
    					else
    					{
    						dynamic attachmentItem = new ExpandoObject();
    						attachmentItem.Attachment_Created = "N/A";
    						attachmentItem.Attachment_Text = "No attachments";
    						merge.Attachments.Add(attachmentItem);
    					}
    
    					var mergeList = new List<ExpandoObject>();
    					mergeList.Add(merge);
    
    					var mergeDataTable = new MailMergeDataTable("Defect", mergeList);
    
    					document.MailMerge.ExecuteNestedGroup(mergeDataTable);
    
    					var pdfConverter = new DocToPDFConverter();
    					var pdfDocument = pdfConverter.ConvertToPDF(document);
    					pdfDocument.DocumentInformation.Subject = $"Cover={pdfDocument.Pages.Count}";
    
    					if (!string.IsNullOrEmpty(defect.GoogleFileId))
    					{
    						var file = drive.DownloadFile(defect.GoogleFileId);
    						var googlePdf = new PdfLoadedDocument(file);
    						var subject = googlePdf.DocumentInformation.Subject;
    						if (subject.Contains("Cover="))
    						{
    							var numberPages = int.Parse(subject.Replace("Cover=", ""));
    							while (numberPages > 0)
    							{
    								googlePdf.Pages.RemoveAt(0);
    								--numberPages;
    							}
    
    						}
    
    						if (googlePdf.Pages.Count > 0)
    							pdfDocument.Append(googlePdf);
    					}
    
    					if (defect.RecordAttachments.Count() > 0)
    					{
    						foreach (var attachment in defect.RecordAttachments)
    						{
    							if (!attachment.Appended)
    							{
    								var page = pdfDocument.Pages.Add();
    								page.Section.PageSettings.Margins.All = 10;
    
    								var attachmentStream = new MemoryStream(attachment.Body);
    								var image = new PdfBitmap(attachmentStream);
    								float imageWidth = image.Width;
    								float imageHeight = image.Height;
    								float pageHeight = page.Graphics.ClientSize.Height;
    								float pageWidth = page.Graphics.ClientSize.Width;
    								float shrinkFactor;
    								var rotate = false;
    
    								if (imageWidth > imageHeight)
    								{
    									rotate = true;
    									imageWidth = image.Height;
    									imageHeight = image.Width;
    								}
    
    								if (image.Width > pageWidth)
    								{
    									shrinkFactor = imageWidth / pageWidth;
    									imageWidth = pageWidth;
    									imageHeight = imageHeight / shrinkFactor;
    								}
    
    								if (imageHeight > pageHeight)
    								{
    									shrinkFactor = imageHeight / pageHeight;
    									imageHeight = pageHeight;
    									imageWidth = imageWidth / shrinkFactor;
    								}
    
    								float x = (pageWidth - imageWidth) / 2;
    								float y = (pageHeight - imageHeight) / 2;
    
    								image.Quality = 75;
    
    								var status = page.Graphics.Save();
    
    								if (rotate)
    								{
    									page.Graphics.RotateTransform(90);
    									page.Graphics.DrawImage(image, y, -imageWidth - x, imageHeight, imageWidth);
    								}
    								else
    								{
    									page.Graphics.DrawImage(image, x, y, imageWidth, imageHeight);
    								}
    
    								page.Graphics.Restore(status);
    
    								var thumbnail = page.ExtractImages()[0].GetThumbnailImage(image.Height / 10, image.Width / 10, null, IntPtr.Zero);
    								using (var thumbnailStream = new MemoryStream())
    								{
    									thumbnail.Save(thumbnailStream, ImageFormat.Jpeg);
    									thumbnailStream.Seek(0, SeekOrigin.Begin);
    									thumbnailStream.Read(attachment.Body, 0, (int)thumbnailStream.Length);
    								}
    
    								attachment.Appended = true;
    							}
    						}
    					}
    
    					using (var pdfStream = new MemoryStream())
    					{
    						pdfDocument.Compression = PdfCompressionLevel.Best;
    						pdfDocument.Save(pdfStream);
    						pdfStream.Seek(0, SeekOrigin.Begin);
    
    						var description = new StringBuilder();
    						description.AppendLine($"Repaired: {defect.Repaired}");
    						description.AppendLine($"Severity: {defect.TruckDefectItem.Severity}");
    						description.AppendLine($"Category: {defect.TruckDefectItem.Category.Name}");
    						description.AppendLine($"Defect: {defect.TruckDefectItem.Name}");
    
    						if (string.IsNullOrEmpty(defect.GoogleFileId))
    							defect.GoogleFileId = drive.InsertFile(defect.GoogleFolderId, $"{defect.Number}", "application/pdf", pdfStream, description.ToString());
    						else
    							drive.UpdateFile(defect.GoogleFileId, pdfStream, description.ToString());
    					}
    				}
    			}
    
    			catch (Exception ex)
    			{
    				Debug.WriteLine(ex.ToString());
    				throw;
    			}
    		}
    
    		partial void TruckDefects_Inserting(TruckDefect entity)
    		{
    			var driver = entity.Truck.Driver;
    			var truck = entity.Truck;
    
    			var number = 1;
    			var lastDefect = DataWorkspace.ApplicationData.TruckDefects.Where(td => td.Truck.Number == truck.Number).OrderByDescending(td => td.Id).Take(1).Execute().SingleOrDefault();
    			if (lastDefect != null)
    				number = int.Parse(lastDefect.Number.Replace($"D{entity.Truck.Number}", "")) + 1;
    
    			entity.Number = $"D{entity.Truck.Number}{number:0000000}";
    
    			UpsertDefectDrive(entity);
    		}
    
    		partial void TruckDefects_Updating(TruckDefect entity)
    		{
    			UpsertDefectDrive(entity);
    		}


    Saturday, June 25, 2016 3:43 AM
  • Hi, Ian.

    Thanks for taking the time to respond...I appreciate it. I'll convert the code to VB and see if I can glean something off of it for my purposes. Have a good one.

    Cheers,

    Jeff

    Saturday, June 25, 2016 11:22 AM