Creating an Excel Spreadsheet programmatically using VB.NET By  G Gnana Arun Ganesh June 10, 2003 The Interoperability services make it very easy to work with COM Capable Applications such as Word and Excel. This article reveals using Excel from a managed application. Excel is the spreadsheet component of Microsoft Office 2000. The majority of Excel programmatic functionality is exposed through Automation via the type library Excel9.olb. The intention of this article is to express that a managed application can interrelate with Excel as a COM server. INTRODUCTION:The Interoperability services make it very easy to work with COM Capable Applications such as Word and Excel. This article reveals using Excel from a managed application. Excel is the spreadsheet component of Microsoft Office 2000. The majority of Excel programmatic functionality is exposed through Automation via the type library Excel9.olb. The intention of this article is to express that a managed application can interrelate with Excel as a COM server. The first step is to create a reference in our project to Excel 9.0 Objects Library. By using Tlbimp tool we can generate Excel.dll. TlbImp Excel9.olb Excel.dll.By adding Excel.dll to our program we can use the functionality of the Excel.Now let us see in detail how to create an Excel Spreadsheet? & Set values to the cell using VB.NET. The codes for Creating, make visible, add a new workbook and to set a value for cell in the Excel file is shown below.  CREATING NEW EXCEL.APPLICATION:Dim exc As New Application ()If exc Is Nothing ThenConsole.WriteLine("ERROR: EXCEL couldn't be started")Return 0End If TO MAKE APPLICATION VISIBLE:exc.set_Visible(0, true) TO GET THE WORKBOOKS COLLECTION:Dim workbooks As Workbooks = exc.WorkbooksDim workbook As _Workbook = workbooks.AddXlWBATemplate.xlWBATWorksheet, 0) TO GET THE WORKSHEETS COLLECTION:Dim worksheet As _Worksheet = CType(sheets.get_Item(1), _Worksheet)If worksheet Is Nothing ThenConsole.WriteLine("ERROR in worksheet == null")End If TO SET THE VALUE FOR CELL:Dim range1 As Range = worksheet.get_Range("C1", Missing.Value)If range1 Is Nothing ThenConsole.WriteLine("ERROR: range == null")End IfConst nCells As Integer = 1Dim args1(1) As [Object]args1(0) = nCellsrange1.GetType().InvokeMember("Value", BindingFlags.SetProperty, Nothing, range1, args1) The Example:Imports SystemImports System.Reflection ' For Missing.Value and BindingFlagsImports System.Runtime.InteropServices ' For COMExceptionImports ExcelClass AutoExcelPublic Shared Function Main() As IntegerDim exc As New ApplicationIf exc Is Nothing ThenConsole.WriteLine("ERROR: EXCEL couldn't be started!")Return 0End Ifexc.set_Visible(0, True)Dim workbooks As Workbooks = exc.WorkbooksDim workbook As _Workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet, 0)Dim sheets As Sheets = workbook.WorksheetsDim worksheet As _Worksheet = CType(sheets.get_Item(1), _Worksheet)If worksheet Is Nothing ThenConsole.WriteLine("ERROR: worksheet == null")End IfDim range1 As Range = worksheet.get_Range("C1", Missing.Value)If range1 Is Nothing ThenConsole.WriteLine("ERROR: range == null")End IfConst nCells As Integer = 1Dim args1(1) As [Object]args1(0) = nCellsrange1.GetType().InvokeMember("Value", BindingFlags.SetProperty, Nothing, range1, args1)Return 100End Function 'MainEnd Class 'AutoExcelNow let us observe how to send a single dimension array to Excel:It is similar to set the value for the cell. Only change is we use array as args2[0] = array2.Const nCell As Integer = 5Dim range2 As Range = worksheet.get_Range("A1", "E1")Dim array2(nCell) As IntegerDim i As IntegerFor i = 0 To (array2.GetLength(0)) - 1array2(i) = i + 1Next i Dim args2(1) As [Object]args2(0) = array2range2.GetType().InvokeMember("Value", BindingFlags.SetProperty, Nothing, range2, args2)End Function '__unknownThe OutPut:Conclusion:With the help of TlbImp.exe tool we can generate .NET assembly from Type library files and we can use that functionality of Type library file in VB.NET.

评论