Click here to Skip to main content
15,559,491 members
Please Sign up or sign in to vote.
2.33/5 (2 votes)
See more:
I am facing a problem while generating reports by exporting to excell.
We are using "SuperflexCell" grid of "grid200"..
It is a nice 3rd party tool, but while exporting to excel it is having a limitation i.e. it can create only one worksheet in an excel file..

In our application we have 2 grids and we want to display each grid in a different sheet of a single excel file..
Due to the limitatio we are unable to do so...

Please some one solve my problem...
I want each grid to be displayed in different sheets in one excel file.
And the file should open immideately on clicking the butto.
I am doing this in vb.net desktop application..

Please help me..

Regards,
Sarthak
Posted
Updated 23-Feb-15 21:01pm
v2
Comments
Michael_Davies 24-Feb-15 11:57am    
Why not use the Excel.Application object to create a workbook with two sheets and fill them in.

From a simple name and address database export:

Dim Excel As Object = CreateObject("Excel.Application")
With Excel
'
' New Work Book With Two Sheets
'
.SheetsInNewWorkbook = 2
.Workbooks.Add()
.Worksheets(1).Select()
'
' Write in the Header Row
'
.cells(1, 1).value = "Title"
.cells(1, 2).value = "Name"
.cells(1, 3).value = "Address1"
.cells(1, 4).value = "Address2"
.cells(1, 5).value = "Address3"
.cells(1, 6).value = "Town"
.cells(1, 7).value = "County"
.cells(1, 8).value = "Postcode"
.cells(1, 9).value = "Country"
.cells(1, 9).EntireRow.Font.Bold = True
'
' Fil in all the Data Rows from the Current View
'
i = 2

Do While rd.Read
.Cells(i, 1).Value = rd("title")
.Cells(i, 2).Value = rd("name")
.Cells(i, 3).Value = rd("addr1")
.Cells(i, 4).Value = rd("addr2")
.Cells(i, 5).Value = rd("addr3")
.Cells(i, 6).Value = rd("town")
.Cells(i, 7).Value = rd("county")
.Cells(i, 8).Value = rd("postcode")
.Cells(i, 9).Value = rd("countries_name")

i += 1
Loop
rd.Close()

.Columns("A:I").EntireColumn.AutoFit()

Excel.Visible = True
End With

System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
Excel = Nothing

Obviously when you want to fill in sheet 2 use Worksheets(2).Select() or use Worksheets(2).Cells(x,y).value = ...

1 solution

Dear Sartha
Please look at this link: [Generating XML and it would be used for excel versions 2003, and 2007]

Test it and and tell me if it works or not. Although it is in c# but converting to VB it is not hard.

Best Regards.
Mahsa Hassankashi.
 
Share this answer
 
Comments
sarthakm 3-Jun-15 2:20am    
Thanks Masha..
Now my problem is solved
sarthakm 3-Jun-15 2:34am    
Hi Masha,

I am generating an excel file in a particular path

the path is concatenated with the file name :

Dim filename As String = Environment.GetFolderPath(Environment.SpecialFolder.Personal) & "\RevLog_reports\ABC"

so the default file name is "ABC."extension""

Here is the code to generate the file:

xlWorkBook = oAPP.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")

For nrow As Integer = 1 To newgrid.Rows - 2
For crow As Integer = 1 To newgrid.Cols - 2
xlWorkSheet.Cells(nrow, crow) = _
newgrid.Cell(nrow, crow).Text
Next
Next

xlWorkBook.SaveAs(filename)



I want the default file to be ABC(0).Xlsx
if second time file is generated it should be ABC(1).Xlsx
like this each time file should be created without ambiguity in the folder.
As I am in a support project I cannot change the default path.
and I have to open the file as a popup while exporting the excel.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900