How to get image position (Anchor) in excel

Dec 18, 2013 at 8:44 AM
Hi,
I have one function call GetVesselUtilizationReportFile and it generate one excel file base on outport. It can be generated successfully. Now I need to generate one excel file for all outports. so I loop through outport by outport, get the byte data, create temp workbook and copy sheet to main workbook. But copyto function does not copy images in sheet. It can only copy text. So that I manually add the image again as per below code. I have hard code the anchor and create the picture. So that I need to know the exact position of current images in excel. May I know how can I get the image position is excel sheet.
foreach (var outport in outports)
            {
                string mimeType;
                string extension;
                byte[] excelStreaming = vesselUtilizationController.GetVesselUtilizationReportFile(DateTime.Now,
                                                                           outport.BusinessUnitId,
                                                                           "excel", out mimeType,
                                                                           out extension);
                // Getting the complete workbook...
                using (Stream stream = new MemoryStream(excelStreaming))
                {
                    var templateWorkbook = new HSSFWorkbook(stream);
                    var hssfSheet = (HSSFSheet)templateWorkbook.GetSheet("VesselUtilizationPrint");
                    hssfSheet.CopyTo(workbook, outport.Code, true, true);
                    
                    var images = templateWorkbook.GetAllPictures();
                    var i = 1;
                    foreach (HSSFPictureData image in images)
                    {
                        var pictureIdx = workbook.AddPicture(image.Data, (PictureType)image.Format);
                        //get sheet
                        var workbookSheet = workbook.GetSheet(outport.Code);
                        // Create the drawing patriarch.  This is the top level container for all shapes. 
                        var drawing = workbookSheet.CreateDrawingPatriarch();
                        //add a picture shape
                        var anchor = helper.CreateClientAnchor();
                        //set top-left corner of the picture,
                        //subsequent call of Picture#resize() will operate relative to it
                        switch (i)
                        {
                            case 1:
                                anchor.Col1 = 3;
                                anchor.Row1 = 2;
                                break;
                            case 2:
                                anchor.Col1 = 19;
                                anchor.Row1 = 2;
                                break;
                            case 3:
                                anchor.Col1 = 2;
                                anchor.Row1 = 8;
                                break;
                        }
                        i++;
                        var pict = drawing.CreatePicture(anchor, pictureIdx);
                        //auto-size picture relative to its top-left corner
                        pict.Resize();
                    }
                }
                
            }
            using(var ms = new MemoryStream())
            {
                // Writing the workbook content to the FileStream...
                workbook.Write(ms);

                // Sending the server processed data back to the user computer...
                var response = HttpContext.Current.Response;
                response.ContentType = "application/vnd.ms-excel";
                response.AddHeader("Content-Disposition", String.Format("attachment;filename={0}", "CrewList.xls"));
                response.Clear();
                response.BinaryWrite(ms.GetBuffer());
                response.End();
            }
Coordinator
Jan 9, 2014 at 10:38 PM
This looks to be custom requirement from you. You can buy professional service from NPOI team. Please contact me via tonyqus@gmail.com