This topic describes how to determine the text that would be displayed in Microsoft Excel® cell given the cell’s value, format string, column width, and worksheet options. You can determine the cell text using the WorksheetCell's GetText method.
This topic contains the following sections:
Text displayed in a cell depends on several factors, other than the actual cell value. Below is a summary of those factors:
The format string determines how the value of cell is converted to text and what literal character should be displayed with the formatted value. You can find more detailed information about format codes here.
The amount of horizontal space available in a cell plays a big part in how the value is displayed to the user.
Displayed text can be different depending on varying column widths.
When displaying numbers and using format string containing “General” or “@”, there are various formats which are tried to find a formatting which fits the cell width.
If a text is used in the cell, the cell displayed text will always be full value, regardless of whether it is cut off or not in the cell.
You can set Worksheet.DisplayOptions ShowFormulasInCells property in code or in Microsoft Excel by: File -> Options -> Advanced -> Display options for this worksheet -> Show formulas in cells instead of their calculated results.
When this option is turned On, formulas are displayed in cells instead of their results, and format strings and cell widths are ignored. Text values display as if their format string were @ , non-integral numeric values display as if their format string were 0.0 and integral numeric values display as if their format string were 0 .
Additionally, if the value cannot fit, it will not display as all hashes. Display text will still return its full text as the cell text, even though it may not be fully seen.
The cell text is exposed by the WorksheetCell's GetText method. The parameter-less overload returns the cell text seen in the Microsoft Excel UI. The other overloads takes a TextFormatMode enumeration value, with the following values:
The following table lists the code examples included in this topic.
The return value of the WorksheetCell GetText methods depends on format strings, applied to the cell.
The code in this example loads a Workbook and creates an instance of the Worksheet DifferentFormats. It access display text of cells in region A1:A8 using WorksheetCell GetText method.
You need the following entities for the purposes of this example:
An Excel file named Book3.xlsx with a worksheet saved on your C hard drive, at C:\
Worksheet name set to DifferentFormats
Value 1234567890 in cells from A1 to A8. Each cell has a different cell formatting, as shown on screen-shot below.
In Visual Basic:
' Load a Workbook
Dim workBook As Infragistics.Documents.Excel.Workbook = Infragistics.Documents.Excel.Workbook.Load("C:\Book3.xlsx")
Dim worksheet As Infragistics.Documents.Excel.Worksheet = workBook.Worksheets("DifferentFormats")
Dim sb As New StringBuilder()
' Loop over cells with different formats sets and show the GetText() method return values
Dim i As Integer = 0
While i < 8
sb.AppendLine([String].Format("{0} " & vbTab & vbTab & " {1}", worksheet.Rows(i).Cells(1).GetText(), worksheet.Rows(i).Cells(0).GetText()))
i += 1
End While
MessageBox.Show(sb.ToString())In C#:
// Load a Workbook
Infragistics.Documents.Excel.Workbook workBook = Infragistics.Documents.Excel.Workbook.Load("C:\\Book3.xlsx");
Infragistics.Documents.Excel.Worksheet worksheet = workBook.Worksheets["DifferentFormats"];
StringBuilder sb = new StringBuilder();
// Loop over cells with different formats sets and show the GetText() method return values
for (int i = 0; i < 8; i++)
{
sb.AppendLine(String.Format("{0} \t\t {1}",
worksheet.Rows[i].Cells[1].GetText(),
worksheet.Rows[i].Cells[0].GetText()));
}
MessageBox.Show(sb.ToString());Returns the value of the WorksheetCell GetText methods depending on column width.
The code in this example loads a Workbook and creates an instance of the Worksheet DifferentWidths. It access display text of cells in region A1:E2 using WorksheetCell GetText method.
You need the following entities for the purposes of this example:
An Excel file named Book3.xlsx with a worksheet saved on your C hard drive, at C:\
Worksheet name set to DifferentWidths
Value 1234567890 in cells from A1 to E1 and value 0.0001234567890 in cells A2 to E2. Column widths are different for each column, set as shown in the screenshot below.
In Visual Basic:
' Load a Workbook
Dim workBook As Infragistics.Documents.Excel.Workbook = Infragistics.Documents.Excel.Workbook.Load("C:\Book3.xlsx")
Dim worksheet As Infragistics.Documents.Excel.Worksheet = workBook.Worksheets("DifferentWidths")
Dim sb As New StringBuilder()
' Loop over cells with different width sets and show the GetText() method return values
Dim i As Integer = 0
While i < 5
sb.AppendLine([String].Format("{0} " & vbTab & vbTab & " {1}", worksheet.Rows(1).Cells(i).GetText(), worksheet.Rows(0).Cells(i).GetText()))
i += 1
End While
MessageBox.Show(sb.ToString())In C#:
// Load a Workbook
Infragistics.Documents.Excel.Workbook workBook = Infragistics.Documents.Excel.Workbook.Load("C:\\Book3.xlsx");
Infragistics.Documents.Excel.Worksheet worksheet = workBook.Worksheets["DifferentWidths"];
StringBuilder sb = new StringBuilder();
// Loop over cells with different width sets and show the GetText() method return values
for (int i = 0; i < 5; i++)
{
sb.AppendLine(String.Format("{0} \t\t {1}",
worksheet.Rows[1].Cells[i].GetText(),
worksheet.Rows[0].Cells[i].GetText()));
}
MessageBox.Show(sb.ToString());Returns the value of the WorksheetCell GetText methods depending on the ShowFormulaInCells worksheet option.
The code in this example loads a Workbook and creates an instance of the Worksheet Formulas. Then, Worksheet property DisplayOptions ShowFormulaInCells property is set to true. The code accesses the displayed text of cells in region B1:B5 using GetText method.
The following screenshot is a preview of the final result.
You need the following entities for the purposes of this example:
An Excel file named Book3.xlsx with a worksheet saved on your C hard drive
Worksheet name set to Formulas
Random numbers in cells A1:A5
Random functions in cells B1:B5.
In Visual Basic:
' Load a Workbook
Dim workBook As Infragistics.Documents.Excel.Workbook = Infragistics.Documents.Excel.Workbook.Load("C:\Book3.xlsx")
Dim worksheet As Infragistics.Documents.Excel.Worksheet = workBook.Worksheets("Formulas")
' Turn On Worksheet options for showing formulas instead of formula results
worksheet.DisplayOptions.ShowFormulasInCells = True
Dim sb As New StringBuilder()
' Loop over cells with different width sets and show the GetText() method return values
Dim i As Integer = 0
While i < 5
sb.AppendLine(worksheet.Rows(i).Cells(1).GetText())
i += 1
End While
MessageBox.Show(sb.ToString())In C#:
// Load a Workbook
Infragistics.Documents.Excel.Workbook workBook = Infragistics.Documents.Excel.Workbook.Load("C:\\Book3.xlsx");
Infragistics.Documents.Excel.Worksheet worksheet = workBook.Worksheets["Formulas"];
// Turn On Worksheet options for showing formulas instead of formula results
worksheet.DisplayOptions.ShowFormulasInCells = true;
StringBuilder sb = new StringBuilder();
// Loop over cells with different width sets and show the GetText() method return values
for (int i = 0; i < 5; i++)
{
sb.AppendLine(worksheet.Rows[i].Cells[1].GetText());
}
MessageBox.Show(sb.ToString());The following topics provide additional information related to this topic.
The following material (available outside the Infragistics family of content) provides additional information related to this topic.