How to Work with Excel Files in C#: Figure 1 - Tutorial

How Do I Work with Excel Workbook in C#?

1. Create a Visual Studio project and add the IronXL NuGet package.
2. Create an Excel file without Interop.
3. Add Style to Excel file using IronXL.
4. Read values from Excel and calculate.
5. Convert Excel to HTML for web usage.

What Is IronXL Library?

IronXL is a robust library for .NET that simplifies working with Excel files. It features an intuitive API that simplifies Excel document manipulation, supporting various formats like XLS, XLSX, and CSV. This versatility allows easy manipulation of cell values, formulas, and formatting. IronXL is optimized for performance, capable of efficiently handling large files and complex data operations while ensuring efficient memory usage. Its cross-platform compatibility enhances its utility across different operating systems. Here are its key features and benefits:

What Are the Key Features of IronXL?

1. Import and Export:

   • Import Data: Supports XLS, XLSX, CSV, and TSV formats.
   • Export Data: Can export worksheets to XLS, XLSX, CSV, TSV, and JSON.

2. Data Manipulation:

   • System.Data Integration: Work with spreadsheets as DataSet and DataTable objects.
   • Formulas: Supports Excel formulas which recalculate when sheets are edited.

3. Styling and Formatting:

   • Cell Styling: Customize font, size, background pattern, border, alignment, and number formats.
   • Ranges: Intuitive range setting with WorkSheet["A1:B10"] syntax.

4. Security:

   • Encryption: Encrypt and decrypt XLSX, XLSM, and XLTX files with passwords.
5. Cross Platform Compatibility:
   • Works with .NET Framework, .NET Core, .NET Standard, and Azure.
   • Compatible with Windows, macOS, Linux, Docker, and AWS.

Why Should I Choose IronXL Over Other Excel Libraries?

1. No Need for Microsoft Office: IronXL does not require Microsoft Office to be installed, making it lightweight and easy to deploy.
2. Ease of Use: The API is intuitive and easy to use, allowing developers to quickly integrate Excel functionality into their applications.
3. Performance: IronXL is optimized for performance, ensuring fast and efficient processing of large Excel files.
4. Versatility: Suitable for a wide range of applications, including web, desktop, and cloud-based solutions.
5. Comprehensive Documentation: Extensive documentation and examples are available, making it easier for developers to get started and find solutions to common problems.

How Do I Get Started with IronXL?

To use IronXL in your .NET projects, you need to ensure that your development environment meets the following prerequisites:

dotnet add package IronXL.Excel --version 2024.8.5

dotnet add package IronXL.Excel --version 2024.8.5

How Do I Create My First Excel File with IronXL?

Let's develop a Visual Studio project and add the IronXL library to get started with working on an Excel file.

How Do I Create a Visual Studio Project and Add IronXL?

Open Microsoft Visual Studio and select 'Create a new project' to get started.

Select the project template of your choice. Here we've selected Console application for simplicity.

Now enter the project name and location for the project.

Select the .NET Framework version you prefer. We've selected the latest available on our machine.

Once you click the Create button, the project will be created and ready for use. Open Solution Explorer to check the project files.

Now, let's install the IronXL library from the NuGet package manager as shown above.

How Do I Create an Excel File Without Interop?

Now let's create an Excel file without using Microsoft Interop library. IronXL provides a simple and intuitive way to create spreadsheets programmatically.

using IronXL;

namespace WorkingWithIronXL
{
    public class Program
    {
        public static void Main()
        {
            // Create new Excel WorkBook Object
            WorkBook workBook = WorkBook.Create();
            // Create WorkSheet
            WorkSheet workSheet = workBook.CreateWorkSheet("newXlDemo");
            // Add data in new worksheet
            workSheet["A1"].Value = "IronXL is the best Excel library";           
            // Save the Excel file as XLSX
            workBook.SaveAs("myIronXlDemo.xlsx");
        }
    }
}

using IronXL;

namespace WorkingWithIronXL
{
    public class Program
    {
        public static void Main()
        {
            // Create new Excel WorkBook Object
            WorkBook workBook = WorkBook.Create();
            // Create WorkSheet
            WorkSheet workSheet = workBook.CreateWorkSheet("newXlDemo");
            // Add data in new worksheet
            workSheet["A1"].Value = "IronXL is the best Excel library";           
            // Save the Excel file as XLSX
            workBook.SaveAs("myIronXlDemo.xlsx");
        }
    }
}

Code Explanation

1. We add the IronXL namespace to start working with the library.
2. Create an Excel object with WorkBook.Create() to create an XLSX file.
3. Call CreateWorkSheet method to create a worksheet inside the workbook.
4. Add values to cells using workSheet["A1"].Value.
5. Save the Excel file using workBook.SaveAs by providing a filename.

Output Excel file

// Save as different Excel formats
workBook.SaveAs("file.xlsx"); // Excel 2007+ format
workBook.SaveAs("file.xls");  // Excel 97-2003 format
workBook.SaveAs("file.xlsm"); // Excel with macros

// Export to other formats
workBook.SaveAsCsv("file.csv", ",");  // CSV with comma delimiter
workBook.SaveAsJson("file.json");     // JSON format
workBook.SaveAsXml("file.xml");       // XML format

// Save as different Excel formats
workBook.SaveAs("file.xlsx"); // Excel 2007+ format
workBook.SaveAs("file.xls");  // Excel 97-2003 format
workBook.SaveAs("file.xlsm"); // Excel with macros

// Export to other formats
workBook.SaveAsCsv("file.csv", ",");  // CSV with comma delimiter
workBook.SaveAsJson("file.json");     // JSON format
workBook.SaveAsXml("file.xml");       // XML format

try 
{
    WorkBook workBook = WorkBook.Create();
    WorkSheet workSheet = workBook.CreateWorkSheet("Demo");
    workSheet["A1"].Value = "Sample Data";

    // Check if directory exists
    string directory = @"C:\ExcelFiles\";
    if (!Directory.Exists(directory))
    {
        Directory.CreateDirectory(directory);
    }

    workBook.SaveAs(Path.Combine(directory, "output.xlsx"));
}
catch (Exception ex)
{
    Console.WriteLine($"Error creating Excel file: {ex.Message}");
}

try 
{
    WorkBook workBook = WorkBook.Create();
    WorkSheet workSheet = workBook.CreateWorkSheet("Demo");
    workSheet["A1"].Value = "Sample Data";

    // Check if directory exists
    string directory = @"C:\ExcelFiles\";
    if (!Directory.Exists(directory))
    {
        Directory.CreateDirectory(directory);
    }

    workBook.SaveAs(Path.Combine(directory, "output.xlsx"));
}
catch (Exception ex)
{
    Console.WriteLine($"Error creating Excel file: {ex.Message}");
}

How Do I Add Styling to Excel Files Using IronXL?

Now let's see how to add styles to Excel cells. IronXL provides comprehensive styling options for creating professional-looking spreadsheets.

using IronXL;

namespace WorkingWithIronXL
{
    public class Program
    {
        public static void Main()
        {
            // Create a new workbook
            WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
            // Create a new worksheet
            WorkSheet workSheet = workBook.CreateWorkSheet("StyledSheet");
            // Add multiple values to cells
            workSheet["A1"].Value = "This Styled Text with Awesome IronXL library";
            workSheet["A2"].Value = 999999;
            // Apply styles to cells
            workSheet["A1"].Style.Font.Bold = true;
            workSheet["A1"].Style.Font.Italic = true;
            workSheet["A1"].Style.Font.Height = 14;
            workSheet["A1"].Style.Font.Color = "#FF0000"; // Red color
            workSheet["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Double;
            workSheet["A2"].Style.BottomBorder.SetColor("#00FF00"); // Green color
            // Save the workbook
            workBook.SaveAs("myIronXlWriteDemo.xlsx");
        }
    }
}

using IronXL;

namespace WorkingWithIronXL
{
    public class Program
    {
        public static void Main()
        {
            // Create a new workbook
            WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
            // Create a new worksheet
            WorkSheet workSheet = workBook.CreateWorkSheet("StyledSheet");
            // Add multiple values to cells
            workSheet["A1"].Value = "This Styled Text with Awesome IronXL library";
            workSheet["A2"].Value = 999999;
            // Apply styles to cells
            workSheet["A1"].Style.Font.Bold = true;
            workSheet["A1"].Style.Font.Italic = true;
            workSheet["A1"].Style.Font.Height = 14;
            workSheet["A1"].Style.Font.Color = "#FF0000"; // Red color
            workSheet["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Double;
            workSheet["A2"].Style.BottomBorder.SetColor("#00FF00"); // Green color
            // Save the workbook
            workBook.SaveAs("myIronXlWriteDemo.xlsx");
        }
    }
}

Code Explanation

1. Create an Excel file using WorkBook.Create.
2. Create a worksheet using workBook.CreateWorkSheet.
3. Add values to cells using workSheet["A1"].Value.
4. Add styles to cells using properties like workSheet["A1"].Style.Font.Bold.
5. Save the workbook using workBook.SaveAs.

Output Excel File

// Apply styles to a range
var range = workSheet["A1:D10"];
foreach (var cell in range)
{
    cell.Style.Font.Bold = true;
    cell.Style.Font.Color = "#0000FF"; // Blue
    cell.Style.SetBackgroundColor("#FFFF00"); // Yellow background
}

// Apply styles to a range
var range = workSheet["A1:D10"];
foreach (var cell in range)
{
    cell.Style.Font.Bold = true;
    cell.Style.Font.Color = "#0000FF"; // Blue
    cell.Style.SetBackgroundColor("#FFFF00"); // Yellow background
}

// Add conditional formatting rule
var rule = workSheet.ConditionalFormatting.CreateConditionalFormattingRule(
    ComparisonOperator.GreaterThan, "100");
rule.FontFormatting.Bold = true;
rule.FontFormatting.FontColor = "#FF0000"; // Red for values > 100
workSheet.ConditionalFormatting.AddConditionalFormatting("A1:A10", rule);

// Add conditional formatting rule
var rule = workSheet.ConditionalFormatting.CreateConditionalFormattingRule(
    ComparisonOperator.GreaterThan, "100");
rule.FontFormatting.Bold = true;
rule.FontFormatting.FontColor = "#FF0000"; // Red for values > 100
workSheet.ConditionalFormatting.AddConditionalFormatting("A1:A10", rule);

How Do I Read Values from Excel and Calculate?

Now let's see how to read Excel files using IronXL and perform calculations. IronXL supports various mathematical functions for data analysis.

using IronXL;
using System;

namespace WorkingWithIronXL
{
    internal class IronXlDemo
    {
        public static void ReadData()
        {
            // Load the Excel worksheet
            WorkBook workBook = WorkBook.Load("sampleEmployeeData.xlsx");
            // Select the first worksheet
            WorkSheet workSheet = workBook.WorkSheets[0];
            // Read a specific cell value
            int cellValue = workSheet["A2"].IntValue;
            Console.WriteLine($"Value in A2: {cellValue}");
            // Read a range of cells
            foreach (var cell in workSheet["A1:H10"])
            {
                Console.Write($"{cell.Text}\t");
                if(cell.AddressString.Contains("H"))
                {
                    Console.WriteLine();
                }
            }
            // Calculate aggregate values
            decimal sum = workSheet["F2:F10"].Sum();
            Console.WriteLine($"Sum of F2:F10: {sum}");
        }
    }
}

using IronXL;
using System;

namespace WorkingWithIronXL
{
    internal class IronXlDemo
    {
        public static void ReadData()
        {
            // Load the Excel worksheet
            WorkBook workBook = WorkBook.Load("sampleEmployeeData.xlsx");
            // Select the first worksheet
            WorkSheet workSheet = workBook.WorkSheets[0];
            // Read a specific cell value
            int cellValue = workSheet["A2"].IntValue;
            Console.WriteLine($"Value in A2: {cellValue}");
            // Read a range of cells
            foreach (var cell in workSheet["A1:H10"])
            {
                Console.Write($"{cell.Text}\t");
                if(cell.AddressString.Contains("H"))
                {
                    Console.WriteLine();
                }
            }
            // Calculate aggregate values
            decimal sum = workSheet["F2:F10"].Sum();
            Console.WriteLine($"Sum of F2:F10: {sum}");
        }
    }
}

Code Explanation

1. Load an Excel file using WorkBook.Load.
2. Access the first worksheet in the workbook.
3. Read a specific cell's value and display it using Console.WriteLine.
4. Iterate through a range of cells and print each value. Print new lines after column H.
5. Calculate the sum of values in range F2:F10 and print it.

Input Excel

Output

// Different ways to read cell values
string textValue = workSheet["A1"].StringValue;
int intValue = workSheet["B1"].IntValue;
decimal decimalValue = workSheet["C1"].DecimalValue;
double doubleValue = workSheet["D1"].DoubleValue;
DateTime dateValue = workSheet["E1"].DateTimeValue;
bool boolValue = workSheet["F1"].BoolValue;

// Read formula results
string formulaResult = workSheet["G1"].FormattedCellValue;

// Different ways to read cell values
string textValue = workSheet["A1"].StringValue;
int intValue = workSheet["B1"].IntValue;
decimal decimalValue = workSheet["C1"].DecimalValue;
double doubleValue = workSheet["D1"].DoubleValue;
DateTime dateValue = workSheet["E1"].DateTimeValue;
bool boolValue = workSheet["F1"].BoolValue;

// Read formula results
string formulaResult = workSheet["G1"].FormattedCellValue;

// Check if cell is empty
if (workSheet["A1"].IsEmpty)
{
    Console.WriteLine("Cell A1 is empty");
}

// Use null-coalescing for safe value reading
string value = workSheet["A1"].StringValue ?? "Default Value";

// Check for specific content types
if (workSheet["B1"].IsNumeric)
{
    decimal numValue = workSheet["B1"].DecimalValue;
}

// Check if cell is empty
if (workSheet["A1"].IsEmpty)
{
    Console.WriteLine("Cell A1 is empty");
}

// Use null-coalescing for safe value reading
string value = workSheet["A1"].StringValue ?? "Default Value";

// Check for specific content types
if (workSheet["B1"].IsNumeric)
{
    decimal numValue = workSheet["B1"].DecimalValue;
}

// Available aggregate functions
decimal sum = workSheet["A1:A10"].Sum();
decimal avg = workSheet["A1:A10"].Avg();
decimal min = workSheet["A1:A10"].Min();
decimal max = workSheet["A1:A10"].Max();
int count = workSheet["A1:A10"].Count();

// Available aggregate functions
decimal sum = workSheet["A1:A10"].Sum();
decimal avg = workSheet["A1:A10"].Avg();
decimal min = workSheet["A1:A10"].Min();
decimal max = workSheet["A1:A10"].Max();
int count = workSheet["A1:A10"].Count();

How Do I Convert Excel to HTML for Web Usage?

Let's see how to convert Excel to HTML for web usage. This is particularly useful for displaying Excel data in web applications.

using IronXL;
using IronXL.Options;

namespace WorkingWithIronXL
{
    internal class IronXlDemo
    {
        public static void ConvertToHtml()
        {
            WorkBook workBook = WorkBook.Load("sampleEmployeeData.xlsx");
            var options = new HtmlExportOptions()
            {
                // Set row/column numbers visible in HTML document
                OutputRowNumbers = true,
                OutputColumnHeaders = true,
                // Set hidden rows/columns visible in HTML document
                OutputHiddenRows = true,
                OutputHiddenColumns = true,
                // Set leading spaces as non-breaking
                OutputLeadingSpacesAsNonBreaking = true
            };
            // Export workbook to the HTML file
            workBook.ExportToHtml("workBook.html", options);
        }
    }
}

using IronXL;
using IronXL.Options;

namespace WorkingWithIronXL
{
    internal class IronXlDemo
    {
        public static void ConvertToHtml()
        {
            WorkBook workBook = WorkBook.Load("sampleEmployeeData.xlsx");
            var options = new HtmlExportOptions()
            {
                // Set row/column numbers visible in HTML document
                OutputRowNumbers = true,
                OutputColumnHeaders = true,
                // Set hidden rows/columns visible in HTML document
                OutputHiddenRows = true,
                OutputHiddenColumns = true,
                // Set leading spaces as non-breaking
                OutputLeadingSpacesAsNonBreaking = true
            };
            // Export workbook to the HTML file
            workBook.ExportToHtml("workBook.html", options);
        }
    }
}

Code Explanation

1. Load the Excel file using the Load method.
2. Set options for HTML conversion using HtmlExportOptions.
3. Use ExportToHtml method to convert and save as HTML.

Input

Output

var htmlOptions = new HtmlExportOptions()
{
    // Table formatting options
    OutputRowNumbers = true,
    OutputColumnHeaders = true,

    // Visibility options  
    OutputHiddenRows = false,
    OutputHiddenColumns = false,

    // Formatting preservation
    OutputLeadingSpacesAsNonBreaking = true,

    // Custom CSS class
    TableCssClass = "excel-table"
};

var htmlOptions = new HtmlExportOptions()
{
    // Table formatting options
    OutputRowNumbers = true,
    OutputColumnHeaders = true,

    // Visibility options  
    OutputHiddenRows = false,
    OutputHiddenColumns = false,

    // Formatting preservation
    OutputLeadingSpacesAsNonBreaking = true,

    // Custom CSS class
    TableCssClass = "excel-table"
};

// Export with inline CSS styles
workBook.ExportToHtml("styled.html", new HtmlExportOptions
{
    OutputRowNumbers = false,
    OutputColumnHeaders = true,
    // Preserves cell styles as inline CSS
    PreserveStyles = true
});

// Export with inline CSS styles
workBook.ExportToHtml("styled.html", new HtmlExportOptions
{
    OutputRowNumbers = false,
    OutputColumnHeaders = true,
    // Preserves cell styles as inline CSS
    PreserveStyles = true
});

// Export specific worksheet
WorkSheet sheet = workBook.GetWorkSheet("Sheet1");
sheet.SaveAsHtml("sheet1.html");

// Export multiple specific sheets
foreach (var sheetName in new[] { "Sheet1", "Sheet3" })
{
    workBook.GetWorkSheet(sheetName).SaveAsHtml($"{sheetName}.html");
}

// Export specific worksheet
WorkSheet sheet = workBook.GetWorkSheet("Sheet1");
sheet.SaveAsHtml("sheet1.html");

// Export multiple specific sheets
foreach (var sheetName in new[] { "Sheet1", "Sheet3" })
{
    workBook.GetWorkSheet(sheetName).SaveAsHtml($"{sheetName}.html");
}

What Are the Real-World Use Cases for IronXL?

IronXL is a versatile .NET library with a wide range of real-world applications, including:

1. Business Reporting:

• Automating periodic reports like sales summaries and financial statements.
• Creating custom dashboards from various data sources.
• Exporting data to Excel for stakeholder presentations.

2. Data Analysis:

• Processing large datasets for statistical analysis and transformation.
• Exporting analyzed data for stakeholder examination.
• Using aggregate functions for quick insights.

3. Inventory Management:

• Managing product inventories with stock levels and supplier details.
• Generating inventory reports to track trends.
• Importing CSV data from suppliers.

4. Customer Relationship Management (CRM):

• Exporting customer data for detailed analysis.
• Updating CRM records by importing modified data.
• Creating segmentation reports using conditional formatting.

5. Educational Institutions:

• Creating student grade books and attendance records.
• Generating exam results and performance analysis.
• Using formulas to calculate grades automatically.

6. Financial Services:

• Automating financial models, budgets, and forecasts.
• Consolidating financial data for comprehensive reporting.
• Creating charts for visual analysis.

7. Human Resources:

• Managing employee data including payroll and evaluations.
• Generating reports on HR metrics and demographics.
• Protecting sensitive data with password encryption.

8. Project Management:

• Tracking project timelines and resource allocation.
• Creating Gantt charts and project management tools.
• Using named ranges for formula management.

9. E-commerce:

• Exporting order details, customer information, and sales data from e-commerce platforms to Excel.
• Analyzing sales trends, customer behavior, and product performance.

10. Healthcare:

• Managing patient records and appointment schedules.
• Analyzing healthcare data to improve patient care.
• Securing patient data with worksheet protection.

How Do I License IronXL for Production Use?

IronXL is an enterprise library from Iron Software. It requires a license to run. You can download a trial license using your email ID from here. Once submitted, the license is delivered to your email. Place this license at the beginning of your code, before using IronXL:

License.LicenseKey = "your Key Here";

License.LicenseKey = "your Key Here";

// Method 1: In code
IronXL.License.LicenseKey = "YOUR-LICENSE-KEY";

// Method 2: Using app.config or web.config
// Add to configuration file:
// <appSettings>
//   <add key="IronXL.LicenseKey" value="YOUR-LICENSE-KEY"/>
// </appSettings>

// Method 3: Using environment variable
Environment.SetEnvironmentVariable("IRONXL_LICENSE_KEY", "YOUR-LICENSE-KEY");

// Method 1: In code
IronXL.License.LicenseKey = "YOUR-LICENSE-KEY";

// Method 2: Using app.config or web.config
// Add to configuration file:
// <appSettings>
//   <add key="IronXL.LicenseKey" value="YOUR-LICENSE-KEY"/>
// </appSettings>

// Method 3: Using environment variable
Environment.SetEnvironmentVariable("IRONXL_LICENSE_KEY", "YOUR-LICENSE-KEY");

What Have We Learned About Working with Excel Files in C#?

IronXL is a powerful .NET library for creating, reading, and editing Excel files, offering an intuitive API that simplifies working with Excel documents. It supports multiple formats including XLS, XLSX, and CSV, making it versatile for various use cases. IronXL allows easy manipulation of cell values, formulas, and formatting, and is optimized for performance, efficiently handling large files and complex operations. Its memory management ensures responsive applications, and its cross-platform compatibility makes it valuable for developers on different operating systems.

Whether you're building web applications, desktop software, or cloud services, IronXL provides the tools needed to work effectively with Excel files in C#. For more advanced features, check out the complete documentation and explore the extensive code examples.

Key takeaways:

• No Microsoft Office installation required
• Cross-platform support for Windows, Linux, macOS
• Comprehensive styling and formatting options
• Support for formulas and calculations
• Security features including password protection
• Easy data export and import
• Excellent performance optimizations

Stay updated with the latest features and improvements by checking the changelog and milestones for IronXL's continuous enhancements.