IronXL 和 Epplus 的比較

// Create a new Excel workbook in XLSX format
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);

' Create a new Excel workbook in XLSX format
Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)

// Create a worksheet named "2020 Budget" in the workbook
var sheet = workbook.CreateWorkSheet("2020 Budget");

' Create a worksheet named "2020 Budget" in the workbook
Dim sheet = workbook.CreateWorkSheet("2020 Budget")

public ActionResult ConvertToXLSX()
{
    byte[] fileData = null;

    // Replace the GetDataTable() method with your DBMS-fetching code.
    using (DataTable dt = GetDataTable())
    {
        // Create an empty spreadsheet
        using (var p = new ExcelPackage())
        {
            // Add a worksheet to the spreadsheet
            ExcelWorksheet ws = p.Workbook.Worksheets.Add(dt.TableName);

            // Initialize rows and columns counter: note that they are 1-based!
            var row = 1;
            var col = 1;

            // Create the column names on the first line.
            // In this sample, we'll just use the DataTable column names
            row = 1;
            col = 0;
            foreach (DataColumn dc in dt.Columns)
            {
                col++;
                ws.SetValue(row, col, dc.ColumnName);
            }

            // Insert the DataTable rows into the XLS file
            foreach (DataRow r in dt.Rows)
            {
                row++;
                col = 0;
                foreach (DataColumn dc in dt.Columns)
                {
                    col++;
                    ws.SetValue(row, col, r[dc].ToString());
                }

                // Alternate light-gray color for uneven rows (3, 5, 7, 9)...
                if (row % 2 != 0)
                {
                    ws.Row(row).Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    ws.Row(row).Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
                }
            }

            // Output the XLSX file
            using (var ms = new MemoryStream())
            {
                p.SaveAs(ms);
                ms.Seek(0, SeekOrigin.Begin);
                fileData = ms.ToArray();
            }
        }
    }

    string fileName = "ConvertedFile.xlsx";
    string contentType = System.Web.MimeMapping.GetMimeMapping(fileName);
    Response.AppendHeader("Content-Disposition", String.Format("attachment;filename={0}", fileName));
    return File(fileData, contentType);
}

Public Function ConvertToXLSX() As ActionResult
	Dim fileData() As Byte = Nothing

	' Replace the GetDataTable() method with your DBMS-fetching code.
	Using dt As DataTable = GetDataTable()
		' Create an empty spreadsheet
		Using p = New ExcelPackage()
			' Add a worksheet to the spreadsheet
			Dim ws As ExcelWorksheet = p.Workbook.Worksheets.Add(dt.TableName)

			' Initialize rows and columns counter: note that they are 1-based!
			Dim row = 1
			Dim col = 1

			' Create the column names on the first line.
			' In this sample, we'll just use the DataTable column names
			row = 1
			col = 0
			For Each dc As DataColumn In dt.Columns
				col += 1
				ws.SetValue(row, col, dc.ColumnName)
			Next dc

			' Insert the DataTable rows into the XLS file
			For Each r As DataRow In dt.Rows
				row += 1
				col = 0
				For Each dc As DataColumn In dt.Columns
					col += 1
					ws.SetValue(row, col, r(dc).ToString())
				Next dc

				' Alternate light-gray color for uneven rows (3, 5, 7, 9)...
				If row Mod 2 <> 0 Then
					ws.Row(row).Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid
					ws.Row(row).Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray)
				End If
			Next r

			' Output the XLSX file
			Using ms = New MemoryStream()
				p.SaveAs(ms)
				ms.Seek(0, SeekOrigin.Begin)
				fileData = ms.ToArray()
			End Using
		End Using
	End Using

	Dim fileName As String = "ConvertedFile.xlsx"
	Dim contentType As String = System.Web.MimeMapping.GetMimeMapping(fileName)
	Response.AppendHeader("Content-Disposition", String.Format("attachment;filename={0}", fileName))
	Return File(fileData, contentType)
End Function

using OfficeOpenXml;
using System;
using System.IO;

namespace ReadExcelInCsharp
{
    class Program
    {
        static void Main(string[] args)
        {
            // Provide file path
            FileInfo existingFile = new FileInfo(@"D:\sample_XLSX.xlsx");
            // Use EPPlus
            using (ExcelPackage package = new ExcelPackage(existingFile))
            {
                // Get the first worksheet in the workbook
                ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
                int colCount = worksheet.Dimension.End.Column;  // Get Column Count
                int rowCount = worksheet.Dimension.End.Row;     // Get row count
                for (int row = 1; row <= rowCount; row++)
                {
                    for (int col = 1; col <= colCount; col++)
                    {
                        // Print data, based on row and columns position
                        Console.WriteLine("Row:" + row + " Column:" + col + " Value:" + worksheet.Cells[row, col].Value?.ToString().Trim());
                    }
                }
            }
        }
    }
}

Imports OfficeOpenXml
Imports System
Imports System.IO

Namespace ReadExcelInCsharp
	Friend Class Program
		Shared Sub Main(ByVal args() As String)
			' Provide file path
			Dim existingFile As New FileInfo("D:\sample_XLSX.xlsx")
			' Use EPPlus
			Using package As New ExcelPackage(existingFile)
				' Get the first worksheet in the workbook
				Dim worksheet As ExcelWorksheet = package.Workbook.Worksheets(1)
				Dim colCount As Integer = worksheet.Dimension.End.Column ' Get Column Count
				Dim rowCount As Integer = worksheet.Dimension.End.Row ' Get row count
				For row As Integer = 1 To rowCount
					For col As Integer = 1 To colCount
						' Print data, based on row and columns position
						Console.WriteLine("Row:" & row & " Column:" & col & " Value:" & worksheet.Cells(row, col).Value?.ToString().Trim())
					Next col
				Next row
			End Using
		End Sub
	End Class
End Namespace

List<UserDetails> persons = new List<UserDetails>()
{
    new UserDetails() {ID="9999", Name="ABCD", City ="City1", Country="USA"},
    new UserDetails() {ID="8888", Name="PQRS", City ="City2", Country="INDIA"},
    new UserDetails() {ID="7777", Name="XYZZ", City ="City3", Country="CHINA"},
    new UserDetails() {ID="6666", Name="LMNO", City ="City4", Country="UK"},
};

Dim persons As New List(Of UserDetails)() From {
	New UserDetails() With {
		.ID="9999",
		.Name="ABCD",
		.City ="City1",
		.Country="USA"
	},
	New UserDetails() With {
		.ID="8888",
		.Name="PQRS",
		.City ="City2",
		.Country="INDIA"
	},
	New UserDetails() With {
		.ID="7777",
		.Name="XYZZ",
		.City ="City3",
		.Country="CHINA"
	},
	New UserDetails() With {
		.ID="6666",
		.Name="LMNO",
		.City ="City4",
		.Country="UK"
	}
}

private static void WriteToExcel(string path)
{
    // Let use below test data for writing it to excel
    List<UserDetails> persons = new List<UserDetails>()
    {
        new UserDetails() {ID="9999", Name="ABCD", City ="City1", Country="USA"},
        new UserDetails() {ID="8888", Name="PQRS", City ="City2", Country="INDIA"},
        new UserDetails() {ID="7777", Name="XYZZ", City ="City3", Country="CHINA"},
        new UserDetails() {ID="6666", Name="LMNO", City ="City4", Country="UK"},
    };

    // Let's convert our object data to Datatable for a simplified logic.
    // Datatable is the easiest way to deal with complex datatypes for easy reading and formatting. 
    DataTable table = (DataTable)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(persons), (typeof(DataTable)));
    FileInfo filePath = new FileInfo(path);
    using (var excelPack = new ExcelPackage(filePath))
    {
        var ws = excelPack.Workbook.Worksheets.Add("WriteTest");
        ws.Cells.LoadFromDataTable(table, true, OfficeOpenXml.Table.TableStyles.Light8);
        excelPack.Save();
    }
}

Private Shared Sub WriteToExcel(ByVal path As String)
	' Let use below test data for writing it to excel
	Dim persons As New List(Of UserDetails)() From {
		New UserDetails() With {
			.ID="9999",
			.Name="ABCD",
			.City ="City1",
			.Country="USA"
		},
		New UserDetails() With {
			.ID="8888",
			.Name="PQRS",
			.City ="City2",
			.Country="INDIA"
		},
		New UserDetails() With {
			.ID="7777",
			.Name="XYZZ",
			.City ="City3",
			.Country="CHINA"
		},
		New UserDetails() With {
			.ID="6666",
			.Name="LMNO",
			.City ="City4",
			.Country="UK"
		}
	}

	' Let's convert our object data to Datatable for a simplified logic.
	' Datatable is the easiest way to deal with complex datatypes for easy reading and formatting. 
	Dim table As DataTable = CType(JsonConvert.DeserializeObject(JsonConvert.SerializeObject(persons), (GetType(DataTable))), DataTable)
	Dim filePath As New FileInfo(path)
	Using excelPack = New ExcelPackage(filePath)
		Dim ws = excelPack.Workbook.Worksheets.Add("WriteTest")
		ws.Cells.LoadFromDataTable(table, True, OfficeOpenXml.Table.TableStyles.Light8)
		excelPack.Save()
	End Using
End Sub

/**
 Load WorkBook
 **/
var workbook = WorkBook.Load(@"Spreadsheets\\GDP.xlsx");

'''
''' Load WorkBook
''' *
Dim workbook = WorkBook.Load("Spreadsheets\\GDP.xlsx")

var worksheet = workbook.GetWorkSheet("GDPByCountry");

Dim worksheet = workbook.GetWorkSheet("GDPByCountry")

/**
 Create WorkBook
 **/
var workbook = new WorkBook(ExcelFileFormat.XLSX);

'''
''' Create WorkBook
''' *
Dim workbook As New WorkBook(ExcelFileFormat.XLSX)

var worksheet = workbook.CreateWorkSheet("Countries");

Dim worksheet = workbook.CreateWorkSheet("Countries")

var range = worksheet["D2:D101"];

Dim range = worksheet("D2:D101")

/**
 Edit Cell Values in Range
 **/
 // Iterate through the rows
for (var y = 2; y <= 101; y++)
{
    var result = new PersonValidationResult { Row = y };
    results.Add(result);

    // Get all cells for the person
    var cells = worksheet[$"A{y}:E{y}"].ToList();

    // Validate the phone number (1 = B)
    var phoneNumber = cells[1].Value;
    result.PhoneNumberErrorMessage = ValidatePhoneNumber(phoneNumberUtil, (string)phoneNumber);

    // Validate the email address (3 = D)
    result.EmailErrorMessage = ValidateEmailAddress((string)cells[3].Value);

    // Get the raw date in the format of Month Day [suffix], Year (4 = E)
    var rawDate = (string) cells[4].Value;
    result.DateErrorMessage = ValidateDate(rawDate);
}

'''
''' Edit Cell Values in Range
''' *
 ' Iterate through the rows
For y = 2 To 101
	Dim result = New PersonValidationResult With {.Row = y}
	results.Add(result)

	' Get all cells for the person
	Dim cells = worksheet($"A{y}:E{y}").ToList()

	' Validate the phone number (1 = B)
	Dim phoneNumber = cells(1).Value
	result.PhoneNumberErrorMessage = ValidatePhoneNumber(phoneNumberUtil, CStr(phoneNumber))

	' Validate the email address (3 = D)
	result.EmailErrorMessage = ValidateEmailAddress(CStr(cells(3).Value))

	' Get the raw date in the format of Month Day [suffix], Year (4 = E)
	Dim rawDate = CStr(cells(4).Value)
	result.DateErrorMessage = ValidateDate(rawDate)
Next y

/**
 Validate Spreadsheet Data
 **/
 // Iterate through the rows
for (var i = 2; i <= 101; i++)
{
    var result = new PersonValidationResult { Row = i };
    results.Add(result);

    // Get all cells for the person
    var cells = worksheet[$"A{i}:E{i}"].ToList();

    // Validate the phone number (1 = B)
    var phoneNumber = cells[1].Value;
    result.PhoneNumberErrorMessage = ValidatePhoneNumber(phoneNumberUtil, (string)phoneNumber);

    // Validate the email address (3 = D)
    result.EmailErrorMessage = ValidateEmailAddress((string)cells[3].Value);

    // Get the raw date in the format of Month Day [suffix], Year (4 = E)
    var rawDate = (string)cells[4].Value;
    result.DateErrorMessage = ValidateDate(rawDate);
}

'''
''' Validate Spreadsheet Data
''' *
 ' Iterate through the rows
For i = 2 To 101
	Dim result = New PersonValidationResult With {.Row = i}
	results.Add(result)

	' Get all cells for the person
	Dim cells = worksheet($"A{i}:E{i}").ToList()

	' Validate the phone number (1 = B)
	Dim phoneNumber = cells(1).Value
	result.PhoneNumberErrorMessage = ValidatePhoneNumber(phoneNumberUtil, CStr(phoneNumber))

	' Validate the email address (3 = D)
	result.EmailErrorMessage = ValidateEmailAddress(CStr(cells(3).Value))

	' Get the raw date in the format of Month Day [suffix], Year (4 = E)
	Dim rawDate = CStr(cells(4).Value)
	result.DateErrorMessage = ValidateDate(rawDate)
Next i

var resultsSheet = workbook.CreateWorkSheet("Results");

resultsSheet["A1"].Value = "Row";
resultsSheet["B1"].Value = "Valid";
resultsSheet["C1"].Value = "Phone Error";
resultsSheet["D1"].Value = "Email Error";
resultsSheet["E1"].Value = "Date Error";

for (var i = 0; i < results.Count; i++)
{
    var result = results[i];
    resultsSheet[$"A{i + 2}"].Value = result.Row;
    resultsSheet[$"B{i + 2}"].Value = result.IsValid ? "Yes" : "No";
    resultsSheet[$"C{i + 2}"].Value = result.PhoneNumberErrorMessage;
    resultsSheet[$"D{i + 2}"].Value = result.EmailErrorMessage;
    resultsSheet[$"E{i + 2}"].Value = result.DateErrorMessage;
}

workbook.SaveAs(@"Spreadsheets\\PeopleValidated.xlsx");

Dim resultsSheet = workbook.CreateWorkSheet("Results")

resultsSheet("A1").Value = "Row"
resultsSheet("B1").Value = "Valid"
resultsSheet("C1").Value = "Phone Error"
resultsSheet("D1").Value = "Email Error"
resultsSheet("E1").Value = "Date Error"

For i = 0 To results.Count - 1
	Dim result = results(i)
	resultsSheet($"A{i + 2}").Value = result.Row
	resultsSheet($"B{i + 2}").Value = If(result.IsValid, "Yes", "No")
	resultsSheet($"C{i + 2}").Value = result.PhoneNumberErrorMessage
	resultsSheet($"D{i + 2}").Value = result.EmailErrorMessage
	resultsSheet($"E{i + 2}").Value = result.DateErrorMessage
Next i

workbook.SaveAs("Spreadsheets\\PeopleValidated.xlsx")

public class Country
{
    [Key]
    public Guid Key { get; set; }
    public string Name { get; set; }
    public decimal GDP { get; set; }
}

Public Class Country
	<Key>
	Public Property Key() As Guid
	Public Property Name() As String
	Public Property GDP() As Decimal
End Class

/**
 Export Data using Entity Framework
 **/
public class CountryContext : DbContext
{
    public DbSet<Country> Countries { get; set; }

    public CountryContext()
    {
        // TODO: Make async
        Database.EnsureCreated();
    }

    /// <summary>
    /// Configure context to use Sqlite
    /// </summary>
    /// <param name="optionsBuilder"></param>
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var connection = new SqliteConnection($"Data Source=Country.db");
        connection.Open();

        var command = connection.CreateCommand();

        // Create the database if it doesn't already exist
        command.CommandText = $"PRAGMA foreign_keys = ON;";
        command.ExecuteNonQuery();

        optionsBuilder.UseSqlite(connection);

        base.OnConfiguring(optionsBuilder);
    }
}

'''
''' Export Data using Entity Framework
''' *
Public Class CountryContext
	Inherits DbContext

	Public Property Countries() As DbSet(Of Country)

	Public Sub New()
		' TODO: Make async
		Database.EnsureCreated()
	End Sub

	''' <summary>
	''' Configure context to use Sqlite
	''' </summary>
	''' <param name="optionsBuilder"></param>
	Protected Overrides Sub OnConfiguring(ByVal optionsBuilder As DbContextOptionsBuilder)
		Dim connection = New SqliteConnection($"Data Source=Country.db")
		connection.Open()

		Dim command = connection.CreateCommand()

		' Create the database if it doesn't already exist
		command.CommandText = $"PRAGMA foreign_keys = ON;"
		command.ExecuteNonQuery()

		optionsBuilder.UseSqlite(connection)

		MyBase.OnConfiguring(optionsBuilder)
	End Sub
End Class

public async Task ProcessAsync()
{
    // Get the first worksheet
    var workbook = WorkBook.Load(@"Spreadsheets\\GDP.xlsx");
    var worksheet = workbook.GetWorkSheet("GDPByCountry");

    // Create the database connection
    using (var countryContext = new CountryContext())
    {
        // Iterate through all the cells
        for (var i = 2; i <= 213; i++)
        {
            // Get the range from A-B
            var range = worksheet[$"A{i}:B{i}"].ToList();

            // Create a Country entity to be saved to the database
            var country = new Country
            {
                Name = (string)range[0].Value,
                GDP = (decimal)(double)range[1].Value
            };

            // Add the entity
            await countryContext.Countries.AddAsync(country);
        }

        // Commit changes to the database
        await countryContext.SaveChangesAsync();
    }
}

Public Async Function ProcessAsync() As Task
	' Get the first worksheet
	Dim workbook = WorkBook.Load("Spreadsheets\\GDP.xlsx")
	Dim worksheet = workbook.GetWorkSheet("GDPByCountry")

	' Create the database connection
	Using countryContext As New CountryContext()
		' Iterate through all the cells
		For i = 2 To 213
			' Get the range from A-B
			Dim range = worksheet($"A{i}:B{i}").ToList()

			' Create a Country entity to be saved to the database
			Dim country As New Country With {
				.Name = CStr(range(0).Value),
				.GDP = CDec(CDbl(range(1).Value))
			}

			' Add the entity
			Await countryContext.Countries.AddAsync(country)
		Next i

		' Commit changes to the database
		Await countryContext.SaveChangesAsync()
	End Using
End Function

// Iterate through all rows with a value
for (var y = 2; y < i; y++)
{
    // Get the C cell
    var cell = sheet[$"C{y}"].First();

    // Set the formula for the Percentage of Total column
    cell.Formula = $"=B{y}/B{i}";
}

' Iterate through all rows with a value
Dim y = 2
Do While y < i
	' Get the C cell
	Dim cell = sheet($"C{y}").First()

	' Set the formula for the Percentage of Total column
	cell.Formula = $"=B{y}/B{i}"
	y += 1
Loop

/**
 Data API to Spreadsheet
 **/
var client = new Client(new Uri("https://restcountries.eu/rest/v2/"));
List<RestCountry> countries = await client.GetAsync<List<RestCountry>>();

'''
''' Data API to Spreadsheet
''' *
Dim client As New Client(New Uri("https://restcountries.eu/rest/v2/"))
Dim countries As List(Of RestCountry) = Await client.GetAsync(Of List(Of RestCountry))()

for (var i = 2; i < countries.Count; i++)
{
    var country = countries[i];

    // Set the basic values
    worksheet[$"A{i}"].Value = country.name;
    worksheet[$"B{i}"].Value = country.population;
    worksheet[$"G{i}"].Value = country.region;
    worksheet[$"H{i}"].Value = country.numericCode;

    // Iterate through languages
    for (var x = 0; x < 3; x++)
    {
        if (x > (country.languages.Count - 1)) break;

        var language = country.languages[x];

        // Get the letter for the column
        var columnLetter = GetColumnLetter(4 + x);

        // Set the language name
        worksheet[$"{columnLetter}{i}"].Value = language.name;
    }
}

For i = 2 To countries.Count - 1
	Dim country = countries(i)

	' Set the basic values
	worksheet($"A{i}").Value = country.name
	worksheet($"B{i}").Value = country.population
	worksheet($"G{i}").Value = country.region
	worksheet($"H{i}").Value = country.numericCode

	' Iterate through languages
	For x = 0 To 2
		If x > (country.languages.Count - 1) Then
			Exit For
		End If

		Dim language = country.languages(x)

		' Get the letter for the column
		Dim columnLetter = GetColumnLetter(4 + x)

		' Set the language name
		worksheet($"{columnLetter}{i}").Value = language.name
	Next x
Next i

static void Main(string[] args)
{
    var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\HelloWorld.xlsx");
    var sheet = workbook.WorkSheets.First();
    var cell = sheet["A1"].StringValue;
    Console.WriteLine(cell);
}

Shared Sub Main(ByVal args() As String)
	Dim workbook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\HelloWorld.xlsx")
	Dim sheet = workbook.WorkSheets.First()
	Dim cell = sheet("A1").StringValue
	Console.WriteLine(cell)
End Sub

/**
 Create Excel File
 **/
static void Main(string[] args)
{
    var newXLFile = WorkBook.Create(ExcelFileFormat.XLSX);
    newXLFile.Metadata.Title = "IronXL New File";
    var newWorkSheet = newXLFile.CreateWorkSheet("1stWorkSheet");
    newWorkSheet["A1"].Value = "Hello World";
    newWorkSheet["A2"].Style.BottomBorder.SetColor("#ff6600");
    newWorkSheet["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Dashed;
}

'''
''' Create Excel File
''' *
Shared Sub Main(ByVal args() As String)
	Dim newXLFile = WorkBook.Create(ExcelFileFormat.XLSX)
	newXLFile.Metadata.Title = "IronXL New File"
	Dim newWorkSheet = newXLFile.CreateWorkSheet("1stWorkSheet")
	newWorkSheet("A1").Value = "Hello World"
	newWorkSheet("A2").Style.BottomBorder.SetColor("#ff6600")
	newWorkSheet("A2").Style.BottomBorder.Type = IronXL.Styles.BorderType.Dashed
End Sub

newXLFile.SaveAsXml($@"{Directory.GetCurrentDirectory()}\Files\HelloWorldXML.XML");

newXLFile.SaveAsXml($"{Directory.GetCurrentDirectory()}\Files\HelloWorldXML.XML")

<?xml version="1.0" standalone="yes"?>
<_x0031_stWorkSheet>
  <_x0031_stWorkSheet>
    <Column1 xsi:type="xs:string" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">Hello World</Column1>
  </_x0031_stWorkSheet>
  <_x0031_stWorkSheet>
    <Column1 xsi:type="xs:string" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
  </_x0031_stWorkSheet>
</_x0031_stWorkSheet>