Gets or sets type of the format string of the spreadsheet cell where data exported to.
[Visual Basic] Public Property FormatIndex As xpFormat [C#] public xpFormat FormatIndex {get; set;}
Gets or sets type of the format string of the spreadsheet cell where data exported to. The default is xpFormat.Format_0 which specifies built-in General format from Microsoft® Excel workbook.
You can use property to specify type of formatting string that will be applied against the cells. Microsoft® Excel has 36 built-in format strings (from xpFormat.Format_0 to xpFormat.Format_35) that cannot be changed, and you also have possibility to define custom formats for the cells setting FormatIndex property to xpFormat.Custom value, and the setting Format property to any custom-defined format string.
FormatIndex property supported only during export of the data into native binary XLS and Microsoft® Excel 2007 XLSX formats and ignored for all other output formats.
[Visual Basic, C#] The following example retrieves DataTable from the sample SQL Server 2000 Pubs database and saves it into native Microsoft® Excel binary format setting Format style of the cells for the hire_date column to custom dd-mmm-yy format.
[Visual Basic, C#] The following example exports data from ADO.NET DataTable to native Microsoft® Excel binary format setting different formatting styles.
[C#]
private void DataTableWithStyles(DataTable dataTableToExport, string dateColumnName) {
VM.xPort.DS2XL xporter;
VM.xPort.Style headerStyle;
VM.xPort.Style contentStyle;
VM.xPort.Style formatStyle;
System.Drawing.Font customFont;
xporter = new VM.xPort.DS2XL();
//Define style for the header.
headerStyle = new VM.xPort.Style("HeaderStyle", dataTableToExport.TableName, -1, 0, -1,
dataTableToExport.Columns.Count - 1);
//Create font that will be used to format header text.
customFont = new System.Drawing.Font("Arial", 8, FontStyle.Bold);
headerStyle.Font = customFont;
headerStyle.HorizontalAlignment = Style.xpHAlignment.Center;
headerStyle.VerticalAlignment = Style.xpVAlignment.Center;
headerStyle.TopBorderLine = Style.xpBorderLineStyle.Double;
headerStyle.BottomBorderLine = Style.xpBorderLineStyle.Double;
headerStyle.BackgroundColor = Color.FromArgb(0, 184, 204, 228);
headerStyle.UnderlineStyle = Style.xpUnderlineStyle.Double;
headerStyle.ReadingDirection = Style.xpReadingDirection.RightToLeft;
//Add custom style to the collection of styles. If we do not add style, it will not be applied.
xporter.Styles.Add(headerStyle);
//Create style for content
contentStyle = new VM.xPort.Style("ContentStyle", dataTableToExport.TableName, 0, 0,
dataTableToExport.Rows.Count - 1, dataTableToExport.Columns.Count - 1);
customFont = new System.Drawing.Font("Arial", 8, FontStyle.Regular);
contentStyle.Font = customFont;
contentStyle.HorizontalAlignment = Style.xpHAlignment.Left;
contentStyle.VerticalAlignment = Style.xpVAlignment.Center;
contentStyle.ReadingDirection = Style.xpReadingDirection.RightToLeft;
//Add custom style to the collection of styles
xporter.Styles.Add(contentStyle);
//Make a clone of the contentStyle style and set only specific properties of custom style.
formatStyle = contentStyle.Clone("DateValues", dataTableToExport.TableName, 0,
dataTableToExport.Columns[dateColumnName].Ordinal, dataTableToExport.Rows.Count - 1,
dataTableToExport.Columns[dateColumnName].Ordinal);
//Set format of the date values in specific column.
//Refer to documentation about which built-in Excel formats
//are available. Use custom (not built-in) format string.
formatStyle.FormatIndex = VM.xPort.Style.xpFormat.Custom;
formatStyle.Format = @"MMM dd, yyyy";
//Add custom style to the collection of styles.
xporter.Styles.Add(formatStyle);
xporter.Export(dataTableToExport, "DataTableWithStyles",
xpOutputFormat.Excel8, true, true);
}
[Visual Basic]
Private Sub DataTableWithStyles(ByVal dataTableToExport As DataTable, ByVal dateColumnName As String)
Dim xporter As VM.xPort.DS2XL
Dim headerStyle As VM.xPort.Style
Dim contentStyle As VM.xPort.Style
Dim customFont As System.Drawing.Font
Dim formatStyle As VM.xPort.Style
xporter = New VM.xPort.DS2XL()
'Define style for the header.
headerStyle = New VM.xPort.Style("HeaderStyle", dataTableToExport.TableName, -1, 0, -1, _
dataTableToExport.Columns.Count - 1)
'Create font that will be used to format header text.
customFont = New System.Drawing.Font("Arial", 8, FontStyle.Bold)
headerStyle.Font = customFont
headerStyle.HorizontalAlignment = Style.xpHAlignment.Center
headerStyle.VerticalAlignment = Style.xpVAlignment.Center
headerStyle.TopBorderLine = Style.xpBorderLineStyle.Double
headerStyle.BottomBorderLine = Style.xpBorderLineStyle.Double
headerStyle.BackgroundColor = Color.FromArgb(0, 184, 204, 228)
headerStyle.UnderlineStyle = Style.xpUnderlineStyle.Double
headerStyle.ReadingDirection = Style.xpReadingDirection.RightToLeft
'Add custom style to the collection of styles. If we do not add style, it will not be applied.
xporter.Styles.Add(headerStyle)
'Create style for content
contentStyle = New Style("ContentStyle", dataTableToExport.TableName, 0, 0, _
dataTableToExport.Rows.Count - 1, dataTableToExport.Columns.Count - 1)
customFont = New System.Drawing.Font("Arial", 8, FontStyle.Regular)
contentStyle.Font = customFont
contentStyle.HorizontalAlignment = Style.xpHAlignment.Left
contentStyle.VerticalAlignment = Style.xpVAlignment.Center
contentStyle.ReadingDirection = Style.xpReadingDirection.RightToLeft
'Add custom style to the collection of styles
xporter.Styles.Add(contentStyle)
'Make a clone of the contentStyle style and set only specific properties of custom style.
formatStyle = contentStyle.Clone("DateValues", dataTableToExport.TableName, 0, _
dataTableToExport.Columns(dateColumnName).Ordinal, dataTableToExport.Rows.Count - 1, _
dataTableToExport.Columns(dateColumnName).Ordinal)
'Set format of the date values in specific column.
'Refer to documentation about which built-in Excel formats
'are available. Use custom (not built-in) format string.
formatStyle.FormatIndex = VM.xPort.Style.xpFormat.Custom
formatStyle.Format = "MMM dd, yyyy"
'Add custom style to the collection of styles.
xporter.Styles.Add(formatStyle)
xporter.Export(dataTableToExport, "DataTableWithStyles", _
xpOutputFormat.Excel8, True, True)
End Sub
Platforms: Windows Server 2008 family, Windows Server 2003 family, Windows 2000 family, Windows Vista family, Windows XP family