Exporting a Datagrid to Excel
-
Hello, I am trying to export the contents of a datagrid to Excel. Here's the code I have so far: 'Fire up an Excel instance Dim ExcelApp As New Excel.Application ExcelApp.Visible = True Dim workbooks As Workbooks = ExcelApp.Workbooks Dim workbook As _Workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet) Dim sheets As Sheets = workbook.Worksheets Dim worksheet As _Worksheet = CType(sheets.Item(1), _Worksheet) 'Put some data on the Worksheet -- This doesn't work, but throws no errors Dim nCell As Integer = 5 Dim Range2 As Range = worksheet.Range("A1", "E1") Dim array2(nCell) As Integer Dim i As Integer For i = 0 To (array2.GetLength(0)) - 1 array2(i) = i + 1 Next Dim args2(1) As Object args2(0) = array2 Range2.GetType().InvokeMember("Value", BindingFlags.SetProperty, Nothing, Range2, args2) Everything works except the last line. Actually, the only thing I have been successful at doing was getting the Excel app to fire up. Beyond that I am left looking at beautiful, although empty, white cells. I'm using VS.Net 2005 latest beta. It seems simple enough... Read a datagrid, send the results to an Excel worksheet. I would use the more accepted method of using Response.Write, but due to our website configuration, things get all screwed up with using a Master page and
tags. If anyone can help me out of this bind, I would be a very happy programmer.
-
Hello, I am trying to export the contents of a datagrid to Excel. Here's the code I have so far: 'Fire up an Excel instance Dim ExcelApp As New Excel.Application ExcelApp.Visible = True Dim workbooks As Workbooks = ExcelApp.Workbooks Dim workbook As _Workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet) Dim sheets As Sheets = workbook.Worksheets Dim worksheet As _Worksheet = CType(sheets.Item(1), _Worksheet) 'Put some data on the Worksheet -- This doesn't work, but throws no errors Dim nCell As Integer = 5 Dim Range2 As Range = worksheet.Range("A1", "E1") Dim array2(nCell) As Integer Dim i As Integer For i = 0 To (array2.GetLength(0)) - 1 array2(i) = i + 1 Next Dim args2(1) As Object args2(0) = array2 Range2.GetType().InvokeMember("Value", BindingFlags.SetProperty, Nothing, Range2, args2) Everything works except the last line. Actually, the only thing I have been successful at doing was getting the Excel app to fire up. Beyond that I am left looking at beautiful, although empty, white cells. I'm using VS.Net 2005 latest beta. It seems simple enough... Read a datagrid, send the results to an Excel worksheet. I would use the more accepted method of using Response.Write, but due to our website configuration, things get all screwed up with using a Master page and
tags. If anyone can help me out of this bind, I would be a very happy programmer.
AFAICT, you can't bind an array to an Excel Range. You have to inster the values into the cells, one at a time. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-
AFAICT, you can't bind an array to an Excel Range. You have to inster the values into the cells, one at a time. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
...that seems to be where I am stuck at - inserting the values one at a time. Using the code: Dim args1(1) As Object args1(0) = nCell Range1.GetType().InvokeMember("Value", BindingFlags.SetProperty, Nothing, Range1, args1) ..I get an error that states: "Public member 'GetType' on type 'Range' not found." Since I'm using VS.Net 2005, I am wondering if there is a library I am missing or if the function has been removed altogether. That is where I am at. Inserting the data, even a single cell value is proving to be my ticket to insanity. So close, yet so far.
-
...that seems to be where I am stuck at - inserting the values one at a time. Using the code: Dim args1(1) As Object args1(0) = nCell Range1.GetType().InvokeMember("Value", BindingFlags.SetProperty, Nothing, Range1, args1) ..I get an error that states: "Public member 'GetType' on type 'Range' not found." Since I'm using VS.Net 2005, I am wondering if there is a library I am missing or if the function has been removed altogether. That is where I am at. Inserting the data, even a single cell value is proving to be my ticket to insanity. So close, yet so far.
First, the
Rangeobject is a little more difficult to use when filling in cells like this. TheCellsobject lets you index rows and columns sperately. Second, I'm wondering why your going through all theInvokeMemberstuff when a simple assignment will do:For I As Integer = 1 to 5
worksheet.Cells(I, 1) = I
NextRageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-
First, the
Rangeobject is a little more difficult to use when filling in cells like this. TheCellsobject lets you index rows and columns sperately. Second, I'm wondering why your going through all theInvokeMemberstuff when a simple assignment will do:For I As Integer = 1 to 5
worksheet.Cells(I, 1) = I
NextRageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
I had no idea it could be that simple. This is a project for work (I'm normally a DBA by trade) and I'll plug in first thing Monday morning and give this a whirl. You have been incredibly helpful, Dave and I really appreciate your expertise. (I was just about to give up on this whole Excel thing and tell my boss if he wants the info on the website, just copy/paste it... :-) I'll let you know Monday how it all works out. If I can get just one cell to reflect info off the ASP page, I'll consider this a success. Ray