Copier des feuilles de calcul C# dans Excel
Table des matières
Installé via NuGet
PM> Install-Package Spire.XLS
Liens connexes
La fonction de copie Excel vous permet non seulement de copier des feuilles de calcul dans un classeur Excel, mais également de copier des feuilles de calcul entre différents classeurs Excel. Cet article présentera des solutions pour copier des feuilles de calcul dans un classeur Excel et entre différents classeurs via Spire.XLS for .NET en C#, VB.NET. En outre, tous les formats de cellules des feuilles de calcul Excel d'origine seront entièrement conservés.
- Copier des feuilles de calcul Excel dans un classeur Excel
- Copier des feuilles de calcul Excel entre des classeurs Excel
Installez Spire.XLS for .NET
Pour commencer, vous devez ajouter les fichiers DLL inclus dans le package Spire.XLS for .NET comme références dans votre projet .NET. Les fichiers DLL peuvent être téléchargés à partir de ce lien ou installés via NuGet.
- Package Manager
PM> Install-Package Spire.XLS
Copier des feuilles de calcul Excel dans un classeur Excel
Voici les étapes pour dupliquer des feuilles de calcul dans un classeur Excel.
- Initialisez une instance de la classe Workbook.
- Chargez un fichier Excel à l'aide de la méthode Workbook.LoadFromFile().
- Ajoutez une nouvelle feuille vierge au classeur à l’aide de la méthode WorksheetCollection.Add().
- Copiez la feuille de calcul d'origine sur la nouvelle feuille à l'aide de la méthode Worksheet.CopyFrom().
- Utilisez la méthode Workbook.SaveToFile() pour enregistrer les modifications dans un autre fichier.
- C#
- VB.NET
using Spire.Xls;
namespace CopyExcelworksheet
{
class Program
{
static void Main(string[] args)
{
//Load the sample Excel
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
//Add worksheet and set its name
workbook.Worksheets.Add("Sheet1_Copy");
//copy worksheet to the new added worksheets
workbook.Worksheets[1].CopyFrom(workbook.Worksheets[0]);
//Save the Excel workbook.
workbook.SaveToFile("Duplicatesheet.xlsx", ExcelVersion.Version2013);
System.Diagnostics.Process.Start("Duplicatesheet.xlsx");
}
}
}
Imports Spire.Xls
Namespace CopyExcelworksheet
Class Program
Private Shared Sub Main(ByVal args() As String)
'Load the sample Excel
Dim workbook As Workbook = New Workbook
workbook.LoadFromFile("Sample.xlsx")
'Add worksheet and set its name
workbook.Worksheets.Add("Sheet1_Copy")
'copy worksheet to the new added worksheets
workbook.Worksheets(1).CopyFrom(workbook.Worksheets(0))
'Save the Excel workbook.
workbook.SaveToFile("Duplicatesheet.xlsx", ExcelVersion.Version2013)
System.Diagnostics.Process.Start("Duplicatesheet.xlsx")
End Sub
End Class
End Namespace
Copier des feuilles de calcul Excel entre des classeurs Excel
Voici les étapes pour dupliquer des feuilles de calcul dans un classeur Excel.
- Initialisez une instance de la classe Workbook.
- Chargez un fichier Excel à l'aide de la méthode Workbook.LoadFromFile().
- Obtenez la première feuille de travail.
- Charger un autre exemple de document Excel
- Ajoutez une nouvelle feuille vierge au deuxième classeur à l’aide de la méthode WorksheetCollection.Add().
- Copiez la feuille de calcul d'origine sur la nouvelle feuille à l'aide de la méthode Worksheet.CopyFrom().
- Utilisez la méthode Workbook.SaveToFile() pour enregistrer les modifications dans un autre fichier.
- C#
- VB.NET
using Spire.Xls;
namespace CopyExcelworksheet
{
class Program
{
static void Main(string[] args)
{
//Load the sample Excel and get the first worksheet
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet sheet = workbook.Worksheets[0];
//Load the second Excel workbook
Workbook workbook2 = new Workbook();
workbook2.LoadFromFile("New.xlsx");
//Add a new worksheet and set its name
Worksheet targetWorksheet = workbook2.Worksheets.Add("added");
//Copy the original worksheet to the new added worksheets
targetWorksheet.CopyFrom(sheet);
//Save the Excel workbook.
workbook2.SaveToFile("CopySheetBetweenWorkbooks.xlsx", FileFormat.Version2013);
System.Diagnostics.Process.Start("CopySheetBetweenWorkbooks.xlsx");
}
}
}
Imports Spire.Xls
Namespace CopyExcelworksheet
Class Program
Private Shared Sub Main(ByVal args() As String)
'Load the sample Excel and get the first worksheet
Dim workbook As Workbook = New Workbook
workbook.LoadFromFile("Sample.xlsx")
Dim sheet As Worksheet = workbook.Worksheets(0)
'Load the second Excel workbook
Dim workbook2 As Workbook = New Workbook
workbook2.LoadFromFile("New.xlsx")
'Add a new worksheet and set its name
Dim targetWorksheet As Worksheet = workbook2.Worksheets.Add("added")
'Copy the original worksheet to the new added worksheets
targetWorksheet.CopyFrom(sheet)
'Save the Excel workbook.
workbook2.SaveToFile("CopySheetBetweenWorkbooks.xlsx", FileFormat.Version2013)
System.Diagnostics.Process.Start("CopySheetBetweenWorkbooks.xlsx")
End Sub
End Class
End Namespace
Demander une licence temporaire
Si vous souhaitez supprimer le message d'évaluation des documents générés ou vous débarrasser des limitations fonctionnelles, veuillez demander une licence d'essai de 30 jours pour toi.
C# Create, Read, or Update Excel Documents
Table of Contents
Installed via NuGet
PM> Install-Package Spire.XLS
Related Links
Excel spreadsheet is a widely used file format that enables users to organize, analyze, and present data in a tabular format. The ability to interact with Excel files programmatically is highly valuable, as it allows automation and integration of Excel functionality into software applications. This capability is particularly useful when working with large datasets, performing complex calculations, or when data needs to be dynamically generated or updated. In this article, you will learn how to create, read, or update Excel documents in C# and VB.NET using Spire.XLS for .NET.
- Create an Excel File in C#, VB.NET
- Read Data of a Worksheet in C#, VB.NET
- Update an Excel File in C#, VB.NET
Install Spire.XLS for .NET
To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Create an Excel File in C#, VB.NET
Spire.XLS for .NET offers a variety of classes and interfaces that you can use to create and edit Excel documents. Here is a list of important classes, properties and methods involved in this article.
| Member | Description |
| Workbook class | Represents an Excel workbook model. |
| Workbook.Worksheets.Add() method | Adds a worksheet to workbook. |
| Workbook.SaveToFile() method | Saves the workbook to an Excel document. |
| Worksheet class | Represents a worksheet in a workbook. |
| Worksheet.Range property | Gets a specific cell or cell range from worksheet. |
| Worksheet.Range.Value property | Gets or sets the value of a cell. |
| Worksheet.Rows property | Gets a collection of rows in worksheet. |
| Worksheet.InsertDataTable() method | Imports data from DataTable to worksheet. |
| CellRange class | Represents a cell or cell range in worksheet. |
The following are the steps to create an Excel document from scratch using Spire.XLS for .NET.
- Create a Workbook object.
- Add a worksheet using Workbook.Worksheets.Add() method.
- Write data to a specific cell through Worksheet.Range.Value property.
- Import data from a DataTable to the worksheet using Worksheet.InsertDataTable() method.
- Save the workbook to an Excel document using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
using System.Data;
namespace CreateExcelSpreadsheet
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook wb = new Workbook();
//Remove default worksheets
wb.Worksheets.Clear();
//Add a worksheet and name it "Employee"
Worksheet sheet = wb.Worksheets.Add("Employee");
//Merge the cells between A1 and G1
sheet.Range["A1:G1"].Merge();
//Write data to A1 and apply formatting to it
sheet.Range["A1"].Value = "Basic Information of Employees of Huanyu Automobile Company";
sheet.Range["A1"].HorizontalAlignment = HorizontalAlignType.Center;
sheet.Range["A1"].VerticalAlignment = VerticalAlignType.Center;
sheet.Range["A1"].Style.Font.IsBold = true;
sheet.Range["A1"].Style.Font.Size = 13F;
//Set row height of the first row
sheet.Rows[0].RowHeight = 30F;
//Create a DataTable
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Gender");
dt.Columns.Add("Birth Date");
dt.Columns.Add("Educational Background");
dt.Columns.Add("Contact Number");
dt.Columns.Add("Position");
dt.Columns.Add("ID");
dt.Rows.Add("Allen", "Male", "1990-02-10", "Bachelor", "24756854", "Mechanic", "0021");
dt.Rows.Add("Patrick", "Male", "1985-06-08", "Master", "59863247", "Mechanic", "0022");
dt.Rows.Add("Jenna", "Female", "1989-11-25", "Bachelor", "79540352", "Sales", "0023");
dt.Rows.Add("Tommy", "Male", "1988-04-16", "Master", "52014060", "Mechanic", "0024");
dt.Rows.Add("Christina", "Female", "1998-01-21", "Bachelor", "35401489", "HR", "0025");
//Import data from DataTable to worksheet
sheet.InsertDataTable(dt, true, 2, 1, true);
//Set row height of a range
sheet.Range["A2:G7"].RowHeight = 15F;
//Set column width
sheet.Range["A2:G7"].Columns[2].ColumnWidth = 15F;
sheet.Range["A2:G7"].Columns[3].ColumnWidth = 21F;
sheet.Range["A2:G7"].Columns[4].ColumnWidth = 15F;
//Set border style of a range
sheet.Range["A2:G7"].BorderAround(LineStyleType.Medium);
sheet.Range["A2:G7"].BorderInside(LineStyleType.Thin);
sheet.Range["A2:G2"].BorderAround(LineStyleType.Medium);
sheet.Range["A2:G7"].Borders.KnownColor = ExcelColors.Black;
//Save to a .xlsx file
wb.SaveToFile("NewSpreadsheet.xlsx", FileFormat.Version2016);
}
}
}
Read Data of a Worksheet in C#, VB.NET
The Worksheet.Range.Value property returns number value or text value of a cell as a string. To get data of a whole worksheet or a cell range, loop through the cells within it. The following are the steps to get data of a worksheet using Spire.XLS for .NET.
- Create a Workbook object.
- Load an Excel document using Workbook.LoadFromFile() method.
- Get a specific worksheet through Workbook.Worksheets[index] property.
- Get the cell range containing data though Worksheet.AllocatedRange property.
- Iterate through the rows and columns to get cells within the range, and return the value of each cell through CellRange.Value property.
- C#
- VB.NET
using Spire.Xls;
namespace ReadExcelData
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook wb = new Workbook();
//Load an existing Excel file
wb.LoadFromFile(@"C:\Users\Administrator\Desktop\NewSpreadsheet.xlsx");
//Get the first worksheet
Worksheet sheet = wb.Worksheets[0];
//Get the cell range containing data
CellRange locatedRange = sheet.AllocatedRange;
//Iterate through the rows
for (int i = 0;i < locatedRange.Rows.Length;i++)
{
//Iterate through the columns
for (int j = 0; j < locatedRange.Rows[i].ColumnCount; j++)
{
//Get data of a specific cell
Console.Write(locatedRange[i + 1, j + 1].Value + " ");
}
Console.WriteLine();
}
}
}
}
Update an Excel Document in C#, VB.NET
To change the value of a certain cell, just re-assign a value to it through Worksheet.Range.Value property. The following are the detailed steps.
- Create a Workbook object.
- Load an Excel document using Workbook.LoadFromFile() method.
- Get a specific worksheet through Workbook.Worksheets[index] property.
- Change the value of a particular cell though Worksheet.Range.Value property.
- Save the workbook to an Excel file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace UpdateCellValue
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook wb = new Workbook();
//Load an existing Excel file
wb.LoadFromFile(@"C:\Users\Administrator\Desktop\NewSpreadsheet.xlsx");
//Get the first worksheet
Worksheet sheet = wb.Worksheets[0];
//Change the value of a specific cell
sheet.Range["A1"].Value = "Updated Value";
//Save to file
wb.SaveToFile("Updated.xlsx", ExcelVersion.Version2016);
}
}
}
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
C# Criar, ler ou atualizar documentos Excel
Índice
Instalado via NuGet
PM> Install-Package Spire.XLS
Links Relacionados
A planilha do Excel é um formato de arquivo amplamente utilizado que permite aos usuários organizar, analisar e apresentar dados em formato tabular. A capacidade de interagir programaticamente com arquivos do Excel é altamente valiosa, pois permite a automação e integração da funcionalidade do Excel em aplicativos de software. Esse recurso é particularmente útil ao trabalhar com grandes conjuntos de dados, realizar cálculos complexos ou quando os dados precisam ser gerados ou atualizados dinamicamente. Neste artigo você aprenderá como crie, leia ou atualize documentos Excel em C# e VB.NET usando Spire.XLS for .NET.
- Crie um arquivo Excel em C#, VB.NET
- Leia dados de uma planilha em C#, VB.NET
- Atualizar um arquivo Excel em C#, VB.NET
Instale o Spire.XLS for .NET
Para começar, você precisa adicionar os arquivos DLL incluídos no pacote Spire.XLS for .NET como referências em seu projeto .NET. Os arquivos DLL podem ser baixados deste link ou instalados via NuGet.
PM> Install-Package Spire.XLS
Crie um arquivo Excel em C#, VB.NET
Spire.XLS for .NET oferece uma variedade de classes e interfaces que você pode usar para criar e editar documentos Excel. Aqui está uma lista de classes, propriedades e métodos importantes envolvidos neste artigo.
| Membro | Descrição |
| Aula de apostila | Representa um modelo de pasta de trabalho do Excel. |
| Método Workbook.Worksheets.Add() | Adiciona uma planilha à pasta de trabalho. |
| Método Workbook.SaveToFile() | Salva a pasta de trabalho em um documento Excel. |
| Aula de planilha | Representa uma planilha em uma pasta de trabalho. |
| Propriedade Worksheet.Range | Obtém uma célula ou intervalo de células específico da planilha. |
| Propriedade Worksheet.Range.Value | Obtém ou define o valor de uma célula. |
| Propriedade Worksheet.Rows | Obtém uma coleção de linhas na planilha. |
| Método Worksheet.InsertDataTable() | Importa dados do DataTable para a planilha. |
| Classe CellRange | Representa uma célula ou intervalo de células na planilha. |
A seguir estão as etapas para criar um documento Excel do zero usando Spire.XLS for .NET.
- Crie um objeto Pasta de trabalho.
- Adicione uma planilha usando o método Workbook.Worksheets.Add().
- Grave dados em uma célula específica por meio da propriedade Worksheet.Range.Value.
- Importe dados de um DataTable para a planilha usando o método Worksheet.InsertDataTable().
- Salve a pasta de trabalho em um documento Excel usando o método Workbook.SaveToFile().
- C#
- VB.NET
using Spire.Xls;
using System.Data;
namespace CreateExcelSpreadsheet
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook wb = new Workbook();
//Remove default worksheets
wb.Worksheets.Clear();
//Add a worksheet and name it "Employee"
Worksheet sheet = wb.Worksheets.Add("Employee");
//Merge the cells between A1 and G1
sheet.Range["A1:G1"].Merge();
//Write data to A1 and apply formatting to it
sheet.Range["A1"].Value = "Basic Information of Employees of Huanyu Automobile Company";
sheet.Range["A1"].HorizontalAlignment = HorizontalAlignType.Center;
sheet.Range["A1"].VerticalAlignment = VerticalAlignType.Center;
sheet.Range["A1"].Style.Font.IsBold = true;
sheet.Range["A1"].Style.Font.Size = 13F;
//Set row height of the first row
sheet.Rows[0].RowHeight = 30F;
//Create a DataTable
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Gender");
dt.Columns.Add("Birth Date");
dt.Columns.Add("Educational Background");
dt.Columns.Add("Contact Number");
dt.Columns.Add("Position");
dt.Columns.Add("ID");
dt.Rows.Add("Allen", "Male", "1990-02-10", "Bachelor", "24756854", "Mechanic", "0021");
dt.Rows.Add("Patrick", "Male", "1985-06-08", "Master", "59863247", "Mechanic", "0022");
dt.Rows.Add("Jenna", "Female", "1989-11-25", "Bachelor", "79540352", "Sales", "0023");
dt.Rows.Add("Tommy", "Male", "1988-04-16", "Master", "52014060", "Mechanic", "0024");
dt.Rows.Add("Christina", "Female", "1998-01-21", "Bachelor", "35401489", "HR", "0025");
//Import data from DataTable to worksheet
sheet.InsertDataTable(dt, true, 2, 1, true);
//Set row height of a range
sheet.Range["A2:G7"].RowHeight = 15F;
//Set column width
sheet.Range["A2:G7"].Columns[2].ColumnWidth = 15F;
sheet.Range["A2:G7"].Columns[3].ColumnWidth = 21F;
sheet.Range["A2:G7"].Columns[4].ColumnWidth = 15F;
//Set border style of a range
sheet.Range["A2:G7"].BorderAround(LineStyleType.Medium);
sheet.Range["A2:G7"].BorderInside(LineStyleType.Thin);
sheet.Range["A2:G2"].BorderAround(LineStyleType.Medium);
sheet.Range["A2:G7"].Borders.KnownColor = ExcelColors.Black;
//Save to a .xlsx file
wb.SaveToFile("NewSpreadsheet.xlsx", FileFormat.Version2016);
}
}
}
Leia dados de uma planilha em C#, VB.NET
A propriedade Worksheet.Range.Value retorna o valor numérico ou o valor do texto de uma célula como uma string. Para obter dados de uma planilha inteira ou de um intervalo de células, percorra as células dentro dela. A seguir estão as etapas para obter dados de uma planilha usando Spire.XLS for .NET.
- Crie um objeto Pasta de trabalho.
- Carregue um documento Excel usando o método Workbook.LoadFromFile().
- Obtenha uma planilha específica por meio da propriedade Workbook.Worksheets[index].
- Obtenha o intervalo de células que contém dados por meio da propriedade Worksheet.AllocatedRange.
- Itere pelas linhas e colunas para obter células dentro do intervalo e retorne o valor de cada célula por meio da propriedade CellRange.Value.
- C#
- VB.NET
using Spire.Xls;
namespace ReadExcelData
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook wb = new Workbook();
//Load an existing Excel file
wb.LoadFromFile(@"C:\Users\Administrator\Desktop\NewSpreadsheet.xlsx");
//Get the first worksheet
Worksheet sheet = wb.Worksheets[0];
//Get the cell range containing data
CellRange locatedRange = sheet.AllocatedRange;
//Iterate through the rows
for (int i = 0;i < locatedRange.Rows.Length;i++)
{
//Iterate through the columns
for (int j = 0; j < locatedRange.Rows[i].ColumnCount; j++)
{
//Get data of a specific cell
Console.Write(locatedRange[i + 1, j + 1].Value + " ");
}
Console.WriteLine();
}
}
}
}
Atualizar um documento Excel em C#, VB.NET
Para alterar o valor de uma determinada célula, basta reatribuir um valor a ela através da propriedade Worksheet.Range.Value. A seguir estão as etapas detalhadas.
- Crie um objeto Pasta de trabalho.
- Carregue um documento Excel usando o método Workbook.LoadFromFile().
- Obtenha uma planilha específica por meio da propriedade Workbook.Worksheets[index].
- Altere o valor de uma célula específica por meio da propriedade Worksheet.Range.Value.
- Salve a pasta de trabalho em um arquivo Excel usando o método Workbook.SaveToFile().
- C#
- VB.NET
using Spire.Xls;
namespace UpdateCellValue
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook wb = new Workbook();
//Load an existing Excel file
wb.LoadFromFile(@"C:\Users\Administrator\Desktop\NewSpreadsheet.xlsx");
//Get the first worksheet
Worksheet sheet = wb.Worksheets[0];
//Change the value of a specific cell
sheet.Range["A1"].Value = "Updated Value";
//Save to file
wb.SaveToFile("Updated.xlsx", ExcelVersion.Version2016);
}
}
}
Solicite uma licença temporária
Se desejar remover a mensagem de avaliação dos documentos gerados ou se livrar das limitações de função, por favor solicite uma licença de teste de 30 dias para você mesmo.
C# Создание, чтение или обновление документов Excel
Оглавление
Установить с помощью Пипа
PM> Install-Package Spire.XLS
Ссылки по теме
Электронная таблица Excel — это широко используемый формат файлов, который позволяет пользователям организовывать, анализировать и представлять данные в табличном формате. Возможность программного взаимодействия с файлами Excel очень ценна, поскольку позволяет автоматизировать и интегрировать функции Excel в программные приложения. Эта возможность особенно полезна при работе с большими наборами данных, выполнении сложных вычислений или когда данные необходимо динамически генерировать или обновлять. В этой статье вы узнаете, как создавайте, читайте или обновляйте документы Excel на C# и VB.NET с помощью Spire.XLS for .NET.
- Создайте файл Excel на C#, VB.NET.
- Чтение данных рабочего листа в C#, VB.NET
- Обновление файла Excel в C#, VB.NET
Установите Spire.XLS for .NET
Для начала вам необходимо добавить файлы DLL, включенные в пакет Spire.XLS for .NET, в качестве ссылок в ваш проект .NET. Файлы DLL можно загрузить по этой ссылке или установить через NuGet.
PM> Install-Package Spire.XLS
Создайте файл Excel на C#, VB.NET.
Spire.XLS for .NET предлагает множество классов и интерфейсов, которые можно использовать для создания и редактирования документов Excel. Вот список важных классов, свойств и методов, рассматриваемых в этой статье.
| Член | Описание |
| Класс рабочей книги | Представляет модель книги Excel. |
| Метод Workbook.Worksheets.Add() | Добавляет лист в книгу. |
| Метод Workbook.SaveToFile() | Сохраняет книгу в документ Excel. |
| Класс рабочего листа | Представляет лист в книге. |
| Свойство Worksheet.Range | Получает определенную ячейку или диапазон ячеек из листа. |
| Свойство Worksheet.Range.Value | Получает или задает значение ячейки. |
| Свойство Worksheet.Rows | Получает коллекцию строк на листе. |
| Метод Worksheet.InsertDataTable() | Импортирует данные из DataTable на лист. |
| Класс CellRange | Представляет ячейку или диапазон ячеек на листе. |
Ниже приведены шаги по созданию документа Excel с нуля с помощью Spire.XLS for .NET.
- Создайте объект Workbook.
- Добавьте рабочий лист с помощью метода Workbook.Worksheets.Add().
- Запишите данные в определенную ячейку через свойство Worksheet.Range.Value.
- Импортируйте данные из DataTable на лист с помощью метода Worksheet.InsertDataTable().
- Сохраните книгу в документ Excel, используя метод Workbook.SaveToFile().
- C#
- VB.NET
using Spire.Xls;
using System.Data;
namespace CreateExcelSpreadsheet
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook wb = new Workbook();
//Remove default worksheets
wb.Worksheets.Clear();
//Add a worksheet and name it "Employee"
Worksheet sheet = wb.Worksheets.Add("Employee");
//Merge the cells between A1 and G1
sheet.Range["A1:G1"].Merge();
//Write data to A1 and apply formatting to it
sheet.Range["A1"].Value = "Basic Information of Employees of Huanyu Automobile Company";
sheet.Range["A1"].HorizontalAlignment = HorizontalAlignType.Center;
sheet.Range["A1"].VerticalAlignment = VerticalAlignType.Center;
sheet.Range["A1"].Style.Font.IsBold = true;
sheet.Range["A1"].Style.Font.Size = 13F;
//Set row height of the first row
sheet.Rows[0].RowHeight = 30F;
//Create a DataTable
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Gender");
dt.Columns.Add("Birth Date");
dt.Columns.Add("Educational Background");
dt.Columns.Add("Contact Number");
dt.Columns.Add("Position");
dt.Columns.Add("ID");
dt.Rows.Add("Allen", "Male", "1990-02-10", "Bachelor", "24756854", "Mechanic", "0021");
dt.Rows.Add("Patrick", "Male", "1985-06-08", "Master", "59863247", "Mechanic", "0022");
dt.Rows.Add("Jenna", "Female", "1989-11-25", "Bachelor", "79540352", "Sales", "0023");
dt.Rows.Add("Tommy", "Male", "1988-04-16", "Master", "52014060", "Mechanic", "0024");
dt.Rows.Add("Christina", "Female", "1998-01-21", "Bachelor", "35401489", "HR", "0025");
//Import data from DataTable to worksheet
sheet.InsertDataTable(dt, true, 2, 1, true);
//Set row height of a range
sheet.Range["A2:G7"].RowHeight = 15F;
//Set column width
sheet.Range["A2:G7"].Columns[2].ColumnWidth = 15F;
sheet.Range["A2:G7"].Columns[3].ColumnWidth = 21F;
sheet.Range["A2:G7"].Columns[4].ColumnWidth = 15F;
//Set border style of a range
sheet.Range["A2:G7"].BorderAround(LineStyleType.Medium);
sheet.Range["A2:G7"].BorderInside(LineStyleType.Thin);
sheet.Range["A2:G2"].BorderAround(LineStyleType.Medium);
sheet.Range["A2:G7"].Borders.KnownColor = ExcelColors.Black;
//Save to a .xlsx file
wb.SaveToFile("NewSpreadsheet.xlsx", FileFormat.Version2016);
}
}
}
Чтение данных рабочего листа в C#, VB.NET
Свойство Worksheet.Range.Value возвращает числовое значение или текстовое значение ячейки в виде строки. Чтобы получить данные всего листа или диапазона ячеек, пройдитесь по ячейкам внутри него. Ниже приведены шаги по получению данных листа с помощью Spire.XLS for .NET.
- Создайте объект Workbook.
- Загрузите документ Excel с помощью метода Workbook.LoadFromFile().
- Получите конкретный лист через свойство Workbook.Worksheets[index].
- Получите диапазон ячеек, содержащий данные, с помощью свойства Worksheet.AllocatedRange.
- Перебирайте строки и столбцы, чтобы получить ячейки в диапазоне, и возвращайте значение каждой ячейки через свойство CellRange.Value.
- C#
- VB.NET
using Spire.Xls;
namespace ReadExcelData
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook wb = new Workbook();
//Load an existing Excel file
wb.LoadFromFile(@"C:\Users\Administrator\Desktop\NewSpreadsheet.xlsx");
//Get the first worksheet
Worksheet sheet = wb.Worksheets[0];
//Get the cell range containing data
CellRange locatedRange = sheet.AllocatedRange;
//Iterate through the rows
for (int i = 0;i < locatedRange.Rows.Length;i++)
{
//Iterate through the columns
for (int j = 0; j < locatedRange.Rows[i].ColumnCount; j++)
{
//Get data of a specific cell
Console.Write(locatedRange[i + 1, j + 1].Value + " ");
}
Console.WriteLine();
}
}
}
}
Обновление документа Excel на C#, VB.NET
Чтобы изменить значение определенной ячейки, просто переназначьте ей значение через свойство Worksheet.Range.Value. Ниже приведены подробные шаги.
- Создайте объект Workbook.
- Загрузите документ Excel с помощью метода Workbook.LoadFromFile().
- Получите конкретный лист через свойство Workbook.Worksheets[index].
- Измените значение определенной ячейки с помощью свойства Worksheet.Range.Value.
- Сохраните книгу в файл Excel, используя метод Workbook.SaveToFile().
- C#
- VB.NET
using Spire.Xls;
namespace UpdateCellValue
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook wb = new Workbook();
//Load an existing Excel file
wb.LoadFromFile(@"C:\Users\Administrator\Desktop\NewSpreadsheet.xlsx");
//Get the first worksheet
Worksheet sheet = wb.Worksheets[0];
//Change the value of a specific cell
sheet.Range["A1"].Value = "Updated Value";
//Save to file
wb.SaveToFile("Updated.xlsx", ExcelVersion.Version2016);
}
}
}
Подать заявку на временную лицензию
Если вы хотите удалить сообщение об оценке из сгенерированных документов или избавиться от ограничений функции, пожалуйста запросите 30-дневную пробную лицензию для себя.
C# Excel-Dokumente erstellen, lesen oder aktualisieren
Inhaltsverzeichnis
Über NuGet installiert
PM> Install-Package Spire.XLS<
verwandte Links
Excel-Tabellen sind ein weit verbreitetes Dateiformat, mit dem Benutzer Daten in einem Tabellenformat organisieren, analysieren und präsentieren können. Die Möglichkeit, programmgesteuert mit Excel-Dateien zu interagieren, ist äußerst wertvoll, da sie die Automatisierung und Integration von Excel-Funktionen in Softwareanwendungen ermöglicht. Diese Funktion ist besonders nützlich, wenn Sie mit großen Datensätzen arbeiten, komplexe Berechnungen durchführen oder wenn Daten dynamisch generiert oder aktualisiert werden müssen. In diesem Artikel erfahren Sie, wie das geht Erstellen, lesen oder aktualisieren Sie Excel-Dokumente in C# und VB.NET mit Spire.XLS for .NET.
- Erstellen Sie eine Excel-Datei in C#, VB.NET
- Lesen Sie Daten eines Arbeitsblatts in C#, VB.NET
- Aktualisieren Sie eine Excel-Datei in C#, VB.NET
Installieren Sie Spire.XLS for .NET
Zunächst müssen Sie die im Spire.XLS for .NET-Paket enthaltenen DLL-Dateien als Referenzen in Ihrem .NET-Projekt hinzufügen. Die DLL-Dateien können entweder über diesen Link heruntergeladen oder über NuGet installiert werden.
PM> Install-Package Spire.XLS
Erstellen Sie eine Excel-Datei in C#, VB.NET
Spire.XLS for .NET bietet eine Vielzahl von Klassen und Schnittstellen, die Sie zum Erstellen und Bearbeiten von Excel-Dokumenten verwenden können. Hier ist eine Liste wichtiger Klassen, Eigenschaften und Methoden, die in diesem Artikel beteiligt sind.
| Mitglied | Beschreibung |
| Arbeitsbuchklasse | Stellt ein Excel-Arbeitsmappenmodell dar. |
| Workbook.Worksheets.Add()-Methode | Fügt der Arbeitsmappe ein Arbeitsblatt hinzu. |
| Workbook.SaveToFile()-Methode | Speichert die Arbeitsmappe in einem Excel-Dokument. |
| Arbeitsblattklasse | Stellt ein Arbeitsblatt in einer Arbeitsmappe dar. |
| Worksheet.Range-Eigenschaft | Ruft eine bestimmte Zelle oder einen bestimmten Zellbereich aus dem Arbeitsblatt ab. |
| Worksheet.Range.Value-Eigenschaft | Ruft den Wert einer Zelle ab oder legt diesen fest. |
| Worksheet.Rows-Eigenschaft | Ruft eine Sammlung von Zeilen im Arbeitsblatt ab. |
| Worksheet.InsertDataTable()-Methode | Importiert Daten aus der Datentabelle in das Arbeitsblatt. |
| CellRange-Klasse | Stellt eine Zelle oder einen Zellbereich im Arbeitsblatt dar. |
Im Folgenden finden Sie die Schritte zum Erstellen eines Excel-Dokuments von Grund auf mit Spire.XLS for .NET.
- Erstellen Sie ein Workbook-Objekt.
- Fügen Sie ein Arbeitsblatt mit der Methode Workbook.Worksheets.Add() hinzu.
- Schreiben Sie Daten über die Worksheet.Range.Value-Eigenschaft in eine bestimmte Zelle.
- Importieren Sie Daten aus einer DataTable in das Arbeitsblatt mit der Methode Worksheet.InsertDataTable().
- Speichern Sie die Arbeitsmappe mit der Methode Workbook.SaveToFile() in einem Excel-Dokument.
- C#
- VB.NET
using Spire.Xls;
using System.Data;
namespace CreateExcelSpreadsheet
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook wb = new Workbook();
//Remove default worksheets
wb.Worksheets.Clear();
//Add a worksheet and name it "Employee"
Worksheet sheet = wb.Worksheets.Add("Employee");
//Merge the cells between A1 and G1
sheet.Range["A1:G1"].Merge();
//Write data to A1 and apply formatting to it
sheet.Range["A1"].Value = "Basic Information of Employees of Huanyu Automobile Company";
sheet.Range["A1"].HorizontalAlignment = HorizontalAlignType.Center;
sheet.Range["A1"].VerticalAlignment = VerticalAlignType.Center;
sheet.Range["A1"].Style.Font.IsBold = true;
sheet.Range["A1"].Style.Font.Size = 13F;
//Set row height of the first row
sheet.Rows[0].RowHeight = 30F;
//Create a DataTable
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Gender");
dt.Columns.Add("Birth Date");
dt.Columns.Add("Educational Background");
dt.Columns.Add("Contact Number");
dt.Columns.Add("Position");
dt.Columns.Add("ID");
dt.Rows.Add("Allen", "Male", "1990-02-10", "Bachelor", "24756854", "Mechanic", "0021");
dt.Rows.Add("Patrick", "Male", "1985-06-08", "Master", "59863247", "Mechanic", "0022");
dt.Rows.Add("Jenna", "Female", "1989-11-25", "Bachelor", "79540352", "Sales", "0023");
dt.Rows.Add("Tommy", "Male", "1988-04-16", "Master", "52014060", "Mechanic", "0024");
dt.Rows.Add("Christina", "Female", "1998-01-21", "Bachelor", "35401489", "HR", "0025");
//Import data from DataTable to worksheet
sheet.InsertDataTable(dt, true, 2, 1, true);
//Set row height of a range
sheet.Range["A2:G7"].RowHeight = 15F;
//Set column width
sheet.Range["A2:G7"].Columns[2].ColumnWidth = 15F;
sheet.Range["A2:G7"].Columns[3].ColumnWidth = 21F;
sheet.Range["A2:G7"].Columns[4].ColumnWidth = 15F;
//Set border style of a range
sheet.Range["A2:G7"].BorderAround(LineStyleType.Medium);
sheet.Range["A2:G7"].BorderInside(LineStyleType.Thin);
sheet.Range["A2:G2"].BorderAround(LineStyleType.Medium);
sheet.Range["A2:G7"].Borders.KnownColor = ExcelColors.Black;
//Save to a .xlsx file
wb.SaveToFile("NewSpreadsheet.xlsx", FileFormat.Version2016);
}
}
}
Lesen Sie Daten eines Arbeitsblatts in C#, VB.NET
Die Worksheet.Range.Value-Eigenschaft gibt den Zahlenwert oder Textwert einer Zelle als Zeichenfolge zurück. Um Daten eines gesamten Arbeitsblatts oder eines Zellbereichs abzurufen, durchlaufen Sie die darin enthaltenen Zellen. Im Folgenden finden Sie die Schritte zum Abrufen von Daten eines Arbeitsblatts mit Spire.XLS for .NET.
- Erstellen Sie ein Workbook-Objekt.
- Laden Sie ein Excel-Dokument mit der Methode Workbook.LoadFromFile().
- Rufen Sie ein bestimmtes Arbeitsblatt über die Eigenschaft Workbook.Worksheets[index] ab.
- Rufen Sie den Zellbereich mit Daten über die Worksheet.AllocatedRange-Eigenschaft ab.
- Durchlaufen Sie die Zeilen und Spalten, um Zellen innerhalb des Bereichs zu erhalten, und geben Sie den Wert jeder Zelle über die CellRange.Value-Eigenschaft zurück.
- C#
- VB.NET
using Spire.Xls;
namespace ReadExcelData
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook wb = new Workbook();
//Load an existing Excel file
wb.LoadFromFile(@"C:\Users\Administrator\Desktop\NewSpreadsheet.xlsx");
//Get the first worksheet
Worksheet sheet = wb.Worksheets[0];
//Get the cell range containing data
CellRange locatedRange = sheet.AllocatedRange;
//Iterate through the rows
for (int i = 0;i < locatedRange.Rows.Length;i++)
{
//Iterate through the columns
for (int j = 0; j < locatedRange.Rows[i].ColumnCount; j++)
{
//Get data of a specific cell
Console.Write(locatedRange[i + 1, j + 1].Value + " ");
}
Console.WriteLine();
}
}
}
}
Aktualisieren Sie ein Excel-Dokument in C#, VB.NET
Um den Wert einer bestimmten Zelle zu ändern, weisen Sie ihr einfach über die Worksheet.Range.Value-Eigenschaft einen neuen Wert zu. Im Folgenden finden Sie die detaillierten Schritte.
- Erstellen Sie ein Workbook-Objekt.
- Laden Sie ein Excel-Dokument mit der Methode Workbook.LoadFromFile().
- Rufen Sie ein bestimmtes Arbeitsblatt über die Eigenschaft Workbook.Worksheets[index] ab.
- Ändern Sie den Wert einer bestimmten Zelle mithilfe der Worksheet.Range.Value-Eigenschaft.
- Speichern Sie die Arbeitsmappe mit der Methode Workbook.SaveToFile() in einer Excel-Datei.
- C#
- VB.NET
using Spire.Xls;
namespace UpdateCellValue
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook wb = new Workbook();
//Load an existing Excel file
wb.LoadFromFile(@"C:\Users\Administrator\Desktop\NewSpreadsheet.xlsx");
//Get the first worksheet
Worksheet sheet = wb.Worksheets[0];
//Change the value of a specific cell
sheet.Range["A1"].Value = "Updated Value";
//Save to file
wb.SaveToFile("Updated.xlsx", ExcelVersion.Version2016);
}
}
}
Beantragen Sie eine temporäre Lizenz
Wenn Sie die Bewertungsmeldung aus den generierten Dokumenten entfernen oder die Funktionseinschränkungen beseitigen möchten, wenden Sie sich bitte an uns Fordern Sie eine 30-Tage-Testlizenz an für sich selbst.
C# Crear, leer o actualizar documentos de Excel
Tabla de contenido
Instalado a través de NuGet
PM> Install-Package Spire.XLS
enlaces relacionados
La hoja de cálculo de Excel es un formato de archivo ampliamente utilizado que permite a los usuarios organizar, analizar y presentar datos en formato tabular. La capacidad de interactuar con archivos de Excel mediante programación es muy valiosa, ya que permite la automatización e integración de la funcionalidad de Excel en aplicaciones de software. Esta capacidad es particularmente útil cuando se trabaja con grandes conjuntos de datos, se realizan cálculos complejos o cuando es necesario generar o actualizar datos dinámicamente. En este artículo, aprenderá cómo cree, lea o actualice documentos de Excel en C# y VB.NET utilizando Spire.XLS for .NET.
- Crear un archivo Excel en C#, VB.NET
- Leer datos de una hoja de trabajo en C#, VB.NET
- Actualizar un archivo Excel en C#, VB.NET
Instalar Spire.XLS for .NET
Para empezar, debe agregar los archivos DLL incluidos en el paquete Spire.XLS for .NET como referencias en su proyecto .NET. Los archivos DLL se pueden descargar desde este enlace o instalar a través de NuGet.
PM> Install-Package Spire.XLS
Crear un archivo Excel en C#, VB.NET
Spire.XLS for .NET ofrece una variedad de clases e interfaces que puede utilizar para crear y editar documentos de Excel. Aquí hay una lista de clases, propiedades y métodos importantes involucrados en este artículo.
| Miembro | Descripción |
| clase de libro de trabajo | Representa un modelo de libro de Excel. |
| Método Workbook.Worksheets.Add() | Agrega una hoja de trabajo al libro de trabajo. |
| Método Workbook.SaveToFile() | Guarda el libro en un documento de Excel. |
| clase de hoja de trabajo | Representa una hoja de cálculo en un libro de trabajo. |
| Propiedad Worksheet.Range | Obtiene una celda o rango de celdas específico de la hoja de trabajo. |
| Propiedad Hoja de trabajo.Rango.Valor | Obtiene o establece el valor de una celda. |
| Propiedad Worksheet.Rows | Obtiene una colección de filas en la hoja de trabajo. |
| Método Worksheet.InsertDataTable() | Importa datos de DataTable a la hoja de trabajo. |
| Clase rango de celdas | Representa una celda o rango de celdas en la hoja de trabajo. |
Los siguientes son los pasos para crear un documento de Excel desde cero usando Spire.XLS for .NET.
- Cree un objeto de libro de trabajo.
- Agregue una hoja de trabajo usando el método Workbook.Worksheets.Add().
- Escriba datos en una celda específica a través de la propiedad Worksheet.Range.Value.
- Importe datos de una tabla de datos a la hoja de trabajo utilizando el método Worksheet.InsertDataTable().
- Guarde el libro en un documento de Excel utilizando el método Workbook.SaveToFile().
- C#
- VB.NET
using Spire.Xls;
using System.Data;
namespace CreateExcelSpreadsheet
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook wb = new Workbook();
//Remove default worksheets
wb.Worksheets.Clear();
//Add a worksheet and name it "Employee"
Worksheet sheet = wb.Worksheets.Add("Employee");
//Merge the cells between A1 and G1
sheet.Range["A1:G1"].Merge();
//Write data to A1 and apply formatting to it
sheet.Range["A1"].Value = "Basic Information of Employees of Huanyu Automobile Company";
sheet.Range["A1"].HorizontalAlignment = HorizontalAlignType.Center;
sheet.Range["A1"].VerticalAlignment = VerticalAlignType.Center;
sheet.Range["A1"].Style.Font.IsBold = true;
sheet.Range["A1"].Style.Font.Size = 13F;
//Set row height of the first row
sheet.Rows[0].RowHeight = 30F;
//Create a DataTable
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Gender");
dt.Columns.Add("Birth Date");
dt.Columns.Add("Educational Background");
dt.Columns.Add("Contact Number");
dt.Columns.Add("Position");
dt.Columns.Add("ID");
dt.Rows.Add("Allen", "Male", "1990-02-10", "Bachelor", "24756854", "Mechanic", "0021");
dt.Rows.Add("Patrick", "Male", "1985-06-08", "Master", "59863247", "Mechanic", "0022");
dt.Rows.Add("Jenna", "Female", "1989-11-25", "Bachelor", "79540352", "Sales", "0023");
dt.Rows.Add("Tommy", "Male", "1988-04-16", "Master", "52014060", "Mechanic", "0024");
dt.Rows.Add("Christina", "Female", "1998-01-21", "Bachelor", "35401489", "HR", "0025");
//Import data from DataTable to worksheet
sheet.InsertDataTable(dt, true, 2, 1, true);
//Set row height of a range
sheet.Range["A2:G7"].RowHeight = 15F;
//Set column width
sheet.Range["A2:G7"].Columns[2].ColumnWidth = 15F;
sheet.Range["A2:G7"].Columns[3].ColumnWidth = 21F;
sheet.Range["A2:G7"].Columns[4].ColumnWidth = 15F;
//Set border style of a range
sheet.Range["A2:G7"].BorderAround(LineStyleType.Medium);
sheet.Range["A2:G7"].BorderInside(LineStyleType.Thin);
sheet.Range["A2:G2"].BorderAround(LineStyleType.Medium);
sheet.Range["A2:G7"].Borders.KnownColor = ExcelColors.Black;
//Save to a .xlsx file
wb.SaveToFile("NewSpreadsheet.xlsx", FileFormat.Version2016);
}
}
}
Leer datos de una hoja de trabajo en C#, VB.NET
La propiedad Worksheet.Range.Value devuelve el valor numérico o el valor de texto de una celda como una cadena. Para obtener datos de una hoja de trabajo completa o un rango de celdas, recorra las celdas que contiene. Los siguientes son los pasos para obtener datos de una hoja de trabajo usando Spire.XLS for .NET.
- Cree un objeto de libro de trabajo.
- Cargue un documento de Excel utilizando el método Workbook.LoadFromFile().
- Obtenga una hoja de trabajo específica a través de la propiedad Workbook.Worksheets[index].
- Obtenga el rango de celdas que contiene datos a través de la propiedad Worksheet.AllocatedRange.
- Itere a través de las filas y columnas para obtener celdas dentro del rango y devuelva el valor de cada celda a través de la propiedad CellRange.Value.
- C#
- VB.NET
using Spire.Xls;
namespace ReadExcelData
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook wb = new Workbook();
//Load an existing Excel file
wb.LoadFromFile(@"C:\Users\Administrator\Desktop\NewSpreadsheet.xlsx");
//Get the first worksheet
Worksheet sheet = wb.Worksheets[0];
//Get the cell range containing data
CellRange locatedRange = sheet.AllocatedRange;
//Iterate through the rows
for (int i = 0;i < locatedRange.Rows.Length;i++)
{
//Iterate through the columns
for (int j = 0; j < locatedRange.Rows[i].ColumnCount; j++)
{
//Get data of a specific cell
Console.Write(locatedRange[i + 1, j + 1].Value + " ");
}
Console.WriteLine();
}
}
}
}
Actualizar un documento de Excel en C#, VB.NET
Para cambiar el valor de una determinada celda, simplemente vuelva a asignarle un valor a través de la propiedad Worksheet.Range.Value. Los siguientes son los pasos detallados.
- Cree un objeto de libro de trabajo.
- Cargue un documento de Excel utilizando el método Workbook.LoadFromFile().
- Obtenga una hoja de trabajo específica a través de la propiedad Workbook.Worksheets[index].
- Cambie el valor de una celda en particular a través de la propiedad Worksheet.Range.Value.
- Guarde el libro en un archivo de Excel utilizando el método Workbook.SaveToFile().
- C#
- VB.NET
using Spire.Xls;
namespace UpdateCellValue
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook wb = new Workbook();
//Load an existing Excel file
wb.LoadFromFile(@"C:\Users\Administrator\Desktop\NewSpreadsheet.xlsx");
//Get the first worksheet
Worksheet sheet = wb.Worksheets[0];
//Change the value of a specific cell
sheet.Range["A1"].Value = "Updated Value";
//Save to file
wb.SaveToFile("Updated.xlsx", ExcelVersion.Version2016);
}
}
}
Solicite una licencia temporal
Si desea eliminar el mensaje de evaluación de los documentos generados o deshacerse de las limitaciones de la función, por favor solicitar una licencia de prueba de 30 días para ti.
C# Excel 문서 만들기, 읽기 또는 업데이트
목차
NuGet을 통해 설치됨
PM> Install-Package Spire.XLS
관련된 링크들
Excel 스프레드시트는 사용자가 데이터를 표 형식으로 구성, 분석 및 표시할 수 있도록 널리 사용되는 파일 형식입니다. 프로그래밍 방식으로 Excel 파일과 상호 작용하는 기능은 Excel 기능을 소프트웨어 응용 프로그램에 자동화하고 통합할 수 있으므로 매우 중요합니다. 이 기능은 대규모 데이터 세트로 작업하거나, 복잡한 계산을 수행하거나, 데이터를 동적으로 생성하거나 업데이트해야 할 때 특히 유용합니다. 이 기사에서는 다음 방법을 배웁니다 Spire.XLS for .NET 사용하여 C# 및 VB.NET에서 Excel 문서를 생성, 읽기 또는 업데이트합니다.
Spire.XLS for .NET 설치
먼저 .NET 프로젝트의 참조로 Spire.XLS for .NET 패키지에 포함된 DLL 파일을 추가해야 합니다. DLL 파일은 이 링크 에서 다운로드하거나 NuGet을 통해 설치할 수 있습니다.
PM> Install-Package Spire.XLS
C#, VB.NET에서 Excel 파일 만들기
Spire.XLS for .NET는 Excel 문서를 생성하고 편집하는 데 사용할 수 있는 다양한 클래스와 인터페이스를 제공합니다. 다음은 이 기사와 관련된 중요한 클래스, 속성 및 메서드 목록입니다.
| 회원 | 설명 |
| 워크북 수업 | Excel 통합 문서 모델을 나타냅니다. |
| Workbook.Worksheets.Add() 메서드 | 통합 문서에 워크시트를 추가합니다. |
| Workbook.SaveToFile() 메서드 | 통합 문서를 Excel 문서에 저장합니다. |
| 워크시트 수업 | 통합 문서의 워크시트를 나타냅니다. |
| Worksheet.Range 속성 | 워크시트에서 특정 셀이나 셀 범위를 가져옵니다. |
| Worksheet.Range.Value 속성 | 셀 값을 가져오거나 설정합니다. |
| Worksheet.Rows 속성 | 워크시트의 행 컬렉션을 가져옵니다. |
| Worksheet.InsertDataTable() 메서드 | DataTable의 데이터를 워크시트로 가져옵니다. |
| CellRange 클래스 | 워크시트의 셀 또는 셀 범위를 나타냅니다. |
다음은 Spire.XLS for .NET 사용하여 처음부터 Excel 문서를 만드는 단계입니다.
- 통합 문서 개체를 만듭니다.
- Workbook.Worksheets.Add() 메서드를 사용하여 워크시트를 추가합니다.
- Worksheet.Range.Value 속성을 통해 특정 셀에 데이터를 씁니다.
- Worksheet.InsertDataTable() 메서드를 사용하여 DataTable의 데이터를 워크시트로 가져옵니다.
- Workbook.SaveToFile() 메서드를 사용하여 통합 문서를 Excel 문서에 저장합니다.
- C#
- VB.NET
using Spire.Xls;
using System.Data;
namespace CreateExcelSpreadsheet
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook wb = new Workbook();
//Remove default worksheets
wb.Worksheets.Clear();
//Add a worksheet and name it "Employee"
Worksheet sheet = wb.Worksheets.Add("Employee");
//Merge the cells between A1 and G1
sheet.Range["A1:G1"].Merge();
//Write data to A1 and apply formatting to it
sheet.Range["A1"].Value = "Basic Information of Employees of Huanyu Automobile Company";
sheet.Range["A1"].HorizontalAlignment = HorizontalAlignType.Center;
sheet.Range["A1"].VerticalAlignment = VerticalAlignType.Center;
sheet.Range["A1"].Style.Font.IsBold = true;
sheet.Range["A1"].Style.Font.Size = 13F;
//Set row height of the first row
sheet.Rows[0].RowHeight = 30F;
//Create a DataTable
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Gender");
dt.Columns.Add("Birth Date");
dt.Columns.Add("Educational Background");
dt.Columns.Add("Contact Number");
dt.Columns.Add("Position");
dt.Columns.Add("ID");
dt.Rows.Add("Allen", "Male", "1990-02-10", "Bachelor", "24756854", "Mechanic", "0021");
dt.Rows.Add("Patrick", "Male", "1985-06-08", "Master", "59863247", "Mechanic", "0022");
dt.Rows.Add("Jenna", "Female", "1989-11-25", "Bachelor", "79540352", "Sales", "0023");
dt.Rows.Add("Tommy", "Male", "1988-04-16", "Master", "52014060", "Mechanic", "0024");
dt.Rows.Add("Christina", "Female", "1998-01-21", "Bachelor", "35401489", "HR", "0025");
//Import data from DataTable to worksheet
sheet.InsertDataTable(dt, true, 2, 1, true);
//Set row height of a range
sheet.Range["A2:G7"].RowHeight = 15F;
//Set column width
sheet.Range["A2:G7"].Columns[2].ColumnWidth = 15F;
sheet.Range["A2:G7"].Columns[3].ColumnWidth = 21F;
sheet.Range["A2:G7"].Columns[4].ColumnWidth = 15F;
//Set border style of a range
sheet.Range["A2:G7"].BorderAround(LineStyleType.Medium);
sheet.Range["A2:G7"].BorderInside(LineStyleType.Thin);
sheet.Range["A2:G2"].BorderAround(LineStyleType.Medium);
sheet.Range["A2:G7"].Borders.KnownColor = ExcelColors.Black;
//Save to a .xlsx file
wb.SaveToFile("NewSpreadsheet.xlsx", FileFormat.Version2016);
}
}
}
C#, VB.NET에서 워크시트 데이터 읽기
Worksheet.Range.Value 속성은 셀의 숫자 값 또는 텍스트 값을 문자열로 반환합니다. 전체 워크시트나 셀 범위의 데이터를 얻으려면 그 안의 셀을 반복하세요. 다음은 Spire.XLS for .NET 사용하여 워크시트의 데이터를 가져오는 단계입니다.
- 통합 문서 개체를 만듭니다.
- Workbook.LoadFromFile() 메서드를 사용하여 Excel 문서를 로드합니다.
- Workbook.Worksheets[index] 속성을 통해 특정 워크시트를 가져옵니다.
- Worksheet.AllocationRange 속성을 통해 데이터가 포함된 셀 범위를 가져옵니다.
- 행과 열을 반복하여 범위 내의 셀을 가져오고 CellRange.Value 속성을 통해 각 셀의 값을 반환합니다.
- C#
- VB.NET
using Spire.Xls;
namespace ReadExcelData
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook wb = new Workbook();
//Load an existing Excel file
wb.LoadFromFile(@"C:\Users\Administrator\Desktop\NewSpreadsheet.xlsx");
//Get the first worksheet
Worksheet sheet = wb.Worksheets[0];
//Get the cell range containing data
CellRange locatedRange = sheet.AllocatedRange;
//Iterate through the rows
for (int i = 0;i < locatedRange.Rows.Length;i++)
{
//Iterate through the columns
for (int j = 0; j < locatedRange.Rows[i].ColumnCount; j++)
{
//Get data of a specific cell
Console.Write(locatedRange[i + 1, j + 1].Value + " ");
}
Console.WriteLine();
}
}
}
}
C#, VB.NET에서 Excel 문서 업데이트
특정 셀의 값을 변경하려면 Worksheet.Range.Value 속성을 통해 값을 다시 할당하면 됩니다. 자세한 단계는 다음과 같습니다.
- 통합 문서 개체를 만듭니다.
- Workbook.LoadFromFile() 메서드를 사용하여 Excel 문서를 로드합니다.
- Workbook.Worksheets[index] 속성을 통해 특정 워크시트를 가져옵니다.
- Worksheet.Range.Value 속성을 통해 특정 셀의 값을 변경합니다.
- Workbook.SaveToFile() 메서드를 사용하여 통합 문서를 Excel 파일에 저장합니다.
- C#
- VB.NET
using Spire.Xls;
namespace UpdateCellValue
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook wb = new Workbook();
//Load an existing Excel file
wb.LoadFromFile(@"C:\Users\Administrator\Desktop\NewSpreadsheet.xlsx");
//Get the first worksheet
Worksheet sheet = wb.Worksheets[0];
//Change the value of a specific cell
sheet.Range["A1"].Value = "Updated Value";
//Save to file
wb.SaveToFile("Updated.xlsx", ExcelVersion.Version2016);
}
}
}
임시 라이센스 신청
생성된 문서에서 평가 메시지를 제거하고 싶거나, 기능 제한을 없애고 싶다면 30일 평가판 라이센스 요청 자신을 위해.
C# Crea, leggi o aggiorna documenti Excel
Sommario
Installato tramite NuGet
PM> Install-Package Spire.XLS
Link correlati
Il foglio di calcolo Excel è un formato di file ampiamente utilizzato che consente agli utenti di organizzare, analizzare e presentare i dati in formato tabellare. La capacità di interagire con i file Excel a livello di codice è estremamente preziosa, poiché consente l'automazione e l'integrazione delle funzionalità di Excel nelle applicazioni software. Questa funzionalità è particolarmente utile quando si lavora con set di dati di grandi dimensioni, si eseguono calcoli complessi o quando i dati devono essere generati o aggiornati dinamicamente. In questo articolo imparerai come farlo creare, leggere o aggiornare documenti Excel in C# e VB.NET utilizzando Spire.XLS for .NET.
- Crea un file Excel in C#, VB.NET
- Leggere i dati di un foglio di lavoro in C#, VB.NET
- Aggiorna un file Excel in C#, VB.NET
Installa Spire.XLS for .NET
Per cominciare, devi aggiungere i file DLL inclusi nel pacchetto Spire.XLS for .NET come riferimenti nel tuo progetto .NET. I file DLL possono essere scaricati da questo link o installato tramite NuGet.
PM> Install-Package Spire.XLS
Crea un file Excel in C#, VB.NET
Spire.XLS for .NET offre una varietà di classi e interfacce che è possibile utilizzare per creare e modificare documenti Excel. Di seguito è riportato un elenco di classi, proprietà e metodi importanti coinvolti in questo articolo.
| Membro | Descrizione |
| Classe cartella di lavoro | Rappresenta un modello di cartella di lavoro di Excel. |
| Metodo Workbook.Worksheets.Add() | Aggiunge un foglio di lavoro alla cartella di lavoro. |
| Metodo Workbook.SaveToFile() | Salva la cartella di lavoro in un documento Excel. |
| Classe di fogli di lavoro | Rappresenta un foglio di lavoro in una cartella di lavoro. |
| Proprietà Worksheet.Range | Ottiene una cella o un intervallo di celle specifico dal foglio di lavoro. |
| Proprietà Worksheet.Range.Value | Ottiene o imposta il valore di una cella. |
| Proprietà Worksheet.Rows | Ottiene una raccolta di righe nel foglio di lavoro. |
| Metodo Worksheet.InsertDataTable() | Importa i dati da DataTable al foglio di lavoro. |
| Classe CellRange | Rappresenta una cella o un intervallo di celle nel foglio di lavoro. |
Di seguito sono riportati i passaggi per creare un documento Excel da zero utilizzando Spire.XLS for .NET.
- Creare un oggetto cartella di lavoro.
- Aggiungi un foglio di lavoro utilizzando il metodo Workbook.Worksheets.Add().
- Scrivere i dati in una cella specifica tramite la proprietà Worksheet.Range.Value.
- Importa i dati da un DataTable al foglio di lavoro utilizzando il metodo Worksheet.InsertDataTable().
- Salva la cartella di lavoro in un documento Excel utilizzando il metodo Workbook.SaveToFile().
- C#
- VB.NET
using Spire.Xls;
using System.Data;
namespace CreateExcelSpreadsheet
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook wb = new Workbook();
//Remove default worksheets
wb.Worksheets.Clear();
//Add a worksheet and name it "Employee"
Worksheet sheet = wb.Worksheets.Add("Employee");
//Merge the cells between A1 and G1
sheet.Range["A1:G1"].Merge();
//Write data to A1 and apply formatting to it
sheet.Range["A1"].Value = "Basic Information of Employees of Huanyu Automobile Company";
sheet.Range["A1"].HorizontalAlignment = HorizontalAlignType.Center;
sheet.Range["A1"].VerticalAlignment = VerticalAlignType.Center;
sheet.Range["A1"].Style.Font.IsBold = true;
sheet.Range["A1"].Style.Font.Size = 13F;
//Set row height of the first row
sheet.Rows[0].RowHeight = 30F;
//Create a DataTable
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Gender");
dt.Columns.Add("Birth Date");
dt.Columns.Add("Educational Background");
dt.Columns.Add("Contact Number");
dt.Columns.Add("Position");
dt.Columns.Add("ID");
dt.Rows.Add("Allen", "Male", "1990-02-10", "Bachelor", "24756854", "Mechanic", "0021");
dt.Rows.Add("Patrick", "Male", "1985-06-08", "Master", "59863247", "Mechanic", "0022");
dt.Rows.Add("Jenna", "Female", "1989-11-25", "Bachelor", "79540352", "Sales", "0023");
dt.Rows.Add("Tommy", "Male", "1988-04-16", "Master", "52014060", "Mechanic", "0024");
dt.Rows.Add("Christina", "Female", "1998-01-21", "Bachelor", "35401489", "HR", "0025");
//Import data from DataTable to worksheet
sheet.InsertDataTable(dt, true, 2, 1, true);
//Set row height of a range
sheet.Range["A2:G7"].RowHeight = 15F;
//Set column width
sheet.Range["A2:G7"].Columns[2].ColumnWidth = 15F;
sheet.Range["A2:G7"].Columns[3].ColumnWidth = 21F;
sheet.Range["A2:G7"].Columns[4].ColumnWidth = 15F;
//Set border style of a range
sheet.Range["A2:G7"].BorderAround(LineStyleType.Medium);
sheet.Range["A2:G7"].BorderInside(LineStyleType.Thin);
sheet.Range["A2:G2"].BorderAround(LineStyleType.Medium);
sheet.Range["A2:G7"].Borders.KnownColor = ExcelColors.Black;
//Save to a .xlsx file
wb.SaveToFile("NewSpreadsheet.xlsx", FileFormat.Version2016);
}
}
}
Leggere i dati di un foglio di lavoro in C#, VB.NET
La proprietà Worksheet.Range.Value restituisce il valore numerico o il valore testo di una cella come stringa. Per ottenere i dati di un intero foglio di lavoro o di un intervallo di celle, scorrere le celle al suo interno. Di seguito sono riportati i passaggi per ottenere i dati di un foglio di lavoro utilizzando Spire.XLS for .NET.
- Creare un oggetto cartella di lavoro.
- Carica un documento Excel utilizzando il metodo Workbook.LoadFromFile().
- Ottieni un foglio di lavoro specifico tramite la proprietà Workbook.Worksheets[index].
- Ottieni l'intervallo di celle contenente i dati tramite la proprietà Worksheet.AllocatedRange.
- Scorrere le righe e le colonne per ottenere le celle all'interno dell'intervallo e restituire il valore di ciascuna cella tramite la proprietà CellRange.Value.
- C#
- VB.NET
using Spire.Xls;
namespace ReadExcelData
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook wb = new Workbook();
//Load an existing Excel file
wb.LoadFromFile(@"C:\Users\Administrator\Desktop\NewSpreadsheet.xlsx");
//Get the first worksheet
Worksheet sheet = wb.Worksheets[0];
//Get the cell range containing data
CellRange locatedRange = sheet.AllocatedRange;
//Iterate through the rows
for (int i = 0;i < locatedRange.Rows.Length;i++)
{
//Iterate through the columns
for (int j = 0; j < locatedRange.Rows[i].ColumnCount; j++)
{
//Get data of a specific cell
Console.Write(locatedRange[i + 1, j + 1].Value + " ");
}
Console.WriteLine();
}
}
}
}
Aggiorna un documento Excel in C#, VB.NET
Per modificare il valore di una determinata cella, è sufficiente riassegnarle un valore tramite la proprietà Worksheet.Range.Value. Di seguito sono riportati i passaggi dettagliati.
- Creare un oggetto cartella di lavoro.
- Carica un documento Excel utilizzando il metodo Workbook.LoadFromFile().
- Ottieni un foglio di lavoro specifico tramite la proprietà Workbook.Worksheets[index].
- Modificare il valore di una cella particolare tramite la proprietà Worksheet.Range.Value.
- Salva la cartella di lavoro in un file Excel utilizzando il metodo Workbook.SaveToFile().
- C#
- VB.NET
using Spire.Xls;
namespace UpdateCellValue
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook wb = new Workbook();
//Load an existing Excel file
wb.LoadFromFile(@"C:\Users\Administrator\Desktop\NewSpreadsheet.xlsx");
//Get the first worksheet
Worksheet sheet = wb.Worksheets[0];
//Change the value of a specific cell
sheet.Range["A1"].Value = "Updated Value";
//Save to file
wb.SaveToFile("Updated.xlsx", ExcelVersion.Version2016);
}
}
}
Richiedi una licenza temporanea
Se desideri rimuovere il messaggio di valutazione dai documenti generati o eliminare le limitazioni della funzione, per favore richiedere una licenza di prova di 30 giorni per te.
C# Créer, lire ou mettre à jour des documents Excel
Table des matières
Installé via NuGet
PM> Install-Package Spire.XLS
Liens connexes
La feuille de calcul Excel est un format de fichier largement utilisé qui permet aux utilisateurs d'organiser, d'analyser et de présenter des données sous forme de tableau. La possibilité d'interagir avec les fichiers Excel par programmation est très précieuse, car elle permet l'automatisation et l'intégration des fonctionnalités Excel dans les applications logicielles. Cette fonctionnalité est particulièrement utile lorsque vous travaillez avec de grands ensembles de données, effectuez des calculs complexes ou lorsque les données doivent être générées ou mises à jour dynamiquement. Dans cet article, vous apprendrez comment créer, lire ou mettre à jour des documents Excel en C# et VB.NET à l'aide de Spire.XLS for .NET.
- Créer un fichier Excel en C#, VB.NET
- Lire les données d'une feuille de calcul en C#, VB.NET
- Mettre à jour un fichier Excel en C#, VB.NET
Installez Spire.XLS for .NET
Pour commencer, vous devez ajouter les fichiers DLL inclus dans le package Spire.XLS for .NET comme références dans votre projet .NET. Les fichiers DLL peuvent être téléchargés à partir de ce lien ou installés via NuGet.
PM> Install-Package Spire.XLS
Créer un fichier Excel en C#, VB.NET
Spire.XLS for .NET propose une variété de classes et d'interfaces que vous pouvez utiliser pour créer et modifier des documents Excel. Voici une liste des classes, propriétés et méthodes importantes impliquées dans cet article.
| Membre | Description |
| Classe de classeur | Représente un modèle de classeur Excel. |
| Méthode Workbook.Worksheets.Add() | Ajoute une feuille de calcul au classeur. |
| Méthode Workbook.SaveToFile() | Enregistre le classeur dans un document Excel. |
| Classe de feuille de travail | Représente une feuille de calcul dans un classeur. |
| Propriété Worksheet.Range | Obtient une cellule ou une plage de cellules spécifique à partir d’une feuille de calcul. |
| Propriété Worksheet.Range.Value | Obtient ou définit la valeur d'une cellule. |
| Propriété Worksheet.Rows | Obtient une collection de lignes dans une feuille de calcul. |
| Méthode Worksheet.InsertDataTable() | Importe les données de DataTable vers la feuille de calcul. |
| Classe CellRange | Représente une cellule ou une plage de cellules dans une feuille de calcul. |
Voici les étapes pour créer un document Excel à partir de zéro à l’aide de Spire.XLS for .NET.
- Créez un objet Workbook.
- Ajoutez une feuille de calcul à l’aide de la méthode Workbook.Worksheets.Add().
- Écrivez des données dans une cellule spécifique via la propriété Worksheet.Range.Value.
- Importez les données d'un DataTable vers la feuille de calcul à l'aide de la méthode Worksheet.InsertDataTable().
- Enregistrez le classeur dans un document Excel à l'aide de la méthode Workbook.SaveToFile().
- C#
- VB.NET
using Spire.Xls;
using System.Data;
namespace CreateExcelSpreadsheet
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook wb = new Workbook();
//Remove default worksheets
wb.Worksheets.Clear();
//Add a worksheet and name it "Employee"
Worksheet sheet = wb.Worksheets.Add("Employee");
//Merge the cells between A1 and G1
sheet.Range["A1:G1"].Merge();
//Write data to A1 and apply formatting to it
sheet.Range["A1"].Value = "Basic Information of Employees of Huanyu Automobile Company";
sheet.Range["A1"].HorizontalAlignment = HorizontalAlignType.Center;
sheet.Range["A1"].VerticalAlignment = VerticalAlignType.Center;
sheet.Range["A1"].Style.Font.IsBold = true;
sheet.Range["A1"].Style.Font.Size = 13F;
//Set row height of the first row
sheet.Rows[0].RowHeight = 30F;
//Create a DataTable
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Gender");
dt.Columns.Add("Birth Date");
dt.Columns.Add("Educational Background");
dt.Columns.Add("Contact Number");
dt.Columns.Add("Position");
dt.Columns.Add("ID");
dt.Rows.Add("Allen", "Male", "1990-02-10", "Bachelor", "24756854", "Mechanic", "0021");
dt.Rows.Add("Patrick", "Male", "1985-06-08", "Master", "59863247", "Mechanic", "0022");
dt.Rows.Add("Jenna", "Female", "1989-11-25", "Bachelor", "79540352", "Sales", "0023");
dt.Rows.Add("Tommy", "Male", "1988-04-16", "Master", "52014060", "Mechanic", "0024");
dt.Rows.Add("Christina", "Female", "1998-01-21", "Bachelor", "35401489", "HR", "0025");
//Import data from DataTable to worksheet
sheet.InsertDataTable(dt, true, 2, 1, true);
//Set row height of a range
sheet.Range["A2:G7"].RowHeight = 15F;
//Set column width
sheet.Range["A2:G7"].Columns[2].ColumnWidth = 15F;
sheet.Range["A2:G7"].Columns[3].ColumnWidth = 21F;
sheet.Range["A2:G7"].Columns[4].ColumnWidth = 15F;
//Set border style of a range
sheet.Range["A2:G7"].BorderAround(LineStyleType.Medium);
sheet.Range["A2:G7"].BorderInside(LineStyleType.Thin);
sheet.Range["A2:G2"].BorderAround(LineStyleType.Medium);
sheet.Range["A2:G7"].Borders.KnownColor = ExcelColors.Black;
//Save to a .xlsx file
wb.SaveToFile("NewSpreadsheet.xlsx", FileFormat.Version2016);
}
}
}
Lire les données d'une feuille de calcul en C#, VB.NET
La propriété Worksheet.Range.Value renvoie la valeur numérique ou la valeur textuelle d'une cellule sous forme de chaîne. Pour obtenir les données d’une feuille de calcul entière ou d’une plage de cellules, parcourez les cellules qu’elle contient. Voici les étapes pour obtenir les données d'une feuille de calcul à l'aide de Spire.XLS for .NET.
- Créez un objet Workbook.
- Chargez un document Excel à l'aide de la méthode Workbook.LoadFromFile().
- Obtenez une feuille de calcul spécifique via la propriété Workbook.Worksheets[index].
- Obtenez la plage de cellules contenant les données via la propriété Worksheet.AllocatedRange.
- Parcourez les lignes et les colonnes pour obtenir les cellules dans la plage et renvoyez la valeur de chaque cellule via la propriété CellRange.Value.
- C#
- VB.NET
using Spire.Xls;
namespace ReadExcelData
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook wb = new Workbook();
//Load an existing Excel file
wb.LoadFromFile(@"C:\Users\Administrator\Desktop\NewSpreadsheet.xlsx");
//Get the first worksheet
Worksheet sheet = wb.Worksheets[0];
//Get the cell range containing data
CellRange locatedRange = sheet.AllocatedRange;
//Iterate through the rows
for (int i = 0;i < locatedRange.Rows.Length;i++)
{
//Iterate through the columns
for (int j = 0; j < locatedRange.Rows[i].ColumnCount; j++)
{
//Get data of a specific cell
Console.Write(locatedRange[i + 1, j + 1].Value + " ");
}
Console.WriteLine();
}
}
}
}
Mettre à jour un document Excel en C#, VB.NET
Pour modifier la valeur d'une certaine cellule, réattribuez-lui simplement une valeur via la propriété Worksheet.Range.Value. Voici les étapes détaillées..
- Créez un objet Workbook.
- Chargez un document Excel à l'aide de la méthode Workbook.LoadFromFile().
- Obtenez une feuille de calcul spécifique via la propriété Workbook.Worksheets[index].
- Modifiez la valeur d’une cellule particulière via la propriété Worksheet.Range.Value.
- Enregistrez le classeur dans un fichier Excel à l’aide de la méthode Workbook.SaveToFile().
- C#
- VB.NET
using Spire.Xls;
namespace UpdateCellValue
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook wb = new Workbook();
//Load an existing Excel file
wb.LoadFromFile(@"C:\Users\Administrator\Desktop\NewSpreadsheet.xlsx");
//Get the first worksheet
Worksheet sheet = wb.Worksheets[0];
//Change the value of a specific cell
sheet.Range["A1"].Value = "Updated Value";
//Save to file
wb.SaveToFile("Updated.xlsx", ExcelVersion.Version2016);
}
}
}
Demander une licence temporaire
Si vous souhaitez supprimer le message d'évaluation des documents générés ou vous débarrasser des limitations fonctionnelles, veuillez demander une licence d'essai de 30 jours pour toi.
C# Convert Excel to HTML
Índice
Installed via NuGet
PM> Install-Package Spire.XLS
Links Relacionados
A planilha do Excel é um formato de arquivo amplamente utilizado que permite aos usuários organizar, analisar e apresentar dados em formato tabular. A capacidade de interagir programaticamente com arquivos do Excel é altamente valiosa, pois permite a automação e integração da funcionalidade do Excel em aplicativos de software. Esse recurso é particularmente útil ao trabalhar com grandes conjuntos de dados, realizar cálculos complexos ou quando os dados precisam ser gerados ou atualizados dinamicamente. Neste artigo você aprenderá como crie, leia ou atualize documentos Excel em C# e VB.NET usando Spire.XLS for .NET.
- Crie um arquivo Excel em C#, VB.NET
- Leia dados de uma planilha em C#, VB.NET
- Atualizar um arquivo Excel em C#, VB.NET
Instale o Spire.XLS for .NET
Para começar, você precisa adicionar os arquivos DLL incluídos no pacote Spire.XLS for .NET como referências em seu projeto .NET. Os arquivos DLL podem ser baixados deste link ou instalados via NuGet.
PM> Install-Package Spire.XLS
Crie um arquivo Excel em C#, VB.NET
Spire.XLS for .NET oferece uma variedade de classes e interfaces que você pode usar para criar e editar documentos Excel. Aqui está uma lista de classes, propriedades e métodos importantes envolvidos neste artigo.
| Membro | Descrição |
| Aula de apostila | Representa um modelo de pasta de trabalho do Excel. |
| Método Workbook.Worksheets.Add() | Adiciona uma planilha à pasta de trabalho. |
| Método Workbook.SaveToFile() | Salva a pasta de trabalho em um documento Excel. |
| Aula de planilha | Representa uma planilha em uma pasta de trabalho. |
| Propriedade Worksheet.Range | Obtém uma célula ou intervalo de células específico da planilha. |
| Propriedade Worksheet.Range.Value | Obtém ou define o valor de uma célula. |
| Propriedade Worksheet.Rows | Obtém uma coleção de linhas na planilha. |
| Método Worksheet.InsertDataTable() | Importa dados do DataTable para a planilha. |
| Classe CellRange | Representa uma célula ou intervalo de células na planilha. |
A seguir estão as etapas para criar um documento Excel do zero usando Spire.XLS for .NET.
- Crie um objeto Pasta de trabalho.
- Adicione uma planilha usando o método Workbook.Worksheets.Add().
- Grave dados em uma célula específica por meio da propriedade Worksheet.Range.Value.
- Importe dados de um DataTable para a planilha usando o método Worksheet.InsertDataTable().
- Salve a pasta de trabalho em um documento Excel usando o método Workbook.SaveToFile().
- C#
- VB.NET
using Spire.Xls;
using System.Data;
namespace CreateExcelSpreadsheet
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook wb = new Workbook();
//Remove default worksheets
wb.Worksheets.Clear();
//Add a worksheet and name it "Employee"
Worksheet sheet = wb.Worksheets.Add("Employee");
//Merge the cells between A1 and G1
sheet.Range["A1:G1"].Merge();
//Write data to A1 and apply formatting to it
sheet.Range["A1"].Value = "Basic Information of Employees of Huanyu Automobile Company";
sheet.Range["A1"].HorizontalAlignment = HorizontalAlignType.Center;
sheet.Range["A1"].VerticalAlignment = VerticalAlignType.Center;
sheet.Range["A1"].Style.Font.IsBold = true;
sheet.Range["A1"].Style.Font.Size = 13F;
//Set row height of the first row
sheet.Rows[0].RowHeight = 30F;
//Create a DataTable
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Gender");
dt.Columns.Add("Birth Date");
dt.Columns.Add("Educational Background");
dt.Columns.Add("Contact Number");
dt.Columns.Add("Position");
dt.Columns.Add("ID");
dt.Rows.Add("Allen", "Male", "1990-02-10", "Bachelor", "24756854", "Mechanic", "0021");
dt.Rows.Add("Patrick", "Male", "1985-06-08", "Master", "59863247", "Mechanic", "0022");
dt.Rows.Add("Jenna", "Female", "1989-11-25", "Bachelor", "79540352", "Sales", "0023");
dt.Rows.Add("Tommy", "Male", "1988-04-16", "Master", "52014060", "Mechanic", "0024");
dt.Rows.Add("Christina", "Female", "1998-01-21", "Bachelor", "35401489", "HR", "0025");
//Import data from DataTable to worksheet
sheet.InsertDataTable(dt, true, 2, 1, true);
//Set row height of a range
sheet.Range["A2:G7"].RowHeight = 15F;
//Set column width
sheet.Range["A2:G7"].Columns[2].ColumnWidth = 15F;
sheet.Range["A2:G7"].Columns[3].ColumnWidth = 21F;
sheet.Range["A2:G7"].Columns[4].ColumnWidth = 15F;
//Set border style of a range
sheet.Range["A2:G7"].BorderAround(LineStyleType.Medium);
sheet.Range["A2:G7"].BorderInside(LineStyleType.Thin);
sheet.Range["A2:G2"].BorderAround(LineStyleType.Medium);
sheet.Range["A2:G7"].Borders.KnownColor = ExcelColors.Black;
//Save to a .xlsx file
wb.SaveToFile("NewSpreadsheet.xlsx", FileFormat.Version2016);
}
}
}
Leia dados de uma planilha em C#, VB.NET
A propriedade Worksheet.Range.Value retorna o valor numérico ou o valor do texto de uma célula como uma string. Para obter dados de uma planilha inteira ou de um intervalo de células, percorra as células dentro dela. A seguir estão as etapas para obter dados de uma planilha usando Spire.XLS for .NET.
- Crie um objeto Pasta de trabalho.
- Carregue um documento Excel usando o método Workbook.LoadFromFile().
- Obtenha uma planilha específica por meio da propriedade Workbook.Worksheets[index].
- Obtenha o intervalo de células que contém dados por meio da propriedade Worksheet.AllocatedRange.
- Itere pelas linhas e colunas para obter células dentro do intervalo e retorne o valor de cada célula por meio da propriedade CellRange.Value.
- C#
- VB.NET
using Spire.Xls;
namespace ReadExcelData
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook wb = new Workbook();
//Load an existing Excel file
wb.LoadFromFile(@"C:\Users\Administrator\Desktop\NewSpreadsheet.xlsx");
//Get the first worksheet
Worksheet sheet = wb.Worksheets[0];
//Get the cell range containing data
CellRange locatedRange = sheet.AllocatedRange;
//Iterate through the rows
for (int i = 0;i < locatedRange.Rows.Length;i++)
{
//Iterate through the columns
for (int j = 0; j < locatedRange.Rows[i].ColumnCount; j++)
{
//Get data of a specific cell
Console.Write(locatedRange[i + 1, j + 1].Value + " ");
}
Console.WriteLine();
}
}
}
}
Atualizar um documento Excel em C#, VB.NET
Para alterar o valor de uma determinada célula, basta reatribuir um valor a ela através da propriedade Worksheet.Range.Value. A seguir estão as etapas detalhadas.
- Crie um objeto Pasta de trabalho.
- Carregue um documento Excel usando o método Workbook.LoadFromFile().
- Obtenha uma planilha específica por meio da propriedade Workbook.Worksheets[index].
- Altere o valor de uma célula específica por meio da propriedade Worksheet.Range.Value.
- Salve a pasta de trabalho em um arquivo Excel usando o método Workbook.SaveToFile().
- C#
- VB.NET
using Spire.Xls;
namespace UpdateCellValue
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook wb = new Workbook();
//Load an existing Excel file
wb.LoadFromFile(@"C:\Users\Administrator\Desktop\NewSpreadsheet.xlsx");
//Get the first worksheet
Worksheet sheet = wb.Worksheets[0];
//Change the value of a specific cell
sheet.Range["A1"].Value = "Updated Value";
//Save to file
wb.SaveToFile("Updated.xlsx", ExcelVersion.Version2016);
}
}
}
Solicite uma licença temporária
Se desejar remover a mensagem de avaliação dos documentos gerados ou se livrar das limitações de função, por favor solicite uma licença de teste de 30 dias para você mesmo.