using System.Data.SQLite;
using Spire.Xls;

namespace ExcelToSQLite
{
    class Program
    {
        static void Main(string[] args)
        {
            // Excel 文件路徑
            string excelFilePath = "G:/Documents/Sample37.xlsx";

            // SQLite 數(shù)據(jù)庫(kù)路徑
            string sqliteFilePath = "output/Database.db";

            // 打開 Excel 文件
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(excelFilePath);

            // 如果數(shù)據(jù)庫(kù)文件不存在，則創(chuàng)建它
            if (!File.Exists(sqliteFilePath))
            {
                SQLiteConnection.CreateFile(sqliteFilePath);
                Console.WriteLine("已創(chuàng)建新的 SQLite 數(shù)據(jù)庫(kù)文件：output.db");
            }

            // 創(chuàng)建 SQLite 連接
            using (SQLiteConnection connection = new SQLiteConnection($"Data Source={sqliteFilePath};Version=3;"))
            {
                connection.Open();

                // 遍歷每個(gè)工作表
                foreach (Worksheet sheet in workbook.Worksheets)
                {
                    string tableName = sheet.Name;

                    // 獲取第一行作為列名
                    var columns = sheet.Rows[0].CellList;
                    string createTableQuery = $"CREATE TABLE IF NOT EXISTS [{tableName}] (";

                    foreach (var column in columns)
                    {
                        createTableQuery += $"[{column.Value}] TEXT,";
                    }
                    createTableQuery = createTableQuery.TrimEnd(',') + ");";

                    // 創(chuàng)建表
                    using (SQLiteCommand createTableCommand = new SQLiteCommand(createTableQuery, connection))
                    {
                        createTableCommand.ExecuteNonQuery();
                    }

                    // 插入數(shù)據(jù)
                    for (int i = 1; i < sheet.Rows.Length; i++) // 跳過第一行
                    {
                        var row = sheet.Rows[i];
                        string insertQuery = $"INSERT INTO [{tableName}] VALUES (";
                        foreach (var cell in row.CellList)
                        {
                            insertQuery += $"'{cell.Value?.Replace("'", "''")}',"; // 防止 SQL 注入
                        }
                        insertQuery = insertQuery.TrimEnd(',') + ");";

                        using (SQLiteCommand insertCommand = new SQLiteCommand(insertQuery, connection))
                        {
                            insertCommand.ExecuteNonQuery();
                        }
                    }
                }

                connection.Close();
                workbook.Dispose();
            }

            Console.WriteLine("Excel 數(shù)據(jù)已成功寫入新的 SQLite 數(shù)據(jù)庫(kù)！");
        }
    }
}

using System.Data.OleDb;
using Spire.Xls;

namespace ExcelToAccess
{
    class Program
    {
        static void Main(string[] args)
        {
            // Excel 文件路徑
            string excelFilePath = "G:/Documents/Sample37.xlsx";

            // Access 數(shù)據(jù)庫(kù)路徑
            string accessDbPath = "output/Database.accdb";

            // 打開 Excel 文件
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(excelFilePath);

            // 為 Access 創(chuàng)建 OleDb 連接字符串
            string connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={accessDbPath};Persist Security Info=False;";

            // 如果數(shù)據(jù)庫(kù)文件不存在，提示創(chuàng)建 - 注意：此步驟通常在代碼外部完成
            if (!System.IO.File.Exists(accessDbPath))
            {
                Console.WriteLine("請(qǐng)先創(chuàng)建一個(gè) Access 數(shù)據(jù)庫(kù)文件。");
                return;
            }

            // 創(chuàng)建 OleDb 連接
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                connection.Open();

                // 遍歷每個(gè)工作表
                foreach (Worksheet sheet in workbook.Worksheets)
                {
                    string tableName = sheet.Name;

                    // 獲取第一行作為列名
                    var columns = sheet.Rows[0].CellList;
                    string createTableQuery = $"CREATE TABLE [{tableName}] (";

                    foreach (var column in columns)
                    {
                        createTableQuery += $"[{column.Value}] TEXT,"; // 假設(shè)所有列都是文本類型
                    }
                    createTableQuery = createTableQuery.TrimEnd(',') + ");";

                    try
                    {
                        // 創(chuàng)建表
                        using (OleDbCommand createTableCommand = new OleDbCommand(createTableQuery, connection))
                        {
                            createTableCommand.ExecuteNonQuery();
                        }

                        // 插入數(shù)據(jù)
                        for (int i = 1; i < sheet.Rows.Length; i++) // 跳過第一行
                        {
                            var row = sheet.Rows[i];
                            string insertQuery = $"INSERT INTO [{tableName}] VALUES (";
                            foreach (var cell in row.CellList)
                            {
                                insertQuery += $"'{cell.Value?.Replace("'", "''")}',"; // 防止 SQL 注入
                            }
                            insertQuery = insertQuery.TrimEnd(',') + ");";

                            using (OleDbCommand insertCommand = new OleDbCommand(insertQuery, connection))
                            {
                                insertCommand.ExecuteNonQuery();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"處理工作表 {sheet.Name} 時(shí)發(fā)生錯(cuò)誤：{ex.Message}");
                    }
                }

                connection.Close();
                workbook.Dispose();
            }

            Console.WriteLine("Excel 數(shù)據(jù)已成功寫入新的 Access 數(shù)據(jù)庫(kù)！");
        }
    }
}