Export and Import Excel Data in C#
When working with Excel files in C#, you can use libraries like EPPlus. A well-liked open-source library called EPPlus makes producing, reading, and altering Excel files simple. Using EPPlus, follow these instructions to export and import Excel data.
What is NPOI?
NPOI is an abbreviation for “Non-Profit Open XML Interface” It is a free and open-source library for working with Microsoft Office documents, especially Word papers, Power Point presentations, and Excel spreadsheets. NPOI enables developers to create, read, write, and manipulate Office documents without install Microsoft Office.
- NPOI was created to interact with the Open XML formats that Microsoft included in their Office 2007 suite. Based on XML and ZIP archive technologies, Open XML is an open standard for managing office documents.
- It offers a standardized approach to represent different Office document components, making it simpler to create, read, and alter them programmatically.
NPOI's salient attributes include: NPOI offers APIs to create, alter, and read Excel spreadsheets, making it a popular choice for working with Excel files (XLSX format). You can use cells, formulas, formatting, and other things.
Note: Make sure the EPPlus library has been installed using NuGet first
Support for Word and PowerPoint: NPOI offers the same manipulation capabilities when working with Word documents (DOCX format) and PowerPoint presentations (PPTX format).
Cross-Platform: NPOI was created in C# and is open-source; it may be used in Windows and non-Windows systems.
- NPOI is independent of Microsoft Office. Thus you don't need to install it on the computer where you use it.
- This is especially helpful in server contexts where there might be better ideas than installing Office.
Flexibility: For working with Office documents, NPOI offers a versatile API. You can edit already-existing documents, create new ones from scratch, and extract data from them.
Community and documentation: NPOI has a vibrant community that supports and contributes to its growth.
- To assist you in getting started, materials are offered, including tutorials, examples, and documentation.
- For developers who need to automate the creation and manipulation of Office documents in their apps, NPOI is a potent tool.
- It's especially helpful if you have to carry out these duties in settings where Microsoft Office might need to be more practical and available.
Using the NPOI Library, export and import Excel data in C#
Let's assume you wish to export a list of data to an Excel file.
Here is an illustration of data exportation to an Excel file using NPOI:
Example:
using System;
using System.Collections.Generic;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
namespace ExcelExportImportNPOI
{
class Program
{
static void Main(string[] args)
{
List<Person> people = new List<Person>
{
new Person { FirstName = "Ram", LastName = "Deva", Age = 29 },
new Person { FirstName = "Seetha", LastName = "Devi", Age = 21 }
};
string filePath = "exported_data.xlsx";
using (var fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("People");
int rowIndex = 0;
IRow headerRow = sheet.CreateRow(rowIndex++);
headerRow.CreateCell(0).SetCellValue("First Name");
headerRow.CreateCell(1).SetCellValue("Last Name");
headerRow.CreateCell(2).SetCellValue("Age");
for each (var person in people)
{
IRow dataRow = sheet.CreateRow(rowIndex++);
dataRow.CreateCell(0).SetCellValue(person.FirstName);
dataRow.CreateCell(1).SetCellValue(person.LastName);
dataRow.CreateCell(2).SetCellValue(person.Age);
}
workbook.Write(fs);
}
Console.WriteLine("Data exported to Excel successfully.");
}
}
public class Person
{
public string FirstName { get; set; }
public string LastName { get; set; }
public int Age { get; set; }
}
}
Output:
The program will generate an Excel file called "exported_data.xlsx" in the same directory as the program file.
The "People" worksheet in the Excel file will have the following information on it:
The program will produce the message "Data exported to Excel successfully." to the terminal once the data has been successfully written to the Excel file.
Before executing the program, please verify that the NPOI library is properly set up and referenced in your project. The NPOI and NPOI.For this program to work, OOXML NuGet packages must be included in your project.
Excel Data Import:
Use a similar method to import data into NPOI from an Excel file.
An illustration of how to read data from an Excel file is shown below:
Example:
using System;
using System.Collections.Generic;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
namespace ExcelExportImportNPOI
{
class Program2
{
static void Main(string[] args)
{
string filePath = "exported_data.xlsx";
using (var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = new XSSFWorkbook(fs);
ISheet sheet = workbook.GetSheetAt(0);
List<Person> importedPeople = new List<Person>();
for (int row = 1; row <= sheet.LastRowNum; row++)
{
IRow dataRow = sheet.GetRow(row);
importedPeople.Add(new Person
{
FirstName = dataRow.GetCell(0).StringCellValue,
LastName = dataRow.GetCell(1).StringCellValue,
Age = Convert.ToInt32(dataRow.GetCell(2).NumericCellValue)
});
}
for each (var person in importedPeople)
{
Console.WriteLine($"Name: {person.FirstName} {person.LastName}, Age: {person.Age}");
}
}
}
}
public class Person
{
public string FirstName { get; set; }
public string LastName { get; set; }
public int Age { get; set; }
}
}
Output:
The included C# program imports data from an NPOI-created Excel file, processes it, and outputs the results to the console.
The output of this program, assuming that the Excel file "exported_data.xlsx" was produced by the one you offered, will be:
Exporting through ClosedXML
Another package that makes it simple to create, edit, and export Excel spreadsheets is ClosedXML.
It may be installed using NuGet:
using System;
using ClosedXML.Excel;
class Program2
{
static void Main(string[] args)
{
var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add("People");
worksheet.Cell("A1").Value = "First Name";
worksheet.Cell("B1").Value = "Last Name";
worksheet.Cell("C1").Value = "Age";
worksheet.Cell("A2").Value = "Ram";
worksheet.Cell("B2").Value = "Seetha";
worksheet.Cell("C2").Value = 29;
worksheet.Cell("A3").Value = "Devuda";
worksheet.Cell("B3").Value = "Smitha";
worksheet.Cell("C3").Value = 26;
workbook.SaveAs("exported_data.xlsx");
Console.WriteLine("Data exported to Excel successfully.");
}
}
Output:
The included C# program creates an Excel file, adds data, and then saves it using the ClosedXML library.
The program will generate an Excel file called "exported_data.xlsx" in the same directory as the program file.
The "People" worksheet in the Excel file will have the following information on it:
The program will produce the message "Data exported to Excel successfully." to the terminal once the data has been successfully written to the Excel file.
Before executing the program, please verify that the ClosedXML library is properly set up and referenced in your project. This program assumes that your project already contains the required ClosedXML NuGet package.
In the same directory as the program file, an Excel file called "exported_data.xlsx" is generated when you run this program.
Using ExcelDataReader import
A library called ExcelDataReader makes it possible to read Excel files without using Microsoft Office.
It may be installed using NuGet:
using System;
using System.Data;
using System.IO;
using ExcelDataReader;
class Program3
{
static void Main(string[] args)
{
using (var stream = File. Open("exported_data.xlsx," FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
do
{
while (reader. Read())
{
string firstName = reader.GetString(0);
string lastName = reader.GetString(1);
int age = reader.GetInt32(2);
Console.WriteLine($"Name: {firstName} {lastName}, Age: {age}");
}
} while (reader.NextResult());
}
}
}
}
Both examples have been simplified to give you a fundamental understanding of dealing with the ClosedXML and ExcelDataReader libraries. The code should be modified to meet your needs, and error checks should be handled correctly.
Output:
Uses of Excel Export and Import:
Excel Data Export:
Data Reporting: Business analytics reports, charts, and visualizations are frequently created by exporting data to Excel.
Data backup: Excel files can be used as a basic backup solution for storing data outside of databases or other systems.
Data sharing: Users can share information with people who might not have direct access to a database by exporting data to Excel.
Data analysis: Using Excel's built-in capabilities can be simpler when data is exported to Excel.
Bulk Data Entry: Users can export data to Excel, make changes, and reimport the revised data into the program.
Making Templates: Users can utilize exported Excel files as templates to fill up data before importing.
Custom Reporting: Users have the option to export data and alter it in Excel to produce reports that are uniquely formatted.
Data Import into Excel:
Data migration: When transferring data from outdated systems to new applications, Excel imports might be helpful.
Data Integration: Data from diverse sources can be integrated into a single system or database by importing the data.
Bulk Data Loading: applications can use Excel import to more quickly load massive amounts of data than human data entering.
User Data Entry: Users can prepare data in Excel and import it into the program, which saves the time by eliminating the need for manual data entry.
Data validation: Importing data enables validation checks before updating the application's database.
Workflow automation: Data imports can start automatic procedures like producing reports or notifying users.
An application's flexibility and interoperability are improved by exporting and importing Excel data. They give consumers a comfortable data management interface and enable seamless data transmission between various systems. Nevertheless, managing fault situations and guaranteeing data consistency across these procedures is crucial.