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

PM> Install-Package Spire.XLS

using Spire.Xls;
    using System.IO;
    
    class Program
    {
        static void Main(string[] args)
        {
            // Load the Excel file
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("Sample.xlsx");
    
            // Get the first worksheet
            Worksheet worksheet = workbook.Worksheets[0];
    
            // Create an output text file
            string outputFile = "Output.txt";
            StreamWriter writer = new StreamWriter(outputFile);
    
            // Iterate through the rows and columns of the worksheet and write the data to the text file
            for (int row = 1; row <= worksheet.LastRow; row++)
            {
                for (int col = 1; col <= worksheet.LastColumn; col++)
                {
                    CellRange range = worksheet.Range[row, col];
                    string cellValue = range.Text == null ? string.Empty : range.Text.ToString();
                    writer.Write(cellValue + "\t"); // Separate cell data using a tab character
                }
                writer.WriteLine(); // Write a line break
            }
    
            // Close the writer and save the text file
            writer.Close();
    
            // Release resources
            workbook.Dispose();
        }
    }

using Spire.Xls;
    using System.Data;
    
    namespace ExcelToAccess
    {
        class Program
        {
            static void Main(string[] args)
            {
                // Create an object of Workbook class
                Workbook workbook = new Workbook();
    
                // Load an Excel file
                workbook.LoadFromFile("Sample.xlsx");
    
                // Get the first worksheet
                Worksheet worksheet = workbook.Worksheets[0];
    
                // Export the data from the worksheet to a DataTable object
                DataTable dataTable = worksheet.ExportDataTable();
    
                workbook.Dispose();
            }
        }
    }

Install-Package System.Data.OleDb

using Spire.Xls;
    using System.Data.OleDb;
    
    class Program
    {
        static void Main(string[] args)
        {
            // Set the Excel file path
            string excelFilePath = "Sample.xlsx";
            // Set the Access database file path
            string accessDbFilePath = "Sample.accdb";
    
            // Load the Excel file
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(excelFilePath);
    
            // Get the first worksheet
            Worksheet worksheet = workbook.Worksheets[0];
    
            // Use the worksheet name as the table name
            string tableName = worksheet.Name;
    
            // Get the first row as column names
            CellRange headerRange = worksheet.Rows[0];
            string[] columnNames = new string[headerRange.Columns.Length];
            for (int i = 0; i < headerRange.Columns.Length; i++)
            {
                columnNames[i] = headerRange.Columns[i].Value.Replace(" ", "_");
            }
    
            // Connect to the Access database
            string connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={accessDbFilePath};Persist Security Info=False;";
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                connection.Open();
    
                // Create the table
                string createTableQuery = $"CREATE TABLE [{tableName}] ({string.Join(", ", columnNames.Select(c => $"[{c}] Memo"))})";
                using (OleDbCommand createTableCommand = new OleDbCommand(createTableQuery, connection))
                {
                    createTableCommand.ExecuteNonQuery();
                }
    
                // Insert data
                string insertQuery = $"INSERT INTO [{tableName}] ({string.Join(", ", columnNames.Select(c => $"[{c}]"))}) VALUES ({string.Join(", ", columnNames.Select(c => $"@{c}"))})";
                using (OleDbCommand insertCommand = new OleDbCommand(insertQuery, connection))
                {
                    foreach (CellRange row in worksheet.Rows.Cast().Skip(1))
                    {
                        for (int i = 0; i < row.Columns.Length; i++)
                        {
                            insertCommand.Parameters.AddWithValue($"@{columnNames[i]}", row.Columns[i].Value);
                        }
    
                        insertCommand.ExecuteNonQuery();
                        insertCommand.Parameters.Clear();
                    }
                }
    
                connection.Close();
                workbook.Dispose();
            }
        }
    }