Search code examples
c#excelspreadsheetgear

Load an Excel cell value with a leading apostrophe

I want to be able to load the exact text within an Excel cell using Spreadsheet gear. This is because I want the leading apostrophe.

For Example the cell has the text; 'Test' This is a test. Spreadsheet gear strips away the leading '.

SpreadsheetGear.IWorkbook workbook = null;
workbook = SpreadsheetGear.Factory.GetWorkbookSet(Workbooks.Open(excelFileName);
IRange usedRange = workbook.Worksheets[0].UsedRange;
Text = workbook.Worksheets[0].Cells[row, 0].Value as string;

Is this a limitation of loading data from Excel? Or is there just a different way to load the contents of an Excel cell using Spreadsheet gear?

Solution

  • The value of IRange.Value/Text will not include a leading apostrophe since this is a special case in SpreadsheetGear/Excel that indicates the inputted contents should be force-treated as Text.

    One way to get your string value with a leading apostrophe would be to check the IRange.PrefixCharacter. Example:

    string text = cell.Value.ToString();
if (cell.PrefixCharacter == '\'')
    text = "'" + text;