Как создать файл excel в vb net

How to create Excel file in VB.Net

Automation to Excel allows you to perform actions such as creating a new workbook, adding data to the workbook, or creating charts etc. The following VB.Net code example shows how to use COM interop to create an Excel file. Before going to create new Excel file programmatically in VB.Net, you must have Excel installed on your system for this code to run properly.

Excel Library

In order to access the object model from Visual VB.NET, you have to add the Microsoft Excel 12.0 Object Library to your current project.

Create a new project and add a Command Button to your VB.Net Form.

How to use COM Interop to Create an Excel Spreadsheet

Form the following images you can find how to add Excel reference library in your VB.Net project.

Select reference dialogue from Project menu.

How to create an Excel Document Programmatically

First we have to initialize the Excel application Object in your VB.Net application.

Before creating new Excel Workbook, you should check whether Excel is installed in your system.

Then create new Workbook

After creating the new Workbook, next step is to write content to worksheet

In the above code we write the data in the Sheet1, If you want to write data in sheet 2 then you should code like this.

Save Excel file (SaveAs() method)t

After write the content to the cell, next step is to save the excel file in your system.

How to properly clean up Excel interop objects

Finally, we have to properly clean up Excel interop objects or release Excel COM objects. Here we write a function to clean up the Excel Application, Excel Workbook and Excel Worksheet Objects.

Creating an Excel Spreadsheet Programmatically

Источник

VB.Net – Excel Sheet

VB.Net обеспечивает поддержку взаимодействия между объектной моделью COM Microsoft Excel 2010 и вашим приложением.

Чтобы использовать эту совместимость в вашем приложении, вам необходимо импортировать пространство имен Microsoft.Office.Interop.Excel в ваше приложение Windows Form.

Создание приложения Excel из VB.Net

Давайте начнем с создания приложения Window Forms, выполнив следующие шаги в Microsoft Visual Studio: Файл → Новый проект → Приложения Windows Forms.

Наконец, нажмите OK, Microsoft Visual Studio создаст ваш проект и отобразит следующую форму Form1 .

Вставьте элемент управления Button1 в форму.

Добавьте ссылку на библиотеку объектов Microsoft Excel в свой проект. Для этого –

Выберите Добавить ссылку в меню проекта.

Выберите Добавить ссылку в меню проекта.

На вкладке COM найдите библиотеку объектов Microsoft Excel и нажмите «Выбрать».

На вкладке COM найдите библиотеку объектов Microsoft Excel и нажмите «Выбрать».

Дважды щелкните окно кода и заполните событие Click для Button1, как показано ниже.

Когда приведенный выше код будет выполнен и запущен с использованием кнопки « Пуск» , доступной на панели инструментов Microsoft Visual Studio, появится следующее окно:

При нажатии на кнопку отобразится следующий лист Excel. Вам будет предложено сохранить рабочую книгу.

Источник

Excel Sheet in VB.net – How to Create an Excel File Using VB.net

Excel Sheet in VB.net – VB.net makes it possible for the COM object model of Microsoft Excel 2010 to work with your application.

To use this interoperability in your application, you need to add the namespace Microsoft.Office.Interop.Excel to your Windows Form Application.

Today I will teach you about How to Create an Excel File Using VB.net. Create an Excel File Using Visual Basic.Net provides support for interoperability between the COM object model of Microsoft Excel and your application.

Table of contents

We have learned how to use the Regular Expression in VB.net in the previous lesson. In this lesson we shall learn How To Write a Program for Excel Sheet in VB.net.

What is Visual Basic’s purpose?

The third-generation programming language was created to aid developers in the creation of Windows applications. It has a programming environment that allows programmers to write code in.exe or executable files.

They can also utilize it to create in-house front-end solutions for interacting with huge databases. Because the language allows for continuing changes, you can keep coding and revising your work as needed.

However, there are some limits to the Microsoft Visual Basic download. If you want to make applications that take a long time to process, this software isn’t for you.

That implies you won’t be able to use VB to create games or large apps because the system’s graphic interface requires a lot of memory and space.

Furthermore, the language is limited to Microsoft and does not support other operating systems.

What is Excel Sheet in VB.net?

In Excel documents, a Worksheet is a group of cells that are set up in rows and columns. It is the part of the computer you use to enter data.

Each worksheet has 1048576 rows and 16384 columns, making it a big table that you can use to organize information.

So let’s get started:

How to Create an Excel File Using VB.net

Steps in Creating an Excel Application from VB.net.

Time needed: 5 minutes.

Creating an Excel Application from VB.net

Step 1: Create New Project

First is open the Visual Basic, Select File on the menu, then click New and create a new project.

Step 2: Name your Project

After that, Add a Reference to Microsoft Excel Object Library to your project.
To do this:
*Select add reference from the project menu.

Step 5: Locate Microsoft Excel Object Library

Double click the code window and add this following code to the top of Public Class Form1.

Imports Excel = Microsoft.Office.Interop.Excel
Step 7: Double click the Button and add this following code

Then, Go back to Design View and Double click the Button and add this following code.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim appXL As Excel.Application
Dim wbXl As Excel.Workbook
Dim shXL As Excel.Worksheet
Dim raXL As Excel.Range
appXL = CreateObject(“Excel.Application”)
appXL.Visible = True
wbXl = appXL.Workbooks.Add
shXL = wbXl.ActiveSheet
shXL.Cells(1, 1).Value = “FIRST NAME”
shXL.Cells(1, 2).Value = “LAST NAME”
shXL.Cells(1, 3).Value = “FULL NAME”
shXL.Cells(1, 4).Value = “SUBJECTS”
With shXL.Range(“A1”, “D1”)
.Font.Bold = True
.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
End With
Dim pupils(5, 2) As String
pupils(0, 0) = “Markfil”
pupils(0, 1) = “Baldomero”
pupils(1, 0) = “Juvy”
pupils(1, 1) = “Aral”
pupils(2, 0) = “Andrew”
pupils(2, 1) = “Braza”
pupils(3, 0) = “Carlie”
pupils(3, 1) = “Golez”
pupils(4, 0) = “Jesriel”
pupils(4, 1) = “Agita”
shXL.Range(“A2”, “B6”).Value = pupils
raXL = shXL.Range(“C2”, “C6”)
raXL.Formula = “=A2 & “” “” & B2″
With shXL
.Cells(2, 4).Value = “English”
.Cells(3, 4).Value = “Algebra”
.Cells(4, 4).Value = “Physics”
.Cells(5, 4).Value = “Trigonometry”
.Cells(6, 4).Value = “Theology”
End With
raXL = shXL.Range(“A1”, “D1”)
raXL.EntireColumn.AutoFit()
appXL.Visible = True
appXL.UserControl = True
raXL = Nothing
shXL = Nothing
wbXl = Nothing
appXL.Quit()
appXL = Nothing
Exit Sub
Err_Handler:
MsgBox(Err.Description, vbCritical, “Error: ” & Err.Number)
End Sub

Step 8: Click F5 to run the program.

Finally, Click F5 to run the program.

Program Output:

Download the Source Code:

Summary

This article shows how to make an Excel sheet and how to fill a range of cells with a number of different values. This article also shows how to get a range of cells as an array of cells.

Источник

Vb net создать файл excel

Answered by:

Question

VWD 2008 Express. Visual Basic.

Is it possible to build an Excel workbook from VB code behind an aspx page. What do I need to do? Can anyone provide a code sample? Thanks.

Answers

I needed to use the following to get the Excel 2003 library:

xlApp = New Excel.ApplicationClass (Which seems to create an Excel 2007 file)

xlApp = New Excel.Application (Which creates an Excel 2003 file)

So the whole thing is:

Not knowing your exact needs, this code sample from ASPNet101.com shows how to export from a Gridview to an Excel file

Thanks for the reply. What I need and want to do is to build an Excel file NOT from a GridView control, but from data I am generating programmatically. I need to be able to create the Excel file, and populate some of its cells with data I have programmatically. I know how to create a regular text file (>CSV) from my VB code behind, but I really need to create a true Excel file. Any other ideas? Thnaks.

I am trying to work with the code example to which you refer. It produces an Excel worksheet, but the first row is blank and then row 2 has my headings (field names top be used in a mail merge). Why would a blank row be placed before the actual header row? How can I get rid of it? Thanks for any additional help.

There are three approaches can export to excel file, approach 2 and 3 are helpful to you.

1.One is using GridView that is bound on DataSet from database. And export GridView to Excel File. http://forums.asp.net/t/1197704/2076903.aspx .
2.Another approach is more flexible: Write an Excel file directly via the data in DataSet. http://forums.asp.net/p/1222903/2189537.aspx .

3.As to export the data to existing excel file or write data into specific cell of excel file, you can use Excel Library: http://forums.asp.net/p/1235485/2244394.aspx .

In this approach, you can define more details about the cells. For example, define the style and format of cells in excel file that is unavailable with the other two approaches.

Thanks for the link. This deals with Visual Studio.NET. I am using Visual Web Developer 2008 Express. However, I went through the steps of adding the reference to the Microsoft Excel 11.0 Object Library (Website>Add Reference), then adding the following at the top of my class module:

But I get the error mesage:

Warning 1 Namespace or type specified in the Imports ‘Microsoft.Office.Interop.Excel’ doesn’t contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn’t use any aliases.

What other steps might I need to use the example at the link you gave in my VWD 2008 application?

Источник

VB.Net — Excel Sheet

VB.Net provides support for interoperability between the COM object model of Microsoft Excel 2010 and your application.

To avail this interoperability in your application, you need to import the namespace Microsoft.Office.Interop.Excel in your Windows Form Application.

Creating an Excel Application from VB.Net

Let’s start with creating a Window Forms Application by following the following steps in Microsoft Visual Studio: File → New Project → Windows Forms Applications

Finally, select OK, Microsoft Visual Studio creates your project and displays following Form1.

Insert a Button control Button1 in the form.

Add a reference to Microsoft Excel Object Library to your project. To do this −

Select Add Reference from the Project Menu.

On the COM tab, locate Microsoft Excel Object Library and then click Select.

Double click the code window and populate the Click event of Button1, as shown below.

When the above code is executed and run using Start button available at the Microsoft Visual Studio tool bar, it will show the following window −



Clicking on the Button would display the following excel sheet. You will be asked to save the workbook.

Источник

VB.Net обеспечивает поддержку взаимодействия между объектной моделью COM Microsoft Excel 2010 и вашим приложением.

Чтобы использовать эту совместимость в вашем приложении, вам необходимо импортировать пространство имен Microsoft.Office.Interop.Excel в ваше приложение Windows Form.

Создание приложения Excel из VB.Net

Давайте начнем с создания приложения Window Forms, выполнив следующие шаги в Microsoft Visual Studio: Файл → Новый проект → Приложения Windows Forms.

Наконец, нажмите OK, Microsoft Visual Studio создаст ваш проект и отобразит следующую форму Form1 .

Вставьте элемент управления Button1 в форму.

Добавьте ссылку на библиотеку объектов Microsoft Excel в свой проект. Для этого –

• Выберите Добавить ссылку в меню проекта.

Выберите Добавить ссылку в меню проекта.

• На вкладке COM найдите библиотеку объектов Microsoft Excel и нажмите «Выбрать».

На вкладке COM найдите библиотеку объектов Microsoft Excel и нажмите «Выбрать».

• Нажмите ОК.

Нажмите ОК.

Дважды щелкните окно кода и заполните событие Click для Button1, как показано ниже.

'  Add the following code snippet on top of Form1.vb
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
   Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
      Dim appXL As Excel.Application
      Dim wbXl As Excel.Workbook
      Dim shXL As Excel.Worksheet
      Dim raXL As Excel.Range
      
      ' Start Excel and get Application object.
      appXL = CreateObject("Excel.Application")
      appXL.Visible = True
      
      ' Add a new workbook.
      wbXl = appXL.Workbooks.Add
      shXL = wbXl.ActiveSheet
      
      ' Add table headers going cell by cell.
      shXL.Cells(1, 1).Value = "First Name"
      shXL.Cells(1, 2).Value = "Last Name"
      shXL.Cells(1, 3).Value = "Full Name"
      shXL.Cells(1, 4).Value = "Specialization"
      
      ' Format A1:D1 as bold, vertical alignment = center.
      With shXL.Range("A1", "D1")
         .Font.Bold = True
         .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
      End With
      
      ' Create an array to set multiple values at once.
      Dim students(5, 2) As String
      students(0, 0) = "Zara"
      students(0, 1) = "Ali"
      students(1, 0) = "Nuha"
      students(1, 1) = "Ali"
      students(2, 0) = "Arilia"
      students(2, 1) = "RamKumar"
      students(3, 0) = "Rita"
      students(3, 1) = "Jones"
      students(4, 0) = "Umme"
      students(4, 1) = "Ayman"
      
      ' Fill A2:B6 with an array of values (First and Last Names).
      shXL.Range("A2", "B6").Value = students
      
      ' Fill C2:C6 with a relative formula (=A2 & " " & B2).
      raXL = shXL.Range("C2", "C6")
      raXL.Formula = "=A2 & "" "" & B2"
       
      ' Fill D2:D6 values.
      With shXL
         .Cells(2, 4).Value = "Biology"
         .Cells(3, 4).Value = "Mathmematics"
         .Cells(4, 4).Value = "Physics"
         .Cells(5, 4).Value = "Mathmematics"
         .Cells(6, 4).Value = "Arabic"
      End With
      
      ' AutoFit columns A:D.
      raXL = shXL.Range("A1", "D1")
      raXL.EntireColumn.AutoFit()
      
      ' Make sure Excel is visible and give the user control
      ' of Excel's lifetime.
      appXL.Visible = True
      appXL.UserControl = True
      
      ' Release object references.
      raXL = Nothing
      shXL = Nothing
      wbXl = Nothing
      appXL.Quit()
      appXL = Nothing
      Exit Sub
Err_Handler:
      MsgBox(Err.Description, vbCritical, "Error: " & Err.Number)
   End Sub
End Class

Когда приведенный выше код будет выполнен и запущен с использованием кнопки « Пуск» , доступной на панели инструментов Microsoft Visual Studio, появится следующее окно:

При нажатии на кнопку отобразится следующий лист Excel. Вам будет предложено сохранить рабочую книгу.