Dynamics Community Forum Thread Details

C# Open XLSX file with OpenXML, add values and save it as an attachment to a note

Hey guys,

once again I have a very specific requirement which is cooking up in my brain.

I have two custom entities.
1: Template Entity X
2: Record Entity Y

Whenever a specific field on Record Entity Y has a specific value, a record from Template Entitiy X is retrieved.
This Template Entity X record contains a note with an attachment. That Attachment is an XLSX Excel file.

My Plug-In gets that XLSX file from that template entity X record.

Now I want to open that file with OpenXML, write some values in some columns, close the file and add it as a new attachment to a note to the Record Entity Y record which triggered the plugin.

This is the code I got so far:

#region RetrieveTemplateRecord
//Retrieve Template Record
QueryByAttribute template_query = new QueryByAttribute("new_customtemplates");
template_query.AddAttributeValue("new_name", "sample_template");
template_query.ColumnSet = new ColumnSet(true);
EntityCollection template_result = service.RetrieveMultiple(template_query);
Entity template_record = template_result.Entities.First();

//Retrieve Note/Attachment from Template Record
QueryByAttribute template_file_query = new QueryByAttribute("annotation");
template_file_query.AddAttributeValue("objectid", template_record.Id);
template_file_query.ColumnSet = new ColumnSet(true);
EntityCollection template_file_result = service.RetrieveMultiple(template_file_query);
Entity template_file = template_file_result.Entities.First();

//Template Body String
string template_body = template_file.GetAttributeValue("documentbody");
//Template Body Byte Array
byte[] template_data = Convert.FromBase64String(template_body);                
#endregion RetrieveTemplateRecord       

#region OpenTemplateInOpenXML
//CONTINUTE HERE
#endregion OpenTemplateInOpenXML

#region DocumentToPriceList
string entitytype = "new_pricelist";
Entity Note = new Entity("annotation");
Guid EntityToAttachTo = targetEntity.Id; // The GUID of the incident
Note["objectid"] = new EntityReference(entitytype, EntityToAttachTo);
Note["objecttypecode"] = entitytype;
Note["subject"] = document_type;
Note["notetext"] = targetEntity.GetAttributeValue("new_name");
Note["filename"] = template_record.GetAttributeValue("new_name")   ".xlsx";
Note["documentbody"] = Convert.ToBase64String(document_binary);
Note["mimetype"] = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
service.Create(Note);
#endregion DocumentToPriceList

Can anyone help me out on how to get from the byte[ to a working spreadsheet document in OpenXML and then put it back into a byte[ and then string in order to add it as an attachment to a note?

I have tried several things but cannot find a working way to "open" the document the right way in OpenXML. I Have tried working with a memorystream, with a filestream. But I can't get around the forth and back conversions.

Best wishes and many thankas guys,

Evgeniy

Quick Links