Images in cell comments

Feb 8, 2011 at 8:31 AM

Hi all,

Firstly, thanks for the product. It really takes away pain of working with Open XML.

I am using the following code to insert images as cell comments.

It is working fine when I save the file and open it for the first time.

But, when I modify the document like changing the cell width in office excel and save it back, it throws me the following exception.

Removed part : /xl/drawings/vmlDrawing1/vml part. (Drawing shape)

  protected void CreateExcelSheet(List localImageList)
        {
            //string templateFile = Server.MapPath("../Template/Template.xlsx");
            FileStream source = System.IO.File.OpenRead(Server.MapPath("Template/Template.xlsx"));
            string sFile = Server.MapPath("Template_" + HttpContext.Current.Session.SessionID + ".xlsx");
            if (File.Exists(sFile))
            {
                File.Delete(sFile);
            }
            FileStream newFileStream = new FileStream(sFile, FileMode.Create);
            using (var package = SpreadsheetDocumentWrapper.Open(source, newFileStream))
            {
                //package.WorkbookPart.WorksheetParts.Add("Data");
                var part = package.WorkbookPart.WorksheetParts["Data"];
                int count = 1;
                foreach (WCFService.myImage testImage in localImageList)
                {
                    part.Worksheet.InsertRows(count, 1);
                    part.Worksheet.SetCellValue(new GridReference(count, 0), new System.Uri(testImage.UrlThumb));
                    WebRequest req = WebRequest.Create(testImage.UrlThumb);
                    //req.Credentials = CredentialCache.DefaultNetworkCredentials;
                    WebResponse response = req.GetResponse();
                    Stream stream = response.GetResponseStream();
                    var image = new Bitmap(stream);
                    part.Worksheet.SetCommentImage(new GridReference(count, 0), image, null, null);
                    count++;
                }
            }
            source.Close();
            newFileStream.Close();
            DownloadFile(sFile);
        }

        protected void DownloadFile(string sFile)
        {
            System.IO.FileInfo targetFile = new System.IO.FileInfo(sFile);
            if (targetFile.Exists)
            {
                Response.AddHeader("Content-Disposition", "inline; filename=" + targetFile.Name);
                Response.AddHeader("Content-Length", targetFile.Length.ToString());
                Response.ContentType = "application/ms-excel";
                Response.TransmitFile(targetFile.FullName);
                Response.Flush();
            }
            if (File.Exists(sFile))
            {
                File.Delete(sFile);
            }
        }
Any help would be appreciated.
Thanks!!!
Coordinator
Feb 8, 2011 at 10:34 AM

Can you please clarify at what point you get the error message. I assume it is an Excel error dialog that you are seeing, but when does it occur - when you save the modified workbook or when you try to re-open it?

Even with this information it will be hard to offer detailed guidance about the cause in your specific scenario. It is quite possibly a bug in the way ExtremeML handles some aspect of the VML drawing object. The VML implementation in Excel is very complex and largely undocumented, so much of what ExtremeML does has been designed by reverse engineering Excel workbooks. Nonetheless, if you can pinpoint exactly what is going wrong, we can probably work out a solution to it.

The most reliable way to do this is to use the OpenXML SDK tool to perform a comparison between various instances of your workbook. I recommend taking a copy of the file created by ExtremeML and comparing to the same file, after it has been opened and re-saved using Excel. If the problem only occurs when you perform a manual update to the generated file (for example changing a column width) you may need to compare three different file versions to pin down the differences. The comparison utility will allow you to quickly home in on the differences between files, which should identify the bit that Excel didn't like. If you document the results and post them here, I will try to use them to create a patch.

Good luck,

Tim

Feb 8, 2011 at 11:19 AM

Thanks for the reply.

Basically  iam facing the issue when I try to reopen the same file after making few modifications to it.

When I compare the files using the open xml tool , I am getting the following error for the second file (modified).

"Cannot open target file: Part /xl/drawings/vmlDrawing1.vml:' 'o:relid' is a duplicate attribute name. Line 15, position 39.

Can I have your email id for sending the attachments.

 

 

 

 

Feb 8, 2011 at 11:36 AM

This is the error description the error log file :

  <?xml version="1.0" encoding="UTF-8" standalone="yes" ?> 
- <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <logFileName>error153920_01.xml</logFileName> 
  <summary>Errors were detected in file 'C:\Users\v-vapoha\Desktop\p2.xlsx'</summary> 
- <removedParts summary="Following is a list of removed parts:">
  <removedPart>Removed Part: /xl/drawings/vmlDrawing1.vml part. (Drawing shape)</removedPart> 
  </removedParts>
  </recoveryLog>

 

Feb 8, 2011 at 12:06 PM



 <v:fill o:relid="R58a4e0474c464ca1" o:relid="rId1" o:title="" color2="#ffffe1"
   size="0,0" aspect="atLeast" origin="-32767f,-32767f" position="-32767f,-32767f"
   recolor="t" rotate="t" type="frame"/>
As we can see from the above snippet that the o:relid has been added twice.
I tried removing the duplicate attribute and opened the file. But, it seems to be corrupted.
Coordinator
Feb 8, 2011 at 1:12 PM

The o:relid attribute specifies a relationship id to an associated image part. From memory, it is the first of these attributes that is created by ExtremeML, which means that, for some reason, Excel is failing to recognise it and is adding its own. You should be able to verify this by examining a working version of the file and checking that a suitably named image part exists.

Can you please document the simplest set of steps that produces this problems (so that I am able to reproduce it in my debugging rig) ?

I will aim to schedule a bug chasing session some time soon, after which I will issue a new release (assuming I succeed in finding the problem) and post a comment on this thread. In the meantime, if you are inclined to experiment with it yourself, you will find all the VML related classes in the ExtremeML.Sdk.Vml namespace. The above markup is createdby the ExtremeML.Sdk.Vml.Fill class.

Feb 8, 2011 at 1:32 PM

I have followed these steps :

1. Created a template file (Template.xlsx) by applying styles. (Column headers in 1st row, Format as table --> Table style medium 9, changed "sheet1" to "Data")

2. Filling the row and column values using the below code. 

3. When the open/save dialog pops up, I am saving the file with xlsx format.

4. Opened the saved doc(Test.xlsx), and saved a copy of the same doc.

5. Opening the new doc throws me a error.

protected void CreateExcelSheet(List localImageList)
        {
            //string templateFile = Server.MapPath("../Template/Template.xlsx");
            FileStream source = System.IO.File.OpenRead(Server.MapPath("Template/Template.xlsx"));
            string sFile = Server.MapPath("Template_" + HttpContext.Current.Session.SessionID + ".xlsx");
            if (File.Exists(sFile))
            {
                File.Delete(sFile);
            }
            FileStream newFileStream = new FileStream(sFile, FileMode.Create);
            using (var package = SpreadsheetDocumentWrapper.Open(source, newFileStream))
            {
                //package.WorkbookPart.WorksheetParts.Add("Data");
                var part = package.WorkbookPart.WorksheetParts["Data"];
                int count = 1;
                foreach (WCFService.myImage testImage in localImageList)
                {
                    part.Worksheet.InsertRows(count, 1);
                    part.Worksheet.SetCellValue(new GridReference(count, 0), new System.Uri(testImage.UrlThumb));
                    WebRequest req = WebRequest.Create(testImage.UrlThumb);
                    //req.Credentials = CredentialCache.DefaultNetworkCredentials;
                    WebResponse response = req.GetResponse();
                    Stream stream = response.GetResponseStream();
                    var image = new Bitmap(stream);
                    part.Worksheet.SetCommentImage(new GridReference(count, 0), image, null, null);
                    count++;
                }
            }
            source.Close();
            newFileStream.Close();
            DownloadFile(sFile);
        }

        protected void DownloadFile(string sFile)
        {
            System.IO.FileInfo targetFile = new System.IO.FileInfo(sFile);
            if (targetFile.Exists)
            {
                Response.AddHeader("Content-Disposition", "inline; filename=" + targetFile.Name);
                Response.AddHeader("Content-Length", targetFile.Length.ToString());
                Response.ContentType = "application/ms-excel";
                Response.TransmitFile(targetFile.FullName);
                Response.Flush();
            }
            if (File.Exists(sFile))
            {
                File.Delete(sFile);
            }
        }

 

 

Feb 10, 2011 at 5:37 PM

Hi Tim,

Are you able to find any solution?

Coordinator
Feb 10, 2011 at 6:36 PM

Sorry - I haven't had time yet. This looks like a potentially complex issue and, to be realistic, it's not likely to get my attention any time soon, as I have too much other work on my schedule.

It was for this reason that I have tried to provide you some clues where to look for the cause. If you are in a hurry, I recommend digging into the source and discovering where and why the two attributes are being added.

Good luck,

Tim

Feb 11, 2011 at 6:03 PM

Tim,

As a alternative, can we place a image as cell column value with extreme ML?

A code snippet will help.