Как открыть книгу excel в visual studio

Давайте научимся быстро и просто создавать и записывать файлы Excel с помощью visual studio c#. Наше самое простое приложение Windows Forms будет брать из текстбокса текст и заносить его в первую ячейку. Статья написана специально для Сергея =).

Начать необходимо с подключения библиотеки Microsoft.Office.Interop.Excel. Выглядеть это должно так:

Если у вас при открытии обозревателя решений – Ссылки – правая кнопка – Добавить ссылку – Сборки – в списке нет Microsoft.Office.Interop.Excel, то добавьте её через Nuget. Проект – управление пакетами NuGet – в строке поиска Excel:

Теперь создайте новый проект Windows Forms и на форму закиньте текстбокс и кнопку. На кнопки кликните два раза, откроется исходный код. В самом верху допишите следующее:

using Excel = Microsoft.Office.Interop.Excel;

А в методе button1_Click замените так:

private void button1_Click(object sender, EventArgs e)
        {
            string fileName = "D:\temp\test.xls";

            try
            {
                var excel = new Excel.Application();

                var workBooks = excel.Workbooks;
                var workBook = workBooks.Add();
                var workSheet = (Excel.Worksheet)excel.ActiveSheet;

                workSheet.Cells[1, "A"] = textBox1.Text;

                workBook.SaveAs(fileName);
                workBook.Close();
            }
            catch (Exception ex) {
                MessageBox.Show("Ошибка: "+ ex.ToString());
            }

            MessageBox.Show("Файл "+ Path.GetFileName (fileName) + " записан успешно!");

        }

Вот, собственно говоря и все. Текст из текстбокса запишется в ячейку A1. Обратите внимание, что папка temp на диске уже должна существовать.

Дополнение. Прочитать первую ячейку

Это тоже просто:

 string fileName = "D:\temp\test.xls";

Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(fileName, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "t", false, false, 0, true, 1, 0);
Excel._Worksheet xlWorksheet = (Excel._Worksheet)xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;

string temp = (string)(xlRange.Cells[1, 1] as Excel.Range).Value2;// 1 1 - адрес 1-й ячейки
MessageBox.Show(temp);

Автор этого материала — я — Пахолков Юрий. Я оказываю услуги по написанию программ на языках Java, C++, C# (а также консультирую по ним) и созданию сайтов. Работаю с сайтами на CMS OpenCart, WordPress, ModX и самописными. Кроме этого, работаю напрямую с JavaScript, PHP, CSS, HTML — то есть могу доработать ваш сайт или помочь с веб-программированием. Пишите сюда.

заметки, си шарп, excel

This post describes importing and exporting your Microsoft Excel XLSX and XLSM spreadsheets directly in your .NET WinForms applications using GrapeCity Spread.NET.

We will use the Spread Designer tool in run-time for editing Excel spreadsheet instances inside your running application with just one line of code. Finally, I will show how to create a simple front-end spreadsheet user interface that integrates the FpSpread spreadsheet control with the NameBox, and FormulaTextBox controls using splitter panes to create the main user interface and how to implement menu items to handle File — Open, File — Save, and File — Design commands in C# and VB.

Here are the steps for importing and exporting Excel spreadsheets in C# and VB.NET WinForms:

1. Create the Spreadsheet Interface Project
2. Configure the Spreadsheet Interface Project
3. Create the File Menu
4. Create the SplitContainer1
5. Configure the SplitContainer1
6. Create SplitContainer2
7. Create the Spreadsheet Control FpSpread
8. Create the NameBox
9. Create the FormulaTextBox
10. Create the WinForms Spreadsheet Designer Component
11. Create Event Handlers for File Menu Items
12. Add Event Handler Code

These steps require:

• Microsoft Visual Studio 2022 (note: while these steps are for VS2022 and .NET 6, Spread.NET WinForms can also work in earlier versions of Visual Studio targeting .NET 4.6.2+, following these same steps)
• Spread.NET for WinForms (trial version or licensed) Get your free 30-day trial of Spread.NET WinForms here.

Want to try the different features available with Spread.NET? Download Spread.NET Now!

Step 1: Create the Project

Create a new project in Visual Studio 2022 by selecting C#, Windows, and Desktop to filter the projects and then select either C# or VB WinForms App.

Step 2: Configure the Project

Type SpreadNetQuickStart for Project Name.

Step 3: Create the File Menu

Now let’s add a MenuStrip: in the Toolbox window (F4) under the category for Menus & Toolbars and double-click the MenuStripcomponent to create a new MenuStrip in the form:

Create the menu items for File – Open, File – Save, File – Save As, File – Design, and File – Exit using the associated shortcut keys:

Note that the File — Save menu item should initially be disabled. The menu separators (use «-« for the menu item text to create a menu separator) and shortcut keys are optional but recommended. We will add code for those menu items in a later step.

Step 4: Create SplitContainer1

Note: Adding splitters isn’t strictly necessary to use FpSpread, but the SplitContainer control makes creating a friendly and flexible spreadsheet interface much easier because it automatically handles resizing for the spreadsheet, namebox, and formula text box controls without requiring any code.

In the Toolbox (CTRL+ALT+X), expand the category for Containers, then double-click the SplitContainer control to create splitContainer1:

Step 5: Configure SplitContainer1

In the Properties window (F4), for splitContainer1, set Orientation to Horizontal, then set Palen1MinSize and SplitterDistance to 23:

The spreadsheet will be in the bottom pane, the formula bar interface will be in the top pane, and the splitter will determine the height of the FormulaTextBox control for showing long formulas that wrap to new lines.

Step 6: Create SplitContainer2

In the Toolbox (CTRL+ALT+X), drag-and-drop a new SplitContainer inside the top pane (Panel1) of splitContainer1 to create splitContainer2:

In the Properties window (F4), for splitContainer2 set Panel1MinSize and SplitterDistance to 150:

Step 7: Create the FpSpread

In Solution Explorer, expand the Solution SpreadNetQuickStart and Project SpreadNetQuickStart, then right-click Dependencies and select Manage NuGet Packages… (or press ALT+P+N+N+N+ENTER):

Then in NuGet Package Manager, select the Browse in the upper-left, then type Spread.WinForms in the search box to find the latest GrapeCity.Spread.WinForms, then click Install:

After installing GrapeCity.Spread.WinForms, go ahead and also install GrapeCity.Spread.WinForms.Design – this package contains the fpSpreadDesigner component for showing the Spread Designer tool in runtime.

Then in the Toolbox (CTRL+ALT+X), select the FpSpread control:

Finally, draw an instance of FpSpread into the bottom pane (Panel2) of SplitContainer1. The Spread Designer tool may open when you create the control (that is the default behavior) – for now, close the Spread Designer if it appears. 

Using the Property Grid (F4), set the Dock property to Fill:

Step 8: Create the NameBox

In the Toolbox (CTRL+ALT+X), expand the category for GrapeCity Spread for WinForms and select the NameBox control:

Draw a new NameBox inside the upper-left Panel1 (SplitContiner2.Panel1):

In the Properties window (F4), for nameBox1, set Dock to Fill:

In the upper-right corner of nameBox1, click the indicator to open the NameBox Tasks, then click the AttachTo drop-down and select fpSpread1 – this will generate code in the form code-behind to attach the NameBox control to the FpSpread control:

Step 9: Create the FormulaTextBox

In the Toolbox (CTRL+ALT+X), expand Containers and select the Panel control:

Inside Panel2 (to the right of NameBox1), draw a new Panel:

In the Properties window (F4), for panel1 set BorderStyle to FixedSingle and Dock to Fill:

In the Toolbox (CTRL+ALT+X) select the FormulaTextBox control:

Inside panel1 (which is inside splitContainer2.Panel2) draw a new FormulaTextBox control:

In the Properties window (F4) for formulaTextBox1, set BorderStyle to None and Dock to Fill:

In the upper-right corner of formulaTextBox1, click the indicator to open the FormulaTextBox Tasks, then click the AttachTo drop-down and select fpSpread1 – this will generate code in the form code-behind to attach the FormulaTextBox control to the FpSpreadcontrol:

Step 10: Create the WinForms Spreadsheet Designer Component

Now in the Toolbox window (CTRL+ALT+X) under the category GrapeCity Spread Design for WinForms, double-click the FpSpreadDesigner component to create a new FpSpreadDesigner in the form:

The fpSpreadDesigner1 component just added should show in the form component tray next to the components menuStrip1 andfpSpread1_Sheet1:

Step 11: Create Event Handlers for File Menu Items

For each menu item in the File menu, double-click that menu item in the design view until each menu item has an associated menu handler generated in the VB or C# code:

Step 12: Add Event Handler Code

Copy the following code to implement the event handlers:

[C#]

Add Event Handler Code C#

private string mFileName = null;
 
private void openToolStripMenuItem_Click(object sender, EventArgs e)
{
    OpenFileDialog ofd = new OpenFileDialog();
    ofd.Filter = "Excel Spreadsheet (*.XLSX;*.XLSM)|*.XLSX;*.XLSM";
    ofd.FilterIndex = 0;
    if (ofd.ShowDialog() == DialogResult.OK)
    {
        mFileName = ofd.FileName;
        fpSpread1.OpenExcel(mFileName);
        saveToolStripMenuItem.Enabled = true;
    }
}
 
private void saveToolStripMenuItem_Click(object sender, EventArgs e)
{
    fpSpread1.SaveExcel(mFileName);
}
 
private void saveAsToolStripMenuItem_Click(object sender, EventArgs e)
{
    SaveFileDialog sfd = new SaveFileDialog();
    sfd.Filter = "Excel Spreadsheet (*.XLSX;*.XLSM)|*.XLSX;*.XLSM";
    sfd.FilterIndex = 0;
    sfd.FileName = mFileName;
    if (sfd.ShowDialog() == DialogResult.OK)
    {
        mFileName = sfd.FileName;
        fpSpread1.SaveExcel(mFileName);
        saveToolStripMenuItem.Enabled = true;
    }
}
 
private void designToolStripMenuItem_Click(object sender, EventArgs e)
{
    fpSpreadDesigner1.ShowDialog(fpSpread1);
}
 
private void exitToolStripMenuItem_Click(object sender, EventArgs e)
{
    DialogResult ret = MessageBox.Show("Do you want to save this file before closing?", "Save Spreadsheet", MessageBoxButtons.YesNoCancel);
    if (ret == DialogResult.Cancel)
        return;
    else if (ret == DialogResult.Yes)
        saveToolStripMenuItem_Click(null, EventArgs.Empty);
    Close();
}

[VB]

Add Event Handler Code VB

Private mFileName As String = Nothing
 
Private Sub OpenToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles OpenToolStripMenuItem.Click
    Dim ofd As New OpenFileDialog
    ofd.Filter = "Excel Spreadsheet (*.XLSX;*.XLSM)|*.XLSX;*.XLSM"
    ofd.FilterIndex = 0
    If ofd.ShowDialog() = DialogResult.OK Then
        mFileName = ofd.FileName
        FpSpread1.OpenExcel(mFileName)
        SaveToolStripMenuItem.Enabled = True
    End If
End Sub
 
 
Private Sub SaveToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles SaveToolStripMenuItem.Click
    FpSpread1.SaveExcel(mFileName)
End Sub
 
Private Sub SaveAsToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles SaveAsToolStripMenuItem.Click
    Dim sfd As SaveFileDialog = New SaveFileDialog()
    sfd.Filter = "Excel Spreadsheet (*.XLSX;*.XLSM)|*.XLSX;*.XLSM"
    sfd.FilterIndex = 0
    sfd.FileName = mFileName
    If sfd.ShowDialog() = DialogResult.OK Then
        mFileName = sfd.FileName
        FpSpread1.SaveExcel(mFileName)
        SaveToolStripMenuItem.Enabled = True
    End If
End Sub
 
Private Sub DesignToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles DesignToolStripMenuItem.Click
    FpSpreadDesigner1.ShowDialog(FpSpread1)
End Sub
 
Private Sub ExitToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ExitToolStripMenuItem.Click
    Dim ret As DialogResult = MessageBox.Show("Do you want to save this file before closing?", "Closing", MessageBoxButtons.YesNoCancel)
    If ret = DialogResult.Cancel Then
        Return
    ElseIf ret = DialogResult.Yes Then
        SaveToolStripMenuItem_Click(Nothing, EventArgs.Empty)
    End If
    Close()
End Sub

The code for File — Open uses the OpenFileDialog to browse for a spreadsheet file, then uses the FpSpread.OpenExcel method to open the selected file. The code for File — Save uses FpSpread.SaveExcel to save the spreadsheet and the code for File — Save As uses the SaveFileDialog to allow the user to save the file to another location or use another name.

The code in File-Design uses the FpSpreadDesigner.ShowDialog method to show the Spread Designer tool in run-time, make changes, and then apply those changes back to the spreadsheet instance in the form. Finally, the code in File — Exit prompts the user whether to save the file and then uses the Close method to close the form.

Ready to Build and Run!

The project is ready to build and run. The File-Design menu will open the Spread Designer tool in run-time, as shown above, which can apply changes to the spreadsheet instance running in the form.

In another article series, we demonstrate How to Import and Export Excel Spreadsheets with C# and WPF, How to Import and Export Excel Spreadsheets using JavaScript, and How to Import and Export Excel Spreadsheets in Angular.

Download the C# Sample | Download the VB Sample

Want to try the different features available with Spread.NET? Download Spread.NET Now!