Cell Text Overflow

Aspose.Cells Product Family
1

We are using Aspose Cells.Net.


We would like to know if a cell contains text which overflows to the next cells. Please let us know what property to use, to check this.

Thanks in advance!

2
Hi,

Well, there is no such property in the APIs. But you may adopt a workaround to do your task. Well, you can create a temporary worksheet and fill the cell data/values into its first row according with the column index. Then call AutoFitColumn() method on this temporary worksheet to get the width of the column. Compare this width with the original width of the column in your actual data sheet. So, you may find out if the text is overflowed in the cell or not. And, then you may remove the temporary sheet from the workbook at the end.

See sample code, I have also attached the template file here for your reference.
Sample code:

Workbook wb = new Workbook("e:\\test2\\CellTextWorkflow.xlsx");
Worksheet sheet1 = wb.Worksheets[0];
Cells cells = sheet1.Cells;
// To Check if a cell contains text which overflows to the next cells

Aspose.Cells.Cell cell =cells["A1"];
double colwidth = cells.GetColumnWidth(0);

//Create a temp worksheet
Worksheet temp = wb.Worksheets[wb.Worksheets.Add()];
Cells tempCells = temp.Cells;
Aspose.Cells.Cell tempCell = tempCells["A1"];
tempCell.PutValue(cell.StringValue);
temp.AutoFitColumn(0);
double tempcolwidth = tempCells.GetColumnWidth(0);
//Compare both widths
if(tempcolwidth > colwidth)
{
Debug.WriteLine("Text Overflowed");
}
//Remove the temporary sheet
wb.Worksheets.RemoveAt(temp.Name);

Thank you.
3

Hi,

I like the example given by Amjad, here is another approach.

I have used System.Windows.Forms.TextRenderer class to measure the text size in pixels. Given the font name and font size, TextRenderer.MeasureText method will return the size of the string in unit of pixels.

We will then compare both cell’s column width and cell’s string width and decide if the cell is overflowing or not.

Please see the code below.

C#


string filePath = @“C:\source.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[0];


Cell cell = worksheet.Cells[“A1”];


Style style = cell.GetStyle();


Size sz = TextRenderer.MeasureText(cell.StringValue, new System.Drawing.Font(style.Font.Name, style.Font.Size));


int ADJUSTMENT=3; //fine tune it


if (sz.Width + ADJUSTMENT > worksheet.Cells.GetColumnWidthPixel(cell.Column))

{

//Text Over Flows the width of the cell’s column

}


4

Thanks Shakeel. I will definitely look into this.

5

Amjad, Although we have not tested it, your solution is fantastic.


Many thanks for your prompt response. Will let you know if we need any other info.