IronXL与Epplus的比较

WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);

Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)

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 cols 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 colum names
            row = 1;
            col = 0;
            foreach (DataColumn dc in dt.Columns)
            {
                col++;
                ws.SetValue(row, col, dc.ColumnName);
            }

            // Insert the DataTable rows to 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 cols 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 colum 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 to 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
anchor-load-a-workbook
**/
var workbook = WorkBook.Load(@"Spreadsheets\\GDP.xlsx");

'''
'''Load WorkBook
'''anchor-load-a-workbook
'''*
Dim workbook = WorkBook.Load("Spreadsheets\\GDP.xlsx")

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

Dim worksheet = workbook.GetWorkSheet("GDPByCountry")

/**
Create WorkBook
anchor-create-a-workbook
**/
var workbook = new WorkBook(ExcelFileFormat.XLSX);

'''
'''Create WorkBook
'''anchor-create-a-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
anchor-edit-cell-values-within-a-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
'''anchor-edit-cell-values-within-a-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
anchor-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
'''anchor-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
anchor-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
'''anchor-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
anchor-download-data-from-an-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
'''anchor-download-data-from-an-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
anchor-create-a-new-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
'''anchor-create-a-new-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>