How to set paragraph line spacing in textbox?

Aspose.Cells Product Family
1

Please explain how I can set this property via code. Right click on a textbox and click “Paragraph” in the dropdown to see the below popup:

image.png (6.5 KB)

How do I set the “Line Spacing” property to “Multiple” via code?

2

@Justafool,
Thank you for your query. Aspose.Cells has provided following two options for setting the paragraph line spacing in the textbox. These options are provided via enumerator TextBox.TextBody.TextParagraphs[index].LineSpaceSizeType

  • Points

  • Percentage

For setting the respective values in Aspose.Cells, we have TextBox.TextBody.TextParagraphs[index].LineSpace property.

In MS Excel, we have following options for line spacing in paragraph:

  • Single

  • 1.5 Lines

  • Double

  • Exactly

  • Multiple

Following are the sample mappings (with random values) for setting different options for line spacing in a paragraph.

MS Excel => LineSpaceSizeType, LineSpace
Single => Percentage, 1000
1.5 Lines => Percentage, 1500
Double => Percentage, 2000
Exactly 13.2 => Points, 13.2
Exactly 28 => Points, 28
Multiple 0.5 => Percentage, 500
Multiple 1 => Percentage, 1000
Multiple 3 => Percentage, 3000
Multiple 3.5 => Percentage, 3500

For example, if you want to set option Multiple and value 3 in MS Excel, you may try the following same code:

// Instantiate a new Workbook.
Workbook workbook = new Workbook();

// Get the first worksheet in the book.
Worksheet worksheet = workbook.Worksheets[0];

// Add a new textbox to the collection.
int textboxIndex = worksheet.TextBoxes.Add(2, 1, 160, 200);

// Get the textbox object.
Aspose.Cells.Drawing.TextBox textbox0 = worksheet.TextBoxes[textboxIndex];

// Fill the text.
textbox0.Text = "Sample text";

textbox0.TextBody.TextParagraphs[0].LineSpaceSizeType = Aspose.Cells.Drawing.Texts.LineSpaceSizeType.Percentage;
textbox0.TextBody.TextParagraphs[0].LineSpace = 3000;
workbook.Save("output.xlsx");

Give a try to the above sample code and share your feedback.

3

Great, thank you. That’s very helpful. I have a follow-up question: how can I set the TextParagraph.Bullet property? It is get-only. How can I insert a bullet into a textbox via code?

4

@Justafool,
We were able to observe the issue where bullets are not added similar to MS Excel but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-47286 – Insert different type of bullets in textbox

5

@Justafool,

Please try the following sample code for your reference:
e.g
Sample code:

 // Instantiate a new Workbook.
            Workbook workbook = new Workbook();

            // Get the first worksheet in the book.
            Worksheet worksheet = workbook.Worksheets[0];

            // Add a new textbox to the collection.
            int textboxIndex = worksheet.TextBoxes.Add(2, 1, 160, 200);

            // Get the textbox object.
            Aspose.Cells.Drawing.TextBox textbox0 = worksheet.TextBoxes[textboxIndex];
            // Fill the text.
            textbox0.Text = "   Sample text";

            textbox0.TextBody.TextParagraphs[0].LineSpaceSizeType = Aspose.Cells.Drawing.Texts.LineSpaceSizeType.Percentage;
            textbox0.TextBody.TextParagraphs[0].LineSpace = 3000;
            
            Bullet bullet = textbox0.TextBody.TextParagraphs[0].Bullet;
            bullet.Type = BulletType.Character;
            ((CharacterBulletValue)bullet.BulletValue).Character = '•';
            
            
            workbook.Save("e:\\test2\\out1.xlsx");
6

Thanks, that lets me set the bullet, which is very helpful. However, I am seeing that accessing bullet.BulletValue.Character gives a different character than the one displayed in the textbox in Excel. Here’s a zipped test workbook and test code to run on it: Book2.zip (7.0 KB)

foreach (var textBox in workbook.Worksheets[0].TextBoxes) {
                    foreach (var tp in textBox.TextBody.TextParagraphs) {
                        var textPar = (TextParagraph) tp;
                        var bulletChar = (bullet.BulletValue as CharacterBulletValue)?.Character;
                        if (bulletChar != null) {
                            Console.WriteLine(bulletChar)
                        }
                    }
                }

The workbook displays this character for the bullet: image.png (296 Bytes)
but the code says the character is ‘v’. How can I access the correct bullet character?

7

@Justafool,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-47291 – Wrong bullet character read from Excel file

8

@Justafool,

We evaluated your issue “CELLSNET-47291” further. The bullet character is ‘v’ in the file because the font is “Wingdings”, so it displays differently. Please check the following setting in the source file:

<a:buFont typeface="Wingdings" panose="05000000000000000000" pitchFamily="2" charset="2"/>
<a:buChar char="v"/>
9

Thanks. How can I check that setting in the source file? Can I modify it via code?

10

@Justafool,

You mean via Aspose.Cells APIs? We will get back to you soon.

11

@Justafool,

.xlsx file is a zip file which contains some xml file. Please unzip it and check drawing1.xml.

We will public Bullet.FontName property to get and set the font name.

12

The issues you have found earlier (filed as CELLSNET-47291) have been fixed in Aspose.Cells for .NET v20.4. This message was posted using Bugs notification tool by Amjad_Sahi