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 Apr 04, 2017 7:01 pm

Hi, I'm looking for a way to determine when to increase the row height to account for text wrapping. Can I get the width of the text from the API? Any suggestions? Font being used is proportional so size of the text is not an option.

MrReports
 
Posts: 4
Joined: Tue Apr 04, 2017 6:41 pm

Wed Apr 05, 2017 3:38 am

Dear MrReports,

Thanks for your inquiry.
Sorry that Spire.XLS doesn't have a direct way to get the width of text. But there is a solution which may meet your need.
First you could insert text in a cell and then call AutofitColumn which will widen the width of column as per text. Then you will get the width of the column using ColumnWidth. Sample code for your reference.
Code: Select all
            CellRange cell = workbook.Worksheets[0].Range["A1"];
            cell.Value = "Hello world";
            sheet.AutofitColumn(1, 1, 1);
            double width = cell.ColumnWidth;

In addition, sorry that I am not quite clear the meaning of "Font being used is proportional so size of the text is not an option". If there is any question, please let me know.

Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Wed Apr 05, 2017 4:57 pm

The width of text is affected by the font (font name,size and style of the font). The font here is proportional meaning 'w' is wider then 'i'. AutofitColumn over sizes the width of the text see code and attached result. The width for both strings end up being the same which is not accurate.
Code: Select all
string texta = "Securities of a single corporate issuer (excluding Schedule 1 Banks) as a % of the total portfolio a";
                            string textb = "iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiia";

                            wsHeader.Range[1, 1].Style.Font.FontName = "Arial Narrow";
                            wsHeader.Range[1, 1].Style.Font.Size = 10;
                            wsHeader.Range[1, 1].Text = texta;
                            wsHeader.AutofitColumn(1, 1, 1);
                            double sizea = wsHeader.Range[1,1].ColumnWidth;
                           
                            wsHeader.Range[2, 1].Style.Font.FontName = "Arial Narrow";
                            wsHeader.Range[2, 1].Style.Font.Size = 10;
                            wsHeader.Range[2, 1].Text = textb;
                            wsHeader.AutofitColumn(2, 1,2);
                            double sizeb = wsHeader.Range[2, 1].ColumnWidth;
                            headerPack.SaveToFile(headerPath, ExcelVersion.Version2010);
Attachments
header.zip
(3.9 KiB) Downloaded 352 times

MrReports
 
Posts: 4
Joined: Tue Apr 04, 2017 6:41 pm

Thu Apr 06, 2017 3:07 am

Dear MrReports,

Thanks for your feedback.
I checked the code you provided and found the mistake with the line "wsHeader.AutofitColumn(2, 1,2)", which is used to fit the second column. Please change it to wsHeader.AutofitColumn(1, 2, 2), then the width of two strings will be different.
If you still have issue, please let me know.

Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Thu Apr 06, 2017 4:32 pm

Please see attached file... The cells becomes over-sized and does not give an accurate measurement. The first string width returns 69 while the second 79. You can see from the file that the strings should be almost identical in width.
Attachments
header.zip
(3.88 KiB) Downloaded 361 times

MrReports
 
Posts: 4
Joined: Tue Apr 04, 2017 6:41 pm

Fri Apr 07, 2017 8:31 am

Dear MrReports,

Thanks again for your information.
So sorry that it doesn't return an accurate measurement. But there is another solution which might meet your needs, please have a try. Sample code for your kind reference.
Code: Select all
//set font, size & style
Font f = new Font("Microsoft Sans Serif", 14, FontStyle.Regular);
//create a bmp / graphic to use MeasureString on
Bitmap b = new Bitmap(1, 1);
Graphics g = Graphics.FromImage(b);
//measure the string
SizeF sizeOfString = new SizeF();
sizeOfString = g.MeasureString("This is a text line", f);

Any question, please let me know.

Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Wed Sep 23, 2020 7:32 pm

Hi,

I am now using the AutoFitRows() feature (Spire.XLS.10.9.0). I'm having an issue where it fails to properly autofit (row height is to big) when I run it on machines where Microsoft office is not installed. However I have found installing and un-installing office 2016 (Office_Professional_Plus_2016_64Bit_English_MLF_X20-42432) resolves the issue. Seems like some component of the install lingers and is need for it to work. Do you know what is required for AutoFitRows() to work properly?
Attachments
ExtraHeight.JPG
ExtraHeight.JPG (42.29 KiB) Viewed 1629 times
NoExtraHeight.JPG
NoExtraHeight.JPG (40.51 KiB) Viewed 1629 times

MrReports
 
Posts: 4
Joined: Tue Apr 04, 2017 6:41 pm

Thu Sep 24, 2020 9:53 am

Hello,

Thanks for your post.
Our Spire.XLS API is a standalone Excel .NET managed assembly and does not depend on Microsoft Office Excel. And I simulated a Excel file and tested your scenario on a machine without Microsoft Office installed, but found the AutoFitRows method works well.
To help us further look into your issue, please provide your full test code and your input file. You could send your file to us (support@e-iceblue.com) via email. Also, please tell us your OS information (e.g. Win7 64bit) and region setting (e.g. China, Chinese). Thanks in advance.

Sincerely,
Rachel
E-iceblue support team
User avatar

rachel.lei
 
Posts: 1571
Joined: Tue Jul 09, 2019 2:22 am

Sat Oct 10, 2020 3:40 am

Hello,

Greetings from E-iceblue!
How is your issue now? Could you please give us some feedback at your convenience?
Thanks in advance.

Sincerely,
Rachel
E-iceblue support team
User avatar

rachel.lei
 
Posts: 1571
Joined: Tue Jul 09, 2019 2:22 am

Return to Spire.XLS