Hi,
I have a function that reads an excel file, and loads the data into the PivotGrid.
Is there any way to find out the data type of the values read in. For example I want to be able to differentiate between strings and doubles and dates. The is no way to get the data type of the cell from WorksheetCell.
Thanks
Sangeetha
Hello,
Excuse us for the delay. We have a direct way to use excel files into pivot grid. Have you tried to use FlatDataSource with ExcelDataConnectionSettings?
Regards.
Plamen.
Hi Plamen,
Yes I just tried this alternative and it works great. I don't have to know the data/data type and do any kind of conversion - very neat.
I would like some clarification about FlatDataSource to help me better understand it though. I notice that when FlatDataSource is created through a stream its ItemsSource is null, so if I want to retrieve this data how can I do it?
Yes you are right that the ItemsSource is initially null because the data of the excel file is processed asynchronously in the background and once it’s ready the ExcelDataConnectionSettings.ItemsSourceChanged event is fired. At this moment you can get the list of the items.
Look at this snippet:
ExcelDataConnectionSettings excelDataSettings =
new ExcelDataConnectionSettings
{
FileStream = fileStream,
GeneratedTypeName = "ExcelData",
WorksheetName = "Sheet1"
};
This is the way to initialize excel settings instance. You can specify WorksheetIndex instead WorkSheetName. GeneratedTypeName is the name of the type that is dynamically created for you and that is the type of the items found in items source.
Keep in mind that not all excel formats are supported by default so you might have to add fomat mappings if your data does not appear properly:
ExcelDataConnectionSettings.AddExcelCellFormatMapping("\"$\"#,##0.00_);[Red]\\(\"$\"#,##0.00\\)", "{0:C3}");
ExcelDataConnectionSettings.AddExcelDateTimeCellFormatMapping("m/d/yy", "ddd dd MM yyyy");
ExcelDataConnectionSettings.AddExcelDateTimeCellFormatMapping("d/m/yy;@", "M/d/yy");
The online samples always are good starting point, so you can check this out:
http://samples.infragistics.com/sldv/RunSamples.aspx?cn=pivot-grid#/pivot-grid/flatdatasource-excel-file
Great! That clarifies it.
Thank You Plamen.