This is a migrated thread and some comments may be shown as answers.

Export ExcelML

3 Answers 51 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Tracy
Top achievements
Rank 1
Tracy asked on 11 Aug 2012, 03:56 AM
HI,

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

Sort by
0
Daniel
Telerik team
answered on 15 Aug 2012, 02:51 PM
Hello Tracy,

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 If
Next

As 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 Sub

Best regards,
Daniel
the Telerik team
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
0
Tracy
Top achievements
Rank 1
answered on 16 Aug 2012, 02:35 AM
Hi Daniel,

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
0
Daniel
Telerik team
answered on 20 Aug 2012, 01:50 PM
Hello 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
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
Tags
Grid
Asked by
Tracy
Top achievements
Rank 1
Answers by
Daniel
Telerik team
Tracy
Top achievements
Rank 1
Share this question
or