C#에서 xlsx 파일과 DataSet/DataTable 가져오기 및 내보내기 방법

using IronXL;
using System.Data;

// Create dataset
DataSet dataSet = new DataSet();

// Create workbook
WorkBook workBook = WorkBook.Create();

// Load DataSet to workBook
WorkBook.LoadWorkSheetsFromDataSet(dataSet, workBook);

Imports IronXL
Imports System.Data

' Create dataset
Private dataSet As New DataSet()

' Create workbook
Private workBook As WorkBook = WorkBook.Create()

' Load DataSet to workBook
WorkBook.LoadWorkSheetsFromDataSet(dataSet, workBook)

using IronXL;
using System.Data;

// Create a DataSet with multiple tables
DataSet salesData = new DataSet("CompanySales");

// Create and populate a products table
DataTable productsTable = new DataTable("Products");
productsTable.Columns.Add("ProductID", typeof(int));
productsTable.Columns.Add("ProductName", typeof(string));
productsTable.Columns.Add("Price", typeof(decimal));

// Add sample data
productsTable.Rows.Add(1, "Laptop", 999.99m);
productsTable.Rows.Add(2, "Mouse", 19.99m);
productsTable.Rows.Add(3, "Keyboard", 49.99m);

// Create and populate a sales table
DataTable salesTable = new DataTable("Sales");
salesTable.Columns.Add("SaleID", typeof(int));
salesTable.Columns.Add("ProductID", typeof(int));
salesTable.Columns.Add("Quantity", typeof(int));
salesTable.Columns.Add("Date", typeof(DateTime));

// Add sample sales data
salesTable.Rows.Add(1, 1, 5, DateTime.Now);
salesTable.Rows.Add(2, 2, 25, DateTime.Now.AddDays(-1));
salesTable.Rows.Add(3, 3, 10, DateTime.Now.AddDays(-2));

// Add tables to DataSet
salesData.Tables.Add(productsTable);
salesData.Tables.Add(salesTable);

// Create workbook and load DataSet
WorkBook workBook = WorkBook.Create();
WorkBook.LoadWorkSheetsFromDataSet(salesData, workBook);

// Save the workbook with all imported data
workBook.SaveAs("SalesReport.xlsx");

Imports IronXL
Imports System.Data

' Create a DataSet with multiple tables
Dim salesData As New DataSet("CompanySales")

' Create and populate a products table
Dim productsTable As New DataTable("Products")
productsTable.Columns.Add("ProductID", GetType(Integer))
productsTable.Columns.Add("ProductName", GetType(String))
productsTable.Columns.Add("Price", GetType(Decimal))

' Add sample data
productsTable.Rows.Add(1, "Laptop", 999.99D)
productsTable.Rows.Add(2, "Mouse", 19.99D)
productsTable.Rows.Add(3, "Keyboard", 49.99D)

' Create and populate a sales table
Dim salesTable As New DataTable("Sales")
salesTable.Columns.Add("SaleID", GetType(Integer))
salesTable.Columns.Add("ProductID", GetType(Integer))
salesTable.Columns.Add("Quantity", GetType(Integer))
salesTable.Columns.Add("Date", GetType(DateTime))

' Add sample sales data
salesTable.Rows.Add(1, 1, 5, DateTime.Now)
salesTable.Rows.Add(2, 2, 25, DateTime.Now.AddDays(-1))
salesTable.Rows.Add(3, 3, 10, DateTime.Now.AddDays(-2))

' Add tables to DataSet
salesData.Tables.Add(productsTable)
salesData.Tables.Add(salesTable)

' Create workbook and load DataSet
Dim workBook As WorkBook = WorkBook.Create()
WorkBook.LoadWorkSheetsFromDataSet(salesData, workBook)

' Save the workbook with all imported data
workBook.SaveAs("SalesReport.xlsx")

// 이 코드 조각은 사용할 수 없습니다!

' 이 코드 조각은 사용할 수 없습니다!

using IronXL;
using System;
using System.Data;

// Load an existing Excel file
WorkBook workBook = WorkBook.Load("FinancialData.xlsx");

// Export to DataSet with column headers from first row
DataSet financialDataSet = workBook.ToDataSet(useFirstRowAsColumnNames: true);

// Process each DataTable in the DataSet
foreach (DataTable table in financialDataSet.Tables)
{
    Console.WriteLine($"Processing table: {table.TableName}");
    Console.WriteLine($"Columns: {table.Columns.Count}, Rows: {table.Rows.Count}");

    // Iterate through columns
    foreach (DataColumn column in table.Columns)
    {
        Console.WriteLine($"  Column: {column.ColumnName} ({column.DataType})");
    }

    // Process first 5 rows as example
    int rowCount = 0;
    foreach (DataRow row in table.Rows)
    {
        if (rowCount++ >= 5) break;

        // Access data by column name
        foreach (DataColumn col in table.Columns)
        {
            Console.WriteLine($"    {col.ColumnName}: {row[col]}");
        }
    }
}

// You can also export specific worksheets as DataTables
WorkSheet specificSheet = workBook.WorkSheets["Q1Sales"];
DataTable q1Data = specificSheet.ToDataTable(useFirstRowAsColumnNames: true);

// Use the DataTable with other .NET components
// For example, bind to a DataGridView or save to database

Imports IronXL
Imports System
Imports System.Data

' Load an existing Excel file
Dim workBook As WorkBook = WorkBook.Load("FinancialData.xlsx")

' Export to DataSet with column headers from first row
Dim financialDataSet As DataSet = workBook.ToDataSet(useFirstRowAsColumnNames:=True)

' Process each DataTable in the DataSet
For Each table As DataTable In financialDataSet.Tables
    Console.WriteLine($"Processing table: {table.TableName}")
    Console.WriteLine($"Columns: {table.Columns.Count}, Rows: {table.Rows.Count}")

    ' Iterate through columns
    For Each column As DataColumn In table.Columns
        Console.WriteLine($"  Column: {column.ColumnName} ({column.DataType})")
    Next

    ' Process first 5 rows as example
    Dim rowCount As Integer = 0
    For Each row As DataRow In table.Rows
        If rowCount >= 5 Then Exit For
        rowCount += 1

        ' Access data by column name
        For Each col As DataColumn In table.Columns
            Console.WriteLine($"    {col.ColumnName}: {row(col)}")
        Next
    Next
Next

' You can also export specific worksheets as DataTables
Dim specificSheet As WorkSheet = workBook.WorkSheets("Q1Sales")
Dim q1Data As DataTable = specificSheet.ToDataTable(useFirstRowAsColumnNames:=True)

' Use the DataTable with other .NET components
' For example, bind to a DataGridView or save to database