I am using the ExcelML format to export a grid and have a couple of questions regarding the formatting.
1. How can apply a border to the header row. I have looked at the following code that is in the documentation and added it to the
ExcelMLExportStylesCreated event but it gives me an error message Object reference not set to an instance of an object. on this line
- borderStyle.PositionType = DirectCast(i, PositionType)
Also I can't figure out how to apply the style to the header row.
Dim cstyle As New StyleElement("MyCustomStyle") Dim borders As New BorderStylesCollection() Dim borderStyle As BorderStyles For i As Integer = 1 To 4 'four borders borderStyle.PositionType = DirectCast(i, PositionType) borderStyle.Color = System.Drawing.Color.Black borderStyle.LineStyle = LineStyle.Continuous borderStyle.Weight = 1.0R borders.Add(borderStyle) Next For Each border As BorderStyles In borders cstyle.Borders.Add(border) Next e.Styles.Add(cstyle)
2. How can I insert a row at the beginning of the output. I have seen many examples on how to do this when using the HTML format but there are no examples when using the ExportML format. The following can be used for the HTML format but there is no <body> tag in when you use the ExportML
Protected Sub rgSummaryReport_GridExporting(source As Object, e As Telerik.Web.UI.GridExportingArgs)
Dim title As String = If(_reportCriteria.AppIsFinalSubmitted, " Final-Submission Report", "Pre-Submission Report")
Dim strHeader As New StringBuilder("<h3><center>" + title + "</center></h3><br>")
strHeader.Append("<h5><b>Contract ID: " + Session("contract_id") + "<br>")
strHeader.Append("Contract Name: " + Session("contract_name") + "<br><br>")
strHeader.Append("Report data is valid as of " + lblReportTimestamp.Text + "</b></h5>")
e.ExportOutput = e.ExportOutput.Replace("<head>", "<head><style type=""text/css"">td {border: 0.1pt solid #000000;}</style>")
e.ExportOutput = e.ExportOutput.Replace("<body>", "<body>" + strHeader.ToString())
e.ExportOutput = e.ExportOutput.Replace("</body>", "Report Downloaded: " + DateTime.Now.ToString() + "</body>")
End Sub
Thank you for your help.
Tracy
3 Answers, 1 is accepted
If you want to add borders to an existing style (like headerStyle) you could use the following approach:
For Each style As StyleElement In e.Styles If style.Id = "headerStyle" Then Dim borders As New BorderStylesCollection() Dim borderStyle As BorderStyles For i As Integer = 1 To 4 'four borders borderStyle = New BorderStyles() borderStyle.PositionType = DirectCast(i, PositionType) borderStyle.Color = System.Drawing.Color.Black borderStyle.LineStyle = LineStyle.Continuous borderStyle.Weight = 1.0 borders.Add(borderStyle) Next For Each border As BorderStyles In borders style.Borders.Add(border) Next End IfNextAs to the second question, inserting a new row is an easy task. Here is an example:
Protected Sub RadGrid1_ExcelMLWorkBookCreated(sender As Object, e As GridExcelMLWorkBookCreatedEventArgs) Handles RadGrid1.ExcelMLWorkBookCreated Dim newRow As New RowElement() Dim newCell As New CellElement() newCell.Data.DataItem = "TEST" newRow.Cells.Add(newCell) e.WorkBook.Worksheets(0).Table.Rows.Insert(1, newRow)End SubBest regards,
Daniel
the Telerik team
Rank 1
Thank you for your response. The examples worked great but I have a couple more questions.
1. When I inserted the row it inserted on row 2 (below the columns). Is there a way to insert the row at row 1.
2. Is there a way to put formulas in the row immediately following the data rows.
3. The text I inserted was in column 1. Is there a way to say insert data at column 3.
4. Is there any documentation on exporting using the ExportML format.
I have read through the ExportML Basics and Structure documents but they don't give much information on the options for the elements/properties.
For example, i found the following in another forum post that shows how to set the AutoFilter range but I can't find any documentation on how to set that range, like what do the parameters stand for. I can see that the first parameter is the range, but what are the other parameters. Is there any documentation on what these parameters are?
e.Worksheet.AutoFilter.Range = String.Format("R{0}C{1}:R{0}C{2}", 1, 1, e.Worksheet.Table.Columns.Count + 1);
I am used to using the Excel Interop and being able to manipulate almost anything in the worksheet. I realize that I will not have all the functionality that is available in the Excel Interop but there seems to be a lot of things that I can do that are not documented.
Thank you for your help.
Tracy
Straight onto your questions:
1. Just insert the row at position 0. The index is zero-based.
2. You could add formulas using the Formula attribute as shown below:
newCell.Attributes("ss:Formula") = "=COUNTA(R2C1:R5C5)"3. Yes, you can. You will have to add empty cells however.
4. The R1C1 format is actually Microsoft Excel reference style. You can find more info here at Microsoft Office website here:
About cell and range references (Microsoft Office website)
Of course, ExcelML (Microsoft XMLSS) is no match for MS Office PIA in terms of functionality but ExcelML does not require any external DLLs or Microsoft Office to be installed.
Best regards,
Daniel
the Telerik team