Импорт в базу данных из документа Microsoft Excel при помощи EPPlus за 10 шагов

Microsoft Excel очень популярная программа (и это вполне заслужено).  С версии Excel 2007 появился формат «xlsx». Этот формат открытый и его используют также и другие программы, например, OpenOffice Calc.  Вот с документов такого формата и будут импортироваться данные в базу.

Простым пользователям нравится работать с таблицами в Экселе. Логика работы программы понятна и проста. Поэтому данные очень часто представляются именно в таком виде. Разберем  один из вариантов импорта в реляционную базу данных SQL Server. Хотя точно такой прием может быть применен и для импорта в любую другую базу, например MySQl.

Для работы с xlsx понадобится библиотека EPPlus (Allows Excel files(xlsx;xlsm) to be created on the server). Взять ее можно http://epplus.codeplex.com

Конкретно будет использоваться версия EPPlus.dll 4.0.4.0, проект asp.net mvc razor, язык программирования C#.

1. Создадим UploadController.cs
2. Добавим туда неймспейсы:

using OfficeOpenXml;
using System.IO;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;

3. Создадим метод со страницей формы загрузки

[HttpGet]
public ActionResult Index()
{
    return View();
}

4. Создадим вьюху для этого метода Index.cshtml

@using (Html.BeginForm("Index","Upload",FormMethod.Post,new {enctype = "multipart/form-data"}))
{
    
    <fieldset>
        <label for="file">Выберите файл для загрузки</label>
        <input type="file" name="file" id="file" />
    </fieldset>

    
    @Html.ValidationSummary()
    <input type="submit" value="Импортировать" />
}

Обратите внимание у формы должен быть прописан атрибут enctype. Без него загрузка работать не будет.

5. Добавим HttpPost метод Index. В него будет переданы данные из формы.

[HttpPost]
public ActionResult Upload(HttpPostedFileBase file)

 

6. Отправить в форму можно какой угодно файл, а не только в формате xlsx. Поэтому добавляем простейшую валидацию.

if (file == null || file.InputStream == null)
{
    ModelState.AddModelError("file", "Файл не выбран");
    return View();
}

if (Path.GetExtension(file.FileName) != ".xlsx")
{
    ModelState.AddModelError("file", "Выберите файл с расширением xlsx");
    return View();
}

Импорт будет состоять из следующих этапов:

• генерация SQL скрипта на основе данных из файла
• выполнение SQL скрипта в базе получателе

Условно считаем, что таблица получатель называется [TableDestination].

7. Добавим вспомогательный метод в контроллер. Он будет использоваться для чтения данных из конкретной ячейки таблицы.

private string GV(ExcelWorksheet sheet,int rowNo, int cellNo)
{
    return sheet.Cells[rowNo, cellNo].Value != null ? sheet.Cells[rowNo, cellNo].Value.ToString() : "";
}

8. Генерация скрипта, привожу кусок кода целиком.

StringBuilder sbSql = new StringBuilder();
//если будут импортироваться даты это настройка переключит текущую сессию SQL Server в формат дат ДД.ММ.ГГГГ
sbSql.Append("set dateformat dmy;");
//очистка таблицы получателя
sbSql.Append("DELETE FROM [TableDestination];");
using (var xls = new ExcelPackage(file.InputStream))
{
    //тут подставьте имя своего листа, импортировать можно из любого листа
    using (var sheet = xls.Workbook.Worksheets["лист1"])
    {
        for (int j = sheet.Dimension.Start.Row + 1; j <= sheet.Dimension.End.Row; j++)
        {
            if (sheet.Cells[j, 1].Value == null) continue;

            sbSql.AppendFormat(@"INSERT INTO [TableDestination] ([Column1],[Column2],[Column3]) VALUES ('{0}','{1}','{2}');", GV(sheet, j, 2), GV(sheet, j, 3), GV(sheet, j, 4));
        }
    }
}

9. Выполняем запрос

using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DbConnection"].ConnectionString))
{
    var command = new SqlCommand(sbSql.ToString(), connection);
    connection.Open();
    int result = command.ExecuteNonQuery();
}

 

10. Ну и осталось только сообщить об успешной операции пользователю. Например так:

return Content("success");

Хотелось бы добавить, что предложенный вариант импорта не единственно возможный, различных библиотек для работы  c xlsx много. Мы работаем с EPPlus не только для чтения и для генерации документов и все задачи решаем вполне успешно.

Если же данный метод у вас работает слишком медленно, то советую обратить внимание на SqlBulkCopy. Эта библиотека работает очень быстро и подойдет  даже для импорта миллионов строк данных.

Ваше мнение по поводу данного метода очень важно мне и другим читателям. Не поленитесь и напишите комментарий, если вас заинтересовала  эта статья.