pinoyz

hi there... i need ur help in spreadsheet gear.... how can i get the data from the datagridview to spreadsheet gear

i have here a sample program that i made but still got an error (nothing happens)

dim con as new oledbconnection(constring)

dim sqlcom oledbcommand(sqlstatement)

dim adapt as new oledbadapter(sqlcom)

dim ds as dataset()

dim dt as datatable()

sqlcom.connection.open()

adapt.fill(dt)

' Create a new workbook and worksheet.
Dim workbook As SpreadsheetGear.IWorkbook = SpreadsheetGear.Factory.GetWorkbook()
Dim worksheet As SpreadsheetGear.IWorksheet = workbook.Worksheets("Sheet1")

' Get the top left cell for the DataTable.
Dim range As SpreadsheetGear.IRange = worksheet.Cells("A1")

' Copy the DataTable to the worksheet range.
range.CopyFromDataTable(dt, SpreadsheetGear.Data.SetDataFlags.None)

' Auto size all worksheet columns which contain data
worksheet.UsedRange.Columns.AutoFit()

sqlcom.connection.close()




Re: Visual Basic Express Edition js06 nid help (spreadsheet gear)

js06

I didn't see this post, the other thread alerted me and i responded in that one

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1230350&SiteID=1






Re: Visual Basic Express Edition js06 nid help (spreadsheet gear)

js06

Were you able to make any progress

I am sorry i could not get back to you sooner.

I can give you some help to get it going.

Let me know where you are at with it and exactly what you want to do.






Re: Visual Basic Express Edition js06 nid help (spreadsheet gear)

pinoyz

hi js06

i'm want to do is details(content) in the dataset will transfer into spreadsheet gear....






Re: Visual Basic Express Edition js06 nid help (spreadsheet gear)

js06

To get it started you will need the following items:

you need to create a workbook with either excel or spreadsheet gear. this will be your template. The cells need to have the same format as your datatable cells/columns. (i think this is why you had an error on the font.)

on your form you need a worbookview. When you right click the view then click workbook explorer. Right click on workbookset and click open workbook. select your workbook and then open. The other workbook (Book1) that is in the explorer you can right click and then close. Your workbook is now your template.

You will have to start working directly with the view now and not memory.

' Get the top left cell for the DataTable.
Dim range As SpreadsheetGear.IRange = worksheet.Cells("A1")

instead you need this:

WorkbookView1.GetLock()

Me.WorkbookView1.ActiveWorksheet.Range("A1").Select()

I believe from here the rest of your code should work with some changes.

' Copy the DataTable to the worksheet range.
range.CopyFromDataTable(datatable, SpreadsheetGear.Data.SetDataFlags.None)

' Auto size all worksheet columns which contain data
worksheet.UsedRange.Columns.AutoFit()

If you don't want the view to be visible set the visible property to false in the VB properties explorer.

From there you can save the file directly to any location either by code or with save dialogue.

The following is the code to save the file to the C directory with the name Report.

Me.WorkbookView1.ActiveWorkbook.SaveAs("C:\\Report.xls", SpreadsheetGear.FileFormat.XLS97)

Me.workbookview1.ReleaseLock()

The whole code would basically be the following:

Dim con as new oledbconnection(connectionstrings)

Dim sqlcom as new oledbcommand(select * from table1,con)

Dim adapt as new oledbadapter(sqlcom)

Dim ds as new dataset()

Dim datatable As System.Data.DataTable = dataset.Tables("OrderItems")

sqlcom.connection.open()

adapt.fill(ds)

sqlcom.connection.close() a

Me.WorkbookView1.GetLock()

Me.WorkbookView1.ActiveWorksheet.Range("A1").Select()

Me.WorkbookView1.Activecell.CopyFromDataTable(datatable, SpreadsheetGear.Data.SetDataFlags.None)

' Auto size all worksheet columns which contain data
Me.WorkbookView1.ActiveWorksheet.UsedRange.Columns.AutoFit()

Me.WorkbookView1.ActiveWorkbook.SaveAs("C:\\Report.xls", SpreadsheetGear.FileFormat.XLS97)

Me.workbookview1.ReleaseLock()

I am not sure about the code for the datatable up above this is what i copied from your example.

Try this out and let me know if it works. If not i will have to look at the solution explorer for Spreadsheet Gear.






Re: Visual Basic Express Edition js06 nid help (spreadsheet gear)

pinoyz

hi.

is it true that when i register my vbee...the spreadsheet gear is now free

when the 30days trial expires means i can't use it anymore






Re: Visual Basic Express Edition js06 nid help (spreadsheet gear)

js06

You get the free version when you register your VB Express and then go to the registration benefits portal.  The one you download from this site is free.

If you downloaded the one from the spreadsheet gear website, that version will expire.  From what i understand your free version will never expire but like VB Express they do not offer support for it.  The support they offer is only for their paid version.  So if you donwload their paid version then they will give you support for 30 days.  After that you are on your own.  That is why i have been telling others like us about the free version so that we can help each other in the forums.

I downloaded the paid version but i have been using the free version since that is what i plan to keep using for a while.  The advantages i have seen with the paid version is that they are constantly upgrading it.  So far the free version has been able to do everything i have been looking for.

Were you able to get the code to work






Re: Visual Basic Express Edition js06 nid help (spreadsheet gear)

pinoyz

can you provide the link for the free version of spreadsheet gear... i'll already register my vbee into my office...

yup!! the support gave it to me.... and works fine... i'll post here the code...






Re: Visual Basic Express Edition js06 nid help (spreadsheet gear)

js06

You will receive an email after you register. I am not sure if I am supposed to post this link. Microsoft is very particular. I would prefer not to step on anyone's toes. As long as you register you will get the link in your email.

If you didn't get an email, you can register again and they will send you another email.

I would definetely like to see the code that you got to work. Thanks






Re: Visual Basic Express Edition js06 nid help (spreadsheet gear)

pinoyz

hi js06,

there's no spreedsheetgear on benefit portal of visual studio express.... how sad.....






Re: Visual Basic Express Edition js06 nid help (spreadsheet gear)

js06

I just checked it and it's still there. it hasn't changed since i first registered on the 12th of february.




Re: Visual Basic Express Edition js06 nid help (spreadsheet gear)

pinoyz

benefits portal visual studio right

all i can see are

DevComponents

SkinCrafter's SkinCrafter Light

Trossen RoboticsCareerBuilder

CareerBuilder

MAKE Magazine

MAKE Controller

you mean this






Re: Visual Basic Express Edition js06 nid help (spreadsheet gear)

js06

Yes , these things are there also and quite a few others.  I am looking at it right now and i see the second one is spreadsheet gear for .net 2006.  Check your filter and make sure you have (all) selected.  I would not think that i would receive a different link than anyone else. 

I am sure there has to be a reason why you can't see it.  Is this the link from your registration email

I would post the link but i do not think they would appreciate me giving away links where anyone could use it without registering.  And also the license says that i am not allowed to distribute any of the downloads.






Re: Visual Basic Express Edition js06 nid help (spreadsheet gear)

pinoyz

hi js06

i select all but no spreadsheet gear.......

this is the link from registration email

Registration Benefit Portal

Registration Benefit Portal

http://go.microsoft.com/fwlink/ LinkID=77367&clcid=0x409

but no spreadsheet gear don't know why...

if you want to gave me the link...email me @ christian_gajo@yahoo.com






Re: Visual Basic Express Edition js06 nid help (spreadsheet gear)

js06

This is really puzzling me because i clicked on the link you posted and it takes me to the same place my link does. I still see the spreadsheet gear as the second one. You might have to talk to microsoft to find out what the problem is.

I assure you that it is there. I posted a thread asking if anyone else has seen it to see if anyone else is having the same problem.