Excel 파일을 읽고 DataTable 및 데이터베이스로 데이터를 내보내는 C#

Install-Package Spire.XLS

using Spire.Xls;

Workbook wb = new Workbook();
wb.LoadFromFile("input.xlsx");

Worksheet sheet = wb.Worksheets[0]; //First sheet

CellRange cell = sheet.Range[1, 1]; // Row1, Column 1 (A1)
string value = cell.Value;

using Spire.Xls;

namespace ReadExcelData
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook wb = new Workbook();

            // Load an existing Excel file
            wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

            // Get the first worksheet
            Worksheet sheet = wb.Worksheets[0];

            // Get the cell range containing data
            CellRange locatedRange = sheet.AllocatedRange;

            // Iterate through the rows
            for (int i = 0; i < locatedRange.Rows.Length; i++)
            {
                // Iterate through the columns
                for (int j = 0; j < locatedRange.Rows[i].ColumnCount; j++)
                {
                    // Get data of a specific cell
                    string cellValue = locatedRange[i + 1, j + 1].Value?.ToString() ?? "N/A";

                    // Align output with a width of 22
                    Console.Write($"{cellValue,-22}");
                }
                Console.WriteLine();
            }
        }
    }
}

using Spire.Xls;

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        // Code will go here
    }
}

Workbook wb = new Workbook();
wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

DataTable dataTable = wb.Worksheets[0].ExportDataTable();

dataGridView1.DataSource = dataTable;

using Spire.Xls;
using System.Data;

namespace ReadExcelIntoDataTable
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            // Create a Workbook object
            Workbook wb = new Workbook();

            // Load an existing Excel file
            wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

            // Get the first worksheet
            Worksheet sheet = wb.Worksheets[0];

            // Export data from worksheet into a DataTable
            DataTable dataTable = sheet.ExportDataTable();

            // Bind DataTable to DataGridView
            dataGridView1.DataSource = dataTable;

            // Dispose resources
            wb.Dispose();
        }
    }
}

Install-Package MySql.Data

using Spire.Xls;
using MySql.Data.MySqlClient;

// Create a Workbook object
Workbook wb = new Workbook();

// Load an Excel document
wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

// Get a specific sheet
Worksheet sheet = wb.Worksheets[0];

// Retrieve headers
List<string> headers = new List<string>();
for (int col = 1; col <= sheet.LastColumn; col++)
{
    string header = sheet.Range[1, col].Value?.ToString();
    // Removing spaces to avoid conflicts with MySQL column names
    string cleanHeader = header?.Replace(" ", "");
    headers.Add($"`{cleanHeader}`");
}

// Retrieve data
List<List<string>> data = new List<List<string>>();
for (int row = 2; row <= sheet.LastRow; row++) {
    List<string> record = new List<string>();
    for (int col = 1; col <= sheet.LastColumn; col++)
    {
        record.Add(sheet.Range[row, col].Value?.ToString() ?? string.Empty);
    }
    data.Add(record);
}

string connectionString = "server=localhost;user=root;password=yourpassword;database=yourdatabase;";
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
    connection.Open();
    // Connection is established; perform database operations here
}

// Create a table with dynamic columns based on headers
List<string> columns = new List<string>();
foreach (string header in headers)
{
    // Assuming all header values are VARCHAR for simplicity; adjust types as needed
    columns.Add($"{header} VARCHAR(255)");
}

// Create a table in database
string columnsSql = string.Join(", ", columns);
string createTableQuery = $ @"
    CREATE TABLE IF NOT EXISTS my_table (
        id INT AUTO_INCREMENT PRIMARY KEY,
        {columnsSql}
)";

// Execute the create table query
using (MySqlCommand createCommand = new MySqlCommand(createTableQuery, connection))
{
    createCommand.ExecuteNonQuery();
}

// Prepare the SQL INSERT statement
string placeholders = string.Join(", ", new string[headers.Count].Select(h => "?"));
string insertQuery = $"INSERT INTO my_table ({string.Join(", ", headers.Select(h => h.Trim('`')))}) VALUES ({placeholders})";

// Insert data into the table
foreach (List<string> record in data)
{
    using (MySqlCommand insertCommand = new MySqlCommand(insertQuery, connection))
    {
        for (int i = 0; i < record.Count; i++)
        {
            insertCommand.Parameters.AddWithValue($" @2024\本地文件打包__20180302\Spire.Web\trunk\src\website\components\com_virtuemart\themes\default\templates\browse\includes\browse_searchparameter_form.tpl.php{i}", record[i]);
        }
        insertCommand.ExecuteNonQuery();
    }
}

using Spire.Xls;
using MySql.Data.MySqlClient;

namespace ExcelToMySQL
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook wb = new Workbook();

            // Load an Excel document
            wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");

            // Get a specific sheet
            Worksheet sheet = wb.Worksheets[0];

            // Retrieve headers
            List<string> headers = new List<string>();
            for (int col = 1; col <= sheet.LastColumn; col++)
            {
                string header = sheet.Range[1, col].Value?.ToString();
                // Removing spaces to avoid conflicts with MySQL column names
                string cleanHeader = header?.Replace(" ", "");
                headers.Add($"`{cleanHeader}`");
            }

            // Retrieve data
            List<List<string>> data = new List<List<string>>();
            for (int row = 2; row <= sheet.LastRow; row++)
            {
                List<string> record = new List<string>();
                for (int col = 1; col <= sheet.LastColumn; col++)
                {
                    record.Add(sheet.Range[row, col].Value?.ToString() ?? string.Empty);
                }
                data.Add(record);
            }

            // Establish a connection to the MySQL database
            string connectionString = "server=localhost;user=root;password=admin;database=excel_db;";
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();

                // Create a table with dynamic columns based on headers
                List<string> columns = new List<string>();
                foreach (string header in headers)
                {
                    // Assuming all header values are VARCHAR for simplicity; adjust types as needed
                    columns.Add($"{header} VARCHAR(255)");
                }

                // Create a table in database
                string columnsSql = string.Join(", ", columns);
                string createTableQuery = $ @"
                    CREATE TABLE IF NOT EXISTS my_table (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        {columnsSql}
                    )";

                // Execute the create table query
                using (MySqlCommand createCommand = new MySqlCommand(createTableQuery, connection))
                {
                    createCommand.ExecuteNonQuery();
                }

                // Prepare the SQL INSERT statement
                string placeholders = string.Join(", ", new string[headers.Count].Select(h => "?"));
                string insertQuery = $"INSERT INTO my_table ({string.Join(", ", headers.Select(h => h.Trim('`')))}) VALUES ({placeholders})";

                // Insert data into the table
                foreach (List<string> record in data)
                {
                    using (MySqlCommand insertCommand = new MySqlCommand(insertQuery, connection))
                    {
                        for (int i = 0; i < record.Count; i++)
                        {
                            insertCommand.Parameters.AddWithValue($" @2024\本地文件打包__20180302\Spire.Web\trunk\src\website\components\com_virtuemart\themes\default\templates\browse\includes\browse_searchparameter_form.tpl.php{i}", record[i]);
                        }
                        insertCommand.ExecuteNonQuery();
                    }
                }
            }

            Console.WriteLine("데이터를 성공적으로 내보냈습니다!");
        }
    }
}

wb.OpenPassword = "psd";
wb.LoadFromFile("file.xlsx");

CellRange cell = sheet.Range[1, 1];
if (cell.HasFormula)
{
    string result = cell.FormulaValue.ToString();
}

DataTable data = sheet.ExportDataTable(range, exportColumnNames: true, computedFormulaValue: true);

Workbook workbook = new Workbook();
workbook.LoadFromFile("input.xlsx");
Worksheet sheet = workbook.Worksheets[0];

for (int row = 1; row <= sheet.LastRow; row++)
{
    for (int col = 1; col <= sheet.LastColumn; col++)
    {
        string cellValue = sheet.Range[row, col].Value?.ToString() ?? string.Empty;
        Console.WriteLine(cellValue);
    }
}