Как читать Excel в C#: Excel в DataTable и базу данных
Работа с файлами Excel является распространенной задачей во многих приложениях на C#, будь то для анализа данных, отчетности или интеграции с базами данных. В этом блоге мы рассмотрим, как читать файлы Excel в C# с использованием бесплатной библиотеки Excel для .NET. Мы охватим:
Чтение рабочего листа Excel в C#
Импорт данных в DataTable
Сохранение данных Excel в базу данных (MySQL)
Библиотека C# для чтения Excel
Для работы с файлами Excel в C# мы будем использовать Free Spire.XLS для .NET — мощную библиотеку, которая позволяет разработчикам создавать, читать, редактировать и конвертировать файлы Excel без зависимости от Microsoft Office.
Установка
Установите библиотеку через диспетчер пакетов NuGet:
Install-Package FreeSpire.XLS
Чтение рабочего листа Excel в C#
Чтобы прочитать рабочий лист Excel, вы можете использовать следующий фрагмент кода на C#. Этот пример демонстрирует, как загрузить файл Excel и пройтись по его строкам и столбцам.
Основные шаги
Создайте объект Workbook: Инициализируйте рабочую книгу для работы.
Загрузите файл Excel: Укажите путь к вашему файлу Excel.
Получите доступ к рабочему листу: Получите первый рабочий лист из книги.
Получите диапазон данных: Извлеките диапазон ячеек, содержащих данные.
Переберите строки и столбцы: Выведите значение каждой ячейки.
using Spire.Xls;
namespace ReadExcelData
{
class Program
{
static void Main(string[] args)
{
// Создайте объект Workbook
Workbook wb = new Workbook();
// Загрузите существующий файл Excel
wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");
// Получите первый рабочий лист
Worksheet sheet = wb.Worksheets[0];
// Получите диапазон ячеек, содержащих данные
CellRange locatedRange = sheet.AllocatedRange;
// Переберите строки
for (int i = 0; i < locatedRange.Rows.Length; i++)
{
// Переберите столбцы
for (int j = 0; j < locatedRange.Rows[i].ColumnCount; j++)
{
// Получите данные конкретной ячейки
string cellValue = locatedRange[i + 1, j + 1].Value?.ToString() ?? "N/A";
// Выравните вывод с шириной 22
Console.Write($"{cellValue,-22}");
}
Console.WriteLine();
}
}
}
}
Импорт данных из Excel в DataTable
Чтобы импортировать данные из файла Excel в DataTable, вы можете использовать следующий фрагмент кода. Этот пример связывает импортированные данные с DataGridView для удобного визуального отображения.
Основные шаги
Создайте форму: Используйте приложение Windows Forms.
Загрузите файл Excel: Как и в предыдущем разделе, загрузите ваш файл Excel.
Экспортируйте данные в DataTable: Используйте метод ExportDataTable для конвертации данных.
Свяжите DataTable с DataGridView: Отобразите данные в элементе управления DataGridView.
using Spire.Xls;
using System.Data;
namespace ReadExcelIntoDataTable
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
// Создайте объект Workbook
Workbook wb = new Workbook();
// Загрузите существующий файл Excel
wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");
// Получите первый рабочий лист
Worksheet sheet = wb.Worksheets[0];
// Экспортируйте данные из рабочего листа в DataTable
DataTable dataTable = sheet.ExportDataTable();
// Свяжите DataTable с DataGridView
dataGridView1.DataSource = dataTable;
// Освободите ресурсы
wb.Dispose();
}
}
}
Сохранение данных Excel в базу данных (MySQL)
Наконец, для сохранения импортированных данных в базу данных MySQL вы можете использовать следующий фрагмент кода. Этот пример извлекает данные из листа Excel и вставляет их в таблицу MySQL.
Основные шаги
Загрузите документ Excel: Как и в предыдущих шагах, загрузите ваш файл Excel.
Извлеките заголовки: Получите строку заголовков для использования в качестве имен столбцов в вашей базе данных.
Соберите данные: Извлеките каждую запись из строк данных.
Подключитесь к MySQL: Установите соединение с базой данных MySQL.
Создайте таблицу базы данных: Динамически создайте SQL для создания таблицы на основе заголовков.
Вставьте данные: Используйте параметризованные запросы для вставки каждой записи в базу данных.
using Spire.Xls;
using MySql.Data.MySqlClient;
namespace ExcelToMySQL
{
class Program
{
static void Main(string[] args)
{
// Создайте объект Workbook
Workbook wb = new Workbook();
// Загрузите документ Excel
wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");
// Получите конкретный лист
Worksheet sheet = wb.Worksheets[0];
// Извлеките заголовки
List<string> headers = new List<string>();
for (int col = 1; col <= sheet.LastColumn; col++)
{
string header = sheet.Range[1, col].Value?.ToString();
// Удалите пробелы, чтобы избежать конфликтов с именами столбцов MySQL
string cleanHeader = header?.Replace(" ", "");
headers.Add($"`{cleanHeader}`");
}
// Извлеките данные
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);
}
// Установите соединение с базой данных MySQL
string connectionString = "server=localhost;user=root;password=admin;database=excel_db;";
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
// Создайте таблицу с динамическими столбцами на основе заголовков
List<string> columns = new List<string>();
foreach (string header in headers)
{
// Предполагая, что все значения заголовков являются VARCHAR для простоты; настройте типы по мере необходимости
columns.Add($"{header} VARCHAR(255)");
}
// Создайте таблицу в базе данных
string columnsSql = string.Join(", ", columns);
string createTableQuery = $@"
CREATE TABLE IF NOT EXISTS my_table (
id INT AUTO_INCREMENT PRIMARY KEY,
{columnsSql}
)";
// Выполните запрос на создание таблицы
using (MySqlCommand createCommand = new MySqlCommand(createTableQuery, connection))
{
createCommand.ExecuteNonQuery();
}
// Подготовьте SQL-запрос INSERT
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})";
// Вставьте данные в таблицу
foreach (List<string> record in data)
{
using (MySqlCommand insertCommand = new MySqlCommand(insertQuery, connection))
{
for (int i = 0; i < record.Count; i++)
{
insertCommand.Parameters.AddWithValue($"@Param{i}", record[i]);
}
insertCommand.ExecuteNonQuery();
}
}
}
Console.WriteLine("Данные успешно экспортированы!");
}
}
}
Заключение
В этой статье мы рассмотрели, как читать данные из файлов Excel с помощью C#, импортировать эти данные в DataTable и сохранять их в базу данных MySQL. Используя библиотеку Free Spire.XLS, разработчики могут эффективно обрабатывать файлы Excel без необходимости в Microsoft Office. Этот подход может значительно улучшить рабочие процессы обработки данных в приложениях на C#.