Exporting to “Excel” from Xceed Grid

Xceed Grid has a fairly easy one line code to export a grid to Excel file. What’s the  catch (at least for me) ? It simply doesn’t allow multiple sheets in a file.(Please correct me if I am wrong).

But I don’t need a multitude of files for a group of reports. And I don’t have Excel installed either !

I have three grids to export.So I created three temporary  .xml (excel) files using the ExcelExporter Class from Xceed., say file1.xml, file2.xml and file3.xml

sample:    Dim excelExp As New Xceed.Grid.Exporting.ExcelExporter()

excelExp.Export(grid1, “c:\temp\file1.xml”)
Now loaded these three files to three different strings string1, string2 and string3 using IO.StreamReader Class.

sample:    Dim sr As IO.StreamReader = New IO.StreamReader(“c:\temp\file1.xml”)
Dim string1 As String = sr.ReadToEnd()

string2 = string2.Substring(string2.IndexOf(“<ss:Worksheet”))

string3 = string3.Substring(string3.IndexOf(“<ss:Worksheet”))

these two lines are used to extract the sheets from their books (including the last </Workbook> tag).

string1= replace(string1,”</Workbook>”,string2)

string1= replace(string1,”</Workbook>”,string3)

These two lines simply insert the worksheets into the first string.

Now use a IO.Streamwriter to Write the string1 to the desired Excel file !

No Excel application is needed to create this excel file.

You can further improvise the code to manipulate the Header, Footer etc. It is very easy to use the replace command and insert the desired tags !

If someone has an easier solution , please comment..


Post a Comment

Required fields are marked *

%d bloggers like this: