Getting cell value type of an existing spreadsheet

[juhohe]

I have created an Excel spreadsheet using Microsoft Excel 2013. The spreadsheet I created is empty but it has some formatting, e.g. I selected all cells and set text alignment to right. I also selected some of columns and set their type to Number, short date, or general. Is it possible to get these formattings and types from an empty spreadsheet? I already tried this, but it seems that RadHorizontalAlignment is always General, not Right as I would like it to be.

[Anna]

Hi,

The scenario you are describing is absolutely valid and you should be able to get both the alignment and the format. Unfortunately I still haven't been able to reproduce the issue you are facing. What I did is I created an Excel file with Excel 2013, set right alignment to the the entire first column and number format to the second column. After importing the file in RadSpreadsheet, I was able to get correct values for both properties using the following code:

CellRange firstCol = CellRange.FromColumn(0);
CellSelection firstColSelection = worksheet.Cells[firstCol];
RadHorizontalAlignment alignmentValue = firstColSelection.GetHorizontalAlignment().Value;

CellRange secondCol = CellRange.FromColumn(1);
CellSelection secondColSelection = worksheet.Cells[secondCol];
CellValueFormat formatValue = secondColSelection.GetFormat().Value;

Do you find any difference between this approach and yours?

Regards,
Anna
Telerik

[juhohe]

I tried your code. It works for format but for alignment I just get the HorizontalAlignment.General, even though I have set the column to align text to right. This is my code:

 

SpreadsheetDocuments doc = new SpreadsheetDocuments();

doc.OpenDocument(templatePath);

Worksheet worksheet = doc.Document.ActiveWorksheet;

CellRange firstCol = CellRange.FromColumn(0);
CellSelection firstColSelection = worksheet.Cells[firstCol];

RadHorizontalAlignment alignmentValue = firstColSelection.GetHorizontalAlignment().Value;

// This test fails. The column A is set to right align and the type is General.
Assert.That(alignmentValue, Is.EqualTo(RadHorizontalAlignment.Right));

CellRange secondCol = CellRange.FromColumn(1);
CellSelection secondColSelection = worksheet.Cells[secondCol];
CellValueFormat formatValue = secondColSelection.GetFormat().Value;

// This works, the column B is set to type number.
Assert.That(formatValue.FormatStringInfo.FormatType, Is.EqualTo(FormatStringType.Number));

 ​

[juhohe]

By the way, the version is 2015.1.0511, installed with NuGet. I tried also a newer version but it caused an error (error message was something like "Part_UnhideThumb not found".

[Anna]

Hi,

Thank you for mentioning the version of the library, this really helped. I tested the code with the older version and indeed the behavior with it is just as you said. This is fixed with the latest official release, so downloading it should solve the problem.

I am not sure what has caused the error you are getting. The Part_UnhideThumb is an element which should be found in the control template. Can you please try to update again and let me know if you encounter the same error again?

Regards,
Anna
Telerik