Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Tue May 14, 2019 10:01 pm

I have a cell with a string like sheet.Range("A1").Value = "This is a Test."
I would like to place a small Excel shape right after this string in the same cell. It seems that I can use the LeftColumnOffset property to do it. But I don't know how to find out the Offset value for a string in a cell. Can you help?

patrick551
 
Posts: 8
Joined: Mon Dec 17, 2018 6:46 pm

Wed May 15, 2019 8:39 am

Hi,

Thank you for your inquiry.
Please refer to the the following code to achieve your requirement. If there is any question, please feel free to write back.
Code: Select all
            string s = "This is a test";
            Graphics g = this.CreateGraphics();
            g.PageUnit = GraphicsUnit.Pixel;
            SizeF sizeF = g.MeasureString(s, new Font("Arial",12.0f));
            float width = sizeF.Width;
            Workbook workbook = new Workbook();
            workbook.CreateEmptySheets(1);
            Worksheet sheet = workbook.Worksheets[0];
            CellRange cell = workbook.Worksheets[0].Range["A1"];
            cell.Style.Font.FontName = "Arial";
            cell.Style.Font.Size = 12.0f;
            cell.Value = s;
            cell.ColumnWidth = 50;
            var imageFile = @"sample.png";
            ExcelPicture pic = sheet.Pictures.Add(1, 1, imageFile);
            pic.Left = (int)width;
            workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010);
            System.Diagnostics.Process.Start("Result.xlsx");

Sincerely,
Nancy
E-iceblue support team
User avatar

nancy.yang
 
Posts: 184
Joined: Wed Apr 03, 2019 2:33 am

Thu May 16, 2019 5:34 pm

I use your suggestion to find out the width of the string in pixel, let say it is a
then I used sheet.ColumnWidthToPixels(50) to find out the pixel width of the cell in question which has ColumnWidth = 50, let say it's b

then I get the Excel Shape

xlsshape = sheet.PrstGeomShapes.AddPrstGeomShape(RowNum, ColumnNum, shapesize, shapesize, 5) '* 5 is the shape type, it varies

xlsshape.LeftColumnOffset = a/b * 1024

the resulting offset is always bigger than the string. The longer the string, the bigger the difference. Did I got the idea of LeftColumnOffset wrong?

patrick551
 
Posts: 8
Joined: Mon Dec 17, 2018 6:46 pm

Fri May 17, 2019 10:58 am

Hi,

Thanks for your feedback.
I have noticed the behavior that the offset is always bigger than the string. I have posted the issue to our Dev team for further investigation, if there is any update, I will inform you. Apologize for the inconvenience caused.

Sincerely,
Nancy
E-iceblue support team
User avatar

nancy.yang
 
Posts: 184
Joined: Wed Apr 03, 2019 2:33 am

Return to Spire.XLS